Consolider les données des feuilles précédentes

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Consolider les feuilles précédentes

Avec cette nouvelle astuce Excel, nous poursuivons la découverte sur les possibilités offertes par la fabuleuse fonction Indirect.



Dans ce nouveau volet, il est question de consolider tous les résultats des mois précédents sur chaque nouvelle feuille, donc sur chaque feuille suivante. Et comme l'illustre la capture de l'exemple finalisé, dans cette première solution, toutes les feuilles offrent des noms incrémentés par un numéro.

Classeur source
Un classeur propose déjà cette construction sur la base de feuilles incrémentées. Nous suggérons de le récupérer.

• Télécharger le classeur cumuls-feuilles-suivantes.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur la première feuille du classeur. Elle est nommée Mois01. Son tableau est intégralement complété. Dans la colonne E (Mois M), elle présente les quantités vendues par article au cours de ce mois. Les références de ces articles sont listées en colonne C (Article). La colonne D (Mois M-1) présente fort naturellement des résultats vierges pour cette première feuille. Sur les feuilles suivantes pour les mois suivants, c'est elle qui devra récupérer les résultats de la feuille précédente pour le mois précédent afin de réaliser le cumul en dernière colonne, la colonne F du cumul. Fort logiquement, les tableaux de chaque feuille proposent la même structure.

Le nom de la feuille
Il y a forcément une logique incrémentée dans les noms des feuilles : Mois01, Mois02, Mois03, etc... Attention néanmoins, s'il y a un espace dans le nom d'onglet, le nom de la feuille reconstruit par calcul doit être stipulé entre simples côtes. Nous le comprendrons. La technique que nous allons découvrir permet de rendre dynamique l'adressage indirect pour répliquer la formule sur une feuille et toutes les suivantes.

Pour chaque nouveau mois, à partir de la cellule D4, nous devons récupérer les quantités cumulées du mois précédent depuis la cellule F4 (Colonne du cumul). C'est en effet dans cette colonne qu'une opération d'addition est naturellement entreprise pour cumuler les données en cascade. Pour désigner une feuille par son nom, encore faut-il être capable de récupérer dynamiquement ce nom.

• En bas de la fenêtre Excel, cliquer sur l'onglet Mois02 pour activer sa feuille,
• Cliquer sur la case de la première quantité à récupérer, soit la cellule D4,
• Taper le symbole égal (=) pour initier la syntaxe du calcul,
• Inscrire la fonction d'information suivie d'une parenthèse, soit : Cellule(,
• Dans la liste des propositions, choisir le paramètre nomfichier, soit : "nomfichier",
• Taper un point-virgule (;) pour passer dans le second argument de la fonction Cellule,
• Puis, cliquer par exemple sur la cellule A1,
• Fermer la parenthèse de la fonction Cellule puis valider le calcul avec la touche Entrée,

La fonction Cellule est classiquement utilisée pour obtenir des informations précises sur une cellule. Mais avec le paramètre nomfichier, nous récupérons le chemin complet du classeur jusqu'au nom de la feuille :

E:\Formation\Excel\ ... \[cumuls-feuilles-suivantes.xlsx]Mois02

On désigne donc arbitrairement la cellule A1. Pour un nom d'onglet, ce choix n'a pas d'importance. Pour visualiser la chaîne complète, il faut inscrire cette formule : =CELLULE("nomfichier"; A1), dans une cellule n'appartenant pas au tableau.

Prélever une partie du nom
Nous savons que les noms des feuilles débutent nécessairement par le mot Mois et se terminent fatalement par un numéro à deux chiffres. Pour exercer une décrémentation afin de pouvoir pointer sur la feuille précédente, nous devons isoler ce numéro. C'est la fonction Excel Droite qui permet de prélever un bout de chaîne par la fin.

• En cellule D4, adapter la précédente syntaxe comme suit :

=DROITE(CELLULE("nomfichier"; A1); 2)

Avec le chiffre 2 en second paramètre de la fonction Droite, nous ne gardons que les deux derniers caractères de la chaîne. Il en résulte le numéro 02. Le zéro en préfixe est étonnamment conservé à ce stade. Ce bout de chaîne est encore considéré comme un texte. Et pourtant, nous devons le décrémenter. Mais nous allons le comprendre, Excel s'adapte.

• Adapter la syntaxe précédente en retranchant une unité, soit :

=DROITE(CELLULE("nomfichier";A1); 2)-1

La soustraction est parfaitement réalisée. De fait, il en résulte le chiffre 1 et le zéro en préfixe a disparu puisque la donnée a été convertie en un nombre. Donc, nous savons que le préfixe d'une feuille est nécessairement le texte : Mois0. Pour plus de souplesse, nous pourrions envisager un test avec la fonction Si (DROITE(CELLULE("nomfichier"; A1);2)-1<10). Sous la dizaine, nous saurions ainsi ajouter dynamiquement le zéro en préfixe et conserver le nombre tel quel à partir de 10.

Nom de la feuille précédente
Désormais donc, pour aboutir au nom de la feuille précédente, il suffit d'assembler le texte Mois0 avec le chiffre décrémenté grâce au symbole de concaténation (&).

• En conséquence, adapter la précédente syntaxe comme suit :

="Mois0" & DROITE(CELLULE("nomfichier"; A1); 2)-1



A validation, nous obtenons bien le nom de l'onglet précédent. Vous remarquez que le calcul du cumul conduit à une erreur en colonne F. En effet, il consiste en une addition qui ne peut pas fonctionner avec un texte. Tout rentrera dans l'ordre lorsque nous aurons finalisé la formule consistant à récupérer la quantité depuis la feuille précédente.

Cumuler les données des feuilles
Précisément, nous devons maintenant exploiter ce nom reconstitué avec la fonction Excel Indirect. Comme vous le savez, elle permet d'interpréter l'information qui lui est passée pour la considérer comme un objet de feuille par exemple.

• Sélectionner de nouveau la cellule D4,
• Dans la barre de formule, cliquer juste après le symbole égal pour y placer le point d'insertion,
• Saisir le nom de la fonction d'interprétation suivi d'une parenthèse, soit : Indirect(,
• Puis, cliquer à la fin de la syntaxe après le chiffre 1 pour y placer le point d'insertion,

Comme vous le savez, pour atteindre une cellule d'une autre feuille, dans l'adresse, le nom de la feuille doit être suivi d'un point d'exclamation. Et grâce à la construction du calcul précédent, nous avons déjà passé le nom de la feuille cible à la fonction Indirect.

• Enfoncer la touche 1 en haut à gauche du clavier pour le symbole de concaténation (&),
• Puis, taper un point d'exclamation entre guillemets, soit : "!",

Maintenant, nous pouvons désigner la cellule à pointer sur la feuille précédente. Il s'agit de la cellule F4 pour le premier cumul. Mais cette formule doit pouvoir se répliquer sur les lignes du dessous, pour que tous les précédents cumuls soient rapatriés. Grâce à la fonction Excel Adresse notamment, nous allons pouvoir reconstruire dynamiquement ces coordonnées.

• Ajouter de nouveau un symbole de concaténation (&),
• Inscrire la fonction pour les coordonnées suivie d'une parenthèse, soit : Adresse(,

A cette fonction, nous devons lui passer deux paramètres. Il s'agit respectivement de l'indice de ligne et de l'indice de colonne de la cellule à atteindre. L'indice de colonne est connu et il est fixe. Il s'agit de la colonne F donc de la colonne 6. L'indice de ligne doit quant à lui s'incrémenter avec la formule répliquée sur les lignes du dessous. Pour cela, il suffit d'exploiter la fonction Excel Ligne à partir d'une cellule de la ligne 4 qui est la ligne de départ.

• Inscrire la fonction pour le numéro de ligne suivie d'une parenthèse, soit : Ligne(,
• Désigner une cellule de la quatrième ligne en cliquant sur B4 par exemple,
• Fermer la parenthèse de la fonction Ligne,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de colonne,
• Taper le chiffre 6 pour pointer sur la colonne F,
• Fermer la parenthèse de la fonction Adresse,
• Puis fermer la parenthèse de la fonction Indirect,
• Enfin, valider la formule avec le raccourci clavier CTRL + Entrée,

Grâce à cette astuce, nous gardons active la cellule du résultat pour l'exploiter dans la foulée. Vous remarquez que le premier résultat tombe et il s'agit bien du premier cumul pour le mois précédent.

• Double cliquer sur la poignée du résultat pour répliquer la formule sur tout le tableau,

Tous les cumuls de la feuille précédente sont effectivement rapatriés dans l'ordre. De fait, tous les actuels cumuls se mettent automatiquement à jour en colonne F. La syntaxe complète de la formule de consolidation que nous avons construite est la suivante :

=INDIRECT("Mois0" & DROITE(CELLULE("nomfichier"; A1); 2)-1 & "!" & ADRESSE(LIGNE(B4); 6))

Il ne reste plus qu'à implanter cette formule strictement à l'identique sur les autres feuilles.

• Sélectionner la plage de cellules D4:D13,
• La copier avec le raccourci clavier CTRL + C,
• Cliquer sur l'onglet Mois03 en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule D4, puis coller (CTRL + V) les formules copiées,
• Puis, répliquer le procédé sur toutes les feuilles jusqu'à Mois06,

Comme vous le constatez, nous avons parfaitement réussi à produire le cumul des résultats sur les feuilles suivantes grâce à une seule formule à répliquer. Cette fonction Indirect est donc définitivement précieuse. Et dans les prochaines Astuces Excel, nous continuerons d'en vanter ses mérites.