Excel 大補帖

【Excel 進階】Google Sheets 也能樞紐分析！

三大章節分秒鐘帶你理解資料透視表，怎麼用都好用😍

Excel 進階資料分析、樞紐分析、資料透視

2022 年多數科技公司減去既有 Excel 操作，轉往線上服務 Google SpreadSheet（試算表），只要有網路就可以無痛分析資料。但是，有些同學會問：「Google 是不是忘記帶上某些功能？」例如，樞紐分析表⋯⋯

仔細看你會發現有組功能被稱為「資料透視表」，實際應用情境和步驟基本與 Excel 樞紐分析表並無二致。這篇單元專欄希望帶你認識這項核心工具外，也期待你帶走更多 Excel 快捷操作、函數應用的撇步，一起來學習吧！

本文目錄
1. 快速上手介面 Intro
2. 聚焦分析資料 Analysis
3. 實戰演習訓練 Exercises
4. 資料透視結語 Conclusion
5. 延伸閱讀

▍快速上手介面 Intro

這一節帶你 1 分鐘為資料建立一組資料透視表，只需要簡單三步驟，一起往下看看吧 🌝

• 打開資料透視表

上方工具列 → 插入 → 資料透視表

• 在新的工作表插入資料透視表

資料範圍可透過「田」字窗格模式或是參照函數選取

• 見畫面如下即完成建置步驟

大功告成，恭喜你建立第一組資料透視表！

▍聚焦分析資料 Analysis

理解直行橫列的基本資料表格觀念後，可以彈性增減我們想知道的資訊，並善用篩選功能 Highlight 可能對後續推行商業決策有利的資料範圍 😎

設定資料透視表編輯器

在此可以透過「新增」按鈕或拖曳區塊的方式客製化分析報表

• 列

舉例來說，當我們想看 2017–09–10 — 2018–08–31 的超市交易紀錄下，各別品項的銷售狀況時，我們會將代表品項名稱的「item_name」放在資料透視表的列。

A1 代表「表頭 item_name」；A2:A 為品項的 unique 後資訊

• 欄

這裡的欄用英文 column 理解較為合適，意思就是「直行」。在 Google Sheet 的資料透視表中，欄有顯示數量限制因此有時可能需要配搭篩選器才能使用。在此次的教學資料集中，並不會用到欄。

因為 item_name 項目高達 800 多，受限於試算表限制無法完全展開

在 Microsoft Excel 中並無欄數的限制問題，使用上相對彈性

• 值

單獨呈現值並無太大統計分析上的意義，因此我們需要設定列或欄，藉此從交易紀錄中獲得各式各樣的 insights。

舉例來說，我們希望看到 2017–09–10–2018–08–31 超市交易紀錄下，各品項的交易數量，便可以將列設定為 item_name；把代表每筆訂單的交易數量 transaction_amount 放入值。

可以得知商品「Alize Sunset」在這段期間共售出 9 組

• 篩選器

有了基本的資料透視表還不夠，因為單純列出資料表格仍很難從中觀察出商業活動的趨勢或是做出銷售決策。

這時我們可以善用篩選器的功能。例如，我們在 2017 年 9 月 10 日啟動了某一檔行銷活動，走期為一週。借助篩選器的力量可以從龐雜的資料集中聚焦特定範圍的資料。

Halibut 銷量與 Table Cloth 相同但獲利能力更佳，在行銷預算不變下，我們可以嘗試高強度推廣前者以期更好的銷售表現

點我訂閱赴日工作、海外留學、Excel 攻略專欄

▍實戰演習訓練 Exercises

根據情境可以使用資料透視進行初步的資料探索，但有時碰到有外部使用者的操作需求時，可搭配 Query 函數打造一組直觀、可互動、降低表格損壞風險的數據儀表板（Data Dashboard）。

對於大多數的使用者來說，Google SpreadSheet 的資料透視表或是 Microsoft Excel 的樞紐分析表已經能達成超過 75% Data Dashboard 的查看功能。

如果想要讓外部使用者（像是你的老闆）更直觀操作的話，依賴編輯器可能又稍顯複雜⋯⋯這時，你可以嘗試使用一些函數或是工具打造更個性化的儀表板！

Query 函數｜在 Google 試算表上寫下你的第一句 SQL 敘述吧！

全稱 Structured Query Language 的 SQL 可以想像成餐廳的服務生，當我們告訴它一些要求後，它會嘗試理解我們的需求並向廚房索取指定的料理；這整個過程就是從資料庫查詢資料。

當我們面對 Google 試算表的資料時，可以利用Query函數完成資料查詢，實際怎麼運用就一起往下閱讀吧 👍

Google SpreadSheet Function
=QUERY(資料範圍, 查詢敘述, 資料包含幾列標題（選填）)

• 參照資料範圍

通常數據儀表板會另開新工作表，因此會使用到「參照其他工作表資料範圍」的語法：

E.g.
'super_market_transaction_record_091017-083118'!A2:F1001
意即，我想從 2017-09-10 - 2018-08-31 超市交易紀錄的 A2 至 F1001 資料格中進行查詢。

• 輸入查詢敘述

E.g.
"SELECT C, D, F 
WHERE B >= DATE'"&TEXT(DATEVALUE(B1),"YYYY-MM-DD")&"' AND 
B <= DATE'"&TEXT(DATEVALUE(D1),"YYYY-MM-DD")&"' 
ORDER BY F DESC"
解讀時可以由下往上閱讀敘述：
* 按照 F 進行遞減排序
* 滿足條件 1：B 需要大於 B1 資料格的日期
* 且滿足條件 2：B 需要小於 D1 資料格的日期
* 選擇顯示 C、D、F 行

使用者可以輕鬆透過 B1 和 D1 的日期驗證進行時間區段調整

點兩下跳出日曆選擇：上方工具列 → 資料 → 資料驗證 → 條件 → 日期

• 選填標題列數

沒有特別需求完全可以不理會這個參數；預設為 -1，會由系統幫你找到標題列數。

▍結語 Conclusion

本篇文章協助你了解更多 Excel 函數和 Google SpreadSheet 的操作，讓我們再複習一次：

1. 面對複雜資料集可以使用「資料透視表 / 樞紐分析表」進行資料探索
2. 善用列、欄、值、篩選器進行資料分析，隨時可以利用拖曳或是按鈕增減
3. 若希望建置一組可互動的儀表板，可以嘗試結合 Query 函數

如果你想知道更多關於資料透視表的資訊可以利用「Pivot Table」關鍵字學習；另外補充的 Query 函數涉及到資料庫程式語言，也可以先在程式語言學習網站 Data Camp 上觀看免費入門影片，相信會對資料處理更有概念！

▍本篇講義雲端連結：https://drive.google.com/drive/folders/1sMdPMv9yKv_6zVOuCKFFIxS149Pte0Wo?usp=sharing

▍任何好奇歡迎聯繫：damien.cl.yu@gmail.com

▍延伸閱讀

• 【Excel 宗師】用 VBA 寫支機器人🤖
• 【Excel 基礎】快捷鍵 + 基本函數實作看這邊👀
• 【Excel 入門】認識何謂試算表軟體