在 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。
留言列表