在 Excel 裡常用來搜尋的函數只有幾個，不外乎是 MATCH、INDEX、VLOOKUP、OFFSET 等。其使用上都要依照函數的邏輯來操作，一般使用者可能沒有理解函數的運作模式，所以導致不會設計公式。
本篇再次來試試說明這幾個函數在查詢資料上的使用。
首先，要先弄對誰是欄、誰是列。一般在學生記不住時，我都會指引其依照中文字「欄」和「列」的文字結構來記。（如下圖）
其中，垂直者為「欄」（COLUMN），水平者為「列」（ROW）。
首先來看到 MATCH 函數。
MATCH 函數是指在儲存格範圍裡找出搜尋內容的位置(傳回一個數字)。下圖中「丁」在儲存格B1:F1中的第 4 個位置(第4欄)，所以，公式儲存格D15：=MATCH(C15,B1:F1,0)，會傳回 4。
如果更改儲存格範圍為列所組成，則公式儲存格D15：=MATCH(B15,A2:A13,0)，會傳回「八月」在第 8 列。
接著看到 INDEX 函數。
INDEX 函數是指在儲存格範圍裡找出欄和列交會的儲存格內容，所以由 INDEX 函數語法中看到，需要給予「指定欄」、「指定列」，藉由兩者交會的位置，對應的儲存格即為所求。
下圖中，先藉由 MATCH 函數來求得「指定欄」、「指定列」，再由 INDEX 函數求得對應的結果。
接著要探討 VLOOKUP 函數。
VLOOKUP 函數是指在儲存格範圍裡根據第 1 欄的內容找到符合資料的列，再依指定的欄傳回所對應的儲存格內容。因此，要搜尋的內容必須存在於第 1 欄中。找到第幾列之後，再依指定欄，取出該列中的對應內容。
要注意，第 1 欄雖然用以比對要搜尋的資料，但是也要列入資料範圍內的欄數計數。
下圖中，先藉由 MATCH 函數來求得指定欄，再由 VLOOKUP 函數求得對應的結果。
當要搜尋的資料不包含在第一欄中，則會傳回錯誤訊息：#N/A。
當缺少指定欄位的數值，則會傳回錯誤訊息： #VALUE!。
當指定的欄超出資料的範圍，則會傳回錯誤訊息：#REF!。
最後再看到 OFFSET 函數。
OFFSET 函數是指在依據起始參照儲存格位置，相對位移指定列數和欄數，再由高度和寬度組成儲存格範圍的位址。
在下圖中，以儲存格A1為起始的參照位址，根據指定列數（八月為第 8 列）和欄數（丁為第 4 欄）產生位移找到指定的位置。
在此要注意，儲存格A1位置視為第 0 列、第 0 欄。 在本例中，因為只要找出一個儲存格，所以高度和寬度都設定為 1。
留言列表