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 !
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
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
Hervé Thiriez, sur 8:11 AM
Entendu !
Merci beaucoup pour la précision !
By
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Anonyme, sur 2:33 PM
Enregistrer un commentaire
<< Accueil