Excel如何自动化整理报表




系统很多默认报表,那些都是符合一般情况、最大公约数设计出来的。实际工作中,为了符合特殊目的,例如填满空白、调整格式、判断单别、计算数值,往往需要再修改系统报表。像这样的财会管理报表,大多是例行性,每周或是每月必须编制,虽然每次整理,并不是太难,但这个一再重工的作业流程,如果「自动化」,岂不更好。在此作个具体介绍:

一、系统应收报表,一张单头应收帐款,有好几项单身出货料号,为了美观,没有数据的单元格空白。

系统应收报表

二、比较理想的数据报表,最好不要有空白,方便筛选、排序、枢纽。因此新建一张工作表,引用原始报表数据,A2单元格因为是第一项,比较特别,直接连结:「=应收!B2」,A3以下是想要填满空白,设计公式为:「=IF(应收!B3″”,B2,应收!B3)」,然而却没有成功带出来。

=IF(应收!B3"",B2,应收!B3)

三、公式本身没有问题,仔细研究过,把光标移到A3单元格的资料编辑列,左右移动,发现虽然显示没有任何内容,但其实单元格里包含了一个空格,这应该是系统报表自己跑出来的,没办法改变。

其实单元格里包含了一个空格

四、系统有政策,Excel有对策。更改公式为:「=IF(TRIM(应收!B3)=””,B2,应收!B3)」,利用Trim函数将空格杀掉,成功带出原始资料。

=IF(TRIM(应收!B3)="",B2,应收!B3)

五、希望报表显示帐款单据的性质,依照单别设定输入判断公式:「=IF(LEFT(B9,2)=”SA”,”销货”,”销退”)」

=IF(LEFT(B9,2)="SA","销货","销退")

六、利用类似公式,可以将原始报表的数据,一一带到另外新建的工作表上,有些原始报表的字段,例如「业务」,因为没需要,不必带过来。

例如「业务」,因为没需要,不必带过来

七、除了直接引用数据,配合管理需要,有些字段数据必须自己设计,例如F3单元格的「净额」公式:「=IF(C3=”销货”,应收!H3,-应收!H3)」,意思是销货取正数,否则(销退)取负数;G3单元格的「月份」公式:「=MID(B3,4,4)」,意思是「帐款编号」B3第四字符位置起,取四个字符;H3单元格的「汇率」公式:「=IF(TRIM(应收!D3)=””,H2,应收!F3/应收!D3)」,意思是「本币应收」除以「原币应收」,如此得到汇率,Trim函数部份先前提过,不再赘述。I3单元格的「税率」公式:「=IF(TRIM(应收!E3)=””,七!I2,应收!E3/(应收!F3-应收!E3))」,意思是如果有税额,将税额除以未税金额(应收减去税额),如此得到税率。最后J3单元格的「本币未税」公式:「=F3/(1+I3)*H3」,意思是含税应收先换算成未税收入,再换算成本币金额,最后得到的,便是每项帐款的收入金额。

=IF(TRIM(应收!E3)="",七!I2,应收!E3/(应收!F3-应收!E3))

这篇文章介绍Excel自动化整理报表的方法。字段数据填好,第一列、第二列公式设好,第三列开始可以一直往下拉,复制公式即可。原始报表有,但是不需要的数据,不用带过来,原始报表没有,但是有需要的资料,可以设计公式计算出来。只要将自动化工作表第一次弄好,往后在做下一期报表时,只要将跑出来的系统报表,取代贴上活页簿中的原始报表,自动化工作表便会更新成新一期的数据,真正的一劳永逸。

原始数据只要第12列,依照本篇文章设的公式,自动化报表第13列开始,还是会一直复制第12列,虽然金额为零,不影响数据正确性,但如果为了美观或者是想检查公式列是否足够,也是可以进一步完善公式设置,关于这部份,以后有适当机会再作补充说明。


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


當前文章分類:
Excel