編輯嚴選
資料透視表,快速彙整資料的好幫手!

2022/06/19閱讀時間約 7 分鐘
這就是資料透視表!
資料透視表是 Google 試算表裡面很常用上的好功能,跟 Excel 的樞紐分析表很相似,可以把大資料彙整成一張統計表,讓你可以快速了解大資料的資訊!
今天想要把這個讓你有機會準時下班、有機會快速終結報告的好功能,介紹給你 (「・ω・)「

資料透視表(Pivot Table)是什麼?

簡單來說,就是個讓你透視資料的好幫手
我們通常用資料透視表來透視很大的資料,知道資料的聚集統計(像是平均值、總和、最大值、最小值、儲存格個數等等),又或是看看資料點與別的資料點之間的關係。做好資料透視表之後,也可以加上篩選器、過濾掉不想看到的項目,又或是再詳細查看計算內容、也可以再分類資料。實務上,我們也很常用資料透視表得出的資料製作圖表。

優點

  • 可以快速彙整資料、取得資訊。
  • 免寫函式,滑鼠點點,資訊就來!

缺點

  • 有大量資料透視表在同一個工作表上可能會讓 Google 試算表變慢。這時可以考慮用 QUERY 取代。如果不知道 QUERY 是什麼,歡迎到我過去的教學唷!
那我們馬上來看看怎麼做吧 (●´ω`●)ゞ

製作步驟與功能探索

這次我拿了內政資料開放平台上,一個叫「全國各鄉鎮市區前二十大姓人口數(按性別分)」的資料,還滿酷的!這邊有我稍稍整理好的版本,歡迎打開來、複製一份到你的雲端硬碟,一起練習看看吧。把檔案打開來,會看到:
A 欄是全台灣的直轄市和縣、B 欄是鄉鎮市區、C 欄是姓氏、D 欄是性別、最後是 E 欄的人口。
那我們來出三道題目好了:
  1. 在全台灣裡面,前十大的姓氏的有哪些?
  2. 按性別分,總人口又是怎麼樣?
  3. 只看臺南市的話,前十大的姓氏又有哪些?
來看看資料透視表可以怎麼幫忙我們達成任務!

零、從頭開始吧

首先點試算表左上角的全選按鈕(截圖中的橘色區域)、或是按快捷鍵 Ctrl/Command + A,把所有的資料選取起來:
再來去工具列,找到「插入」>> 「資料透視表」,安心按下去:
之後會跳出一個視窗,詢問要在新的、或是現有的資料表建立資料透視表:
如果點選「新的工作表」,試算表就會新增一個新的工作表:
如果選了「現有工作表」的話,就可以指定表格要出現的位置:
再點選「田」字的圖標,就可以選取目標位置:
那我們這次就在「新的工作表」建立資料分析表吧!

一、編輯器

建立之後,會看到右手邊的「資料透視表編輯器」:
這邊的編輯器就是我們編輯資料透視表的主要介面,關於資料透視表的大部分操作都會在這邊完成!可以看到 Google 試算表已經依照資料特性、給出一些建議使用的方案,點下去就可以直接生成相對應的資料透視表了。
編輯器有「列」、「欄」、「值」還有「篩選器」,右手邊有新增的按鈕可以按。「列」就是你想讓資料透視表出現的「列」(資料會往下長)、「欄」就是「直欄」(資料會往右長)、值就是資料中想要計算、可計量的「值」!而篩選器可以把資料項目篩選掉,只留下想要看到的資料。

二、全台灣前十大姓氏

我們首先來看看第一題:
在全台灣裡面,前十大的姓氏的有哪些?
我們好像可以試著用姓氏、人口來取得這個資訊,只要得到各姓氏的總人口數後排序、取得前十名就可以了。
來試著在資料透視表新增這些資訊吧!我們先在「列」那邊按下新增、點選「姓氏」:
新增後,會看到編輯器長這樣:
也會看到左邊的試算表起了點變化:
那麼,我們要計算的是總人口,所以在「值」這邊新增「人口」:
新增後,編輯器就會像這樣:
左邊的試算表則是:
如果你顯示的畫面不像上面的樣子,可以回頭看到「值」那邊的編輯器畫面,確定你的「匯總依據」是顯示「SUM」。
如果你把匯總依據的下拉式選單打開來,會看到有不同的選項選擇。如果你有不同的匯總需求,可以來看看這些是什麼:
  • SUM:值的總和
  • COUNTA:列或欄裡儲存格的個數
  • COUNT:列或欄裡儲存格的個數(且儲存格只包含數字)
  • COUNTUNIQUE:列或欄裡有唯一儲存格的種類數
  • AVERAGE:值的平均值
  • MAX:值的最大值
  • MIN:值的最小值
  • MEDIAN:值的中位數
  • PRODUCT:值相乘數
  • STDEV:計算值的樣本標準差
  • STDEVP:計算值的總體標準差
  • VAR:計算值的樣本變異數
  • VARP:計算值的總體標準差
回到資料編輯器,我們來對現在的值進行排序吧!假如想要看到前十名的姓氏出現在表的前十列,那就回到「列」、按下「排序」選取「遞減」、再去「排序依據」的下拉式選單,選取「人口的 SUM」(人口的總和):
我們來看看左邊的試算表,結果是:
結果就出來啦!就是這麼簡單~
新增列、欄、值和等等要介紹的篩選器的方法還有一個,那就是從最右邊的項目直接拖曳到列、欄、值、篩選器的地方:
如果要刪除列、欄、值或篩選器,可以點選項目右上角的 X 關掉:

三、按性別分,總人口又是怎麼樣?

如果我們要以現在的資料,再用「性別」這個角度切入的話,又要怎麼辦呢?很簡單,在「欄」加上性別就可以了!跟著我們前一個步驟,在「欄」這邊,新增「性別」:
左邊的試算表結果就出來了!

四、只看南市的話,前十大的姓氏又有哪些?

如果只想看到特定條件篩選下的資訊,我們可以用編輯器最下方的篩選器即可!我們在篩選器那邊新增「直轄市/縣」,會出現這樣:
我們把下拉式選單打開,可以看到現在全部的直轄市和縣都被選取起來了:
可是這不是我們要的結果,我們只想看到「臺南市」。這樣的話,我們可以先按「清除」,再到清單裡面勾選「臺南市」:
點選清除後,清單上的勾勾會消失。
清單往下滑,會找到「臺南市」,選取後「確定」大力按下去!
來看看執行結果如何:
做出來囉!如果還想看到某個統計裡、更詳細的資訊,你可以在值的儲存格上點兩下。例如,我想知道在臺南市裡,姓陳的女性的詳細統計狀況,我就可以在 B3 上面點兩下,會有個新表冒出來:
這邊就會看到原本資料來源的所有欄位、已經篩選好的資料統計狀況,你就可以做更進一步的分析囉 ヾ(´ε`ヾ)

之後要再用這個表製作圖表、套用條件式格式、或是放到簡報上面都不錯,讓你免寫函式就可以做出專業的報表,方便又快速。另外,資料透視表還有一些其他的功能也很實用,例如顯示值的百分比、多欄、多列、計算結果單位等等,如果有興趣的話,也歡迎詢問唷!
如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!
想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!
我是喜特先生,Mr. Sheet,我們下個教學見!
3.3K會員
122內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
留言0
查看全部
發表第一個留言支持創作者!