錦子老師

如下圖所示，A3:B10為增家家貿易公司往來款記錄，金額皆採用「貨幣」格式，由於不同公司的幣種于一樣，因此在D:E欄使用SUMIF函數求和時，如何才能引用B欄其公司原本貨幣格式？

n   解決方案：

使用GET.CELL函數擷取引用儲存格數字格式，再使用TEXT函數將求和結果傳回為相對應格式。

將文字計算式轉換為計算結果：

步驟1： 點取E3儲存格，按 CTRL + F3鍵，開啟「名稱管理員」對話方塊，如下圖所示。

步驟2： 點取「新增」鈕，開啟「新名稱」對話方塊。

步驟3： 點取「名稱」欄位，輸入「FORMAT」字串。

步驟4： 點取「參照到」欄位，輸入「=GET.CELL(7,INDEX($B$3:$B$10,MATCH($D3 , $A$3:$A$10,0)))」字串，如下圖所示。

步驟5： 點取「確定」鈕，回到「名稱管理員」對話方塊。

步驟6： 點取「關閉」鈕。

步驟7：   點取「E3」儲存格，輸入公式為：「=TEXT(SUMIF(A$3:A$10,D3,B$3:B$10) , FORMAT)」任選一種。

步驟8：     將滑鼠指標移到E3儲存格右下角拖拉方塊上方，待指標變為「+」後，按住滑鼠左鍵不放向下拖曳到E4儲存格。

步驟9：請將活頁簿存成204.XLSX。

n   原理分析

1.      在名稱「FORMAT」中，使用INDEX與MATCH二個函數配合，傳回A欄值為D3所對應的B3:B10儲存格中數值，使用GET.CELL函數，取得INDEX傳回儲存格數字格式。

2.      使用TEXT函數將SUMIF函數求得的和傳回為FORMAT格式。

n   注意：

1.      GET.CELL函數第2個參數REFENCE必須對儲存格的參照，因此不可以使用VLOOKUP函數代替OFFSET、INDIRECT、INDEX函數查詢。

2.      TEXT函數無法完成「儲存格格式」對話方塊中「字型」相關功能，也不能接受「顏色」代碼。

n   知識擴展

使用FORMAT作為CELL函數的第一個參數，可以用于傳回引用儲存格的數字格格式代碼，但功能較弱，僅能傳回一些常用的格式代碼，雖然可以通過查詢格式代碼，但對於貨幣、大小寫及其他自定義代碼則無法區分。