Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

UTILISATION DU TABLEUR EXCEL LES FONCTIONS CONDITIONNELLES LES FONCTIONS CONDIT

UTILISATION DU TABLEUR EXCEL LES FONCTIONS CONDITIONNELLES LES FONCTIONS CONDITIONNELLES GENERALITES Les fonctions conditionnelles permettent d’additionner, de calculer la moyenne, de compter ou d’obtenir les valeurs minimum ou maximum d’une plage par rapport à des critères que l’on spécifie. Il s’agit des fonctions suivantes : 1. SOMME.SI 2. SOMME.SI.ENS 3. MOYENNE.SI 4. MOYENNE.SI.ENS 5. NB.SI 6. NB.SI.ENS 7. MAX.SI.ENS (depuis EXCEL 2019) 8. MIN.SI.ENS (depuis EXCEL 2019) Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 2 1. LA FONCTION « SOMME.SI » 1.1 Description SOMME.SI permet d’additionner une plage d’après un critère spécifique recherché dans une autre plage. 1.2 Syntaxe On écrit : =SOMME.SI(plage;critère;[somme_plage]) • plage (Obligatoire). Plage de cellules à calculer en fonction du critère. Les cellules de chaque plage doivent être des nombres ou des noms, des matrices ou des références contenant des nombres. Les valeurs vides ou textuelles ne sont pas prises en compte. La plage sélectionnée peut contenir des dates au format Excel standard (voir exemples ci-dessous). Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 3 • critère (Obligatoire). Critère, exprimé sous forme de nombre, d’expression, de référence de cellule, de texte ou de fonction qui définit les cellules à ajouter. Important : Tous les critères textuels et tous les critères qui contiennent des symboles mathématiques ou logiques doivent être placés entre guillemets ("). En revanche, les guillemets ne sont pas nécessaires pour les critères numériques. Par exemple, l’argument critère peut être exprimé sous l’une des formes suivantes : 32, ">32", B5, "32", "pommes" ou AUJOURDHUI(). • plage_somme (Facultatif). Cellules réelles à ajouter, si vous voulez ajouter d’autres cellules que celles qui sont spécifiées dans l’argument plage. Si l’argument plage_somme est omis, Excel ajoute les cellules spécifiées dans l’argument plage (les cellules auxquelles s’applique le critère). Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 4 Vous pouvez utiliser les caractères génériques (?) et (*) dans l’argument critère. (?) correspond à un caractère quelconque et (*) correspond à une séquence de caractères quelconque. Pour rechercher réellement un point d’interrogation ou un astérisque, tapez un tilde (~) devant ce caractère. Si la plage est Si somme_plage est Les cellules sont A1:A5 B1:B5 B1:B5 A1:A5 B1:B3 B1:B5 A1:B4 C1:D4 C1:D4 A1:B4 C1:C2 C1:D4 L’argument plage_somme ne doit pas nécessairement avoir la même taille et la même forme que l’argument plage. Les cellules effectivement additionnées sont déterminées en utilisant la cellule supérieure gauche de l’argument plage_somme comme cellule de début, puis en incluant les cellules dont la taille et la forme correspondent à l’argument plage. Par exemple : Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 5 A B C 1 Valeur de propriété Commission Données 2 100 000 7 000 250 000 3 200 000 14 000 4 300 000 21 000 5 400 000 28 000 6 Formules Résultats Commentaires 7 =SOMME.SI(A2:A5;">160000";B2:B5) 63 000 € Somme des valeurs de commissions excédant 160 000 8 =SOMME.SI(A2:A5;">160000") 900 000 € Somme des valeurs de propriétés excédant 160 000 9 =SOMME.SI(A2:A5;300000;B2:B5) 21 000 € Somme des commissions relatives aux valeurs de propriétés égales à 300 000 10 =SOMME.SI(A2:A5;">" & C2;B2:B5) 49 000 € Somme des commissions relatives aux valeurs de propriétés supérieures à la valeur de C2. 1.3 Exemple d’application 1 Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 6 A B C 1 Catégorie Nourriture Ventes 2 Légumes Tomates 2 300 3 Légumes Céleri 5 500 4 Fruits Oranges 800 5 Beurre 400 6 Légumes Carotte 4 200 7 Fruits Pommes 1200 8 Formules Résultats Commentaires 9 =SOMME.SI(A2:A7;"Fruits";C2:C7) 2 000 Somme de la vente de tous les produits alimentaires dans la catégorie « Fruits ». 10 =SOMME.SI(A2:A7;"Légumes";C2:C7) 12 000 Somme de la vente de tous les produits alimentaires dans la catégorie « Légumes ». 11 =SOMME.SI(B2:B7;"*es";C2:C7) 4 300 Somme de la vente de tous les produits alimentaires se terminant par « es » (Tomates, Oranges et Pommes). 12 =SOMME.SI(A2:A7;"";C2:C7) 400 Somme de la vente de tous les produits alimentaires n’appartenant pas à une catégorie spécifique. 1.4 Exemple d’application 2 Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 7 2. LA FONCTION « SOMME.SI.ENS » 2.1 Description SOMME.SI.ENS permet d’additionner une plage d’après plusieurs critères spécifiques recherchés dans d’autres plages. 2.2 Syntaxe On écrit : =SOMME.SI.ENS(somme_plage;plage_critères1; critères1; [plage_critère2; critères2]; ...) • somme_plage (Obligatoire) : Plage de cellules à additionner. • plage_critères1 et critère1 (Obligatoire) : forment une paire utilisée pour la recherche de critères spécifiques. Lorsque les éléments de la plage sont trouvés, leurs valeurs correspondantes dans somme_plage sont ajoutées. • Plage_critères2 ; critère2 (Facultatif) : Plages supplémentaires et critères associés. Jusqu’à 127 paires plage/critères sont autorisées. Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 8 2.3 Remarques 1. Toutes les remarques faites dans l’étude de SOMME.SI sont valables également pour SOMME.SI.SENS, notamment la possibilité d’utiliser les caractères génériques « * » et « ? » pour la recherche. 2. Noter cependant que l’ordre des arguments n’est pas le même entre les fonctions SOMME.SI et SOMME.SI.ENS. Par exemple, l’argument somme_plage est le premier argument de la fonction SOMME.SI.ENS, alors qu’il est le troisième argument de la fonction SOMME.SI. Cette différence peut poser problème lors de l’utilisation de ces fonctions. Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 9 A B C 1 Quantités vendues Produits Vendeurs 2 5 Pommes David 3 4 Pommes Marie 4 15 Artichauts David 5 3 Artichauts Marie 6 22 Bananes David 7 12 Bananes Marie 8 10 Carottes David 9 33 Carottes Marie 10 Formules Commentaires Résultats 11 =SOMME.SI.ENS(A2:A9;B2:B9;”=A*“;C2:C9;"David") ou =SOMME.SI.ENS(A2:A9;B2:B9;”A*“;C2:C9;"David") ou =SOMME.SI.ENS(A2:A9;B2:B9;”A*“;C2:C9;“=David") Ajoute le nombre de produits qui commencent par A et ont été vendus par David. On peut écrire ou pas le signe « égale » dans les critères. 15 12 =SOMME.SI.ENS(A2:A9;B2:B9;"<>Bananes“;C2:C9;"David") ou =SOMME.SI.ENS(A2:A9;B2:B9;"<>Bananes“;C2:C9;“=David") Ajoute le nombre de produits qui ne sont pas des bananes et sont vendus par David. 30 2.4 Exemple d’application Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 10 3. LA FONCTION « MOYENNE.SI» 3.1 Description MOYENNE.SI renvoie la moyenne (arithmétique) de toutes les cellules d’une plage qui répondent à des critères donnés. 3.2 Syntaxe On écrit : =MOYENNE.SI(plage;critères;[plage_moyenne])_ • plage (Obligatoire) : Une ou plusieurs cellules dont la moyenne doit être calculée, • critères (Obligatoire) : représente le critère, sous forme de nombre, d’expression, de référence de cellule ou de texte, qui détermine les cellules dont la moyenne est à calculer. • [plage_moyenne] (Facultatif) : ensemble des cellules dont la moyenne est à calculer. Si cet argument est omis, l’argument plage est utilisé. Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 11 A B C 1 Commission Commission 2 100 000 7 000 3 200 000 14 000 4 300 000 21 000 5 400 000 28 000 6 Formules Résultats Commentaires 7 =MOYENNE.SI(B2:B5;"<23000") 14 000 Moyenne de toutes les commissions inférieures à 23 000. 8 =MOYENNE.SI(A2:A5;"<250000") 150 000 Moyenne de toutes les valeurs de propriétés inférieures à 250 000. 9 =MOYENNE.SI(A2:A5;"<95000") #DIV/0! Moyenne de toutes les valeurs de propriétés inférieures à 95 000. Comme aucune valeur ne remplit cette condition, MOYENNE.SI renvoie l’erreur #DIV/0! car elle tente de diviser par 0. 10 =MOYENNE.SI(A2:A5;">250000";B2:B5) 24 500 Moyenne de toutes les commissions avec une valeur de propriété supérieure à 250 000. 3.3 Exemple d’application 1 Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 12 A B C 1 Région Bénéfices (milliers) 2 Est 45678 3 Ouest 23789 4 Nord -4 789 5 Sud (nouvelle succursale) 0 6 Centre-ouest 9678 7 Formules Résultats Commentaires 8 =MOYENNE.SI(A2:A6;"=*Ouest";C2:C6) 16 733,5 Moyenne des bénéfices pour les régions Ouest et Centre. 9 =MOYENNE.SI(A2:A6;"<>*(Nouvelle succursale)";C2:C6) 18 589 Moyenne des bénéfices pour toutes les régions, à l’exception des nouvelles succursales. 3.4 Exemple d’application 2 Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 13 4. LA FONCTION « MOYENNE.SI.ENS» 4.1 Description MOYENNE.SI.ENS renvoie la moyenne (arithmétique) de toutes les cellules d’une plage qui répondent à des critères donnés. 4.2 Syntaxe On écrit : =MOYENNE.SI.ENS(moyenne_plage;plage_critères1; critères1; [plage_critère2; critères2]; ...) • moyenne_plage (Obligatoire) : Une ou plusieurs cellules dont la moyenne doit être calculée ; • plage_critères1 et critère1 (Obligatoire) : forment une paire utilisée pour la recherche de critères spécifiques. Lorsque les éléments de la plage sont trouvés, la moyenne de leurs valeurs correspondantes dans moyenne_plage est renvoyée. • Plage_critères2; critère2 (Facultatif) : Plages supplémentaires et critères associés. Jusqu’à 127 paires plage/critères sont autorisées. Juin 2020 Formation EXCEL Niveau 3. Professeur : MANOUAN Etien Claude Expert Formateur en Suite Bureautique 14 Les fonctions MOYENNE.SI et MOYENNE.SI.ENS mesurent la tendance centrale qui est l’emplacement central d’un groupe de nombres dans une distribution statistique. Rappelons uploads/Philosophie/ les-fonctions-conditionnelles-ipec4-jour.pdf