Médiaforma

All posts in excel-pratique

La fonction SOMME.SI.ENS() permet de faire la somme des cellules d’une colonne lorsque les cellules correspondantes dans une autre colonne respectent un ou plusieurs critères.

Pour bien comprendre comment utiliser cette fonction, nous allons raisonner sur une pyramide des âges :

Les données numériques s’étalent de la ligne 7 (année de naissance 2017) à la ligne 106 (année de naissance 1918).

Voici la syntaxe de la fonction SOMME.SI.ENS() :

=SOMME.SI.ENS(plage1;plagecritère1;critère1;plagecritère2;critère2;…)

Où :

  • plage1 est la plage qui contient les cellules à sommer.
  • plagecritère1 est la plage sur laquelle le critère critère1 sera appliqué.
  • Plagecritère2 est la plage sur laquelle le critère critère2 sera appliqué.
  • .

Nous allons utiliser la fonction SOMME.SI.ENS() pour calculer le nombre de femmes entre 20 et 30 ans. Voici la formule utilisée :

=SOMME.SI.ENS(C7:C106;B7:B106;">=20";B7:B106;"<=30")

Calculons maintenant le nombre d’hommes au-delà de 60 ans. Voici la formule utilisée :

=SOMME.SI.ENS(D7:D106;B7:B106;">=60")

Remarque

En utilisant la même syntaxe, vous pourriez utiliser la fonction NB.SI.ENS() pour calculer le nombre de lignes qui correspondent à un ou plusieurs critères, ou encore la fonction MOYENNE.SI.ENS() pour calculer la moyenne des cellules qui correspondent à un ou plusieurs critères.


Cette section vous montre comment effectuer un calcul en fonction d’une condition. Nous allons partir de la feuille de calcul suivante :

Nous allons remplir les cellules B12 et B13 en fonction des valeurs de la plage B2:B10.

Lorsqu’une valeur de cette plage est positive, elle intervient en tant qu’entrée. Lorsqu’elle est négative, elle intervient en tant que sortie. La cellule B12 contiendra la somme de toutes les valeurs positives de la plage B2:B10, et la cellule B13 la somme de toutes les valeurs négatives de cette même plage.

Pour arriver à ces résultats, vous allez utiliser la fonction SOMME.SI() dont voici la syntaxe :

=SOMME.SI(plage1; critère; plage2)

plage1 est la plage sur laquelle s’applique la fonction SOMME.SI(), critère est le critère appliqué aux cellules de la plage. Le paramètre plage2 est facultatif. Lorsqu’il n’est pas précisé, les valeurs de plage1 qui respectent le critère sont ajoutées. Lorsqu’il est précisé, les valeurs de plage2 dont les valeurs de plage1 qui respectent le critère sont ajoutées.

Cliquez dans la cellule B12 et entrez la formule suivante :

=SOMME.SI(B2:B10;">0")

Le premier paramètre désigne la plage de cellules à analyser et le deuxième le critère de sélection des cellules. Le troisième paramètre n’est pas précisé car les valeurs à additionner sont celles de la plage précisée dans le premier paramètre.

Cliquez dans la cellule B13 et entrez la formule suivante :

=ABS(SOMME.SI(B2:B10;"<0"))

Cette formule est proche de la précédente, si ce n’est que la somme doit porter sur les valeurs négatives (deuxième paramètre) et que le résultat affiché dans la cellule B13 doit être positif, d’où l’utilisation de la fonction ABS() pour obtenir la valeur absolue du résultat.


Cette section va vous montrer comment renseigner une cellule lorsqu’une condition est vérifiée. Nous allons partir de cette feuille de calcul :

Le but est de recopier dans la colonne C les valeurs de la colonne B lorsqu’elles sont positives, et dans la colonne D les valeurs opposées de la colonne B lorsqu’elles sont négatives. Voici le résultat désiré :

Pour arriver à ce résultat, nous allons utiliser la fonction SI() dont voici la syntaxe :

=SI(test;valeur si vrai;valeur si faux)

test est un test logique, valeur si vrai est la valeur à afficher dans la cellule si le test est vrai et valeur si faux est la valeur à afficher dans la cellule si le test est faux.

Cliquez dans la cellule C2, puis entrez la formule suivante :

=SI(B2>0;B2;"")

Si la cellule B2 est positive, cette valeur est affichée dans la cellule C2. Sinon, c’est une chaîne vide qui est affichée.

Cliquez dans la cellule D2 et entrez la formule suivante :

=SI(B2<0;ABS(B2);"")

Si la cellule B2 est négative, sa valeur absolue est affichée dans la cellule D2. Sinon, c’est une chaîne vide qui est affichée.

Il ne vous reste plus qu’à utiliser la poignée de recopie pour copier la formule de la cellule C2 jusqu’à la cellule C10, puis la formule de la cellule D2 jusqu’à la cellule D10.

Pour aller plus vite, vous pouvez également sélectionner les cellules C2 et D2 et utiliser la poignée de recopie jusqu’à la cellule D10.


Il existe de nombreuses fonctions dédiées aux chaînes de caractères dans Excel. Vous les utiliserez pour remplir des cellules à partir d’autres cellules existantes. En voici quelques-unes :

Fonction Signification
MAJUSCULE() et MINUSCULE() Version majuscule et minuscule de la chaîne passée en argument
SUBSTITUE() Remplace un texte par un autre dans une chaîne
CHERCHE() Recherche une sous-chaîne dans une chaîne
NBCAR() Nombre de caractères de la chaîne passée en argument
CONCATENER() ou & Concatène les chaînes passées en argument

 

Pour illustrer ces fonctions, nous allons raisonner sur cette feuille de calcul :

Les colonnes E à H sont calculées à partir des colonnes A à D :

  • La colonne E est obtenue en assemblant les données de la colonne C et de la colonne D mise en majuscules.
  • La colonne F est obtenue en remplaçant la lettre “A” par la lettre “a” dans les données de la colonne A.
  • La colonne G recherche la sous-chaîne “er” dans les cellules de la colonne B. Elle affiche la position de cette sous-chaîne ou un code d’erreur si la sous-chaîne n’est pas trouvée.
  • La colonne H est une amélioration de la colonne G. Elle affiche trouvé si la sous-chaîne “er” est trouvée dans les cellules de la colonne B. Elle affiche non trouvé dans le cas contraire.

Voici les formules utilisées :

  • Cellule E2: =CONCATENER(C2;" ";MAJUSCULE(D2))
  • Cellule F2: =SUBSTITUE(A2;"A";"a")
  • Cellule G2: =CHERCHE("er";B2)

Comme vous le voyez, un code d’erreur s’affiche dans la colonne G lorsque la chaîne “er” n’est pas trouvée dans les cellules de la colonne B. Pour améliorer le comportement de la fonction CHERCHE(), vous allez tester la valeur renvoyée par cette fonction dans la fonction ESTERR(). Si une erreur est retournée (#VALEUR!), la chaîne “non trouvé” sera affichée. Sinon, la chaîne “trouvé” sera affichée. Rien de plus simple grâce à la fonction SI(). Voici la formule utilisée pour calculer la cellule H2 :

=SI(ESTERR(CHERCHE("er";B2));"non trouvé";"trouvé")

Pour compléter les cellules des lignes 3 à 92, sélectionnez les cellules E2 à H2 et utilisez la poignée de recopie jusqu’à la ligne 92.


Vous devez faire des calculs sur des dates ? Pour éviter que cela ne tourne au cauchemar, vous avez tout intérêt à utiliser les fonctions dédiées d’Excel. En voici quelques-unes :

 

Fonction Signification
AUJOURDHUI Date système
MAINTENANT() Date et heure système
JOURS() Nombre de jours entre deux dates
NO.SEMAINE() Numéro de la semaine

 

Pour bien comprendre l’utilisation de ces fonctions, nous allons raisonner sur un exemple :

La cellule E1 contient la date du jour. Elle est automatiquement mise à jour à l’ouverture du classeur grâce à la fonction AUJOURDHUI() :

Les cellules des colonnes D, E et F contiendront respectivement le numéro de la semaine de la colonne C, le nombre de jours entre la date courante et les cellules de la colonne C, et le nombre de semaines entre la date courante et les cellules de la colonne C.

Voici la formule utilisée pour calculer la cellule D4 :

=NO.SEMAINE(C4)

La cellule E4 doit contenir le nombre de jours entre la cellule E1 et la cellule C4. Pour pouvoir recopier rapidement la formule sur les cellules de la colonne E, vous devez utiliser une référence absolue à la cellule E1 :

=JOURS($E$1;C4)

La cellule F4 doit contenir le nombre de semaines entre la cellule E1 et la cellule C4. Tout comme pour la cellule E4, vous devez utiliser une référence absolue à la cellule E1 :

=TRONQUE(($E$1-C4)/7)

Comme il n’existe aucune fonction pour déterminer le nombre de semaines entre deux dates, un calcul est nécessaire. En soustrayant les deux dates, on obtient une différence en jours. En divisant cette valeur par 7, on obtient une valeur en semaines. Enfin, en prenant la troncature de cette valeur on obtient un nombre entier de semaines.

La cellule D4 est recopiée jusqu’à la cellule D12, la cellule E4 est recopiée jusqu’à la cellule E12, et la cellule F4 est recopiée jusqu’à la cellule F12. Voici le résultat :


De nombreuses fonctions mathématiques sont disponibles dans Excel. Voici les plus courantes :

Fonction Signification
NB() Nombre de cellules dans une plage
NBVAL() Nombre de cellules non vides dans une plage
MIN() Plus petite valeur d’une plage
MAX() Plus grande valeur d’une plage
NB.VIDE() Nombre de cellules vides dans une plage
MOYENNE() Moyenne des valeurs d’une plage

Pour voir comment utiliser ces fonctions, nous allons partir de cette feuille de calcul :

A titre d’exemple, nous allons calculer la somme des ventes mensuelles pour l’ensemble des commerciaux et la moyenne des ventes par trimestre pour chacun des commerciaux.

La somme des ventes mensuelles sera calculée dans les cellules C16, C17 et C18.

Sélectionnez les cellules C5 à C15. L’icône Analyse rapide s’affiche dans la partie inférieure droite de la sélection. Cliquez dessus :

Un panneau comportant cinq onglets s’affiche. Sélectionnez l’onglet Totaux et cliquez sur l’icône Somme :

La somme des cellules C5 à C15 s’affiche dans la cellule C16. Si vous cliquez sur cette cellule, la barre de formule contient ceci :

=SOMME(C5:C15)

La plage C5:C15 ayant été définie de façon relative dans la formule, vous pouvez recopier la formule dans les cellules D16 et E16. Pointez le carré noir en bas et à droite de la cellule C16. Le pointeur de la souris se transforme en un signe plus. Maintenez le bouton gauche de la souris enfoncé puis déplacez la souris vers la cellule E16 :

Relâchez le bouton gauche de la souris pour recopier la formule. Voici le résultat :

Vous allez maintenant calculer la moyenne des ventes par trimestre pour chacun des commerciaux. Sélectionnez les cellules C5 à E5. L’icône Analyse rapide s’affiche à droite de la sélection. Cliquez dessus. Un panneau comportant cinq onglets s’affiche. Sélectionnez l’onglet Totaux. Pointez l’icône Moyenne. Comme vous pouvez le voir, Excel calcule la moyenne des cellules sélectionnées par colonnes et l’affiche sur la ligne inférieure, ce qui n’est pas du tout l’effet recherché :

Cliquez sur la flèche à droite des six icônes. Cette fois-ci l’icône Moyenne donne bien le résultat attendu. Cliquez dessus :

Si vous cliquez sur la cellule F5, vous verrez qu’elle contient la formule suivante :

=MOYENNE(C5:E5)

Les cellules étant référencées de façon relative, vous pouvez utiliser la poignée de recopie pour obtenir les moyennes des autres commerciaux :


Dans la section précédente, vous avez calculé un prix TTC dans la cellule D2 avec la formule suivante :

=B2*(1+C2)

Ici, la référence aux cellules B2 et C2 est faite de façon relative.

Si vous copiez la cellule D2 dans la cellule D3 avec un simple copier-coller, la formule s’adaptera pour faire référence à la ligne 3. Cliquez sur la cellule D2 et cliquez sur l’icône Copier, dans le groupe Presse-papiers, sous l’onglet Accueil du ruban. Cliquez dans la cellule D3 et cliquez sur l’icône Coller, dans le groupe Presse-papiers, sous l’onglet Accueil du ruban. Comme vous le voyez, la formule s’adapte à la ligne 3 : les cellules B2 et C2 sont transformées en B3 et C3 :

Dans certains cas, il est important qu’une formule fasse référence aux cellules de façon absolue. Si cette formule est déplacée dans une autre cellule, elle restera inchangée. Selon l’effet recherché, il suffit d’insérer un $ devant les noms des colonnes et/ou des lignes dans la formule.

Raisonnons sur la formule relative de l’exemple précédent :

=B2*(1+C2)

Pour faire référence de façon absolue aux cellules dans la formule, vous utiliserez cette syntaxe :

=$B$2*(1+$C$2)

Ici, les colonnes B et C et la ligne 2 sont accédées de façon absolue.

Remarque

Lorsque vous êtes en train de définir une formule, vous pouvez appuyer sur la touche de fonction F4. Les signes $ seront automatiquement ajoutés à la référence en cours de frappe. Supposons que vous ayez saisi B2 dans la formule. Appuyez sur F4, la référence devient $B$2. Appuyez à nouveau sur F4. La référence devient B$2. Appuyez une troisième fois sur F4. La référence devient $B2. Appuyez une quatrième et dernière fois sur F4. La référence devient B2.

Avec un peu d’habitude, vous trouverez certainement la touche F4 très pratique.

Comme vous le voyez, le résultat du calcul est le même que précédemment :

Par contre, si vous copiez-collez la formule de la cellule D2 dans la cellule D3, elle ne s’adapte pas à la ligne 3. Le résultat est donc bien différent :