Monsieur Excel
Pour tout savoir faire sur Excel !

13 janvier 2006

Formule matricielle en cellule

Les formules matricielles, dans Excel, sont à la fois très puissantes et peu intuitives. On valide une formule matricielle, dans une ou dans plusieurs cellules d’un coup, en utilisant la combinaison [Ctrl]-[Maj]-[Entrée], ce qui les rend inaccessibles à ceux qui frappent sur leur clavier avec un seul doigt !

Prenons l’exemple ci-dessous, dans lequel nous avons des familles auxquelles appartiennent des produits et où, en C13, on veut calculer le chiffre d’affaires correspondant à une famille de produits donnée :

La solution classique revient à utiliser la colonne E, en entrant en E2 la formule =si(D2=$C$12;B2*C2;""), à la copier vers le bas et à récupérer le total en E11 dans C13.

La colonne E est parfaitement inutile si vous utilisez en C13 une formule matricielle : {=somme(si(D2:D10=C12;B2:B10*C2:C10;0))}

C’est-à-dire que vous entrez la formule sans les accolades, et que vous validez avec la combinaison [Ctrl]-[Maj]-[Entrée], qui ajoute de façon automatique les accolades.

En conclusion, quand on entre une formule matricielle, on demande à Excel de réaliser une boucle sur les éléments vectoriels (ou matriciels) indiqués, puis de réaliser l’opération demandée sur chacun de ces éléments, un par un.

Remarque – N’entrez surtout pas les accolades au clavier, sinon Excel prendrait cela pour du texte !

rédigé par Hervé Thiriez | 21 commentaire(s)

21 Commentaire(s):

  • Pourquoi ne pas utiliser directement la formule Somme.si
    {Somme.si(plage;critère;somme_plage)} ?

    By Anonymous Anonyme, sur 10:31 AM  

  • Pour deux raisons...

    La première est qu'un lecteur m'avait récemment demandé de présenter les formules matricielles.

    La seconde, c'est que l'on peut aller beaucoup plus loin avec les formules matricielles qu'avec la fonction somme.si().

    Ceci dit, cette fonction est tout à fait intéressante, et je compte bien la présenter un jour :)

    PS - La fonction somme.si() ne requiert pas de validation matricielle comme vos accolades le laissent penser...

    By Blogger Hervé Thiriez, sur 8:11 AM  

  • Entendu !
    Merci beaucoup pour la précision !

    By Anonymous Anonyme, sur 4:24 PM  

  • Bonjour.
    Merci d'avoir pris en compte ma demande sur les formules matricielles, mais je n'en vois toujours pas l'utilité pratique à mon niveau.
    J'aimerais bien avoir des exemples d'utlisations concrètes, autres que somme.si().

    By Anonymous Anonyme, sur 12:45 PM  

  • Bonjour,

    Peut-on créer une formule matricielle avec plusieurs critéres?

    PS: [Ctrl]-[Maj]-[Entrée] ne fontionne pas, mais [Ctrl]-[shift]-[Entrée] fonctionne.

    Emilienne Buquet
    emilienne.buquet@yamaha-motor.fr

    By Anonymous Anonyme, sur 4:10 PM  

  • [Ctrl]-[Maj]-[Entrée] est exactement la même chose que [Ctrl]-[Shift]-[Entrée], il y a seulement des claviers qui affichent l'un et d'autres qui affichent l'autre. J'ai fait le choix du terme français, cocorico ! C'est comme pour [Del] et [Suppr].

    Bien entendu, on peut créer des formules matricielles avec plusieurs conditions :)

    By Blogger Hervé Thiriez, sur 8:40 AM  

  • Dans votre exemple je ne comprends pas l'avantage d'une fonction matricielle sur la fonction sommeprod suivante : SOMMEPROD(((D2:D10)=C13)*(B2:B10)*(C2:C10))
    qui, si on s'habitue à cette fonction permet de résoudre beaucoup de choses.Elle a également une syntaxe relativement simple et intuitive. Personnellement je l'utilise aussi à la place aussi de la fonction somme.si...Avez vous d'autres exemples de fonction matricielle ? Merci

    By Blogger Jack Spyker, sur 4:06 PM  

  • Merci,

    j'ai reçu un fichier qui contenait de telle formules. Et pas moyen pour moi de faire apparaitre les satanés accolades. Maintenant je suis sauvé.

    Exemple : dans mon cas il fallait que je fasse la somme des cellules avec la valeur A dans une colonne et la valeur B dans une autre.

    By Blogger Unknown, sur 4:24 PM  

  • C'est sûr que l'exemple choisi ne permet pas de mettre en lumière le véritable intérêt des formules matricielles. Je n'ai pas encore tout compris sur le sujet mais j'ai trouvé des exemples intéressants.

    Exemple: comment trouver la valeur de la première cellule non vide au dessus d'une cellule donnée:
    => cellule non vide

    By Anonymous Anonyme, sur 2:11 AM  

  • La formule somme.si est un peu limitée, surtout si les critères sont plusieurs.
    La sommeprod est plus adapté pour ce qui savent bien l'utiliser.
    Une formule matriciele est magnipulable, complète et sible à mettre en place.

    un exemple simple :
    En remplie deux colonnes avec des chiffres de 2 à 30 sur 20 lignes. Dans la cellule B21 on veut savoir la somme des deux colonnes avec les contitions suivantes : A1:A20>20 et B1:B20<15.
    Voici la formule matricielle : {=SOMME(SI(A1:A20>20;A1:A20;0)+SI(B1:B20<10;B1:B20;0))}

    By Anonymous Anonyme, sur 8:43 PM  

  • Voici un autre exemple :
    mettez des A,B,C dans les cellules A1:A10. Mettez des 1, 2 dans les cellules B1:B10. Mettez 10,20,30 dans les cellules C1:C10. Mettez 2,3 dans les cellules D1:D10.
    Maintenant on veut calculer dans la D12 La somme de C*D si la colonne A="A" et B="2"

    Voici la formule :
    {=SOMME((A2:A10="A")*(B2:B10=2)*(C2:C10*D2:D10))}
    Resultat = 60

    By Anonymous Anonyme, sur 9:36 PM  

  • je n'arrive pas a valider ma formule par ctrl maf entree . cela ne fait rien

    Ma formule :

    =NB(SI(($I$310:$II$9999="CL")*($K$30:$K$9999=B$5);$E$30:$E$9999))

    Merci de votre aide

    Christine

    By Anonymous Anonyme, sur 2:49 PM  

  • les formules matricielles sont utilisees dans le cas de double condition
    Exemple:
    {SUM(IF(CC=D347,IF(CE=E347,DBVAL1,0)))}

    By Blogger Frederic van Haute, sur 9:24 PM  

  • Bonjour

    j'ai vu qu'on avait soulevait le cas de la fonction SOMME.SI ce n'est qu'un exemple mais les fonctions matricielles sont bien plus utiles cela ma permis de simuler une fonction MAX.SI inexistante dans Excel.

    je trouve regrettable le fait de les valider par une combinaison de de touches, il suffit de mettre une cellule en mode édition et on perd l'attribut de fonction matricielle

    @Frédéric

    pour les double condition je préfère de loin utiliser la fonction SOMMEPROD

    By Blogger AIT YAHIA Idir, sur 5:28 PM  

  • bonjour sur cet formule je voudrais
    que la dernière ligne F26 s'adapte
    automatiquement avec la dernière ligne de mon tableau
    et celui ci varie en permanence

    =SOMME(1/NB.SI(F4:F26;F4:F26))

    By Anonymous Anonyme, sur 6:49 PM  

  • C'est un peu compliqué. Je traiterai cela dans un article ultérieur.

    Pour trouver le n°de la dernière ligne utilisée, il faut faire un equiv(-infini;vecteur;-1)...

    By Blogger Hervé Thiriez, sur 11:03 PM  

  • Comment réaliser cette commende en VBA ? Je rempli un tableau complet de 256 lignes sur 300 colonnes en formule matricielle et je ne peux pas me premettre de me mettre sur chaque cellule et faire un ctrl-shift-entrée.
    Merci de votre aide

    By Anonymous Anonyme, sur 4:03 PM  

  • Un exemple simple et clair de l'utilisation de l'écriture matricielle...

    J'ai compris et je vous en remercie!

    Cordialement.

    By Anonymous Mathias, sur 1:13 PM  

  • Slt
    Est ce qu'on peux utuliser la fonction Rank ds une formule matrecielle (cad renvoyer le rang d une cellule selon plusieurs conditions)
    Cordialement.

    By Blogger Mouha, sur 4:57 PM  

  • Bonjour,
    j'ai un classeur xls dans lequel j'ai plusieurs formules matricielles. Récemment j'ai eu un problème avec l'apparition de #NOM? à la place de mes résultats de calcul à chaque réouverture de classuer.
    Je dois à chaque fois manuellement réactiver les formules (F2 puis ctrl+Maj+Entrée).
    Auriez-vous une solution à cette problématique.

    Très Cordialement...

    By Anonymous Anonyme, sur 10:17 AM  

  • si votre formule matricielle est supérieure à 255 caractères,
    alors FormulaArray ne validera pas la formule matricielle

    vous pouvez sélectionner votre cellule (ici A1) avec:

    Range("A1").select

    puis ajouter en dessous:

    Application.SendKeys"{F2}^+~"

    Cela revient à faire un CTRL+MAJ+ENTER sur la cellule A1

    By Anonymous Anonyme, sur 2:33 PM  

Enregistrer un commentaire

<< Accueil