Avez-vous entendu parler des mystérieuses «formules matricielles»? Les utilisez-vous? Ou êtes-vous comme la plupart des utilisateurs d’Excel, vous les avez placés dans la pile «trop dure», pour ne plus jamais être revus. Ils semblent toujours apparaître dans les forums comme des solutions à des questions de formule délicates.
Dans cet article, je veux vous présenter un type de formule matricielle qui est en fait assez facile à comprendre et peut être appliquée à de nombreuses situations réelles. Une fois que vous aurez compris le concept de ce type de formule matricielle, vous pourrez l’appliquer encore et encore. Vos collègues de travail vous regarderont comme si vous étiez un génie d’Excel, ils seront fascinés par le calcul apparemment impossible que vous venez d’effectuer. . . mais secrètement, vous saurez que ce n’était pas si difficile.
Une formule matricielle est un calcul qui fonctionne avec un tableau ou une série de données. Ne vous inquiétez pas si ce concept vous souffle en ce moment, à la fin de cet article, il aura du sens.
Souvent, les formules matricielles sont entrées en appuyant sur Ctrl + Maj + Entrée – d’où la raison pour laquelle elles sont parfois appelées formules CSE. Il existe des fonctions de tableau qui ne nécessitent pas l’utilisation de Ctrl + Maj + Entrée. Sachez donc que Ctrl + Maj + Entrée ne s’applique pas à toutes les formules matricielles.
Le type de formule matricielle que nous utiliserons dans cet exemple nécessite Ctrl + Maj + Entrée. Une fois la formule entrée dans la barre de formule, n’appuyez pas simplement sur Entrée, mais appuyez sur Ctrl + Maj + Entrée en même temps. Vous saurez quand cela a fonctionné car Excel affichera automatiquement un “{” (crochet ouvrant) au début de la formule et un “}” (crochet fermant) à la fin de la formule.
Vous devez appuyer sur Ctrl + Maj + Entrée, non seulement lors de la création de la formule la première fois, mais à chaque fois que vous modifiez la formule.
Dans les exemples ci-dessous, je montrerai les {} accolades, mais rappelez-vous, ne les tapez pas, Excel les affichera tout seul.
Pour notre exemple, nous examinerons le type de formule matricielle qui renvoie le résultat dans une seule cellule.
Nous allons recréer la fonction SUMIF sous forme de formule matricielle. Pourquoi? J’espère que vous comprenez déjà la fonction SUMIF, donc les concepts seront plus faciles à comprendre. L’objectif n’est pas de recréer un SUMIF à l’aide d’une formule matricielle, mais d’apprendre les concepts afin de pouvoir l’appliquer à vos propres scénarios.
Regardez le tableau de données ci-dessous, il montre les ventes trimestrielles pour 3 départements d’un magasin de vêtements. Si nous voulions connaître le total annuel de n’importe quel département, nous pourrions utiliser la fonction SUMIF.
La valeur de la cellule F4 calcule les ventes totales du département Vêtements pour hommes:
=SUMIF(A2:A13,F2,C2:C13)
Décomposons la fonction SUMIF en ses deux parties SUM et IF. La logique requiert que la fonction IF soit calculée en premier, puis la fonction SOMME peut être utilisée pour calculer le total. C’est ce que nous allons faire.
La première étape pour convertir SUMIF en formule matricielle consiste à créer une fonction IF pour une seule ligne.
=IF(A2=F2,C2,0)
La deuxième étape consiste à étendre la plage pour inclure toutes les cellules requises pour le calcul (c’est ce qui en fait une formule matricielle, car elle calcule en fonction d’un tableau de cellules).
=IF(A2:A13=F2,C2:C13,0)
La troisième étape consiste à inclure la fonction SOMME autour de la fonction IF.
=SUM(IF(A2:A13=F2,C2:C13,0))
< La dernière étape consiste à appuyer sur Ctrl + Maj + Entrée pour entrer la formule (notez qu’Excel affiche le {} par lui-même).
La formule de la cellule F6 est:
{=SUM(IF(A2:A13=F2,C2:C13,0))} Vous venez de créer une formule matricielle. Ce n’était pas si difficile, n’est-ce pas.
Comprenons brièvement pourquoi cette formule fonctionne.
{=SUM(IF(A2:A13=F2,C2:C13,0))} La section de la formule en bleu ci-dessus sera calculée comme TRUE ou FALSE pour chaque cellule. Est-ce que A2 = F2, puis A3 = F2, puis A4 = F2, et ainsi de suite. Comme F2 est une cellule unique, elle est utilisée comme comparaison pour chaque cellule de A2 – A13.
Il y a 12 cellules dans la plage, donc il y a 12 résultats TRUE / FALSE.
{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
C2:C13,0))} La section orange de la formule ci-dessus sera calculée comme indiqué ci-dessous.
{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
{1000;800;1200;1100;750;1050;1150;850;950;1250;900;1000},0))} Chacun de ces résultats de formule est calculé comme une fonction IF distincte.
{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
{1000;800;1200;1100;750;1050;1150;850;950;1250;900;1000},0))} La première fonction IF a calculé comme FALSE, donc le résultat sera zéro (il aurait été 1000, s’il était TRUE)
{=SUM(IF({FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},
{1000;800;1200;1100;750;1050;1150;850;950;1250;900;1000},0))} La deuxième fonction IF a calculé comme TRUE, donc le résultat sera 800 (il aurait été zéro si c’était FALSE)
Cela continue pour chaque résultat de la fonction IF. Cela se calcule pour fournir le résultat suivant.
{=SUM({0;800;0;0;750;0;0;850;0;0;900;0}))} Il s’agit maintenant d’une fonction SOMME simple, qui ajoutera les nombres ensemble. Le résultat est de 3 300 (ce qui est exactement le même que SUMIF).
Ce concept fonctionne pour une variété de situations. Maintenant que vous connaissez le concept, vous pouvez créer vos propres formules complexes, ce qui serait impossible avec une fonction normale.
Vous pouvez créer une fonction SUMIFBUTFIXEDVALUEIFNOT (Somme si, mais valeur fixe sinon):
{=SUM(IF(A2:A13=F2,C2:C13,500))} Ou peut-être la fonction AVERAGEROWNUMBER (nombre de lignes moyen):
=AVERAGE(ROW(A2:A13))
Vous pouvez également créer l’avantage des colonnes d’assistance sans avoir à créer une colonne d’assistance. Dans l’exemple ci-dessous, les cellules E14 et F14 sont combinées en un seul critère de recherche puis comparées à A2 et B2 combinés, puis A3 et B3 combinés, puis A4 et B4 combinés, et ainsi de suite.
=MATCH(E14&F14,A2:A13&B2:B13,0)
Les formules de tableau peuvent sembler excellentes, mais il y a quelques problèmes à prendre en compte:
Voici quelques articles du blog qui incluent d’autres exemples de formules matricielles.
N’oubliez pas:
Si vous avez trouvé cet article utile ou si vous avez une meilleure approche, veuillez laisser un commentaire ci-dessous.
Avez-vous besoin d’aide pour l’adapter à vos besoins?
Je suppose que les exemples de cet article ne correspondaient pas exactement à votre situation. Nous utilisons tous Excel différemment, il est donc impossible d’écrire un article qui répondra aux besoins de chacun. En prenant le temps de comprendre les techniques et principes de cet article (et ailleurs sur ce site) vous devriez pouvoir l’adapter à vos besoins.
N’y allez pas encore, il y a beaucoup plus à apprendre sur comment-supprimer. Consultez les derniers articles:
Comment créer une séquence continue dans Excel
Comment déplacer un graphique dans Microsoft Excel
Ajouter une ligne diagonale à une cellule dans Excel
Comment composer certaines lignes ou colonnes dans Excel
sauvegarder
