Excel如何以函数自动生成枢纽彙总




会计Excel函数的最高境界,我觉得是自动编製例行性报表。想当初,我是台北某公司小会计课长,每个星期五资金预估,那资金预估表看起来複杂,其实就是把ERP导出来的报表,经过一番整理,将所需要资讯彙总在一起而已。每个星期,我得重覆一遍相同的步骤,历时至少半个小时。后来,我把这些步骤写成函数,每星期将最新的ERP报表複製贴上,Excel函数就会刷刷刷,自动帮我编製好报表,历时五分钟!我的这个实验,让当时Excel功力已达资料库的主管,都为之讚赏,以下,分享最基础的步骤,也就是以函数自动生成枢纽彙总:

一、ERP跑出来的模拟付款明细表,如图所示,实际上还有厂商、帐款日期等栏位,被我省略掉了,而且通常一个星期或一个月的资料笔数,会远远多出此范例很多,我这是简单说明的删除版本。

ERP跑出来的模拟付款明细表

二、会计人一般拿到这种报表,很多时候就是跑枢纽,统计某栏位的金额,例如,将模拟付款明细表的应付款日彙总,得出之后每个日期应付款总金额,方便作资金规划。

将模拟付款明细表的应付款日彙总

三、每个星期的ERP报表笔数不同,要自动整理资料,首先要自动判断资料笔数有多少:「=COUNTA(报表!A:A)-1」,函数「COUNTA」会计算范围中非空值的储存格个数,这裡减个1,是把栏位那一列减掉,所以会得出ERP报表的笔数,也就是「12」。

=COUNTA(报表!A:A)-1

四、在自动编製报表的Excel档案中,至少要有两个工作表,一个是把最新的ERP报表複製贴上,一个是自动编製的报表(会先把栏位名称的表头贴上)。在判断出最新ERP报表的资料笔数之后,第一步是自动複製资料,以便于后续处理:「=IF(ROW(B2)-1>$O$2,””,报表!A2)」,函数「ROW」会传回引用储存格的列数,例如「B2」的列数就是2,「(ROW(B2)-1」表示是减掉第一栏栏位名称后的列数,「$O$2」就是上一项用「=COUNTA(报表!A:A)-1」得到的资料笔数,「$」用意是锁定储存格,「=IF(ROW(B2)-1>$O$2,””,报表!A2)」表示如果列数大于资料笔数了,显现空白,否则传回ERP报表A2(第一栏第二列)资料。

=IF(ROW(B2)-1>$O$2,"",报表!A2)

五、只要没有挂「$」锁定,拉储存格公式时,栏位和列数会自动跳,例如「B2」储存格带「报表!A2」的内容,「C2」会带「报表!B2」的内容,「B3」就会带「报表!A3」的内容,利用这个特性,就可以把整个最新ERP报表的内容複製生成,而且因为之前有抓ERP报表的资料笔数,所以控制好超出范围的会显示空白,这样比较美观。

只要没有挂「$」锁定,拉储存格公式时,栏位和列数会自动跳

六、想要依照「应付款日」彙总,所以必须先整理出「应付款日」的内容,第一步:「=IF(C2=””,””,SMALL(C:C,ROW(H2)-1))」,「IF(C2=””,””,」同样是控制超出笔数范围的空白,「SMALL(C:C,ROW(H2)-1)」是将12笔资料的应付款日,从小排到大,「C:C」是应付款日栏位,「ROW(H2)-1)」的计算值是1、「ROW(H3)-1)」的计算值是2,这样便得到一个序列,「SMALL(C:C,ROW(H2)-1)」表示在C栏中,取第一小的储存格内容。

=IF(C2="","",SMALL(C:C,ROW(H2)-1))

七、实际操作中,不需要那麽多相同的应付款日(如「2015//9/16」),要再想个办法整理出不同的应付款日排序:「=IF(C2=””,0,IF(EXACT(H2,H1),0,H2))」,「IF(C2=””,0,」如果空白,显示数值为0,否则「IF(EXACT(H2,H1),0,H2))」,也就是如果左边栏位的上下储存格相同,同样显示数值为0,否则传回H2,如图所示,结果便是把第一个出现不同的应付款日保留,其馀归零。

=IF(C2="",0,IF(EXACT(H2,H1),0,H2))

八、在上一个步骤的基础,可以先编个应付款日的序列:「=COUNTA(I:I)-COUNTIF(I:I,0)-ROW(J2)+2」,「COUNTA(I:I)」为I栏中非空值的储存格个数,「COUNTIF(I:I,0)」为I栏中数值为0的储存格个数,「COUNTA(I:I)-COUNTIF(I:I,0)」就是I栏中非0的应付款日个数(4),「ROW(J2)」的数值为2,所以整个公式「=COUNTA(I:I)-COUNTIF(I:I,0)-ROW(J2)+2」的结果是(4+2-2)=4,往下拉,便成了4、3、2、1、0、-1、……的数列排序。

=COUNTA(I:I)-COUNTIF(I:I,0)-ROW(J2)+2

九、有了数列排序,接下来可以排序应付款日了:「=IF(J2<1,””,LARGE(I:I,J2))」,如果K栏相对的J栏的应付款日小于1,显示空白,否则就在I栏中取相对应大的储存格内容,例如「J2」是4,在「K2」的公式会传回J栏中第四大的内容,也就是「2015/9/16」,「K3」会传回第三大,I栏总共有五个应付款日,重覆的应付款日都被设定为「1900/1/0」(0),所以依序排列的结果,如图所示。

=IF(J2<1,"",LARGE(I:I,J2))

十、最后,再用个「=SUMIF(C:C,K2,F:F)」,成功达到跟枢纽彙总相同的效果,而且重点是,以后每次要再彙总模拟付款明细表,只要将最新或更新的ERP报表,整个複製贴上到资料工作表即可,这对于一再重覆的例行性报表,有很大的自动化作用。

=SUMIF(C:C,K2,F:F)

关于SUNIF函数的用法,可参考先前文章:《Excel如何彙总营业成本表项目》《Excel如何帐龄加权计算合计》《Excel如何多条件求和》


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


當前文章分類:
数据透视表