如何善用 Query 函數自動捉取最新填寫Google表單的紀錄

利用 Google 表單來製作免費的線上問卷調查，或是收集學校班級、公司、民間團體裡的各種投票、意見、資料，當填問卷者重覆填寫問卷時，要如何捉取填問卷者最新回覆的內容呢?

一般人可能會想到將Google 表單填寫的回覆內容寫入Google 試算表後，使用 vlookup 函式捉取，但使用該函式只會捉取指定範圍中的符合條件第一筆資料，而Google 表單紀錄顯示是依填充時間順序由上往下排列，因此使用vlookup 函式，只能捉到最早填寫的紀錄，如以下例子所示。

在上面紅色框我們可以看到Google 表單的紀錄是依填寫時間排列，最新填寫的紀錄，會顯示在最下列，右邊我們顯示了用 vlookup 函式，查找ID為1001人員姓名，查找的結果為「王小明」，是ID為1001中的第一筆紀錄，但通常我們會想要捉取填表者最新填寫內容，就以上例而言我們會想要捉取的名字為「王三明」。

要達成上面要求，我們可以使用 query 、 index 及 rows 三個函式來達成，這三個函式中最主要的是用 query 函式找出所有符合 ID 為 1001 的姓名，在本例也就是 「王小明」及「王三明」。

首先使用 query 函式語法如下:

QUERY(資料, 查詢, [標題]）

參數設定如下：

資料: 為所要搜尋資料範圍，在本例為 A:D 欄。

查詢: 所用的語法為 the Google Visualization API Query Language. 語法類似 SQL，在本例我們們使用下面語法

select D where B =1001

上面語法的意義是搜尋符合 B 欄 (ID) 為 1001的所有 D 欄 (姓名)的欄位。

整個語法為

query(A:D,”select D where B=1001")

上述語法執行結果如下:

用 query 函式找出符合條件資料後，接下來捉出最後一筆（最新）資料，這時可用 index 函式，語法如下:

INDEX(參照, 列, 欄 )

參數設定如下：

參照: 在此為用 query 函式找出的結果。

列: 在此我們要找最後(新)一列，我們可以用 rows 函式，傳回用 query 函式找出的結果列數。

欄: 在本例只有一欄，所以為 1。

整個語法為

index(query(A:D,”select D where B=1001"),rows(query(A:D,”select D where B=1001")),1)

上述語法執行結果如下:

我們可以看到執行的結果，是我們想要捉取的姓名為「王三明」。活用 query 、 index 及 rows 三個函式，即可捉取重覆填寫 Google 表單的最新紀錄，達到使用 vlookup 無法達到的效果。

善用 Excel 進階函數 vlookup，解決工作上大小事。

Excel 我們知道提供很多函數，從數學、財務、統計、日期...等等有上百個函數，但要在這些函數選一個最常用的函數，我想非 vlookup 莫屬，這個函數主要用途是在您指定的資料範圍內，透過「查閱值」，去尋找在所指定資料範圍內，你所要的欄位資料，其語法如下:

VLOOKUP(查閱值 , 資料範圍 , 指定欄位索引, [已排序])

其參數說明如下:

• 查閱值是我們所要搜尋的值，使用上有二個限制:

1. 必需是資料範圍內第一欄所會出現的值。
2. 在資料範圍第一欄內，該值最好在第一欄內是唯一值，否則結果可能不如預期。

• 資料範圍係我們所要搜尋的範圍，系統會在該範圍的第一欄尋找搜尋詞中指定查閱值。
• 指定欄位索引，代表要傳回的值所屬的欄索引。資料範圍中的第一欄編號為 1，也就是說，如果你想要傳回資料範圍內第3欄的資料，指定欄位索引就設為3，如指定欄位索引並非介於 1 和資料範圍的欄數之間，系統會傳回 #VALUE!。
• [已排序]有二種情形:
1. 絕大多數情況下，請設為 FALSE，該設定會使系統會傳回完全相符的值。如果有多個相符值，系統會傳回與找到的資料範圍第一欄中第一個值對應的儲存格內容；如果找不到相符值，則會傳回  #N/A。
2. 設為 TRUE 或省略，系統會傳回最接近的相符值 (小於或等於搜尋詞)。如果進行搜尋的欄中所有的值均大於搜尋詞，則會傳回 #N/A。

以下舉一實際範例來說明如何應用，現假設我們想從下面範例的股票股利股息表，捉出特定公司現金股息及股價，算出其現金殖利率，在此因為股票代號是唯一的，當然我們就選用股票代號為查閱值，去查出該股票本年度的現金股息及股價。

因此在下面現金殖利率表中，股票代號由我們手動輸入所要查詢的股票代號，股價欄位則應用 vlookup 捉取，語法如下:

=vlookup(B2,'股票股利股息表'!A:F,6,false)

在此查閱值為現金殖利率表的B2，也就是股票代號欄位;資料範圍為股票股利股息表的A到F欄;指定欄位索引在此我們要的是股價，也就是在股票股利股息表由股票代號起算的第6個欄，因此設定為6;[已排序]一般則設為 false 。同樣的現金股息的部分也是相同的方式，語法如下:

=vlookup(B2,'股票股利股息表'!A:F,3,false)

得到股價及現金股息，就可以計算出現金股息殖利率了，在下表的範例1，我們展示計算泰詠現金股息殖利率的結果;在範例2，我們展示如果我們沒輸入股票代號或輸入股票代號，不在股票股利股息表中時，會傳回  #N/A，如果要避免出現，可加上 iferror 函數，將 #N/A轉為我指定的值，因此前面兩個函數可修改如下:

=iferror(vlookup(B4,'股票股利股息表'!A:F,6,false),) 

=iferror(vlookup(B4,'股票股利股息表'!A:F,3,false),)

修改後的結果如範例3所示，在此我們將 #N/A 改為空白 。

前面介紹完 vlookup 語法，粗略來看該語法所做的事情，事實上很簡單，就是將我們所要的資料，由指定資料範圍找出來，有點像是簡單查詢程式，但其應用變化多端存乎一心，用的好的話可以解決工作上的很多問題，例如可用在下面場合:

1. 如同前面的範例，將其中一個表當作資料庫，將我們所要欄位資料捉到另一個表做計算使用。
2. 比對兩個表或欄位資料是否有差異，在文書作業中，我們常常會製做很多表，有的時侯版本控管沒控管好，時間久了我們會忘了那一版為最終版，這時可用 vlookup 函數來做比對，方法是在其中一個表中捉取另一個表中的資料，看是全部都會捉到，還是會出現#N/A，由此可知兩個表的差異，當然如果要更細緻一點，可以在加上 if , and ,or 等判斷式，做更細部的確認。
3. 可以用於將多張不同的表，匯整成一張大表使用。
4. 將適用於試算表運算的表，改為適合的閱讀或文書作業的表，就如同在如何將紙本表單轉為 Google 表單後，讓填寫人填寫完後轉為紙本表單格式印出。 所做的，將表單填表人所填的資訊，轉為紙本表單格式印出。
5. 跟 4 的應用反過來，是將適合的閱讀或文書作業的表改為試用試算表計算用的表，在此時單用 vlookup 可能沒辦法完全處理好，需要搭配使用 index 、 column、row 等函數才有辦法處理。

以上為 vlookup 使用方式及其應用的介紹，如果能應用的好，事實上可以解決工作上的很多問題，故我一直認為 vlookup 為 Excel 最重要的函數，不過如果你問我在 Google 試算表是否也是如此，那我會說不一定，原因為在 Google 試算表有提供另一個同類型的函數 query ，其使用限制更少，功能更強，有興趣朋友可以參考我這篇文章。但 query 使用語法較為複雜，需下如 SQL 的語法才能使用，而且限制在Google 試算表才能用，因此 vlookup 還是一值得學習的函數。

如何捉取其他 Google 試算表資料

在 Goole 試算表中，如果我們要捉取其他試算表的資料，例如現在有一個工作表2如下:

現我們想將工作表2 A1 格內的資料連結到工作表1 A1 的資料，我們可以在工作表１的 A1　鍵入　='工作表2'!A1　，這麼做就可將工作表２的A1格的資料連結到工作表１的 A1格中，結果如下面所示。

但如果是要捉取別的 Google 試算表內工作表的資料，該如何做？在 Google 試算表中可以使用 importrange 函式，語法如下:




IMPORTRANGE(spreadsheet_key, range_string)

其中 spreadsheet_key - 做為資料匯入來源的試算表網址，spreadsheet_key 的值必須放置在引號內，或者是一個含有適當文字的儲存格參照。例如我們想要將試表算1的工作表2的A1:B3 的資料連結到試算表2的工作表1，我們要先取得試算表1的網址，如下圖紅框所示。

https://docs.google.com/spreadsheets/d/10Pa3p6E5STYxFoq4UPCXMoraQxxS9ml5UZqP4Q_3Sv4/edit#gid=2090295703

在使用時，可將全部的網址用引號括起來，然後直接帶入函式中，不然也可以只取 spreadsheet_key，也就是 d/ 至 /edit 中間粗體字串使用。

range_string - 符合 "[sheet_name!]range" 格式的字串 ，用於指定要匯入的範圍。在本例也就是  工作表2'!A1:B3 ，也需要用引號括起來。

整個函式為以下:

=importrange("10Pa3p6E5STYxFoq4UPCXMoraQxxS9ml5UZqP4Q_3Sv4", "'工作表2'!A1:B3")

代入試算表2一開始會出現錯誤訊息 #REF ，如下圖所示。

這是因為還未取得授權，只要點下允許存取鍵即可，結果如下圖。

如此即完成整個設定過程。

如何將紙本表單轉為 Google 表單後，讓填寫人填寫完後轉為紙本表單格式印出。

我們在工作常有一些制式的紙本表單，需要定時發佈給個人或單位填報繳回，七請八催收齊後，還要在將紙本的資料在鍵入表格匯整，這樣等於同樣的填報動作要做兩次，除了浪費人力外，也增加人為錯誤的機率，這時我們可以用Google 表單發佈給個位或單位填寫，來避免上述問題，但是有些紙本調查表單係因應法規或規定所產生的，而且需要有核章過的紙本紀錄留存(在大公司跟公家機關工作的應該會暸解我的意思)，這時就會產生問題，這時就會想有沒有一種辦法，讓填寫的人填寫完後，直接轉成紙本表單的格式印出核章交回留存。

要達成這樣的目標，可以在 Google 表單的資料轉入 Google 試算表中著手，下面以實例說明，現在假設我們有一個紙本表單格式如下:

我們將其轉為 Google 表單結果如下:

設假設 Google 表單填表人已填列資訊如下:

要將填表人所填資料帶入最上面所述紙本表單的格式中，可在 Google 試算表中新增一個工作表，在本例將工作表名就定為「表單」，並將紙本表單格式設定好。

接下來要將填表人所填之資料帶入表中，可用以下兩種做法:

• 如果很確定填表人不會重複填入，可以用 vlookup 函式，但是該函式要求 搜尋詞 必需在 資料範圍 的最前列，故 Google 表單在設計時需將含有 搜尋詞 的欄置於 資料範圍 的最前欄，想進一步瞭解vlookup用法，可參考「善用 Excel 進階函數 vlookup，解決工作上大小事。」做法。
• 如果會重複填寫而且要捉取的是最新填寫的資料，就要用 query 函式結合 index 及rows 函式來達成，就如同前篇「如何善用 Query 函數自動捉取最新填寫Google表單的紀錄」做法。

無論是用前述二種方法的那一種方法，都要有一個填表人唯一代碼，去做 搜尋詞 ，這可以是人員的員工編號、機關代號或是其他可代表唯一的代表號，在本例就是 ID 編號，故紙本表單格式中的 ID 欄位，設定為讓填表人輸入其 ID 編號 ，姓名 及 出生年月日 這二個欄位，就可由 ID 編號去搜尋 表單回應1 內的資料，在本例中因為允許填報者重複輸入，故用第2種做法，姓名 及 出生年月日 的語法如下 :

• 姓名:

index(query('表單回應 1'!A:D,CONCATENATE("select D where B=",B3)),rows(query('表單回應 1'!A:D,CONCATENATE("select D where B=",B3))),1)

• 出生年月日

 index(query('表單回應 1'!A:D,CONCATENATE("select C where B=",B3)),rows(query('表單回應 1'!A:D,CONCATENATE("select C where B=",B3))),1)

經過上述設定後，以下為輸入 ID 代號為 1001 的結果。

 需要列印時，選擇 Google 試算表 [檔案]->[列印] 即可。

以上雖完成主要功能設定，但還有下列細項要設定:

• 為避免填表人看到其他人所填的資料內容，可將表單回應的工作表隱藏，此隱藏不影響 Google 表單的運作。

• 前面所用函式在 ID 欄位未填值時，會出現 #VALUE! ，不太美觀而且易造成誤解，可以用 iferror 函式來避免出現此一情形，例如以下:

iferror(index(query('表單回應 1'!A:D,CONCATENATE("select D where B=",B3)),rows(query('表單回應 1'!A:D,CONCATENATE("select D where B=",B3))),1),)

• 為了避免前面辛苦設定的函式被不小心誤刪，可以將工作表設為保護，但是記得要將 ID 這一欄位設為除外欄位，否則填表人將會無法使用。

• 接著為保持操作一致性，可用 Google 協作平台將 Google 表單及 Google 試算表放在一起，利於填表人作業，如以下影片所示 :

以上介紹如何將紙本表單改為 Google 表單後，保有紙本表單格式輸出的作法，即可結省人力，也可達到紙本表單輸出核章留存的效用，有興趣的朋友不妨可以試試看。