Formules de tableaux dynamiques, une nouvelle logique de conception

J’espère que vous avez déjà vu cette article Excel et les matrices (pas celles des cours de mathématiques)

C’est quoi ? (Rappel)

Ces sont des formules qui renvoient une série de valeurs (tableau) et non une seule valeur.
Si le nombre de valeur en résultat dépasse 1, elle va s’étendre sur les autres cellules, on parle de propagation.
Cette propagation se fait seulement si elle a la place suffisante sinon on obtient une erreur #PROPAGATION ! (#SPILL ! en version US, #EPARS ! ancienne versions).

Autres raisons d'erreur de propagation

• Présence de cellules fusionnées sur sa zone de propagation.
• La zone de propagation dépasse les limites de la feuille (ligne 1 048 576, colonne XFD).
• Se trouve dans un tableau structuré.

Oh non ! Ma formule ne se propage pas !

C’est qu’elle ne renvoie qu’un seul résultat.
> Revoir la logique de la formule si ça ne devrait pas être le cas.

Oh non ! Ma formule se propage !

C’est qu’elle renvoie plusieurs résultats.
> Ajouter une fonction de regroupement (agrégation) par exemple SOMME, MOYENNE…
> Ajouter un opérateur d’intersection implicite @ (voir plus bas).

Une image vaut mille mots

D3 : Formule classique (formule de tableaux dynamiques renvoyant 1 résultat). E3 : Formule renvoyant 5 valeurs et s’est propagée automatiquement (dans les anciennes versions d’Excel cela aurait nécessité la sélection d’une plage de 5 cellules et une validation par Ctrl + Maj + Entrer. F3 : Formule avec un opérateur d’intersection implicite @ (voir plus bas). G3 : La fonction SOMME agrège les résultats en une seule valeur, on a la somme des 5 valeurs renvoyées par B2 : B6 * 10 (celles que l’on voit en E3 : E7).

Comment modifier une telle formule ?

Ici la formule en colonne E (image précédente) se trouve seulement en E3, ce sont les résultats qui s’affichent dans les cellules E4 : E7.
Les formules affichées dans la barre de formule pour E4 : E7 sont une simple information.
Elles sont grisées (mode Fantôme 👻) dans la barre de formule pour nous montrer qu’elles ne sont pas réellement dans la cellule.
Si l’on écrit dans une de ces cellules, la formule ne peut plus se propager et affiche donc #PROPAGATION !

Il faut donc modifier la formule en E3.

Et si j’insère ou supprime une cellule ?

Exemple : J’insère une cellule au-dessus de la cellule E5 (E5 devient E6, E6 devient E7) et… il ne se passe rien !
Les valeurs sont propagées en dessous de la cellule E3, sur la plage E4 : E7 et elles le restent dans cette plage.

Comment faire référence à toute la plage de résultat ?

Un problème complexe car ces formules peuvent renvoyer un nombre variable de résultats et donc la plage n’a pas une taille fixe ! On va utiliser l’opérateur # (dièse) pour faire référence à la plage de propagation.

=NB(E3#) ' = 5 

Attention :

• Si la plage se trouve dans un autre classeur, ‘[Classeur]Feuille’!E3# retournera #Ref si le classeur cible est fermé ! Donc éviter de travailler avec ces formules entre classeurs.
• Si le fichier est ouvert dans une ancienne version d’Excel on verra cette formule matricielle {=_xlfn.ANCHORARRAY(E3)} qui affichera #Nom en cas d’actualisation (et fera peut-être planter Excel ☹️).
• L’opérateur # fonctionne seulement avec les matrices (formule de tableaux dynamiques et formules de tableau héritées).

C’est quoi l’intérêts de ces formules ?

• Des formules plus solides : Si un utilisateur modifie une des cellules affichant un résultat, il voit disparaître toute la colonne de calcul et se rend vite compte de son erreur !
• Fini de se demander si une des formules de la colonne de valeurs a été modifié par erreur et est différente des autres.
• Plus de recopie de formule.
  Parfois on ne savait pas où arrêter cette recopie car la plage était variable en nombre de lignes donc on recopiait plus large et on maquait les résultats en trop (souvent en erreur) avec une fonction SI ou une mise en forme conditionnelle.
• Des fichiers plus petits.

Qui a ajouté des arobases (@) à ma formule ?

Je balance (poucave pour faire jeune), c’est Excel le fautif ! Dans certain cas il ajoute l’opérateur d’intersection implicite @ quand on ouvre un ancien fichier.
On est déjà habitué à son utilisation dans les formules structurées.

Ici il est question d’indiquer à Excel d’utiliser l’intersection implicite et non la table dynamique en entier et ainsi forcer la formule à renvoyer qu’une valeur unique.

Intersection implicite c’est quoi ?

C'était le mode d'évaluation des plages par défaut (Implicitly Intersecting Evaluation IIE). Il a été remplacé par l'évaluation en tableau (Array Evaluation AE).

Exemple 1 :

Exemple d'illustration fait sur une ancienne version d'Excel (ne fonctionne plus comme ça maintenant).

On utilise le même formule NBCAR(A2:A6) (Nombre de lettres) mais on obtient des résultats différents en fonction de sa position.

Ceci est dû au fait que l'on a fourni une plage de cellules à NBCAR et non une seule cellule.

Excel va donc utiliser de manière implicite l'intersection entre la plage de la formule et la position de la formule.

Formule :
1 > La formule est en ligne 3, l'intersection avec A2:A6 est donc A3 (Luc).
2 >La formule est en ligne 5, l'intersection avec A2:A6 est donc A5 (Arnaud).
3 > La formule est en ligne 7, l'intersection avec A2:A6 n'existe pas d'où l'erreur (il n'y a pas d'intersection avec la colonne (B) non plus).
4 > La formule est en ligne 9, l'intersection avec A2:A6 n'existe pas. L'intersection avec la colonne A renvoie toute la plage A2:A6 ce qui n'est pas supporté par la fonction NBCAR.

Exemple 2

Une simple somme 😊

Ancienne version

A1:A2 renvoie 2 >2+1=3 >SOMME(3)=3

Nouvelle version

A1:A2 renvoie {1;2} >{1;2}+1={2;3} >SOMME({2;3})=5

Remarque :

• Si on définit le nom PLagePlus1 ainsi A1:A2+1 alors SOMME(PlagePlus1) = 5 !
  Dans le cas d'une plage nommée, Excel évalue la plage et renvoie une matrice à la fonction SOMME.
• De même une validation matricielle (CTRL+MAJ+ENTRER) aurait renvoyée aussi 5.

Exemple 3 :

Formule E3 : Plusieurs résultats (table dynamique) et la formule s’est propagée vers le bas automatiquement.

Formule F3 : 1 résultat. Ici, via le symbole @, on a indiqué à Excel de prendre en compte la position de la formule par rapport à la plage et ainsi de n’utiliser que la valeur correspondante dans la même ligne.
La formule est en ligne 3 donc elle utilise B3 (5 * 10 = 50).
Sur les anciennes versions d’Excel, ce comportement était implicite (fait par défaut) mais maintenant il faut l’indiquer à Excel sinon on a le résultat de la formule E3.

Exemple 4 :

Une portion de formule classique que l’on trouve dans des formules complexes pour générer une suite arithmétique de valeurs comme 0, 1, 2… (souvent dans des fonctions INDEX, DECALER, GRANDE.VALEUR par exemple).
La formule en C2 = LIGNE() - LIGNE($B$2 : $B$6) ne fonctionne plus depuis l’arrivée des formules e cette fonctionnalité !
Il faut ajouter un @ comme dans la formule en E2 = LIGNE() - @LIGNE($B$2 : $B$6).

Remarques :
Et pourquoi si compliqué et pas simplement :
= LIGNE(A1) avec la recopie vers le bas.
Ou
= LIGNE(A1:A5) avec la propagation qui semblent faire le job !

Avec ces 2 formules, si l’utilisateur ajoute des lignes au-dessus, la série ne commencera plus à 1 ce qui n’est pas le cas de la formule de l’exemple !
Cependant, sur les versions récentes d’Excel, on a maintenant la fonction SEQUENCE qui fait ça très bien.

Et le @ avec les anciennes version pré dynamique d’Excel ?

Si l’on ouvre un ancien fichier Excel, les @ seront automatiquement ajoutés aux formules le nécessitant.
Si on ajoute des @ à notre formule, dans certain cas Excel affichera une alerte et proposera une correction de la formule pour la rendre compatibles avec les anciennes versions.

Si l’on refuse, on aura une formule mixte (au niveau de l’intersection implicite) et ne sera pas compatible.

Dans les anciennes versions, elle sera en erreur #NOM ! et sa syntaxe sera :
= B2 : B4 + _xlfn. SIMPLE()

Est-ce qu'elles fonctionnent avec les règles de validation et la mise en forme conditionnelle ?

Même limitations que celle des formules matricielles.

Elles sont autorisées avec ces outils mais ne peuvent pas être utilisées simplement sous forme de matrice mais avec dans des formules (agrégation, EQUIV…) ne renvoyant qu’un résultat.

Impossible

Fonctionne seulement avec la première valeur 20 (10*2) !

Possible

C’est la formule EQUIV qui travaille avec la matrice et revoie une valeur (le ESTNUM est une astuce permettant de renvoyer VRAI si la valeur est trouvée et FAUX dans le cas contraire, l’inverse de ce que fait ESTNA).

La règle de validation n’a donc qu’une valeur à évaluer.

De même on ne peut les utiliser directement avec l’option Liste mais via l’intermédiaire d’une plage dynamique avec l’opérateur #.

C'est pour toutes les fonctions d'Excel ?

Certaines fonctions ne renvoient pas de table dynamique comme SOMME, MAX, MOYENNE...

Il semble qu'Excel génère un nom (masqué) quand on utilise une de ces fonctions (noms visibles en VBA).

Sub ListeDesNom()
    Dim objNom As Name
    Dim objFeuille As Worksheet
    
    Debug.Print "== Noms Portée/Étendue/Zone Classeur =="
    For Each objNom In ThisWorkbook.Names
        Debug.Print "  " & objNom.Name
    Next
    Debug.Print "== Nom Portée/Étendue/Zone Feuille =="
    For Each objFeuille In Worksheets
        Debug.Print "  -- Feuille : " & objFeuille.Name & "(" & objFeuille.CodeName & ") --"
        For Each objNom In objFeuille.Names
            Debug.Print "     " & objNom.Name
        Next
    Next
End Sub

Feuil2!_FilterDatabase        <= Filtre automatique
_xlfn.FORMULATEXT / _xlfn.LET <= Fonctions dynamiques
_xlpm.Rayon                   <= Nom définie dans LET
Résultat                      <= Nom classique


Feuil2!_FilterDatabase        <= Filtre automatique
Feuil2!ValFeuil2              <= Nom classique

Remarque :

• La fonction SOMME n'est pas listée.
• La fonction FORMULETEXTE utilisé 2 fois n'est présente qu'une fois.

Infos, trucs et astuces diverses

Qui vont (je l'espère) augmenter au fils du temps.

Certaines fonctions n'aiment pas les références aux plages avec l'opérateur #

Je veux générer une liste de dates décalées de la date d'aujourd'hui de 1, 2 et 3 mois.

La formule = MOIS.DECALER( AUJOURDHUI() ; SEQUENCE(3) ) fonctionne parfaitement.

On va externaliser la fonction SEQUENCE de cette fonction et donc aller chercher le résultat de SEQUENCE via un une référence à une plage dynamique (#).

La formule se met en erreur #VALEUR! . Ça me rappel un problème identique avec les références structurées.

On tente le même remède que pour les références structurées.

On va forcer Excel à convertir ma référence avant de l'utiliser dans la formule.

Simplement en ajoutant un + (plus) devant la référence ! 🤔

Merci pour votre attention bienveillante.