Excel如何设计两期差异分析的函数公式




Excel如何设计两期差异分析的函数公式

会计每月结帐,最普遍的管理工具是两期差异分析,报表上一定会有个当期金额和上期金额,为了方便参考验证和了解趋势,实务上通常会再加入近几期的资料,例如最近半年或者最近一年。於Excel操作时,每次开始一个新的结帐期间,必须将先前的当期改为上期,然後插入一行或者一栏作为当期金额,如此一来,往往原本设定好的差异金额和差异比例公式会跑掉,每次要再调整一次。在此介绍如何设计应用函数,将差异分析的公式固定住,从此不用再每期调整,以下具体介绍:

一丶二月份结帐损益表,包含一月份金额,标黄色部份为差异分析,「差异金额」(D6)的公式是:「=C6-B6」,「差异比率」(E6)的公式是:「=D6/B6」。

标黄色部份为差异分析

二丶到了三月结帐,插入一栏,填入三月份损益金额,仔细看,原本的差异金额和差异比率仍然没变,再仔细看里面的公式,「差异金额」(E6)的公式是:「=C6-B6」,和之前相同,「差异比率」(F6)的公式是:「=E6/B6」。表示如果引用插入栏左边的储存格,公式不受影响,如果是引用插入栏右的储存格,公式会自动跟着往後移。

=E6/B6

三丶首先,介绍「ADDRESS」函数。於「D6」储存格输入公式:「=ADDRESS(ROW(),COLUMN()-1)」」,如同函数视窗的说明:「依照指定的栏列号码,传回代表储存格位址的字串。」,公式的计算结果是为「$C$6」,这里的「Column_num」是「3」,代表Excel里的C栏。经过如此说明,应该可以理解「ADDRESS」函数的妙用之处。

=ADDRESS(ROW(),COLUMN()-1)

四丶然後是重头戏,再於「D6」储存格输入公式:「=INDIRECT(ADDRESS(ROW(),COLUMN()-1))」,公式的计算结果是「84,000」,亦即储存格「$C$6」的值。如此一来,应当能理解「INDIRECT」函数的妙用之处,并且能体会「ADDRESS」函数搭配「INDIRECT」函数的神奇之处。

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))

五丶於是「D6」为两期差异金额的完美公式:「=INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))」。

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))

六丶於是「E6」两期差异比率的完美公式:「=INDIRECT(ADDRESS(ROW(),COLUMN()-1))/INDIRECT(ADDRESS(ROW(),COLUMN()-3))」。

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))/INDIRECT(ADDRESS(ROW(),COLUMN()-3))

七丶新插入一栏三月金额,两期差异金额及差异比率马上随着更新期间。

两期差异金额及差异比率马上随着更新期间

会计人的工作很不平均,月末结帐,月初出报表,「忙季」的时候跟打仗一样,到了月中过後,又有一大段时间是「淡季」,可以轻松悠哉地喝个下午茶的。虽然说淡季空闲下来,但忙季的工作量并不因此减少。工作量就是这麽多,该加班的加班,压力大就只能继续努力。在这种工作特性下,如果能够有任何可以Excel耍点小聪明的地方,所有会计人都应该给自己一个机会。先前我自己每次编制管理报表,这个两期差异公式都要再调整一下,觉得很烦,有一次终於开窍,让「ADDRESS」函数搭配「INDIRECT」函数组合成黄金公式,从此至少有一块小地方轻松许多。建议会计人在月中悠哉喝下午茶的时候,考虑一下这里所介绍的小技巧。

附带一提,范例是以列作为差异分析,如果熟悉了这里所介绍的「ADDRESS」函数和「INDIRECT」函数,遇到报表以栏作为差异分析的情况,相信能如法泡制!


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


當前文章分類:
Excel