方格精選

資料透視表,快速彙整資料的好幫手!

更新於 發佈於 閱讀時間約 7 分鐘
raw-image

資料透視表是 Google 試算表裡面很常用上的好功能,跟 Excel 的樞紐分析表很相似,可以把大資料彙整成一張統計表,讓你可以快速了解大資料的資訊!

今天想要把這個讓你有機會準時下班、有機會快速終結報告的好功能,介紹給你 (「・ω・)「


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

簡單來說,就是個讓你透視資料的好幫手

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

優點

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

缺點

  • 有大量資料透視表在同一個工作表上可能會讓 Google 試算表變慢。這時可以考慮用 QUERY 取代。如果不知道 QUERY 是什麼,歡迎到我過去的教學唷!

那我們馬上來看看怎麼做吧 (●´ω`●)ゞ


製作步驟與功能探索

這次我拿了內政資料開放平台上,一個叫「全國各鄉鎮市區前二十大姓人口數(按性別分)」的資料,還滿酷的!這邊有我稍稍整理好的版本,歡迎打開來、複製一份到你的雲端硬碟,一起練習看看吧。把檔案打開來,會看到:

raw-image

A 欄是全台灣的直轄市和縣、B 欄是鄉鎮市區、C 欄是姓氏、D 欄是性別、最後是 E 欄的人口。

那我們來出三道題目好了:

  1. 在全台灣裡面,前十大的姓氏的有哪些?
  2. 按性別分,總人口又是怎麼樣?
  3. 只看臺南市的話,前十大的姓氏又有哪些?

來看看資料透視表可以怎麼幫忙我們達成任務!


零、從頭開始吧

首先點試算表左上角的全選按鈕(截圖中的橘色區域)、或是按快捷鍵 Ctrl/Command + A,把所有的資料選取起來:

raw-image

再來去工具列,找到「插入」>> 「資料透視表」,安心按下去:

raw-image

之後會跳出一個視窗,詢問要在新的、或是現有的資料表建立資料透視表:

raw-image

如果點選「新的工作表」,試算表就會新增一個新的工作表:

raw-image

如果選了「現有工作表」的話,就可以指定表格要出現的位置:

raw-image

再點選「田」字的圖標,就可以選取目標位置:

raw-image

那我們這次就在「新的工作表」建立資料分析表吧!


一、編輯器

建立之後,會看到右手邊的「資料透視表編輯器」:

raw-image

這邊的編輯器就是我們編輯資料透視表的主要介面,關於資料透視表的大部分操作都會在這邊完成!可以看到 Google 試算表已經依照資料特性、給出一些建議使用的方案,點下去就可以直接生成相對應的資料透視表了。

編輯器有「列」、「欄」、「值」還有「篩選器」,右手邊有新增的按鈕可以按。「列」就是你想讓資料透視表出現的「列」(資料會往下長)、「欄」就是「直欄」(資料會往右長)、值就是資料中想要計算、可計量的「值」!而篩選器可以把資料項目篩選掉,只留下想要看到的資料。


二、全台灣前十大姓氏

我們首先來看看第一題:

在全台灣裡面,前十大的姓氏的有哪些?

我們好像可以試著用姓氏、人口來取得這個資訊,只要得到各姓氏的總人口數後排序、取得前十名就可以了。

來試著在資料透視表新增這些資訊吧!我們先在「列」那邊按下新增、點選「姓氏」:

raw-image

新增後,會看到編輯器長這樣:

raw-image

也會看到左邊的試算表起了點變化:

raw-image

那麼,我們要計算的是總人口,所以在「值」這邊新增「人口」:

raw-image

新增後,編輯器就會像這樣:

raw-image

左邊的試算表則是:

raw-image

如果你顯示的畫面不像上面的樣子,可以回頭看到「值」那邊的編輯器畫面,確定你的「匯總依據」是顯示「SUM」。

如果你把匯總依據的下拉式選單打開來,會看到有不同的選項選擇。如果你有不同的匯總需求,可以來看看這些是什麼:

  • SUM:值的總和
  • COUNTA:列或欄裡儲存格的個數
  • COUNT:列或欄裡儲存格的個數(且儲存格只包含數字)
  • COUNTUNIQUE:列或欄裡有唯一儲存格的種類數
  • AVERAGE:值的平均值
  • MAX:值的最大值
  • MIN:值的最小值
  • MEDIAN:值的中位數
  • PRODUCT:值相乘數
  • STDEV:計算值的樣本標準差
  • STDEVP:計算值的總體標準差
  • VAR:計算值的樣本變異數
  • VARP:計算值的總體標準差


回到資料編輯器,我們來對現在的值進行排序吧!假如想要看到前十名的姓氏出現在表的前十列,那就回到「列」、按下「排序」選取「遞減」、再去「排序依據」的下拉式選單,選取「人口的 SUM」(人口的總和):

raw-image

我們來看看左邊的試算表,結果是:

raw-image

結果就出來啦!就是這麼簡單~

新增列、欄、值和等等要介紹的篩選器的方法還有一個,那就是從最右邊的項目直接拖曳到列、欄、值、篩選器的地方:

raw-image

如果要刪除列、欄、值或篩選器,可以點選項目右上角的 X 關掉:

raw-image


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

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

raw-image

左邊的試算表結果就出來了!

raw-image


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

如果只想看到特定條件篩選下的資訊,我們可以用編輯器最下方的篩選器即可!我們在篩選器那邊新增「直轄市/縣」,會出現這樣:

raw-image

我們把下拉式選單打開,可以看到現在全部的直轄市和縣都被選取起來了:

raw-image

可是這不是我們要的結果,我們只想看到「臺南市」。這樣的話,我們可以先按「清除」,再到清單裡面勾選「臺南市」:

raw-image
raw-image

來看看執行結果如何:

raw-image

做出來囉!如果還想看到某個統計裡、更詳細的資訊,你可以在值的儲存格上點兩下。例如,我想知道在臺南市裡,姓陳的女性的詳細統計狀況,我就可以在 B3 上面點兩下,會有個新表冒出來:

raw-image

這邊就會看到原本資料來源的所有欄位、已經篩選好的資料統計狀況,你就可以做更進一步的分析囉 ヾ(´ε`ヾ)



之後要再用這個表製作圖表、套用條件式格式、或是放到簡報上面都不錯,讓你免寫函式就可以做出專業的報表,方便又快速。另外,資料透視表還有一些其他的功能也很實用,例如顯示值的百分比、多欄、多列、計算結果單位等等,如果有興趣的話,也歡迎詢問唷!

如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!

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

我是喜特先生,Mr. Sheet,我們下個教學見!



留言
avatar-img
留言分享你的想法!
喜特先生 Mr. Sheet -avatar-img
發文者
2024/03/19
問了 Gemini 知不知道喜特先生的結果⋯⋯提及了這篇文章,趕快過去看看吧!
朱晨瑜-avatar-img
2023/08/27
你好,想請問一下,你是怎麼做可以選擇「姓氏」等分類類別呢?我也是用全選,但只能選擇欄位,謝謝你!
喜特先生 Mr. Sheet -avatar-img
發文者
2023/08/27
朱晨瑜 哈囉!謝謝你來看我的教學。我這邊做的只是把要放到資料透視表的列或是欄拖進去,透視表就會顯示項目了(可以看看回覆的 gif 圖)如果只能選擇欄位(我猜你的意思是「列」那邊沒辦法選?),或許可以重新設定看看選取範圍有沒有什麼問題,可以再打開「田」字圖示重新定義整個範圍就可以了。當然,如果還是有問題,歡迎回覆、附上截圖,我可以試著幫你解決看看~
avatar-img
喜特先生官方沙龍
19.2K會員
153內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
2024/05/25
這邊統整了過往喜特先生發布過的「資料驗證」系列文! 資料驗證是個「驗證資料是否符合某條件的機制」,我們通常會用它來避免別人輸入無效的值,減少錯誤的發生。你可以按照順序慢慢學習,把資料驗證這功能一次搞懂!
Thumbnail
2024/05/25
這邊統整了過往喜特先生發布過的「資料驗證」系列文! 資料驗證是個「驗證資料是否符合某條件的機制」,我們通常會用它來避免別人輸入無效的值,減少錯誤的發生。你可以按照順序慢慢學習,把資料驗證這功能一次搞懂!
Thumbnail
2024/03/22
已命名範圍是 Google 試算表的功能,簡單來說可以把儲存格參照範圍改成自己想要的名字,可以讓算式更好讀、減少維護時間、減少錯誤機率、在算式和可以用這個名字引用這個範圍。來看看怎麼做!
Thumbnail
2024/03/22
已命名範圍是 Google 試算表的功能,簡單來說可以把儲存格參照範圍改成自己想要的名字,可以讓算式更好讀、減少維護時間、減少錯誤機率、在算式和可以用這個名字引用這個範圍。來看看怎麼做!
Thumbnail
2023/11/07
一直以來都是喜特先生在教學,我們今天來換個角度,看看 Google 官方部落格是怎麼教的!我在那邊找到了一篇關於使用智慧型方塊的好範例,在這分享給大家。
Thumbnail
2023/11/07
一直以來都是喜特先生在教學,我們今天來換個角度,看看 Google 官方部落格是怎麼教的!我在那邊找到了一篇關於使用智慧型方塊的好範例,在這分享給大家。
Thumbnail
看更多
你可能也想看
Thumbnail
孩子寫功課時瞇眼?小心近視!這款喜光全光譜TIONE⁺光健康智慧檯燈,獲眼科院長推薦,網路好評不斷!全光譜LED、180cm大照明範圍、5段亮度及色溫調整、350度萬向旋轉,讓孩子學習更舒適、保護眼睛!
Thumbnail
孩子寫功課時瞇眼?小心近視!這款喜光全光譜TIONE⁺光健康智慧檯燈,獲眼科院長推薦,網路好評不斷!全光譜LED、180cm大照明範圍、5段亮度及色溫調整、350度萬向旋轉,讓孩子學習更舒適、保護眼睛!
Thumbnail
創作者營運專員/經理(Operations Specialist/Manager)將負責對平台成長及收入至關重要的 Partnership 夥伴創作者開發及營運。你將發揮對知識與內容變現、影響力變現的精準判斷力,找到你心中的潛力新星或有聲量的中大型創作者加入 vocus。
Thumbnail
創作者營運專員/經理(Operations Specialist/Manager)將負責對平台成長及收入至關重要的 Partnership 夥伴創作者開發及營運。你將發揮對知識與內容變現、影響力變現的精準判斷力,找到你心中的潛力新星或有聲量的中大型創作者加入 vocus。
Thumbnail
在職場上,Excel 是許多人必備的工具之一。它可以用來處理各種數據,包括統計、分析、報表等。在處理數據時,求和是常見的操作之一。然而,如果我們想要在篩選項目更改時,總和資料也隨機變動,該怎麼做呢?
Thumbnail
在職場上,Excel 是許多人必備的工具之一。它可以用來處理各種數據,包括統計、分析、報表等。在處理數據時,求和是常見的操作之一。然而,如果我們想要在篩選項目更改時,總和資料也隨機變動,該怎麼做呢?
Thumbnail
資料視覺化是資料分析的重要一環,能夠將抽象的資料透過圖表呈現出來,找到資料背後的規律及洞見。但對於許多人來說,製作專業水準的圖表仍然是一大難題。要製定適當的圖表類型,設計優雅大方的格調,需要耗費許多時間和精力。 但其實要解決資料視覺化的困擾,Excel提供了強大的製圖功能。用戶只需選擇好資料來源,
Thumbnail
資料視覺化是資料分析的重要一環,能夠將抽象的資料透過圖表呈現出來,找到資料背後的規律及洞見。但對於許多人來說,製作專業水準的圖表仍然是一大難題。要製定適當的圖表類型,設計優雅大方的格調,需要耗費許多時間和精力。 但其實要解決資料視覺化的困擾,Excel提供了強大的製圖功能。用戶只需選擇好資料來源,
Thumbnail
【方格子最新好物-下載“CSV”(EXCEL插入”建議圖表”的使用實例之一】 最近發現方格子主編很盡心地在作者自己的資料欄裡編寫文章的數據統計資料,因此,每天我都會研究一下自己的進展,個人覺得很是有趣。有了這個系統資訊之後,我才赫然發現只要默默經營網站,並持續創作不放棄,還是有許多可以學習成長的地方
Thumbnail
【方格子最新好物-下載“CSV”(EXCEL插入”建議圖表”的使用實例之一】 最近發現方格子主編很盡心地在作者自己的資料欄裡編寫文章的數據統計資料,因此,每天我都會研究一下自己的進展,個人覺得很是有趣。有了這個系統資訊之後,我才赫然發現只要默默經營網站,並持續創作不放棄,還是有許多可以學習成長的地方
Thumbnail
Google 試算表還有一個花括號 { } 的參照方法,可以參照範圍。學會的話,對處理大量資料有很多好處!我們來看看怎麼用 { } 來處理吃資料吧。
Thumbnail
Google 試算表還有一個花括號 { } 的參照方法,可以參照範圍。學會的話,對處理大量資料有很多好處!我們來看看怎麼用 { } 來處理吃資料吧。
Thumbnail
我花了一點時間,研究圖表功能,並做了21支教學影片,讓同為『圖表淪落人』的上班族,可以從基礎開始,修煉你的圖表超能力
Thumbnail
我花了一點時間,研究圖表功能,並做了21支教學影片,讓同為『圖表淪落人』的上班族,可以從基礎開始,修煉你的圖表超能力
Thumbnail
最近因為輸入資料節省時間跟正確性的需求, 去查了Excel的函數運算怎麼用, 花了大概10分鐘查資料看教學影片, 試了兩三次就成功了~ 現在來分享一下我這次學到的函數。 由於工作上的需求, 需要從一堆數值裡面篩選出範圍內的數量, 但因為數量有點大,我又有點懶惰(其實不只有點), 步驟一:輸入關鍵字
Thumbnail
最近因為輸入資料節省時間跟正確性的需求, 去查了Excel的函數運算怎麼用, 花了大概10分鐘查資料看教學影片, 試了兩三次就成功了~ 現在來分享一下我這次學到的函數。 由於工作上的需求, 需要從一堆數值裡面篩選出範圍內的數量, 但因為數量有點大,我又有點懶惰(其實不只有點), 步驟一:輸入關鍵字
Thumbnail
先對整體有個輪廓,會比細部指令來得重要喔!先見林,再見樹。
Thumbnail
先對整體有個輪廓,會比細部指令來得重要喔!先見林,再見樹。
Thumbnail
活用「探索(Explore)」功能,幫你省時省力、快速完成工作!
Thumbnail
活用「探索(Explore)」功能,幫你省時省力、快速完成工作!
Thumbnail
今天要介紹的是進階的 SELECT 功能,可以即時對 QUERY 的結果運算,迅速取得數值的平均、總和、最大值、最小值和數量,省去拉資料透視表(pivot table)的麻煩!
Thumbnail
今天要介紹的是進階的 SELECT 功能,可以即時對 QUERY 的結果運算,迅速取得數值的平均、總和、最大值、最小值和數量,省去拉資料透視表(pivot table)的麻煩!
追蹤感興趣的內容從 Google News 追蹤更多 vocus 的最新精選內容追蹤 Google News