Référence 3D
Une référence 3D pointe vers la même cellule ou plage sur plusieurs feuilles consécutives (ex : =SOMME(Feuil1:Feuil12!B5) pour additionner B5 sur 12 feuilles). Elle est idéale pour consolider des données mensuelles réparties sur plusieurs feuilles identiques. Les feuilles doivent être dans le même classeur.
À quoi sert un référence 3d dans Excel ?
La référence 3D te permet de faire des calculs qui traversent plusieurs feuilles d'un même classeur en une seule formule. Au lieu d'écrire =Feuil1!B5+Feuil2!B5+Feuil3!B5+...+Feuil12!B5, tu écris simplement =SOMME(Feuil1:Feuil12!B5). Excel parcourt automatiquement toutes les feuilles situées entre Feuil1 et Feuil12 et additionne la cellule B5 de chacune.
Tu vas utiliser cette technique chaque fois que tes données sont organisées par période (un onglet par mois, par trimestre ou par année) avec la même structure. C'est le cas classique des reporting financiers, des suivis de production ou des tableaux de bord consolidés. La référence 3D t'évite de maintenir une formule à rallonge qui s'allonge encore à chaque nouvel onglet ajouté.
Référence 3D : exemples concrets
Exemple 1 : Contrôleur de gestion : consolider les charges mensuelles
Tu es contrôleur de gestion dans une entreprise industrielle. Tu as un classeur avec 12 onglets (Jan à Déc), chacun contenant les charges par poste budgétaire, toujours structurés à l'identique. Tu dois créer un onglet de synthèse annuelle qui additionne chaque ligne de charges sur les 12 mois.
Sans référence 3D, tu devrais écrire =Jan!B5+Fév!B5+Mar!B5+...+Déc!B5 pour chaque poste. Avec 50 postes budgétaires, ça fait 50 formules à rallonge. Avec la référence 3D, tu écris =SOMME(Jan:Déc!B5) et tu recopies la formule sur toutes les lignes. Si tu ajoutes un onglet "Jan-bis" entre Jan et Déc, il est automatiquement inclus dans le calcul.
Tu peux aussi utiliser MOYENNE, NB, MIN ou MAX en 3D pour analyser l'évolution de chaque poste au fil des mois, directement dans ton onglet de synthèse.
Exemple 2 : Chef de projet : suivre les heures par équipe sur plusieurs sprints
Tu es chef de projet dans une agence digitale. Chaque sprint a son propre onglet avec le même tableau : une ligne par collaborateur et une colonne "Heures passées" en D. Tu veux un tableau récapitulatif qui totalise les heures de chaque personne sur l'ensemble des sprints.
Tu crées un onglet Bilan et tu écris =SOMME(Sprint1:Sprint8!D3) pour le premier collaborateur. Excel parcourt les 8 onglets et additionne toutes les valeurs en D3. Tu recopies la formule vers le bas pour chaque membre de l'équipe. Si un nouveau sprint est ajouté entre Sprint1 et Sprint8, ses heures seront automatiquement comptabilisées.
Cette approche te permet de produire un suivi d'avancement global en quelques minutes, sans copier-coller de données entre les onglets et sans risque d'oublier un sprint dans le calcul.
- #1 La syntaxe est toujours NomFeuilleDébut:NomFeuilleFin!CelluleOuPlage. Les feuilles doivent être contiguës dans l'ordre des onglets du classeur.
- #2 Tu peux utiliser SOMME, MOYENNE, NB, NBVAL, MIN, MAX et quelques autres fonctions en 3D, mais pas RECHERCHEV ni INDEX.
- #3 Si tu renommes un onglet inclus dans la plage 3D, Excel met à jour la formule automatiquement. Pas besoin de corriger à la main.
Si tu déplaces un onglet en dehors de la plage (par exemple tu glisses "Mars" après "Déc"), il sera exclu du calcul sans message d'erreur. Vérifie toujours l'ordre de tes onglets après un réarrangement.
Les erreurs courantes avec le référence 3d
✕ Ajouter un onglet en dehors de la plage et croire qu'il est inclus
La référence 3D couvre uniquement les feuilles situées physiquement entre la feuille de début et la feuille de fin dans la barre d'onglets. Si tu crées un nouvel onglet "Bonus" mais que tu le places après la feuille de fin (par exemple après Déc), il ne sera pas inclus dans =SOMME(Jan:Déc!B5).
Le piège est que tout semble fonctionner normalement. Excel ne te prévient pas qu'un onglet est exclu du calcul. Tu ne t'en rends compte qu'en vérifiant les totaux manuellement.
Solution : Place toujours les nouveaux onglets entre les feuilles de début et de fin de ta référence 3D en les glissant dans la barre d'onglets. Ou bien mets à jour la formule pour inclure le nouvel onglet dans la plage.
✕ Supprimer un onglet qui sert de borne à la référence 3D
Si tu supprimes un onglet qui se trouve entre la feuille de début et la feuille de fin, Excel ajuste la plage silencieusement. Mais si tu supprimes la feuille de début ou la feuille de fin, la formule peut renvoyer une erreur #REF!.
Ce problème survient souvent quand on fait du ménage dans un classeur en fin d'année. On supprime l'onglet "Jan" pour alléger le fichier, et toutes les formules 3D qui commençaient par Jan: se cassent d'un coup.
Solution : Avant de supprimer un onglet, vérifie s'il est utilisé comme borne d'une référence 3D. Utilise Ctrl+Maj+F (Rechercher dans tout le classeur) pour chercher le nom de l'onglet dans les formules.
Questions fréquentes sur le référence 3d
Commence à taper ta formule (par exemple =SOMME()), puis clique sur l'onglet de la première feuille, maintiens Maj enfoncé et clique sur l'onglet de la dernière feuille. Ensuite, sélectionne la cellule ou la plage voulue. Excel génère automatiquement la syntaxe Feuil1:Feuil12!B5. Tu peux aussi taper la formule directement si tu connais les noms des onglets.
Les fonctions compatibles avec les références 3D sont principalement les fonctions d'agrégation : SOMME, MOYENNE, NB, NBVAL, NB.VIDE, MIN, MAX, ET, OU, ECARTYPE et VAR. Les fonctions de recherche comme RECHERCHEV, INDEX ou EQUIV ne fonctionnent pas en 3D. Si tu as besoin de chercher une valeur sur plusieurs feuilles, tu devras utiliser une approche différente comme INDIRECT combiné avec un nom de feuille variable.
Oui, à condition que le nouvel onglet soit placé entre la feuille de début et la feuille de fin dans la barre d'onglets. Par exemple, si ta formule est =SOMME(Jan:Déc!B5) et que tu insères un onglet "Ajustements" entre Mar et Avr, il sera automatiquement inclus dans le calcul. En revanche, si tu le places après Déc ou avant Jan, il ne sera pas pris en compte.
Termes liés
Envie d'aller plus loin ?
Explore les 293 termes de notre lexique ou découvre nos formules Excel.