excel表格函数及部分实用公式

2023-05-16 18:46 作者:lemon远 0人读过 | 我要投稿

excel函数非常强大，适当的学习可提升工作效率和个人竞争力。整理个大概，用于学习

一、关联匹配类
1、VLOOKUP：用于查找首列满足条件的元素

相当于=VLOOKUP（找什么，在哪找，第几列，精确找还是大概找一找）

①查找的值

姓名查找工资 =VLOOKUP（查找值，数据表，列序数，[匹配条件]）

查找不到返回空值 =IFERROR(VLOOKUP(G2,B:E,4,0),"")

②查找区域或数组

根据部门和姓名查工资 =VLOOKUP(E2&F2,IF({1,0},A2:A8&B2:B8,C2:C8),2,0)

③返回值的在列数

④精确or匹配查找
2、HLOOKUP：搜索表的顶行或值的数组中的值，并在表格或数组中指定的行的同一列中返回一个值

在A1到D3的区域中查找“手机”，并返回找到的行中的第三列的值，FALSE为精确匹配的值

=HLOOKUP("手机", A1:D3, 3, FALSE)
3、INDEX：返回表格或区域中的值或引用该值

INDEX(数组或区域, 行号, 列号)

INDEX(array, row_num, [column_num])
4、MATCH：用于返回指定内容在指定区域（某行或者某列）的位置

MATCH(lookup_value,lookup_array, [match_type])
5、RANK：求某一个数值在某一区域内一组数值中的排名

RANK(排谁，在那个区域里排，降序/升序)，其中0表示降序，1表示升序

=RANK(C2,$C$2:$C$10,0)
6、ROW：返回单元格所在的行
7、Column：返回单元格所在的列
8、Offset：返回对单元格或单元格区域中指定行数和列数的区域的引用

offset是一个偏移函数，它以一个区域为原点进行偏移得到一个新的偏移区域

=OFFSET(reference, rows, cols, [height], [width])
二、清洗处理类
1、Trim：清除掉字符串两边的空格
2、Rtrim：清除单元格右边的空格
3、Ltrim：清除单元格左边的空格
4、Concatenate：合并单元格中的内容
5、Left：从左截取字符串

①分离单位与数字，B2为单位（kg/元）：=LEFT(B2,LEN(B2)-4)

②除后3位数之外的部分：=LEFT(B2,LEN(B2)-3)
6、Right：从右截取字符串
7、Mid：从中间截取字符串
8、Replace：替换掉单元格的字符串
9、Substitute：和replace接近，实现固定文本替换
10、Find：查找文本位置
11、Search：返回一个指定字符或文本字符串在字符串中第一次出现的位置 ,从左到右查找
12、Len：返回文本字符串中的字符个数
13、Lenb：返回文本字符串中用于代表字符的字节数
三、逻辑运算类
1、IF：=IF(条件, true时返回值, false返回值)

①判断月薪：=IF(B3>=6000,"高薪","低薪")

②判断多个条件：=IFS(B3<6000,"员工",B3<10000,"经理",B3>=10000,"老板")

③身份证号码自动生成性别：=IF(MOD(MID(B2,17,1),2),"男","女")
2、AND：逻辑判断，相当于“并”

大于90分，AND函数返回逻辑值为真TRUE，显示结果为合格。反之

=IF(AND(C2>90,C3>90,C4>90),"合格","不合格")
3、OR：逻辑判断，相当于“或”

大于90分，OR函数返回逻辑值为真TRUE，显示结果为合格。反之

=IF(OR(C2>90,C3>90,C4>90),"合格","不合格")
四、计算统计类
1、MIN：找到某区域中的最小值
2、MAX：找到某区域中的最大值
3、AVERAGE：计算某区域中的平均值

一个区域里面的数字机选平均值

=AVERAGE(A1:A4)
4、COUNT：计算含有数字的单元格的个数

统计姓吴且销售额1万以上的人数

=COUNTIFS(B2:B14,"吴*",D2:D14,">=10000")
5、COUNTIF：计算某个区域中满足给定条件的单元格数目

①求文本型单元格个数：=COUNTIF($A$2:$A$6,"*")

②求非空单元格个数：=COUNTIF($A$2:$A$6,"<>")

③求空单元格个数：=COUNTIF($A$2:$A$6,"")
6、COUNTIFS：统计一组给定条件所指定的单元格数

COUNTIF(区域，判断条件)
7、SUM：计算单元格区域中所有数值的和

求总和：=SUM（B2：B9）
8、SUMIF：求满足条件的单元格和

单条件求和：=SUMIF（F：F，I2，G：G）
9、SUMIFS：对一组满足条件指定的单元格求和

多条件求和：=SUMIFS(G2:G9,D2:D9,I3,G2:G9,">"&K3)
10、SUMPRODUCT：返回相应的数组或区域乘积的和

SUMPRODUCT（array1,array2,array3, ...）
11、Stdev：统计型函数，求标准差

STDEV.S(number1,[number2],...)
12、Substotal：汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化

SUBSTITUTE(text, old_text, new_text, [instance_num])
13、Int：取整函数，向下取整
14、Round：取整函数，按小数位取数
五、时间序列类
1、TODAY：返回当前日期的序列号，动态函数

实用。自动返回今日日期。可自定义显示单元格格式

=TODAY（）
2、NOW：返回当前日期和时间的序列号，动态函数

实用。自动返回当前的日期与时间。可自定义显示单元格格式

=NOW()
3、YEAR：返回对应于某个日期的年份

身份证号码自动生成年龄：=YEAR(TODAY())-MID(B2,7,4)

通过进入公司的日期与指定年份来计算工龄：=YEAR($E$2)-YEAR(B2)
4、MONTH：返回日期（以序列数表示）中的月份
5、DAY：返回以序列数表示的某日期的天数
6、WEEKDAY：返回对应于某个日期的一周中的第几天
7、Datedif：计算两个日期之间相隔的天数、月数或年数

①身份证号码自动生成年龄：=DATEDIF(TEXT(MID(B2,7,8),"0-00-00"),TODAY(),"Y")

②出生年月计算年龄：=DATEDIF(B2,TODAY(),"y")
8、WORKDAY：计算一项工作需要做N天后完成的结束日期等

预算完工日期 =WORKDAY（开始日期，天数，[假期]）
六、其他
1、LOWER：将指定字符串中的字母转换为小写

英文大写转小写：=LOWER(B2)
2、TEXT：通过格式代码对数字应用格式，从而更改数字的显示方式（功能巨多）

①金额转中文大写（财务专用）：=TEXT(B2,"[dbnum2]")

②身份证号码自动生成出生日期：=TEXT(MID(B2,7,8),"0-00-00")
3、SUBSTITUTE：将查找到的某个字符，替换成别的字符

①加密指定内容（隐藏手机号中间四位）：=SUBSTITUTE(B3,MID(B3,4,4),"****")

②字符串计数：=LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1

③带单位求和：=SUMPRODUCT(SUBSTITUTE(D2:D10,"元","")*1)&"元"

④数据分列：=TRIM(MID(SUBSTITUTE($C3,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))