贊贊小屋

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數

Excel中的SUMIF和VLOOKUP函數可以排上超好用前十名,本文以外幣銀行餘額評價表為例說明,另外有時公式會落落長,介紹三種建立名稱的方法讓你的公式乾淨俐落。

公司因應商業貿易需求,通常會有多個外幣銀行帳戶,因此在期未帳上一定會有一些外幣存款、甚至是外幣現金。平常也許直接以外幣金額紀錄,不過於結帳出具財務報表中的資產負債表時,總是要依適當匯率全部換算成本幣新台幣金額。這一節便以此為範例,分享如何藉助名稱設定,更為直觀地編製外幣評價表。

一、期末銀行存款餘額表

報表除了「銀行別」和「銀行代號」外,還有「幣別」及「存款餘額」,這裡的「存款餘額」顯然為原幣金額。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第1張

二、SUMIF函數

設計公式:「=SUMIF(‘1′!C:C,’2′!A2,’1’!D:D)」,依照各幣別先彙總「外幣餘額」,希望再接著帶入本期「匯率」,計算出「本幣餘額」。這裡最下面一列為「合計」,金額「550,000」和上個步驟一致,算是以這個方法驗算公式設計是否有問題。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第2張

三、定義名稱

於上方功能區的「公式」頁籤,「已定義之名稱」指令群組中將「定義名稱」下拉,選擇「定義名稱」,然後在「新名稱」對話方塊裡,建立「匯率1」為「=匯率!$A$1:$C$20」,亦即各幣別的「現金」「本行買入」匯率。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第3張

四、名稱方塊

除了以「定義名稱」方式建立名稱,還有個更為直覺的方法,例如選取儲存格A1到D20的範圍,在左上角名稱方塊直接輸入「匯率2」,按下「Enter」鍵確認後,立即新增了一個「匯率2」名稱,其參照範圍即為儲存格A1到D20。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第4張

五、名稱管理員

除了上方功能區的「公式」頁籤裡,「已定義之名稱」指令群組中的「名稱管理員」可以看到完整的名稱並且維護之後,還能直接在工作表左上角的名稱方塊下拉,會出現簡潔版的目前所設定名稱。

既然已經設定好了名稱範圍,運用VLOOKUP函數時,直接以名稱範圍作為查找表格,設計公式:「=IF(A2=”NTD”,1,VLOOKUP(A2,匯率1,3,0))」、「=IF(A2=”NTD”,1,VLOOKUP(A2,匯率2,3,0))」,兩者所計算出來的結果相同。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第5張

六、選取範圍建立名稱

最後最介紹較為進階的名稱設定方法,同樣在上方功能區的「公式」頁籤裡,「已定義之名稱」指令群組中有個「從選取範圍建立」,輔助浮窗說明為「從選定儲存格自動產生名稱」,其具體作用請詳後述。

注意到這裡的資料不同於第四步驟的匯率表:首先有兩個內容完全相同的「幣別A」和「幣別B」,再者「匯率A」、「現金A」、「即期A」和「匯率B」、「現金B」、「即期B」其實內容各不相同,但因為產生名稱的時候為避免名稱相同而衝突,因此特別將欄題標位作了些微修改。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第6張

七、「以選取範圍建立」視窗

跳出「以選取範圍建立」視窗,預設「頂端列」和「最左欄」為勾選狀態,這不但是通常適用的情況,剛好也符合本節範例,因此不作任何變動,直接按「確定」。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第7張

八、自動產生名稱

呼叫出「名稱管理員」視窗,Excel真的依照「頂端列」和「最左欄」一一產生了相對應的名稱。

這裡的「自動產生名稱」,和第四步驟的直接設定又有些不同,以「幣別B」為例,其「參照到」的範圍為「=匯率B!$B$2:$B$20」,如此應當能理解「從選取範圍建立」的作用。

「幣別A」的「參照到」的範圍為「=匯率B!$B$2:$H$20」,和「幣別B」兩相比較,至此應當能理解為何要有兩個內容完全相同的「幣別A」和「幣別B」欄位。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第8張

九、引用名稱的函數公式

利用第七步驟自動產生的名稱,用上本章第一節所學到的技巧,設計函數公式:「=IF(A2=”NTD”,1,MATCH(A2,幣別B,0))」及「=IF(A2=”NTD”,1,INDEX(現金A,MATCH(A2,幣別B,0)))」,即得到和第五步驟相同的計算結果。

另外在D12儲存格輸入「=現金A EUR」,會得到「現金A」和「EUR」兩個名稱交叉的儲存儲值。再加上舉一反三的示例:「現金A USD」、「=現金A JPY」,應該就知道Excel名稱還有這個妙用。

外幣評價表每月更新有點煩?Excel設定匯率名稱,靈活應用SUMIF和VLOOKUP函數 VLOOKUP 第9張

結語:三種建立名稱的方法

Excel有三種建立名稱的方法:「定義名稱」、「名稱方塊」、「從選取範圍建立」,這一節剛好三種方法都介紹了。這裡的名稱皆是代表儲存格範圍,不過名稱也可以是單純的數值或文字,甚至可以是巨集4.0函數,另外名稱在參照到儲存格範圍時,是否以「$」固定也會有複製公式時是否順移的作用。諸此種種,提供給讀者參考,如果在各自的工作上有需要再予以運用變化。

本文內容取自《會計人的Excel小教室進階篇|報表自動化》,書本仍然是最好的學習方法,今天就買本書吧!

金石堂網路書店網址:

https://www.kingstone.com.tw/basic/2014713574503