0
(0)

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.

Excel Illustration référence 3D
= 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).
Excel Ref 3D Bornes dédiées
  • 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.

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

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.

Article intéressant ?

Cliquez sur une étoile pour noter cet article !

Note moyenne 0 / 5. Nombre de votes : 0

Aucun vote pour l'instant ! Soyez le premier à noter ce post.

Nous sommes désolés que cet article ne vous ait pas été utile !

Améliorons cet article !

Dites nous comment nous pouvons améliorer cet article ?

Publications similaires

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *