Excel中的几个“HR函数”

dhabc123 · 发表于 2008-1-4 12:14

学习

xiu_zhang · 发表于 2008-1-8 23:00

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用leoyi在2007-11-26 11:03:14的发言： 好公式！厉害完善一下公式：=ROUND(MAX((B20-1600)*5%*{1,2,3,4,5,6,7,8,9}-25*{0,1,5,15,55,135,255,415,615},0),2)这样就不会导致汇总时金额有几分钱的差别了</div>请问，公式中的*是说明意思啊？在此先谢过咯！

xolf · 发表于 2008-1-8 23:08

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用xiu_zhang在2008-1-8 23:00:44的发言： 请问，公式中的*是说明意思啊？在此先谢过咯！</div><a href="http://club.excelhome.net/viewthread.php?tid=161588&px=0">http://club.excelhome.net/viewthread.php?tid=161588&px=0</a><a href="http://club.excelhome.net/viewthread.php?tid=155418">http://club.excelhome.net/viewthread.php?tid=155418</a>欢迎来到eh论坛,请参考.

xiu_zhang · 发表于 2008-1-9 21:54

谢谢楼主回复

LOVEBABY521 · 发表于 2008-1-10 16:49

<div class="msgheader">QUOTE:</div><div class="msgborder">以下是引用xolf在2006-12-9 14:25:43的发言： 为什么我用第一个公式会出错呢?</div>

baick0001 · 发表于 2008-1-14 14:58

太好了

huangqin257 · 发表于 2008-1-15 13:32

非常感谢楼主的贡献

hzy888 · 发表于 2008-2-1 00:52

受教了！！！！

fangfang1979 · 发表于 2008-2-7 22:17

tai xie xie le

taylor_penny · 发表于 2008-2-13 16:21

我也来说一个，虽然我的比之前的高手讲的烦多了，但个人觉得还是比较容易理解先在一张表上我累计税率先写上。            A           B        C         D<table cellspacing="0" cellpadding="0" width="359" border="0" style="WIDTH: 269pt; BORDER-COLLAPSE: collapse;"><colgroup><col width="71" style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2272;"></col><col span="4" width="72" style="WIDTH: 54pt;"></col></colgroup><tbody><tr height="21" style="HEIGHT: 15.75pt;"><td class="xl48" width="71" height="21" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 53pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent;">1</td><td class="xl46" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">不超过500元</td><td class="xl47" align="right" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">1E-19</td><td class="xl49" align="right" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">5%</td><td class="xl45" align="right" width="72" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">0</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">2</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过500元至2000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">500</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">10%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">25</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">3</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过2000元至5000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">2000</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">15%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">125</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">4</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过5000元至20000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">5000</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">20%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">375</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">5</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过20000元至40000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">20000</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">25%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">1375</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">6</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过40000元至60000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">40000</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">30%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">3375</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">7</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过60000元至80000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">60000</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">35%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">6375</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">8</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过80000元至100000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">80000</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">40%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">10375</td></tr><tr height="20" style="HEIGHT: 15pt;"><td class="xl48" height="20" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent;">9</td><td class="xl46" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">超过100000元的部分</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">100000</td><td class="xl49" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">45%</td><td class="xl45" align="right" style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent;">15375</td></tr></tbody></table>然后用公式vlookup("应税金额“，B1:C9,2,TRUE),___结果1这个计算计税等级，然后再用vlookup（结果1，C1:D9,2,TRUE)计算出对应的速算扣除数----结果2结果1-结果2，就可以计算出税额可虽然没有之前的简洁，但比较容易明白

		自动登录	找回密码
密码			免费注册

Excel中的几个“HR函数”

[分享]