Excel如何sumif自动求和应收帐款




最近读者来信,有数据要汇总成管理报表,已经整理成数据透视表,但是项目多,手动一个一个带数字进去,仍然很花时间,看能不能一劳永逸,直接拉公式。经过一番心思,我想出来的方案不是很完美,可是稍微多一点「布局」,还真的有函数可以套用,这个案例里面有些值得参考的地方,以下分享:

一、如图所示,这是原始数据,当然,实际情况可能有几百几千笔,文章范例都是极简化的。

出货明细表

二、数据想整理成如图所示的汇总报表,应该还蛮清楚的,其中有个「出货日」字段要特别说明,同样一组地区客户,可能有多次不同日期的出货,在统计时,只标出最早的出货日期即可。另外,这个案例中,每个客户在同一地区,只会有一种币别的出货,不会有两种币别同时出货的情形。

同样一组地区客户,可能有多次不同日期的出货

三、不啰嗦,上枢纽,依照图片所示拉曳字段。

依照图片所示拉曳字段

四、跑出来的枢纽,几乎就是理想中的报表,格式上,难免还是要画龙点睛地修饰,内容上,卡到一个最早出货日,所图片中标黄色的部份,其实只要合计数,不需要各出货日的明细了。

其实只要合计数,不需要各出货日的明细了

五、将枢纽上有用的数字,带到设计好的表格里,便是可以交差的管理报表。

将枢纽上有用的数字,带到设计好的表格里

六、虽然跑枢纽很容易,套数字却很手工,一笔一笔带感觉不怎么SMART,来搞个函数吧。首先,要解决最早出货日的困扰,选取所有数据,将「出货日」依照「最旧到最新」排序。

将「出货日」依照「最旧到最新」排序

七、希望把原来的出货日,以地区客户作划分,变更成是各组地区客户相对应的最早出货日。刚好函数VLOOKUP会带出匹配相符的头一笔数据,利用这个特性,可以达到想要的效果。首先,把地区和客户连在一起:「=A2&B2」,然后每笔资料以地区客户作为条件,查找出明细表中相对应的第一笔出货日,因为出货日已经事先排序过了,带出来恰恰就会是最早出货日,函数公式为:「=VLOOKUP(F2,IF({1,0},$F$2:$F$8,$C$2:$C$8),2,)」。仔细把公式拆解,想想EXCEL会怎么运行这个公式,应该还容易理解的,其中有个「IF({1,0}」,是因为搜寻值在搜寻对象的右边,所以必须先倒置,这个涉及到数组的概念,看起来有点奇怪,但其实作用很简单,左右对调就是了,有机会我要来写篇文章专门介绍。后面多加一栏「=A2&B2&D2」,把地区客户币别都并在一起,是为了加总金额用的,需要分币别计算,所以并入参数中,下一步骤就可以了解其作用。

=VLOOKUP(F2,IF({1,0},$F$2:$F$8,$C$2:$C$8),2,)

八、自动填写出货日的公式:「=IFERROR(VLOOKUP(CONCATENATE(J2,K2),$F$2:$G$8,2,0),””)」,其中「CONCATENATE(J2,K2)」是组合函数,将两个字符串并在一起,作用等同于「J2&K2」,最外面套个「IFERROR(,””)」函数,是如果有地区客户是无此资料的,就带出空白,避免显示难看的「#N/A」。自动填写金额的公式:「=SUMIF($H$2:$H$8,CONCATENATE($J2,$K2,M$1),$E$2:$E$8)」,意思是在数据表格的地区客户币别字段(「$H$2:$H$8」),如果有管理报表里的项目(CONCATENATE($J2,$K2,M$1)),就将金额纳入加总计算($E$2:$E$8),冠个「$」作用是固定住列或栏,以便可以直接将公式往右或往下拉。

=SUMIF($H$2:$H$8,CONCATENATE($J2,$K2,M$1),$E$2:$E$8)


有系統、全面性的學習Excel職場應用,:會計人的Excel小教室PressPlay頻道


當前文章分類:
SUMIF