14. Exercice - Fonctions NB.SI, NB.SI.ENS, SOMME.SI et SOMME.SI.ENS
Prérequis
Il convient de lire le cours Fonctions Excel jusqu'au chapitre Fonctions NB.SI, NB.SI.ENS, SOMME.SI et SOMME.SI.ENS avant d'entamer cet exercice.
Question
Soit l’extrait de feuille de calcul représentant une liste d'abonnés d'une association :
Question 1
A une autre feuille du même classeur, est calculé le nombre d'abonnées par ville d'une part et par ville et par an d'autre part :
- Ecrire la formule en B3 permettant de calculer le nombre d'adhérents par ville à partir de la liste de la 1ère feuille. La plage B3:B9 doit être remplie par recopie incrémentée de la formule en B3.
- Ecrire la formule en C3 permettant de calculer le nombre d'adhérents par ville et par an à partir de la liste de la 1ère feuille. La plage C3:F9 doit être remplie par recopie incrémentée de la formule en C3.
Question 2
A une autre feuille du même classeur, est calculé la somme de dons par ville d'une part et par ville et par secteur d'autre part :
- Ecrire la formule en B3 permettant de calculer la somme de dons par ville à partir de la liste de la 1ère feuille. La plage B3:B8 doit être remplie par recopie incrémentée de la formule en B3.
- Ecrire la formule en C3 permettant de calculer la somme de dons par ville et par secteur d'activité à partir de la liste de la 1ère feuille. La plage C3:H8 doit être remplie par recopie incrémentée de la formule en C3.
Vous pouvez télécharger le fichier pour cet exercice ici.
Indications Exercice - Fonctions NB.SI, NB.SI.ENS, SOMME.SI et SOMME.SI.ENS
Question 1
Solution :
Formule en B3 de la feuille 2, à copier sur la plage B3:B9
=NB.SI(Feuil1!H$2:H$1475; A3)
Formule en C3 de la feuille 2, à copier sur la plage C3:F9
=NB.SI.ENS(Feuil1!$H$2:$H$1475; $A3; Feuil1!$I$2:$I$1475; C$2)
Formule en I2 de la feuille 1, à copier sur la plage I2:I1475
=ANNEE(F2)
Explications :
En ce qui concerne la formule en B3 de la feuille 2, la fonction NB.SI compte les occurrence de la valeur en A3 soit "Paris" dans la plage H2:H1475 de la feuille 1. La référence absolue est nécessaire pour le numéro de ligne et facultative pour le numéro de colonne car la recopie se fera verticalement.
En ce qui concerne la formule en C3 de la feuille 2, nous avons 2 critères qui sont la ville et l'année. Nous devons donc faire appel à la fonction NB.SI.ENS.
D'autre part, puisque la feuille 1 ne contient pas l'année d'adhésion des abonnées, nous avons ajouté cette information à la colonne I. La fonction ANNEE donne l'année pour la date donnée en argument, soit la date d'adhésion inscrite en colonne F.
Question 2
Solution :
Formule en B3 de la feuille 3, à copier sur la plage B3:B8
=SOMME.SI(Feuil1!H$2:H$1475; A3; Feuil1!G$2:G$1475)
Formule en C3 de la feuille 3, à copier sur la plage C3:H8
=SOMME.SI.ENS(Feuil1!$G$2:$G$1475; Feuil1!$H$2:$H$1475; $A3; Feuil1!$E$2:$E$1475; C$2)
Explications :
En ce qui concerne la formule en B3 de la feuille 3, la fonction SOMME.SI compte la somme des valeurs en colonne G dont les valeurs correspondantes en colonne H sont égaux à la valeur en A3 de la feuille 3 soit "Paris". La référence absolue est nécessaire pour le numéro de ligne et facultative pour le numéro de colonne car la recopie se fera verticalement.
En ce qui concerne la formule en C3 de la feuille 3, nous avons 2 critères qui sont la ville et l'année. Nous devons donc faire appel à la fonction SOMME.SI.ENS.
Vous pouvez télécharger le fichier rempli avec ces formules ici.
Commentaires des lecteurs
