[數據分析#35] Google Sheet Query 9 語法大全及練習檔案

為什麼我超推薦學習 Google Sheet Query Function (函數)?

  1. 學習 Query,可以減少公式的行數,省時又方便
    • QUERY 可以完全取代 UNIQUE 函數 + SUMIF/COUNTIF 函數
    • QUERY 甚至也可以完全取代 Pivot Table
  2. Google Sheet Query 是 SQL 的入門版,學會 Query,SQL 就先會一半
    • 如果可以在 Google Sheet 裡面就練習 SQL 的思維方式,對於資料處理會有很大的進階
  3. 報表的公式有修改,就要重新下拉,超麻煩!有 Google Sheet Query 後,不用再下拉公式
    • 如果你認識 Google Sheet Query 或是 Google Sheet 裡面的 Arrayformula,他可以完全改變你對 Google Sheet 的想法!如果你也有遇到報表的數字新增,公式就要不斷下拉,那 Query 可以協助不用再來下拉公式

什麼是 Google Sheet Query Function (函數)?

  • 可以引用資料來源
  • 可以 aggregate data, rearrange data, create new data, filter data, sort data
  • Query 裡面包含 10 幾種函數可以寫,就像在 Google Sheet 裡面寫 SQL 一樣,更方便了

Google Sheet Query 有哪些函數可以寫?

Google Sheet Query 公式的組成

  1. 資料範圍:在下面的範例以 data_range 顯示,以 ‘ ‘ 單引號選取資料範圍
  2. 接著就是下 Query 的語法:在下面的範例以 google_sheet_query 顯示,以 “ “ 雙引號將公式包起來
= QUERY('data_range'!A:A, "google_sheet_query")

Google Sheet Query 支援哪些資料格式?

  1. String 文字格式:以 ‘ ‘ 單引號的方式顯示為文字
  2. Numbers 數字格式:直接輸入數值就可以了
  3. Time/Date 時間或是日期格式:可以將時間轉為數值,或是以 date’2020-01-01′ 或是 TEXT(N12,”yyyy-mm-dd”) 的形式處理

如果想要看更多實例轉換,可以參考:Google Sheet Query Where 條件教學

4 大基本用法:SELECT, WHERE, GROUP BY, ORDER BY

  1. SELECT
  • select A,E:直接將要選取的欄位打上來
=query('RAW Data'!A:P,"select A,E")
  • select *:選取資料範圍中,全部的欄位
=query('RAW Data'!A:P,"select *")
  1. WHERE
  • select A,E Where E <= 20:選取 A, E 兩欄,並且當 E 欄 ≤ 20,如果你需要的 Where 條件很複雜,可以到 Google Sheet Query Where 條件教學 這邊看更多 Where 條件設置的進階用法
=query('RAW Data'!A:P,"select A,E Where E <= 20")
  1. GROUP BY
  • 只要是你想要做敘述性統計,例如+-×÷,或是平均 avg()、最小 min()、最大 max(),最後都要用 GROUP BY 這個函數做「聚合」,需要聚合的資料就是除了敘述性統計以外的其他行
  • 以下的範例:我選取 B, C, K 這三行,然後把 L 加總,把 P 平均,所以要對 B, C, K 這三行進行聚合,也就是 GROUP BY
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K")
  1. ORDER BY
  • ORDER BY 就是排序,看我們想要依照哪一行做排列
  • ASC 就是從小到大,DESC 就是從大到小
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K order by sum(L) asc")
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K order by sum(L) desc")

樞紐分析:Pivot (資料透視表)

  • 如果想要在 Google Sheet 裡面做樞紐分析表,Query 也可以做到:樞紐分析會分成三個部分:列、欄,還有樞紐裡面的值
  • 舉例:如果樞紐分析裡的列是 C 欄,欄是 G 欄,樞紐分析的值是 L (加總),那 Query 裡的樞紐分析就是:SELECT C,sum(L) GROUP BY C PIVOT G
=QUERY('RAW Data'!A:P,"select C,sum(L) GROUP BY C PIVOT G")

回傳限制:LIMIT、OFFSET

  1. LIMIT
  • LIMIT 是用來控制資料的行數,如果只想回傳 10 行,可以 LIMIT 10
=QUERY('RAW Data'!A:P,"select B, C, K, sum(L), avg(P) group by B, C, K order by sum(L) desc limit 10")
  1. OFFSET
  • 跳過資料範圍裡的 N 行之後,再執行 Google Sheet Query 函數,所以如果我寫 OFFSET 10,那就會跳過除了表頭以外的 10 行,然後回傳。所以前 10 行的值都不會顯示
=QUERY('RAW Data'!A:P,"select * Offset 10")

調整格式:LABEL, FORMAT

  1. LABEL
  • LABEL 可以幫 Query 後的結果,重新取表頭名稱,如果你的 RAW DATA 是英文表頭,那 QUERY 輸出之後,可以換成中文表頭,以方便閱讀囉!
=QUERY('RAW Data'!A:P,"select A, B label A '姓名', B '國家'")
  1. FORMAT
  • FORMAT 可以用來調整 Query 之後的結果,是以怎麼樣的「格式」呈現
  • FORMAT 最常用來格式化日期、時間、數值這類的呈現格式
    • 日期:FORMAT C ‘DD-MM-YYYY’,或是: FORMAT C ‘YYYY’、FORMAT C ‘MM’、FORMAT C ‘DD’
    • 時間:FORMAT G ‘HH’,或是 FORMAT G ‘MM:SS’
    • 數值:FORMAT E ‘##,##0.00’
=query('RAW Data'!A:P,"select K format K 'dd-mm-yyyy'")
=query('RAW Data'!A:P,"select K format K 'HH:MM:SS'")
=query('RAW Data'!A:P,"select L format L '##,##0.00'")

Google Sheet Query 函數先後順序

  • 如果你也有疑問,那哪些函數要先寫,哪些要後寫?以下是 Google Sheet Query 函數在寫的時候的先後順序:SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL, FORMAT
  • 排序必須照著這個寫!以免函數不能運轉喔!
  • 這個順序主要是數據在整理跟運算的時候的先後處理順序,這裡來解釋一下數據處理的順序:選取資料 (SELECT)、篩選條件 (WHERE)、聚合 (GROUP BY)、樞紐 (PIVOT),接著排列結果的順序 (ORDER BY)、只回傳某幾行或是從某幾行開始才回傳 (LIMIT & OFFSET),接著格式化表頭及格式化值 (LABEL & FORMAT)

總結

Google Sheet Query 整體的用法就分為這 9 個函數語法 (9 useful google sheet query functions),我覺得最值得深學的就是 WHERE 這個語法,你可以到 Google Sheet Query Where 條件教學 這邊,看更多教學

如何練習 Google Sheet Query 函數?

  1. 複製一份我的 Google Sheet 教學文件
  2. 在我整理好的幾個工作表中,練習這 9 個語法

更多學習資源推薦

如何使用這個網站?我的數據分析文章導覽!裡面也會分享更多實用 Google Sheet 教學:本站數據分析文章導覽 及 更多 Google Sheet 實用文章教學

【1 對 1 免費諮詢】數據分析師 0~1 培訓班陪你一路到轉職成功!

hi 我是 Lisa

你也是 0 經驗、沒有相關背景,但也想要轉職數據分析師,或運用數據分析,提升在領域的專業度嗎?

-

你是否也覺得,數據分析的網路學習資源很多,但是多到不知道從哪裡開始?

學了很多工具,但學完了,問題才真正開始,不知道業界怎麼使用?又該如何面臨工作跟面試?

-

我在 2019 年從營運專員的職位轉職到數據分析師

在這之後,因為數據分析,也讓我快速建構不同領域的知識:行銷、營運、商業策略、產品、網頁

轉職過程中,花了很多時間成本摸索

我發現,學數據分析最快的方式是:

  1. 確認自己跟市場需求之間的差距:訂定轉職策略及了解數據分析師的工作方式
  2. 以業界應用的角度學習工具:包括數據工具及分析用思維架構,並且如何高效運用
  3. 建立一個商業上的真實專案,並且得到回饋:打造屬於你及貼合職缺需求的作品集和專案
  4. 打造一個高效履歷跟面談:透過高價值包裝術拿下面試、成功獲得數據行業的門票

當初如果我有這套系統思維,應該可以在短時間內轉職,馬上拿到進入業界的門票

-

現在,我想把這套完整課程分享給你

這是專門為了 0 經驗、沒有相關背景的轉職者所設計

不需要去考研究所、不需要花大錢準備出國讀書

可以用最快的時間準備轉職、拿到門票!

-

如果你正在準備轉職數據分析師,且試圖找過解決辦法卻始終治標不治本!歡迎跟我預約免費 1 對 1 諮詢,我會在諮詢時,了解你的需求並且評估我是否可以協助到你!

電子書 職場人必學的數據分析術

Lisa Wu

Hi 大家好!我是喜歡探索身心靈健康及平衡的數據分析師 Lisa Wu!這個網站會幫助你:利用數據分析 (理性) 及 身心靈平衡 (感性) 的系統,去達成人生目標!如果你對我的人生系統工具有興趣,歡迎聯繫我:couplehonest@gmail.com

This Post Has 3 Comments

Comments are closed.