一、常用汇总公式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 |
|
来自: sunnynie2021 > 《待分类》