- Excel 和 Google 試算表都可以用介面簡單視覺化的篩選、排序…，但遇到複雜篩選就很卡，此時 QUERY 可以幫你解決。
- 別用複製的 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），用數字標示
- 是不是很像地址「市 > 區 > 路 > 巷 > 弄 > 號 > 樓」的觀念？
- 檔案中有幾張表 > 表中有幾欄 > 欄中有幾列，有上下層關係，所以 FILTER 的順序是選：爸爸（表） > 兒子（欄） > 孫子（列）
-
如何把它們取出呢？
- SQL 語法：
FROM 表 SELECT 欄 WHERE 列 - Query 語法：
QUERY(表, "SELECT 欄 WHERE 列", 標題有幾列) - 就是這麼簡單，選中一張表，選定表中有幾個欄要拿出，再設定條件把符合條件的列取出，資料就存在列中，叫它條目（Entries），一筆資料是一個條目。
-
橫行到底是 Row 還是 Entry？比如有張 100 列（Row）的試算表，但裡面只有 30 筆（Entries）資料，空的列沒有資料。
-
- SQL 語法：
-
用 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 單位, 職稱, 姓名用表格標題選擇三欄
- 選定範圍後，就可用 SELECT 選出要使用的欄，它還有很多功能，不過不用懂這麼多，就當它只能選出欄吧！叫 AI 幫你寫好即可，例如：
-
用 WHERE 來 FILTER 列
- 選定欄後，就篩選顯示哪些列，就是告訴它「哪些顯示哪些不顯示」（如試算表的 Filter）、「用什麼方式顯示」（如試算表的 Rank）、顯示多少筆、修改格式…等，一樣，叫 AI 寫好，所以看幾個範例即可。
WHERE 會議 = "V"：會議欄有打勾的列顯示，沒打勾的隱藏WHERE 類別 <> "長官"：類別欄是客戶公司長官的則不列出
- QUERY() 有 3 個「引數」，SQL FROM 放如第一個，SELECT 和 WHERE 放入第二個，第三格告訴它有幾列標題列，就是這麼簡單粗暴。
- 而篩選條件就是用「= 等於」、「<> 不等於」、「> 大於」、「< 小於」、「>= 大於等於」、「⇐ 小於等於」來判斷哪些要顯示出來。
- 選定欄後，就篩選顯示哪些列，就是告訴它「哪些顯示哪些不顯示」（如試算表的 Filter）、「用什麼方式顯示」（如試算表的 Rank）、顯示多少筆、修改格式…等，一樣，叫 AI 寫好，所以看幾個範例即可。
-
- Google 試算表的
-
製作表單
- 如果你想動手玩，這裡是我做的試算表，打開複製一張到你自己的 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，就會以它為左上角把 FILTER 的結果貼在這張表上。
- 所有資料在「總表」中。
-
做「用餐清單」所有人都入列
- 我們所有資料在「總表」中，新增一張「用餐清單 」表。
- 在「參加會議名單」表的左上角輸入
=QUERY('總表'!A1:H, "SELECT A, B, C, D, F WHERE F='V' ORDER BY D DESC",1)，Enter，符合條件的新表就跳出來了。 - 原則跟前面相同，這裡把篩選長官的
D<>'長官'拿掉，因為長官也要吃飯。
-
後加工
- 其他的表依此類推，只要打一行字就跳出整頁，接下來就回到試算表的介面用滑鼠操控即可。
-
加上總計列
- 如果前 31 列是標題 + 資料，把總計寫在 32 列，就是加總「C3:C31」，如果增加一筆資料就要改公式了。所以我把總計列寫頂端，增加多少列，公式都不用改，C3:C 就是 C 欄第 3 列直到最後一列，不管有幾列
- 在每張表上方加上一條空行，在任一行加上
=COUNTA(C3:C)（COUNT 系列函數用來數「有幾個」，這是計算 C 欄有幾列含內容），就知道總人數了。-
有人總把總計放下面，但如果資料往下新增，總計就要一直往下推，位置變了公式就要改，沒改就出錯。因此我都放頂端，加總整欄，減少出錯機率。
-
- QUERY 產出表後，你可以改它的顏色、字體… 等「格式」，但不能加字或改字，因為在複製出來的新表範圍內打字，QUERY 會認為儲存格含有資料，它不可能產出一張表把你原來有資料的格子覆蓋掉，避免衝突它會自動迴避，表就消失了。
- 修改內容要回到原來那張「總表」。
-
存成多個檔案
- Google 試算表複製表成為一個新檔案很容易
- 用
QUERY可以用一行字產生一張表（不然你就要複製表、刪除部分… 很多手工操作），但它們還在同一個檔案中，客戶常常要求要一張表一個檔案，就在頁籤按下「複製到/ 新試算表」，分把每張表存一個新檔即可。
- 如果你想動手玩，這裡是我做的試算表，打開複製一張到你自己的 Google Drive 就可以玩。
-
用 QUERY FILTER 的巧妙
-
方便跟 AI 溝通
- 如果你試過要 AI 幫你產出 Excel 公式，例如把「A欄 + B欄 x C欄」產生新的「D欄」，因為只能靠打字，你要描述整個檔案有哪些表、欄內容讓 AI 知道，然後它回覆你的還要去找放在哪張表的哪個位置，有點麻煩麻煩。
- 我想如果很簡單的公式根本不用問它，要問它通常是它有點複雜，我發現各大 AI 指示你 Excel 公式比告訴你複雜的 Python 程式的能力還差。
- 如果用 Query，你可以先把整張表輸出成 Excel 或 CSV 檔丟給 AI（怕洩密就給標題列），它用一行指令讓你完成很多工作，你就不用去對位置了。
-
解決函數輸出數字而非內容的問題
- 原本任務很簡單，就是「用『類別』、『會議』2 欄當條件列出出席客人數」，要研究到 QUERY 是因為試算表自己不認識它自己用滑鼠篩選的結果。
- 用螢幕上的「漏斗」就可以 FILTER ，問題是僅少數函數認識它
- 如果只用滑鼠，就會用「漏斗」篩選讓「長官」不顯示（顯示 25 筆），不過，用
COUNTA()計算還是 30 筆，因為它不認識漏斗篩選！ - 少數函數如
SUBTOTAL()（小計）認識漏斗篩選，你可以這麼寫SUBTOTAL(3, 範圍)即可數「顯示的列數」。 - 接下來用「會議」欄打勾篩選有參加會議的人，簡單嘛！用 SUBTOTAL 把顯示的列內容送給
COUNTIF()判斷！抱歉，失敗了。 - 原因是
SUBTOTAL送回的不是這些顯示列的「內容」，而是顯示的「列數」，只有一個數字「23」給COUNTIF，它從 23 找不到如何判斷哪一列要留下來的條件，就出錯。很多傳統 Excel 函數只會送回簡單內容，而不是送回整張表。 - 你可以試試看，如果要辨識用滑鼠來做漏斗篩選的結果再加上第二個條件篩選，要寫超複雜的公式，後來我就放棄了，乾脆複製一張算了啊！這麼麻煩，殺雞用牛刀！
- 但 QUERY 只需要打一行字就可以產出新表，因為它傳回整張表，用
COUNTA也可以計算列數！
-
我想要用 QUERY FILTER 但不要一張新表
- 傳統函數的好處是快速給你算完的數字，但如果你要的不只是數字，而是要把結果再做下一輪判斷，它就不管用了，此時 FILTER、QUERY 這種給你一張篩選完新表的函數就很好用。
- 加入需要傳統函數辦不到的複雜篩選，但又不想要一張新表怎麼辦？例如，我只想在原來頁面頂端顯示「參加人數」。
- 只要一個數字，不想打出 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 」又從「會議清單」 QUERY 一次，沒有問題。
- 這告訴我們，雖然不能直接去產出表修改內容，但可以連鎖 QUERY，所以可以把一切都放在總表，用 QUERY 做出一張張表連鎖使用，總表一改每張表都跟著改，可以簡單自動化。
-
比如可以這麼玩（不負責任隨便說）：用試算表抓 Spotify 新歌清單（用 API），依照音樂風格放進不同清單，從爵士樂清單抓出不同風格爵士分表，把分表做成很多個 YouTube 播放清單（用 API），再產出影片賺廣告費。
-
-
-
後記
- 試算表函數有點像程式語言，但少能像程式語言「返回」（Return）大量內容，就可對返回後的內容做下一步處理。
- 我想受限於 MS Excel 的限制， Google 試算表要讓每個功能跟 Excel 一模一樣，而 Excel 怕修改了用戶不會用，很多程式幾十年不動（例如它居然沒有「刪除列」功能），讓其它業者也只能跟著老舊。
- 如果客戶（主管）提出很瞎又很複雜的要求時怎麼辦？還好有 Query！