Consolider plusieurs feuilles sur des positions variables

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 totaux des feuilles

A l'occasion de précédentes astuces Excel, nous avons appris à consolider dynamiquement les données de plusieurs feuilles. Et pour cela, nous avions engagé la fameuse et précieuse fonction Indirect. Mais jusqu'alors, nous avons agi dans des contextes favorables et confortables. En effet, les tableaux des feuilles présentaient la même architecture.

Pour repousser toujours plus loin les limites grâce à cette fonction Indirect, nous allons maintenant apprendre à consolider les données de plusieurs feuilles, précisément lorsque l'emplacement des informations à réunir n'est pas strictement défini.

Classeur source et présentation
Pour réussir ces démonstrations, nous proposons d'appuyer l'étude sur un classeur constitué de plusieurs feuilles dans lesquelles l'organisation des données pourrait être jugée hasardeuse.

• Télécharger le classeur consolidation-totaux.xlsm 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 de ce classeur constitué de sept feuilles. La première feuille est nommée synthèse. Elle doit consolider dynamiquement les résultats des commerciaux. Et précisément, chaque feuille suivante porte le nom d'un commercial. Si vous les affichez tour à tour, vous remarquez que le bilan des quantités d'articles vendus est dressé pour chacun. C'est la ligne de synthèse qui nous intéresse. Elle est repérée par le terme Total Qte. Elle additionne toutes les quantités vendues. Mais comme vous pouvez le voir, d'une feuille à une autre, ces informations de synthèse à réunir sur la première feuille ne sont jamais placées au même endroit.

Sur la première feuille de ce classeur, vous notez que tous les noms des commerciaux, donc des feuilles, sont énumérés.



Et c'est un calcul dynamique, décomposé en deux étapes, qui permet de les lister. En d'autres termes, si vous ajoutez des feuilles à ce classeur (des nouveaux commerciaux), elles seront automatiquement intégrées dans l'énumération. Nous avions commis ce tour de force à l'occasion de l'une des astuces précédentes. Tout d'abord, un nom de formule existe. Vous pouvez consulter sa syntaxe en cliquant sur le bouton Gestionnaire de noms dans le ruban Formules.



C'est la fonction de macro XL4 Lire.Classeur qui est utilisée : =TRANSPOSE(LIRE.CLASSEUR(1)). Avec le chiffre 1 en paramètre, elle renvoie un tableau horizontal des noms des feuilles. Grâce à la fonction Transpose, nous le transformons en une liste verticale. Mais ces noms de feuilles sont accompagnés de leurs chemins d'accès complets en partant du classeur. C'est la raison pour laquelle nous devons purger chaque ligne restituée à l'aide d'une formule spécifique.

Il s'agit d'une formule matricielle. Elle est écrite à partir de la cellule D4 et elle est reproduite sur la hauteur du tableau :

{=SI(LIGNES($1:2)<=NBVAL(NomF); LIEN_HYPERTEXTE(INDEX(NomF; LIGNES($1:2)) & "!A1"; INDEX(STXT(NomF; TROUVE("]"; NomF)+1; 100); LIGNES($1:2))); "")}

Elle parcourt toutes les lignes du tableau des noms des feuilles, en les purgeant des chemins en préfixe, pour les restituer tour à tour, par le jeu de la réplication du calcul. Sa construction est largement expliquée dans une astuce précédente.

Réunir les totaux éparpillés
C'est aussi un raisonnement matriciel qui doit nous permettre de regrouper tous les résultats des commerciaux en regard de leurs noms. Pour cela, il est question d'extraire sur chaque feuille la donnée numérique située à droite de l'information Total Qte. Mais comme son emplacement varie, nous devons rechercher pour chaque commercial, son emplacement en ligne et son emplacement en colonne. En fournissant ces deux indications à la fonction Index, nous pourrons regrouper tous les totaux.

• Sélectionner la case du premier total à rapatrier en cliquant sur la cellule E4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Puis, construire le critère suivant : D4<>"",

En effet, l'extraction ne doit avoir lieu que dans la mesure où le nom du commercial, donc le nom de la feuille, existe.

• Dès lors, taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,

Pour désigner le tableau d'extraction, nous devons d'abord pointer sur la feuille reconnue par le nom du commercial inscrit en regard de la formule. Et comme vous le savez, pour que ce texte soit considéré comme un nom de feuille, nous devons l'interpréter.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner la feuille en cliquant sur le nom du commercial, soit : D4,

Sur cette feuille pointée dynamiquement, comme nous ne connaissons pas l'emplacement du total à récupérer, nous devons exercer la recherche sur une plage suffisamment grande. Ainsi, nous nous assurerons de l'englober dans la zone analysée.

• A la suite de la syntaxe, ajouter l'assemblage suivant : &"!A1:Z50",

Grâce au caractère de concaténation (&), au nom de la feuille nous associons la plage d'étude préfixée d'un point d'exclamation. C'est lui qui indique à Excel de descendre dans la hiérarchie, de la feuille aux cellules.

• Fermer la parenthèse de la fonction Indirect,
• Puis, taper un point-virgule (;) pour passer dans l'argument de la ligne de la fonction Index,

Cette position en ligne doit être trouvée par la recherche du titre Total Qte. Dans le raisonnement matriciel que nous sommes en train d'engager, ce sont toutes les cellules de la plage définie qui vont être passées en revue tour à tour. La formule va donc retourner un tableau de valeurs. Dans ce tableau, nous devons trouver celle qui répond favorablement. En engageant un test de concordance sur ce titre, seule une cellule portera une valeur booléenne permettant de repérer la seule ligne effective. Elle sera donc considérée comme la plus petite dans le lot. Pour l'extraire, il suffit donc d'engager la fonction Min avant d'exercer le test.

• Inscrire la fonction du minimum suivie d'une parenthèse, soit : Min(,
• Inscrire la fonction conditionnelle suivie d'une parenthèse, soit : Si(,
• Désigner de nouveau la plage sur sa feuille en répliquant la construction précédente, soit :

INDIRECT(D4&"!A1:Z50")

• Puis, ajouter le critère suivant :="Total Qte",

Dans ce raisonnement matriciel, cellule à cellule, c'est effectivement le titre à débusquer pour trouver l'information numérique de synthèse à récolter.

• Taper un point-virgule (;) pour passer dans la branche Alors de la fonction Si,
• Inscrire la fonction donnant la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,

Bien sûr, en guise de cellule, c'est une matrice que nous allons lui passer. Et cette matrice n'est autre que la plage de recherche. Ainsi, le test de la fonction Si dévoilera la cellule cherchée dont l'indice de ligne sera retourné en première position grâce à la fonction Min.

• Désigner de nouveau la plage de la feuille analysée, en répliquant l'assemblage précédent :

INDIRECT(D4&"!A1:Z50")

• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Si,

En effet, la branche Sinon de cette dernière n'est pas utile. Seule l'unique concordance nous intéresse.

• Puis, fermer la parenthèse de la fonction Min,
• Taper un point-virgule (;) pour passer dans l'argument de la colonne pour la fonction Index,

Pour la position en colonne, le principe est exactement le même. La recherche doit être réalisée sur le titre. Lorsque la concordance est trouvée, cette fois c'est l'indice de la colonne qui doit être retourné à la place de l'indice de ligne. Il suffit donc de remplacer la fonction Ligne par la fonction Colonne.

• Recopier et adapter la syntaxe de l'argument de la ligne de la fonction Index, comme suit :

Min(Si(INDIRECT(D4&"!A1:Z50") = "TotalQte"; Colonne(INDIRECT(D4&"!A1:Z50"))))

• Puis, ajouter une unité à ce résultat trouvé, soit : +1,

En effet, c'est l'information numérique située à droite du titre de synthèse que nous devons récolter.

• Fermer la parenthèse de la fonction Index,
• Taper un point-virgule (;) pour passer dans la branche Sinon de la première fonction Si,
• Inscrire deux guillemets ("") pour garder la cellule vide lorsque le nom de feuille n'existe pas,
• Fermer la parenthèse de la fonction Si,
• Enfin, valider nécessairement la formule matricielle par le raccourci CTRL + MAJ + Entrée,

Le premier résultat tombe. Il indique un total de 590 unités vendues. Et si vous affichez la feuille du premier commercial, vous remarquez qu'il s'agit effectivement de son bilan. Voyons maintenant si les autres peuvent être consolidés par cette même formule, malgré leurs positions variables.

• Cliquer et glisser la poignée du résultat jusqu'en cellule E13,

L'extraction s'arrête aussitôt que la fin de l'énumération des noms de feuilles est atteinte. Après une vérification rapide, vous constatez que toutes les données des différents onglets sont réunies malgré des emplacements variables. Si vous créez une nouvelle feuille en dupliquant la dernière et en renommant la copie, vous remarquez qu'elle est intégrée dans la feuille de synthèse avec son score. Mais il est nécessaire de retirer la poignée de la première formule en raison de sa nature spécifique.

La syntaxe complète de la formule matricielle que nous avons bâtie est la suivante :

{=SI(D4<>""; INDEX(INDIRECT(D4&"!A1:Z50"); MIN(SI(INDIRECT(D4&"!A1:Z50") = "Total Qte"; LIGNE(INDIRECT(D4&"!A1:Z50")))); MIN(SI(INDIRECT(D4&"!A1:Z50") = "Total Qte"; COLONNE(INDIRECT(D4&"!A1:Z50")))) + 1); "")}