用 Google 試算表 QUERY 複雜篩選 FILTER

別用複製的 Excel 公式了，Google 試算表 QUERY 一行搞定複雜 Filter ！

我要辦場會議，做了「會議邀請表」調查名單中的人員： 1）是否參加；2）是否用餐；3）是否吃素；4）會後行程是否參加；5）會後行程是否協助安排交通？

用 Excel 和 Google 表單都很容易，調查完把試算表發給客戶，他可以自己篩選、排序來觀看結果吧？

如果這麼想，你一定沒當過乙方，花錢的甲方爸爸是不下參數的，這當然是乙方的工作！甲方要看到的是：

• 參加表、用餐表、吃素表、後續行程參加表、安排交通表… 每個都是單獨一張表；
• 與會人士 30 人中有客戶公司 5 位長官，他們是主人，不顯示在簽到表（簽到表有 25 人）；但要吃飯（用餐表顯示 30 人）；
• 每個表要一個單獨的檔案，不要全部在一個檔案中的好幾頁；
• 族繁不及備載的各種要求。

雖然試算表有篩選、排序… 各種功能，但客戶不需要，他只要簡單結果，貼在 PDF 都行，就是「印出來給我看」！

試算表越做越互動，這種類似「列印」的觀念它並不擅長，還好 Google 試算表和 MS Excel 都有 FILTER() 函數，而 Google 獨有的 QUERY() 更強大，2022 年我不會這麼說，但 2023 年有了生成式 AI，程式碼叫它寫即可，所以它突然變棒了！

Excel 有 Query 外界資料庫的函數，但在單獨檔案中用它我還沒發現。

常識等級說明 SQL 語言如何 FILTER ？

Google 試算表的 QUERY() 簡單粗暴，就是把工程師操作資料庫的 SQL 語言放進括號裡，功能完整、語法簡單、參考資料多，而且你在學校被迫上資訊課時多少聽過一點。

但如果你不是工程師，就算上過課，也忘得差不多了吧？這裡簡單說 SQL 常識，目的是知道 AI 在生成啥，完全看不懂它寫什麼，出錯都不知道是錯了什麼。

「試算表」和「資料庫」差不多，「檔案 > 表 > 欄 > 列」是上下層級的觀念，想找某一格資料，要說哪張「表」中的哪一「欄」下的哪一「列」：

• 有多張「表」（Table/ Sheet）；
• 表中「直行」叫「欄」（Column），用 A、B、C 標示；
• 橫行叫「列」（Row），用數字標示

是不是很像地址「市 > 區 > 路 > 巷 > 弄 > 號 > 樓」的觀念？

如何把它們取出呢？

• SQL 語法： FROM 表 SELECT 欄 WHERE 列
• Query 語法： QUERY(表, "SELECT 欄 WHERE 列", 標題有幾列)

就是這麼簡單，選中一張表，選定表中有幾個欄要拿出，再設定條件把符合條件的列取出，資料就存在列中，叫它條目（Entries），一筆資料是一個條目。

橫行到底是 Row 還是 Entry？比如有張 100 列（Row）的試算表，但裡面只有 30 筆（Entries）資料，空的列沒有資料。

用 SQL 角度來解釋 QUERY

用 FROM 來 FILTER 表（資料來源）

標準 SQL 用 FROM 選擇表，例如「FROM 會議調查表」（只要寫表名）。

QUERY() 把 FROM 放在第一個引數，「資料來源/範圍」（一定要表名 + 範圍）。

• 選同一張表 QUERY(A2:E6, ......)：從現在工作的這張表的 A2 到 E6 當資料來源，來源和結果在同一張表，不用表名。
• 選別張表 QUERY("會議調查表"!A2:E6, ......)：選擇「會議調查表」的 A2 到 E6 範圍，把結果貼到「出席表」，來源和結果不同，要註明表名。

表（資料來源）是 QUERY 跟 SQL 不同之處，我想是因為試算表常常在同一張表中把一塊範圍當作資料來源，把 QUERY 結果放在同一張表的不同的位置，所以要標示範圍。

用 SELECT 來 FILTER 欄

選定範圍後，就可用 SELECT 選出要使用的欄，它還有很多功能，不過不用懂這麼多，就當它只能選出欄吧！叫 AI 幫你寫好即可，例如：

• SELECT * 選擇所有欄
• SELECT A, B, C 用表格標號選擇三欄
• SELECT 單位, 職稱, 姓名 用表格標題選擇三欄

用 WHERE 來 FILTER 列

選定欄後，就篩選顯示哪些列，就是告訴它「哪些顯示哪些不顯示」（如試算表的 Filter）、「用什麼方式顯示」（如試算表的 Rank）、顯示多少筆、修改格式…等，一樣，叫 AI 寫好，所以看幾個範例即可。

• WHERE 會議 = "V"：會議欄有打勾的列顯示，沒打勾的隱藏
• WHERE 類別 <> "長官" ：類別欄是客戶公司長官的則不列出

QUERY() 有 3 個「引數」，SQL FROM 放如第一個，SELECT 和 WHERE 放入第二個，第三格告訴它有幾列標題列，就是這麼簡單粗暴。

而篩選條件就是用「= 等於」、「<> 不等於」、「> 大於」、「< 小於」、「>= 大於等於」、「<= 小於等於」來判斷哪些要顯示出來。

製作表單

如果你想動手玩，這裡是我做的試算表，打開複製一張到你自己的 Google Drive 就可以玩。

聲明：本文範例中的姓名、電話、郵件均以假名產生器自動產生，如有雷同，純屬意外。

做「會議清單」 篩掉長官，留下參加者

所有資料在「總表」中。

• 新增一張「會議清單」表。
• 在「參加會議名單」表的左上角輸入 =QUERY('總表'!A1:H,"SELECT A,B,C,D,E WHERE D<>'長官' AND E='V' ORDER BY D DESC",1)
• Enter，符合條件的新表就跳出來了。

上面那一段說的是：

• 資料來源：'總表'!A1:H 總表左上角 A1 到右下角 H（不打數字表示整列）
• SQL 語句：選擇 ABCDE 欄，以 DE 欄篩選，顯示 D 欄非「長官」、 E 欄是「V」的列，以 D 欄降冪排列
• 標題列：第 1 列為標題

做「用餐清單」所有人都入列

我們所有資料在「總表」中，新增一張「用餐清單」表。

在「參加會議名單」表的左上角輸入 =QUERY('總表'!A1:H, "SELECT A, B, C, D, F WHERE F='V' ORDER BY D DESC",1) ，Enter，符合條件的新表就跳出來了。

原則跟前面相同，這裡把篩選長官的 D<>'長官' 拿掉，因為長官也要吃飯。

後加工

其他的表依此類推，只要打一行字就跳出整頁，接下來就回到試算表的介面用滑鼠操控即可。

加上總計列

在每張表上方加上一條空行，在任一行加上 =COUNTA(C3:C) （COUNT 系列函數用來數「有幾個」，這是計算 C 欄有幾列含內容），就知道總人數了。

有人總把總計放下面，但如果資料往下新增，總計就要一直往下推，位置變了公式就要改，沒改就出錯。因此我都放頂端，加總整欄，減少出錯機率。

QUERY 產出表後，你可以改它的顏色、字體… 等「格式」，但不能加字或改字，因為在複製出來的新表範圍內打字，QUERY 會認為儲存格含有資料，它不可能產出一張表把你原來有資料的格子覆蓋掉，避免衝突它會自動迴避，表就消失了。

修改內容要回到原來那張「總表」。

存成多個檔案

用 QUERY 可以用一行字產生一張表（不然你就要複製表、刪除部分… 很多手工操作），但它們還在同一個檔案中，客戶常常要求要一張表一個檔案，就在頁籤按下「複製到/ 新試算表」，分把每張表存一個新檔即可。

用 QUERY FILTER 的巧妙

方便跟 AI 溝通

如果你試過要 AI 幫你產出 Excel 公式，例如把「A欄 + B欄 x C欄」產生新的「D欄」，因為只能靠打字，你要描述整個檔案有哪些表、欄內容讓 AI 知道，然後它回覆你的還要去找放在哪張表的哪個位置，有點麻煩麻煩。

我想如果很簡單的公式根本不用問它，要問它通常是它有點複雜，我發現各大 AI 指示你 Excel 公式比告訴你複雜的 Python 程式的能力還差。

如果用 Query，你可以先把整張表輸出成 Excel 或 CSV 檔丟給 AI（怕洩密就給標題列），它用一行指令讓你完成很多工作，你就不用去對位置了。

解決函數輸出數字而非內容的問題

原本任務很簡單，就是「用『類別』、『會議』2 欄當條件列出出席客人數」，要研究到 QUERY 是因為試算表自己不認識它自己用滑鼠篩選的結果。

如果只用滑鼠，就會用「漏斗」篩選讓「長官」不顯示（顯示 25 筆），不過，用 COUNTA() 計算還是 30 筆，因為它不認識漏斗篩選！

少數函數如 SUBTOTAL() （小計）認識漏斗篩選，你可以這麼寫 SUBTOTAL(3, 範圍) 即可數「顯示的列數」。

接下來用「會議」欄打勾篩選有參加會議的人，簡單嘛！用 SUBTOTAL 把顯示的列內容送給 COUNTIF() 判斷！抱歉，失敗了。

原因是 SUBTOTAL 送回的不是這些顯示列的「內容」，而是顯示的「列數」，只有一個數字「23」給 COUNTIF，它從 23 找不到如何判斷哪一列要留下來的條件，就出錯。很多傳統 Excel 函數只會送回簡單內容，而不是送回整張表。

你可以試試看，如果要辨識用滑鼠來做漏斗篩選的結果再加上第二個條件篩選，要寫超複雜的公式，後來我就放棄了，乾脆複製一張算了啊！這麼麻煩，殺雞用牛刀！

但 QUERY 只需要打一行字就可以產出新表，因為它傳回整張表，用 COUNTA 也可以計算列數！

我想要用 QUERY FILTER 但不要一張新表

傳統函數的好處是快速給你算完的數字，但如果你要的不只是數字，而是要把結果再做下一輪判斷，它就不管用了，此時 FILTER、QUERY 這種給你一張篩選完新表的函數就很好用。

加入需要傳統函數辦不到的複雜篩選，但又不想要一張新表怎麼辦？例如，我只想在原來頁面頂端顯示「參加人數」。

因為打印新表才能用 COUNT 數數兒，沒關係，可以用 ROWS() 把 QUERY() 包起來，就是它確實產生了一張新表，但只產生在記憶體中沒有顯示出來，用 ROWS 計算這個「不存在的新表」有幾列。

=ROWS(QUERY(A3:H,"SELECT * WHERE D<>'長官' AND E='V'",0))

上面這一段是：

• 建立 QUERY()
• 範圍：本表從有資料開始的全部（不包括標題列），因為同一表，不用標註表名；
• 篩選條件：欄位選全部（反正不會打出來），列的篩選是：1）長官不顯示；2）會議打勾則顯示
• 標題只有 0 列，因為只要算有幾列資料，標題會多跑出一列，而前面就沒選標題列了
• 最後用 ROWS() 把整個 QUERY 包起來，ROWS 的用途是你給它試算表的範圍，它就回覆你內含幾列，我給它整個新表。

唯一麻煩是它不認識你在螢幕上用「漏斗」做的篩選就是了。

連鎖 QUERY，層層 FILTER

QUERY 會產生一張新表，但這表似乎又不真實存在，像泡泡一碰就消失，新表可以當正常表用來再次 QUERY 嗎？

答案是可以的。如上圖，「會議清單」是從「總表」QUERY 產出的，而「QUERY 的 QUERY」又從「會議清單」 QUERY 一次，沒有問題。

這告訴我們，雖然不能直接去產出表修改內容，但可以連鎖 QUERY，所以可以把一切都放在總表，用 QUERY 做出一張張表連鎖使用，總表一改每張表都跟著改，可以簡單自動化。

比如可以這麼玩（不負責任隨便說）：用試算表抓 Spotify 新歌清單（用 API），依照音樂風格放進不同清單，從爵士樂清單抓出不同風格爵士分表，把分表做成很多個 YouTube 播放清單（用 API），再產出影片賺廣告費。

後記

試算表函數有點像程式語言，但少能像程式語言「返回」（Return）大量內容，就可對返回後的內容做下一步處理。

我想受限於 MS Excel 的限制， Google 試算表要讓每個功能跟 Excel 一模一樣，而 Excel 怕修改了用戶不會用，很多程式幾十年不動（例如它居然沒有「刪除列」功能），讓其它業者也只能跟著老舊。

如果客戶（主管）提出很瞎又很複雜的要求時怎麼辦？還好有 Query！