[TITRE_MINIATURE]12 FEUILLES, 1 FORMULE[/TITRE_MINIATURE]
Dans ce tutoriel, je vais vous montrer comment additionner automatiquement les données de plusieurs feuilles Excel avec une seule formule, afin de créer une synthèse annuelle qui se met à jour sans multiplier les additions manuelles.
Lorsque nous travaillons avec une feuille par mois, par agence ou par service, nous finissons souvent avec des formules longues du type « Janvier plus Février plus Mars ».
Elles sont difficiles à relire et deviennent vite sources d’erreurs.
Nous allons remplacer tout cela par une référence 3D, c’est-à-dire une référence capable de viser la même cellule dans plusieurs feuilles à la fois.
À la fin, nous verrons également comment utiliser deux feuilles de délimitation pour que les nouveaux mois soient automatiquement intégrés dans tous nos calculs.
Téléchargement
Tutoriel Vidéo
1. Présentation
Pour illustrer ce tutoriel, nous allons pouvoir utiliser le fichier Excel suivant dans lequel nous retrouvons les dépenses mensuelles d’une association.
Ce classeur contient une feuille par mois : « Janvier », « Février », « Mars », « Avril », « Mai » et « Juin », ainsi qu’une feuille nommée « Synthèse » :
Dans chaque feuille mensuelle, nous devons impérativement utiliser la même organisation, le loyer doit, par exemple, toujours se trouver sur la même ligne et le montant toujours dans la même colonne, car Excel ne va pas rechercher le texte « Électricité ». Il va simplement récupérer, par exemple, la cellule B8 dans chacune des feuilles.
Les références 3D ciblent principalement des adresses de cellules identiques dans plusieurs feuilles.
Nous devons également vérifier l’ordre des onglets. Ils doivent apparaître dans cet ordre : « Synthèse », « Janvier », « Février », « Mars », « Avril », « Mai » et « Juin ».
Notre base est maintenant prête. Nous allons pouvoir demander à Excel de parcourir plusieurs feuilles sans écrire le nom de chaque mois séparément.
2. Comprendre et créer une référence 3D
Une référence Excel classique vise une cellule ou une plage située dans une seule feuille.
Depuis la feuille « Synthèse », si nous souhaitons récupérer le loyer de janvier, nous tapons le signe « = », puis nous nous rendons dans la feuille janvier pour cliquer sur la cellule B7.
Excel insère alors la formule suivante :
=Janvier!B7
Le point d’exclamation sépare le nom de la feuille de l’adresse de la cellule.
Pour additionner janvier, février et mars manuellement, nous pourrions écrire :
=Janvier!B7+Février!B7+Mars!B7
Cette méthode fonctionne, mais elle oblige à ajouter chaque feuille dans la formule.
Avec six mois et plusieurs catégories, le fichier devient inutilement compliqué.
Une référence 3D permet de définir une plage de feuilles de la même manière qu’une plage de cellules.
Dans « A1:A10 », les deux-points signifient « de A1 jusqu’à A10 ».
Dans « Janvier:Juin », ils signifient « de la feuille Janvier jusqu’à la feuille Juin ».
Dans la feuille « Synthèse », nous saisissons en B7 et nous saisissons :
=SOMME(Janvier:Juin!B7)
Cette formule demande à Excel d’additionner la cellule B7 de toutes les feuilles comprises entre « Janvier » et « Juin ».
Comme le loyer est de 850 euros pendant six mois, le résultat est égal à 5 100 euros.
Nous pouvons également construire la formule sans saisir les noms des feuilles. Nous écrivons d’abord :
=SOMME(
Nous cliquons sur l’onglet « Janvier », puis sur la cellule B7. Nous maintenons ensuite la touche [Maj] et nous cliquons sur l’onglet « Juin ».
Excel sélectionne toutes les feuilles intermédiaires et génère automatiquement la référence 3D. Nous fermons la parenthèse, puis nous appuyons sur [Entrée].
Cette méthode est particulièrement utile lorsque les noms des feuilles contiennent des espaces. Excel ajoute alors automatiquement les apostrophes nécessaires.
Par exemple, avec des feuilles nommées « Mois 01 » et « Mois 06 », la formule devient :
=SOMME('Mois 01:Mois 06'!B7)
Nous revenons dans la feuille « Synthèse », puis nous recopions la formule de B7 jusqu’à B13 à l’aide de la poignée de recopie.
Excel adapte automatiquement l’adresse de la cellule. Pour l’électricité, la formule devient :
=SOMME(Janvier:Juin!B8)
Pour les événements, elle devient :
=SOMME(Janvier:Juin!B13)
Nous obtenons ainsi tous les totaux sans devoir modifier manuellement les noms des feuilles.
3. Construire une synthèse complète
Une référence 3D peut être utilisée avec plusieurs fonctions de calcul, et pas uniquement avec SOMME.
Dans la cellule C7, nous calculons la moyenne mensuelle du loyer :
=MOYENNE(Janvier:Juin!B7)
La fonction MOYENNE additionne les six valeurs, puis divise le résultat par le nombre de cellules numériques. Nous recopions cette formule jusqu’à C13.
Dans la cellule D7, nous recherchons le montant mensuel le plus faible :
=MIN(Janvier:Juin!B7)
Dans la cellule E7, nous recherchons le montant le plus élevé :
=MAX(Janvier:Juin!B7)
Nous recopions également ces deux formules vers le bas.
Pour les événements, Excel compare les montants de toutes les feuilles. La fonction MIN retourne 350 euros, tandis que MAX retourne 760 euros.
Ces fonctions indiquent la valeur minimale ou maximale, mais pas le nom de la feuille dans laquelle elle apparaît. Les références 3D sont surtout destinées aux calculs de consolidation.
Nous pouvons maintenant calculer le total général en B14 avec une formule classique basée sur notre synthèse :
=SOMME(B7:B13)
Il existe cependant une autre possibilité. Nous pouvons demander à Excel d’additionner directement une plage entière dans toutes les feuilles :
=SOMME(Janvier:Juin!B7:B13)
La première plage, « Janvier:Juin », désigne les feuilles. La seconde, « B7:B13 », désigne les cellules à additionner dans chaque feuille.
Cette formule additionne donc 42 cellules : sept catégories multipliées par six mois.
Pour contrôler que chaque montant a bien été saisi, nous pouvons utiliser la fonction NB :
=NB(Janvier:Juin!B7:B13)
Le résultat attendu est 42. Un résultat inférieur indique qu’au moins une cellule est vide ou ne contient pas une véritable valeur numérique.
Nous pouvons aussi contrôler chaque catégorie. Dans une colonne F intitulée « Contrôle », nous saisissons en F7 :
=SI(NB(Janvier:Juin!B7)=6;"Complet";"À vérifier")
Nous recopions ensuite la formule jusqu’à F13.
Lorsque six nombres sont détectés, Excel affiche « Complet ». Dans le cas contraire, il affiche « À vérifier ».
4. Ajouter de nouveaux mois sans modifier les formules
La formule suivante utilise toutes les feuilles physiquement placées entre « Janvier » et « Juin » :
=SOMME(Janvier:Juin!B7)
L’ordre des onglets est donc déterminant.
Si nous déplaçons « Mars » après « Juin », cette feuille sort de la plage et ses valeurs ne sont plus additionnées. Excel ne signale pas d’erreur : le total change simplement.
À l’inverse, si nous insérons une feuille « Juin » entre « Mars » et « Avril », sa cellule B7 sera intégrée au calcul. Si elle contient un nombre, celui-ci faussera le résultat.
Une première solution consiste à remplacer « Juin » par « Juillet » lorsque nous ajoutons un nouveau mois :
=SOMME(Janvier:Juillet!B7)
Cette méthode reste simple, mais elle oblige à modifier toutes les formules de la synthèse.
Pour rendre le classeur plus souple, nous allons créer deux feuilles vides nommées « Début » et « Fin ».
Nous plaçons les feuilles dans cet ordre : « Synthèse », « Début », « Janvier », « Février », « Mars », « Avril », « Mai », « Juin », puis « Fin ».
Nous remplaçons ensuite notre formule par :
=SOMME(Début:Fin!B7)
Les cellules B7 des feuilles « Début » et « Fin » étant vides, elles ne modifient pas le résultat.
Lorsque nous créons la feuille « Juillet », nous devons simplement l’insérer entre « Juin » et « Fin ». Elle est alors automatiquement intégrée à toutes les formules utilisant la plage « Début:Fin ».
Pour éviter les erreurs, nous pouvons attribuer une couleur particulière aux onglets « Début » et « Fin ».
Nous effectuons un clic droit sur l’onglet, nous sélectionnons « Couleur d’onglet », puis nous choisissons une couleur facilement identifiable.
Nous devons également éviter de renommer ou de supprimer ces deux feuilles, car elles servent de limites à toutes nos formules.
Enfin, toutes les fonctions Excel n’acceptent pas les références 3D : SOMME, MOYENNE, MIN, MAX et NB sont parfaitement adaptées, mais des fonctions comme SOMME.SI.ENS, FILTRE ou RECHERCHEX ne peuvent pas parcourir directement plusieurs feuilles de cette manière.
Lorsque les feuilles n’utilisent pas la même structure, il vaut mieux regrouper les données dans une seule base, éventuellement avec Power Query, puis effectuer les calculs sur cette base consolidée.
Les références 3D restent néanmoins la méthode la plus directe lorsque nous disposons d’une feuille par mois, par magasin ou par service et que toutes les feuilles suivent le même modèle.
Grâce à elles, nous remplaçons une succession de références difficiles à maintenir par une seule formule claire, courte et immédiatement actualisée.
