Formule matricielle VS somme.si.ens RESOLU

[Kran]

Bonjour à tous,

Je colle (comme la UhU). à dire je ne comprends pas d'où peut venir le problème.

Jusqu'à présent j'utilisais une formule matricielle pour faire une somme à conditions sur des matrices de lignes :

 {=SOMME(SI((C(-51)=LC(-51))*(C(-50)=LC(-50))*(C(-62)=LC(-62));C(-1)))}

Elle marche très bien mais est extrêmement lourde (surtout que je la répète sur quelques 3000 lignes). Du coup pour tester le poids j'ai fais une formule équivalente à l'aide d'une SOMME.SI.ENS :

 =SOMME.SI.ENS(C(-1);C(-51);LC(-51);C(-50);LC(-50);C(-62);LC(-62))

Ce qui fonctionne bien d'habitude sur ce genre de conditions mais pas ici

la formule matricielle me renvoie 6750.72 (la bonne valeur) quand le SOMME.SI.ENS me renvoie 0

A l'aide

 

[Kran]

Re : Formule matricielle VS somme.si.ens

Avec une petite pièce jointe c'est mieux

 

[Kran]

Re : Formule matricielle VS somme.si.ens

Bon en faite je crois que je viens de trouver tout seul...

La SOMME.SI.ENS ne peut comparer que des nombres!? c'est horrible...

merci à tous, problème résolu

P.S. : horrible sensation de parler tout seul quand même

 

[Modeste]

Re : Formule matricielle VS somme.si.ens RESOLU

Bonsoir Kran,

Si tu repasses par ici ... en modifiant ton "Critères3" en une paire de guillemets vides, le résultat est le même qu'avec ta formule matricielle

=SOMME.SI.ENS(BL:BL;P:P;P2;Q:Q;Q2;E:E;"")

 

[Jocelyn]

Re : Formule matricielle VS somme.si.ens RESOLU

Bonjour le Forum,
Bonjour Kran, Modeste,

Principalement pour saluer l'ami Modeste, mais quand même une autre solution par sommeprod

=SOMMEPROD((LC(-49):L(1)C(-49)=LC(-49))*(LC(-48):L(1)C(-48)=LC(-48))*(LC(-45):L(1)C(-45)=LC(-45))*LC(-1):L(1)C(-1))

Cordialement

 

[Kran]

Re : Formule matricielle VS somme.si.ens RESOLU

Bonjour à vous deux,

@modeste, Le problème c'est que cette cellule n'est pas toujours vide. Effectivement si je m'ai "" ou que je rempli la cellule, le résultat est juste mais il faut que je puisse englober les deux cas.

@Jocelyn, J'ai fait le test entre une sommeprod, une somme.si.ens et une formule matricielle et les délais d'exécution sont quasi équivalent de toute façon (la sommeprod est effectivement un poil plus rapide de 1 sec sur 10000cellules de calcul)

Je vais voir laquelle j'utiliserai donc dans le futur, sachant que la formule matricielle ne peut pas être modifié en fichier partagé (avantage ou inconvénient?? )

En tout cas merci bcp à vous deux

 

[Misange]

Re : Formule matricielle VS somme.si.ens RESOLU

Bonjour

juste un petit commentaire
les formules sommeprod ou somme.si.ens sou somme.si validée en matricielle n'ont pas de raison d'être différentes en terme de rapidité car ce sont toutes les trois des formules matricielles.
Une formule matricielle n'est pas définie par le fait qu'il faut la valider avec la combinaison de touche ctrl+Maj+entrée mais par le fait qu'elle évalue des plages et non juste une cellule.
Somme.si.ensemble, tout comme sommeprod regarde pour chaque cellule de la plage, si elle correspond au critère, renvoie vrai ou faux puis fait la somme des produits.
Il n'y a pas à ma connaissance de restriction pour la modification d'une matricielle en mode partagé.

 

[Kran]

Re : Formule matricielle VS somme.si.ens RESOLU

Bonjour Misange,

Merci pour cet éclaircissement. Cependant, je confirme une formule matricielle de format {} n'ai pas modifiable sur un classeur partagé (du moins pas sous 2007), une SOMME.SI.ENS oui.

Cela dit ça m'arrange vu que je suis le seul à manipuler ces formules, je préfère qu'elles ne puissent plus être altéré une fois le classeur partagé.

 

[Modeste]

Re : Formule matricielle VS somme.si.ens RESOLU

Bonjour Kran, Jocelyn, Misange ... et puis tous les autres!

@Kran: ravi de voir que tu es repassé Par contre, je ne suis pas certain d'avoir bien compris

il faut que je puisse englober les deux cas.

S'il faut additionner les valeurs en colonne BL, que E soit vide ou non pourquoi ne pas supprimer la condition?

@Jocelyn: je suis repassé principalement pour te rendre ton salut (mais ça peut durer un bout de temps, à ce rythme! )

@tous: je venais précisément de lire un article de Microsoft sur les comparaisons entre SOMMEPROD, SOMME.SI.ENS et les formules matricielles. Ils semblent dire que SOMME.SI.ENS (voire BDSOMME que j'oublie souvent) serait plus performantes et/ou plus rapides. Je ne résiste donc pas au plaisir de vous communiquer Ce lien n'existe plus ... si vous ne le connaissez déjà!?

 

[Misange]

Re : Formule matricielle VS somme.si.ens RESOLU

Hello Modeste
Je connaissais cet excellent lien, plein de très bons conseils (et qui a fait l'objet de discussions animées dans certain forum privé). Le choix de sommeprod versus somme.si.ensemble n'est pas si évident. Cela dépend beaucoup du nombre de données, et du nombre de fois qu'il faut recalculer la feuille. Cela vaut le coup de se poser la question si comme Charles Williams tu travailles fréquemment sur des bases de données vraiment très importantes. Là ça peut être crucial car le temps de recalcul et la quantité de mémoire nécessaire peut tout simplement rendre l'opération ingérable. Ce qui est le plus efficace dans ce cas c'est effectivement de splitter le sommeprod (ou le somme.si.ens d'ailleurs) pour tester les différents éléments séparément.

N'étant pas chirogourdiste par nature (les anciens du MPFE retrouveront là un terme cher à Alain Vallon), je préfère toujours une formule qui se valide sans grand écart de doigts donc sommeprod à somme.si matriciel.
Concernant les formules matricielles, étant comme certains ont du le remarquer une adepte convaincue des tableaux (comme Charles Williams du reste), comme un tableau ne peut pas contenir de formules matricielles à plusieurs cellules (saisir une formule maticielle sur toute une colonne en même temps) j'évite

 

[Misange]

Re : Formule matricielle VS somme.si.ens RESOLU

Un autre excellent lien sur l'optimisation (en anglais)
Excel Speedup & Optimization Tips by Experts | Chandoo.org - Learn Microsoft Excel Online

 

[Kran]

Re : Formule matricielle VS somme.si.ens RESOLU

@Kran: ravi de voir que tu es repassé

Merci , ravi de voir que mon topic sucite tant d'animation

Par contre, je ne suis pas certain d'avoir bien compris S'il faut additionner les valeurs en colonne BL, que E soit vide ou non pourquoi ne pas supprimer la condition?

En réalité il faut que toutes cellules concerné soient égales pour que l'addition se fasse. si c'est vide l'autre doit aussi être vide pour être sommé, si c'est écrit "Grut", l'autre aussi doit contenir "Grut" sinon pas de somme.

Conditions qui doit être vérifier pour 4 colonnes à la fois. Je sais pas trop si je suis clair...

 

[Modeste]

Re : Formule matricielle VS somme.si.ens RESOLU

Bonjour le fil et le forum,

Précisons d'emblée que je ne suis pas un ardent défenseur de SOMME.SI.ENS, mais juste pour répondre à la question initiale, il me semble qu'en modifiant la syntaxe pour les critères de la manière suivante, les résultats pourraient être plus conformes aux attentes:

=SOMME.SI.ENS(C(-1);C(-49);"="&LC(-49);C(-48);"="&LC(-48);C(-60);"="&LC(-60))

... à tester ...
Ceci dit, explorer des colonnes entières risque d'être un brin "gourmand" (quelle que soit la formule matricielle utilisée)

Merci à Misange pour le lien, même si -en ce qui me concerne- je suis loin de comprendre toutes les subtilités de la langue de Shakespeare ... alors s'il s'agit d'experts, en sus