一大波常用函数与公式

一、常用汇总公式

A列求和：=sum(A:A)

A列最小值：=min(A:A)

A列最大值：=max(A:A)

A列平均值：=average(A:A)

A列数值个数：=count(A:A)

A列非空单元格个数：=counta(A:A)

A列第2小值：=small(A:A,2)

A列第2大值：=large(A:A,2)

取绝对值：=abs(A1)

取整：=int(A1)

四舍五入：=round(A1,2)保留2位小数；

=round(A1,0)不保留小数；

=round(A1,-1)，精确到十位

二、判断公式

1、把公式产生的错误值显示为无

D12公式=IFERROR(VLOOKUP(A12,$B$3:$E$8,4,0),"无")

说明：如果是错误值则显示为“无”，否则返回公式本身返回的结果。

图 1

2、IF多条件判断返回值

D2公式：=IF(AND(B2<>"",C2<>""),C2/B2,"0%")

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

图 2

三、统计公式

1、查找重复内容

B1公式：=IF(COUNTIF(A:A,A2)>1,"重复","")

图 3

2、重复内容首次出现时不提示

B1公式：=IF(COUNTIF(A$1:A1,A1)>1,"重复","")

图 4

3、重复内容首次出现时提示重复

=IF(COUNTIF(A2:A99,A2)>1,"重复","")

图 5

4、统计不重复计数

B2公式：C2=SUMPRODUCT(1/COUNTIF(A2:A5,A2:A5))

说明:用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

图 6

四、求和公式

1、连续非空单元格求和 快捷键Alt+=

2、多表相同位置求和

公式：=SUM('1月:6月'!B9)

图 7

说明：1月为第一张工作表，6月为最后一张工作表，如有更多的工作表，只需要修改第一张工作表和最后一张表的名称。如果在表中间删除或添加表，公式结果会自动更新。

3、单条件求和

C11公式：=SUMIF(A2:A8,B11,C2:C8)

图 8

4、多条件求和

F3公式：=SUMIFS(C:C,A:A,E3,B:B,$F$2)

图 9

5、隔列求和

N6公式：=SUMIF($B$5:$M$5,$N$5,B6:M6)

图 10

五、查找与引用

1、单条件查找

B12公式：=VLOOKUP(A12,$B$3:$C$8,2,0)

说明：查找是VLOOKUP最擅长的，基本用法。

图 11

2、多条件查找

D9公式：=VLOOKUP(B9&C9,$A$2:$D$6,4,0)

说明：创建辅助列，用&把多个条件变为一个条件。

图 12

3、多列查找

B13公式：=VLOOKUP($A13,$B$2:$E$6,COLUMN(B1),0)

说明：第三个参数用column函数取列数。

图 13

4、反向查找

C13公式：=INDEX(B5:B10,MATCH(B13,C5:C10,))

说明：利用MATCH函数查找位置，用INDEX函数取值

图 14

5、指定区域最后一列非空值

H2公式：=LOOKUP(1,0/(B2:G2),B2:G2)

说明：lookup函数可以忽略错误值，0/(B2:G2)返回{0,0,0,0,0,#DIV/0!},。

图 15

6、模糊查找

D4公式：=VLOOKUP(B4,$G$3:$H$11,2) 或者

=LOOKUP(B4,$G$3:$G$11,$H$3:$H$11)

用if函数也可以，但是区间多，公式太长太复杂。

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

图 16

六、字符串处理公式

1、多单元格字符串合并

公式：B1=PHONETIC(A1:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

图 17

关于Phonetic、CONCATENATE、&的区别参考文章：

合并函数Concatenate函数，&，phonetic函数的区别

2、根据身份证号码提取性别、出生日期、地区代码

图 18

图 19

图 20

七、日期公式

1、两日期相隔的年、月、天数计算

图 21

datedif函数第3个参数说明：

"Y" 时间段中的整年数。

"M" 时间段中的整月数。

"D" 时间段中的天数。

"MD" 天数的差。忽略日期中的月和年。

"YM" 月数的差。忽略日期中的日和年。

"YD" 天数的差。忽略日期中的年。

2、工作日天数

公式：=NETWORKDAYS(A1,"2015-12-31",C1:C7)

说明：返回两个日期之间的所有工作日数，周末和任何指定为假期的日期不被视为工作日

图 22

3、工作日

公式：=WORKDAY(A1,10,C1:C3)

说明：返回在指定的工作日之前（第2个参数为负数）或之后（第2个参数为正数）的某个日期，第3个参数如果省略表示不剔除指定的节假日。

图 23

4、计算某日期是星期几

公式：=WEEKDAY(A1,2)

说明：返回一周中的第几天的数值，结果为1到7，第2个参数为2表示周一返回1，周二返回2，以此类推。

图 24

图 25