Excel公式总结

本思维导图对工作常用的excel公式进行了梳理和总结，希望对大家有所帮助。喜欢的小伙伴可以点个赞哦！

常用Excel公式总结

基础函数

统计函数

求和sum

计数count（只统计数字） counta（文字，数字）

平均值average

最大/最小值max min

第几大/小large small

排名rank

例子

large(A3:A15,5)A3-15中第5大的数字； large(A3:A15,row(2:2))提取第2大的数字

rank(A2,A2:A9,0/1)其中0-降序，1-升序

多种求和方式

累加求和：sum($A$2:A2)

快捷键求和：alt+=组合键

生成有规律的序列数

生成当前单元格行号-row()，列号-column()

文本函数

字符提取函数

从左侧提取left()

从右侧提取right()

从中间提取mid()

mid(A1,2,5)提取从2个字符开始后面5个字符

把提取的字符变成数字：--left(A1,10) left(A1,10)*1

查找和替换函数

替换字符substitute()

substitute(A1,"-","@")把A1中的-替换为@

substitute(A1,"-","")把A1中的-删除

substitute(A1,"-","@",5)把A1中的第5个-替换为@，区分大小写

去除单元格的所有数字 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,"")8,"")9,"")

去掉所有字母类似上面的例子，只是把A1换成upper(A1)

替换位置replace()

replace(A1,3,6,"abc")把A1中第3个字符后6个字符替换为abc，即3-9

找到find()

find("天津",A1)，返回1，说明第1位就就找到了天津

find("天津",A1,5)，从第5位开始寻找天津

寻找search()

find区分大小写，不支持通配符；search不区分大小写，支持通配符，通配符*代表那个字符，？代表1个字符

字符串长度函数

计算字符数len()

计算字节数lenb()

其他文本函数

提取当前工作簿路径cell()

将字符重复n次rept()

制作条形图，B1=rept("|",A1) A1中是数字，重复多少次，同时把B1的字体改为STENCIL

删除字符串中多余空格，把多个空格改为单个，trim()

字符和数字之间的转换 char(65)=A，code(a)=97

生成纵向大写英文字母的序号，char=(row(65:65))，然后下拉；或者char(64+row(1:1))

生成横向大写的char(64+column(A:A))，小写的加96

生成随机的大写 char(64+randbetween(1,26))

将阿拉伯数字转换为中文大写numberstring(number，type)

type=1 对应中文小写汉字，比如十一亿五千万

type=2 对应中文大写汉字，比如拾壹亿伍仟万

type=3 直接将数字意义对应转化成中文小写，比如一一五o

符合银行的大写规则：IF(B2=0,"零元",IF(B2<0,TEXT(INT(ABS(B2)),"负[DBNum2]g/通用格式")&"元"&IF((INT(B2*10)-INT(B2)*10)=0,"",TEXT(INT(B2*10)-INT(B2)*10,"[DBNum2]")&"角")&IF((INT(B2*100)-INT(B2*10)*10)=0,"整",TEXT(INT(B2*10)-INT(B2*10),"[DBNum2]")&TEXT(INT(B2*100)-INT(B2*10)*10,"[DBNum2]")&"分"),TEXT(INT(B2),"[dbnum2]")&"元"&IF(INT(B2*10)-INT(B2)*10=0,"",TEXT(INT(B2*10)-INT(B2)*10,"[dbnum2]")&"角")&IF((INT(B2*100)-INT(B2*10)*10)=0,"整",TEXT(INT(B2*10)-INT(B2*10),"[DBNum2]")&TEXT(INT(B2*100)-INT(B2*10)*10,"[DBNum2]")&"分")))

时间和日期函数

提取当前日期函数 today()

日期函数date() 年月日

date(2021,2,1)

date(2021,2,0) 1月31日

date(2021,2,-1) 1月30日

提取年 月 日

year()

month()

day()

例子 提取上月的最后一天 date(year(today()),month(today()),0) 提取去年的最后一天 date(year(today()),1,0) 本月有多少天 day(date(year(today(),month(today())+1,0)) 判断是否为闰年 day(date(year(today()),2,29))

提取当前时间函数 now()

时间函数time() 时 分 秒

提取时 分 秒

hour()

minute()

second()

例子 现在时间加90分钟 now()+time(0,90,0)

星期及月份函数 weekday() weekday(A1,2)以星期一为起点的数字

datedif函数 datedif(starttime,endtime,unit) unit

Y-时间段中的整年数

M-时间段中的整月数

D-时间段中的整天数

MD-天数的差，忽略月和年

YM-月数的差，忽略日和年

YD-天数的差，忽略年

数字处理函数

舍入函数

round() round(A1,2)保留两位小数

roundup() 向上舍入，如round(12.11,1) 12.2

rounddown() 向下舍入

按倍数舍入数字

mround() mround(1,3) 0; mround(2,3) 3

ceiling() ceiling(1,3) 3

floor() floor(1,3) 0

应用案例：加班时间按2小时取整，奖金按50的倍数取整

取整与取余函数

取整函数int()

取余函数mod()

应用案例，生成0-4循环的编号 mod(row(5:5),5) mod(row(1:1),5) mod(row(2:2),5) mod(row(3:3),5) mod(row(4:4),5)

应用案例，提取日期和时间，如A1=2021/8/31 15:15:15 日期 int(A1) 时间mod(A1,1)

IF函数

基础函数

and()

or()

应用案例：if(and(A1="女",B1>20)) if(or(A1="女",B1>20))

if

多重嵌套if(A1>=90,"A",if(A1>=80,"B","C")) =IF(A8>90,"优秀",IF(A8>80,"良好",IF(A8>70,"中等",IF(A8>60,"及格","不及格"))))

示例：if(isnumber(find("银行",A1)),"金融业","非金融业")

条件统计函数

countifs()函数

示例：

countifs(A1:A12,"银行")

countifs(A1:A12,">"&5000)或者 countifs(A1:A12,">"&B1)

统计日期：countifs(A1:A12,">="&"2021-2-1",A1:A12,"<"&"2021-3-1")

使用通配符：countifs(A1:A12,"王*")

支持通配符的常用函数：search() searchb() countifs() sumifs() averageifs() lookup() vlookup() hlookup() match()

拓展用法：

同时统计两个条件的数量，比如：同时统计一组、二组的人数，sum(countifs(A:A,{"一组","二组"}) 或者 countifs(A:A,"一组")+countifs(A:A,"二组")

引用单元格条件

sumifs()函数

sumifs(A1:A12,B1:B12,">"&500) 第一个参数求和区域，第二个参数标准区域，第三个是求和标准

averageifs()函数

averageifs(A1:A12,B1:B12,">"&500) 第一个参数求平均区域，第二个参数标准区域，第三个是求平均标准

INDEX加MATCH函数

match()函数

基础语法

match(A1,D1:D9,0)，A1是查找的值,D1:D9查找的区域（区域只能是整行或者整列）,0精确匹配

返回结果是数字，返回A1在区域中的位置

index()函数

index(array, row_num, [column_num]) 返回表格或数组中的元素值，此元素由行号和列号的索引值给定。当函数 INDEX 的第一个参数为数组常量时，使用数组形式。 array必需。单元格区域或数组常量。 如果数组只包含一行或一列，则相对应的参数 row_num 或 column_num 为可选参数。 如果数组有多行和多列，但只使用 row_num 或 column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。 row_num必需。选择数组中的某行，函数从该行返回数值。如果省略 row_num，则必须有 column_num。 column_num可选。选择数组中的某列，函数从该列返回数值。如果省略 column_num，则必须有 row_num。

index加match组合完成各种方式的查找，比vlookup()更灵活，效果同

=INDEX(G:G,MATCH(C3,H:H,0)) 这是一个嵌套函数，先计算MATCH函数，再将结果作为参数，计算INDEX函数。 首先，MATCH在H列查找匹配，结果为4，即C3的值在H列里位于第4行。

如果表1、表2不在同一张表里呢？很简单，公式前加入“工作表名称!”即可，如下。 =INDEX(表1!C:C,MATCH(D2,表2!D:D,0))

LOOKUP函数

基础语法

Lookup(lookup_value，lookup_vector，[result_vector])，功能是在某一行或者某一列查找指定的值，然后返回另一行或者另一列相同位置的值。 lookup_value表示要查找的值。 lookup_vector表示在其中查找指定值的单行（或者单行的一维数组）或者单列（或者单列的一维数组）。 [result_vector]可选，表示返回值的行或者列，大小必须与第二个参数lookup_vector一样

Lookup(lookup_value，array)，功能是在数组首行或者首列查找指定的值，然后返回数组最后一行或者列相同位置的值

注意事项

1、查找区域的数据即函数中的第二个参数（数组的首行或者首列）必须按照升序排列，否则结果可能有误。

2、如果在查找区域找不到指定值，则返回小于指定值的最大值。

3、如果指定的查找值小于查找区域的最小值，函数会返回错误值。

lookup完整函数公式为： =LOOKUP（1，0/（（$A$2:$A$9=E2）*（$B$2：$B$9=F2）），$C$2：$C$9)



VLOOKUP函数

基础语法

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 要查找的值，查找区域，要返回的结果在查找区域的第几列，精确匹配或近似匹配）

近似查找 注意查找区域中的首列内容必须以升序排序。

通配符查找

=VLOOKUP("*"&D2&"*",A:B,2,0)星号（*）匹配任意一串字符

=VLOOKUP("钱*",A:B,2,0)星号（*）匹配任意一串字符，所有姓钱的数据之和 =VLOOKUP("钱?",A:B,2,0)星号（*）匹配任意一串字符，第一个姓钱的数据

取消合并单元格-单元格内容为数字

=VLOOKUP(9E+307,A$2:A2,1,1) 9E+307是科学记数，表示9*10^307，是Excel允许键入的最大数值。

查找第一次出现的对应值

=VLOOKUP(F2,B:D,3,0) 当查找区域首列出现有两个或更多值与查找值匹配时，函数VLOOKUP返回第一次出现的对应值

交叉查询

=VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0) MATCH(B12,A1:G1,0)部分找到B12单元格内容“华北地区”在区域A1:G1中的位置5，把它作为VLOOKUP函数的第3参数

反向查找

函数VLOOKUP+ IF{1,0}结构

=VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0) IF({1,0},B2:B11,A2:A11)部分 当为1时条件成立返回B2:B11 当为0时条件不成立返回A2:A11

函数VLOOKUP+CHOOSE{1,2}结构

=VLOOKUP(D2,CHOOSE({1,2},B2:B11,A2:A11),2,0) 函数CHOOSE：根据给定的索引值，从参数串中选出相应值或操作。 CHOOSE(index_num, value1, [value2], ...) 如果第一参数为1，则CHOOSE返回value1；如果第一参数为2，则CHOOSE返回value2。 CHOOSE({1,2},B2:B11,A2:A11)部分 当条件为1时，返回B2:B11 当条件为2时，返回A2:A11

结果引用合并单元格内容

=VLOOKUP("座",OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1) MATCH(D2,B2:B14,0)部分找到业务员“阿文”在区域B2:B14中的位置11 OFFSET（基点，偏移行数，偏移列数，行高，列宽） OFFSET(A2,,,11)是以A2单元格为基点，偏移0行0列，返回行高为11的新区域A2:A12的引用

HLOOKUP函数，横向查找

OFFSET函数

基础语法

OFFSET(reference,rows,cols,height,width) Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值#VALUE!。 Rows相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用 5 作为参数 Rows，则说明目标引用区域的左上角单元格比 reference 低 5 行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。 Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用 5 作为参数 Cols，则说明目标引用区域的左上角的单元格比 reference 靠右 5 列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。 Height高度，即所要返回的引用区域的行数。Height 可以为负，-x表示当前行向上的x行。 Width宽度，即所要返回的引用区域的列数。Width 可以为负，-x表示当前行向左的x列。

简单举例

=OFFSET(C3,2,3,1,1) 显示单元格 F5 中的值 =SUM(OFFSET(C3:E5,-1,0,3,3)) 对数据区域 C2:E4 求和

=SUM(OFFSET(A1,0,0,COUNTA($A:$A),COUNTA($1:$1))) 整表求和

用offset这个来做下拉菜单，自动更新内容 

=OFFSET($A$1,0,0,1,COUNTA($1:$1)) 一级下拉菜单

=OFFSET($A$2,0,MATCH($G3,$1:$1,0)-1,COUNTA(OFFSET($A$2,0,MATCH($G3,$1:$1,0)-1,100,1)),1) 二级动态下拉菜单

INDIRECT函数

补充知识

Excel引用样式共有2种，即A1引用样式和R1C1引用样式。 　　一、那何为A1引用样式? 　　这里的A就是列号，即A列;这里的1表示行号，即第1行;所以在A1引用样式下，第1行第1列，用A1来表示，就是我们通常说的A1单元格。 　　二、那何为R1C1引用样式? 　　1、这里的R就是Row的第一个字母，R1就是表示第1行;这里的C就是Column的第一个字母，C1就是表示第1列;所以在R1C1引用样式下，第1行第1列就是用R1C1来表示。 　　从这里，我们可以看出，A1引用样式和R1C1引用样式，有相通的地方，但也有不同。比如列号，在R1C1引用样式下，列号还是按数字1 2 3 ...来表示，但在A1引用样式下，列号用字母A B C...来表示。 　　2、如何启用R1C1引用样式呢? 　　可以到 Excel选项》公式》将R1C1引用样式 打钩 ，即可启用。

基础语法

.indirect函数的语法格式 =INDIRECT(ref_text,[a1]) Ref_text 为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值#REF!或#NAME?

简单举例

indirect函数的引用的两中形式。 一种加引号，一种不加引号。 =INDIRECT("A1")——加引号，文本引用——即引用A1单元格所在的文本（B2) =INDIRECT(A1)——不加引号，地址引用——因为A1的值为B2，B2又=11，所以返回11

输入公式=INDIRECT(A3) 不加引号——A3=baidu，不存在baidu这样的赋值项（B2又=11），所以出错