比對/尋找資料

整理資料時,常常會需要比對資料或是搜尋資料,如果資料少時,或許可以按Ctrl + F搜尋,然後手動填上資料,或是寫寫if()、choose()函數來處理;但,當資料多或是複雜時,就會變成耗時費工。

上圖如果要利用if()函數來處理,會寫到厭世
=if(left(C2, 1)=”A”, “臺北市”, if(left(C2, 1)=”B”, “臺中市”, if(left(C2, 1)=”C”, ……..
A~Z共有26個字母,要寫25組if函數。

對照表格(table_array) 最左欄中尋找含有某特定值的欄位,找到相答的資料後,傳回同一列中指定欄位的值。

: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

: VLOOKUP(要查的值, 對照表格, 傳回欄位, [比對方式])

對照表(table_array)

通常要設定成絕對位址(固定),最左欄 的值一定要是唯一的,不然,只會找到第一筆,不會往下再找出重覆的資料。

比對方式(range_lookup)

true : 大約相符

使用這參數值時,table_array(對照表格)這個參數必須以最左邊的欄位做遞增排序

false : 完全相符

練習範例請點 下載
註1:本範例中的資料都不是真的,僅供練習使用。
註2:工作表(身分證字母代碼)中的資料來源是整理自 臺北市北投區戶政事務所網頁

方法一

對於文字函數不熟者,可在身分證字號跟出生地中間插入一個欄位(字母),用來存放身分證字號的第一碼(英文字母)

D2儲存格的函數
=LEFT(C2, 1)
==>取出C2儲存格中最左邊1個字(第一個字)

函數 : VLOOKUP(要查的值, 對照表格, 傳回欄位, [比對方式])

E2儲存格的函數
=VLOOKUP(D2, 身分證字母代碼!$A$2:$B$27, 2, FALSE)

本範例使用VLOOKUP函數,參數說明如下

參數 說明
要查的值 本範例做法是字母那一欄,所以帶入的參數值為 D2。例如要查M這個字母是對應到哪一個縣市
對照表格 被用來查詢的表格,此表格存放在 身分證字母代碼 工作表中,只要選取需要的範圍即可。儲存格範圍必須設為絕對位址(固定),選取 身分證字母代碼!A2:B27 後按鍵盤的 F4按鍵,欄與列前面會自動加上$,變成 身分證字母代碼!$A$2:$B$27
傳回欄位 傳回的欄位是指要傳回 對照表格 中的哪一欄位的值,本範例是要傳回縣市,所以是第 2 個欄位。
比對方式 本範例要比對的方式是要找到一模一樣的代碼,所以要輸入FALSE參數。

方法一,會在表格中間多出一個看似不必要存在的欄位,所以,這方法比較適合在練習時使用,因為可以在字母那個欄位清楚看到取出來的字母是否正確,可對照查看。
如果這個方法已學會,請試試方法二,省略字母這個欄位,練習在函數之外再加另一個函數或是函數包含另一個函數。


方法二

對於文字函數熟悉者,可直接在出生地那欄輸入函數(=LEFT(C2, 1) )取出身分證字號的第一碼(英文字母)。

函數 : VLOOKUP(要查的值, 對照表格, 傳回欄位, [比對方式])

D2儲存格的函數
=VLOOKUP(LEFT(C2,1), 身分證字母代碼!$A$2:$B$27, 2, FALSE)

本範例使用VLOOKUP函數,參數說明如下

參數說明
要查的值本做法不是利用其他欄位來存放身分證字號的第一碼(字母),而是直接寫函數 LEFT(C2,1) 將字母取出後當成VLOOKUP()函數中的參數。
對照表格被用來查詢的表格,此表格存放在 身分證字母代碼 工作表中,只要選取需要的範圍即可。儲存格範圍必須設為絕對位址(固定),選取 身分證字母代碼!A2:B27 後按鍵盤的 F4按鍵,欄與列前面會自動加上$,變成 身分證字母代碼!$A$2:$B$27
傳回欄位傳回的欄位是指要傳回 對照表格 中的哪一欄位的值,本範例是要傳回縣市,所以是第 2 個欄位。
比對方式本範例要比對的方式是要找到一模一樣的代碼,所以要輸入FALSE參數。