Tout le monde (ou presque) sait que
- Si je glisse un onglet (le déplace) à une autre position en restant dans le même classeur, cela ne peut pas changer les résultats de formules placées dans d'autres feuilles.
- Si j'ajoute une NOUVELLE feuille et y saisie des informations/données, cela ne peut pas changer les résultats de formules placées dans d'autres feuilles.
Et si ce n'était pas vrai !
Découvrons cette fonctionnalité peu connue, très puissante mais très dangereuse et très limitée.
Qu'est-ce qu'une référence 3D ?
Petits rappels sur les références "classiques" dite 2D
- La référence B5 donne l'abscisse (ici la colonne B) et l'ordonné (ici la ligne 5).
- La référence B5 : C8 donne les coordonnés de 2 cellules permettant de caractériser une plage de 8 cellules.
On pourrait traduire le 2 points ( : ) par jusqu'à (ici de B5 jusqu'à C8). - La référence Dupont!B5 ou 'Du pont'!B5 donne le nom de la feuille (entre apostrophe s'il y a des espaces (ici Dupont ou Du pont).
Le point d'exclamation ( ! ) servant de séparateur.
On travaille donc normalement en 2 dimensions (2 coordonnés : coordonné Colonne et coordonné Ligne).
La syntaxe des références 3D
On indiquer à Excel que le plage/cellule va de telle feuille jusqu'à telle feuille.
FeuilleDupont : FeuilleMartin ! B5 ou FeuilleDupont : FeuilleMartin ! B5 : B8.
On travaille ainsi sur une pile/superposition de plage/cellule marqué par 2 feuilles "frontières", d'où la 3eme dimension.

= SOMME( FeuilleDupont : FeuilleMartin ! B5 )
Intérêts de ce type de références
Tout comme quand on insère une cellule dans une plage, les formules se mettent à jour, si l'on insère ou supprime une feuille entre les 2 feuilles "frontières" marquant les limites de la référence 3D, cette modification impactera les résultats des formules utilisant la référence 3D.
On voit tout de suite l'intérêt :
- Un nouveau participant : On glisse/ajoute sa "fiche/feuille" entre les 2 feuilles "frontières", les formules sont mises à jour.
- Un participant se retire : On glisse/supprime sa "fiche/feuille" hors des 2 feuilles "frontières", les formules sont mises à jour.
Inconvénients
- On ne peut déplacer/supprimer les feuilles "frontières".
- Les feuilles doivent se suivre forcément (feuilles consécutives).
- La structure des feuilles doit être la même (ici la "donnée" doit forcement être en B5 pour toutes les feuilles).
- Toutes fonctions ne sont pas compatibles.
- Fonctionnalité peu connue donc les utilisateurs non avertis risquent de faire des "bêtises" (ajout d'une feuille non pertinente dans la plage de feuille par exemple).
Astuces
- Utiliser des feuilles/onglets "frontières" dédiés à ce rôle (exemple : DebAd et FinAd) permettant ainsi de déplacer toutes les feuilles (ici les participants).

- Alerter l'utilisateur en changeant la couleurs des onglets, prévenir l'utilisateur via un fiche d'instruction dans une feuille.
- Interdire l'Ajout / Suppression / Déplacement de feuille via le ruban Révision > Protéger le classeur (groupe Protéger).
Création facile de ces références
- Cliquer sur l’onglet de la première feuille de calcul à référencer.
- Maintenir la touche Maj du clavier enfoncée et cliquer sur l’onglet de la dernière feuille de calcul à référencer.
- Sélectionner la cellule ou la plage de cellules à référencer.
Liste des fonctions prenant en charge ce type de références
- SOMME
- MOYENNE
- NB
- NBVAL
- MAX
- MIN
- PRODUIT
- MEDIANE
- AVERAGEA
- ECART.MOYEN
- GRANDE.VALEUR
- PETITE.VALEUR
- EQUATION.RANG
- RANG (obsolète)
- FREQUENCE
- MOYENNE.GEOMETRIQUE
- CONCAT
- JOINDRE.TEXTE
- ECARTYPE.STANDARD
- ECARTYPE.PEARSON
- STDEVA
- STDEVPA
- ECARTYPE (obsolète)
- ECARTYPEP (obsolète)
- VAR.P.N
- VAR.N
- VAR.S
- VARA
- VARPA
- VAR (obsolète)
- VAR.P (obsolète)
- LET
- ASSEMB.H
- ASSEMB.V
- DANSCOL
- DANSLIGNE
- CENTILE (obsolète)
- CENTILE.EXCLURE
- CENTILE.INCLURE
- COEFFICIENT.ASYMETRIE
- COEFFICIENT.ASYMETRIE.P
- KURTOSIS
- MAXA
- MINA
- MOYENNE.HARMONIQUE
- MOYENNE.RANG
- MOYENNE.REDUITE
- QUARTILE (obsolète)
- QUARTILE.EXCLURE
- QUARTILE.INCLURE
- RANG.POURCENTAGE.EXCLURE
- RANG.POURCENTAGE.INCLURE
- SOMME.CARRES
- SOMME.CARRES.ECARTS
- TEST.Z (obsolète)
- Z.TEST
Merci pour votre attention bienveillante.