Les formules, top 10 des plus utiles
On a découvert comment fonctionnent les formules, on va maintenant utiliser les plus populaires.
Sommaire
- 1) Date : AUJOURDHUI , MAINTENANT
- 2) Logique : SI
- 3) Logique : ET , OU , OUX , NON
- 4) Statistiques : SOMME , MOYENNE , MAX , MIN , NB
- 5) Statistiques : SOMME.SI , MOYENNE.SI , NB.SI , ENS
- 6) Texte : CONCATENER , TEXTE
- 7) Texte : DROITE , GAUCHE , STXT
- 8) Texte : CNUM , CTXT
- 9) Recherche : RECHERCHEV , RECHERCHEH
- 10) Recherche : INDEX , EQUIV
1) Date : AUJOURDHUI , MAINTENANT
Ces 2 formules sont dynamiques. Le résultat affiché va donc s'actualiser en fonction du moment où elles sont utilisées.
- A1 → =AUJOURDHUI() donne la date du jour (exemples)
- A3 → =MAINTENANT() donne la date et l'heure actuelle (exemples)
2) Logique : SI
La fonction SI est très importante et permet de faire beaucoup de choses intéressantes, il faut donc bien comprendre son fonctionnement.Le but de cette fonction est de tester une donnée en entrée selon une condition choisie. La réponse à ce test ne peut être que "oui" ou "non", puis la fonction retourne une donnée si la réponse est "oui" ou une autre donnée si la réponse est "non".
La formule est la suivante : =SI( test_logique ; valeur_si_vrai ; valeur_si_faux )
Pour mieux montrer l'utilité des formules, on a ici des données qui changent chaque fois qu'Excel relance les calculs. C'est possible grâce à la fonction =ALEA.ENTRE.BORNES(min;max) , qui permet de générer un nombre aléatoire compris entre le min et max défini.
Cette fonction est utilisée en colonne C pour déterminer le nombre de vente sur chaque mois.
Le montant du chiffre d'affaire en colonne B est de 20x le nombre en colonne C.
En colonne D, la fonction SI est utilisée. Le test ici est de savoir si le chiffre d'affaire est supérieur ou égal à l'objectif fixé en J12 (10 000€ ici).
Si la réponse à ce test est "oui", on affiche "Objectif atteint", si la réponse est "non", on laisse la cellule vide.
La formule pour la ligne 2 est la suivante : =SI(B2>=$J$12;"Objectif atteint";"")
- test_logique : B2>=$J$12 → B2 est donc la cellule que l'on va tester, on utilise ensuite l'opérateur supérieur ou égal >= puis la cellule J12 qui contient la valeur de l'objectif fixé
- valeur_si_vrai : "Objectif atteint" → Lorsque l'on souhaite écrire du texte dans une formule, il faut "entourer le texte par des guillemets"
- valeur_si_faux : "" → pour afficher un texte vide dans la cellule, on utilise les guillemets sans rien d'autre
En colonne E, une autre fonction SI. Le test ici est de savoir si le chiffre d'affaire est inférieur au seuil d'alerte en J13 (5 000€ ici).
Si la réponse à ce test est "oui", on affiche "Alerte", si la réponse est "non", on laisse la cellule vide.
La formule pour la ligne 2 est la suivante : =SI(B2<$J$13;"Alerte";"")
La talbeau en G1:J7 montre l'ensemble des opérateurs utilisables pour effectuer le test_logique de la fonction SI. Des exemples pour chaque test sont disponibles en colonne J. Maintenant que l'on comprend comment fonctionne cette formule, on peut simplement regarder le contenu des cellules de la colonne J via la barre de formule.
Voici également l'article Microsoft sur la fonction SI ici.
3) Logique : ET , OU , OUX , NON
Ces fonctions sont très utilisées avec la formule SI que l'on vient de voir. Le but de ces fonctions est de pouvoir combiner plusieurs conditions dans un même test_logique.On commence par ET , OU , OUX qui ont toutes les 3 la même syntaxe, exemple avec ET : =ET( test_logique_1 ; test_logique_2 ; test_logique_... ) :
La différence entre ces 3 fonctions se trouve dans le résultat renvoyé en fonction des valeurs en entrée.
- ET → retourne "oui" uniquement lorsque l'on répond "oui" à toutes les conditions
- OU → retourne "non" uniquement lorsque l'on répond "non" à toutes les conditions
- OUX → retourne "non" lorsque l'on répond à l'identique à toutes les conditions
La double condition est la suivante :
- 5 260€ est il supérieur ou égal à 5 000€ ?
- 2 220€ est il supérieur ou égal à 5 000€ ?
Petite précision sur les termes logiques :
- oui = vrai = 1
- non = faux = 0
On comprend maintenant à quoi servent ces 3 fonctions, mais à quoi peut bien servir la fonction NON ?
La fonction NON permet d'inverser les valeurs retournées par tous ces cas de test, afin d'avoir toutes les possibilités sous la main en cas de besoin.
Voici les 3 tableaux ET , OU , OUX inversés par la fonction NON. Ils s'appellent donc maintenant NON ET , NON OU , NON OUX :
Avec ces quelques fonctions de logique, on peut exécuter n'importe quel test dont on peut avoir besoin pour créer des programmes.
4) Statistiques : SOMME , MOYENNE , MAX , MIN , NB
Cette série de formule permet de faire différents calculs.La syntaxe est la même pour chaque formule de ce type.
Exemple : =SOMME( nombre_1 ; nombre_2 ; nombre_... )
Concernant la syntaxe, soit on utilise chaque cellule séparée d'un ';' , soit on utilise une plage de cellule comme dans l'exemple ci-dessus.
Ces formules ont des noms suffisamment explicite pour comprendre le but de chacune. La formule NB sert à compter un nombre de valeurs selon différentes conditions.
- E8 → =NB(B1:B15) compte le nombre de cellules contenant des nombres. B1, B14 et B15 ne sont pas des nombres, donc le résultat est 12.
- E9 → =NBVAL(B1:B15) compte le nombre de cellules qui ne sont pas vides. B14 et B15 sont vides, donc le résultat est 13.
- E10 → =NBVAL(B1:B15) compte le nombre de cellules vides uniquement. B14 et B15 sont vides, donc le résultat est 2.
5) Statistiques : SOMME.SI , MOYENNE.SI , NB.SI , ENS
Il est possible d'ajouter des conditions personnalisées à toutes ces formules.
La syntaxe est la même pour SOMME & MOYENNE, on retire l'argument plage_somme pour NB.
Exemples avec SOMME.SI qui permet d'utiliser 1 condition, et SOMME.SI.ENS qui permet d'en utiliser plusieurs :
- =SOMME.SI( plage_critère ; critère ; plage_somme )
- =SOMME.SI.ENS( plage_somme ; plage_critère_1 ; critère_1; plage_critère_2 ; critère_2 ; ... )
La syntaxe pour l'utilisation d'un critère est légèrement différente de celle utilisée pour le test_logique de la fonction SI. Voici comment Microsoft explique sa syntaxe :
"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." L'article est disponible ici.
- E3 → fait la somme des cellules de la plage B2:B13 dont la valeur est supérieure ou égale à 5 000€.
- E4 → fait la somme des cellules de la plage B2:B13 dont la valeur est supérieure ou égale à 5 000€ ET inférieure à 10 000€.
6) Texte : CONCATENER , TEXTE
On passe maintenant à l'utilisation des formules pour manipuler du texte.CONCATENER permet de regrouper différentes chaînes de textes les unes à la suite des autres. Syntaxe : =CONCATENER( texte_1 ; texte_2 ; texte_... )
TEXTE permet d'utiliser le système de format de cellule d'Excel à travers une formule (le tuto sur les formats est disponible ici). Syntaxe : =TEXTE( texte ; format )
- E2, E3, E4 → affiche 2 noms de mois puis affiche la somme du chiffre d'affaire effectué sur ces 2 mois.
- E6 → affiche le nom d'un mois et le chiffre d'affaire correspondant, on remarque que le nombre n'est pas au format monétaire.
- E7 → même chose qu'en E6 avec le nombre au format monétaire à l'aide de la fonction TEXTE et du format "# ##0 €".
7) Texte : DROITE , GAUCHE , STXT
Ici, on va découper du texte pour récupérer la partie dont on a besoin.DROITE et GAUCHE permettent de récupérer un nombre voulu de caractères provenant de la droite ou de la gauche du texte. Syntaxe : =DROITE( texte ; nombre_de_caractères )
STXT permet de récupérer un nombre voulu de caractères provenant du milieu du texte. Syntaxe : =STXT( texte ; n°_1er_caractère ; nombre_de_caractères )
Attention, les fonctions de "texte" retournent des données au format "texte".
- B2:B6 → récupère le 1er caractère en partant de la gauche du texte situé en colonne A
- D2:D6 → formule SI utilisant la condition "1" au format texte pour afficher Homme ou Femme selon la colonne B
- E2:E3 → récupère les 3 1er caractères en partant de la gauche du texte situé en colonne A, puis, de ce résultat, récupère ensuite les 2 1er caractères en partant de la droite
- E4:E6 → même chose qu'en E2:E3 mais avec la fonction STXT qui récupère directement 2 caractères à partir du 2e caractère du texte en colonne A
8) Texte : CNUM , CTXT
Deux fonctions qui permettent de modifier le format des cellules.CNUM convertit une cellule au format texte en format numérique =CNUM( texte )
CTXT permet de convertir une cellule au format numérique en format texte. Syntaxe : =CTXT( nombre ; nombre_de_décimales ; séparateur_de_milliers )
- B2 → format texte car une fonction texte est utilisée
- D2 → format numérique car conversion de B2 via CNUM
- F2 → format texte car conversion de D2 via CTXT
9) Recherche : RECHERCHEV , RECHERCHEH
Ces fonctions de recherche permettent de retrouver des informations dans des bases de données.RECHERCHEV recherche une valeur dans une colonne, puis renvoi une valeur depuis une colonne adjacente =RECHERCHEV( valeur_cherchée ; plage_de_données ; n°_colonne_renvoyé ; type_recherche )
RECHERCHEH recherche une valeur dans une ligne, puis renvoi une valeur depuis une ligne adjacente =RECHERCHEH( valeur_cherchée ; plage_de_données ; n°_ligne_renvoyé ; type_recherche )
On commence par RECHERCHEV, la plus utilisée. Cette fonction prend tout son sens lorsque l'on recherche de la donnée précise dans une grande plage. On va donc utiliser un exemple avec la table des départements de France.
- A1:C97 → base de données de l'ensemble des numéros de départements, avec leurs noms et la région associée
- E1:H6 → n° de sécurité sociale duquel on extrait le n° du département, on peut ensuite retrouver son nom ainsi que sa région
- D2:D6 → génération du n° du département pour la colonne E, afin de prendre en compte le cas particulier de la Corse. Les données de cette colonne D sont cachées dans l'exemple ci-dessus
La génération de n° de sécurité sociale aléatoire prend en compte le cas particulier de la Corse, qui a dédoublé son n° de département 20 en 2A et 2B.
Voici les formules utilisées dans les colonnes F, G et H :
On va analyser les formules de la ligne 2 :
-
F2 → =CONCATENER("'";STXT(E2;6;2)) : fonction CONCATENER pour ajouter 2 chaînes textuelles côte à côte. La 1ere est une simple apostrophe '. La 2e est la fonction STXT qui permet d'extraire les caractères 6 et 7 du n° de sécu.
Le résultat est : '39 -
G2 → =RECHERCHEV(F2;A:B;2;FAUX) : la valeur recherchée est celle en F2. On recherche cette valeur dans la 1ere colonne de la plage A:B. Une fois cette valeur trouvée en colonne A, on garde le n° de ligne correspondant et on retourne la valeur de la 2e colonne de la plage, sur cette même ligne.
Le dernier argument FAUX, précise que l'on souhaite une correspondance exacte avec notre valeur en F2.
'39 est en ligne n°16, et le contenu de la colonne B en ligne 16 est "Jura"
- H2 → =RECHERCHEV(F2;A:C;3;FAUX) : même chose que pour G2 en changeant 2 paramètres. La plage est maintenant A:C, et on souhaite récupérer la valeur de la 3e colonne de cette plage, donc la C. Le contenu de la colonne C en ligne 16 est "Bourgogne-Franche-Comté"
La RECHERCHEH fonction de façon similaire, voici ce que ça donnerait en utilisant le même exemple :
On va analyser les formules de la ligne 6 :
- C6 → =RECHERCHEH(B6;1:2;2;FAUX) : la valeur recherchée est celle en B6. On recherche cette valeur dans la 1ere ligne de la plage 1:2. Une fois cette valeur trouvée en ligne 1, on garde la colonne correspondante et on retourne la valeur de la 2e ligne de la plage, sur cette même colonne. '39 est en colonne P, et le contenu de la ligne 2 en colonne P est "Jura"
- D6 → =RECHERCHEH(B6;1:3;3;FAUX) : même chose que pour C6 en changeant 2 paramètres. La plage est maintenant 1:3, et on souhaite récupérer la valeur de la 3e ligne de cette plage, donc la 3. Le contenu de la ligne 3 en colonne P est "Bourgogne-Franche-Comté"
10) Recherche : INDEX , EQUIV
La limite de la formule RECHERCHEV (ou RECHERCHEH) vient de son unique sens de recherche. La recherche ne peut se faire que de la gauche vers la droite (ou du haut vers le bas). Cette limitation a été corrigée dans la version 2019 (et la version en ligne 365) avec l'arrivée de la nouvelle formule RECHERCHEX.Ici, on ne va pas voir la nouvelle RECHERCHEX mais une méthode qui permet de faire la même chose tout en étant compatible avec l'ensemble des versions d'Excel. Cette méthode utilise la combinaison des 2 formules INDEX et EQUIV.
EQUIV recherche une valeur dans une liste, et retourne un nombre correspondant à la position de cette valeur dans la liste =EQUIV( valeur_cherchée ; liste ; type_de_recherche )
INDEX retourne une valeur dans une matrice qui correspond à l'intersection du n° de ligne et du n° de colonne donné =INDEX( matrice ; n°_ligne ; n°_colonne )
On va analyser les données de la ligne 2. Ici, on recherche le n° du département correspondant au Jura :
- E2 → =EQUIV(D2;B:B;0) : la valeur recherchée est celle en D2. On recherche cette valeur dans la colonne B. Le type de recherche 0 pour une correspondance exacte. La ligne 16 est retournée.
- F2 → =INDEX(A:A;E2) : la matrice ici est la colonne A, étant donné qu'il n'y a qu'une seule colonne, il n'y aura pas besoin de sélectionner un n° de colonne pour renvoyer une valeur. Le n° de ligne retournée correspond au résultat de la formule EQUIV, 16, ce qui retourne le département 39.
