Plus de 450 fonctions sur Excel. Rien que ça, et on ne parle même pas des outils. Mais est-ce que l’objectif est de toutes les connaitre ? Toutes les utiliser ? Bien sûr, la réponse est non. La question est plutôt de savoir LESQUELLES utiliser et sont adaptées à nos besoins.
Il existe plusieurs types de fonctions qui ont des attributs différents, des rôles différents. Et Excel les classe en catégories liées à leur fonction primaire :
- Effectuer des calculs
- Manipuler les textes
- Manipuler les dates
- Vérifier des conditions et utiliser les valeurs VRAI et FAUX
- Rechercher des valeurs en fonctions de différents critères
- Les validations des données
- Etc
Nous avons, pour pouvoir vous aiguiller, commencé à créer une série d’articles sur chacun de ces domaines en énumérant les différentes fonctions qui les composent. Après les fonctions de date, de texte, mathématiques, voici … les fonction logiques !
Nous allons donc nous focaliser sur les différentes fonctions spécialisées dans les fonctions logiques, considérée comme la base du code, qui vérifient la plupart du temps des conditions.
Les différents types de fonctions de logique
On a listé 23 fonctions.
Et pour aller plus pertinemment dans le détail nous les avons elles-mêmes disposées en plusieurs catégories :
- Celles qui permettent d’obtenir des résultats en fonction d’une.des condition.s
- Celles qui permettent d’obtenir une valeur booléenne (VRAI / FAUX)
- Celles qui permettent de calculer une synthèse en fonction d’une condition
- Celles qui permettent de calculer une synthèse en fonction d’une.des condition.s
- Celle qui permet d’obtenir une liste de résultat en fonction d’une.des condition.s
-
Ça nous fait donc 23 fonctions réparties dans 5 catégories.
Les 4 fonctions permettant d’obtenir des résultats d’après une.des condition.s
Tout le monde connait de nom la fonction SI. Quand on pense Excel + logique, forcément c’est elle qui fait echo dans notre tête. Et effectivement c’est cette catégorie que nous allons commencer à éplucher.
Des fonctions qui à partir d’une condition nous permettent d’obtenir des résultats.
Nous allons commencer par les deux plus connues, les fonctions SI et SIERREUR. La fonction SI comme son nom l’indique va indiquer une valeur si la condition est respectée et une autre si elle ne l’est pas, tandis que SIERREUR va indiquer une valeur pour remplacer une erreur.
La fonction SI
Utilité : Renvoyer différentes valeurs en fonction de la validation d'une condition.
Syntaxe : =SI( test_logique ; [valeur_si_vrai] ; [valeur_si_faux] )
Formule : =SI( 14 > 16 ; ‘’Oui’’ ; ‘’Non’’ ) >>> Résultat : Non. Non est la réponse renvoyée si le test (14>16) est faux.
La fonction SIERREUR
Utilité : Renvoyer une valeur si une première valeur est une erreur.
Syntaxe : =SIERREUR( valeur ; valeur_si_erreur )
Formule : =SIERREUR( 15/0 ; ‘’NC’’ ) >>> Résultat : NC. La division par 0 renvoie l’erreur #DIV/0, c’est donc la valeur si erreur qui est renvoyée.
Mais il existe deux autres fonctions, disons méconnues ou sous-exploitées et pourtant présentes depuis longtemps. SI.MULTIPLE et SI.CONDITIONS qui vont-elles nous permettre d’obtenir facilement des résultats en fonctions de plusieurs conditions.
La fonction SI.CONDITIONS
Utilité : Renvoyer différentes valeurs en fonction de la validation d'une ou plusieurs conditions.
Syntaxe : =SI.CONDITIONS( test_logique1 ; valeur_si_vrai1 ; [test_logique2] ; … )
Formule : =SI .CONDITIONS( 25 > 30 ; ‘’Possibilité 1’’ ; 25 > 24 ; ‘’Possibilité 2’’ ; 25 > 20 ; ‘’Possibilité 3’’ ) >>> Résultat : Possibilité 2. 25>24 est le premier test logique vrai, c’est donc la valeur qui va avec qui sera affichée.
La fonction SI.MULTIPLE
Utilité : Vérifier une valeur et attribuer un résultat correspondant en fonction d’une liste de valeurs.
Syntaxe : =SI.MULTIPLE( expression ; valeur1 ; résultat1 ; [défaut ou valeur2] ; … )
Formule : = SI.MULTIPLE ( 4 ; 6 ; ‘’Samedi’’ ; 7 ; ‘’Dimanche’’ ; ‘’Semaine’’ ) >>> Résultat : Semaine. 4 est différent de 6 et de 7, c’est donc le résultat par défaut qui est affiché.
Les 6 fonctions permettant d’obtenir des valeurs booléennes
Nous allons commencer par les 3 fonctions qui répondent à plusieurs conditions dépendantes ou indépendantes par des valeurs booléennes : les fonctions ET, OU et OUX (spoiler : elles portent bien leurs noms).
La fonction ET
Utilité : Renvoyer VRAI si toutes les conditions sont validées, sinon renvoyer FAUX.
Syntaxe : =ET( valeur_logique1 ; [valeur_logique2] ; [valeur_logique3] ; … )
Formule : =ET( 15 < 25 ; 15 > 10 ) >>> Résultat : FAUX. Une des conditions est fausse, la fonction ET renvoie donc FAUX.
La fonction OU
Utilité : Renvoyer VRAI si au moins une condition est validée, sinon renvoyer FAUX.
Syntaxe : =OU( valeur_logique1 ; [valeur_logique2] ; [valeur_logique3] ; … )
Formule : =OU( 15 < 25 ; 15 > 10) >>> Résultat : VRAI. Une des conditions est vraie, la fonction OU renvoie donc VRAI
La fonction OUX
Utilité : Renvoyer VRAI si une (et une seule) condition est validée, sinon renvoyer FAUX.
Syntaxe : =OUX( valeur_logique1 ; [valeur_logique2] ; [valeur_logique3] ; … )
Formule : =OUX( 15 < 25 ; 15 < 10) >>> Résultat : FAUX. Il y a plus d’une condition vraie, la fonction OUX renvoie donc FAUX.
Les 3 autres fonctions sont plus primitives. 3 sont mentionnées mais il en existe d’autres qui permettent de vérifier différentes choses dans la catégorie des ESTQUELQUECHOSE.
La fonction ESTTEXTE
Utilité : Renvoyer VRAI si la valeur testée est un texte (sinon FAUX).
Syntaxe : =ESTTEXTE( valeur )
Formule : =ESTTEXTE( 78 ) >>> Résultat : FAUX. 78 n’est pas un texte mais un nombre, la fonction ESTTEXTE renvoie FAUX.
La fonction ESTNUM
Utilité : Renvoyer VRAI si la valeur testée est un nombre (sinon FAUX).
Syntaxe : =ESTNUM( valeur )
Formule : =ESTNUM( ‘’Bravo’’ ) >>> Résultat : FAUX. Bravo est un texte mais pas un nombre, la fonction ESTNUM renvoie FAUX.
La fonction ESTFORMULE
Utilité : Renvoyer VRAI si la référence testée contient une formule (sinon FAUX).
Syntaxe : =ESTFORMULE( référence )
Ce que contient la cellule C4 : Norbert
Formule : =ESTFORMULE( C4 ) >>> Résultat : FAUX. Norbert n’est pas une formule mais du texte, la formule affiche donc FAUX.
Les 7 fonctions permettant de calculer une synthèse d’après une condition
Dans cette catégorie nous allons commencer par les fonctions qui calculent le nombre de cellules respectant une condition fixe, à savoir : celles qui contiennent une valeur (donc non vide) avec la fonction NBVAL et celles qui contiennent un nombre avec la fonction NB.
La fonction NBVAL
Utilité : Calculer le nombre de cellules non vides dans une plage de cellules.
Syntaxe : =NBVAL( valeur1 ; [valeur2] ; … )
Formule :
B2 = 3
B3 =
B4 = Abeille
=NBVAL(B2 : B4) >>> Résultat : 2. Dans la plage B2 à B4, B2 et B4 contiennent une valeur mais pas B3, ce qui fait 2 cellules non vides.
La fonction NB
Utilité : Calculer le nombre de cellules correspondant à des nombres dans une plage de cellules.
Syntaxe : =NB( valeur1 ; [valeur2] ; … )
Formule :
B2 = 3
B3 =
B4 = Abeille
=NB( B2 : B4 ) >>> Résultat : 1. Dans la plage de B2 et B4, seule la cellule B2 contient un nombre.
Nous allons passer en revue maintenant la triplette de fonction qui permettent d’effectuer des calculs en fonction d’une condition choisie, amovible, personnalisable : NB.SI, SOMME.SI, MOYENNE.SI
La fonction NB.SI
Utilité : Calculer le nombre de cellules correspondant à un critère dans une plage de cellules.
Syntaxe : =NB.SI( plage ; critère )
Formule :
B2 = Vélo
B3 = Moto
B4 = Vélo
=NB.SI(B2 :B4 ; ‘’Vélo’’ ) >>> Résultat : 2. Dans la plage de B2 à B4, il y a deux fois le mot Vélo.
La fonction SOMME.SI
Utilité : Calculer la somme des valeurs des cellules correspondant à un critère dans une plage de cellules.
Syntaxe : =SOMME.SI( plage_critère ; critère ; plage_somme )
Formule :
B2 = Vélo C2 = 1500
B3 = Moto C3 = 7000
B4 = Vélo C4 = 2000
=SOMME.SI( B2 :B4 ; ‘’Vélo’’ ; C2 :C4 ) >>> Résultat : 3500. Le total des nombres qui sont en face du mot ‘’Vélo’’ est de 3500. Le 7000 est en face de ‘’Moto’’ et n’est donc pas comptabilisé.
La fonction MOYENNE.SI
Utilité : Calculer la moyenne des valeurs des cellules correspondant à un critère dans une plage de cellules.
Syntaxe : =MOYENNE.SI( plage_critère ; critère ; plage_moyenne )
Formule :
B2 = Vélo C2 = 1500
B3 = Moto C3 = 7000
B4 = Vélo C4 = 2000
=MOYENNE.SI( B2 :B4 ; ‘’Vélo’’ ; C2 :C4 ) >>> Résultat : 1750. La moyenne des nombres qui sont en face du mot ‘’Vélo’’ est de 1750. Le 7000 est en face de ‘’Moto’’ et n’est donc pas comptabilisé.
Pour clore cette catégorie (et en beauté) voici deux fonctions qui permettent d’effectuer tout genre de calculs MAIS avec la grande particularité d’offrir des résultant qui prennent en compte des filtres / critères appliqués.
La fonction SOUS.TOTAL
Utilité : Réaliser une opération mathématique dynamique sur une plage de tableau. Le résultat ne comptera pas les cellules masquées par des filtres.
Syntaxe : =SOUS.TOTAL( no_fonction ; réf1 ; [réf2] ; ... )
Formule :
B2 = 5
B3 = 12 (masquée)
B4 = 6
=SOUS.TOTAL( 9 ; B2 :B4 ) >>> Résultat : 11. 9 correspond à une somme, la cellule B3 est masquée, c’est donc le total de B2 et B4 qui est renvoyé.
La fonction AGREGAT
Utilité : Réaliser une opération mathématique dynamique sur une plage de tableau. Le résultat ne comptera pas les cellules remplissant plusieurs critères choisis (erreurs, masquées par les filtres etc.).
Syntaxe : =AGREGAT( no_fonction ; options ; matrice ; [k])
Formule :
B2 = 5
B3 = 12 (masquée)
B4 = 6
=AGREGAT( 9 ; 3 ; B2 :B4 ) >>> Résultat : 11. 9 correspond à une somme, 3 correspond à ignorer les cellules masquées, la cellule B3 est masquée, c’est donc le total de B2 et B4 qui est renvoyé.
Les 5 fonctions permettant de calculer une synthèse d’après une ou plusieurs conditions
Voisine de la catégorie précédente, celle-ci nous offre relativement les mêmes choses mais avec plusieurs améliorations.
Plusieurs types de calculs en fonction non pas d’une, mais plusieurs conditions. Les fonction ENS : NB.SI.ENS, SOMME.SI.ENS, MOYENNE.SI.ENS, MIN.SI.ENS et MAX.SI.ENS vont ici permettre de multiplier et associer plusieurs conditions et en faire ressortir des résultats encore plus précis.
La fonction NB.SI.ENS
Utilité : Calculer le nombre de cellules correspondant à un ou plusieurs critères dans une plage de cellules.
Syntaxe : =NB.SI.ENS( plage_1 ; critère_1 ; [plage_2] ; [critère_2] ; … )
Formule :
B2 = Casquette C2 = Rouge
B3 = Bonnet C3 = Rouge
B4 = Casquette C4 = Vert
B5 = Casquette C5 = Rouge
=NB.SI.ENS( B2 :B5 ; ‘’Casquette’’ ; C2 :C5 ; ‘’Rouge’’ ) >>> Résultat : 2. Il y a uniquement 2 casquettes rouges, puisque en B4, la casquette est verte, et en C3, c’est le bonnet qui est rouge.
La fonction SOMME.SI.ENS
Utilité : Calculer la somme des valeurs des cellules correspondant à un ou plusieurs critères dans une plage de cellules.
Syntaxe : =SOMME.SI.ENS( plage_somme ; plage_critère_1 ; critère_1 ; [plage_critère_2] ; [critère_2] ; … )
Formule :
B2 = Casquette C2 = Rouge D2 = 49
B3 = Bonnet C3 = Rouge D3 = 35
B4 = Casquette C4 = Vert D4 = 60
B5 = Casquette C5 = Rouge D5 = 51
=SOMME.SI.ENS( D2 :D5 ; B2 :B5 ; ‘’Casquette’’ ; C2 :C5 ; ‘’Rouge’’ ) >>> Résultat : 100. Les nombres de la colonne D qui sont en face des critères « Casquette » et « Rouge » font un total de 100.
La fonction MOYENNE.SI.ENS
Utilité : Calculer la moyenne des valeurs des cellules correspondant à un ou plusieurs critères dans une plage de cellules.
Syntaxe : =MOYENNE.SI.ENS( plage_moyenne ; plage_critère_1 ; critère_1 ; [plage_critère_2] ; [critère_2] ; … )
Formule :
B2 = Casquette C2 = Rouge D2 = 49
B3 = Bonnet C3 = Rouge D3 = 35
B4 = Casquette C4 = Vert D4 = 60
B5 = Casquette C5 = Rouge D5 = 51
=MOYENNE.SI.ENS( D2 :D5 ; B2 :B5 ; ‘’Casquette’’ ; C2 :C5 ; ‘’Rouge’’ ) >>> Résultat : 50. Les nombres de la colonne D qui sont en face des critères « Casquette » et « Rouge » font en moyenne 50.
La fonction MIN.SI.ENS
Utilité : Récupérer la valeur la moins élevée parmi les valeurs des cellules correspondant à un ou plusieurs critères dans une plage de cellules.
Syntaxe : =MIN.SI.ENS( plage_min ; plage_critère_1 ; critère_1 ; [plage_critère_2] ; [critère_2] ; … )
Formule :
B2 = Casquette C2 = Rouge D2 = 49
B3 = Bonnet C3 = Rouge D3 = 35
B4 = Casquette C4 = Vert D4 = 60
B5 = Casquette C5 = Rouge D5 = 51
=MIN.SI.ENS( D2 :D5 ; B2 :B5 ; ‘’Casquette’’ ; C2 :C5 ; ‘’Rouge’’ ) >>> Résultat : 49. Le nombre le moins élevé qui est en face de « Casquette » et « Rouge » est 49 (en D2).
La fonction MAX.SI.ENS
Utilité : Récupérer la valeur la plus élevée parmi les valeurs des cellules correspondant à un ou plusieurs critères dans une plage de cellules.
Syntaxe : =MAX.SI.ENS( plage_max ; plage_critère_1 ; critère_1 ; [plage_critère_2] ; [critère_2] ; … )
Formule :
B2 = Casquette C2 = Rouge D2 = 49
B3 = Bonnet C3 = Rouge D3 = 35
B4 = Casquette C4 = Vert D4 = 60
B5 = Casquette C5 = Rouge D5 = 51
=MAX.SI.ENS( D2 :D5 ; B2 :B5 ; ‘’Casquette’’ ; C2 :C5 ; ‘’Rouge’’ ) >>> Résultat : 51. Le nombre le moins élevé qui est en face de « Casquette » et « Rouge » est 51 (en D5).
La fonction qui permet d’obtenir une liste de résultat en fonction d’une.des condition.s
C’est la fonction ultime de cet article, la fonction FILTRE. Arrivée sur le tard sur Excel (version 2019+) elle n’en reste pas moins la reine des fonctions logiques et pour cause : elle sait tout faire.
Filtrer des données selon une ou plusieurs conditions, le tout en version propagée (donc des résultats en plage de cellules et non en cellules uniques), aussi puissante utilisée seule qu’en la combinant à d’autres fonctions... Bref, cette fonction elle l’une des plus puissante d’Excel.
La fonction FILTRE
Utilité : Récupérer la liste des lignes d’une plage de cellules en fonction d’un ou plusieurs critères.
Syntaxe : =FILTRE( tableau; inclure ; [si_vide] )
Formule (en F2):
B2 = Spatule C2 = Inox D2 = 15
B3 = Cuillère C3 = Argent D3 = 30
B4 = Spatule C4 = Plastique D4 = 2
B5 = Fourchette C5 = Inox D5 = 7
=FILTRE( B2 : D5 ; B2 : B5 = ‘’Spatule’’ ) >>> Résultat :
F2 = Spatule G2 = Inox H2 = 15
F3 = Spatule G3 = Plastique H3 = 15
Les 2 lignes du tableau qui se trouve en B2 à D5 qui contiennent le mot « Spatule » dans la colonne B sont extraites dans les cellules qui suivent F2.
On dit souvent que les fonctions de logique, ou le système conditionnel c’est la base du code. Ce qui est vrai, et surtout sur Excel tout le vivier de fonctionnalités de ce domaine et la puissance de ces dernières offrent un vrai éventail de possibilités.
Avec les fonctions de logique, seules ou couplées à d’autres fonctions / outils, on peut faire un maximum de choses.
Pour aller plus loin dans leur apprentissage, pour apprendre à mieux cibler comment, quand, avec quoi les utiliser, vous pouvez également vous inscrire à nos formations.
Vous êtes un particulier, rendez-vous sur la page de nos formations individuelles !
Vous êtes une entreprise, rendez-vous sur la page de nos offres entreprises !
