一大波常用函数与公式

一、常用汇总公式

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

(0)

相关推荐