QUERY 函式大解析（十四）：子查詢①篩選聚集結果

這邊有一張表格，有員工的姓名、部門、年齡、薪水、公司的資料：

在這個表格裡，我想找「薪水在高於平均值的員工」有哪些，可以怎麼找呢？或許你有閃過這樣的寫法：

=QUERY(A:E, "SELECT * WHERE D > AVG(D)")

但是這樣會回傳錯誤，因為 AVG(D) 這樣的聚集函數沒辦法出現在 WHERE 語句裡面。

那還可以怎麼寫呢？如果是在 SQL 上，你可以用 HAVING 來完成，用它對聚集函數的結果進行篩選。可惜 QUERY 沒有 HAVING 這個方法，那還可以怎麼解決呢？

我想要介紹一個新東西，叫做子查詢（Subquery）。

子查詢（Subquery）

以 Google 試算表的 QUERY 角度來看，子查詢就是 QUERY 裡面再包著一個或多個 QUERY。我們通常會把開頭的等號（=）後的 QUERY 叫做主查詢，在 QUERY 裡面存在的 QUERY 叫做子查詢。

我們可以用子查詢來篩選、計算聚集（加總、平均、數量等）、從其他表格查詢資料來幫助現在的 QUERY，解鎖更多查詢的可能性！

在 QUERY，我們通常會把子查詢放在兩個地方。

• 在 WHERE 中使用（第四行～第五行）：

=QUERY(資料,
       "SELECT ...
        WHERE A > "
      & QUERY(資料,
              "QUERY 語句​") & 
       "")      

當你想要以子查詢的結果作為 WHERE 的條件，就用這個！

要注意這種子查詢會用到 & 來連結，讓 WHERE 跟第二個 QUERY 的結果接在一起。你可以回到這邊複習一下 & 的用法：QUERY 函式大解析（九）：與儲存格連動。

• 當作主查詢的表格使用（第二行～第五行）：

=QUERY(
    QUERY(A:D,
         "SELECT ...
          WHERE ...
          ..."),
  "SELECT Col1, Col2, Col3 ...
   WHERE ...
   ...")  

當你想要先用子查詢做一個表格給主查詢用，就用這個！

這種子查詢則是不需要用 &，但是主查詢的時候要用 Col# 取代欄位字母，像是第一欄就是 Col1、第二欄就是 Col2、第三欄就是 Col3，以此類推。

不過這邊提到的使用時機只是一個參考，你可以看看自己的需求，再衡量要用哪一個方法來做子查詢。卡住的話也歡迎問喜特先生喔✨

對聚集結果篩選

好，回到一開始提到的題目。歡迎來這邊複製一份試算表，一起來練習！

在這個表格裡，我想找「薪水在高於平均值的員工」有哪些：

這邊我想用第一個方法（把子查詢放在 WHERE 後面）比較符合我的需求。我目前想到的是：

=QUERY('員工資料'!A:E,
        "SELECT * 
         WHERE D >= 平均值")

（寫 QUERY 的時候，可以善用 Ctrl/Cmd + Enter 來換行喔）

這樣應該就可以得到答案了。不過這邊的「平均值」要怎麼求？我們可以先在「子查詢」的工作表，用 QUERY 求平均值，也就是：

=QUERY('員工資料'!A:E,
        "SELECT AVG(D)")

來看看結果：

對，雖然得到數字了，不過第一列有個標題列，如果直接拿這個結果去跟主查詢接在一起的話，會產生錯誤，因為主查詢的 QUERY 會變成這樣：

=QUERY('員工資料'!A:E,
        "SELECT * 
         WHERE D >= avg 薪水 65918.36735")

「avg 薪水 65918.36735」，文字跟數字混在一起了。

那怎麼把標題列拿掉？很簡單，我們可以再用 LABEL 語句，強制把「avg 薪水」變成空白。回到我們在工作表上寫的子查詢 QUERY，稍稍更改一下：

=QUERY('員工資料'!A:E,
        "SELECT AVG(D)
         LABEL AVG(D) ''"

讚，標題列不見啦！

再來把它跟主查詢用 & 接起來就可以了。

=QUERY('員工資料'!A:E,
        "SELECT * 
         WHERE D >= "
         & QUERY('員工資料'!A:E,
                   "SELECT AVG(D)
                    LABEL AVG(D) ''")
         & "")

當然，你也可以繼續編輯主查詢。

比如說，我做了一個簡易的資料透視表，找到各個公司裡，各個部門超過平均值薪水的員工個數：

=QUERY('員工資料'!A:E,
        "SELECT B, COUNT(A)
         WHERE D >= "
         & QUERY('員工資料'!A:E,
                   "SELECT AVG(D)
                    LABEL AVG(D) ''")
         & "GROUP BY B
            PIVOT E")

替代方案

如果你覺得子查詢有點複雜不好用，你也可以先在別的儲存格，先用 AVERAGE 函式算 D 欄的平均值是多少，然後再用 & 連接起來也是一招。我在「替代方案」工作表也做了一個範例，供你參考：

如果你喜歡這次的文章，歡迎你透過這些方法支持我：

• 按下愛心、按下儲存
• 留言告訴我你的想法
• 加入喜特先生的官方沙龍，即時看到我發布的教學
• 付費訂閱喜特先生的官方沙龍，加入每月小額訂閱方案
• 追蹤喜特先生的 Facebook
• 按這邊小額贊助我的創作！

想要看更多文章的話，歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔！

我是喜特先生，Mr. Sheet，我們下個教學見！