Monsieur Excel
Pour tout savoir faire sur Excel !

23 septembre 2009

SommeProd et formule matricielle

Ce n’est pas la première fois que nous illustrons la résolution d’un problème un peu complexe à l’aide de la fonction SommeProd et/ou d’une formule matricielle.

Dans l’exemple ci-dessous, nous souhaitons calculer en D15 la somme des montants du service « Comptabilité » en juin qui correspondent à plus de 50% d’engagement :

Comme cela a souvent été le cas, nous vous proposons plusieurs solutions :

En D15 :
=sommeprod(--(A2:A13="Comptabilité");--(mois(B2:B13)=6);--(C2:C13>50%);D2:D13)

En D16 :
=sommeprod(1*(A2:A13="Comptabilité");1*(mois(B2:B13)=6);1*(C2:C13>50%);D2:
D13)

En D17 :
=somme((A2:A13="Comptabilité")*(MOIS(B2:B13)=6)*(C2:C13>50%)*D2:D13)

Les deux premières solutions sont des variantes l’une de l’autre, l’objectif de "--" ou de "1*" étant de remplacer un vecteur de VRAI et de FAUX par un vecteur de 1 et de 0.

La troisième solution doit être saisie comme une formule matricielle, donc validée avec la combinaison [Ctrl]-[Maj]-[Entrée]…

C'est le prix à payer pour sa compacité supérieure !

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

10 Commentaire(s):

  • Bonjour Monsieur Excel,

    J'aimerais savoir pourquoi la formule : =SOMMEPROD((A2:A13="Comptabilité")*(MOIS(B2:B13)=6)*(C2:C13>50%)*(D2:D13))est mauvaise.

    Merci d'avance

    Cordialement

    Antoine

    By Anonymous Anonyme, sur 11:37 AM  

  • Cette solution fonctionne effectivement, car vous utilisez ici les "*" entre les blocs.

    Elle ne fonctionne plus si vous remplacez les "*" par des ";", ce qui correspond à la syntaxe naturelle de la fonction.

    By Blogger Hervé Thiriez, sur 9:43 AM  

  • Bonjour Mr Excel,

    En tâtonnant dans un cas de figure similaire, j'ai utilisé la formule #3, sans la valider avec un Ctrl+Maj+Entrée, elle donne toutefois le bon résultat.

    Est-ce normal, où vais-je me retrouver avec un gag plus tard ?

    Merci d'avance !

    By Anonymous klari, sur 11:17 AM  

  • Quelle version d'Excel utilisez-vous ? Est-ce Windows ou Mac ?

    Pour ma part, avec Excel 2003 et2007 sous Windows, j'obtiens un message d'erreur si je valide la troisième formule sans la mettre en "matriciel".

    By Blogger Hervé Thiriez, sur 8:54 AM  

  • Bonjour,

    Je souhaiterais mettre en place cette formule dans un fichier qui peut avoir un nombre de ligne différent d'un mois à l'autre. Du coup, j'ai mis la formule suivante qui ne fonctionne pas (#NOMBRE!) : =SOMMEPROD(--('SDB Invoice'!AS:AS="NEW");--('SDB Invoice'!AK:AK="LCEP");'SDB Invoice'!X:X)

    Est-ce normal ? Faut il vraiment que la plage de cellule soit bien défini ?

    Merci d'avance pour votre réponse. Et merci pour ce très bon blog !
    Cdt,
    Valérie

    By Anonymous Anonyme, sur 3:17 PM  

  • Essayez sans prendre des colonnes entières, par exemple avec AS2:AS1000...

    By Blogger Hervé Thiriez, sur 8:38 AM  

  • Effectivement ça fonctionne !
    Merci,
    Valérie

    By Anonymous Anonyme, sur 2:38 PM  

  • bonjour,

    J'utilise la formule n° 3 et j'aimerais savoir si je peux mettre des noms au lieu de plages car sinon cela m'oblige à l'actualiser tous les mois, au fur et à mesure que ma base de données grossit. Si ce n'est pas possible, quelle solution dois-je utiliser pour gagner du temps ?

    Merci pour votre réponse

    By Anonymous Anonyme, sur 12:00 AM  

  • A priori, cela marche...

    Pourquoi n'avez-vous pas essayé vous-même avant d'écrire ce commentaire ?

    By Blogger Hervé Thiriez, sur 2:31 PM  

  • En fait, la difficulté est que ma BDD est dans un fichier différent et que les noms ne fonctionnent pas dans ce cas, je crois.

    By Anonymous Anonyme, sur 5:34 PM  

Enregistrer un commentaire

<< Accueil