Excel检查成本分摊设置
网志图文版：
http://www.b88104069.com/archives/876
成本分摊三元素：制程、工时、成本，月底把这三块资料统计好了之后，当
月所有成本先分摊到各个制程，各制程再依工单工时比例，将制程成本分配
到各个工单，由此计算出该工单产出的单位成本，最后所有工单入库和其他
存货异动加权平均，便可结算出当月成本。
其中成本分摊到制程比较关键，一般ERP系统帐结算成本，例如鼎新Tip-top
，以部门会科组合起来作为一成本项目，先评估这成本项目该由哪些制程分
摊，再设置好分摊权数，明确各制应依多少比例分摊，总分摊权数合计是
100%。举例而言，A部门5100会科当月共有100块成本，这100块以3:2比例分
给a和b两个制程。一家公司假设有十个部门、十个会科、十个制程好了，这
样就有10X10X10=1,000笔资料，实在不是个小数目。
更麻烦的是，费一番功夫设置好庞大数据库，却并非从此一劳永逸，因为会
科并非一成不变、部门组织有可能调整，而且某月份某制程也有可能无工时
产生（根本未开工），有时候甚至连分摊比例都可能需要修改。凡此种种情
形，原来的设置就必须更新，否则成本结算会跳出错误讯息，抛转成本传票
时会拉不出会科。
如前所述，分摊设置的资料过于庞杂，没办法一笔一笔检视是否有误，很需
要有一套完善机制，能把错误讯息侦察出来，倘若系统没有，资讯人员又没
有客制，那只得靠万能的会计人自己动手囉。在此分享我所遇到的实例，还
有相对应的Excel检错方式：
一、首先，如图所示，已经设置好的部门会科，有些当月没有交易金额产生
，另外当月有些新增的部门会科，这两种情况都会使得成本结算出问题，想
要利用Excel侦错，因为涉及到部门会科一组两个变量，必须引用二维数列的
概念。
二、第一直觉我是在常用函数MAX上动手脚：
{=MAX(($D$3:$D$11=A10)*($E$3:$E$11=B10)*$D$3:$D$11)}。这个公式表示
在D3到D11范围里，同时满足D3到D11中等于A10、而且E3到E11中等于B10的储
存格，选择其中最大值。在第十列公式取的是A10B10(会科5300部门D)，D栏
里没有符合的储存格，所以取值是零。在第九列公式取的是A9B9(会科5300部
门C)，D栏里只有一个同时符合这两个条件，就是公式计算结果的5300。需特
别注意阵列符号{}，如果是直接输入，会让储存格变成文字而非公式计算，
要在输入“=MAX(($D$3:$D$11=A10)*($E$3:$E$11=B10)*$D$3:$D$11)”之后
，鼠标停留在公式栏，同时按住Ctrl和Shift不放，再按Enter键，这样会自
动跑出{}，将公式阵列化。
三、上一个步骤求的是实际费用有、分摊设置无的部份，只要依照公式原理
，前后字段稍加替换，便可求出分摊设置有、实际费用无的部份。
四、一般遇到多条件求值的情况，真正的Excel高手信手捻来就是个阵列函数
，我半路出家，阵列观念不及格，只有简单函数MAX常用，所以第一时间将
MAX阵列化，想出前面那个长相奇怪的公式，勉强还堪用。不过既然是多条件
求值，在此当然要介绍名门正宗的阵列函数了。在公式栏输入“
=SUMPRODUCT(($D$3:$D$11=A10)*($E$3:$E$11=B10))”，意思是满足D3到
D11中等于A10、并且E3到E11同一列数也等于B10，这两个条件都满足的储存
格个数。在F10储存格里的公式，实际费用是会科5300部门D，没有设置分摊
，所以计算结果是0个，在上一格F9的公式里，实际费用是会科5300部门C，
设置里刚好有个会科部门都相同的分摊组合，所以计算结果有1个相符。
五、和MAX函数情况相同，上一步骤公式求的是实际费用有，分摊设置无的部
份，只要依照SUMPRODUCT公式原理，将字段稍加替换，便可求出分摊设置有
，实际费用无的部份。
六、想全面了解SUMPRODUCT函数，在公式栏左边的“fx”按一下，函数小教
室跳出来，如果小教室看了还不够，左下角还有个“函数说明(H)”超连结，
点一下即可查阅官方指南。
七、Excel方法很多，同样目的有许多不同路径可以达到，如同这篇文章的
MAX和SUMPRODUCT一般，戏法人人会变，巧妙各有不同，多熟悉一个函数，便
多一种戏法，所以平常没事的时候，只要随便一个空格按下“fx”，所有函
数都在这里，任君学习！
延伸阅读：
Excel：设置成本分摊表（间接部门）
http://www.b88104069.com/archives/1342
Excel：成本分摊设置（直接部门）
http://www.b88104069.com/archives/1403
Excel：边际贡献分析表
http://www.b88104069.com/archives/2461