Excel et les matrices (pas celles du cours de mathématiques)

Pourquoi cet article ?

Excel nous offre de nouvelles fonctions très puissantes que j’aimerais présenter mais pour les utiliser il faut une bonne compréhension de la notion de matrice d’où cet article.
De plus cette notion est devenue incontournable pour comprendre comment Excel interprète toutes vos formules depuis 2019 !

Pourquoi 2019 ?

Avant cette année, il fallait dire à Excel « travail en matricielle » quand on en avait besoin en validant nos formules, non pas avec la touche ENTRER mais avec la combinaison de touche CTRL+MAJ+ENTRER mais depuis cette date c’est inutile puisque qu’Excel travail par défaut de cette façon !

« Je n’ai rien remarqué en faisant mes formules donc ce n’est pas important !», c’est pourtant une petite révolution dans la manière de concevoir ses formules et propulse les utilisateurs à un niveau d’utilisation que seul les « pro » d’Excel atteignaient avec justement ces formules matricielles.

Mais c’est quoi une matrice ?

C’est un tableau/grille de valeurs mais non affiché.

• Les matrices Excel ont 2 dimensions (Ligne et Colonne) même si ce n'est qu'une ligne ou une colonne de valeurs

Des fonctions "anciennes" renvoient comme résultat des matrices (FREQUENCE, MATRICE.UNITAIRE).

De nombreuses nouvelles fonction le font (UNIQUE, SEQUENCE, FILTRE, TRIER, TRIERPAR...).

Une plage n'est pas une matrice mais peut-être "convertie" en matrice (et inversement).

Et une formule matricielle ?

Une formule qui utilise une matrice ou une formule qui renvoie une matrice comme résultat (plusieurs valeurs simultanément, on ne parle pas ici de recopie de formule).

Ça sert à quoi un tableau que l’on ne voit pas ?

Le plus souvent, d’éviter d’afficher de nombreux calcules intermédiaires. Une image vaut plus que mille mots (Confucius) donc quelques exemples.

Exemple 1 : Calculer la recette globale directement

(Réalisé avec une ancienne version d’Excel)

Mes ventes de la journée, comment calculer la recette du jour ? Testons 4 méthodes :

• Recette 1 (D6) : Solution non réaliste pour un grand tableau car trop manuelle.
• Recette 2 (D7) : On a dû créer les 3 calculs intermédiaires (D2, D3, D4).
• Recette 3 (D8 : Validation normale par ENTRER <=> D9 : Validation avec la combinaison de touches CTRL+MAJ+ENTRER) : La solution matricielle.

Décomposons le travail d’Excel avec l’outil Evaluer la formule (Ruban Formules).

Excel fait bien les 3 calculs de montant (50, 300, 2000) puis en fait la somme.
Remarquer les accolades générer par la validation matricielle CTRL+MAJ+ENTRER (ça ne marchera pas si c’est vous qui les saisissez au clavier directement !).

Exemple 2 : Calculer la recette globale directement s’il y a livraison et que la quantité soit au moins de 3.

Les colonnes E, F, G, H, I sont là pour décomposer ce que va faire la formule matricielle.
> Colonne E : Livraison.
> Colonne F : Qt >=3.
> Colonne G : On génère un ET logique.

Rappel :

• VRAI+0  = 1 donc pour Excel VRAI = 1
• FAUX+0 = 0 donc pour Excel FAUX = 0
  1*1 = 1 les autres : 0*1, et 1*0 et 0*0 = 0

> Colonne H : Calculs des montants.
> Colonne I : On met à zéro les montants ne correspondant pas à notre recherche.
> Cellule I8 : Somme de la colonne I.

> Cellule I9 : la même chose directement (les colonnes de calculs précédemment explicités sont générées en mémoire mais pas affichées).

Remarque : C’était la méthode utilisée avant l’arrivée de la fonction SOMME.SI.ENS pour faire ce type de calcul (Microsoft avait même développé un assistant pour aider les utilisateurs).

Exemple 3 : Renvoyer le top3 des valeurs et déterminer la droite de régression correspondante

Pour illustrer les fonctions/formule renvoyant plusieurs résultats.

Lors de la validation de la formule, on doit avoir préalablement sélectionner la plage où les valeurs renvoyées vont s’inscrire, dont savoir à l’avance le nombre de résultats ce qui n’est pas toujours évident !
Pour la formule en E3 (Top3), on savait que l’on attendait 3 résultats donc on a sélectionné la plage E3 : E5.
Pour la formule en C9 (Droite de régression), on ne sait pas combien de colonne on va avoir (ici on en a sélectionné une de trop d’où les #N/A).

Les constantes matricielles

Ce sont des matrices saisies manuellement et pouvant être utilisées directement dans les formules.

On a des constantes à 1 dimension, en vertical (en colonne) ou en horizontal (en ligne) et des constantes à 2 dimensions (en ligne et en colonne).

• Les lignes sont indiquées par des point virgules ( ; ) et les colonnes par des point ( . ).
• Pour les constantes à 2 dimensions :
  - On déclare pour chaque ligne les valeurs des colonnes (l’inverse ne fonctionne pas !).
  - Chaque ligne doit avoir le même nombre de valeurs : {1.2.3 ; 10.20 } n'est pas correcte (1 ligne à 3 valeur, 1 ligne à 2 valeurs)

Manipulation de constantes matricielles

C'est la même logique pour toutes les types de matrices.

Avec une valeur

La valeur est ajoutée/multipliée à chaque élément de la matrice.

Entre constantes matricielles

• Exemple 1 & 2 : Les valeurs sont ajoutées/multipliées par pair de valeurs (ici pour le 1er exemple : 1 + 10, 4 + 100, 3 + 20).
• Exemple 3 : Les matrices de même sens (en ligne/colonne) doivent avoir la même taille sinon les valeurs manquantes génèreront des #N/A (ici on aura 3 + #N/A => #N/A).
• Exemple 4 : Si les matrices ne sont pas dans le même sens, Excel génère une matrice ayant comme dimension la combinaison des 2 dimensions (ici on a comme taille : 3X1 et 1X2, on a donc une matrice 3X2 en résultat).

Excel va dimensionner toutes les matrices à la même taille puis va recopier les valeurs d'origines (Broadcasting) et enfin combiner les valeurs élément par élément.

Une astuce pour dupliquer une colonne/ligne est de la multiplier avec une constante constituée de 1.

Les 2 modes d'évaluation d'Excel

Le mode IIE (Implicitly Intersecting Evaluation)

• C'est le mode qu'Excel utilisait par défaut avant 2019.
• C'est le mode que l'on peut forcer avec l'opérateur @ (voir Formules de tableaux dynamiques, une nouvelle logique de conception)

Exemple

Le mode AE (Array Evaluation)

• C'est le mode qu'Excel utilise maintenant par défaut (voir plus bas Et donc maintenant ?).
• C'est le mode que l'on active en validant en mode matriciel (CSE CTRL+MAJ+ENTRER).
• C'est le mode utilisé depuis toujours par les formules avec les règles de validations et les mises en forme conditionnelles (les formules évaluent/utilisent les matrices mais le résultat final doit forcément être une valeur unique, pas une matrice).
• C'est le mode utilisé pour les formules liées à un Nom.

Pour résumer, avant seules les cellules n'étaient pas évaluées en matrice, maintenant tout l'est ce qui est plus cohérent.

Quelques petites expériences

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

Quelle compatibilité entre les fonctions et les matrices ?

Il n'y a pas de règles ni d'informations disponibles à ce sujet. À vous de tester !

Dans une même fonction, certains arguments peuvent être des matrices et d'autres non.

Quelques fonctions en exemples

• Scalaire : Valeur unique, pas valeur matricielle.
• Mixte : Valeur unique/Scalaire ou valeur matricielle.

Fonctions	Arguments
SOMME, MOYENNE, MAX, MIN, MEDIANE	Nombre1 [ ; Nombre2 à 255]
COS, SIN, TAN, ACOS...	Nombre
RECHERCHEV, RECHERCHEH	Valeur_cherché ; Table_matrice ; No_index_col/lig  [ ; Valeur_proche]
INDEX	Matrice ; No_lig [ ; No_col]
EQUIV	Valeur_cherchée ; Tableau_recherche [ ; Type]
NB.SI, NB.SI.ENS	Plage/Plage_critères1 à 127 ; Critère/Critères1 à 127
SOMME.SI	Plage ; Critère ; Somme_plage
SOMME.SI.ENS	Somme_plage ; Plage_critères1 à 127 ; Critères1 à 127
BDSOMME, BDMOYENNE, BDMAX...	Base de données ; Champs  ; Critères

Illustration avec RECHERCHEV

No_index_col avec la matrice { 2 . 4 } permet de récupérer le Nom et l'Age en 1 formule (la matrice { 2 ; 4 } aurait présenté les résultats en colonne).

Et donc maintenant ?

Valider par CTRL+MAJ+ENTER n’a plus d’utilité !

Ces anciennes formules, appelées formules de tableaux héritées sont toujours prises en charge pour des raisons de compatibilité, mais ne doivent plus être utilisées.
Maintenant, on valide simplement par ENTRER (Avez-vous remarqué que l’exemple 2 n’a pas d’accolades car réalisé avec une version récente d’Excel).
Ces nouvelles formules s’appellent formules matricielles dynamiques.

Pourquoi dynamique ?
Il n’y a plus besoin d’anticiper la taille de la matrice renvoyée (comme dans l’exemple 3).
La formule va se propager automatiquement sur les cellules vides en dessous ou/et à droite pour afficher la totalité des résultats renvoyés par la formule.
On a l’erreur #PROPAGATON ! si elle est bloquée/gênée par la présence d’une saisie dans une des cellules devant être utilisée pour l’affichage des résultats de la formule.
(#SPILL ! en anglais et #EPARS ! sur les 1ere versions françaises).

Remarquer le pointillé bleu indiquant la zone nécessaire à la propagation de la formule.

Comme Excel n'évalue pas les formules de la même manière, on peut avoir des résultats différents dans certain cas entre les versions d'Excel avant et après les formules matricielles dynamique !

Différences de résultat entre versions pré/post dynamiques d'Excel.

Remarque :

Dans les anciennes versions (et aussi les nouvelles), si on définit le nom PLagePlus1 ainsi A1:A2+1 alors SOMME(PlagePlus1) = 5 !

Pour savoir pourquoi je vous invite à consulter Formules de tableaux dynamiques, une nouvelle logique de conception - Bienvenue à 1forme.fr

Merci pour votre attention bienveillante.