文员必须会的excel公式【文员必学的excel教程】 收藏 0

成为同事眼中的Excel大神，学会这10组公式就够了，收藏备用吧

Hello，大家好，今天跟大家分享10组常用的Excel公式，大家如果在以后遇到类似的问题，直接套用函数即可，让你成为同事眼中的Excel大神！废话不多说，让我们直接开始吧！

公式：=–TEXT(MID(A2,7,8),\”0-00-00\”)

使用方法：复制公式将A2更改为你表中的单元格位置即可

原理概述：首先利用mid函数将身份证号码中的出生日期提取出来，随后利用text函数将提取出来的字符串设置为日期的显示样式，最后利用2个减号将其设置为常规格式。为什么要使用减号呢？这是因为text函数是文本函数，它的结果也是文本格式，文本格式下的日期是不能使用函数计算的，所以需要转换格式

公式：=DATEDIF(TEXT(MID(A2,7,8),\”0-00-00\”),TODAY(),\”y\”)

使用方法：复制公式后，只需更改A2为表格中对应的单元格即可

原理概述：首先利用TEXT(MID(A2,7,8),\”0-00-00\”)来提取出出生日期，随后利用DATEDIF来计算出生日期与今天相差了多少年，TODAY()函数的作用是获取今天的日期

公式：=IF(MOD(MID(A2,17,1),2)=1,\”男\”,\”女\”)

使用方法：复制公式，更改A2为对应的单元格位置即可

原理概述：身份证号码第17位是性别代码，偶数为女性，奇数为男性。首先使用mid函数将17位的数字提取出来，随后利用mod函数判断其奇偶性，最后利用if判断返回对应的结果

公式：=LOOKUP(1,0/((A2:A8=E2)*(B2:B8=F2)),C2:C8)

使用方法：复制公式更改为对应的单元格区域即可，格式为：=LOOKUP(1,0/(条件1*条件2*条件3),需要返回的结果列)

原理概述：这是一个数组公式，讲解起来比较复杂，主要是利用了逻辑值相乘，可以把true看做是1，false看着是0，两两相乘，结果为1对应的就是我们需要查找的数据

公式：=VLOOKUP(\”*\”&D2&\”*\”,$A$1:$B$8,1,FALSE)

使用方法：D2需要更改为表格中对应的查找值，A1:B8需要更改表格中对应的查找区域并绝对引用

原理概述： *号是一个通配符，他表示任意多个字符，我们将查找值得前后分别连接两个星号，这样的话就可以实现通过关键字查找数据

公式：=INDEX(A1:A10,MATCH(E2,B1:B10,0))

使用方法：复制公式替换为自己表中的单元格数据即可，A1:A10是需要返回的结果列，E2是查找值，B1:B10是查找的数据区域

原理概括：所谓的反向查找就是找到查找值左侧的数据，利用vlookup是非常复杂的，但是利用index+match却非常的简单，首先用match函数查找数据位置，随后利用index函数返回对应的结果即可

公式：=IF(COUNTIF($A$2:$A$10,A2)=1,\”\”,\”是\”)

使用方法：更改A2:A10与A2为你表中对应的单元格位置即可，A2:A10是统计的数据区域，需要进行绝对引用，A2是第一个数据

原理概述：主要是利用countif进行条件计数，如果数据不存在重复，它的计数结果就是1，否则的话结果就大于1，最后利用if函数做一个判断即可

公式：=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))

使用方法：复制公式后，只需要更改COUNTIF函数的第一第二参数为需要统计的数据区域即可

原理概述：首先利用countif函数计算重复值，随后用1除以这个结果，最后利用sumproduct求和，比如一个值重复出现3次，就会得到3个1/3，它们相加后结果是1

公式：=LEN(B2)-LEN(SUBSTITUTE(B2,\”，\”,\”\”))+1

使用方法：更改公式中的B2为你表格中的单元格，在SUBSTITUTE更换为你表格中的分符号。

原理概述：在这里每个姓名都是利用逗号隔开的，首先先计算下每个单元格的字符数，随后利用SUBSTITUTE函数将逗号都替换掉，然后再计算下字符数，两者相减就是逗号的个数，然后再加1就会得到人数

提取姓名：=LEFT(A2,LENB(A2)-LEN(A2))

提取电话：=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))

使用方法：将公式中的A2替换为自己表格中的单元格位置即可

原理概括：中文在excel中会占据2个字节，而数值仅仅只会占用1个字节，先利用len函数得到字符数，再利用lenb计算字节数，两者相减就会得到汉字的个数，随后利用left或者right函数提取数据即可

以上就是今天分享的所有的Excel公式，大家可以收藏下，以后遇到类似的问题，直接套用即可

我是Excel从零到一，关注我，持续分享更多Excel技巧

（此处已添加圈子卡片，请到今日头条客户端查看）

这10个Excel函数公式，职场必备，办公不求人

在职场办公中，对数据的统计分析应用最多的还是Excel，如果不掌握一定量的技巧，那在办公的过程中，肯定会求助于别人，今天，小编给大家带来10个Excel函数公式，让你在职场办公中不在求人。

一、Excel函数公式：自动计算年龄。

目的：根据员工的【出生日期】自动计算年龄，并保持更新。

方法：

在目标单元格中输入公式：=DATEDIF(C3,TODAY(),\”y\”)。

解读：

1、Datedif函数是一个系统隐藏函数，其作用为：以指定的方式计算两个日期之间的差值，语法结构为：=Datedif(开始日期,结束日期,统计方式)。常用的统计方式为“Y”、“M”、“D”，即“年”、“月”、“日”。

2、Today()函数的作用为获取当前的日期。

3、在Datedif函数中，第二个参数用Today()，而不用当前固定日期的原因在于，保持年龄的自动更新。

二、Excel函数公式：判断是否重复。

目的：判断【学历】是否重复。

方法：

在目标单元格中输入公式：=IF(COUNTIF(F$3:F$12,F3)>1,\”重复\”,\”不重复\”)。

解读：

1、如果要判断指定的值是否重复，可以统计该值的个数，如果该值的个数＞1，则肯定重复，否则不重复。

2、Countif函数的作用为单条件计数，语法结构为：=Countif(条件范围,条件)。返回值为数值。

3、用If函数判断Countif函数的返回值，如果＞1，则返回“重复”，否则返回“不重复”。

三、Excel函数公式：划分等次。

目的：判断【月薪】值，如果＞=3500元，则为“高薪”，否则为“低薪”。

方法：

在目标单元格中输入公式：=IF(G3>=3500,\”高薪\”,\”低薪\”)。

解读：

If函数是最常用的函数之一，功能为：判断是否满足某个条件，如果满足，则返回第二个参数，不满足则返回第三个参数。语法结构为：=if(判断条件,条件成立时的返回值,条件不成立时的返回值)。

四、Excel函数公式：多条件判断。

目的：判断【月薪】值，如果＞=3500，则为“高薪”，如果＜2500，则为“低薪”，大于等于2500，且小于3500的，则为“中等”。

方法：

在目标单元格中输入公式：=IFS(G3<2500,\”低薪\”,G3<3500,\”中等\”,G3>3500,\”高薪\”)。

解读：

1、遇到＞2种类型的判断时，一般情况下，都会用IF函数的嵌套形式去完成，例如，本示例中的公式为：=IF(G3>3500,\”高薪\”,IF(G3<2500,\”低薪\”,\”中等\”))。但随着嵌套的次数越多，公式就会变得越负责，越容易出错。

2、Ifs函数的作用为：检查是否满足一个或多个条件，并返回与第一个TRUE条件对应的值。语法结构为：=Ifs(判断条件1,返回值1,判断条件2,返回值2……判断条件N,返回值N)，判断条件和返回值是成对出现的。但此函数只能在16版及以上的Excel中或高版本的WPS中使用。

五、Excel函数公式：查找信息。

目的：根据【员工姓名】查找该员工的【月薪】。

方法：

在目标单元格中输入公式：=VLOOKUP(J3,B3:G12,6,0)。

解读：

1、Vlookup函数为最常用的查询引用函数，功能为：根据指定的值，返回指定范围内对应列的值。语法结构为：=Vlookup(查询值,数据范围,返回值所在的列数,匹配模式)。

2、参数“返回值所在的列数”为相对列数，是根据“数据范围”而定义的，并不是根据原数据定义的；“匹配模式”为“0”和“1”，“0”为精准查询，“1”为模糊查询。

六、Excel函数公式：排名。

目的：对员工的【月薪】从高到低进行排名。

方法：

在目标单元格中输入公式：=RANK(G3,G$3:G$12)。

解读：

1、Rank函数为常见的排序函数，语法结构为：=Rank(排序的值,数据范围,[排序方式])。

2、“排序方式”有2种，“0”和“1”，“0”或省略为降序，“1”为升序。

七、Excel函数公式：多条件计数。

目的：按【学历】统计【月薪】＞2000的人数。

方法：

在目标单元格中输入公式：=COUNTIFS(F3:F12,J3,G3:G12,\”>\”&K3)。

解读：

Countifs函数为多条件计数函数，语法结构为：=Countifs(条件1范围,条件1,条件2范围,条件2)。条件范围和条件必须成对出现，缺一不可哦！

八、Excel函数公式：快速计算总金额。

目的：根据每位员工的【销量】和【单价】快速计算总销售额。

方法：

在目标单元格中输入公式：=SUMPRODUCT(F3:F12,G3:G12)。

解读：

Sumproduct函数的作用为：返回相应的数组或区域乘积的和。语法结构为：=Sumproduct(数组1,[数组2],[数组3]……)。计算过程为：每个数组对应元素先乘积，再求和。

九、Excel函数公式：日期转星期。

目的：快速地计算出员工【出生日期】对应的星期。

方法：

在目标单元格中输入公式：=TEXT(C3,\”aaaa\”)。

解读：

1、Text函数的作用为根据指定的代码将指定的值转换为文本形式，语法结构为：=Text(值,格式代码)。

2、格式代码“aaaa”的作用为将对应的日期转为“星期X”的形式，如果要转为“X”的形式，则代码为：aaa。

十、Excel函数公式：多条件求和。

目的：按【性别】统计【月薪】＞3000的总和。

方法：

在目标单元格中输入公式：=SUMIFS(G3:G12,D3:D12,J3,G3:G12,\”>\”&K3)。

解读：

1、Sumifs函数的作用为：多条件求和，语法结构为：=Sumifs(求和区域,条件1区域,条件1,条件2区域,条件2……)。

2、参数“条件区域”和“条件”必须成对匹配使用。

10组职场人必备的Excel公式，学会它们，同事不敢再叫你小白

hello，大家好，今天跟大家分享10组Excel公式，都是我们在工作中用到的频率比较高的比较基础的公式，学会他们同事再也不敢叫你excel小白了，话不多下，下面就让我们来看下是哪些公式吧

公式：=VLOOKUP(D2,A2:B5,2,FALSE)

公式解释：

第一参数为查找的值，这里为橙子所在位置，D2

第二参数为数据区域，这里为单价表区域，A2:B5

第三参数为查找值在数据区域的第几列，所以这里为2

第四参数为精确匹配，所以为false

公式：=TEXT(MID(B2,7,8),\”0-00-00\”)

公式解释：

利用mid函数在身份证号码中提取出生日期，利用text函数将格式设置为日期格式

mid函数的用语法为：=mid（要提取的字符串你，从第几位开始提取，提取多少位）

在这里我们将公式设置为MID(B2,7,8)，就是从身份证的第七位开始提取，提取8位，然后我们使用text函数设置显示格式就好了

公式：=IF(MOD(MID(B2,17,1),2)=1,\”男\”,\”女\”)

公式解释

先使用mid函数提取身份证号码倒数第二位，当倒数第二是奇数性别为男，偶数性别为女，最后利用mod求奇偶，最后利用if函数判断

公式：=B2-TODAY()<7

公式解释：

合同到期日期减去今天的日期小于7就填充为红色

在这里我们是使用条件格式来制作的，当条件满足就会填充为红色，不满足就不做任何颜色的更改

TEXT函数：对数据进行格式转换

用法:=TEXT(字符串，想要设置的格式）

这个函数可以设置的格式很多，跟大家列举了3个工作中经常用到的情况

公式：=IF(B2>=90,\”优秀\”,IF(B2>=80,\”良好\”,IF(B2>=60,\”及格\”,\”不及格\”)))

公式解释：

If用法参数

第一参数：条件

第二参数：条件成立返回的值

第三参数：条件不成立返回的值

我们在每一个if函数的第三参数即错误值返回的结果中嵌套入一个if函数，当条件不满足时，会返回一个if函数

我们将小王的34分带入函数中，首先34>=90,这个条件是不成立的所以函数会返回if函数的第二参数，他的第二参数又是一个if函数，所以34会被带入第二个if函数中，在这里34>=80,依然不成立，这样的话34就会返回第二个if函数的第三参数，而他对应的仍然是一个if函数，所以会带入第三个if函数进行计算在这里34>=60依然不成立，函数会返回第三个if函数的第三参数，它对应的结果就是不及格

公式：=COUNTIF(C2:C15,\”成型车间\”)

解析

Countif函数的用法，

第一参数为条件区域

第二参数为条件

所以公式中第一参数为部门区域，第二区域为成型车间

公式：=SUMIF(C2:C17,\”成型车间\”,E2:E17)

解析sumif函数

第一参数为判断的区域

第二参数为条件

第三参数为求和区域

所以这里第一参数为部门区域，第二参数为成型车间，第三参数为求和区域

公式：=LEFT(C3,3)

Left函数：从数据左边提取文本

第一参数为要提取的字符串

第二参数为要提取多少位

公式：=RIGHT(C3,2)

right函数：从数据右边提取文本

第一参数为要提取的字符串

第二参数为要提取多少位

我是Excel从零到一关注持续分享更多Excel技巧

本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com