Comparer les résultats de plusieurs feuilles Excel

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


Sujets que vous pourriez aussi aimer :


Importer les données des autres feuilles

Avec cette nouvelle astuce Excel, nous poursuivons la découverte des prouesses permises par l'irrésistible fonction Indirect.



Dans l'exemple finalisé illustré par la capture, nous comparons les résultats des commerciaux en important leurs chiffres depuis les feuilles dans lesquelles ils sont archivés. Deux listes déroulantes permettent de choisir deux commerciaux respectifs. Ces noms désignent les feuilles où leurs résultats sont implantés. Grâce à la fonction Excel Indirect, nous pointons directement et dynamiquement sur la bonne feuille reconnue par le nom choisi avec la liste déroulante. Et c'est ce que nous allons découvrir ensemble.

Classeur source
Pour simplifier la mise en application, nous proposons de baser l'étude sur un classeur hébergeant déjà ces données dans de multiples feuilles.

• Télécharger le classeur adresse-indirecte-feuille.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 de ce classeur. Elle offre les deux tableaux destinés à établir les comparaisons dynamiques des résultats. Au-dessus de ces tableaux, deux listes déroulantes permettent de choisir deux noms respectifs de commerciaux à confronter. En consultant les onglets en bas de la fenêtre Excel, vous remarquez effectivement que les feuilles portent les noms des commerciaux.



C'est cette astuce qui va nous permettre de pointer sur le bon emplacement à prélever, en fonction du choix émis dans une liste et ce, grâce à la fonction Indirect.

Si vous cliquez sur les autres onglets en bas de la fenêtre Excel pour consulter les autres feuilles, vous constatez que tous les tableaux des résultats ont la même structure. Chaque première donnée à prélever débute à partir de la ligne 3. Les quantités à importer sont en colonne D tandis que les totaux hors taxes sont en colonne E.



En revanche et c'est intéressant, les organisations diffèrent. Les articles ne sont pas forcément classés dans le même ordre d'une feuille à une autre. Mais grâce à la puissance d'Excel nous le verrons, nous réaliserons néanmoins des consolidations parfaites dans les deux tableaux de la feuille d'accueil.

Importer et comparer
Dans la syntaxe des formules Excel, les coordonnées d'une "cellule externe" sont préfixées du nom de la feuille les hébergeant. Et ce nom est lui-même suivi d'un point d'exclamation. C'est lui qui traduit la hiérarchie permettant de partir de la feuille pour descendre jusqu'à ses cellules. Et nous l'avons dit, ces noms de feuilles sont renvoyés dynamiquement par les choix émis avec les deux listes déroulantes. Mais pour que ces informations soient considérées comme telles, elles doivent être interprétées avec la fonction Excel Indirect.

• Revenir sur la feuille AdresseIndirect,
• Sélectionner la case de la première quantité à importer en cliquant sur la cellule C6,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire le nom de la fonction d'extraction suivi d'une parenthèse, soit : Index(,
• Taper alors le nom de la fonction d'interprétation suivi d'une parenthèse, soit : Indirect(,
• Cliquer alors sur la cellule de la première liste déroulante, soit C3,
• Puis, enfoncer la touche F4 du clavier pour la figer, ce qui donne : $C$3,

En effet, la formule que nous sommes en train de bâtir est vouée à être répliquée sur les lignes du dessous. Et au fil de la réplication, cette cellule ne doit pas suivre le déplacement de la formule pour continuer de pointer sur le nom de la feuille.

Maintenant, nous devons poursuivre la construction de l'adresse de la plage d'extraction. Il s'agit de celle des quantités, quelle que soit la feuille, puisque son nom est mentionné directement grâce à la fonction Indirect.

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

Ainsi, nous nous apprêtons à descendre dans la hiérarchie pour désigner les cellules de la feuille.

• Inscrire les coordonnées de la plage pour les quantités, soit : D3:D10,
• Puis, fermer les guillemets,

Comme cette plage est précisée dans les guillemets, elle est statique. Donc les dollars des références absolues ne sont pas utiles.

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

Nous l'avons évoqué, cet indice de ligne est variable. D'une feuille à une autre, les références des articles ne sont pas forcément classées dans le même ordre. Pour chacune, nous devons donc retrouver leur position. Et comme vous le savez, c'est la fonction Excel Equiv qui renseigne sur la ligne d'une donnée cherchée.

• Taper le nom de la fonction de recherche de position suivi d'une parenthèse, soit : Equiv(,
• Désigner la première référence cherchée en cliquant sur sa cellule B6,
• Puis, taper un point-virgule (;) pour passer dans l'argument du tableau de recherche,

Ce tableau n'est autre qu'une colonne. Et quelle que soit la feuille, les références sont inscrites en colonne B entre les lignes 3 et 10. Et comme précédemment, pour pointer dynamiquement sur cette plage, nous devons de nouveau reconstruire son adresse.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner le nom dynamique de la feuille en cliquant sur la cellule C3 de la première liste,
• Comme précédemment, enfoncer la touche F4 du clavier pour la figer dans le calcul,
• Inscrire le symbole de concaténation (&),
• Puis, ouvrir les guillemets suivis d'un point d'exclamation, soit : "!,
• Dès lors, saisir les coordonnées de la plage de recherche, soit : B3:B10,
• Puis, fermer les guillemets et fermer la parenthèse de la fonction Indirect,

De fait, nous sommes de retour dans les arguments de la fonction Equiv, plus précisément dans le deuxième sur le tableau de recherche dont nous venons de reconstruire les coordonnées.

• Taper un point-virgule suivi du chiffre zéro : ;0, pour réaliser une recherche exacte,
• Fermer alors la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index qui l'englobe,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

Grâce à cette technique et comme vous le savez, nous conservons active la cellule du résultat pour l'exploiter dans l'enchaînement. Et c'est ce que nous allons faire.

• Double cliquer sur la poignée du résultat pour la répandre jusqu'en ligne 13,

Toutes les quantités vendues pour le commercial désigné par défaut sont effectivement importées. Et si vous changez le nom de ce vendeur avec la première liste déroulante, vous constatez que les données importées varient. L'extraction a bien été produite dans une autre feuille, celle correspondant au nom désigné dynamiquement. Vous pouvez facilement vérifier la cohérence de ces informations en basculant sur la bonne feuille et ce, malgré l'organisation variable des références.

Pour importer les totaux hors taxes, la formule est la même au détail près que la plage d'extraction doit être adaptée. Il ne s'agit plus de la colonne D mais de la colonne E. C'est la nature statique de cette construction particulière qui impose cette intervention manuelle.

=INDEX(INDIRECT($C$3 & "!E3:E10"); EQUIV(B6; INDIRECT($C$3 & "!B3:B10"); 0))

Après avoir validé et répliqué cette formule, vous constatez que tous les montants correspondants sont effectivement importés à leur tour.

Dès lors, pour les données du second commercial à comparer avec le premier, il suffit de reproduire et de répliquer ces deux précédentes formules, en adaptant le nom de la feuille choisi par la liste déroulante en H3.

Pour les quantités :
=INDEX(INDIRECT($H$3 & "!D3:D10"); EQUIV(B6; INDIRECT($H$3 & "!B3:B10"); 0))

Pour les totaux hors taxes :
=INDEX(INDIRECT($H$3 & "!E3:E10"); EQUIV(B6; INDIRECT($H$3 & "!B3:B10"); 0))

Il est désormais très simple de comparer les résultats de deux commerciaux en important automatiquement leurs données par simples choix dans les listes déroulantes. Et ces comparaisons sont d'autant plus simples à réaliser qu'une mise en forme conditionnelle prédéfinie se déclenche automatiquement. Elle passe en vert chaque ligne d'un commercial pour laquelle le total hors taxes est supérieur à la ligne correspondante de l'autre commercial.



Si vous souhaitez consulter ces deux règles, vous devez d'abord sélectionner une cellule de l'un des deux tableaux. Ensuite, dans la section Styles du ruban Accueil, vous devez cliquer sur le bouton Mise en forme conditionnelle. En bas de la liste des propositions, vous devez alors choisir l'option Gérer les règles. Enfin, dans la boîte de dialogue qui apparaît, vous devez cliquer sur le bouton Modifier la règle.

Pour finir, pour une formule plus dynamique, capable de se répliquer automatiquement des quantités jusqu'aux totaux, nous aurions pu bâtir la syntaxe suivante :
=INDEX(INDIRECT($C$3 &"!" & ADRESSE(LIGNE(A$3); COLONNE(D$3)) & ":" & ADRESSE(LIGNE(A$10); COLONNE(D$10))); EQUIV($B6; INDIRECT($C$3 & "!B3:B10"); 0))