最近有人問我：「外商會計跟傳統會計做的Excel有什麼不同？」
其實沒有用什麼特別酷炫的東西，但我們經常使用：控制頁（CONTROL）。
為什麼要用控制頁？
因為會計報表常常要依月份比對資料，如果每個 Sheet 都自己手動改月份再加總，函式再強，一整天拉來拉去早晚會出錯，而且報表分析又要求很趕，還要一直檢查月份資料是不是帶錯、重新SUM。
後來才在厲害的人身上學到，如果將Excel頁面的公式都會連動到Control主控頁
換一個月份 → 全部連動。
這樣整份 Excel 不會亂，也比較安心。
CONTROL 頁裡放什麼？怎麼控制?
//C2欄
=DATE(A2,B2,1)
//D2欄
=EOMONTH(C2,0)
你只要改「月份」那一格，
整個報表就知道現在該抓哪一段資料。之後變更月份時，其他報表就會連動到這個月份做變動。
概念:
假設3月時，我從ERP下載下來Data，貼成2月或其他月份，等於在這邊設一個控制點
讓我們在其他頁面瀏覽時，直觀的發現錯誤。
怎麼用CONTROL當開關？
現在假設我每個月都需要把台幣的報表做給美國總公司；每個月要用月初到月底的匯率均價當我的換算標準。
那首先會建立一匯率頁FX_DAILY，並放上每日匯率。
匯率爬蟲可以使用RPA，或是有些ERP有串外面的匯率可以直接從系統下載。
然後Excel sheets會有一頁台幣的分類帳、一頁要換算成美金的分類帳以及最後要集計的TB (Trial Balace)頁
控制頁決定期間後，就要讓匯率連動到Control頁的月份匯率：
=AVERAGEIFS(
FX_DAILY!$C:$C,
FX_DAILY!$A:$A, ">=" & CONTROL!$C$2,
FX_DAILY!$A:$A, "<=" & CONTROL!$D$2)
意思白話：
「將這個月的匯率全部抓出來，算平均。」
這樣不用每個月手動輸入公式，僅是把當月更新的匯率跟分類帳貼進去即可
將單位換成美金，也跟著CONTROL走
Ledger_TWD 是從ERP下載下來的原始資料。
換算 USD 就很簡單，美元那一格的函式為:
//TWD 金額 / CONTROL!平均匯率
=D2/E2
因CONTROL頁已經決定好本月匯率了，所以整個 USD Ledger 也會自動更新。
不會有「我忘記換匯率」這種問題。
最後，把 TB 做成自動的
=SUMIFS(
Ledger_TWD!$C:$C,
Ledger_TWD!$B:$B, <科目>,
Ledger_TWD!$A:$A, ">=" & CONTROL!$C$2,
Ledger_TWD!$A:$A, "<=" & CONTROL!$D$2
)
意思是：
「請把這個月的金額，依各個會計科目的分類加總起來。」
這樣整張 TB 會跟著月份自動跑出來，不需要每個月重做一次報表。
(且偵測是否錯月)
'Amount_TWD B2公式:
=SUMIFS(Ledger_TWD!$D:$D,Ledger_TWD!$B:$B,A2,Ledger_TWD!$A:$A,">="&CONTROL!$C$2,Ledger_TWD!$A:$A,"<="&CONTROL!$D$2)
'Amount_USD C2公式:
=SUMIFS(Ledger_USD!$F:$F,Ledger_USD!$B:$B,A2,Ledger_USD!$A:$A,">="&CONTROL!$C$2,Ledger_USD!$A:$A,"<="&CONTROL!$D$2)
若Control頁的月份填錯，則TB頁會顯示不出資料 /*以做控制點
像這樣瞄一下總金額有沒有對，不用整份報表重新Xlookup、Sumif等等，
能省下許多時間。
最後祝大家都能提早下班，好好規劃生活。
