sujingjhong.com

在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中用來查表函數主要有VLOOKUP、HLOOKUP、MATCH、INDEX。

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

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

那我們參照官方文件後，剖析一下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", "")

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

圖二

咦？怎麼顯示#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")

讓我們再重新拉一次⋯⋯

圖二

成功！