EXCEL常规函数汇总及问题（一）

最新推荐文章于 2023-01-12 11:03:20 发布

一个懒鬼

最新推荐文章于 2023-01-12 11:03:20 发布

阅读量548

点赞数 2

分类专栏： excel

原文链接：https://blog.csdn.net/livan1234/article/details/90212464

版权

excel 专栏收录该内容

5 篇文章 1 订阅

订阅专栏

1、vlookup(根据什么找,到哪里找,找哪个列,0)函数的应用：主要是做匹配，通过一个字段关联两个表，然后将两个表匹配出来。

这一功能类似于SQL中的联表搜索：select t1.a0, t2.a1, t2.a2 from t1, t2 where t1.a0=t2.a0

vlookup语句为：vlookup(a0, t2, a1a2, 0)：其中a0即为条件，根据a0找到t2表中的a1和a2字段。

（这里要注意了，索引列与被索引列都必须都为第一列，且索引列数据必须来自被索引列，图示中标红的列，否则函数不会自动匹配查找你需要的数据）

excel中vlookup函数的使用方法（图解详细说明）

详细请看旁边链接：https://jingyan.baidu.com/article/5552ef47e3228b518ffbc91c.html

2、iferror(value，value_if_error)函数的应用：表示判断value的正确性，如果value正确则返回正确结果，否则返回value_if_error。

在C2单元格输入公式=IFERROR(A2/B2,"错误")

说明为：https://jingyan.baidu.com/article/36d6ed1f5296f21bce488354.html

3、Row（单元格）函数的应用：Row函数用于返回单元格的行号，Rows函数用于返回数组或引用单元格的行数。

说明为：https://baijiahao.baidu.com/s?id=1614135728627759828&wfr=spider&for=pc

Excel里MOD()函数的意思是求两数相除的余数，

Excel里sumproduct()函数的意思sum是和，product是积，所以是乘积之和的意思，数组中每一项的乘积之和。

sumproduct函数的语法格式：=SUMPRODUCT（array1,array2,array3, ...）Array为数组。

可以配合使用来实现 excel如何奇数行求和？偶数行求和？隔行求和？

4、match(查找值，查找范围，0)函数的应用：Match函数用于返回查找值在数组的位置，表示查找小于或等于 lookup_value 的最大值。

说明为：https://baijiahao.baidu.com/s?id=1611778949509455169&wfr=spider&for=pc

可配合INDEX(array,row_num,[column_num])。

=index(A:A,match(65,E1:E12,0)) 返回某一个查找的值

5、index(查询范围，行，列)函数的应用：返回表或区域中的值或对值的引用。

返回对应的值，下面的函数返回B4的数据。

说明为：https://jingyan.baidu.com/article/a24b33cd146efb19fe002be4.html

6、ABS(需要计算的值)函数的应用：取绝对值=abs(-2)得2。

7、MAX&MIN(需要计算的数组)函数：取数组中的最大值和最小值=max(A3:A13)得最大值。

8、round(需要舍的值，舍的位数)函数的应用：四舍五入=ROUND(2.52,1)，保留一位小数。

9、roundup(需要舍的值，舍的位数)函数的应用：按照指定的小数位数向上舍入，即不管是几都进一，roundup(月份/3,0)为第几季。

10、rounddown(需要舍的值，舍的位数)函数的应用：按照指定的小数位数向下舍入，不管是几都舍去，rounddown((月份+2)/3,0)为第几季。

说明为：https://jingyan.baidu.com/article/3a2f7c2ecdd9e126afd611f4.html

11、MID(text, start_num, num_chars)函数：从某一段字符串中截取出指定数量的字符，=mid(c2, 7, 8)：从第七位开始，截取8个字符。

12、MOD(分子，分母)函数：取余函数。

13、IF(logic_condition, true_result, false_result)函数：某一条件成立后执行第一个，否则执行第二个。

=IF(AND(B2>60,C2>60,D2>60),"合格","不合格")

说明：两个条件同时成立用AND,任一个成立用OR函数。

14、SUMIF（条件范围，条件，求和范围）:条件求和函数。

15、SUMIFS（求和范围，条件1范围，条件1，条件2范围，条件2,……条件N范围，条件N）：条件求和函数。

例如：

求男生的总成绩和男生中分数大于等于80分的总成绩。

方法：

=SUMIF(D3:D9,"男",C3:C9)

=SUMIFS(C3:C9,C3:C9,">=80",D3:D9,"男")。

16、COUNTIF(条件范围，条件)函数：条件计数函数（统计条目的出现的次数）

17、COUNTIFS（条件范围1，条件1，条件范围2，条件2……条件范围N，条件N）函数：条件计数函数。

例如：

计算男生的人数或男生中成绩>=80分的人数。

方法：

=COUNTIF(D3:D9,"男")

=COUNTIFS(D3:D9,"男",C3:C9,">=80")。

18、DATEDIF(需要计算的时间，当前日期，按照年份)函数：计算出年龄。

=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")&"周岁"。后面就会显示55周岁

补充：=text( value , format_text )

=mid(text，star_num , num_chars) 截取字符

19、SUMPRODUCT+COUNTIF函数：对成绩进行分组排名。

=countifs($B$3:$B$9,B3,$C$3:$C$9, ">"&C3)+1：计算比自己大的值的个数。

=sumproduct(($B$3:$B$12=B3)*($C$3:$C$12>C3))+1：分两步：其一、大于当下值的组合为true，小于为false，其二、两个相乘再求和即为有多少个比他大的，加1即为第几名。

说明为：https://baijiahao.baidu.com/s?id=1615486352994963545&wfr=spider&for=pc

sumproduct(array1,array2,array3, ...)函数：数组中对应行的值乘积之和。

https://jingyan.baidu.com/article/3c343ff7074afe0d36796353.html

20、SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])：替换函数。

替换电话号码的部分数值：

=substitute(B2,MID(B2,4,4),"****",1)

21、取整的间隔小时数：=TEXT(B2-B1,"[h]")：B1、B2为两个时间值。

22、生成随机数：=RANDBETWEEN(数字下限,数字上限)

23、提取时间中的日期值：提取日期时间中的日期值=INT(A2)；要继续提取时间，只需要做个减法，就可以了。

https://jingyan.baidu.com/article/c33e3f48305deeea15cbb52e.html

24、提取混合内容中的姓名：=LEFT(A2,LENB(A2)-LEN(A2))

LENB函数将每个汉字（双字节字符）的字符数按2计数，LEN函数则对所有的字符都按1计数。

因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。

LEFT函数从文本字符串的第一个字符开始，返回指定个数的字符，最终提取出员工姓名。

25、比较大小写的单词是否相同：

不区分大小写：=A2=B2

区分大小写：=EXACT(A2,B2)：EXACT函数区分大小写，但忽略格式上的差异。

26、合并带格式的单元格内容：

=A2&TEXT(B2," y年m月d日") 结果：韩梅梅85年5月1日

TEXT()函数：把B列的日期变成具有特定样式的字符串，然后再与A列的姓名连接

27、合并多个单元格内容:

=A2&B$1&B2

28、多条件查找：要求查询部门为生产,并且岗位为部长的姓名

=LOOKUP(1,0/((B2:B7=F2)*(C2:C7=G2)),A2:A7)

1、从公式的结构中可以看出，此方法为向量查询用法。

2、公式==LOOKUP(1,0/((B2:B7=F2)*(C2:C7=G2)),A2:A7)中，首先(B2:B7=F2)是否成立，如果成立，返回True，暨1，否则返回False，暨0。然后计算0/((B2:B7=F2)*(C2:C7=G2))的结果，当条件成立时，返回的结果为0，当条件不成立时，返回的结果为错误。

3、Lookup函数的另一个特点：向下查询，暨当查找值在查找范围中没有精准匹配的值时，返回小于查找值的最大值的结果。

=Lookup(查找值，查找值所在范围，返回值所在范围)。