sum

Progresser en Excel (2) : les sommes

24/09/2016

Nous avions vu dans l’article précédent de cette série qu’il était possible de sommer un tableau rapidement en utilisant le raccourci « Alt + = ». Ici, nous allons voir quelques autres astuces pour gagner du temps sur vos sommes, et savoir quelle formule utiliser efficacement !

D’abord, une excellente habitude à prendre est d’ajouter une ligne vide supplémentaire en bas de votre somme, avant le résultat et de la prendre en compte dans la formule. Elle ne modifiera pas le résultat, mais vous permet d’insérer des lignes à votre somme sans avoir à modifier votre formule. Très pratique quand le nombre de lignes à sommer est régulièrement modifié !

Il vous suffit de sélectionner la ligne de résultat et d’insérer une ligne au-dessus de celle-ci en appuyant sur la touche « Ctrl » et « + ».

art2Picture1

Si un jour vous devez rajouter une ligne, il vous suffit d’insérer une ligne supplémentaire à partir de la ligne que vous venez de rajouter, et elle sera prise en compte dans votre somme !

art2Picture2

Une fois que vous connaissez les raccourcis claviers nécessaires pour gagner du temps, ainsi que les bonnes pratiques de construction, reste à savoir comment optimiser l’utilisation des formules de calcul !

Une simple formule de somme ne suffit pas toujours. Prenons un exemple simple : sur le tableau ci-dessous, on retrouve des chiffres de vente par année et par vendeur. Plutôt que de sommer le total des ventes indépendamment des autres paramètres, ou pire, de changer la plage sur laquelle nous appliquons notre somme à chaque fois que nous voulons produire une analyse différente, nous pouvons utiliser SOMME.SI.

art2Picture3

La syntaxe est la suivante : SOMME.SI(plage ; critère ; sommeplage). En Anglais, la fonction est SUMIF(), et a la même syntaxe.

Pour la création de la liste de validation, référez-vous à cet autre article.

art2Picture4

art2Picture5

Ici, nous obtenons la somme de toutes les ventes effectuées par la personne sélectionnée dans notre liste de noms.

art2Picture6

Ici, toutes les ventes effectuées dans l’année sélectionnée.

Il est aussi possible de mettre plusieurs conditions sur une somme, en utilisant la formule SOMME.SI.ENS(Sommeplage ; plage1 ; critère1 ; … ; plage X ; critère X), qui correspond à la fonction SUMIFS() en Anglais, avec la même syntaxe.

art2Picture7

On recherche donc ici toutes les ventes faites par Nicolas en 2015, en l’occurrence 0, vu que les seules ventes de 2015 viennent de Jeanne.

SOMMEPROD() est une fonction qui permet de retourner la somme du produit de deux plages ou tableaux de même taille. Prenons un tableau donnant le salaire horaire de chaque employé ainsi que son nombre d’heures travaillées dans le mois.

art2Picture8

Pour passer du coût unitaire de chaque élément à un coût total, il faut deux étapes (coût unitaire * nb d’unités, pour chaque unité différente, qu’on additionne).

art2Picture9

Ici, au lieu de multiplier chaque salaire horaire par le nombre d’heures travaillées, puis de les additionner entre eux pour obtenir la masse salariale totale, il suffit de faire un SOMMEPROD (SUMPRODUCT() en Anglais).

art2Picture10

L’un des principaux avantages de cette formule est qu’elle est très versatile : bien utilisée, elle peut aussi servir à ajouter des divisions entre elles, ou à n’ajouter que les opérations correspondant à une série de conditions (comme nous le permettait SOMME.SI.ENS avec l’addition).

En effet, le point-virgule qui sépare les deux critères correspond par défaut à un « * » qui marque la multiplication, notre total du tableau aurait ainsi pu être écrit : =SOMMEPROD(C20:C23*D20:D23) avec le même résultat final.

On pourrait ainsi déduire le nombre total d’heures travaillées en faisant un SOMMEPROD « adapté » qui fait la division de chaque salaire mensuel par son salaire horaire correspondant.

art2Picture11

La formule calcule ici : 1120/8 + 1400/10 + 1872/12 + 2100/15 = 576

Si l’on souhaite retrouver seulement le nombre d’heures travaillées par une personne en particulier, par exemple Catherine, il suffit de multiplier notre total par cette condition :

art2Picture12

La formule ne fait alors que la somme des divisions correspondant à la ligne sur laquelle se trouve le prénom « Catherine », soit 1872/12 = 156. Comme pour SOMME.SI.ENS, nous aurions pu baser notre critère sur une liste de validation, si jamais nous avions besoin de le changer régulièrement.