Excel: Un truc puissant dont vous ne pourrez plus vous passer

Publié le 29 août 2011
par Sophie Marchand M.Sc., CPA, CGA, MVP

Excel: Un truc puissant dont vous ne pourrez plus vous passer

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 (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!

.

Dans l’exemple ci-dessous, nous avons un fichier à deux onglets. 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). 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.

.

.

Dans l’onglet “Occupation employés”, 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.

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.

.

.

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.

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 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).

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)

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.

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

.

.

Nous sommes donc 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.

.

.

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

.

Vous noterez que les titres des graphiques changent automatiquement. Nous expliquerons comment procéder pour créer des titres de graphiques dynamiques dans un prochain article.

.

CFO-Masque_Formations-en-ligne_FB Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.  

Découvrez quelles formations vous conviennent

 

8 réflexions sur “Excel: 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 de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Scroll to Top