工作中最常用的12个函数公式—5大工作函数搞定你的表格 收藏 0

职场人必备的15个excel函数公式，简单易懂，快速提高工作效率

Hello.大家好，今天跟大家分享15个Excel函数公式，都是我们工作中经常用到的公式，工作中遇到类似的问题，可直接套用，快速提高工作效率，话不多说，下面就让我们来一起学习下吧

1. 身份证号码提取出生日期

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

在这里我们使用mid函数提取身份中号码中的出生日期，然后使用text函数设置数字的格式，因为text是一个文本函数，所以它输出的结果是一个文本，我们在公式的最前面输入两个减号，将文本格式的数值转换为常规格式的设置

2. 计算年龄

公式：=DATEDIF(C3,TODAY(),\”Y\”)

Datedif函数的作用是计算两个时间的差值，

第一参数：开始时间，就是我们提取身份证号码的时间

第二参数：结束时间，我们使用today函数获取今天的日期

第三参数：返回类型，在这里我们设置为Y，代表年份

3. 根据身份证号码提取性别

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

身份证号码中也包含着每个人的性别信息，它只与身份证号码的第17位有关，第17位是计数性别为男，为偶数性别则为女，所以我们使用mid提取第17位的数值，然后使用mod函数计算这个数的奇偶性，最后使用if函数判断输出结果

4. 合并单元格求和

公式：=SUM(C3:C14)-SUM(D4:D14)

在这里我们需要先选择想要求和的区域，然后在编辑栏输入公式，最后按Ctrl+回车批量填充公式

5. 合并单元格计数

公式：=COUNTA(C3:C14)-SUM(D4:D14)

在这里我们需要先选择想要计数的区域，然后在编辑栏输入公式，最后按Ctrl+回车批量填充公式

6. 合并单元格填充序号

公式：=MAX($A$2:A2)+1

在这里我们需要先选择想要添加序号的数据区域，然后在编辑栏输入公式，最后按Ctrl+回车批量填充公式

7. 按照类别排序

公式：=COUNTIF($B$3:B3,B3)

8. 查找重复内容

公式：=IF(COUNTIF(C:C,C3)>1,\”重复\”,\”\”)

在这里我们首先使用countif函数对C列进行计数，如果结果大于1代表有重复值，然后我们使用if函数来判断结果是不是大于1，大于1就返回重复，如果不大于1就返回空值

9. 条件计数

公式：=COUNTIF(B:B,E2)

Countif函数的作用：条件计数，

语法：=countif（要统计的数据区域，统计条件）

10. 条件求和

公式：=SUMIF(B3:B15,F3,D3:D15)

Sumif函数的作用：条件求和

语法：=sumif（要统计的数据区域，求和条件，求和区域）

11. 条件判断

公式：=IF(C3>=60,\”通过\”,\”不通过\”)

If函数是一个条件判断函数，他能根据条件来返回对应的结果

语法：=IF（判断条件，条件正确时返回的结果，条件错误时返回的结果）

12. 生成随机数

随机小数公式：=RAND()

这个函数不需要参数，选择函数后直接点击回车即可生成小数

随机整数：=RANDBETWEEN(1,100)

在这里我们生成的是一个1到100之间的随机整数，

语法：=RANDBETWEEN（最小值，最大值）

13. 隔行求和

公式：=SUMPRODUCT((MOD(ROW(C3:G8),2)=0)*C3:G8)

在这里我们先使用row函数获取行号，然后使用mod函数判断每一行的奇偶性，最后使用SUMPRODUCT函数进行求和

14. 隔列求和

公式：=SUMPRODUCT((MOD(COLUMN(B4:G8),2)=1)*B4:G8)

与隔行求和相似，使用COLUMN函数获取列号，然后使用mod判断奇偶性，最后求和

15. 排名

公式：=RANK(C3,$C$3:$C$15)

语法：RANK(数值，要名字的数值区域)

以上这些公式你都知道吗？

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

Excel工作表中最常用的9类21个公式，动图演示，中文解读

在Excel工作表中，有一个非常重要的工具，那就是函数公式，如果要全部掌握，几乎是不可能的，但是对于一些常用的公式则必须掌握，例如下文的9类21个公式！

一、Excel工作表函数：求和类。

（一）Sum。

功能：计算指定的单元格区域中所有数值的和。

语法结构：=Sum(值1,值2……值N)。

目的：计算总“月薪”。

方法：

在目标单元格中输入公式：=SUM(1*G3:G12)，并用Ctrl+Shift+Enter填充。

解读：

如果直接用Sum函数进行求和，结果为0，究其原因就在于“月薪”为文本型的数值，如果不想调整数据类型，可以给每个参数乘以1将其强制转换为数值类型，然后用Sum函数进行求和。

（二）Sumif。

功能：对满足条件的单元格求和，即单条件求和。

语法结构：=Sumif(条件范围,条件,[求和范围])，当“条件范围”和“求和范围”相同时，可以省略“求和范围”。

目的：根据“性别”计算总“月薪”。

方法：

在目标单元格中输入公式：=SUMIF(D3:D12,J3,G3:G12)。

解读：

由于“条件范围”和“求和范围”不相同，所以不能省略参数“求和范围”。

（三）Sumifs。

功能：对一组给定条件指定的单元格求和。

语法结构：=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……)

目的：根据“性别”统计相应“学历”下的总“月薪”。

方法：

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

解读：

参数“条件范围”和“条件”必须成对出现，否则公式无法正确执行！

二、Excel工作表函数：判断类。

（一）If。

功能：判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。

语法结构：=If(判断条件,条件为真时的返回值,条件为假时的返回值)。

目的：判断“月薪”的档次，如果≥4000，则返回“高”，如果≥3000，则返回“中”，否则返回“低”。

方法：

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

解读：

If函数除了单独判断外，还可以嵌套使用，但多级嵌套时，需要理清逻辑关系，否则容易出错！

（二）Ifs。

功能：判断是否满足一个或多个条件并返回与第一个TRUE条件对应的值。

语法结构：=Ifs(条件1,返回值1,条件2,返回值2……)。

目的：判断“月薪”的档次，如果≥4000，则返回“高”，如果≥3000，则返回“中”，否则返回“低”。

方法：

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

解读：

参数中的“条件”和“返回值”必须成对出现，但该函数仅能应用于16及以上版本的Excel中，在WPS高版本中也可以使用哦！

三、Excel工作表函数：查找类。

（一）Vlookup。

功能：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的的序号，再进一步返回选定单元格的值。

语法结构：=Vlookup(查询值,数据范围,返回值列数,查询模式)，查询模式分为精准查询和模糊查询。

目的：根据“员工姓名”查询对应的“月薪”。

方法：

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

解读：

参数“返回值列数”要根据“数据范围”来确定，是返回值所在的的相对列数。

（二）Lookup。

功能：从单行或单列或单数组中查找一个值。

Lookup函数具有两种语法结构：向量形式和数组形式。

1、向量形式。

功能：从单行或单列中查找查找指定的值，返回第二个单行或单列中相同位置的值。

语法结构：=Lookup(查找值，查找值所在范围，[返回值所在范围])，当“查找值所在范围”和“返回值所在范围”相同时，可以省略“返回值所在范围”。

目的：根据“员工姓名”查询对应的“月薪”。

方法：

1、以“员工姓名”为主要关键字进行升序排序。

2、在目标单元格中输入公式：=LOOKUP(J3,B3:B12,G3:G12)。

解读：

在使用Lookup函数查询数据时，首次要以“查询值”为主要关键字进行升序排序，否则无法得到正确的结果。

2、数组形式。

功能：从指定的范围第一列或第一行中查询指定的值，返回指定范围中最后一列或最后一行对应位置上的值。

语法：=Lookup(查找值，查询范围)。

重点解读：

从“功能”中可以看出，Lookup函数的数组形式，查找值必须在查询范围的第一列或第一行中，返回的值必须是查询范围的最后一列或最后一行对应的值。即：查找值和返回值在查询范围的“两端”。

目的：根据“员工姓名”查询对应的“月薪”。

方法：

1、以“员工姓名”为主要关键字进行升序排序。

2、在目标单元格中输入公式：=LOOKUP(J3,B3:G12)。

解读：

查询值必须在数据范围的第一列，返回值必须在数据范围得最后一列。

3、优化形式。

目的：根据“员工姓名”查询对应的“月薪”。

方法：

在目标单元格中输入公式：=LOOKUP(1,0/(B3:B12=J3),G3:G12)。

解读：

“优化形式”其本质还是向量形式，但在此必须了解Lookup函数的一个特定，就是当查询不到指定的值时，会自动向下匹配，原则为小于当前值的最大值。如果公式中的条件不成立，则返回错误值，如果公式成立，则返回0，小于查询值的最大值为0，所以返回相应位置的值。

四、Excel工作表函数：统计类。

（一）Countif。

功能：计算指定区域中的满足条件的单元格数量，即单条件计数。

语法结构：=Countif(条件范围,条件)。

目的：计算“月薪”在指定范围内的人数。

方法：

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

解读：

条件计数函数除了Countif函数外，还有多条件计数函数Countifs。

（二）Averageifs。

功能：查找一组给定条件指定的单元格的算术平均值。

语法结构：=Averageifs(数值范围,条件1范围,条件1,条件2范围,条件2……)

目的：根据“性别”统计相应“学历”下的平均“月薪”。

方法：

在目标单元格中输入公式：=AVERAGEIFS(G3:G12,D3:D12,J3,F3:F12,K3)。

解读：

参数“条件范围”和“条件”必须成对出现。

五、Excel工作表函数：提取类。

（一）Left。

功能：从一个字符串中的第一个字符开始返回指定个数的字符。

语法结构：=Left(字符串,[字符长度])，当省略“字符长度”时，默认值为1。

目的：提取“员工姓名”中的“姓”。

方法：

在目标单元格中输入公式：=LEFT(B3,1)。

解读：

也可以使用公式：=LEFT(B3)。

（二）Mid。

功能：从指定字符串中的指定位置起返回指定长度的字符。

语法结构：=Mid(字符串,开始位置,字符长度)。

目的：返回“月薪”中的第2、3位。

方法：

在目标单元格中输入公式：=MID(G3,2,2)。

六、Excel工作表函数：日期类。

（一）Datedif。

功能：以指定的方式计算两个日期之间的差值。

语法结构：=Datedif(开始日期,结束日期,统计方式)，常见的统计方式有“Y”、“M”、“D”，即“年”、“月”、“日”。

目的：计算距离2021年元旦的天数。

方法：

在目标单元格中输入公式：=DATEDIF(TODAY(),\”2021-1-1\”,\”d\”)。

解读：

用Today函数获取当前日期，计算距离2021年1月1日的天数。

（二）Days。

功能：返回两个日期之间的天数。

语法结构：=Days(结束日期,开始日期)。

目的：计算距离2021年元旦的天数。

方法：

在目标单元格中输入公式：=DAYS(\”2021-1-1\”,TODAY())。

解读：

Days函数的参数顺序为“结束日期”、“开始日期”，而并不是“开始日期”、“结束日期”，和Datedif函数要区别使用！

七、Excel工作表函数：数字处理类。

（一）Round。

功能：按指定的位数对数值四舍五入。

语法结构：=Round(值或单元格引用,小数位数)。

目的：对“月薪”四舍五入后保留2位小数。

方法：

在目标单元格中输入公式：=ROUND(G3,2)。

（二）Randbetween。

功能：返回介于指定的数值之间的随机值。

语法结构：=Randbetween(下限值,上限值)。

目的：生成1000至2000之间的随机值。

方法：

在目标单元格中输入公式：=RANDBETWEEN(1000,2000)。

解读：

如果要更改随机值，按F9即可。

八、Excel 工作表函数：数据库函数。

（一）Dsum。

功能：求满足给定条件的数据库中记录的字段（列）数据的和。

语法结构：=Dsum(数据库区域,返回值所在的相对列数（列标题的相对引用、列标题),条件区域)。

注意事项：

1、参数“数据库区域”和“条件区域”必须包含有效的列标题。

2、第二个参数用“列标题”作为返回依据时，其值必须包含在\”\”（英文双引号）中，如“月薪”、“婚姻”等。

目的：根据“性别”统计“月薪”。

方法：

在目标单元格中输入公式：=DSUM(B2:G12,\”月薪\”,J2:J3)。

（二）Dget。

功能：从数据库中提取符合指定条件且唯一存在的记录。

语法结构：=Dget(数据库区域,返回值所在的相对列数（列标题的相对引用、列标题),条件区域)。

注意事项：

1、参数“数据库区域”和“条件区域”必须包含有效的列标题。

2、第二个参数用“列标题”作为返回依据时，其值必须包含在\”\”（英文双引号）中，如“月薪”、“婚姻”等。

目的：根据“员工姓名”查询对应的“月薪”。

方法：

在目标单元格中输入公式：=DGET(B2:G12,\”月薪\”,J2:J3)。

九、Excel工作表函数：其他类。

（一）Len。

功能：返回文本字符串中的文本个数。

语法结构：=Len(值或单元格引用)。

目的：计算“员工姓名”的长度。

方法：

在目标单元格中输入公式：=LEN(B3)。

（二）Cell。

功能：返回引用中第一个单元格的格式，位置或内容的有关信息。

语法结构：=Cell(返回类型,[单元格区域])。

目的：显示当前工作表的文件路径。

方法：

在目标单元格中输入公式：=CELL(\”filename\”)。

6个工作中常用的Excel函数公式，让表格数据计算变得更简单

【温馨提示】亲爱的朋友，阅读之前请您点击【关注】，您的支持将是我最大的动力！

Excel作为我们常用办公软件之一，它不仅仅是制作表格软件，最重要的是能对数据进行计算，解决人工计算效率低、易出错的问题。

Excel中数据计算依靠的是函数公式，如果全部弄清楚Excel中函数的使用方法，有一定的难度，毕竟Excel有400多个函数，所以熟练掌握工作中常用就好了。今天阿钟老师整理了几个工作中常用的函数公式。

01.判断业绩是否完成

表格中统计出了2月份各产品的销量，其中2月份任务量为200件，如何判断任务是否完成？

公式：=IF(C2>=200,\”完成\”,\”未完成\”)

方法：在D2单元格输入上面的公式，按回车键确认公式后，再选中D2单元格，鼠标移动到D2单元格右下角，按下鼠标左键下拉完成公式填充。

02.销售业绩排名

表格中统计了每个人的销售额，我们需要根据销售额由多到少排名次，看看谁的销售业绩最好。

公式：=RANK(C2,$C$2:$C$12,1)

方法：在D2单元格输入上面的公式，回车键确认公式后，再选中D2单元格，鼠标双击单元格右下角完成公式填充，排名也就计算出来了。

03.合并单元格求和

为了表格易读、美观，我们经常会对表格中同一类数据进行合并单元格，但合并单元格后的计算又是一大问题。比如表格中部门列合并了单元格，要求“1月”的销量按部门进行求和。

公式：=SUM(C2:C17)-SUM(D3:D17)

方法：首先要选中求和区域D2：D17单元格（是不是和1、2的操作不一样呢），然后再输入上面的公式，公式输入完成后按Ctrl+回车键确认公式，这样我们要进行的求和计算结果就出来了。

04.按条件求和

表格中需要计算各部门的销量合计。

公式：=SUMIF(A:A,G2,D:D)

方法：在H2单元格输入上面的公式，回车键确认公式后，再双击或下拉填充公式即可得出结果。

05.按条件计数

表格中需要统计每个部门有多少人？

公式：=COUNTIF(B:B,F2)

方法：在G2单元格输入上面的公式，回车键确认公式后再双击或下拉填充公式即可得出各部门人数。

06.查询销量

表格中统计的销量数据较多，如果找某一人的销售数据，翻来翻去不容易找，我们可以用公式快速查找，比如查找“公孙胜”“2月”的销量数据。

公式：=VLOOKUP(G2,B:E,3,0)

小伙伴们，在使用Excel中还碰到过哪些问题，评论区留言一起讨论学习，坚持原创不易，您的点赞转发就是对小编最大的支持，更多教程点击下方专栏学习。

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