6.1 Quelques formules avancées

TABLE DES MATIÈRES

• 6.1.1 Additionner des cellules répondant à un critère
• 6.1.2 Établir des comparaisons logiques
• 6.1.3 La couleur des parenthèses :
• 6.1.4 Rechercher une valeur spécifique dans un tableau
• 6.1.5 La fonction Somme Produit

---

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é.

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.

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.

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”,””)))

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 :

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.

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

6.1.5 La fonction Somme Produit

SommeProduit est une formule extrêmement puissante, elle vous permet, comme indiqué dans la section 7.5 Le coin des geeks, de calculer la somme des produits de plages ou de matrices. Par matrices, comprenez tables/tableaux, c’est une approximation, mais qui vous permettra de mieux saisir la logique.

Pour illustrer à quoi cela sert, vous pouvez parcourir l’exemple suivant :

Dans le cas d’une réponse de crise humanitaire, plusieurs ONG interviennent dans une Camp composé de différentes zones. Chacune des ONG cible un certains nombres de bénéficiaires. Vous voudriez calculer l’ensemble des bénéficiaires que les deux ONGs de la zone B couvrent dans le domaine de la santé et de l’eau et assainissement (EAH). Vous pourriez bien sûr utiliser un deux filtres et ensuite calculer la somme une fois les filtres appliqués.

Mais cela présuppose que vous ne voulez faire que cette opération, que vos critères ne sont pas très nombreux et que vous ne devez pas effectuer régulièrement cette opération.

Si c’est le cas, nous vous conseillons la formule suivante :

=SOMMPROD((Tableau1[Zone]=G1)*(Tableau1[Domaine]=F1)*Tableau1[Bénéficiaires])