Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?

Gérer efficacement un budget, ou n'importe quel autre type de données est une tâche essentielle à maîtriser sur Excel.

Pour cela, plusieurs fonctions se distinguent par leur utilité et leur puissance dans la manipulation et l'analyse des données. En plus de la célèbre fonction SOMME, qui nous permet de calculer des totaux en toute simplicité, nous avons également à notre disposition des évolutions plus puissantes, comme les fonctions SOMME.SI ou encore SOMMEPROD.

Dans ce tutoriel, je vais vous présenter ces différentes fonctions, en illustrant leurs utilisations au travers d’exemples concrets.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Niveau 1 – La fonction SOMME

Calculer le total des dépenses est crucial pour gérer le budget de l'entreprise.

Pour cela, la fonction de base à maîtriser est la fonction SOMME, laquelle nous permet en effet d'ajouter des valeurs numériques présentes dans une plage de cellules.

Pour illustrer ce tutoriel, nous allons pouvoir utiliser l’exemple suivant :

Dans cet exemple, nous retrouvons les dépenses d’une entreprise en euros pour les postes de "Transport", "Repas" et "Fournitures" sur trois jours différents.

À partir de ce tableau, nous allons souhaiter extraire un certain nombre d’informations. Les résultats de ces formules vont ensuite nous permettre d'avoir une vision claire de nos différentes dépenses et de gérer le budget plus efficacement.

   1.1. Étape 1 : Calcul du coût des repas

Pour commencer, voyons comment effectuer une somme très simple, laquelle va nous permettre de déterminer le montant total de nos dépenses de repas du Lundi au Mercredi.

Pour cela, suivez ces étapes :

- Nous commençons par sélectionner la cellule B14, là où nous voulons voir le résultat du coût total des repas.

- Ensuite, nous saisissons le signe égal « = » pour qu’Excel comprenne que nous sommes en train de saisir une formule et non du texte.

- Puis, nous saisissons la formule « =SOMME(C9:C11) ». Cette formule utilise la fonction SOMME pour additionner toutes les valeurs de la colonne C entre les lignes 9 et 11. Nous pouvons saisir les coordonnées correspondantes à la main, ou les sélectionner directement à l’aide de la souris.

- Enfin, nous appuyons sur la touche [Entrée] pour obtenir le résultat. La cellule B14 affichera maintenant le total de nos dépenses de repas pour les trois jours.

   1.2. Étape 2 : Calcul du coût total des dépenses

Il suffira ensuite de sélectionner des cellules différentes pour obtenir les résultats correspondants.

Ainsi, pour obtenir le coût total de nos dépenses sur les trois jours dans toutes les catégories (Transport, Repas, Fournitures), nous utiliserons plutôt la plage des cellules B9 à D11, c’est-à-dire sur les trois lignes et les trois colonnes du tableau :

 =SOMME(B9:D11) 

Cette formule somme toutes les valeurs dans la plage allant de B9 à D11, ce qui couvre toutes nos dépenses pour chaque catégorie et pour chaque jour.

   1.3. Étape 3 : Calcul du Coût des Fournitures

Pour additionner nos dépenses de fournitures sans nous soucier du nombre exact de lignes, nous pouvons encore sélectionner directement l’intégralité des cellules qui compose celle-ci.

Ainsi, nous pouvons saisir la formule suivante :

 =SOMME(D:D) 

Ici, au lieu de spécifier une plage de plusieurs cellules, nous indiquons simplement la colonne D, ce qui demande à Excel de sommer toutes les valeurs de la colonne D, peu importe combien de lignes il y a.

Pour saisir directement les coordonnées de cette colonne, nous pouvons simplement cliquer sur la lettre correspondante.

   1.4. Étape 4 : Calcul du Coût pour un Jour Spécifique (Mardi)

Bien entendu, ce que nous pouvons mettre en place sur une colonne entière est également transposable sur une entière. Ainsi, si nous avons besoin de connaître le total de nos dépenses pour seulement le Mardi, suivons ces instructions, il suffira de sélectionner la ligne 10 :

 =SOMME(10:10) 

Comme nous pouvons alors le constater, la formule SOMME va traiter ici uniquement les valeurs numériques, en ignorant les textes.

Cela dit, il faut garder en tête quelques informations importantes :

- Si la ligne contient des erreurs, la fonction SOMME renverra elle-même une erreur :

- Toutes les cellules de la ligne (ou la colonne) seront intégrées le résultat obtenu par la fonction SOMME, il faut donc être vigilant aux cellules qui pourraient se trouver en dehors de la table. Ainsi, cette méthode ne permettra pas d’obtenir le cout des transports, car les résultats calculés sont sur la même colonne (ici, cela génère même une référence circulaire, car le résultat de la formule dépend également de celui de la cellule B18 dans laquelle la formule est saisie) :

   1.5. Sommes automatiques

Dans Excel, il est possible d’utiliser la fonctionnalité de somme automatique pour insérer automatiquement la fonction SOMME avec les arguments déjà insérés.

Tout d'abord, sélectionnons les lignes contenant les données, mais également la ligne vide située en dessous, dans laquelle nous souhaitons insérer les totaux :

Ensuite, nous nous rendons dans le menu Formules > Somme automatique :

Ainsi, chacune des lignes sera sommée automatiquement :

Pour gagner du temps, plutôt que d’utiliser l’icône du menu Formules, nous pouvons également utiliser le raccourci clavier [Alt]+[=].

Attention toutefois, comme je vous l’expliquais un peu tôt, maintenant que la cellule D12 contient le total des dépenses de fournitures, le résultat de la cellule B16 va inclure cette cellule et retourner une valeur erronée :

Il faudra alors diviser ce résultat par deux :

2. Niveau 2 – La fonction SOMME.SI

La fonction SOMME que nous venons d’utiliser dans la partie précédente permet d’additionner les valeurs d'une plage de cellules donnée. Par contre, lorsque nous souhaiterons additionner les valeurs dans une plage de cellules uniquement si elles répondent à certains critères spécifiés, nous devrons utiliser une fonction un peu plus sophistiquée : SOMME.SI.

La fonction SOMME.SI attend trois arguments, dont deux sont obligatoires :

 =SOMME.SI(Plage; Critère; [Somme_Plage]) 

- 1. Plage : Il s'agit du premier argument obligatoire. Cette plage de cellules spécifie les valeurs que nous souhaitons évaluer pour déterminer quelles valeurs doivent être additionnées.

- 2. Critère : C'est le deuxième argument obligatoire. Le critère est une expression qui spécifie les conditions que les valeurs doivent remplir pour être incluses dans le calcul.

- 3. [Somme_Plage] : C'est le troisième argument, facultatif. Cette plage de cellules spécifie les critères auxquels les valeurs doivent répondre pour être incluses dans le calcul. Si ce troisième argument est omis, la fonction considérera que les valeurs de la plage spécifiée dans le premier argument doivent satisfaire les critères spécifiés dans le deuxième argument.

Pour comprendre son utilisation, nous allons pouvoir utiliser un second exemple dans lequel sont reprises les ventes de trois produits, réalisées par chaque représentant d’une entreprise.

Pour chaque représentant, nous voulons obtenir le montant des ventes respectives.

Pour ce faire, nous utiliserons la fonction SOMME.SI de la manière suivante :

 =SOMME.SI($A$22:$A$27;A32;$C$22:$C$27) 

Ici :

- $A$22:$A$27 : Il s'agit d'une plage de cellules contenant les critères sur lesquels la fonction SOMME.SI doit se baser pour additionner les valeurs. Les signes dollar ($) avant la lettre et le chiffre ($A$ et $22:$A$27) signifient que cette plage est absolue. En d'autres termes, lorsque vous faites référence à cette plage dans votre formule et que vous la copiez ou la déplacez vers une autre cellule, la référence ne changera pas. Dans cet exemple, cette plage contient les noms des représentants, puisque nous souhaitons additionner les ventes en fonction de ces noms. Il est possible de saisir directement ces symboles dollars, ou pour gagner du temps, nous pouvons également utiliser la touche [F4] du clavier.

- A32 : Il s'agit du critère spécifique que nous souhaitons utiliser pour filtrer les valeurs à additionner. Dans cet exemple, s’agit du nom du premier représentant spécifique situé dans la cellule A32. Cette référence est relative, ce qui signifie que lors de la copie ou du déplacement de la formule vers une autre cellule, la référence sera ajustée en conséquence. Ainsi, lorsque nous copierons la formule vers la cellule B32, elle fera référence à la cellule B32 pour le critère.

- $C$22:$C$27 : Il s'agit d'une autre plage de cellules contenant les valeurs à additionner, en fonction du critère spécifié. Comme pour la première plage, les signes dollar ($) avant la lettre et le chiffre ($C$ et $22:$C$27) signifient que cette plage est absolue. Ainsi, lorsque vous copiez ou déplacez la formule, la référence à cette plage ne changera pas.

Cette formule SOMME.SI permet donc de récupérer rapidement les valeurs de la colonne C (ventes) pour les lignes où les valeurs de la colonne A (représentants) correspondent au nom spécifié dans la cellule A32.

Il ne reste plus qu’à étendre la formule sur les deux autres cellules pour obtenir le montant des ventes réalisées par chaque commercial.

3. Niveau 3 – La fonction SOMME.SI.ENS

Pour continuer notre tutoriel, passons maintenant à une fonction Excel encore un peu plus avancée, qui permet cette toujours de calculer une somme conditionnelle, mais cette fois-ci basée sur plusieurs critères : la fonction SOMME.SI.ENS.

Toujours sur l’exemple précédent, supposons maintenant que nous souhaitions obtenir le montant des ventes réalisées par un représentant donné, pour un produit en particulier.

Nous utilisons alors la formule suivante :

 =SOMME.SI.ENS($C$23:$C$28;$A$23:$A$28;A38;$B$23:$B$28;B38)  

Où :

- $C$23:$C$28 désigne la plage de cellules contenant les valeurs à additionner (les ventes).

- $A$23:$A$28 est la première plage de critères qui correspond aux noms des représentants dans notre tableau.

- A38 est le critère spécifique pour le représentant dont nous voulons calculer les ventes. Dans notre exemple, cela correspond à "Edmond Prochain".

- $B$23:$B$28 est la deuxième plage de critères qui correspond aux produits.

- B38 est le critère spécifique pour le produit concerné. Ici, c'est "Produit1".

Comme vous pouvez le constater, le piège lorsque nous passons de la fonction SOMME.SI à SOMME.SI.ENS, c’est que l’argument Somme_Plage passe de la dernière à la première position dans la liste des arguments.

4. Niveau 4 – La fonction SOMMEPROD 

La dernière fonction que nous allons découvrir dans cette vidéo est la fonction SOMMEPROD. Il s’agit qu’une fonction très puissante d'Excel qui permet de calculer la somme des produits de plusieurs plages ou tableaux.

Elle est donc particulièrement utile pour effectuer des calculs complexes où nous devons multiplier des valeurs entre elles avant de les additionner, et cela, même avec des conditions.

Pour illustrer son utilisation, prenons un nouvel exemple où nous avons une liste de ventes par représentant et par produit, et cette fois, nous voulons calculer les ventes totales en prenant en compte une remise spécifique pour chaque produit.

La formule est alors la suivante :

 =SOMMEPROD(C23:C28;1-E23:E28) 

Ici :

- « C23:C28 » représente la plage de cellules des ventes que nous voulons additionner.

- « 1-E23:E28 » représente la plage de pourcentages de remise pour chaque produit. En soustrayant ces valeurs de 1, nous obtenons le pourcentage du prix de vente qui est effectivement payé après remise. Sans cette opération, le résultat obtenu aurait été le montant total de la remise.

Notez que cette formule aurait été pu être saisie de la manière suivante :

 =SOMMEPROD((C23:C28)*(1-E23:E28)) 

Dans ce cas, l'opérateur * est utilisé pour multiplier les ventes par le pourcentage payé après remise pour chaque ligne.

Dans ces deux version, SOMMEPROD prend ensuite tous les produits de ventes et pourcentages payés après remise.

L’avantage de la deuxième méthode, c’est que celle-ci va maintenant nous permettre d’ajouter des conditions, en les saisissant entre parenthèse, puis en multipliant aux autres paramètres. Ainsi, pour obtenir le montant des ventes remisées correspondantes au Produit1, la formule devient :

  =SOMMEPROD((C23:C28)*(1-E23:E28)*(B23:B28="Produit1"))