今天我向大家分享5个Excel技巧,希望能帮助更多喜欢研究Excel公式和钻研学习的人解决一些财务难题。
◆◆税金计提表文本与数值、百分比连接公式◆◆
月末计提税金时一般会计算税费合计和增值税税负率,我比较喜欢利用公式把文本与数值、百分比连接起来。
A9="8月税费合计
"&TEXT(B7+F4+F5+F6+F7+F8,"0.00")&"元,
8月增值税税负率
"&TEXT(B7/B3,"0.00%")&"。"
用TEXT文本函数显示数值格式,用&符号连接文本,加上标点符号,就实现了文本与数值、百分比的连接。日常工作中需要把表格中的文字连在一起就可以用&符号连接。
◆◆账龄分析单多条件求和、查找公式◆◆
设置辅助区域,利用LOOKUP查找函数和SUMIF、SUMIFS单多条件求和公式实现账龄分析的单多条件求和及查找。
①用LOOKUP函数可以划分账龄区间
F2=LOOKUP(E2,$H$3:$I$7)
如果不用辅助区域,可以用常量数组
F2=LOOKUP(E2,{0,"小于30天";31,"1~3个月";91,"3~6个月";181,"6-1年";361,"大于1年"})
②用SUMIF单条件求和公式可以取得某个发包方所有项目的应收账款金额。
C14=SUMIF(A2:A11,A14,$D$2:$D$11)
用SUMIFS多条件求和公式可以取得某个发包方某个项目的应收账款金额和某个发包方某个区间的应收账款金额。
C16=SUMIFS($D$2:$D$11,$A$2:$A$11,A16,$B$2:$B$11,B16)
G14=SUMIFS($D$2:$D$11,$A$2:$A$11,E14,$F$2:$F$11,F14)
③用LOOKUP函数可以实现多条件查找某个发包方某个项目的应收账款区间(两个公式)。
K14=LOOKUP(I14&J14,$A$1:$A$11&$F$1:$F$11,$F$1:$F$11)
L14=LOOKUP(1,0/(($A$2:$A$11&$B$2:$B$11)=(I14&J14)),$F$2:$F$11)
◆◆毛利率计算屏蔽错误值公式◆◆
表格中进行除法运算时,如果被除数为0,就会显示错误#DIV/0!,利用IFERROR函数可以让错误值不再显示出来。
D9=TEXT((C9-B9)/C9,"0.00%"),如果是错误值会显示#DIV/0!。
表格中出现大量的错误值还会影响其他公式的计算,利用IFERROR函数就避免了这种情况的出现。D2=IFERROR(TEXT((C2-B2)/C2,"0.00%"),""),如果是错误值则显示为空,否则正常显示。
◆◆公司贷款购车等额本息还款利息计算◆◆
最近我公司采用等额本息分期还款的方式,贷款买了一辆电动车。领导让我核算一下利息是否是贷款公司告知的年利息约为4%。我通过公式和制表算了一下,等额本息还款方式中的利息除以贷款总额得到2年的利息约为8%,的确和贷款公司的说法没什么差异。可是这两年中我们每个月都在还本金,利息随着本金的减少而减少,实际年利率还是7.5%。
①每月还款额=[贷款本金×月利率×(1+月利率)^还款月数]÷[(1+月利率)^还款月数-1]
D5=ROUND(($C$2*10000*$C$3/12*(1+$C$3/12)^24)/((1+$C$3/12)^24-1),2),使用时就是更换贷款金额和还款月数,代入公式就可得到每月还款额。
②利息计算
C5=ROUND($C$2*10000*$C$3/12,2),第一个月的利息就是176000*7.5%/12=1100元,本金6819.93元。
C6=ROUND(($C$2*10000-SUM($B$5:B5))*$C$3/12,2),以后每月的利息就算的基础都要减去以前月份的已经还的本金,就用锁定单元格求和的方法实现。
③长期待摊费用-车贷、长期应付款-车贷的余额账与表的核对
H10=H8-SUMIF(F:F,$F$5,C:C),H11=H9-SUMIF(F:F,$F$5,D:D),已经还款的月份用√标记,余额用利息合计或本息合计减去已标记√的月份的利息或本息的合计,账表核对一致,避免漏记或错记。
等额本息还款购车购房是很流行的方式,你可以套用一下这个公式算一下你每月还款额中的本金和利息是多少,也可以自己推算一下多少年的房贷利息几乎和本金持平。
◆◆固定资产折旧已计提月份每月加1◆◆
每月计提固定资产折旧时,累计折旧等于月折旧乘以已计提月份。已计提月份每月都要在上月的基础上加1,可以用复制1,已计提月份列的数据选择性粘贴-数值(运算:加),就可实现。
在所有的单元格都对相同的数字进行加减乘除运算时,就可用到复制—选择性粘贴-数值(运算:加减乘除),相关数据需要行变列或列变行勾选转置即可。
注:文章版权归原作者所有,内容仅供读者学习、交流之目的,如有侵权,请联系我们删除。
联系邮箱:zirongzhang@ningmengyun.com