Synthèse annuelle avec une seule formule Excel

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


Sujets que vous pourriez aussi aimer :


Une formule pour un bilan annuel

Pour réaliser des extractions chirurgicales, les fonctions Excel Index et Equiv n'ont pas fini de nous surprendre.



Sur l'exemple illustré par la capture, à partir d'un tableau relatant les chiffres par mois, une synthèse globale est entreprise dans un autre tableau sur la droite. C'est une formule unique qui consolide les scores pour livrer les résultats par site et par trimestre mais aussi pour l'exercice complet. Le principe est similaire à celui qu'avait établi la formation Excel pour chercher et trouver dans une table à deux entrées. Nous allons vite le comprendre.

Classeur Excel à télécharger
Pour la mise en place de la solution, nous suggérons d'appuyer les travaux sur un classeur Excel offrant des chiffres à consolider.

• Télécharger le classeur bilan-annuel-un-calcul.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 retrouvons bien le tableau des chiffres par site et par mois. Vous notez néanmoins la présence de lignes et de colonnes de synthèse pour les trimestres et le regroupement des productions par site. Et précisément sur la droite, un petit tableau vide en son centre, attend la réunion de toutes ces informations de synthèse. Sa vocation est de faciliter l'interprétation des résultats ainsi réunis et confrontés.

Comme dans une table à deux entrées, c'est une extraction qu'il s'agit de réaliser sur deux clés de recherche. Elles sont d'ailleurs fournies en entêtes de lignes et de colonnes du tableau de synthèse.

Pour faciliter ces doubles recherches en lignes et en colonnes, des noms de plages ont été attribués.

• En haut à gauche de la feuille Excel, déployer la zone Nom,

Vous remarquez la présence de trois plages nommées. Sites désigne la ligne d'entête des productions et sites. Tab représente l'intégralité des cellules du tableau de recherche. Enfin, la plage nommée trim représente la colonne des mois et trimestres. Nous exploiterons ces noms pour désigner le tableau d'extraction et pour réaliser les recherches de positions dans les titres. Nous allons donc imbriquer deux fonction Equiv dans la fonction Index.

La fonction d'extraction
Nous l'avons dit, nous allons maintenant initialiser l'extraction sur la base de ces deux clés de recherche dynamiques, grâce à la fonction Index.

• Cliquer sur la première cellule d'extraction du petit tableau, soit en Q4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule d'extraction,
• Inscrire la fonction d'extraction suivie d'une parenthèse, soit : Index(,
• Désigner le tableau de recherche par son nom, soit : tab,
• Puis, taper un point-virgule (;) pour passer dans l'argument de l'indice de ligne,

Position en ligne
C'est dans cet argument que nous devons repérer la position de l'information cherchée dans la colonne nommée trim. Cette information n'est autre que le nom du trimestre rappelé en cellule P4 voisine du calcul. Et comme vous le savez, pour trouver dynamiquement cette position, nous avons besoin de la fonction Equiv.

• Inscrire la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner le trimestre cherché par les coordonnées de sa cellule, soit : P4,
• Enfoncer trois fois la touche F4 du clavier pour la libérer en ligne , ce qui donne : $P4,

En effet, nous allons reproduire la formule finale vers le bas et sur la droite. Vers le bas, la ligne doit suivre pour considérer les autres trimestres. Donc elle doit être libérée. Sur la droite en revanche, elle doit toujours être utilisée comme source de recherche. Elle ne doit pas suivre le déplacement. Nous la conservons figée, d'où la présence du dollar devant l'indice de colonne.

• Taper un point-virgule (;) pour passer dans l'argument de la colonne de recherche,
• Désigner la colonne des trimestres par son nom, soit : trim,
• Puis, taper un point-virgule suivi du chiffre zéro : ;0, pour une recherche exacte,
• Dès lors, fermer la parenthèse de la fonction Equiv,
• Puis, taper un point-virgule (;) pour atteindre l'indice de colonne de la fonction Index,

Position en colonne
Dans ce nouvel argument, le principe est similaire. Mais il ne s'agit plus de chercher le trimestre dans sa colonne de titre. Il est question de trouver la position du site dans sa ligne de titre. La fonction Excel Equiv est donc de nouveau de mise. Le croisement de ces deux positions fournira à la fonction Index l'emplacement exact de l'information à extraire.

• Inscrire de nouveau la fonction de recherche suivie d'une parenthèse, soit : Equiv(,
• Désigner le premier site cherchéen cliquant sur sa cellule Q3,
• Cette fois, enfoncer seulement deux fois la touche F4 du clavier, ce qui donne : Q$3,

Le principe est inversé par rapport au cas précédent. En répliquant la formule vers le bas, la recherche sur le nom du site ne doit pas bouger en ligne ($3). En revanche sur la droite, ce sont bien les autres sites qui doivent être considérés. Donc, c'est la colonne qui reste libre de suivre le déplacement cette fois.

• Taper un point-virgule (;) pour passer dans l'argument de la ligne de recherche,
• Désigner la ligne de titre par son nom, soit : sites,
• Taper un point-virgule suivi du chiffre zéro : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Puis, fermer la parenthèse de la fonction Index,
• Enfin, valider la formule par le raccourci clavier CTRL + Entrée,

Grâce à cette technique, nous conservons active la cellule du résultat. Le premier résultat importé tombe et il est tout à fait cohérent. Il s'agit bien du bilan pour le site1 pour le premier trimestre.

• Tirer la poignée du résultat vers le bas jusqu'en cellule Q7,
• Puis, tirer la poignée de la sélection vers la droite jusqu'en colonne S,

Comme vous pouvez l'apprécier, ce sont bien tous les résultats de synthèse qui sont strictement importés pour ce bilan annuel. Et c'est une unique formule qui a permis d'extirper toutes les données situées aux croisements des informations cherchées. La syntaxe complète est la suivante :

=INDEX(tab; EQUIV($P4; trim; 0); EQUIV(Q$3; sites; 0))

Vous l'avez compris, outre l'imbrication des fonctions de recherche Equiv dans la fonction d'extraction Index, l'enjeu de cette solution réside dans l'emploi des références absolues judicieusement calibrées.