在Excel判斷欄位值是否在陣列裡

今天在臉書上看到一位朋友提問,他的問題是這樣的:

如果我要在B欄呈現「該列的A欄的值如果等於C欄任意一列的值的話,就在該列的B欄打V(例如A1的值是2=C欄中的2,所以在B1打V;A6的值是7=C欄中的7,所以也要在B6打V)」

這是他的問題範例,以及預期的最終結果:

#ABC
12V2
25V5
38V8
467
58V
67V
73
87V
94
103

他的問題整理下就是:

當元素屬於特定一個陣列的元素時,顯示「V」,否則就不做任何事。

這時候我們可以把問題拆解成下列步驟來實現:

  1. 特定一個陣列範圍,用來檢查元素(把它稱為檢查陣列)
  2. 當一個元素屬於檢查陣列元素時,顯示
  3. 當一個元素不屬於檢查陣列元素時,不做任何事。

那2, 3就是邏輯控制:if ... is true then ..., else ...。可以用 Excel中的 IF函數 來處理。

所以這時候問題就聚焦在「在Excel中如何判斷一個元素是否屬於特定陣列

如果是在JavaScript裡,就會很直覺的使用 Array.prototype.includes()。例如就可以解成:

const validNums=[2, 5, 8, 7];
const nums=[2, 5, 8, 6, 8, 7, 3, 7, 4, 3];
nums.forEach(num=>{
    if(validNums.includes(num)){
        console.log(`${num} => V`);
    }else{
        console.log(`${num} =>`);
    }
});
// 2 => V
// 5 => V
// 8 => V
// 6 =>
// 8 => V
// 7 => V
// 3 =>
// 7 => V
// 4 =>
// 3 =>

那在Excel中該如何解?Excel中用來查表函數主要有VLOOKUPHLOOKUPMATCHINDEX

在這裡我主要利用MATCH來解,為什麼用MATCH

  • INDEX主要是以索引值返回元素(search by index),但我們這裡則反過來,是要用元素來查詢其是否在特定陣列裡面(search by value)。
  • VLOOKUPHLOOKUP主要是查詢到後,返回指定欄(列)的值,那還需要在C欄後增設一欄用來賦值,會再多費工。

那我們參照官方文件後,剖析一下match函數式:

=MATCH(lookup_value, lookup_array, [match_type])

代表我們需要⋯⋯

  • 需要比對的數值(lookup_value)
  • 需要搜尋的範圍(lookup_array)
  • MATCH函數該如何進行比對?(match_type)

那我們需要比對的數值,就是A欄的數值A1, A2, A3...,這些可以寫好公式,直接利用Excel自動填入即可。

再來需要搜尋範圍,就是C欄的陣列數值,就是C1:C4

接著MATCH該如何運作?我們是需要精確的比對,所以輸入 0

最後我們可以完成函數式,我們以需要比對A1值是否屬於C欄值為例,所以我們是需要在B1填入公式:

=MATCH(A1, $C$1:$C$4, 0)

不過到這裡還不太正確,我們是需要在比對到時,回傳V,所以在外面再包一層IF函數:

=IF(MATCH(A1, $C$1:$C$4, 0), "V", "")

接著把它拉到其他列,讓程式協助我們自動完成:

excel-tutorial-20170917-1

咦?怎麼顯示#N/A了?

讓我們重新查一下文件,原來⋯⋯

  • MATCH 會傳回 lookup_array 內相符值的位置,而非值本身。例如,MATCH("b",{"a","b","c"},0) 會傳回 2,這是陣列 {"a","b","c"} 內 "b" 的相對位置。
  • ...
  • 如果 MATCH 函數無法找到符合的項目,則傳回 #N/A 錯誤值。
  • ...

那這時候代表:

  1. MATCH函數找到符合項目時,會回傳位置,不會回傳#N/A
  2. MATCH函數找不到時,會回傳#N/A

那連結回我們要解決的問題就是:

  1. 當找到元素時,顯示V;找不到元素時,不做任何事,但也不應該顯示錯誤。所以說當找不到元素時,應該讓欄位顯示空白值,空白值就是""
  2. 而MATCH函數找到元素時,會回傳位置,不會回傳#N/A,這時候應該顯示V;找不到元素時,會回傳#N/A,這時候應該顯示空白值。

簡單講就是:

  • 找到了,不會回傳#N/A,而是會回傳數字。
  • 沒找到,會回傳#N/A

那我們要做的就是,判斷他回傳是不是#N/A,如果是#N/A就顯示空白值,不是的話,就顯示V

那在EXCEL中,判斷是否為#N/A的話,可以用ISERROR()或者ISNA()

所以我們就可以改成:

=IF(ISNA(MATCH(A1, $C$1:$C$4, 0)), "", "V")

讓我們再重新拉一次⋯⋯

excel-tutorial-20170917-2

成功!