Partage pratique de compétences Excel : 10 formules les plus utilisées entre professionnels

Cet article a compilé les 10 formules Excel les plus couramment utilisées pour tout le monde sur le lieu de travail. J'espère qu'il pourra vous aider à résoudre vos problèmes.

C'est vraiment inefficace si vous ne savez pas comment. utiliser les fonctions de formule, mais il y a tellement de formules et de fonctions qu'il est impossible de toutes les apprendre en même temps. C'est un dilemme auquel de nombreux professionnels sont confrontés.

Aujourd'hui, j'ai résumé dix formules fréquemment utilisées à partager avec vous. Je crois que si vous apprenez ces dix formules, vous pouvez également réussir sur le lieu de travail.

Formule 1 : Comptage conditionnel

Le comptage conditionnel est très courant dans les applications Excel. Par exemple, le nombre de femmes dans la liste des statisticiens est un représentant typique du comptage conditionnel.

Le comptage conditionnel nécessite l'utilisation de la fonction COUNTIF. La structure de la fonction est =COUNTIF (zone statistique, condition). Dans cet exemple, la première formule =COUNTIF(B : B,G2 ), B:B est la zone statistique, G2 est la condition et le résultat de la formule indique qu'il y a 14 données « féminines » dans la colonne B. =COUNTIF(统计区域,条件)，在本例第一个公式=COUNTIF(B:B,G2)中，B:B就是统计区域，G2是条件，公式结果表示B列中为“女”的数据有14个。

第二参数条件可以不使用单元格引用，直接用具体内容作为条件，当条件为文本时，需要在条件两边添加英文状态的双引号，比如第二个公式=COUNTIF(B:B,"女")就是如此。 

公式2：快速标注重复数据

平时的工作中也经常会遇到标注重复值的问题，例如在一份销售明细表中，对重复的销售人员姓名进行标注。

首先使用公式=COUNTIF(A:A,A2)计算出每个姓名出现的次数，当结果大于1就表示姓名重复，进而使用IF函数得到最终的结果。

公式为：=IF(COUNTIF(A:A,A2)=1,"","重复")，结果如图所示。

判断姓名是否重复还有一种情况：第一次出现不算重复，从第二次起才算重复。

遇到这种情况，只需要修改COUNTIF函数的条件区域即可，公式为：=IF(COUNTIF($A:A2,A2)=1,"","重复")，结果如图所示。

公式3：多条件计数

如果要对多个条件进行统计，就要用到COUNTIFS函数，例如需要统计出男性中学历为本科的人数。

COUNTIFS的函数结构为=COUNTIFS(数据区域1,条件1,数据区域2,条件2,……)，最多可以有127组条件。

本例的第一个公式=COUNTIFS(B:B,G2,C:C,G3)中，共有两组条件，B列是对性别进行判断，C列是对学历进行判断。

同样，条件可以引用单元格，也可以直接使用具体内容，这与COUNTIF函数完全一致。

公式4：条件求和

除了条件计数，条件求和的应用也很广泛，例如在销售明细表中统计出电视销量的合计。

按条件求和时需要用到SUMIF函数，函数的结构为=SUMIF(条件区域,条件,求和区域)，在本例中，条件区域是B列，求和区域是C列，条件可以使用单元格也可以直接使用具体内容，要统计出电视销量的合计就有两种写法：=SUMIF(B:B,"电视",C:C)和=SUMIF(B:B,B2,C:C)。

公式5：多条件求和

有条件求和就会有多条件求和，例如要按照销售人员和商品名称两个条件进行求和时，就要用到多条件求和函数SUMIFS了。

SUMIFS函数的结构为=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2,……)，在本例中，求和区域是D列的销售数量，第一个条件区域是B列的销售人员，第二个条件区域是C列的商品名称，因此最终的公式为：

=SUMIFS(D:D,B:B,"沈伊杰",C:C,"壁挂空调")

La deuxième condition de paramètre ne peut pas utiliser de références de cellule, mais utiliser directement un contenu spécifique comme condition. Lorsque la condition est du texte, vous devez ajouter des guillemets anglais des deux côtés de la condition, comme la deuxième formule =. COUNTIF(B: B,"female")C'est tout. 🎜🎜🎜Formule 2 : Marquer rapidement les données répétées🎜🎜🎜🎜Nous rencontrons souvent le problème du marquage des valeurs répétées dans notre travail quotidien, comme dans un tableau de détail des ventes , marquez les noms des vendeurs en double. 🎜🎜 🎜🎜Utilisez d'abord la formule =COUNTIF(A:A,A2) pour calculer le nombre de fois que chaque nom apparaît. Lorsque le résultat est supérieur à 1, cela signifie que le nom est répété, puis utilisez. la fonction IF pour obtenir le résultat final. 🎜🎜La formule est : =IF(COUNTIF(A:A,A2)=1,"","Repeat"), et le résultat est tel qu'indiqué dans la figure. 🎜🎜 🎜🎜Il existe une autre façon de déterminer si un nom est répété : la première occurrence n'est pas considérée comme une répétition, et seule la deuxième occurrence est considérée comme une répétition. 🎜🎜Dans ce cas, il vous suffit de modifier la zone conditionnelle de la fonction COUNTIF. La formule est : =IF(COUNTIF($A$1:A2,A2)=1,"","Repeat" ), le résultat est tel qu'indiqué sur la figure. 🎜🎜 🎜🎜🎜Formule 3 : Comptage multi-conditions 🎜🎜🎜🎜Si vous souhaitez compter plusieurs conditions, vous devez utiliser la fonction COUNTIFS. Par exemple, vous devez compter. le nombre d'hommes ayant fait des études secondaires. Nombre d'étudiants de premier cycle. 🎜🎜 🎜🎜La structure fonctionnelle de COUNTIFS est =COUNTIFS (zone de données 1, condition 1, zone de données 2, condition 2,...), et il peut y avoir jusqu'à 127 ensembles de conditions. 🎜🎜Dans la première formule de cet exemple =COUNTIFS(B:B,G2,C:C,G3), il existe deux ensembles de conditions. La colonne B est utilisée pour juger du sexe et la colonne C est utilisée pour juger du niveau d'éducation. 🎜🎜De même, les conditions peuvent faire référence à des cellules ou utiliser directement un contenu spécifique, ce qui est exactement le même que la fonction COUNTIF. 🎜🎜🎜Formule 4 : Sommation conditionnelle🎜🎜🎜🎜En plus du comptage conditionnel, la sommation conditionnelle est également largement utilisée, comme le comptage des ventes de téléviseurs dans le total des détails des ventes. 🎜🎜 🎜🎜Vous devez utiliser la fonction SUMIF lors de la sommation par conditions. La structure de la fonction est =SUMIF (zone de condition, condition, zone de sommation). Dans cet exemple, la zone de condition est la colonne B et la zone de sommation est la colonne C. Les conditions peuvent Vous pouvez également utiliser directement des cellules pour utiliser un contenu spécifique. Pour calculer le total des ventes TV, il existe deux manières d'écrire : =SUMIF(B:B,"TV",C:C) et =SUMIF. (B:B,B2 ,C:C). 🎜🎜🎜Formule 5 : Somme multi-conditions 🎜🎜🎜🎜S'il y a une somme conditionnelle, il y aura une somme multi-conditions, par exemple, basée sur les deux conditions du vendeur et nom du produit Lors de la sommation, la fonction de sommation multi-conditions SUMIFS est utilisée. 🎜🎜 🎜🎜La structure de la fonction SUMIFS est =SUMIFS (zone de sommation, zone de condition 1, condition 1, zone de condition 2, condition 2,...). Dans cet exemple, la zone de sommation est la quantité des ventes dans la colonne D, la première La zone de condition est le vendeur dans la colonne B, et la deuxième zone de condition est le nom du produit dans la colonne C, donc la formule finale est : 🎜🎜=SUMIFS(D:D,B:B,"Shen Yijie" ,C:C ,"Climatiseur mural")🎜

Une chose à rappeler est que l'emplacement de la zone de sommation de SUMIFS est différent de celui de SUMIF. La zone de sommation de SUMIFS est dans le premier paramètre, tandis que la zone de sommation de SUMIF est dans le troisième paramètre. . Ne ​​vous y trompez pas !

Formule 6 : Calculer la date de naissance en fonction du numéro d'identification

Pour obtenir la date de naissance à partir du numéro d'identification, ce problème doit être familier à ceux qui travaillent dans des postes d'administration des ressources humaines, et la formule est relativement simple :

=TEXT(MID(A2,7,8),"0-00-00") obtiendra le résultat recherché, comme le montre la figure :

Pour comprendre le principe de cette formule , vous devez d'abord connaître la carte d'identité. Il y a certaines règles dans le numéro. Les cartes d'identité actuellement utilisées comportent essentiellement 18 chiffres, et les huit chiffres à partir du septième chiffre représentent la date de naissance.

Cette formule implique deux fonctions. Tout d'abord, regardons la fonction MID. La fonction MID a trois paramètres : =MID (où extraire, à partir de quel mot commencer et combien de mots prendre).

MID(A2,7,8) signifie intercepter huit chiffres à partir du septième numéro de la cellule A2. L'effet est comme indiqué dans la figure :

Après l'extraction de la date de naissance, ce n'est pas l'effet. nous avons besoin. A ce moment-là, c'est la fonction magicien TEXTE. La fonction TEXTE n'a que deux paramètres, et le format est =TEXTE (contenu à traiter, "afficher dans quel format"). fait partie de la fonction MID, et le format d'affichage est "0-00-00", bien sûr, vous pouvez l'afficher au format "0年00月00日", la formule doit être modifiée en =TEXT(MID (A2,7,8),"0年00月00日" " Y")

Une fonction Excel cachée DATEDIF est utilisée ici. La fonction nécessite trois paramètres et la structure de base est =DATEDIF (date de début). , délai, méthode de calcul). Dans cet exemple, la date de début est la date de naissance, en utilisant B2 comme premier paramètre ; la date limite est aujourd'hui, en utilisant la fonction AUJOURD'HUI() comme deuxième paramètre ; la méthode de calcul est calculée par année, en utilisant "Y" ; comme troisième paramètre. Si vous devez calculer l'âge directement à partir du numéro d'identification, la formule peut être écrite comme suit :

=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(), "Y" )

Formule 8 : Différents résultats sont obtenus selon les intervalles

Ce type de problème est principalement observé dans l'évaluation des performances. Par exemple, l'entreprise effectue une évaluation des performances des employés et doit déterminer. le niveau de récompense basé sur les résultats de l'évaluation. Les règles de notation sont les suivantes : un score inférieur à 50 est un E, un score de 50 à 65 (inclus) est un D, ​​un score de 65 à 75 (inclus) est un C, un score. de 75 à 90 (inclus) est un B, et un score de 90 ou plus est un A.

Vous pouvez utiliser la formule =LOOKUP(E2,{0;50;65;75;90},{"E";"D";"C";"B";"A"}) pour obtenir les récompenses de chaque Niveau d'employé, le résultat est tel qu'indiqué sur la figure :

Il est difficile d'expliquer le principe de cette formule. Vous pouvez vous référer aux tutoriels précédents liés à la fonction RECHERCHE. En fait, pour résoudre ce genre de problème, il suffit de rappeler la routine : LOOKUP renvoie les résultats correspondants par intervalle : =LOOKUP(score, {liste de valeurs limites inférieures}, {liste de niveaux de récompense}), et d'utiliser des points entre les valeurs limites inférieures sont séparés par des points-virgules, et les niveaux de récompense sont également séparés par des points-virgules.

Vous pouvez également saisir la relation correspondante entre la limite inférieure du score et le niveau de récompense dans le tableau. La formule peut être modifiée comme =LOOKUP(E2,$I$2:$J$6), et le résultat est celui indiqué. dans la figure.

Formule 9 : Données de correspondance à condition unique

Si vous voulez dominer le lieu de travail, que pouvez-vous faire si vous ne savez pas comment faire correspondre ? Comment puis-je effectuer une correspondance avec une seule condition sans RECHERCHEV ?

La structure de base de la fonction RECHERCHEV est =RECHERCHEV (que rechercher, où le chercher, quelle colonne rechercher, comment le rechercher. Par exemple, pour trouver le niveau d'éducation le plus élevé par votre nom). peut utiliser la formule =VLOOKUP(G2,B:E,4,0) pour obtenir Le résultat requis est tel qu'indiqué dans la figure :

Il y a deux points clés que vous devez connaître lorsque vous utilisez cette fonction : ①Le contenu que vous recherchez doit se trouver dans la première colonne de la plage de recherche. Par exemple, lors d'une recherche par nom, la plage de recherche commence par la colonne B au lieu de la colonne A.

②Le numéro de colonne fait référence à la colonne dans la plage de recherche plutôt qu'à la colonne du tableau. Par exemple, si vous souhaitez trouver le niveau d'éducation le plus élevé, il se trouve dans la 4ème colonne de la plage de recherche, et non dans la colonne numéro 5. le tableau.

Formule 10 : Faire correspondre des données avec plusieurs conditions

Vous serez vraiment invincible si vous apprenez à faire correspondre des données avec plusieurs conditions !

Donnez un exemple de correspondance de la quantité vendue selon deux conditions : nom et nom du produit, comme indiqué dans la figure :

La formule est =LOOKUP(1,0/(($A:$A=E2)*($B:$B=F2)),$C:$C)

La routine d'utilisation de la fonction RECHERCHE pour la correspondance multi-conditions est : = LOOKUP(1,0 /((plage de recherche 1 = valeur de recherche 1) * (plage de recherche 2 = valeur de recherche 2) *... * (plage de recherche n = valeur de recherche n)), plage de résultats), il convient de le noter le fait qu'il existe plusieurs conditions de recherche entre est une relation de multiplication, et elles doivent être placées dans le même ensemble de parenthèses que le dénominateur de 0/.

D'accord, les dix formules les plus couramment utilisées sont partagées ici. Une fois utilisées, vous pouvez vraiment dominer le lieu de travail !

Recommandations d'apprentissage associées : Tutoriel Excel

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!