Excel: Sumproduct, un truc puissant dont vous ne pourrez plus vous passer

Publié le 29 août 2011
par Sophie Marchand M.Sc.

La fonction Sumproduct pour faire une somme conditionnelle

Je vous ai déjà parlé de la façon d’utiliser l’assisant Conditional Sum (Somme conditionnelle en français) pour effectuer une somme conditionnelle sur plusieurs critères. Je vous ai également montré comment le faire en utilisant la fonction SUMIF (SOMME.SI en français). Aujourd’hui, j’aimerais vous montrer une autre alternative, très puissante, soit l’utilisation de la fonction SUMPRODUCT dans Excel (SOMMEPROD en français) non pas pour faire la somme de champs multipliés mais bien pour faire une somme conditionnelle selon divers critères. Une fois que vous aurez maîtrisé cette méthode, vous ne pourrez plus vous en passer!

 

Démonstration concrète de l’utilisation de la fonction SUMPRODUCT dans Excel

Dans l’exemple ci-dessous, nous avons un fichier à deux onglets. Ce que l’on souhaite démontrer, c’est le temps que chaque employé passe réellement à faire un travail productif vs le temps total où il est disponible pour travailler.

Le premier onglet s’intitule “Liste principale”. Il contient les heures de travail planifiées (octroyées) par employé à différentes dates durant l’année 2011.

 

Le deuxième onglet s’intitule “Occupation Employés” et il sert à calculer le taux d’occupation réel des employés vs les heures de travail planifiées (octroyées). Dans cet onglet, nous avons d’abord inséré un tableau dans lequel nous avons mis le nom de tous les employés dans une colonne avec des cellules supplémentaires pour ajouter des employés au besoin. Voir colonne B dans l’image ci-bas.

 

 

Compléter la table de données

Nous avons ensuite attribué à chaque employé un secteur d’activité. D’ailleurs, dans l’onglet « Liste principale », la colonne « Secteur » n’est pas un input mais plutôt un calcul dérivé du tableau ci-bas. Nous avons simplement utilisé la formule Vlookup (Recherchev en français) pour présenter le secteur d’activité de chaque employé.

Par la suite, nous avons ajouté une colonne “heures hebdomadaires” pour indiquer le nombre d’heures disponibles par semaine par employé.

Enfin, nous avons ajouté deux colonnes: “Début” et “Fin” pour indiquer la date d’embauche et la date de fin d’emploi de chaque ressource.

 

Créer un menu déroulant de validation de données

Ensuite, nous avons créé un menu déroulant avec tous les noms des employés à l’aide de la fonctionnalité Data Validation (Validation de données en français). Ainsi, lorsque nous cliquons dans la cellule B14 (voir image ci-bas), nous avons un menu déroulant avec la liste des employés et nous devons choisir un employé. Dans la cellule B15, nous utilisons un simple Vlookup (Recherchev en français) pour indiquer le secteur d’activité.

À la cellule B19, nous avons indiqué la date du premier lundi de l’année 2011. Dans la cellule B20, nous avons simplement inscrit: =B19+7 et nous avons copié la formule jusqu’à ce que l’on atteigne le dernier lundi de l’année 2011, soit le 26 décembre 2011.

 

Calculer le nombre d’heures planifiées

Dans la colonne C, nous avons employé une formule bien spéciale pour calculer le nombre d’heures planifiées (octroyées) par ressource par semaine. À la cellule C19, la formule de la fonction Sumproduct ressemble à ceci:

=SUMPRODUCT((‘Liste principale’!$B$2:$B$1901=$B$14)*(‘Liste principale’!$C$2:$C$1901>=$B19)*(‘Liste principale’!$C$2:$C$1901<$B20);’Liste principale’!$D$2:$D$1901)

Ce qu’il faut comprendre, c’est que tous les arguments séparés par des * sont des conditions. Donc, dans le présent exemple, on dit d’abord que l’employé doit être égal à la cellule B14 (là où se trouve notre choix du menu déroulant). On dit ensuite que la date doit être supérieure ou égale au 3 janvier 2011 et qu’elle doit être inférieure au 10 janvier 2011. Le dernier argument, précédé d’un ; indique les cellules à additionner, i.e. les heures planifiées (octroyées).

 

Calculer les heures disponibles

Pour calculer les heures disponibles, dans la cellule D19, nous avons utilisé la formule suivante:

=IF(AND(VLOOKUP($B$14;$B$4:$F$11;4;FALSE)=$B19;VLOOKUP($B$14;$B$4:$F$11;5;FALSE)>=$B19);
VLOOKUP($B$14;$B$4:$F$11;3;FALSE);0)

En résumé, cette formule indique que si la date indiquée dans la colonne B est supérieure ou égale à la date d’embauche et égale ou inférieure à la date de fin d’emploi, on doit indiquer le nombre d’heures de travail disponibles dans cette semaine.

 

Calculer l’écart

En conclusion, l’écart est simplement la différence entre les heures octroyées (planifiées) et les heures disponibles.

 

Fonction Sumproduct dans Excel

 

Tracer un graphique des heures octroyées vs disponibles

Nous sommes finalement en mesure de tracer un graphique qui montre les heures de travail octroyées (planifiées) vs les heures de travail disponibles par employé, selon sa période d’embauche.

 

Fonction Sumproduct dans Excel

 

Voici une courte vidéo vous montrant le dynamisme des graphiques ainsi créés.

 

 

Par ailleurs, vous noterez que les titres des graphiques changent automatiquement. Nous vous expliquons comment faire dans cet article : Excel: Créer des titres de graphiques dynamiques.

Voilà, vous connaissez maintenant la fonction Sumproduct dans Excel !

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Êtes-vous aussi bon que vous le pensez avec Excel ? Faites le test ! Vous pourrez ensuite suivre la formation Excel – Mise à niveau qui peut servir de mise à niveau pour les professionnels qui utilisent déjà Excel dans le cadre de leurs fonctions et qui désirent aller plus loin.

 

Voici quelques commentaires d’apprenants ayant suivi la formation Excel – Mise à niveau :

 

 

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

8 réflexions sur “Excel: Sumproduct, un truc puissant dont vous ne pourrez plus vous passer”

  1. Par le passé, j’ai lu que le compilateur Excel était plus performant dans ces situations en utilisant les paramètres séparés plutôt qu’en un seul paramètre.
    Ceci serait donc plus performant comme syntaxe:
    =SUMPRODUCT(–(‘Liste principale’!$B$2:$B$1901=$B$14);–(‘Liste principale’!$C$2:$C$1901>=$B19);–(‘Liste principale’!$C$2:$C$1901<$B20);’Liste principale’!$D$2:$D$1901)
    Je devrai faire des tests pour m'assurer de cette prémisse, mais je ne serais pas surpris que ce soit réellement plus rapide au niveau de la logique de programmation.

  2. Bonjour,

    Nous pouvons désormais utiliser la fonction SUM.IFS dans Excel 2010 pour faire la somme de données prenant en compte plusieurs critères!

    En espérant que cela vous soit utile.

  3. Merci beaucoup l’article m’a beaucoup aidé à monter un plan de charge prévisionnel d’une équipe pour mon client. J’ai par contre utilisé la fonction SOMME.SI.ENS dans mon cas. J’ai ajouté également un graphique pour mettre en évidence les pics de surcharge d’activité avec un graphique en aires à partir d’un certain taux. En tout cas ajouter toutes les dates du calendrier puis faire des additions pour en tirer un graph… lumineux !
    Raphael

  4. Bonjour, j’essaie de créer un fichier de suivi de masse salariale qui va alimenter un PnL analytique.
    Pour ce faire j’ai besoin de faire une somme à double condition mais avec un critère en colonne et l’autre en ligne. Est ce possible?

    D’avance merci.

    64110 64130 64110
    Salaire de base primes IJSS

    Accueil Salarié X 10 1 0
    Maintenance Salarié Y 10 0 1
    Accueil Salarié Z 10 2 1

    je souhaite faire la somme des salariés de l’accueil en 64110.

    Merci de votre aide!

    Benoît

      1. Bonjour et merci de votre réponse.
        Cependant, je crois que cet exemple ne répond pas tout à fait à ma question.
        En effet dans l’article, l’utilisation de sommeprod se fait au travers de conditions qui s’applique sur les colonnes.

        =SUMPRODUCT((‘Liste principale’!$B$2:$B$1901=$B$14)*(‘Liste principale’!$C$2:$C$1901>=$B19)*(‘Liste principale’!$C$2:$C$1901<$B20);’Liste principale’!$D$2:$D$1901)

        Exemple, l'argument " ‘Liste principale’!$B$2:$B$1901=$B$14 " renverra 1 si la condition est vrai. Cet argument recherche la valeur de B14 dans la colonne B2:B1901.

        Et l'ensemble des arguments est recherché en colonne.

        Ce que je cherche à faire c'est par exemple une formule du type :

        =SOMMEPROD((G1:AH1="641")*(A3:A133="XYZ");G3:AH133)

        Avec un premier argument recherchant une condition sur la ligne 1 et un deuxième argument recherchant une condition sur la colonne A et si les 2 conditions sont respectées faire la somme de la matrice G3:AH133

        Peut-être est ce impossible?

        merci de votre aide en tout cas.

        Benoît

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Retour en haut