Google 試算表(Spreadsheets)是個好工具,內建不少跟 Excel 相似的函數能夠使用,所以會一點 EXCEL 的人都能夠輕鬆上手。如果用來取代 Excel 應該是問題不大,因此我也常常會推薦使用 Google Spreadsheets 統計數據、製作表單等等。

本篇介紹 3 個實用的 Google Spreadsheets 函數:

  1. VLOOKUP
  2. HLOOKUP
  3. MATCH

VLOOKUP

VLOOKUP 的功用是在 特定範圍第 1 欄垂直尋找 特定值並且回傳 指定欄位的值

這麼說可能有點抽象,直接來看以下範例:

  A 欄    B 欄
1 學生    成績
2 小明    100
3 小王    95
3 水仙    88
4 小羊    60
5 火星    35
6 金星    33
7 老牛    10

假設我們有學生、成績 2 欄資料,如果想要輕鬆取的小羊的成績,可以用 VLOOKUP ,只要在儲存格輸入已下公式,就能夠得到小羊的成績。

=VOLOOKUP("小羊", A1:B7, 2, FALSE)

上述公式的 A1:B7 就是一開始提到的 特定範圍 ,所以 Google Spreadsheets 會從特定範圍的 第 1 欄垂直尋找 小羊 ,然後把特定範圍的第 2 欄,也就是 B 欄回傳。

最後一個參數 FALSE 則是代表是否已將特定範圍中的第 1 欄進行排序,此處設定為 FALSE 因為我們希望精準搜尋,不希望 Google Spreadsheets 回傳相似的結果,而 Google Spreadsheets u文件也提到建議設為 FALSE

建議將已排序設為 FALSE。設為 FALSE 後,系統會傳回完全相符的值。如果有多個相符值,系統會傳回與找到的第一個值對應的儲存格內容;如果找不到相符值,則會傳回 #N/A。
如果已排序為 TRUE 或省略,系統會傳回最接近的相符值 (小於或等於搜尋詞)。如果進行搜尋的欄中所有的值均大於搜尋詞,則會傳回 #N/A。

如果已排序設為 TRUE 或省略,且範圍中的第一欄並未排序,系統可能會傳回不正確的值。如果 VLOOKUP 似乎無法提供正確結果,請確認最後一個引數已設為 FALSE。在大多數情況下,該引數應設為 FALSE。不過,如果系統已將資料排序,您可以將該引數設為 TRUE,讓函式發揮最佳效能。

此外, HLOOKUP 也有相同的參數,在此建議同樣設定為 FALSE 即可。

HLOOKUP

VLOOKUP 是垂直的尋找,那 HLOOKUP 的功用就是在 特定範圍第 1 列水平尋找 特定值並且回傳 指定列的值

假設除了學生成績資料外,還多一份學號與學生的對應資料:

  A 欄                                                  L 欄
1 學號  A001    A007    A003    A002    A005    A004    A006
2 學生  小明    小王    老牛    小羊    金星    火星    水仙

我們希望只要搜尋學號,就能夠知道學生姓名的話,就可以用 HLOOKUP ,例如想知道 A003 的姓名,只要輸入以下公式,就能夠得到學生姓名:

=HLOOKUP("A003", B1:L2, 2, FALSE)

上述公式的 B1:L2 就是一開始提到的 特定範圍 ,所以 Google Spreadsheets 會從特定範圍的 第 1 列水平尋找 A003 ,然後把相對應特定範圍的第 2 列,也就是 D2 儲存格回傳。

最後一個參數 FALSE 則是代表是否已將特定範圍中的第 1 列進行排序,此處設定為 FALSE 因為我們希望精準搜尋,不希望 Google Spreadsheets 回傳相似的結果

MATCH

MATCH 就更簡單了, MATCH 是傳回指定值在特定範圍中的相對位置。

以第 1 個範例為例:

  A 欄    B 欄
1 學生    成績
2 小明    100
3 小王    95
3 水仙    88
4 小羊    60
5 火星    35
6 金星    33
7 老牛    10

假設想知道水仙在 A 欄的位置,只要輸入:

=MATCH("水仙", A1:A7, 0)

上述結果會得到 4 ,代表水仙在第 4 個。

此外,上述公式的最後 1 個參數 0 代表的是使用精準搜尋,與 VLOOKUP / HLOOKUP 類似, MATCH 會假設資料已經排序過,所以我們用 0 代表未排序過,強迫 Google Spreadsheets 使用精準搜尋:

0 indicates exact match, and is required in situations where 範圍 is not sorted.

參考資料

  1. https://support.google.com/docs/answer/3093318

  2. https://support.google.com/docs/answer/3093375

  3. https://support.google.com/docs/answer/3093378