Excel表格设计中的陷井

黄其宾

做会计的人，不免要和税收打交道，计算税收就是必修课程。以增值税为例，现行主要税率为17%，其算法是：卖价（含税销售价）=销售额+税额，这其中：税额=销售额×17%，销售额=卖价÷（1+17%），计算过程中，按四舍五入方法约到角分，因为这样，在这表面天衣无缝的公式中，陷井便出现了。
例：在卖价为100,000.00元的商品，其销售额为85,470.09元，将销售额乘以税率17%，等于14,529.92元，销售额+税额大于卖价1分钱。
假设你的表是这样设计的，卖价在单元格A3中，销售额在A4中，税额在A5中，按一般的设计，A4中的公式为：“=A3/1.17”，A5中的公式为：“=A4*0.17”，A3、A4、A5中的数值格式通设为小数点两位，在“工具”-“选项”-“视图”中，选“以显示值为准”，这样来保证四舍五入计算的正确性。
在一般状况下，这样的设计无可厚非，但在上例数据中，这个设计就有问题了，也就是“销售额+税额”大于或小于“卖价”一分钱的问题，会给你设计的“销售明细表”之类东西带来麻烦，使你提心掉胆地防备自动计算数据的错误，降低了工作效率。
从目前国税部门的管理来看，发生上述错误国家是允许调整“税额”一分钱的，因此，我们可以通过条件判别的方式，在数据出现上述错误时，调整“税额”一分钱，来达到在表格设计中，只要计算出“卖价”，就能自动正确计算出“销售额”和“税额”。
我们用以下文字来描述公式设计的逻辑性：
如果通过上面公式的计算，发现“销售额”+“税额”大于“卖价”一分钱，那么，“税额”就调小一分钱，反之，税额就调大一分钱。
以上为例：在A5中公式改为“=IF(A4+ROUND(A4*0.17,2)<A3,ROUND(A4*0.17,2)+0.01,IF(A4+ROUND(A4*0.17,2)>A3,ROUND(A4*0.17,2)-0.01,ROUND(A4*0.17,2)))”。
“ROUND(A4*0.17,2)”：其意义就是“税额”计算，A4（销售额）乘以“税率”（0.17）取两位小数后四舍五入。
这上面公式计算有三种结果：
1、A4+ROUND(A4*0.17,2)<A3，即“销售额”+“税额”小于“卖价”；
2、A4+ROUND(A4*0.17,2)>A3，即“销售额”+“税额”大于“卖价”；
3、A4+ROUND(A4*0.17,2)=A3，即“销售额”+“税额”等于“卖价”；
当出现以上情况时公式的处理：
出现1时，“ROUND(A4*0.17,2)+0.01”，“税额”加一分钱；
出现2时，“ROUND(A4*0.17,2)-0.01”，“税额”减一分钱；
出现3时，“ROUND(A4*0.17,2)”，“税额”不加不减，正常计算；
以上就是这个问题公式的剖析。

VNVN

支持！高手！

jyhxr

高啊,分析得非常好,难得,楼主是注册会计师吧,虽然偶不是会计,也不得不佩服楼主哎...

黄其宾

小企业会计而已，大企业会计不会有精力做这个的哟！