Utiliser des références 3D (consolidation)

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 forcément ê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 couleur 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.

Astuce si l'on prend en compte toutes les feuilles

Il suffit de saisir :

= Somme('*'!B2 )

Le caractère * (étoile) sera remplacée par la référence à la plage de feuilles.

Risque de références circulaires

La feuille de synthèse contenant les références 3D ne peut être placée dans la plage de feuilles prise en compte par ces références sous peine de déclencher une erreur de type "Références circulaires".

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

Mise à jour

Un utilisateur m'a demandé de faire une somme d'une plage en référence 3D avec une condition elle-même basée sur une référence 3D.

"Faire la somme des cellules D9 si la cellule E4 contient "u" pour les feuilles Feuil1 à Feuil4".

Malheureusement aucune opération sur les références 3D n'est supportée, par exemple une simple multiplication.

= SOMME( FeuilleDupont : FeuilleMartin ! B5 *2 ) => #REF!

Je lui ai proposé cette formule :

=REDUCE(0;
        {"Feuil1";"Feuil2";"Feuil3";"Feuil4"};
        LAMBDA(c;n;c + SI(NB.SI.ENS(INDIRECT("'"&n&"'!E4");"*u*");INDIRECT("'"&n&"'!D9");0)))

Voir Les fonctions liées à la fonction LAMBDA

Merci pour votre attention bienveillante.