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

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

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 :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

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 :

Excel formation - SOMME - 01

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.

Excel formation - SOMME - 02

 

   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.

Excel formation - SOMME - 03

 

   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.

Excel formation - SOMME - 04

 

   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) 

Excel formation - SOMME - 05

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 :

Excel formation - SOMME - 06

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

Excel formation - SOMME - 07

 

   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 :

Excel formation - SOMME - 08

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

Excel formation - SOMME - 09

Ainsi, chacune des lignes sera sommée automatiquement :

Excel formation - SOMME - 10

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 :

Excel formation - SOMME - 11

Il faudra alors diviser ce résultat par deux :

Excel formation - SOMME - 12

 

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.

Excel formation - SOMME - 13

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.

Excel formation - SOMME - 14

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.

Excel formation - SOMME - 15

 

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) 

Excel formation - SOMME - 16

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

Excel formation - SOMME - 17

 

 

 

 



Articles qui pourraient vous intéresser

Les 8 (+1 incroyable) fonctions Excel à maîtriser absolument en 2024
Percez les secrets de la fonction SOMME (SOMME.SI/SOMMEPROD) et des ces déclinaisons pour boostez votre productivité sur Excel ?
TOP3 des méthodes pour extraire des données par date dans Excel
Comment identifier la date la plus ancienne ou la plus récente en fonction de critères sur Excel ?
Comment calculer une moyenne sans tenir compte des zéros sur Excel ?
Comment fusionner des tableaux, les trier et supprimer les doubles avec une seule formule Excel ? (ASSEMB.V et ASSEMB.H)
Arrêtez d'imbriquer des fonctions SI() sur Excel ! Découvrez SI.MULTIPLE() et SI.CONDITIONS()
Comment obtenir efficacement le numéro de semaine d’une date donnée sur Excel ?
Comment identifier la cellule qui se répète le plus de fois sur Excel ? (Fonction MODE)
Comment améliorer votre gestion de données avec les fonctions base de données d’Excel ?
Créer une somme intelligente et dynamique sur Excel sans VBA
Quelle fonction permet de lister le nom des feuilles de calcul Excel sans VBA ?

Contact - Plan du site - À propos - Contact

© Excelformation.fr 2018-2024 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.