快速計算 Excel VLOOKUP 的 欄位編號

日期：

Excel VLOOKUP 函數可以方便在資料範圍內根據查閱值（搜尋條件），查找對應的欄位值。但是當資料龐大的時候，想要計算其中一欄的欄位編號變得困難（例如資料範圍有 A ~ Z 欄，要找到 T 欄距離 A 欄有幾格？）。以下透過 MATCH 函數可以快速計算取得欄位編號。



遭遇困難：

VLOOKUP 函數語法

VLOOKUP (查閱值, 查閱的資料範圍, 欄位編號, 是否完全符合)

原本資料欄位數量只有個位數時，例如佔了A ~ E 欄五欄的資料範圍 ，欄位編號用手指頭就可以算出來。但是如果資料變得龐大，例如 A ~ Z 欄的資料範圍，想要計算其中一欄的欄位編號變得困難。

解決方式1：(2022/2/13 更新)

MATCH 函數 可以回傳查找的欄位名稱在資料範圍內的相對位置。想要知道 「收入」欄的欄位編號是多少？

= MATCH (查閱值, 查閱的資料範圍, 符合類型)   
= MATCH ("收入", A1:Z1, 0)
將會回傳 4

要特別注意的是 MATCH 函數的第三個變數「符合類型」，如果沒有輸入，預設值是 1。需要手動輸入第三變數 0，才不會取到後面相同欄位名稱的另一個欄位 F 欄。


想要查找產品 ID 「53984413-0A9E-0F99-2F67-854444A5E082」的「收入」。原本使用 VLOOKUP 函數語法是：

= VLOOKUP (查閱值, 查閱的資料範圍, 欄位編號, 是否完全符合) 
= VLOOKUP ("53984413-0A9E-0F99-2F67-854444A5E082", A1:F10, 4, 1) 

將第三變數「欄位編號」修改成使用 MATCH 函數自動取得

= VLOOKUP ("53984413-0A9E-0F99-2F67-854444A5E082", A1:F10, MATCH("收入", A1:Z1, 1), 1) 

解決方式2：

COLUMNS 函數可以回傳欄數，所以使用兩個儲存格的欄數差，可以得到欄位編號。假設 A 欄的欄位編號是 1，想要知道 C 欄的欄位編號是多少？

= COLUMNS(A1:C1) 
將會回傳 3 

解決方式3：

直接使用線上工具：在黃色網址的儲存格輸入「開始欄位」和「終點欄位」的欄位名稱，即可自動計算「終點欄位」的欄位編號。


線上工具一樣是使用 COLUMNS 函數，但是為了方便輸入作了拆解。假設 A 欄的欄位編號是 1，想要知道 Z 欄的欄位編號是多少？先計算「開始欄位」的欄數、「終點欄位」的欄數再換算成欄位編號。
= COLUMNS(A1) 回傳欄數 1
= COLUMNS(Z1) 回傳欄數 26
「開始欄位」的欄位編號設定是 1、則可計算「終點欄位」的欄位編號

因為需要將欄位名稱當作變數傳入函數，所以額外使用到 INDIRECT 函數。

留言

張貼留言