会计常用的130个函数公式.docx
《会计常用的130个函数公式.docx》由会员分享，可在线阅读，更多相关《会计常用的130个函数公式.docx（23页珍藏版）》请在冰豆网上搜索。
会计常用的130个函数公式
会计常用的130个函数公式
1、文本与百分比连接公式
如果直接连接，百分比会以数字显示，需要用Text函数格式化后再连接
="本月利润完成率为"&TEXT（C2/B2,"0%"）
2、账龄分析公式
用lookup函数可以划分账龄区间
=LOOKUP（D2,G$2:
H$6）
如果不用辅助区域，可以用常量数组
=LOOKUP（D2,{0,"小于30天";31,"1~3个月";91,"3~6个月";181,"6-1年";361,"大于1年"}）
3、屏蔽错误值公式
把公式产生的错误值显示为空
公式：
C2=IFERROR（A2/B2,""）
说明：
如果是错误值则显示为空，否则正常显示。
4、完成率公式
如下图所示，要求根据B的实际和C列的预算数，计算完成率。
公示：
E2=IF（C3<0,2-B3/C3,B3/C3）
5、同比增长率公式
如下图所示，B列是本年累计，C列是去年同期累计，要求计算同比增长率。
公示：
E2=（B2-C2）/IF（C2>0,C2,-C2）
6、金额大小写公式
=TEXT（LEFT（RMB（A2）,LEN（RMB（A2））-3）,"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;"）&TEXT（RIGHT（RMB（A2）,2）,"[dbnum2]0角0分;;整"）
7、多条件判断公式
公式：
C2=IF（AND（A2<500,B2="未到期"）,"补款",""）
说明：
两个条件同时成立用AND,任一个成立用OR函数。
8、单条件查找公式
公式1：
C11=VLOOKUP（B11,B3:
F7,4,FALSE）
9、双向查找公式
公式：
=INDEX（C3:
H7,MATCH（B10,B3:
B7,0）,MATCH（C10,C2:
H2,0））
说明：
利用MATCH函数查找位置，用INDEX函数取值
10、多条件查找公式
公式：
C35
=Lookup（1,0/（（B25:
B30=C33）*（C25:
C30=C34））,D25:
D30）
11、单条件求和公式
公式：
F2=SUMIF（A:
A,E2,C:
C）
12、多条件求和公式
=Sumifs（c2:
c7,a2:
a7,a11,b2:
b7,b11）
13、隔列求和公式
公式H3:
=SUMIF（$A$2:
$G$2,H$2,A3:
G3）
如果没有标题，那只能用稍复杂的公式了。
=SUMPRODUCT（（MOD（COLUMN（B3:
G3）,2）=0）*B3:
G3）
14、多表相同位置求和
公式：
b2=SUM（Sheet1:
Sheet19!
B2）
说明：
在表中间删除或添加表后，公式结果会自动更新。
15、两条查找相同公式
公式:
B2=COUNTIF（Sheet15!
A:
A,A2）
说明：
如果返回值大于0说明在另一个表中存在，0则不存在。
16、两表数据多条件核对
如下图所示，要求核对两表中同一产品同一型号的数量差异，显示在D列。
公式：
D10=SUMPRODUCT（（$A$2:
$A$6=A10）*（$B$2:
$B$6=B10）*$C$2:
$C$6）-C10
17、个税计算
工资表调整
工资表原个税列变为3列，分别是累计应缴预扣所得额、累计税额、本月应扣缴税额。
（列标题大家自已命名吧）
公式设置
1月工资表公式：
D2（累计所得额）=B2-C2-5000
E2（本月累计税额）=5*MAX（0,D2*{0.6;2;4;5;6;7;9}%-{0;504;3384;6384;10584;17184;36384}）
F2（本月应扣缴额）=E2
2月工资表公式：
D2（累计所得额）=B2-5000-C2+IFERROR（VLOOKUP（A2,'1月工资表'!
A:
D,4,0）,0）
注：
使用vlookup查找上月累计应扣应缴所得额
E2（本月累计税额）=5*MAX（0,D2*{0.6;2;4;5;6;7;9}%-{0;504;3384;6384;10584;17184;36384}）
注：
公式同上月
F2（本月应扣缴额）=E2-IFERROR（VLOOKUP（A2,'1月工资表'!
A:
F,5,0）,0）
注：
本月应扣= 本月累计应扣应缴个税- 上月累计数
其他月份工资表同2月，只需要把公式中引用的工作表改为上一月即可。
如制作4月份工资表，公式中的2月改为3月即可。
18、会计科目截取公式
一级科目：
=LEFT（B3,FIND（"-",B3）-2）
二级科目：
=MID（B3,FIND（"-",B3）+2,FIND（"-",B3&"-",FIND（"-",B3）+1）-FIND（"-",B3）-3）
三级科目：
=MID（B3,FIND（"-",B3&"-",FIND（"-",B3）+1）+2,100）
19、多表同一位置汇总公式
多个工作表如果格式完全相同，可以用sum函数的多表求和功能。
如下图所示，要求在汇总表里设置合计公式，汇总前19个工作表B列的和。
B2=SUM（Sheet1:
Sheet19!
B2）
20、中国式排名公式
公式:
C12
=SUMPRODUCT（（$D$4:
$D$9>=D4）*（1/COUNTIF（D$4:
D$9,D$4:
D$9）））
21、先进先出公式
提醒：
该公式属超高难度公式，不建议新手使用和费力的去理解，仅供excel中高水平用户参考使用。
定义名称：
Lj=SUBTOTAL（9,OFFSET（!
$E$2,,,ROW（）-1））-SUBTOTAL（9,OFFSET（!
$B$2,,,ROW（!
$B$1:
$B9）））
G3公式：
{=SUMPRODUCT（IF（lj>$B$3:
B3,$B$3:
B3,lj）,IF（lj>0,$C$3:
C3））-SUM（$G$2:
G2）}
22文本、日期与百分比连接
要求：
下面为日期与文本进行连接。
E2单元格输入公式为（&表示连接）：
=TEXT（A2,"yyyy-mm-dd"）&B2&TEXT（C2,"0.00%"）
注：
如果使用简单的连接而不定义格式的话那么就像D列一样出现这样的数字格式，日期与时间的本质是数值，所以会出现这样的问题。
23 IF条件判断
例：
在下面的题目中，如果性别为“男”则返回“先生”，如果为“女”，则返回女士。
在E2单元格中输入公式：
=IF（D2="男","先生","女士"），然后确定。
说明：
在Excel中引用文本的时候一定要使用英文状态下的半角双引号。
以上公式判断D2如果是男，则返回先生，否则那一定就是女，返回女士。
24合同到期计算
计算合同到期是财务工作中一个最常见的用法。
在D2单元格中输入公式：
=EDATE（B2,C2），然后确定。
注意：
第二个参数一定是月份的数量，比如2年那么就是24个月。
25VLOOUP查找函数
查找姓名对应的销售额。
在F3单元格中输入公式
=VLOOKUP（E3,$A$2:
$C$9,3,0），按Enter键完成。
如下图所示：
26条件求和
例：
求下面的1月的1组的数量总计，在E9单元格中输入公式：
=SUMIFS（G2:
G7,A2:
A7,"1月",B2:
B7,"1组"），确定填充即可。
注：
以上函数支持通配符，同时对于条件要注意加上英文状态下的半角单引号。
27带有合格单元格的求和
合并单元格的求和，一直是一个比较让新手头疼的问题。
选中D2:
D13单元格区域，然后在公式编辑栏里输入公式：
=SUM（C2:
C13）-SUM（D3:
D14），然后按完成，如下图所示：
注：
一定要注意第二个SUM函数的区域范围要错位，不然就报错。
28带有小计的单元格求和
在表中带有小计是许多领导的最爱的一个风格，但是对于做表的人来说绝对一个是很难受的过程，那么带有小计的单元格到底怎么样求和呢。
在C9单元格里是输入公式：
=SUM（C2:
C8）/2，按Enter键完成。
如下图所示：
注意：
这里是自用了小计与求和的过程是重复计算了上面的数据，所以再除以2就可以得到不重复的结果，也正是想要的结果。
29VLOOKUP账龄分析
在D2单元格中输入公式：
=VLOOKUP（TODAY（）-B2,{0,"0-30天";30,"30-60天";60,"60-90天";90,"90天以上"},2,1），按Enter键后向下填充。
如下图所示：
最后同上一个方法一样插入数据透视表即可。
注：
使用VLOOKUP函数的最后一个参数为1时为模糊查找的原理进行查询。
结果。
30多工作表求和
下表中是4个月的业绩统计，每个工作表的里面的张成的位置都是一样的，求张成的1-4月的提成统计。
在F5单元格中输入公式：
=SUM（'1月:
4月'!
C2）按Enter键完成填充。
如下图所示：
31金额大写转化
如下图所示，将A列的数字转换成财务大写数字。
在B2单元格中输入公式，然后向下填充即可。
=TEXT（TRUNC（ABS（ROUND（A2,2）））,"[DBNum2]"）&"元"&IF（ISERR（FIND（".",ROUND（A2,2）））,"",TEXT（RIGHT（TRUNC（ROUND（A2,2）*10））,"[DBNum2]"））&IF（ISERR（FIND（".0",TEXT（A2,"0.00"）））,"角",""）&IF（LEFT（RIGHT（ROUND（A2,2）,3））=".",TEXT（RIGHT（ROUND（A2,2））,"[DBNum2]"）&"分","整"）
32票据金额拆分
将下面的金额拆分至对应的单位的单元格中去。
在D6单元格中输入公式：
=IF（$C6,LEFT（RIGHT（"￥"&$C6/1%,COLUMNS（D:
$N）））,""）
按Enter键完成后，向右向下填充。
33交叉查找
在H2单元格中输入公式：
=VLOOKUP（$G2,$A$2:
$D$9,MATCH（H$1,$A$1:
$D$1,0）,0），按Enter键完成后向下向右填充。
注：
一定要锁定VLOOKUP函数的第一个参数的列号，MATCH函数的第一个参数的行号，这样才能得到正确的结果。
34屏蔽错误
FERROR函数是屏蔽错误值的一个函数。
在E2单元格中输入的公式查询的时候出现了一个错误，此时想把这个公式屏蔽为空白，那么就可以在E2单元格中输入公式：
=IFERROR（VLOOKUP（C2,$I$3:
$K$7,3,0）,""），确定后向下填充。
注意：
该函数先判断第一参数是否为错误值，如果为错误值则返回为定义的第二个参数，如果不是错误值，那么继续地返回其本身。
35四舍五入函数
功能：
将某个数字四舍五入为指定的位数
语法：
ROUND（number,num_digits）
在E2、单元格中分别输入公式：
=ROUND（D2,2），
在F2单元格中分别输入公式：
=ROUND（D2,0），
在G2单元格中分别输入公式：
=ROUND（D2,-2）
注：
如果num_digits大于0（零），则将数字四舍五入到指定的小数位；如果num_digits等于0，则将数字四舍五入到最接近的整数；如果num_digits小于0，则在小数点左侧进行四舍五入。
一、员工信息表公式
1、计算性别（F列）
=IF（MOD（MID（E3,17,1）,2）,"男","女"）
2、出生年月（G列）
=TEXT（MID（E3,7,8）,"0-00-00"）
3、年龄公式（H列）
=DATEDIF（G3,TODAY（）,"y"）
4、退休日期 （I列）
=TEXT（EDATE（G3,12*（5*（F3="男"）+55））,"yyyy/mm/ddaaaa"）
5、籍贯（M列）
=VLOOKUP（LEFT（E3,6）*1,地址库!
E:
F,2,）
注：
附带示例中有地址库代码表
6、社会工龄（T列）
=DATEDIF（S3,NOW（）,"y"）
7、公司工龄（W列）
=DATEDIF（V3,NOW（）,"y"）&"年"&DATEDIF（V3,NOW（）,"ym"）&"月"&DATEDIF（V3,NOW（）,"md"）&"天"
8、合同续签日期（Y列）
=DATE（YEAR（V3）+LEFTB（X3,2）,MONTH（V3）,DAY（V3））-1
9、合同到期日期（Z列）
=TEXT（EDATE（V3,LEFTB（X3,2）*12）-TODAY（）,"[<0]过期0天;[<30]即将到期0天;还早"）
10、工龄工资（AA列）
=MIN（700,DATEDIF（$V3,NOW（）,"y"）*50）
11、生肖（AB列）
=MID（"猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD（MID（E3,7,4）,12）+1,1）
二、员工考勤表公式
1、本月工作日天数（AG列）
=NETWORKDAYS（B$5,DATE（YEAR（N$4）,MONTH（N$4）+1,）,）
2、调休天数公式（AI列）
=COUNTIF（B9:
AE9,"调"）
3、扣钱公式（AO列）
婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元
=SUM（（B9:
AE9={"事";"旷";"病";"丧";"婚"}）*{30;50;20;10;10}）
三、员工数据分析公式
1、本科学历人数
=COUNTIF（D:
D,"本科"）
2、办公室本科学历人数
=COUNTIFS（A:
A,"办公室",D:
D,"本科"）
3、30~40岁总人数
=COUNTIFS（F:
F,">=30",F:
F,"<40"）
四、其他公式
1、提成比率计算
=VLOOKUP（B3,$C$12:
$E$21,3）
2、个人所得税计算
假如A2中是应税工资，则计算个税公式为：
=5*MAX（A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},）
3、工资条公式
=CHOOSE（MOD（ROW（A3）,3）+1,工资数据源!
A$1,OFFSET（工资数据源!
A$1,INT（ROW（A3）/3）,,）,""）
注：
A3:
标题行的行数+2,如果标题行在第3行，则A3改为A5
工资数据源!
A$1:
工资表的标题行的第一列位置
动图
4、Countif函数统计身份证号码出错的解决方法
由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。
不过只需要用 &"*" 转换为文本型即可正确统计。
=Countif（A:
A,A2&"*"）
五、利用数据透视表完成数据分析
1、各部门人数占比
统计每个部门占总人数的百分比
2、各个年龄段人数和占比
公司员工各个年龄段的人数和占比各是多少呢？
3、各个部门各年龄段占比
分部门统计本部门各个年龄段的占比情况
4、各部门学历统计
各部门大专、本科、硕士和博士各有多少人呢？
5、按年份统计各部门入职人数
每年各部门入职人数情况
升级会员 