OKJobs Article OKJobs SOMME.SI .ENS

Le contrôleur de gestion et Excel sont des inséparables, le maîtriser reste une étape cruciale pour la réactivité et la fiabilité des activités de traitement de données, d’analyse et de simulations.

S’il existe une fonction trop peu utilisée à mon goût, c’est la fonction SOMME.SI.ENS, et pourtant elle peut faire gagner énormément de temps. Elle peut remplacer par exemple l’utilisation d’un tableau croisé dynamique + RechercheV, ou Sous-totaux + RechercheV, mais aussi d’avoir des états dynamiques.

C’est pour cela qu’elle ouvre le bal de cette nouvelle catégorie !

L’aide officiel sur cette fonction se trouve par ici.

Définition :

La fonction SOMME.SI.ENS permet de sommer les valeurs d’une plage de données, en fonction d’un ou plusieurs critères.

En anglais : SUMIFS.

Syntaxe :

SOMME.SI.ENS (Plage_à_sommer ; plage_1 ; critère_1 ; plage_2 ; critère_2 ; …)

  • Plage à sommer (Obligatoire) : les cellules à additionner quand les critères sont vérifiés
  • Plage 1 (Obligatoire) : Les cellules dans lesquels chercher le critère
  • Critère 1 (Obligatoire): Le critère à chercher
  • Plage/Critère 2 (Facultatif)

Exemples :

Tableau à copier/coller sur Excel sur la cellule A1 si vous voulez tester :

Produit IDVilleCA HT
GaufretteRabat1
BiscuitRabat10
CakeRabat100
CookiesRabat1000
GénoiseRabat10000
GaufretteCasablanca1
BiscuitCasablanca10
CakeCasablanca100
CookiesCasablanca1000
GénoiseCasablanca10000
GaufretteMarrakech1
BiscuitMarrakech10
CakeMarrakech100
CookiesMarrakech1000
GénoiseMarrakech10000
  • Utilisation en Colonne avec 1 critère

Je veux connaître le total CA fait sur les Biscuits :OKJobs Exemple 1 SOMME.SI .ENS OKJOBS

La fonction va donc sommer toutes les valeurs de la colonne C, dès qu’elle trouve que la cellule correspondante de la colonne A est égale à la cellule E7 (on peut mettre directement “Biscuit” dans le critère 1 au lieu de la cellule E7 si on ne cherche pas à avoir une fonction dynamique)

Si sur certains exemples, vous ne prenez pas toute la colonne comme plage, il faut faire en sorte que la dimension de la plage à sommer, et de toutes les plages (1,2 ..Etc) soient de la même dimension, sinon erreur :

OKJobs Exemple 1.2 SOMME.SI .ENS OKJOBS

La plage à sommer va de la ligne 2 à 16, par contre la plage du critère 1 va de la ligne 2 à 18.

  • Utilisation en Colonne avec 2 critères

OKJobs Exemple 2.1 SOMME.SI .ENS OKJOBS

Sur cet exemple là, on a utilise le critère du produit et de la ville. Il ne trouve qu’une seule valeur correspondante ce qui donne pour Cookies/Rabat = 1000.

  • Utilisation en Ligne avec 2 critères

La même logique s’applique pour les lignes aussi, soit prendre toute la ligne, soit une plage de la même dimension pour ne pas avoir d’erreurs.

OKJobs Exemple 2.2 SOMME.SI .ENS OKJOBS 1

Ici on cherche à sommer le CA HT du produit Gaufrette vendu dans la ville de Rabat (1+100).

  • Bonus : Utilisation Matricielle de SOMME.SI.ENS

Maintenant prenons l’exemple où on veut sommer le CA des Cookies et des Cakes sur une même cellule.

Option 1 : Utiliser deux SOMME.SI.ENS :

OKJobs Exemple 3...1 SOMME.SI .ENS OKJOBS

 

Option 2 : Utiliser une SOMME.SI.ENS Matricielle :

Une fonction matricielle permet en quelques sortes d’intégrer la logique des boucles dans une formule.

Voici la syntaxe à utiliser pour sommer la SOMME.SI.ENS :

OKJobs Exemple 3...2 SOMME.SI .ENS OKJOBS

 

Que signifie cette formule :

  • Tout d’abord, les différentes variables qu’on veut sommer doivent être intégrées dans des guillemets {“Cookies”;”Cake”}
  • Il faut insérer la fonction SOMME.SI.ENS à l’intérieur d’une somme, car on désire sommer le résultat de SOMME.SI.ENS pour Cookies et pour Cake
  • Avant de valider la formule, il faut appuyer simultanément sur Ctrl+Shift+Entrée, et non Entrée uniquement. Excel reconnaît que vous voulez insérer une formule matricielle, il va insérer toute votre formule autour de guillemet comme vous le constatez sur le screenshot {=SOMME..etc}. Si vous ne retrouvez pas les guillemets, c’est que la manipulation Ctrl+Shirt+Entrée n’a pas été correctement faite, revenir dans la formule et la refaire.

 

J’espère que cet article vous a été utile.

N’hésitez pas à le partager autour de vous et à nous dire en commentaire quelles sont les utilisations que vous faites de cette fonction, car il y en a vraiment plein ! 🙂

Et pour apprendre à faire de puissantes recherches matricielles sur Excel avec les fonctions INDEX + EQUIV, cet article est pour vous.

Khalid – OKJobs

(Cet article a été lu 820 fois)

Khalid est un manager contrôle de gestion et un grand passionné du recrutement. Il a fondé OKJobs pour accompagner les entreprises à pouvoir trouver, attirer et fidéliser les meilleurs talents en contrôle de gestion. Il consacre son temps libre à l'accompagnement de chercheurs d'emploi et au partage de connaissances sur la recherche d'emploi et le contrôle de gestion.