Quelles sont les formules de fonctions Excel couramment utilisées ? Sur le lieu de travail, Excel est le plus souvent utilisé pour l'analyse statistique des données. Si vous ne maîtrisez pas un certain nombre de compétences, vous vous tournerez certainement vers d'autres personnes pour obtenir de l'aide au cours du processus de bureau. , l'éditeur vous le donnera. Il apporte 43 formules de fonctions Excel pour vous aider à arrêter de demander de l'aide sur le lieu de travail.
Il s'agit d'un module fonctionnel prédéfini dans Excel qui effectue des calculs, des analyses et d'autres tâches de traitement de données dans un ordre et une structure spécifiques. Par conséquent, les fonctions sont appelées « formules spéciales ». Comme les formules, le résultat final renvoyé par une fonction Excel est une valeur. Une fonction a un nom unique qui n'est pas sensible à la casse et détermine sa fonction et son objectif.
Pour donner un exemple simple : lors du traitement d'un tableau, comment mettre la première lettre du nom en majuscule ?
Si vous ne comprenez pas les fonctions, les modifiez-vous manuellement une par une ? Si vous connaissez la fonction Proper, vous ne la modifierez pas une par une. Entrez la formule de la fonction et faites-le en 3 secondes !
Les formules sont des calculs conçus par les utilisateurs et combinés avec des données constantes, des références de cellules, des opérateurs et d'autres éléments pour le traitement et le calcul des données. Les utilisateurs utilisent des formules pour calculer des résultats dans un but précis. Les formules d'Excel doivent donc (et ne peuvent) renvoyer que des valeurs.
La structure de la formule : =(C2+D2)*5 Du point de vue de la structure de la formule, les éléments qui composent la formule incluent généralement des éléments tels que des signes égal, des constantes, des références et des opérateurs. Parmi eux, le signe = est indispensable. Mais dans les applications réelles, les formules peuvent également être utilisées à l'aide de tableaux, de fonctions Excel ou de noms (formules nommées).
Normalement, Excel effectue les opérations de formule dans l'ordre de gauche à droite. Lorsque plusieurs opérateurs sont utilisés dans la formule, Excel effectuera les opérations en fonction de la priorité de chaque opérateur. Pour les opérateurs du même niveau, les opérations seront effectuées de gauche à droite. Et l'opération séquentielle à droite. L'ordre de priorité spécifique est le suivant :
Lors de l'utilisation de formules Excel pour les calculs, le résultat correct peut ne pas être obtenu pour une raison quelconque et une valeur d'erreur est renvoyée. Les valeurs d'erreur courantes et leurs significations sont indiquées dans le tableau ci-dessous.
Lorsque le résultat d'une formule renvoie une valeur d'erreur, la cause de l'erreur doit être trouvée rapidement et la formule doit être modifiée pour résoudre le problème.
Les fonctions Excel comprennent généralement le nom de la fonction, la parenthèse gauche, les paramètres, la virgule demi-largeur et la parenthèse droite.
Structure de la formule de fonction : =IF(A1>0,"nombre positif", IF(A1<0,nombre négatif,"")) Pour les paramètres de la fonction, elle peut être composée d'éléments tels que des valeurs numériques, des dates et du texte ou des constantes peuvent être utilisés, un tableau, une référence de cellule ou une autre fonction.
Lorsque les paramètres d'une fonction sont également des fonctions, Excel appelle cela une imbrication de fonctions. Il existe 11 types de fonctions au total, dont des fonctions de base de données, des fonctions de date et d'heure, des fonctions d'ingénierie, des fonctions financières, des fonctions d'information, des fonctions logiques, des fonctions de requête et de référence, des fonctions mathématiques et trigonométriques, des fonctions statistiques, des fonctions de texte et des fonctions définies par l'utilisateur. .
Le contenu de cet article est sous forme de catalogue, présentant ce que fait chaque fonction, quelle fonction peut être utilisée pour résoudre un certain problème, etc. Vous pouvez apprendre la méthode d'utilisation spécifique sur Baidu.
Pour les fonctions, vous n’avez pas besoin de les mémoriser par cœur. Il vous suffit de savoir quel type de fonction doit être sélectionné, quels paramètres sont requis et comment les utiliser ! Par exemple, sélectionnez des champs, utilisez les fonctions GAUCHE/DROITE/MID... laissez les autres détails au tout-puissant Baidu !
Ce qui suit est une classification et une introduction de ces fonctions nécessaires couramment utilisées selon différents scénarios d'application.
1. Classe de correspondance d'association
Les données requises ne se trouvent pas dans la même feuille Excel ou dans la même feuille Excel dans différentes feuilles. Trop de données sont difficiles à copier et sujettes aux erreurs. Comment les intégrer ? Les fonctions suivantes sont utilisées pour l'association multi-tables ou la comparaison ligne-ligne. Plus le tableau est complexe, plus il est amusant à utiliser !
01. RECHERCHEV
Fonction : Utilisé pour rechercher les éléments de la première colonne qui remplissent les conditions.
Syntaxe : =VLOOKUP (lookup_value,table_array, col_index_num, [range_lookup])
*Remarques : [ ] sont des paramètres facultatifs, les autres sont des paramètres obligatoires, il en va de même ci-dessous. = RECHERCHEV (élément à rechercher, emplacement à rechercher, numéro de colonne dans la plage contenant la valeur à renvoyer, renvoie une correspondance approximative ou exacte - indiquée par 1/VRAI ou 0/FAUX). Exemple : recherchez le poste de l'employé dont le nom se trouve dans la cellule F5.
02. RECHERCHEH
Fonction : recherchez une valeur dans la première ligne d'un tableau ou d'un tableau numérique, puis renvoyez la valeur dans la colonne de la ligne spécifiée du tableau ou du tableau. Le H dans HLOOKUP signifie « ligne ».
Syntaxe : =HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])
Exemple : =HLOOKUP("Axle",A1:C4, 2, TRUE) recherche l'essieu dans la première ligne et renvoie la valeur de la ligne 2 dans la même colonne (colonne A).
La différence entre LOOKUP et HLOOKUP : Lorsque la valeur de comparaison est située dans la première ligne du tableau de données, si vous souhaitez afficher le nombre de lignes spécifié vers le bas, vous pouvez utiliser HLOOKUP. RECHERCHEV peut être utilisée lorsque la valeur de comparaison se trouve dans la colonne de gauche des données à trouver.
03.INDEX
Fonction : renvoie une valeur ou une référence à une valeur dans un tableau ou une plage.
Syntaxe : =INDEX(array,row_num, [column_num])
Exemple : =INDEX(B2:D11,3,3) est la valeur située à l'intersection de la troisième ligne et de la troisième colonne dans la plage A2:B3.
04.MATCH
Fonction : utilisé pour renvoyer la position du contenu spécifié dans la zone spécifiée (une certaine ligne ou colonne).
Syntaxe : =MATCH(lookup_value,lookup_array, [match_type])
Exemple : =MATCH(41,B2:B5,0) Position de la valeur 41 dans la plage de cellules B2:B5.
match_type：
1 ou omis : MATCH trouve la valeur maximale inférieure ou égale à lookup_value.
0 : MATCH pour trouver la première valeur exactement égale à lookup_value.
-1 : MATCH pour trouver la plus petite valeur supérieure ou égale à lookup_value.
05.CLASSEMENT
Fonction : Trouver le classement d'une certaine valeur parmi un groupe de valeurs dans une certaine zone.
Syntaxe : =RANK(numéro,réf,[ordre])
Exemple : =RANK(A3,A2:A6,1) La méthode de classement de A3 dans A2:A6 dans le tableau ci-dessus : 0 est décroissant, 1 est ascendant, la valeur par défaut est 0
06.RANG
Fonction : renvoie le numéro de ligne référencé.
Syntaxe : = ROW([référence])
Exemple : = ROW() Le numéro de ligne de la ligne où se trouve la formule
07. COLONNE
Fonction : Renvoie la colonne où se trouve la cellule.
Syntaxe=COLONNE(référence)
Exemple : =COLUMN (D10) renvoie 4 car la colonne D est la quatrième colonne.
08. DÉCALAGE
Fonction : renvoie une référence à une cellule ou à une plage de cellules avec un nombre spécifié de lignes et de colonnes. La référence renvoyée peut être une seule cellule ou une plage de cellules. Vous pouvez spécifier le nombre de lignes et de colonnes à renvoyer.
Syntaxe : =OFFSET(référence, lignes, colonnes,[hauteur], [largeur])
Exemple : =OFFSET(D3,3,-2,1,1) affiche la valeur dans la cellule B6, où 3 correspond aux trois lignes ci-dessous, -2 aux deux lignes de gauche et 1 à la hauteur de la ligne et à la largeur de la colonne. .
Avant le traitement des données, les données extraites doivent être initialement nettoyées, par exemple en supprimant les espaces de chaînes, en fusionnant les cellules, en remplaçant, en interceptant les chaînes et en trouvant l'emplacement où les chaînes apparaissent.
Chaîne d'interception : utilisez MID /LEFT/RIGHT
Remplacer le contenu d'une cellule : SUBSTITUTE /REPLACE
Fusionner des cellules : utilisez CONCATENER
Effacer les espaces de chaîne : utilisez TRIM/LTRIM/RTRIM
Rechercher l'emplacement du texte dans une cellule : TROUVER/RECHERCHE
09.MID
Fonction : intercepter la chaîne du milieu
Syntaxe : =MID(text,start_num, num_chars)
Exemple : =MID(A2,1,5) À partir du premier caractère de la chaîne en A2, 5 caractères sont renvoyés.
Extrayez l’année et le mois en fonction du numéro d’identification.
10.GAUCHE
Fonction : intercepter la chaîne par la gauche.
Syntaxe : =LEFT(text,[num_chars])
Exemple : =LEFT(A2,4) Les quatre premiers caractères de la première chaîne.
11.DROITE
Fonction : intercepter la chaîne par la droite.
Syntaxe : =RIGHT(text,[num_chars])
Exemple : =RIGHT(A2,5) les 5 derniers caractères de la première chaîne
12. SUBSTITUT
Fonction : Remplacez old_text par new_text dans une chaîne de texte.
Syntaxe : =SUBSTITUTE(text,old_text, new_text, [instance_num])
Exemple : =SUBSTITUTE(A2, "Sales", "Cost") remplace "Sales" par "Cost" (données de coût) et remplace une partie du numéro de téléphone.
13.REMPLACER
Fonction : Remplace la chaîne dans la cellule.
Syntaxe : =REPLACE(old_text,start_num, num_chars, new_text)
Exemple : =REPLACE(A2,6,5,"*") Dans A2, à partir du sixième caractère (f), remplacez cinq caractères par un seul caractère *.
La différence entre REPLACE et SUBSTITUTE : les deux fonctions sont très proches. La différence est que REPLACE implémente le remplacement en fonction de la position et doit fournir le remplacement à partir de quelle position, le nombre de remplacements et le nouveau texte après le remplacement ; SUBSTITUTE remplace en fonction du contenu du texte et doit fournir le remplacement de l'ancien texte et du nouveau texte, et quel ancien texte est remplacé, etc. Par conséquent, REPLACE implémente le remplacement de texte à position fixe et SUBSTITUTE implémente le remplacement de texte à position fixe.
14. CONCATENER
Fonction : Concaténer deux ou plusieurs chaînes de texte en une seule chaîne.
Syntaxe : =CONCATENATE(text1,[text2], ...)
Une autre façon de fusionner le contenu des cellules est &. Lorsqu'il y a trop de contenu à fusionner, CONCATENATE est plus efficace.
Exemple : =CONCATENATE(B2, " ", C2) joint trois parties : la chaîne dans la cellule B2, le caractère espace et la valeur dans la cellule C2.
15.TRIM
Fonction : supprime tous les espaces du texte, à l'exception des espaces simples entre les mots.
Syntaxe : =TRIM(texte)
Le texte est le texte pour supprimer les espaces.
Exemple : =TRIM("First Quarter Earnings ") supprime les espaces de début et de fin du texte de la formule.
16.LTRIM
Fonction : Supprime les espaces ou autres caractères prédéfinis du côté gauche d'une chaîne.
Syntaxe : =LTRIM (chaîne, [charlist])
17.RTRIM
Fonction : Supprimez les espaces ou autres caractères prédéfinis du côté droit d'une chaîne.
Syntaxe : = LTRIM(string, [charlist])
18.TROUVER
Fonction : Trouver la position du texte
Syntaxe : =FIND(find_text,within_text, [start_num])
Exemple : =FIND("M",A2) La position du premier "M" dans la cellule A2
19. RECHERCHE
Fonction : renvoie la position à laquelle un caractère ou une chaîne de texte spécifié apparaît pour la première fois dans la chaîne, en recherchant de gauche à droite.
Syntaxe : =SEARCH(find_text,within_text,[start_num])
Exemple : =SEARCH("e",A2,6) Dans la chaîne de la cellule A2, en commençant par la 6ème position, la position du premier "e".
La différence entre FIND et SEARCH : Les fonctions de ces deux fonctions sont presque les mêmes, et elles peuvent trouver l'emplacement des caractères. La différence est que la fonction FIND recherche avec précision et est sensible à la casse ; sensible aux majuscules et minuscules.
20.LEN
Fonction : renvoie le nombre de caractères dans la chaîne de texte.
Syntaxe : =LEN(texte)
Exemple : =LEN(A1) La longueur de la chaîne dans la cellule A1
21.LENB
Fonction : renvoie le nombre d'octets utilisés pour représenter les caractères dans une chaîne de texte.
Syntaxe : =LENB(texte)
Exemple : =LEN(A1)Le nombre d'octets dans la chaîne de cellules A1.
La logique, comme son nom l'indique, n'entre pas dans les détails, il suffit d'aller à la fonction.
22. SI
Fonction : lors de l'utilisation de la fonction logique IF, si la condition est vraie, la fonction renverra une valeur ; si la condition est fausse, la fonction renverra une autre valeur.
Syntaxe : =IF(Logique,Value_if_true,Value_if_false)
La fonction IF renvoie une valeur si la condition spécifiée est évaluée à vrai et une autre valeur si la condition est évaluée à faux.
23.COUNTIF
Fonction : Utilisé pour compter le nombre de cellules qui remplissent une certaine condition ; par exemple, compter le nombre de fois qu'une ville spécifique apparaît dans la liste des clients.
Syntaxe : =COUNTIF(cellule 1 : cellule 2, condition)
Comptez le nombre de fois qu'un magasin spécifique apparaît dans la liste.
24.ET
Fonction : jugement logique, équivalent à « union ».
Syntaxe : si tous les paramètres sont vrais, True sera renvoyé. Il est souvent utilisé pour le jugement multi-conditions.
Exemple : =AND(A2>1,A2<100) Si A2 est supérieur à 1 et inférieur à 100, TRUE est affiché sinon, FALSE est affiché ;
25.OU
Fonction : jugement logique, équivalent à « ou ».
Syntaxe : tant que le paramètre a la valeur True, Ture sera renvoyé, souvent utilisé pour le jugement multi-conditions.
Exemple : =OR(A2>1,A2<100) Si A2 est supérieur à 1 ou inférieur à 100, TRUE est affiché sinon, FALSE est affiché ;
Lors de l’utilisation de statistiques de tableaux Excel, il est souvent nécessaire d’utiliser diverses formules fournies avec Excel, qui est également le type le plus couramment utilisé. (Pour ceux-ci, Excel est livré avec des fonctions de raccourci)
26.MIN
Fonction : trouver la valeur minimale dans une certaine zone.
Syntaxe : =MIN(numéro1, [numéro2], ...)
Exemple : =MIN(D2:D11) Le nombre minimum dans la plage D2:D11.
27.MAX
Fonction : trouver la valeur maximale dans une certaine zone.
Syntaxe : =MAX(numéro1, [numéro2], ...)
Exemple : =MAX(A2:A6) La valeur maximale dans la zone A2:A6.
28. MOYENNE
Fonction : Calculer la valeur moyenne dans une certaine zone.
Syntaxe : =AVERAGE(numéro1, [numéro2], ...)
Exemple : =AVERAGE(D2:D11) La moyenne des nombres dans la plage de cellules D2 à D11.
29.COMTE
Fonction : Comptez le nombre de cellules contenant des nombres.
Syntaxe : =COUNT(valeur1, [valeur2], ...)
Exemple : =COUNT(A2:A7) Compte le nombre de cellules contenant des nombres dans la plage de cellules A2 à A7.
30.COMPTIFS
Fonction : Comptez le nombre de cellules spécifiées par un ensemble de conditions donné.
Syntaxe : COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],…)
Exemple : =COUNTIFS(A2:A7,"<6",A2:A7,">1") calcule le nombre de nombres compris entre 1 et 6 (à l'exclusion de 1 et 6) contenus dans les cellules A2 à A7.
31.SOMME
Fonction : Calculez la somme de toutes les valeurs de la plage de cellules.
Syntaxe : =SOMME(cellule 1 :cellule 2)
Exemple : =SUM(A2:A10) additionne les valeurs des cellules A2:10.
32.SUMIF
Fonction : Trouvez la somme des cellules qui remplissent les conditions.
Syntaxe : =SUMIF(plage,critères, [sum_range])
Exemple : =SUMIF(A2:A7,"Fruit",C2:C7) La somme des ventes de tous les aliments dans la catégorie "Fruit".
32.SUMIFS
Fonction : additionnez un groupe de cellules qui remplissent les conditions spécifiées.
Syntaxe : =SUMIFS(sum_range,criteria_range1, criteria_range2, criteria2], ...)
Exemple : =SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "LUNING") Calculez la quantité totale de produits commençant par "香" et vendus par "LUNING".
33.SUMPRODUCT
Fonction : renvoie la somme des produits de tableau ou de zone correspondants.
Syntaxe : =SUMPRODUCT (array1, [array2], [array3], ...)
Exemple : =SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) Calculez la somme des produits de A1 à A100 du tableau 1 et de B1 à B100 du tableau 2, soit A1*B1+A2 *B2+A3* B3+…
34.STDEV
Fonction : Estimer l’écart type en fonction de l’échantillon.
Syntaxe : STDEV(numéro1,[numéro2],...)
Exemple : =STDEV(D2:D17) écart type de la colonne
35.SOUS-TOTAL
Fonction : Renvoie le sous-total dans la liste ou la base de données.
Syntaxe : =SOUS-TOTAL(num_fonction,ref1,[ref2],...)
Exemple : =SUBTOTAL(9,A2:A5) utilise 9 comme premier paramètre pour calculer la somme des valeurs du sous-total dans les cellules A2:A5.
http://36.INT／ROUND
Fonction : La fonction ROUND arrondit un nombre à un nombre spécifié de chiffres.
Syntaxe : =ROUND(A1, 2)
Exemple : =ROUND(2,15, 1) arrondit 2,15 à une décimale
Fonction : INT arrondit un nombre à l’entier le plus proche.
Syntaxe :=INT(8.9) Arrondit 8,9 à l'entier le plus proche.
Spécialement conçu pour gérer les formats d'heure et les conversions.
37.AUJOURD'HUI
Fonction : Renvoie le numéro de série de la date actuelle.
Syntaxe : =AUJOURD'HUI()
li'z=TODAY()+5 renvoie la date actuelle plus 5 jours. Par exemple, si la date actuelle est le 01/01/2012, cette formule renvoie le 06/01/2012.
38.MAINTENANT
Fonction : Renvoie le numéro de série de la date et de l'heure actuelles.
Syntaxe : =Maintenant()
=MAINTENANT()+7 renvoie la date et l'heure 7 jours plus tard.
39.ANNÉE
Fonction : Renvoie l'année correspondant à une certaine date.
Syntaxe : =ANNÉE(numéro_série)
=ANNÉE(A3) L'année de la date dans la cellule A3
40.MOIS
Fonction : Renvoie le mois dans la date.
Syntaxe : =MOIS(numéro_série)
=MOIS(A2) Le mois de la date dans la cellule A2
41.JOUR
Fonction : renvoie le nombre de jours dans une date exprimé sous forme de numéro de série.
Syntaxe : =DAY(numéro_série)
=JOUR(A2) Le nombre de jours dans la date dans la cellule A2
42.JOUR DE LA SEMAINE
Fonction : Renvoie le jour de la semaine correspondant à une certaine date. Par défaut, le nombre de jours est un nombre entier compris entre 1 (dimanche) et 7 (samedi).
Syntaxe : =WEEKDAY(serial_number,[return_type])
=WEEKDAY(A2) Le jour de la semaine de 1 (dimanche) à 7 (samedi)
=WEEKDAY(A2, 2) Le jour de la semaine de 1 (lundi) à 7 (dimanche).
43.DATEDIF
Fonction : Calculer le nombre de jours, de mois ou d'années entre deux dates.
Syntaxe : =DATEDIF(date_début,date_fin,unité)
=DATEDIF(Start_date,End_date,"Y")Le nombre d'années dans une période
=DATEDIF(Start_date,End_date,"D")Le nombre de jours dans une période
=DATEDIF(Start_date,End_date,"YD") ignore l'année dans la date et le nombre de jours dans une période
Ci-joint:
