Aller au contenu
Accueil » Ma liste de fonctions Excel que j’utilise le plus

Ma liste de fonctions Excel que j’utilise le plus

Créer des macros est une très bonne chose pour développer vos compétences. Mais créer des macros en combinant la puissance des fonctions Excel, c’est encore mieux ! Cet article présente les fonctions Excel les plus utilisées avec des exemples.

ma liste de fonctions Excel que j'utilise le plus !

La légendaire fonction SI

La fonction SI est indéniablement la fonction la plus utilisée sous Excel. Elle permet de réaliser une action suivant le résultat d’une condition. La structure de la fonction SI est la suivante :

SI( condition ; valeur si la condition est vraie, valeur si la condition est fausse)

Dans un premier temps, la condition donnée dans la fonction est évaluée. Suivant le résultat (vrai ou faux), l’instruction correspondante est alors exécutée. Voici un exemple pour catégoriser une liste de personne afin de savoir si elles sont mineures ou majeures.

Excel fonction SI

La traduction de la fonction est la suivante : Si l’âge est supérieur à 18, alors la personne est majeure sinon elle est mineure.

Ensuite, dans la seconde partie de la fonction SI, vous pouvez parfaitement enchaîner une autre fonctions SI afin de rajouter une condition.

Excel Fonctions SI imbriqués

Voici comment traduire la fonction : Si l’âge de la personne est supérieur à 18 alors elle est majeure sinon SI l’âge de la personne est supérieure à 2 alors la personne est mineure sinon c’est un bébé.

Vous pouvez ainsi imbriquer jusqu’à 64 fonctions SI. Cependant, plus vous en mettrez et plus il sera compliqué de comprendre votre fonction. Parfois, il peut être judicieux de travailler sur plusieurs colonnes.

La fonction SI.CONDITONS

Depuis la version 2019 d’Office, une manière plus élégante de définir des SI imbriqués est d’utiliser la fonction SI.CONDITIONS. Elle permet de définir plusieurs conditions à la suite (jusqu’à 127) avec la valeur associée. Voici la syntaxe :

=SI.CONDITIONS(condition1 ; valeur1 ; condition2 ; valeur2 ; … ; conditionN ; valeurN)

Les conditions sont testées les unes après les autres jusqu’à en obtenir une qui soit remplie. Ce sera donc la première condition remplie qui renverra la valeur. Si aucune condition n’est remplie, la fonction renverra la valeur #N/A. Vous pouvez spécifier une valeur par défaut à la fin de votre fonction en rentrant VRAI pour l’argument du test logique suivant de la valeur par défaut.

=SI.CONDITIONS(condition1 ; valeur1 ; condition2 ; valeur2 ; … ; VRAI ; ValeurParDéfaut)

ET / OU

Les fonctions ET / OU permettent de combiner les tests logiques. Le résultat de ces fonctions suivent les règles de l’algèbre de Boole. Tout d’abord, voici la syntaxe des deux fonctions :

=ET(test_logique_1 ; test_logique2 ; … ; test_logique_N)

=OU(test_logique_1 ; test_logique2 ; … ; test_logique_N)

Maintenant, voyons le résultat de ces fonctions par rapport aux résultats des tests logiques à l’intérieur de la fonction :

Excel fonction ET OU

Voici les conclusions du tableau :

  • La fonction ET est la plus restrictive. Il faut que tous les tests logiques renvoient la valeur VRAI pour que le résultat final soit VRAI
  • La fonction OU renvoie la valeur VRAI dès lors qu’au moins une condition est vraie.
  • Si tous les tests logiques renvoient FAUX, les deux fonctions renvoient FAUX également.

SOMME et SOMME.SI

La fonction SOMME est également une des fonctions les plus utilisées dans Excel. Elle permet d’éviter de devoir écrire une formule avec un nombre de signe « + » gigantesque en sélectionnant toutes les cellules. La fonction SOMME vous permet de travailler sur une plage de cellules. Ceci vous permet également de pouvoir glisser la formule bien plus facilement. La syntaxe est la suivante :

=SOMME(nombre1 ; nombre2 ; …)

L’argument nombre1 peut tout à fait être une plage de cellule comme on peut le voir sur l’exemple suivant :

Excel fonction SOMME

Dans cet exemple, on calcule la somme des âges des personnes dans la liste sélectionnées. Une autre fonction associée à la fonction SOMME qui est très utile est la fonction SOMME.SI. Elle permet de réaliser une somme sur une plage de cellule selon un critère défini. Voici la syntaxe :

=SOMME.SI(plage ; critère ; somme_plage)

Excel fonction SOMME.SI

Si l’argument « somme_plage » n’est pas donné, la fonction utilisera les cellules données dans l’argument « plage ». Ceci ne fonctionne que si les cellules contiennent des valeurs bien entendu.

Concernant le critère, celui-ci peut être soit donné directement dans la formule comme on le voit dans l’exemple soit faire référence à la valeur dans une cellule. Vous pouvez tout à fait également définir un critère de comparaison comme par exemple faire la somme des valeurs qui sont supérieure à 40. Voici la formule qui permet de le faire en reprenant l’exemple du dessus :

=SOMME.SI(B2:B10; »>40″)

Dans cette formule, l’argument « somme_plage » n’est pas donné car on contrôle et on somme sur la même plage, à savoir les cellules B2:B10. Ensuite, le critère « >40 » fera un filtre sur les valeurs pour ne sommer que les valeurs supérieurs à 40. Dans la liste de l’exemple, il n’y a que les valeurs 45 et 55 ce qui donne un total de 100. Je vous laisse essayer par vous-même si cela vous intéresse.

MOYENNE et MOYENNE.SI

Le calcul d’une moyenne est quelque chose de simple en soit. Mais eu lieu de devoir récupérer le nombre de valeur et en faire la somme, une fonction dédiée se charge de le faire. La fonction MOYENNE fonctionne de la même façon que la fonction SOMME sauf que celle-ci calcule la moyenne tout simplement. Vous donnez comme argument une plage de cellule et la fonction vous renvoie la valeur moyenne. Vous pouvez soit sélectionner les cellules une à une soit directement sélectionner une plage de cellule. Cette dernière option est de loin la plus pratique.

=MOYENNE(valeur1 ; valeur2 ; …)

Comme pour la section précédente, on trouve également la fonction MOYENNE.SI qui permet d’intégrer une condition sur la prise en compte de la valeur dans le calcul.

=MOYENNE.SI(plage ; critère ; somme_plage)

Le critère peut être une valeur donnée directement dans la formule ou on peut faire référence à une cellule. Je vous invite à lire la section précédente pour plus de détails. Enfin, voici le résultat des fonctions SOMME, SOMME.SI, MOYENNE et MOYENNE.SI pour notre exemple.

Excel fonctions MOYENNE et MOYENNE.SI

RACINE et ABS, ENT

La fonction RACINE, comme son nom l’indique, permet de calculer la racine carrée d’un nombre. Sa syntaxe est très simple :

=RACINE(nombre)

La fonction ABS permet de récupérer la valeur absolue d’un nombre. Le résultat de cette fonction pour un nombre positif sera le nombre lui-même. Pour un nombre négatif, on récupèrera le même nombre sans le signe négatif. Voici la syntaxe :

=ABS(nombre)

La fonction ENT permet d’arrondir un nombre à l’entier directement inférieur. Cela peut être pratique suivant le cas que vous souhaitez traiter. J’attire votre attention sur le comportement de cette fonction avec des nombres négatifs. En effet, par exemple, prenons la valeur -3.2, le résutlat de ENT(-3.2) sera -4. Voici la syntaxe de la fonction :

=ENT(nombre)

Et voici en image le résultat que donne les fonctions ABS et ENT en fonction de la valeur donnée :

Excel fonction ABS et ENT

MIN et MAX

Les fonctions MIN et MAX sont très pratique pour récupérer les valeurs extrêmes d’une liste de valeur. Voici les syntaxes des fonctions :

=MIN(valeur1 ; valeur2 ; …)

=MAX(valeur1 ; valeur2 ; …)

La liste de valeurs peut aussi être une plage de valeur dans Excel.

ARRONDI, ARRONDI.INF et ARRONDI.SUP

La fonction arrondi permet de récupérer la valeur arrondie avec un nombre de décimales défini. Un arrondi avec deux décimales du nombre 1.234 donnera 1.23. Si la décimale qui oriente l’arrondi est 5, l’arrondi sera à la valeur supérieur. Par exemple, l’arrondi à deux deux décimales du nombre 5.235 donne 5.24. Voici la syntaxe de la fonction :

=ARRONDI(nombre ; nb_decimales)

Les fonctions ARRONDI.INF et ARRONDI.SUP permettent d’imposer la direction de l’arrondi. La fonction ARRONDI.INF va arrondir la valeur avec le nombre de décimales donné en se rapprochant de 0. La fonction ARRONDI.SUP quant à elle arrondira la valeur en s’éloignant de zéro.

Excel Fonctions ARRONDI ARRONDI.INF ARRONDI.SUP

NB, NB.SI, NBVAL et NB.VIDE

La fonction NB permet de récupérer le nombre de cellules qui contiennent un nombre et seulement un nombre. Cette fonction peut s’avérer être très pratique pour gérer les cellules vides ou contenant du texte qu’on ne souhaite pas prendre en compte.

=NB(valeur1 ; valeur2 ; …)

La fonction NBVAL quant à elle permet de retourner le nombre de cellule non-vide. En d’autres termes, si une cellule contient un nombre ou du texte, celle-ci sera comptabilisée contrairement à la fonction NB qui ne prend en compte que les nombres.

=NBVAL(valeur1 ; valeur2 ; …)

La fonction NB.SI permet de compter le nombre de cellules qui répondent à un critère. Par exemple, on peut compter le nombre de personnes mineures dans une liste.

=NB.SI(plage ; critère)

La fonction NB.VIDE permet de récupérer le nombre de cellules vides dans la plage donnée. Elle a le comportement contraire de la fonction NBVAL. L’argument de la fonction NB.VIDE est une plage de cellule.

=NB.VIDE(plage)

Voici le résultat des fonctions appliquées sur la colonne Mineur / Majeur :

Excel Fonction NB NBVAL NB.SI et NB.VIDE

La fonction NB renvoie 0 car il n’y a aucun nombre dans la colonne traitée. Ensuite, la fonction NBVAL renvoie la valeur 9 car il y a 9 lignes non vides. La fonction NB.SI avec comme critère « Mineur » renvoie 2 car il n’y a que deux personnes mineures dans la liste, Lola étant considérée comme un bébé. Et enfin, la fonction NB.VIDE renvoie une valeur nulle car il n’y a aucune cellule vide dans la plage considérée.

NB.JOURS.OUVRES

Si vous avez besoin de connaître le nombre de jours ouvrés entre deux dates, la fonction NB.JOURS.OUVRES est faite pour vous ! Cette fonction vous servira afin de gérer des plannings ou de calculer une charge de travail d’une équipe. L’utilisation de cette fonction est très simple, il suffit de renseigner la date de début et la date de fin. Généralement ces deux valeurs seront stockées dans une cellule. Vous n’aurez donc qu’à faire référence à ces cellules. Voici la syntaxe de la fonction ainsi qu’un exemple.

=NB.JOURS.OUVRES(début ; fin ; [jours fériés])

Excel Fonction NB.JOURS.OUVRES

L’argument « jours fériés » est une liste de date, renseignée dans une plage de cellule, qui représente les jours fériés. Si ceux-ci tombent un jour normalement ouvré, ils seront alors décomptés du total.

Enfin, nous n’avons pas tous des métiers où nous travaillons du lundi au vendredi. Pour certains, les weekends sont les dimanches et lundis ou tout autre couple de jours. Il est possible que dans votre activité, il n’y ait qu’un seuil jour de fermeture par semaine. Pour gérer ces cas de figure, vous avez à disposition la fonction NB.JOURS.OUVRES.INTL dont voici la syntaxe :

=NB.JOURS.OUVRES.INTL(début; fin; [week-end]; [jours fériés])

Cette fonction rajoute l’argument « week-end » qui permet via un code dédié de définir quels sont les jours du weekend dans votre activité. Voici la liste des codes pour définir les jours de weekend.

Excel Fonction NB.JOURS.OUVRES.INTL

CONCATENER

La fonction CONCATENER permet de joindre ensemble plusieurs chaînes de caractères. Cela vous permet de créer une chaîne de caractère complexe à partir de plusieurs cellules contenant des chaînes de caractères simples.

Excel fonction CONCATENER

GAUCHE / DROITE et STXT

Les fonctions GAUCHE et DROITE permettent de récupérer un nombre de caractères définis d’une chaîne de caractères. La fonction GAUCHE commencera à récupérer les caractères par la gauche et la fonction DROITE, par la droite.

=GAUCHE(texte; [nb_caractères])

=DROITE(texte; [nb_caractères])

L’argument « texte » représente la chaîne de caractère que vous souhaitez traiter. L’argument « nb_caractères » est le nombre de caractères que vous souhaitez récupérer. Cet argument est obligatoire un entier positif. Si l’argument est supérieur à la longueur de la chaîne, la chaîne complète est renvoyée. L’argument « nb_caractères » est facultatif, si vous ne le renseignez pas, sa valeur par défaut est 1.

Ces fonctions sont très pratiques pour extraire des parties extrêmes d’une chaîne de caractère. Si vous devez extraire une portion de texte à l’intérieur d’une chaîne de caractère, vous pourrez alors utiliser la fonction STXT qui est une forme généralisée de la fonction GAUCHE.

=STXT(texte; départ; nb_caractères)

L’argument texte est la chaîne de caractère à traiter. Ensuite, l’argument « départ » représente le numéro du caractère à partir duquel vous voulez extraire votre sous-chaîne de caractères. Et enfin, l’argument « nb_caractères » est le nombre de caractères que vous souhaitez extraire.

CHERCHE

Maintenant que vous savez comment extraire des sous-chaînes de caractères, la fonction CHERCHE vous permet de récupérer la position de celle-ci. Cette fonction est particulièrement pratique si la position n’est pas la même à chaque fois. La fonction CHERCHE vous donnera alors la position dans chaque chaîne de caractères et vous pourrez utiliser ce résultat dans la fonction STXT par exemple.

CHERCHE(texte_cherché ; texte ; [départ])

L’argument « texte_cherché » est la sous-chaîne de caractères que vous souhaitez trouver dans la chaîne de caractère « texte ». L’argument « départ » permet de définir une position de départ autre que 1, qui est la valeur par défaut.

Voici deux exemples d’utilisation de la fonction issus de la documentation :

=CHERCHE(« n »; »imprimante »)

Ce premier exemple renvoie la valeur 8 car « n » est le huitième caractère dans le mot « imprimante ».

=CHERCHE(« forme »; »plateforme »)

Ce second exemple renvoie la valeur 6 car la sous-chaîne « forme » commence au sixième caractère dans le mot « plateforme ».

RECHERCHEV / RECHERCHEH et INDEX(EQUIV)

RECHERCHEV et RECHERCHEH

Les fonctions RECHERCHEV et RECHERCHEH sont deux fonctions très utiles qui permettent de réaliser des recherches dans un tableau. Ces deux fonctions fonctionnent de la même manière sauf dans la direction de recherche. La fonction RECHERCHEV va rechercher verticalement la valeur cible alors que la fonction RECHERCHEH recherchera horizontalement. Voici la syntaxe des deux fonctions :

=RECHERCHEH(valeur_cherchée, table_matrice, no_index_row, [valeur_proche])

=RECHERCHEV(valeur_cherchée , table_matrice, no_index_col, [valeur_proche])

Pour la fonction RECHERCHEH, la fonction va chercher la « valeur_cherchée » dans la première ligne de la « table_matrice ». L’argument « valeur_proche » détermine si vous recherchez la valeur exacte ou approchée. Si la valeur de l’argument est omis ou égale à VRAI et que la valeur exacte n’est pas trouvée, la fonction renvoie la valeur directement inférieure. Si la fonction trouve votre valeur sur la première ligne, cela détermine donc la colonne dans la matrice. Enfin, l’argument « no_index_row » représente l’index de la ligne dans la matrice considérée.

Pour la fonction RECHERCHEV, le fonctionnement est identique à la différence que la valeur cherchée est recherchée dans la première colonne de la matrice. Ensuite, il faudra renseigner le numéro de la colonne qui vous intéresse.

Voici un exemple qui illustre le fonctionne de la fonction :

Excel Fonction RECHERCHEV et RECHERCHEH

Une version améliorée de ces fonctions existe dans les versions récentes d’Excel. Si vous avez la chance d’avoir une version supérieure à 2019, vous pourrez utiliser la fonction RECHERCHEX qui généralise ces deux fonctions. Voici le lien vers la documentation officielle si cela vous intéresse.

Une autre manière de généraliser les fonctions RECHERCHEV et RECHERCHEH consiste à utiliser la combinaison des fonctions INDEX et EQUIV. La première permet de définir un tableau de recherche et la seconde de trouver une équivalence avec notre valeur de recherche pour obtenir la position de celle-ci.

INDEX / EQUIV

La fonction INDEX permet de renvoyer un élément d’une matrice à partir du numéro de ligne et de colonne de cet élément.

Ensuite, la fonction EQUIV permet de renvoyer le numéro de la ligne ou de la colonne où se trouve un élément recherché.

En combinant les deux, on peut alors créer une fonction qui généralise les fonctions RECHERCHEV et RECHERCHEH.

Le premier argument de la fonction INDEX est la matrice de travail. Ici c’est le tableau allant de la cellule contenant « Brigitte » jusqu’à la cellule contenant « Lola est une personne Bébé ».

Le second argument de la fonction INDEX est le numéro de la ligne. Pour récupérer ce numéro, la fonction EQUIV va aller chercher la position du prénom recherché, ici Benoît, dans la liste sélectionnée. Il faudra faire attention à bien sélectionner une colonne de taille équivalente à la matrice de base.

Le troisième argument de la fonction INDEX est le numéro de la colonne. La fonction EQUIV va donc renvoyer le numéro de la colonne correspondant à « CONCATENER », soit 4.

Le résultat donne donc « Benoît est une personne Majeure ».

Fonctions pour les dates

Il faut savoir qu’Excel gère les dates via un numéro de série. Ce numéro de série est un nombre entier qui correspond au nombre de jours depuis la date du 01/01/1900. Si vous rentrez dans une cellule la valeur 1 et que vous la mettez au format date, vous obtiendrez la date du 01/01/1900. Avec la valeur 50 000 vous obtiendrez la date du 21/11/2036 car c’est le 50 000e jour depuis le 01/01/1900.

Si vous rajoutez une décimale à ce nombre, cela pilotera les heures. La valeur 3.5 fait donc référence à la date du 03/01/1900 à 12h00.

Cela étant dit, passons maintenant aux fonctions utiles pour travailler avec les dates sous Excel.

MAINTENANT et AUJOURDHUI

La fonction =MAINTENANT() renvoie la date du jour au format JJ/MM/AAA HH/mm si aucun style n’est défini. Bien sûr vous pourrez toujours appliqué un format différent derrière.

La fonction =AUJOURDHUI() quant à elle renvoie uniquement la date du jour. Par exemple, si votre fichier Excel fait un suivi de planning, au lieu de devoir rentrer manuellement la date, vous pouvez utiliser cette fonction pour automatiquement avoir la date du jour.

La différence entre ces deux fonctions est que MAINTENANT renvoie l’heure alors que AUJOURDHUI uniquement le jour.

DATE

La fonction DATE permet de renvoyer le numéro de série d’une date à partir de l’année, du mois et du jour. La syntaxe est la suivante :

=DATE(année ; mois ; jour)

Les arguments sont tous des nombres, par exemple le chiffre 2 pour février.

JOUR, MOIS, ANNEE

Les fonctions JOUR, MOIS et ANNEE prennent pour argument un numéro de série correspondant à une date comme défini plus haut dans cet article. Si vous avez une valeur au format DATE, cela conviendra parfaitement. L’utilité de ces fonctions est de récupérer facilement le numéro du jour, du mois ou de l’année d’une date dans votre fichier. Cela facilite le traitement dans certains cas. Voici les syntaxes des fonctions :

=JOUR(numero_serie)

=MOIS(numero_serie)

=ANNEE(numero_serie)

SIERREUR

Voici une dernière fonction que j’apprécie tout particulièrement. Lorsque je construis des fichiers Excel pour d’autres utilisateurs, ceux-ci peuvent par mégarde rentrer les mauvais types de données. Les fonctions dans le classeur Excel peuvent alors renvoyer une erreur. Afin d’éviter de voir cette erreur, la fonction SIERREUR permet d’afficher une valeur par défaut si une erreur est remontée. Cela permet de garder une feuille de travail propre même si les données sont erronées.

=SIERREUR(valeur ; valeur_si_erreur)

L’argument « valeur » sera la formule que vous souhaitez utiliser. Pour l’argument « valeur_si_erreur », vous pourrez afficher le message ou la valeur ou même une formule que vous devrez définir.

Voilà pour la liste des fonctions utiles sous Excel. Si vous souhaitez passer à la vitesse supérieure en intégrant des macros VBA, voici un article qui vous présentera le sujet.

N’hésitez pas à laisser un commentaire, j’y répondrai avec grand plaisir.
A bientôt,
Benjamin

Si vous avez aimé l'article, vous êtes libres de le partager ! :)

Laisser un commentaire