Top 10 des fonctions Excel les plus importantes
1. Fonction SI
IF Function renvoie une valeur si la condition que vous spécifiez est TRUE, sinon une autre valeur. En termes simples, la fonction SI peut d’abord tester une condition et renvoie une valeur basée sur le résultat de cette condition.
Syntaxe
SI(test_logique,valeur_si_vrai,valeur_si_faux)
Arguments
- logical_test : la condition que vous souhaitez évaluer.
- value_if_true : la valeur que vous souhaitez obtenir si cette condition est VRAIE.
- value_if_false : La valeur que vous voulez obtenir si cette condition est FALSE.
Remarques
- Le nombre maximum de conditions imbriquées que vous pouvez effectuer est de 64.
- Vous pouvez utiliser des opérateurs de comparaison pour évaluer une condition.
Exemple
Dans l’exemple ci-dessous, nous avons utilisé un opérateur de comparaison pour évaluer différentes conditions.
- Nous avons utilisé un texte spécifique pour obtenir le résultat si la condition est remplie ou non.
- Vous pouvez également utiliser TRUE et FALSE pour obtenir le résultat.
- Si vous ignorez la spécification d’une valeur pour obtenir le résultat si la condition est VRAIE, elle renverra zéro.
- Et si vous ignorez la spécification d’une valeur pour obtenir le résultat si la condition est FAUX, elle renverra zéro.
Dans l’exemple ci-dessous, nous avons utilisé la fonction SI pour créer une formule d’imbrication.
Nous avons spécifié une condition et si cette condition est fausse, nous avons utilisé un autre SI pour évaluer une autre condition et effectuer une tâche et si cette condition est FAUX, nous avons utilisé un autre SI.
De cette façon, nous avons utilisé IF cinq fois pour créer une formule d’imbrication. Vous pouvez utiliser la même chose 64 fois pour une formule d’imbrication.
2. Fonction SIERREUR
La fonction SIERREUR renvoie une valeur spécifique si une erreur se produit. En termes simples, il peut tester la valeur et si cette valeur est une erreur, il renvoie la valeur que vous avez spécifiée.
Syntaxe
SIERREUR(valeur, valeur_si_erreur)
Arguments
- valeur : La valeur que vous voulez tester pour l’erreur.
- value_if_error : la valeur que vous souhaitez obtenir en retour lorsqu’une erreur se produit.
Remarques
- La fonction SIERREUR concerne l’occurrence d’une erreur, pas le type de l’erreur.
- Si vous ignorez la valeur ou value_if_error, il renverra 0 dans le résultat.
- Il peut tester #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? et #NULL!.
- Si vous évaluez un tableau, il renverra un tableau de résultats pour chaque élément spécifié.
Exemple
Dans l’exemple ci-dessous, nous avons utilisé la fonction SIERREUR pour remplacer le #DIV/0 ! avec un texte significatif.
IFERROR n’est compatible qu’avec les versions 2007 et antérieures. Pour résoudre ce problème, vous pouvez utiliser ISERROR.
3. Fonction TRUNC
La fonction TRUNC renvoie un entier après avoir tronqué le nombre d’origine. En termes simples, il supprime les décimales d’un nombre avec une précision spécifique, puis renvoie la partie entière du résultat.
Syntaxe
TRUNC(nombre, [num_chiffres])
Arguments
- nombre : Le nombre que vous souhaitez tronquer.
- [num_digits] : un nombre pour spécifier la précision pour tronquer un nombre.
Remarques
- Si vous ignorez la spécification de plusieurs, il renverra une erreur.
- Il arrondit à partir de zéro.
- Si vous avez deux multiples sur la même distance, il renverra le multiple supérieur au nombre que vous arrondissez.
Exemple
Dans l’exemple ci-dessous, nous avons utilisé TRUNC pour tronquer les données afin de supprimer l’heure des dates.
4. Fonction SUMIF
La fonction SUMIF renvoie la somme des nombres qui remplissent la condition que vous spécifiez . En termes simples, il considère et calcule uniquement la somme des valeurs qui remplissent la condition.
Syntaxe
SUMIF(plage, critères, [somme_plage])
Arguments
- plage : une plage de cellules à partir de laquelle vous souhaitez vérifier les critères.
- critère : un critère qui peut être un nombre, un texte, une expression, une référence de cellule ou une fonction.
- [sum_range] : une plage de cellules contenant les valeurs que vous souhaitez additionner.
Remarques
- Si sum_range est omis, les cellules de la plage seront additionnées.
- Assurez-vous d’utiliser des guillemets doubles pour spécifier des critères de texte ou des critères qui incluent des symboles mathématiques, qui doivent être placés entre guillemets doubles.
- La taille de la plage de critères et de la plage de sommes doit être de la même taille.
Exemple
Dans l’exemple ci-dessous, nous avons spécifié A1:A9 comme plage de critères et B1:B9 comme plage de somme et après cela, nous avons spécifié les critères dans A12 qui a la valeur C.
Vous pouvez également insérer des critères directement dans la fonction. Dans l’exemple ci-dessous, nous avons utilisé un caractère générique astérisque pour spécifier un critère qui a un alphabet « S ».
Et, si vous ignorez la plage de somme, cela vous donnera la somme de la plage de critères. Mais cela ne sera possible que si la plage de critères a des valeurs numériques.
5. Fonction INDEX
La fonction INDEX renvoie une valeur à partir d’une liste de valeurs en fonction de son numéro d’index. En termes simples, INDEX renvoie une valeur à partir d’une liste de valeurs et vous devez spécifier la position de cette valeur.
Syntaxe
INDEX a deux syntaxes différentes. Dans le premier , vous pouvez utiliser une forme de tableau d’un index pour obtenir simplement une valeur à partir d’une liste en utilisant sa position.
INDEX(tableau, num_ligne, [num_colonne])
Dans le second , vous pouvez utiliser un formulaire de parrainage qui est moins utilisé dans la vraie vie, mais vous pouvez l’utiliser si vous avez plus d’une gamme à valoriser.
INDEX(référence, row_num, [column_num], [area_num])
Arguments
- tableau : une plage de cellules ou une constante de tableau.
- référence : une plage de cellules ou plusieurs plages.
- row_number : numéro de la ligne à partir de laquelle vous souhaitez obtenir la valeur.
- [col_number] : le numéro de la colonne à partir de laquelle vous souhaitez obtenir la valeur.
- [area_number] : si vous faites référence à plusieurs plages de cellules (en utilisant la syntaxe de référence), spécifiez un nombre pour faire référence à une plage parmi toutes celles-ci.
Remarques
- Lorsque les arguments row_num et column_num sont spécifiés, il renverra la valeur dans la cellule à l’intersection des deux.
- Si vous spécifiez row_num ou column_num comme 0 (zéro), il renverra le tableau de valeurs pour la colonne ou la ligne entière, respectivement.
- Lorsque row_num et column_num sont hors de la plage, il renverra une erreur #REF!.
- Si area_number est supérieur aux plages de nombres que vous avez spécifiées, il renverra #REF!.
Exemple 1 – Utilisation de ARRAY pour obtenir une valeur à partir d’une liste
Dans l’exemple ci-dessous, nous avons utilisé la fonction INDEX pour obtenir la quantité du mois de juin. Dans la liste, Jun est en 6ème position (6ème ligne) c’est pourquoi j’ai spécifié 6 dans row_number. INDEX a renvoyé la valeur 1904 dans le résultat.
Et si vous vous référez à une plage avec plus d’une colonne, vous devez spécifier le numéro de colonne.
Exemple 2 – Utilisation de REFERENCE pour obtenir la valeur de plusieurs listes
Dans l’exemple ci-dessous, au lieu de sélectionner toute la plage en une seule fois, je l’ai sélectionnée en trois plages différentes. Dans le dernier argument, nous avons spécifié 2 dans area_number qui définira la plage à utiliser à partir de ces trois plages différentes.
Maintenant, dans la deuxième plage, nous nous référons à la 5ème ligne et à la 1ère colonne. INDEX a renvoyé la valeur 172 qui se trouve dans la 5e ligne de la 2e plage.
6. Fonction RECHERCHEV
La fonction VLOOKUP recherche une valeur dans la première colonne d’une table et renvoie la valeur de la même ligne de la valeur correspondante à l’aide du numéro d’index. En termes simples, il effectue une recherche verticale.
Syntaxe
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Arguments
- lookup_value : une valeur que vous souhaitez rechercher dans une colonne. Vous pouvez faire référence à une cellule contenant la valeur de recherche ou entrer directement cette valeur dans la fonction.
- table_array : une plage de cellules, une plage nommée à partir de laquelle vous souhaitez rechercher la valeur.
- col_index_num : un nombre représente le numéro de la colonne à partir de laquelle vous souhaitez récupérer la valeur.
- range_lookup : utilisez false ou 0 pour établir une correspondance exacte et true ou 1 pour une correspondance appropriée. La valeur par défaut est Vrai.
Remarques
- Si VLOOKUP ne trouve pas la valeur que vous recherchez, il renverra un #N/A.
- VLOOKUP ne peut vous donner que la valeur qui se trouve à droite de la valeur de recherche. Si vous voulez regarder du côté droit, vous pouvez utiliser INDEX et MATCH pour cela.
- Si vous utilisez une correspondance exacte, elle ne correspondra qu’à la valeur qui se trouve en premier dans la colonne.
- Vous pouvez également utiliser des caractères génériques avec VLOOKUP .
- Vous pouvez utiliser TRUE ou 1 si vous voulez une correspondance appropriée et FALSE ou 0 pour une correspondance exacte.
- Si vous utilisez une correspondance appropriée (True) : il renverra la plus petite valeur suivante de la liste s’il n’y a pas de correspondance exacte.
- Si la valeur que vous recherchez est inférieure à la plus petite valeur de la liste, VLOOKUP renverra #N/A.
- S’il existe une valeur exacte que vous recherchez, elle vous donnera cette valeur exacte.
- Assurez-vous d’avoir trié la liste par ordre croissant.
Exemple
1. Utilisation de VLOOKUP pour les catégories
Dans l’exemple ci-dessous, nous avons une liste d’étudiants avec des notes qu’ils ont obtenues, et dans la colonne des remarques, nous voulons une note en fonction de leurs notes.
Dans la liste des marques ci-dessus, nous souhaitons ajouter des remarques selon la plage de catégories ci-dessous.
En cela, nous avons deux options à utiliser.
La PREMIÈRE consiste à créer une formule d’imbrication avec IF, ce qui prend un peu de temps, et la DEUXIÈME option consiste à créer une formule avec VLOOKUP avec une correspondance appropriée. Et la formule sera :
=VLOOKUP(B2,$E$2:$G$5,3,TRUE)
Comment ça fonctionne
J’utilise la colonne « MIN MARKS » pour faire correspondre la valeur de recherche et j’obtiens la valeur en retour de la colonne « Remarques ».
J’ai déjà mentionné que lorsque vous utilisez TRUE et qu’il n’y a pas de valeur de recherche de correspondance exacte, il renverra la plus petite valeur suivante à partir de la valeur de recherche. Par exemple, lorsque nous recherchons une valeur 77 dans la table des catégories, 65 est la plus petite valeur après 77.
C’est pourquoi nous avons obtenu « Bien » dans les remarques.
2. Gestion des erreurs dans la fonction VLOOKUP
L’un des problèmes les plus courants qui surviennent lorsque vous utilisez VLOOKUP est que vous obtiendrez #N/A chaque fois qu’aucune correspondance n’est trouvée. Mais la solution à ce problème est simple et facile. Permettez-moi de montrer avec un exemple simple.
Dans l’exemple ci-dessous, nous avons une liste de noms et leur âge et dans la cellule E6, nous utilisons la fonction VLOOKUP pour rechercher un nom dans la liste. Chaque fois que je tape un nom qui n’est pas sur la liste, j’obtiens #N/A.
Mais ce que je veux ici, c’est afficher un message significatif au lieu de l’erreur. La formule sera : =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)
Comment ça marche : IFNA peut tester une valeur pour #N/A et s’il y a une erreur, vous pouvez spécifier une valeur à la place de l’erreur.
7. Fonction IFNA
La fonction IFNA renvoie une valeur spécifique si une erreur #N/A se produit. Contrairement à IFERROR, il évalue uniquement l’erreur #N/A et renvoie la valeur que vous avez spécifiée.
Syntaxe
IFNA(valeur, valeur_if_na)
Arguments
- valeur : La valeur que vous souhaitez tester pour l’erreur #N/A.
- value_if_na : la valeur que vous souhaitez renvoyer si une erreur s’est produite.
Remarques
- Si vous ne spécifiez aucun argument, IFNA le traitera comme une chaîne vide (« »).
- Si une valeur est un tableau, elle renverra le résultat sous forme de tableau.
- Il ignorera toutes les autres erreurs #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? et #NULL!.
Exemple
Dans la fonction VLOOKUP, #N/A se produit lorsque la valeur de recherche n’est pas dans la plage de recherche et pour cela nous avons spécifié un message significatif en utilisant IFNA.
Remarque : IFNA est introduit dans Excel 2013, il n’est donc pas disponible dans les versions précédentes.
8. Fonction RAND
La fonction RAND renvoie un nombre aléatoire compris entre 0 et 1. En termes simples, vous pouvez générer un nombre aléatoire compris entre 0 et 1 (il met à jour sa valeur chaque fois que vous apportez une modification à la feuille de calcul).
Syntaxe
RAND()
Arguments
- Il n’y a pas d’argument à spécifier dans les fonctions RAND
Remarques
- Si vous mettez zéro dans un multiple, cela renverra zéro dans le résultat.
- Si vous ignorez la spécification de plusieurs, il renverra une erreur.
- Il arrondit à partir de zéro.
- Si vous avez deux multiples sur la même distance, il renverra le multiple supérieur au nombre que vous arrondissez.
Exemple
En plus d’avoir des nombres entre 0 et 1, vous pouvez également utiliser RAND pour des nombres aléatoires entre deux nombres spécifiques. Dans l’exemple ci-dessous, je l’ai utilisé pour créer une formule qui génère un nombre aléatoire entre 50 et 100.
Lorsque vous entrez cette formule dans une cellule, elle renvoie un nombre compris entre 100 et 50 en multipliant les valeurs renvoyées par le RAND avec l’équation que nous avons utilisée. Pour comprendre cette formule, nous devons la diviser en trois parties :
- Tout d’abord, lorsqu’il détecte le nombre le plus bas à partir du nombre le plus élevé, vous obtenez la différence entre les deux.
- Ensuite, il multiplie cette différence par le nombre aléatoire renvoyé après la soustraction.
- Et troisièmement, ajoutez ce nombre avec le nombre le plus bas restant dans la troisième partie de l’équation.
En relation: Comment générer rapidement des lettres aléatoires dans Excel
9. Fonction SOMME
La fonction SUM renvoie la somme des valeurs fournies. En termes simples, avec la fonction SOMME, vous pouvez calculer la somme d’une liste de valeurs (vous pouvez entrer directement une valeur dans la fonction ou vous référer à une plage de cellules.
Syntaxe
SOMME(nombre1,[nombre2],…)
Arguments
- nombre1 : nombre, plage de cellules contenant des nombres ou cellule unique contenant un nombre.
- [number2] : nombre, plage de cellules contenant des nombres ou cellule unique contenant un nombre.
Remarques
- Il ignore les valeurs de texte.
Exemple
Dans l’exemple ci-dessous, vous pouvez insérer des nombres directement dans la fonction en utilisant des virgules entre eux.
Vous pouvez également simplement vous référer à une plage pour calculer la somme des nombres et s’il y a un texte, une valeur logique ou une cellule vide, il les ignorera.
S’il y a une valeur d’erreur dans une cellule à laquelle vous faites référence, elle renverra #N/A dans le résultat.
Si vous avez des valeurs numériques formatées en tant que texte, il les ignorera. Il est recommandé de les convertir en nombres avant d’utiliser SUM.
10. Fonction OU
OR Function renvoie une valeur booléenne (TRUE ou FALSE) après avoir testé les conditions que vous spécifiez. En termes simples, vous pouvez tester plusieurs conditions avec la fonction AND et elle renvoie TRUE si l’une de ces conditions (ou toutes) est TRUE et renvoie FALSE uniquement si toutes ces conditions sont FALSE.
Syntaxe
OU(logique1, [logique2], …)
Arguments
- logical1 : Condition que vous souhaitez vérifier.
- [logical2] : Conditions supplémentaires que vous souhaitez vérifier.
Remarques
- Les valeurs seront ignorées si la cellule ou le tableau de référence contient une cellule ou un texte vide.
- Le résultat des conditions doit être en valeur logique (TRUE ou FALSE).
- Il renverra une erreur si aucune valeur logique n’est renvoyée.
Exemple
Dans l’exemple ci-dessous, nous avons créé une condition à l’aide de la fonction SI selon laquelle si un élève obtient 60 points au-dessus dans l’une des deux matières, la formule renvoie VRAI.
Maintenant, dans l’exemple ci-dessous, nous avons utilisé un nombre pour obtenir des valeurs logiques dans une formule. Vous pouvez également effectuer la condition ci-dessus dans l’ordre inverse.
Vous pouvez utiliser TRUE et FALSE au lieu de nombres. La fonction OU traite ces valeurs logiques comme des nombres.
Plus de tutoriels
Fonctions statistiques / Fonctions de date / Chaîne – Fonctions de texte / Fonctions financières