EXCEL常规函数汇总及问题(一)

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常规函数汇总及问题(一)》

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

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

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

《EXCEL常规函数汇总及问题(一)》

说明为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的数据。

《EXCEL常规函数汇总及问题(一)》

说明为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(条件范围,条件,求和范围):条件求和函数。

《EXCEL常规函数汇总及问题(一)》

15、SUMIFS(求和范围,条件1范围,条件1,条件2范围,条件2,……条件N范围,条件N):条件求和函数。

例如:

《EXCEL常规函数汇总及问题(一)》

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

方法:

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

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

16、COUNTIF(条件范围,条件)函数:条件计数函数(统计条目的出现的次数)

17、COUNTIFS(条件范围1,条件1,条件范围2,条件2……条件范围N,条件N)函数:条件计数函数。

例如:

《EXCEL常规函数汇总及问题(一)》

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

方法:

=COUNTIF(D3:D9,”男”)

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

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

《EXCEL常规函数汇总及问题(一)》

=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函数:对成绩进行分组排名。

《EXCEL常规函数汇总及问题(一)》

=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, …)函数:数组中对应行的值乘积之和。

《EXCEL常规函数汇总及问题(一)》

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

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

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

《EXCEL常规函数汇总及问题(一)》

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

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

《EXCEL常规函数汇总及问题(一)》

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

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

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

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

《EXCEL常规函数汇总及问题(一)》

LENB函数将每个汉字(双字节字符)的字符数按2计数,LEN函数则对所有的字符都按1计数。

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

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

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

《EXCEL常规函数汇总及问题(一)》

不区分大小写:=A2=B2

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

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

《EXCEL常规函数汇总及问题(一)》

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

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

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

《EXCEL常规函数汇总及问题(一)》

=A2&B$1&B2

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

《EXCEL常规函数汇总及问题(一)》

=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(查找值,查找值所在范围,返回值所在范围)。

《EXCEL常规函数汇总及问题(一)》

总结:

1、常用的几个函数组合:https://baijiahao.baidu.com/s?id=1607346377232210818&wfr=spider&for=pc

2、方法总结为:https://jingyan.baidu.com/article/636f38bb9bfc2dd6b946104c.html

3、常用快捷键:https://mbd.baidu.com/newspage/data/landingsuper?context=%7B%22nid%22%3A%22news_9272714227277649820%22%7D&n_type=1&p_from=4

版权声明:本文为CSDN博主「livan1234」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/livan1234/article/details/90212464

1.问题?  –出现打印预览没有边框,但是显示页面自己添加了就是打印不出来。(痛苦呀!)

          问题出现在可能该列被隐藏了,导致无法显示该边的边框线。

2.问题?  —如何在excel中多行插入空白内容。

        《EXCEL常规函数汇总及问题(一)》《EXCEL常规函数汇总及问题(一)》

 

 

 

 

    原文作者:一个懒鬼
    原文地址: https://blog.csdn.net/weixin_44657888/article/details/115480771
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞