Utiliser des références 3D

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

1. Cliquer sur l’onglet de la première feuille de calcul à référencer.
2. Maintenir la touche Maj du clavier enfoncée et cliquer sur l’onglet de la dernière feuille de calcul à référencer.
3. Sélectionner la cellule ou la plage de cellules à référencer.

Liste des fonctions prenant en charge ce type de références

Merci pour votre attention bienveillante.