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」函數,遇到報表以欄作為差異分析的情況,相信能如法泡製!