Les formules matricielles

Contactez moi pour vos questions

Mes idées cadeaux pour Noël

Les formules matricielles. sont un type particulier de formules très puissante pour traiter des tableaux entiers de données.

Les fonctions matricielles - Découverte avec des exemples

Dans cette page, nous allons découvrir les formules matricielles à l'aide d'exemples. Ils vous permettront de vous approprier rapidement cette nouvelle fonctionnalité.

L'essentiel
  • Appuyer sur les touches : Ctrl+Maj+entrée pour valider.
  • Des crochets apparaissent de part et d'autre de la fonction dans le champs de la cellule : {}
  • Les résultats d'une formule peuvent être contenus dans plus d'une cellule.

Exemple n°1 : Multiplication de colonnes entre elles

Imaginons que nous souhaitions multiplier les valeurs d'une colonne de 5 cellules par une autre colonne de 5 cellules tout en souhaitant que les résultats apparaissent dans une 3e colonne. On pourra alors procéder ainsi :

Etape 1 : Sélectionner les cellules de la colonne résultat

Multiplication de colonnes entre elles

Etape 2 :

  • Taper le signe égale "="
  • Sélectionner les cellules de la colonne 1

Formule matricielle - Multiplication de colonnes entre elles

Etape 3 :

  • Taper le signe étoile "*"
  • Sélectionner les cellules de la colonne 2
    La formule résultante est =B2:B6*C2:C6

Formule matricielle - Multiplication de colonnes entre elles e3

Etape 4 :

  • Appuyer sur les touches : Ctrl+Maj+entrée

Formule matricielle - Multiplication de colonnes entre elles e4

Les résultats s'affichent dans les 5 cellules résultats, vous remarquerez aussi les accolades qui sont apparues de chaque côté de la formule.
Excel signale de cette manière qu'il s'agit d'une formule matricielle.

Exemple n°2: Comptabilisation du nombre de caractères

Imaginons que nous souhaitions compter le nombre de caractères présents dans un groupe de cellules contenant du texte.

Formule matricielle - Comptabilisation du nombre de caractères -

Résultat :

Formule matricielle - Comptabilisation du nombre de caractères - e2

L'avantage des formules matricielles prend ici toute sa place. En effet si ce travail avait été réalisé par des formules standards, il aurait été nécessaire d'écrire la formule NBCAR() autant de fois qu'il y a de cellules distinctes. Puis il aurait fallu en faire la somme.

Exemple n°3 : Recherche d'une chaîne de caractères

Dans cet exemple, nous allons voir comment créer un outil pour rechercher un pays parmi une liste de pays.

Etape n° 1 :

  • Nommer le groupe de cellules contenant la liste de pays par "ListePays". (Je vous propose dans ce module d'utiliser des noms pour plus de facilité, toutefois vous pouvez malgré tout utiliser les références habituel de cellules. Pour en avoir plus sur le nommage des cellules consulter cette page : referencecellule )

Formule matricielle - Recherche d'une chaîne de caractères -

Etape n° 2 :

  • Nommer la cellule contenant le pays que l'utilisateur va souhaiter rechercher "PaysRecherche"

Formule matricielle - Recherche d'une chaîne de caractères - e2

Etape n° 3 :

  • Inscrire la formule suivante dans la cellule de réponse (C15)

"=SI(OU(PaysRecherche=ListePays);"Le pays est dans la liste";"Le pays n'est pas dans la liste")"

  • Ne pas oublier de terminer la saisie en appuyant sur les touches : Ctrl+Maj+entrée

Formule matricielle - Recherche d'une chaîne de caractères - e3

Etape n° 4 :

  • Utiliser l'outil créé en indiquant un nom de pays dans la cellule bleue.

Formule matricielle - Recherche d'une chaîne de caractères - e4

 

Exemple n°4 : Recherche d'une chaîne de caractères

Dans cet exemple, nous allons voir comment interroger un tableau à l'aide de formules matricielles à travers l'exemple d'un tableau représentant les ventes de parapluies.

Formule matricielle - Recherche d'une chaîne de caractères - e5

Question n° 1 : Combien de parapluies ont été vendus au mois de janvier ?

Pour répondre à cette question, par l'approche classique, nous sélectionnerions les quantités correspondantes au mois de janvier et nous en ferions la somme. Cependant cette approche atteint ses limites lorsque que le tableau devient volumineux et que les tris ne sont plus possibles.

Formule matricielle - Recherche d'une chaîne de caractères - e6

La technique consiste donc à faire la somme des quantités de la colonne D mais uniquement pour les cellules pour lesquelles le mois associé est le mois de janvier.

Formule : "=SOMME((B4:B10="Janvier")*(D4:D10))"

Formule matricielle - Recherche d'une chaîne de caractères - e7

Question n° 2 : Combien de parapluies ont été vendus au mois de janvier en Afrique?

La logique est la même que l'exemple précédent en ajoutant le pays.

Formule : "=SOMME((B4:B10="Janvier")*(C4:C10="Afrique")*(D4:D10))"

Formule matricielle - Recherche d'une chaîne de caractères - e7

Question n° 3 : Combien de parapluies ont été vendus au mois de février ailleurs qu'en Asie ?

Cette fois ci, on utilise les symboles : "<>" pour exclure la valeur "Asie"

Formule : "=SOMME((B4:B10="Janvier")*(C4:C10<>"Asie")*(D4:D10))"

Formule matricielle - Recherche d'une chaîne de caractères - e8

 

Question n° 4 : Quel est le nombre de continents dans lesquels des ventes ont été réalisées au mois de mars ?

Cette fois ci, on utilise les symboles : "<>" pour exclure les valeurs non-nulles sur la colonne Quantité.

Formule : "=SOMME((B4:B10="Mars")*(D4:D10<>""))"

Formule matricielle - Recherche d'une chaîne de caractères - e9

Formule matricielle - Recherche d'une chaîne de caractères - e10

Question n° 5 : Quel est le nombre de ventes pour lesquelles le volume a été supérieur à 200 000 unités ?

Cette fois ci, on utilise le symbole : ">" pour ne sélectionner auer les quantités supérieur à 200  000.

Formule : "=SOMME((B4:B10<>"")*(D4:D10>200000))"

Formule matricielle - Recherche d'une chaîne de caractères - e11

Formule matricielle - Recherche d'une chaîne de caractères - e12

 

Question n° 6 : Quelle est la somme des ventes qui ont un total supérieur à 100 000 unités ?

Cette fois ci, on utilise le symbole : ">" pour ne sélectionner auer les quantités supérieur à 100 000 et on mulitiliplie par la cellules de la colonne Quantité.

Formule : "=SOMME((B4:B10<>"")*(D4:D10>100000)*(D4:D10))"

Formule matricielle - Recherche d'une chaîne de caractères - e13

Formule matricielle - Recherche d'une chaîne de caractères - e14

On voit à travers ces exemples que l'on peut interroger un tableau de la même façon qu'avec une base de données.

Etes-vous curieux ? Alors je vous conseille...

Livre très pratique pour faire des statistiques avec Excel rapidement. Cette page a été inspirée de méthodes décrites dans ce livre.
Dans le top 10 des livres que je recommande !


Autres sites à découvrir

Autres dossiers à découvrir