Excel如何設計兩期差異分析的函數公式 - 會計

Excel如何設計兩期差異分析的函數公式

網誌圖文版：

http://www.b88104069.com/archives/4226

會計每月結帳，最普遍的管理工具是兩期差異分析，報表上一定會有個當期
金額和上期金額，為了方便參考驗證和瞭解趨勢，實務上通常會再加入近幾
期的資料，例如最近半年或者最近一年。於Excel操作時，每次開始一個新
的結帳期間，必須將先前的當期改為上期，然後插入一行或者一欄作為當期
金額，如此一來，往往原本設定好的差異金額和差異比例公式會跑掉，每次
要再調整一次。在此介紹如何設計應用函數，將差異分析的公式固定住，從
此不用再每期調整，以下具體介紹：

一、二月份結帳損益表，包含一月份金額，標黃色部份為差異分析，「差異
金額」（D6）的公式是：「=C6-B6」，「差異比率」（E6）的公式是：「
=D6/B6」。

二、到了三月結帳，插入一欄，填入三月份損益金額，仔細看，原本的差異
金額和差異比率仍然沒變，再仔細看裡面的公式，「差異金額」（E6）的公
式是：「=C6-B6」，和之前相同，「差異比率」（F6）的公式是：「=E6/B6
」。表示如果引用插入欄左邊的儲存格，公式不受影響，如果是引用插入欄
右的儲存格，公式會自動跟著往後移。

三、首先，介紹「ADDRESS」函數。於「D6」儲存格輸入公式：「
=ADDRESS(ROW(),COLUMN()-1)」」，如同函數視窗的說明：「依照指定的欄
列號碼，傳回代表儲存格位址的字串。」，公式的計算結果是為「$C$6」，
這裡的「Column_num」是「3」，代表Excel裡的C欄。經過如此說明，應該
可以理解「ADDRESS」函數的妙用之處。

四、然後是重頭戲，再於「D6」儲存格輸入公式：「
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))」，公式的計算結果是「84,000」
，亦即儲存格「$C$6」的值。如此一來，應當能理解「INDIRECT」函數的妙
用之處，並且能體會「ADDRESS」函數搭配「INDIRECT」函數的神奇之處。

五、於是「D6」為兩期差異金額的完美公式：「
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))-INDIRECT(ADDRESS(ROW(),COLUMN()-2))
」。

六、於是「E6」兩期差異比率的完美公式：「
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))/INDIRECT(ADDRESS(ROW(),COLUMN()-3))
」。

七、新插入一欄三月金額，兩期差異金額及差異比率馬上隨著更新期間。

會計人的工作很不平均，月末結帳，月初出報表，「忙季」的時候跟打仗一
樣，到了月中過後，又有一大段時間是「淡季」，可以輕鬆悠哉地喝個下午
茶的。雖然說淡季空閒下來，但忙季的工作量並不因此減少。工作量就是這
麼多，該加班的加班，壓力大就只能繼續努力。在這種工作特性下，如果能
夠有任何可以Excel耍點小聰明的地方，所有會計人都應該給自己一個機會
。先前我自己每次編制管理報表，這個兩期差異公式都要再調整一下，覺得
很煩，有一次終於開竅，讓「ADDRESS」函數搭配「INDIRECT」函數組合成
黃金公式，從此至少有一塊小地方輕鬆許多。建議會計人在月中悠哉喝下午
茶的時候，考慮一下這裡所介紹的小技巧。

附帶一提，範例是以列作為差異分析，如果熟悉了這裡所介紹的「ADDRESS
」函數和「INDIRECT」函數，遇到報表以欄作為差異分析的情況，相信能如
法泡製！


延伸閱讀：

Excel如何調整財務報表的差異比率格式

http://www.b88104069.com/archives/1819

Excel如何檢查會計報表的金額是否一致

http://www.b88104069.com/archives/4036

Excel如何編製損益表的費用分析報告

http://www.b88104069.com/archives/3369

--


周末，我們繼續Excel：精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室： https://www.facebook.com/acctexcel


--