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.
.
Pour consulter d’autres articles traitant d’Excel, vous pouvez consulter cet ARCHIVE EXCEL.
..
Follow @mon_cher_watson
.
Pour recevoir les articles de Mon Cher Watson par courriel, cliquez sur :
.
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.