Re : Combiner une fonction RECHERCHE multi-critère et une fonction somme
Bonsoir abracadabra,
Tu trouveras dans l'aide Excel l'utilisation première de SOMMEPROD() pour multiplier plusieurs colonnes entre elles, mais l'aide ne va pas plus loin.
SOMMEPROD() est une fonction matricielle, assez magique même si très lourde en mémoire. Contrairement à SOMME.SI() ou NB.SI() qui n'autorisent qu'un seul critère, cette fonction permet d'effectuer des calculs en fonction de plusieurs critères.
Les plages utilisées doivent être de même taille (dans ton exemple les plages s'étendent toutes de la ligne 2 à la ligne 11), les conditions sont placées entre parenthèse, il ne faut pas utiliser de colonne entière (jusqu'à XL 2003 je crois), les conditions ET et OU se traduisent par * et +, voilà pour le plus gros à connaitre.
Dans ta formule en B6:
SOMMEPROD((Frais!$A$2:$A$11=Facture!$B$2)*(Frais!$B$2:$B$11=Facture!A6)*(Frais!$C$2:$C$11))
La première condition est: (Frais!$A$2:$A$11=Facture!$B$2)
cette première condition renvoie une matrice avec VRAI pour chaque ligne où la condition est vérifiée, FAUX pour les autres, comme ceci:
{FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;FAUX;FAUX;FAUX}
et effectivement dans le tableau du premier onglet, la condition est vérifiée à partir de la cinquième cellule (soit en A6) jusqu'à la 7ème (soit en A8), et pas pour les autres.
La seconde condition est: (Frais!$B$2:$B$11=Facture!A6), qui elle renvoie cette matrice:
{VRAI;FAUX;VRAI;FAUX;FAUX;VRAI;FAUX;VRAI;VRAI;FAUX}
soit VRAI à chaque fois qu'il y a un A dans la colonne B, FAUX pour les autres.
En multipliant des VRAI et des FAUX, on obtient 0 ou 1, VRAI par VRAI = 1, VRAI par FAUX ou FAUX par FAUX = 0
Avec nos deux matrices, la multiplication donne donc:
{0;0;0;0;0;1;0;0;0;0}
soit 1 si les deux conditions sont vérifiées (même entreprise et même type de frais), 0 si au moins une des conditions est fausse.
La somme des frais se trouve dans la dernière partie de la formule:
(Frais!$C$2:$C$11)
qui elle aussi est une matrice pouvant s'écrire comme ceci, et qui reprend l'ensemble des valeurs de la plage colonne C:
{15;200;7;6;3;66;721;22;335;9}
SOMMEPROD() va donc additionner le résultat de la multiplication de nos matrices,
{0;0;0;0;0;1;0;0;0;0} *
{15;200;7;6;3;66;721;22;335;9}
est donc égal à 66
Petite astuce bien pratique pour vérifier les résultats intermédiares d'une formule, tu sélectionnes dans la barre de formule la partie à contrôler et tu appuies sur la touche F9. Ensuite tu fais ESCAPE pour ne pas modifier ta formule si non F9 te remplacera la partie de ta fomule par le résultat).
Si la formule est longue bien évidemment tu peux aussi la décomposer en plusieurs morceaux.
Voilà abracadabra, j'espère que ces quelques commentaires pourront t'aider à y voir plus clair, sans oublier bien sûr les centaines de fils que tu trouveras sur le forum autour de SOMMEPROD()
Bien à toi,
mth
