C'est quoi une référence 3D Excel ?
Une référence 3D vise la même cellule ou plage sur plusieurs feuilles consécutives d'un même classeur, sous la forme =SOMME(Janvier:Décembre!B2). Les deux feuilles bornes se séparent par un deux-points, et le calcul embrasse toutes les feuilles rangées entre elles dans la barre d'onglets. Elle suppose des onglets au même agencement et n'a aucun sens d'un classeur à l'autre.
À quoi sert une référence 3D dans Excel
Une référence 3D fait tenir en une seule formule un calcul qui traverse plusieurs feuilles d'un même classeur. Plutôt que d'écrire =Jan!B2+Fév!B2+Mar!B2 et de rallonger la formule à chaque onglet, tu bornes la série avec =SOMME(Jan:Mar!B2), et Excel additionne la cellule B2 de chaque feuille comprise entre les deux.
Le vrai gain arrive quand tes données suivent un modèle répété, un onglet par mois, par magasin ou par équipe, tous bâtis sur le même quadrillage. La cellule B2 y désigne partout la même chose, donc une référence 3D consolide les douze mois d'un coup et se recopie ensuite sur chaque ligne de ta feuille de synthèse.
C'est là son terrain, et aussi sa limite : les feuilles doivent se suivre dans la barre d'onglets et partager la même disposition. Dès que les onglets se dispersent, ou que la cellule visée ne désigne plus la même chose d'une feuille à l'autre, le raccourci ne tient plus.
Comment créer une référence 3D dans Excel
Une référence 3D additionne la même cellule sur plusieurs feuilles consécutives d'un classeur. Range tes onglets mensuels dans l'ordre et donne-leur une structure identique, puis laisse Excel écrire la syntaxe pendant que tu cliques.
- 1Tape le début de ta formule, par exemple =SOMME(, dans la cellule de synthèse.
- 2Clique sur l'onglet de la première feuille à inclure, par exemple Jan.
- 3Maintiens la touche Maj enfoncée et clique sur l'onglet de la dernière feuille, par exemple Déc, pour sélectionner toutes les feuilles intermédiaires.
- 4Clique sur la cellule ou la plage voulue, par exemple B2, sur la feuille active.
- 5Ferme la parenthèse et valide avec Entrée. Excel affiche alors =SOMME(Jan:Déc!B2), et le même geste marche avec MOYENNE, NB ou MAX.
=SOMME(Début:Fin!B2). Tout onglet glissé entre ces deux repères rejoint le calcul, sans que tu retouches jamais la formule.
Quelles fonctions acceptent une référence 3D
Une référence 3D ne marche qu'avec les fonctions qui résument une plage, pas avec celles qui la fouillent. Du côté qui passe, tu retrouves SOMME, MOYENNE, MAX, MIN, NB, NBVAL et PRODUIT, ainsi que les fonctions statistiques comme ECARTYPE et VAR. Chacune traite la série de feuilles comme un seul bloc et en sort un chiffre unique.
SOMME.SI, NB.SI et RECHERCHEV, en revanche, refusent la syntaxe 3D et renvoient #VALEUR! dès que tu bornes deux feuilles. Ces fonctions ont besoin de colonnes alignées côte à côte sur une même feuille, ce qu'une plage éclatée sur douze onglets ne leur donne pas.
Pour additionner sous condition ou retrouver une valeur à travers les onglets, il te faut donc un autre montage. Une consolidation qui rassemble d'abord les feuilles, ou un SOMME.SI répété feuille par feuille, fait alors le travail que la 3D refuse.
Pourquoi insérer ou déplacer une feuille modifie une référence 3D
Une référence 3D ne mémorise pas la liste des feuilles, seulement les deux noms qui la bornent et la position des onglets entre eux. Jan:Mar désigne donc tout ce qui se trouve, au moment du calcul, entre l'onglet Jan et l'onglet Mar, ces deux-là compris.
Cette règle a un bon côté. Glisse un nouvel onglet entre Jan et Mar et il rejoint le total tout seul, sans que tu touches à la formule, ce qui garde ta consolidation vivante à mesure que le classeur grandit. Ajoute un onglet de correctifs en cours d'année et son montant remonte aussitôt dans la synthèse.
Le revers est plus sournois. Sors une feuille de la fourchette, par exemple en tirant Février après Mars, et elle quitte le total sans le moindre avertissement. La formule affiche toujours =SOMME(Jan:Mar!B2), mais le résultat, lui, a baissé, et rien à l'écran ne dit pourquoi.
=SOMME(Jan:Mar!B2) devient =SOMME(Janvier:Mar!B2) sans que le résultat bouge. La référence suit le nom de la feuille, pas une position figée.
Pour aller plus loin
Questions fréquentes sur la référence 3D
Utilise la même syntaxe 3D qu'avec une somme, sous la forme =MOYENNE(Jan:Déc!B2). Excel prend la cellule B2 de chaque feuille comprise entre Jan et Déc et en calcule la moyenne, comme si ces douze valeurs vivaient dans une seule colonne. MIN, MAX, NB et NBVAL s'écrivent exactement de la même façon. Attention, une feuille dont la cellule B2 est vide ne compte pas dans la moyenne, ce qui fausse le résultat si tu comptais bien diviser par douze.
Non, une référence 3D reste enfermée dans un seul classeur. Elle empile des feuilles voisines du même fichier, alors que viser un autre classeur relève de la référence externe, qui cite le nom du fichier source entre crochets comme [Budget]Jan!B2. Tu ne peux pas fondre les deux dans une même expression 3D. Pour rassembler des données réparties sur plusieurs fichiers, passe plutôt par l'outil Consolider ou par une requête qui réunit les classeurs avant le calcul.
La référence 3D ne gère pas les conditions, donc =SOMME.SI(Jan:Déc!A2:A50;"Loyer";Jan:Déc!B2:B50) renvoie l'erreur #VALEUR!. Le contournement le plus direct additionne un SOMME.SI par feuille, comme =SOMME.SI(Jan!A:A;"Loyer";Jan!B:B)+SOMME.SI(Fév!A:A;"Loyer";Fév!B:B), et ainsi de suite. Sur beaucoup d'onglets, mieux vaut regrouper les données dans un seul tableau avec une colonne Mois, puis lancer un SOMME.SI.ENS ou un tableau croisé dynamique. Tu récupères alors le filtre par condition que la 3D ne sait pas appliquer.
Du mot à la pratique
Tu sais maintenant ce que le mot veut dire. Reste à s’en servir : la formule, le fichier, et le cours qui remet tout dans l’ordre.
Tout le lexiqueDes modèles Excel prêts à télécharger
Toutes les formules Excel, expliquées
Tout le cours Excel en PDF
