当前位置:育德教育 / 会计实操 / 浏览文章

值得财务人收藏的Excel函数公式

日期:2018-06-10 20:42:34 来源:育德会计培训

1、计算所有单元格数值的绝对值之和

公式:=SUMPRODUCT(ABS(A1:A10))

2、纵向生成A-AZ的序列

公式:=SUBSTITUTE(ADDRESS(1,ROW(A1),4),1,"")

公式写在任意单元格,然后下拉

3、随机生成1-6颗“★”

公式:=REPT("★",RANDBETWEEN(1,6))

4、根据文本字符串指定字符“-”进行分列

公式:=TRIM(SUBSTITUTE($A1,"-",REPT(" ",99)),99COLUMN(A1)-98,99))

公式右拉

5、根据身份证号码判断性别

公式:=IF(ISEVEN(--A1,17,1)),"女","男")

6、获取字符串中第1个数字出现的位置

公式:=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))

7、计算大于200的数值的总和

公式:=SUMIF(A1:A10,">200")

8、计算姓名中包含“明”字的人员销量总和

公式:=SUMIF(A1:A10,"明",B1:B10)

9、建立一个指定网址的超链接

公式:=HYPERLINK("http://www.1excel.net","我的Excel")

10、根据应纳税所得额计算个人所得税

公式:=MAX((B1-3500){0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,105,555,1005,2775,5505,13505},0) 

11、根据个人所得税计算税前工资

公式:=IF(B2=0,"无法计算",MIN(3500+(B2+5{0,21,111,201,551,1101,2701})/(5%{0.6,2,4,5,6,7,9})))

数组公式,CTRL+SHIFT+回车键三键结束

12、根据税后工资计算税前工资

公式:=ROUND(MAX((B2-3500-{0,0,105,555,1005,2755,5505,13505})/(1-{0,0.03,0.1,0.2,0.25,0.3,0.35,0.45}))+3500,2)

13、根据税后工资计算个税

公式:=MAX((B2-3500-5{0,21,111,201,551,1101,2701})/(1-5%{0.6,2,4,5,6,7,9})+3500-B2,0)

14、随机生成1-6颗“★”

公式:=IFERROR(INDEX($A$2:$A$15,SMALL(IF(MATCH($A$2:$A$15,$A$2:$A$15,0)=ROW($1:$14),ROW($1:$14),9^9),ROW(A1))),"")

数组公式,需CTRL+SHIFT+回车键三键结束,公式下拉

15、数值四舍五入保留2位小数

公式:=ROUND(A1,2)

16、数值向下取舍保留2位小数

公式:=ROUNDDOWN(A1,2)

17、数值向上取舍保留2位小数

公式:=ROUNDUP(A1,2)

18、数值向下取整(截尾取整)

公式:=INT(A1)

19、返回10年前的今天的日期

公式:=TEXT(EDATE(TODAY(),-120),"yyyy/m/d")

20、计算本月总天数

公式:=EDATE(TODAY(),1)-TODAY()

21、将数值按从小到大升序排列

公式:=SMALL($A$1:$A$10,ROW(A1))

公式下拉

22、将数值按从大到小降序排列

公式:=LARGE($A$1:$A$10,ROW(A1))

公式下拉

23、计算前三名的数值总和

公式:=SUM(LARGE(A1:A10,{1,2,3}))

24、提取以数字开头的字符串中的数字

公式:=LOOKUP(9^9,--LEFT(A1,ROW($1:$99)))

25、计算字符串中双字节字符个数(例如汉字)

公式:=LENB(A1)-LEN(A1)

26、计算字符串中单字节字符个数(例如数字或字母)

公式:=2LEN(A1)-LENB(A1)

27、两列合并成一列(数据从第2行开始)

公式:=OFFSET($A$1,INT((ROW(A1)-1)/2)+1,MOD(ROW(A1)-1,2)) 

28、计算此刻距离国庆还有多少个小时

公式:=TEXT("2017-10-1"-NOW(),"[h]")

29、将小写金额转换成人民币大写金额

公式:=SUBSTITUTE(IF(-RMB(A2,2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(TEXT(RIGHT(RMB(A2,2),2),"[dbnum2]0角0分;;整"),"零分","整"),"零元整"),"零角",IF(A2^2<1,,"零")) 

30、根据开工日期和指定工作日天数计算完工日期

公式:=TEXT(WORKDAY("2017-8-28",270),"yyyy-m-d")

31、计算分数大于60分的人员个数

公式:=COUNTIF(B2:B10,">60")

32、计算不重复数据个数

公式:=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))

33、计算本月总天数

公式:=DAY(DATE(YEAR(TODAY()),MONTH(NOW())+1,))

34、随机生成2017年任意一天的日期

公式:=TEXT(RANDBETWEEN("2017-1-1","2017-12-31"),"yyyy-m-d")

35、判断本年是闰年还是平年

公式:=IF(COUNT("2-29"),"闰年","平年")

36、计算文本字符串中数字的个数

公式:=COUNT(-A2,ROW($1:$99),1)) 

数组公式CTRL+SHIFT+回车键三键结束

37、根据身份证号码判断星座

公式:=LOOKUP(--A2,11,4),{101,"摩羯座";120,"水瓶座";219,"双鱼座";321,"白羊座";420,"金牛座";521,"双子座";622,"巨蟹座";723,"狮子座";823,"处女座";923,"天秤座";1024,"天蝎座";1123,"射手座";1222,"摩羯座"})

38、多条件查找

公式:=LOOKUP(1,0/((条件区域1=条件1)(条件区域2=条件2)(条件区域3=条件3)),结果区域)

39、提取字符串中任意位置的数值

公式:=LOOKUP(9^9,--LEFT(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&"0123456789")),99),ROW($1:$99)))

40、制作工资条公式

公式:=CHOOSE(MOD(ROW(A1)-1,3)+1,A$1,INDEX(A$2:A$11,INT((ROW(A1)-1)/3)+1),"")

公式右拉,然后下拉

41、计算本月总天数

公式:=DAY(EOMONTH(TODAY(),0))

42、生成1,1,1,2,2,2,3,3,3…的循环序列

公式:=INT((ROW(A1)-1)/3)+1

任意位置输入公式,然后下拉

43、根据采购日期和付款天数设置提醒

公式:=TEXT(TODAY()-采购日期-付款协议天数,"已超出付款期限0天;距离付款期限还有0天;今天是最后付款期限")

44、判断今天是星期几

公式:=TEXT(TODAY(),"AAAA")

45、VLOOKUP函数反向查询

公式:=VLOOKUP(C1,IF({1,0},B1:B10,A1:A10),2,0) 

46、生成1,2,3,1,2,3,1,2,3,…,1,2,3的循环序列

公式:=MOD(ROW(A1)-1,3)+1

47、九九乘法表公式

公式:=IF(AND(ROW(A1)>=COLUMN(A1),ROW(A1)<10),ROW(A1)&"x"&COLUMN(A1)&"="&ROW(A1)COLUMN(A1),"")

48、计算大于等于500并且小于900的数字的个数

公式:=COUNTIFS(A1:A10,">500",A1:A10,"<900")

49、计算大于等于500并且小于900的数字的总和

公式:=SUMIFS(A1:A10,A1:A10,">500",A1:A10,"<900")

50、判断两个单元格内容是否一致(区分大小写字母)

公式:=EXACT(A2,B2)

51、根据身份证号码判断性别

公式:=IF(ISODD(--B2,17,1)),"男","女")

52、根据日期判断属于第几季度

公式:=TEXT(LEN(2^MONTH(A2)),"第0季度")

53、多列数据合并成一列

公式:=INDIRECT(TEXT(SMALL(IF($A$1:$E$10<>"",ROW($1:$10)100+COLUMN(A:E)),ROW(A1)),"r0c00"),0)

数组公式CTRL+SHIFT+回车键三键结束,公式下拉

54、计算大于等于60分的分数的平均分

公式:=AVERAGEIF(A1:A10,">=60")

55、计算双色球中奖概率

公式:=1/(COMBIN(33,6)COMBIN(16,1))

56、生成A-Z的26个英文字母 

公式:=ROW(A65))

公式下拉

57、计算数字1-1000的总和

公式:=SUMPRODUCT(ROW(1:1000))

58、计算两个日期之间的工作日天数

公式:=NETWORKDAYS(A1,B1)

59、计算两个日期之间间隔的天数

公式:=DATEDIF(A1,B1,"D")

60、计算两个日期之间间隔的月数

公式:=DATEDIF(A1,B1,"M")

61、计算两个日期之间间隔的年数

公式:=DATEDIF(A1,B1,"Y")

62、返回当前工作簿的名称

公式:=CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)

63、计算不重复数据个数

公式:=SUM(N(FREQUENCY(A2:A11,A2:A11)>0))

数组公式CTRL+SHIFT+回车键三键结束

育德会计培训微信公众号(关注育德公众号,获取更多考试干货~)
免责声明:因考试政策、内容不断变化与调整,以上信息仅供参考,如有异议,请考生以权威部门公布的内容为准!本站部分文章转载于网络,版权归原作者所有,转载目的在于传递更多信息,并不代表本网赞同其观点和对其真实性负责。如涉及作品内容、版权问题,请与我们联系,将在第一时间删除内容!
学习资料免费领取
满分学霸笔记 名师内部讲义 历年真题答案 考前冲刺资料 免费在线题库 考点全面解析
©20005-2018 长沙育德教育咨询有限公司   文章 湘ICP备13006518号 影响力教育品牌 品牌价值机构 湖南十佳教育机构