2020年3月24日 星期二

如何善用 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]如何創建自定義函數

在 Excel 內建函式中找不到自已想要的函式怎麼辦?事實上我們可以自已建一個,以下是一個簡單的Excel自定義函數示例,用於將民國年轉為西元: Function 民國轉西元(x As Double) As Double     民國轉西元 = x + 1911 End Func...