Dans ce tutoriel, nous allons créer une formule unique capable de calculer automatiquement un total dans n’importe quelle feuille Excel, simplement en choisissant son nom dans une liste déroulante.
Lorsque nos ventes, nos dépenses ou nos relevés sont répartis sur plusieurs onglets, nous finissons souvent par dupliquer les mêmes formules, modifier les noms de feuilles à la main et risquer une erreur à chaque changement.
Ici, nous allons combiner les fonctions « SOMME.SI » et « INDIRECT » pour construire une synthèse entièrement dynamique, qui sélectionne la bonne feuille, retrouve les lignes correspondant à notre critère et additionne automatiquement les valeurs associées.
Et à la fin, nous verrons comment utiliser des tableaux structurés pour que le calcul prenne également en compte toutes les nouvelles lignes ajoutées, sans modifier une seule référence.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le classeur suivant dans lequel nous retrouvons les relevés de consommation électrique de plusieurs bâtiments.
Ce classeur contient cinq feuilles : « Synthese », « Atelier », « Bureaux », « Entrepot » et « Boutique ».
Dans chaque feuille de bâtiment, nous retrouverons la même organisation.
Nous devons respecter exactement les mêmes colonnes dans toutes les feuilles.
Dans notre exemple, les usages sont toujours placés dans la colonne A et les consommations dans la colonne B.
Dans la feuille « Synthese », nous retrouvons les cellules « Bâtiment », « Usage » et « Consommation totale » :
La cellule B6 accueillera le bâtiment sélectionné, B7 contiendra l’usage recherché et B8 affichera le résultat.
Notre objectif est maintenant très concret. Si nous sélectionnons « Atelier » en B6 et « Machines » en B7, Excel devra additionner 2 850, 3 120 et 2 940, pour afficher un total de 8 910 kWh.
2. Créer les listes déroulantes et tester SOMME.SI
2.1. Créer la liste des bâtiments
Pour éviter les fautes de frappe, nous allons créer deux listes déroulantes.
Nous avons déjà vu dans des tutoriels précédent comment créer des listes déroulantes, donc pour gagner du temps, je vous propose d’utiliser la fonctionnalités « Création de listes » de ma « Barre d’outils by excelformation », qui ajoute de nombreuses fonctionnalités directement dans Excel, et notamment la création de listes déroulantes.
Pour cela, nous sélectionnons la cellule B6, puis nous nous rendons dans Excelformation > Insérer liste déroulante :
Dans la liste « Ajouter une liste », nous nous rendons tout en bas, et nous y retrouvons la liste des onglets :
Il ne reste plus qu’à supprimer « Synthèse », puis à valider :
Pour les usages, nous sélectionnons la cellule B7, puis nous rendons à nouveau dans l’outil de création de listes.
Ensuite, nous cliquons sur le bouton « […] » du champ « Ajouter une plage de cellules » pour aller sélectionner la liste dans la feuille « Atelier » :
Cela permet de récupérer la liste des usages de chaque bâtiment :
Cette précaution est importante, car la fonction INDIRECT construira une référence à partir du texte présent dans B6.
Une simple différence d’accent ou un espace ajouté par erreur suffirait à provoquer une erreur « #REF! ».
2.2. Tester une formule avec une feuille fixe
Avant de rendre notre formule dynamique, nous allons vérifier le fonctionnement de « SOMME.SI » sur une seule feuille.
Nous sélectionnons « Machines » dans B7, puis nous saisissons dans B8 :
=SOMME.SI(Atelier!A7:A14;B7;Atelier!B7:B14)
La fonction « SOMME.SI » attend trois arguments :
- Le premier correspond à la plage dans laquelle Excel doit rechercher le critère. Ici, nous analysons les usages placés entre A7 et A14 dans la feuille « Atelier ».
- Le deuxième argument est le critère à retrouver. Nous utilisons la cellule B7, qui contient par exemple le texte « Machines ».
- Le troisième argument désigne les valeurs à additionner. Excel additionne donc les consommations de B7 à B14 lorsque l’usage situé sur la même ligne correspond à notre sélection.
Nous obtenons bien 2 850 kWh.
Le problème est que le nom « Atelier » est directement inscrit dans la formule. Même si nous sélectionnons « Boutique » en B6, Excel continuera de consulter la feuille « Atelier ».
Nous pourrions créer plusieurs fonctions « SI » imbriquées, mais la formule deviendrait rapidement longue et difficile à modifier. Nous allons plutôt demander à Excel de construire lui-même la référence à la feuille sélectionnée.
3. Combiner SOMME.SI et INDIRECT
3.1. Construire une référence dynamique
La fonction « INDIRECT » transforme un texte en véritable référence Excel.
Par exemple, si nous souhaitons obtenir la valeur de la cellule B7 de la feuille « Atelier », nous nous plaçons sur une cellule vide, nous saisissons le signer « = » et nous allons cliquer sur la cellule B7 :
Pour utiliser la valeur sélectionnée dans la cellule B7 de la feuille « synthèse », nous devons assembler plusieurs éléments avec le caractère « & » :
=B6&"!B7"
Sauf qu’ici Excel affiche alors le texte de la référence, il ne l’utilise pas encore comme une plage de cellules :
Nous ajoutons donc la fonction « INDIRECT » :
=INDIRECT(B6&"!B7")
Une bonne habitude consiste à encadrer le nom de la feuille entre apostrophes pour permettre de gérer des noms contenant des espaces :
=INDIRECT("'"&B6&"'!B7")
Cela ne change rien au résultat, mais permet d’anticiper des noms de feuilles plus complexes.
3.2. Écrire la formule complète
Nous pouvons maintenant intégrer ces deux références dans « SOMME.SI » :
=SOMME.SI(INDIRECT("'"&B6&"'!A7:A14");B7;INDIRECT("'"&B6&"'!B7:B14"))
La première fonction « INDIRECT » désigne la colonne des usages de la feuille sélectionnée.
La cellule B7 fournit le critère à rechercher, tandis que la seconde fonction « INDIRECT » désigne la colonne des consommations à additionner.
Si nous sélectionnons « Boutique » et « Climatisation », Excel calcule 980 + 1 040 + 1 010 et affiche 3 030 kWh.
Si nous choisissons « Bureaux » et « Informatique », le résultat devient 2 750 kWh, sans aucune modification de la formule.
3.3. Sécuriser le résultat
Si une feuille est renommée ou si B6 est vide, « INDIRECT » renvoie une erreur « #REF! ».
Nous allons donc entourer notre calcul avec « SIERREUR » :
=SIERREUR(SOMME.SI(INDIRECT("'"&B6&"'!A7:A14");B7;INDIRECT("'"&B6&"'!B7:B14"));0)
La valeur zéro sera désormais affichée lorsqu’une référence est incorrecte.
Pour conserver une cellule vide tant que les deux sélections ne sont pas renseignées, nous pouvons encore améliorer la formule :
=SI(OU(B6="";B7="");"";SIERREUR(SOMME.SI(INDIRECT("'"&B6&"'!A7:A14");B7;INDIRECT("'"&B6&"'!B7:B14")));0))
La fonction « OU » vérifie si B6 ou B7 est vide. Si c’est le cas, la fonction « SI » renvoie une chaîne vide. Sinon, le calcul est exécuté.
4. Rendre les bases automatiquement extensibles
4.1. Transformer les données en tableaux structurés
Notre formule fonctionne, mais elle s’arrête actuellement à la ligne 14. Une consommation ajoutée en ligne 15 ne serait donc pas prise en compte.
Pour résoudre ce problème, nous allons transformer chaque base en tableau structuré.
Nous nous rendons dans la feuille « Atelier », puis nous sélectionnons une cellule du tableau. Nous utilisons le raccourci [Ctrl]+[L].
Excel détecte automatiquement la plage A6:B14. Nous vérifions que la case « Mon tableau comporte des en-têtes » est cochée, puis nous validons avec « OK ».
Dans l’onglet « Création de tableau », nous remplaçons le nom proposé par Excel par « Atelier ».
Nous répétons l’opération dans chaque feuille et nous nommons les tableaux « Bureaux », « Entrepot » et « Boutique ».
Le nom du tableau doit correspondre exactement au texte de la liste déroulante.
Les noms de tableaux ne peuvent pas contenir d’espace.
Si nous utilisons « Atelier principal », nous devrons par exemple nommer le tableau « Atelier_principal » et reprendre exactement ce texte dans B6.
4.2. Utiliser des références structurées
Dans un tableau structuré, la colonne des usages du tableau « Atelier » s’écrit :
=Atelier[Usage]
La colonne des consommations s’écrit :
=Atelier[Consommation_kWh]
Pour rendre le nom du tableau dynamique, nous le récupérons dans B6 et nous ajoutons le nom de la colonne entre crochets.
La formule finale devient :
=SI(OU(B6="";B7="");"";SIERREUR(SOMME.SI(INDIRECT(B6&"[Usage]");B7;INDIRECT(B6&"[Consommation_kWh]"));0))
Cette version est plus lisible et surtout automatiquement extensible.
Nous pouvons le vérifier en sélectionnant « Boutique » et « Climatisation », puis en ajoutant une nouvelle ligne au bas du tableau « Boutique » :
Climatisation 950
Le tableau s’agrandit automatiquement et notre résultat s’adapte à la structure réelle de celui-ci.
Nous devons simplement garder à l’esprit qu’« INDIRECT » est une fonction volatile. Dans un fichier contenant des milliers de formules de ce type, les recalculs peuvent devenir plus lents.
Lorsque nous créons un nouveau fichier, une base unique contenant une colonne « Bâtiment » reste souvent plus simple à analyser avec « SOMME.SI.ENS » ou un tableau croisé dynamique.
En revanche, lorsque les données sont déjà réparties dans plusieurs feuilles identiques, l’association de « SOMME.SI » et « INDIRECT » constitue une solution pratique et rapide à mettre en place.
