0
(0)

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
Excel_Matrice
Exemples de matrices

Des fonctions "anciennes" renvoient déjà comme résultat des matrices (FREQUENCE, MATRICE.UNITAIRE, PRODUITMAT, MATRICE.UNITAIRE, INVERSEMAT, TRANSPOSE, MODE.MULTIPLE, TENDANCE, DROITEREG...).

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)

Excel : Matrice
Excel : Calculer la recette des ventes avec une matrice

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 : Matrice
Excel : Matrice

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.

Excel : Matrice

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.

Excel : Matrice

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.

Excel : Matrice - Constante matricielle
Différentes formes de constantes matricielles

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

Excel : Matrice - Constante matricielle
Différentes manipulations des constantes matricielles avec une valeur

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

Entre constantes matricielles

Excel : Matrice - Constante matricielle
  • 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.

Excel_Matrice
Combinaison de 2 matrices d'orientation différente

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

Excel : Matrice - Constante matricielle

Les 2 modes d'évaluation d'Excel

Le mode IIE (Implicitly Intersecting Evaluation)

Exemple

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 faite 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.

Intersection implicite
La même formule donne des résultats différents en fonction de son placement

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).

Une simple somme de 2 plages 😊 >>>

  • En validant normalement on obtient 10 (8+2) > Intersection implicite.
  • En validant en matricielle on obtient 96 > Génération d'une matrice.
Excel_Matrice
Matrice générée par la validation matricielle
Excel_Matrice
Excel_Matrice
  • En cas d'utilisation de constantes matricielle, Excel évalue la matrice puis exécute la fonction SOMME > Génération d'une matrice.
Excel_Matrice
Illustration de l'intersection implicite

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

Excel_Matrice
Exemple RECHERCHEV avec l'argument No_index_col sous forme de matrice

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).

Excel : Matrice
Message d'erreur #PROPAGATION

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 !

Avant

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

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

Après

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

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

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.

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

Un commentaire

  1. Bonjour Arnaud, j'espère que tu vas bien
    Il faut que je me penche sérieusement sur cette fonction LAMBDA...
    A bientôt peut être

Laisser un commentaire

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