Link Search Menu Expand Document
Boîte à outils Excel

6.1 Quelques formules avancées


TABLE DES MATIÈRES


6.1.1 Additionner des cellules répondant à un critère

Il est possible d’additionner des cellules dans une plage donnée qui correspondent à un critère donné. Ce critère peut être un chiffre, un mot, la valeur d’une cellule ou encore une comparaison. Pour cela, il faut utiliser la fonction =SOMME.SI.

La fonction Excel =SOMME.SI a une syntaxe qui contient trois paramètres dont l’un est optionnel :

=SOMME.SI(plage de cellules; condition à remplir; plage à additionner)

  • La plage de cellules : Cet argument contient la plage de cellule à additionner et est obligatoire.
  • La condition à remplir : Cet argument contient la condition qui détermine quelle cellule sera prise en compte dans l’addition. L’argument peut contenir un nombre, une expression, une référence de cellule, une date ou encore une chaîne de texte. Cet argument est obligatoire.
  • Plage à additionner : Cellules réelles à ajouter, si vous voulez ajouter d’autres cellules que celles qui sont spécifiées dans l’argument plage. Cet argument est optionnel.

Découvrez ci-dessous les deux cas d’utilisation de la fonction SOMME.SI :

a. Additionner les nombres selon leur valeur

Ceci est l’utilisation la plus simple de la fonction SOMME.SI. Vous additionnez simplement des valeurs qui correspondent à une valeur ou à une comparaison. Par exemple la somme des cellules qui contiennent le chiffre 100. Ou la somme des nombres inférieurs à 1000, etc. Dans ce cas, vous avez besoin uniquement des deux premiers arguments de la fonction SOMME.SI, c’est à dire plage de cellules et condition à remplir. Le troisième argument (plage à additionner) ne sera pas utilisé.

image info

b. Additionner les nombres selon une valeur correspondante

Dans ce cas-ci, vous additionnez les valeurs qui correspondent à une condition qui se trouve dans une autre plage de cellules. Souvent, vous avez une colonne avec les valeurs à tester et une autre colonne avec des valeurs à additionner au cas où la condition est remplie. Vous avez donc besoin d’utiliser les trois arguments de la fonction SOMME.SI.

image info

À retenir

Même si la formule SOMME.SI est plutôt facile à utiliser, il convient de faire attention à certains points qui peuvent créer des erreurs de calcul :

  • L’argument contenant la condition de comptage (condition à remplir) doit impérativement se trouver entre guillemets, même s’il s’agit de conditions numériques (“5”) ou d’expresssions qui contiennent un opérateurs de comparaisons (“< 10”).
  • Si une des cellules à tester contient une erreur (#VALEUR!), la fonction SOMME.SI renverra comme résultat une erreur également.
  • Si vous voulez utiliser une condition textuelle qui utilise une longue chaîne de caractères (plus de 255 caractères), la fonction va renvoyer une erreur à la place du résultat. Mais il est possible de contourner cette limitation : il suffit de séparer le long texte en plus petites parties (avec moins de 255 caractères chacune) que l’on “recompose” ensuite grâce à l’opérateur de concaténation (&).

Exemple : = SOMME.SI(A1:A1000;”première partie” & “seconde partie”;B1:B1000)

6.1.2 Établir des comparaisons logiques

La fonction SI d’Excel permet d’évaluer une valeur (habituellement la valeur d’une cellule) selon une condition et de renvoyer une valeur différente selon si la condition est remplie ou pas.

La fonction =SI a une syntaxe qui contient trois paramètres :

=SI(Condition; Valeur si condition est remplie; Valeur si condition n’est pas remplie)

  • La condition : Elle compare deux valeurs l’une par rapport à l’autre grâce à un des opérateurs logiques : = égal, > supérieur à, >= supérieur ou égal à, < inférieur à, <= inférieur ou égal à, <> différent de.
  • La valeur si condition est remplie : C’est la valeur (ou formule) que la fonction SI va renvoyer au cas où la condition est vraie. Cet argument est facultatif.
  • La valeur si condition n’est pas remplie : C’est la valeur (ou formule) renvoyée par la fonction SI dans le cas où la condition n’est pas remplie. Cet argument est facultatif.

Les valeurs renvoyées comme réponse peuvent contenir un texte (comme par exemple Oui ou Non), un chiffre, le contenu d’une cellule spécifique ou même une autre formule (par exemple une autre fonction SI – voir le paragraphe sur les fonctions SI imbriquées ci-dessous.

image info

Si vous avez besoin de mettre plusieurs fonctions SI les unes dans les autres, il vous faudra utiliser la fonction SI imbriquée.

Prenons un exemple pratique où vous souhaitez créer à partir d’une variable Âge en colonne A, une nouvelle variable avec des tranches d’âge relatives à chacun des individus présents dans votre base de données :

  • Moins de 20 ans
  • De 20 à 49 ans
  • 50 ans et plus

La formule est la suivante :

=SI(A1<20,”Moins de 20 ans”,SI(ET(A1>19,A1<50),”Entre 20 et 49 ans”,SI(49<A1,”50 ans et plus”,””)))

À retenir

  • Il est possible d’imbriquer un très grand nombre de fonction, mais pour garantir un maximum d’efficacité et de lisibilité, éviter d’en utiliser plus de 10 imbrications.
  • N’hésitez pas à utiliser le retour à la ligne (ALT + Entrée) dans la barre de formule pour les rendre plus lisibles. Pour la formule ci-dessous, on peut avoir de manière équivalente ceci : =SI(A1<20,”Moins de 20 ans”, =SI(ET(A1>19,A1<50),”Entre 20 et 49 ans”, =SI(49<A1,”50 ans et plus”,””))

6.1.3 La couleur des parenthèses :

Il n’est pas toujours facile d’avoir le bon nombre de parenthèses aux bons endroits. Et dans les formules avec des fonctions SI imbriquées, ce problème survient régulièrement. Vous cliquez dans la Barre de formule qui contient votre formule, Excel va afficher, avec des couleurs diverses, les cellules utilisées dans votre formule.

Mais pas seulement ! Le logiciel affiche également les paires des parenthèses correspondantes avec les couleurs différentes pour chaque paire. Il est donc plus facile de s’y retrouver. Et ceci est valable également pour d’autres formules complexes en Excel !

6.1.4 Rechercher une valeur spécifique dans un tableau

Pour rechercher une valeur spécifique dans une base de données, il est possible d’utiliser une combinaison des fonctions =EQUIV et =INDEX.

Si la fonction EQUIV renvoie le numéro de ligne, sa syntaxe est la suivante :

=INDEX(tableau; EQUIV(valeur recherchée; plage de recherche; 0); no colonne)

Si la fonction INDEX renvoie le numéro de colonne, sa syntaxe est la suivante :

=INDEX(tableau; no ligne; EQUIV(valeur recherchée; plage de recherche; 0))

Prenons un exemple pratique : l’objectif est de trouver l’identifiant ainsi que l’âge du bénéficiaire du pays recherché. L’utilisateur doit pouvoir entrer la ville dans la cellule en dessous de Pays recherché et voir ensuite le résultat de sa recherche dans la partie correspondante, en E6 et F6 :

image info

Sélectionnez pour commencer la fonction INDEX :

  • Dans Matrice, entrez la plage de cellules du tableau : A1:C9
  • Dans No_lig, n’entrez rien pour le moment (la fonction EQUIV qui calculera le numéro de ligne en fonction de la recherche sera insérée ici)
  • Dans No_col, entrez la colonne du tableau qui contient les données à renvoyer. Dans cet exemple, le but est d’afficher l’identifiant, c’est donc la colonne 1.
  • Puis positionnez-vous dans No_lig et ajoutez la fonction EQUIV. Le but de la fonction EQUIV est donc de renvoyer le numéro de ligne du pays. Dans Valeur_cherchée, entrez la valeur dont il faut rechercher la position, ici, le pays, en E2.
  • Dans Tableau_recherche, entrez la plage de cellules dans laquelle la fonction recherchera l’identifiant du pays :
    B1:B9
  • Dans Type, entrez 0 pour trouver la valeur exacte.

image info

En E6, la formule renvoie bien l’identifiant associé au pays mentionné en E2.