Google 試算表樞紐分析表範例教學,像 Excel 般資料快速重整


樞紐分析表」是 Excel 裡一個快速分析資料的實用功能,而且號稱高手都一定要會用,他的名字聽起來很厲害,其實操作起來「很簡單」。因為「樞紐分析表」原本的目的就是要「化繁為簡」,解決大量龐雜的資料表格很難讀出重點的問題,幫你快速建立起「摘要」,簡單重新整理成有彈性的統計報表。

舉實際的例子來說明的話:

像是原本在試算表中建立一個花費帳目表,但是每一筆花費只是根據支出日期排列,就是一個流水帳,而裡面的支出人、消費類別、消費形式等等都是錯綜雜亂的,這樣的流水帳目,就可以利用「樞紐分析表」來快速建立一個「以支出人、支出類型重新分類」的試算表。

又或者公司裡有一份銷售報表,裡面有每一個單位的每一個產品的銷售數據,可是這份報表原本依照銷售數字排序,所以 A 單位的五個產品散落在不同欄位、 B 單位的十個產品也錯雜在不同地方,這時候,可以利用「樞紐分析」來快速建立一份 A、 B、 C 等單位各自所有產品的總銷售量報表。

在報告、統計時,善用「樞紐分析表」,就能以更精確、更聰明的方式「透視」原本雜亂的報表,會讓你的報告看起來更專業。




而這個 Excel 高手愛用的「樞紐分析表」功能,在「 Google 試算表」中有一個更直觀的名字,叫做「資料透視表」;

也就是幫你透視原本雜亂的資料,快速重新整理出新的邏輯。

這篇文章,我就用實際自己的流水帳試算表為例,示範如何在 Google 試算表中使用「樞紐分析表(資料透視表)」的功能。




  • 這篇文章的前因後果:

「前因」:我之前其實有簡單介紹過 Google 試算表有這個樞紐分析功能,就在「Excel 記帳雲端進化! Google 表單比記帳 App 還好用」這篇文章中,不過因為沒有詳細介紹,有電腦玩物的讀者寫信來詢問我,可不可以完整介紹一下這個「樞紐分析表(資料透視表)」到底要怎麼用?用來做什麼?而這篇文章就是我的回答。

「後果」:如果想要了解 Google 試算表更多功能教學的朋友,推薦繼續參考我的這篇文章:「Google 試算表活用教學:你可能還不知道的10個技巧」。也推薦參考我最新撰寫的新書《比別人快一步的 Google 工作術:從職場到人生的100個聰明改造提案》。

2017/7/9 新增出國旅行記帳分帳不混亂教學:免裝 App 的記帳表格範本下載 




步驟1:選擇工作表,原始數據來源是什麼?


首先,我們先有一份資料很豐富但很雜亂的數據表格,這是最適合使用「樞紐分析表(資料透視表)」的時機。

例如我之前利用「我的家庭記帳方案:實作 Google 雲端記帳一個月心得」這個方法,收集了大量我和老婆支出的流水帳,可是帳目很凌亂,就來試試看利用 Google 試算表的樞紐分析重新整理。



我打開這份 Google 試算表,點擊上方的「資料」功能選單,選擇要建立這份資料的「資料透視表」,這就是 Google 的樞紐分析表了。



先來看看最後的成果,我可以利用樞紐分析,把上面那樣雜亂的資料表,「快速簡單」的變成下圖這樣重新整理後的統計表。

在整理後的統計表中,我可以透視每一種消費類別裡,我和老婆各自支出的總額統計,這是不是比雜亂的流水帳更清楚呢?而要建立這樣的統計表,只要簡單幾個點選即可。





步驟2:建立列,要聚焦分析哪個項目?


一開始建立「樞紐分析表(資料透視表)」時,你看到的其實是下面這樣的空白表格,因為我們尚未決定要匯入哪些原始資料表的欄位和數值,所以我們一步一步重新來示範。

首先,我們要決定「直列」要放什麼欄位,可以這樣去思考:

我想從哪些項目切入做聚焦的分析?

例如我要分析我和老婆的花費金額比較,或是我要分析不同消費類別的花費比較。



假設我要從「支出人」的角度切入分析,那麼我就在右邊點選「直列」的「新增欄位」,選擇「支出人」這個欄位。



這時候會看到來源資料表裡的支出人項目名稱,都出現在「樞紐分析表(資料透視表)」的直列上了。





步驟3:建立欄,要分析這個項目的哪種資料類型?


有了切入角度後,那要看這個項目的哪些資料類型呢?

例如我從支出者的角度切入,想看每個支出者在每種消費項目上的花費統計,這時候就可以在「橫欄」做新增欄位的選擇(其實列與欄的意義也可以替換,後面的教學你就會看到,這邊的說明只是為了比較好解釋)。

在右邊的「橫欄」處選擇「新增欄位」,選擇原始資料表中的「消費類別」,這時候「樞紐分析表(資料透視表)」就會呈現下圖這樣的表格。





步驟4:建立值,匯入數據進行統計分析


有了一個新的表格整理欄位後,就要「把數值匯入」,在這裡的例子就是要把各種開支金額匯入表格中。

這時後到右邊的「值」選擇「新增欄位」,然後匯入原始表格裡的「消費金額」,預設是會「加總」的模式匯入。



於是,我就看到一個全新的「樞紐分析表(資料透視表)」,在以支出人為切入點的角度中,看到每一種消費類型裡每一個人的支出總額。

這樣的重新透視,就給了原本雜亂的資料表,全新的整理意義





步驟5:建立篩選,聚焦某一區段資料


「樞紐分析表(資料透視表)」還可以做進一步篩選,例如原本的整張數據表資料是三個月的帳目,但我這時候只想看 2015 年 11 月的帳目,要怎麼快速篩選呢?

來到右邊的「篩選器」,選擇「新增欄位」,並且選擇要透過「交易日期」來篩選出項目,目的是「只讓」2015年 11 月的資料進行統計。



所以這時候打開原本預設的「所有項目」選單,原本是所有交易日期都統計,請先「清除」所有勾選。



接著在搜尋欄位,輸入關鍵字進行過濾,例如我輸入「2015/11」,那麼交易日期是 11 月的資料就會全部列出來,這時候我再選「全部選取」,就等於是只勾選了 2015 年 11 月的資料囉!



透過這樣的方式,立刻就可以把原本的「樞紐分析表(資料透視表)」,變成只有統計 11 月的分析表了。





步驟6:進一步變化


「樞紐分析表(資料透視表)」還可以做進一步外觀調整,右方的各個「欄位數值卡片」都可以「拖曳移動位置」,或是「插入新的卡片」,透過這個方法,我可以改變統計表的格式外觀,調整成我想要的。

例如我在「列」中又繼續插入「支付方式」的卡片,這樣一來就可以看到表格多了一層現金、信用卡的分類欄位。



我也可以拖曳右方每一張卡片的位置,例如把「支付方式」、「消費類別」移動到列,把「支出人」移動到欄,那原本的統計表,就會立刻變成下圖的模樣。

利用 Google 試算表或是 Excel 的「樞紐分析表(資料透視表)」,確實只要輕鬆幾個選擇,就可以重新整理出真正想要的統計表格。


  • 如果你是搜尋而來,說不定你想找的,還想學的是:




「樞紐分析表(資料透視表)」沒有看起來的那麼難懂,因為他的設計目的就是要「簡單清楚」,當你下次遇到雜亂的資料表時,不用自己手動重新整理,試試看 Excel 或 Google 試算表內建的樞紐分析,就能快速重整出你需要的統計報告,節省時間,也讓你的報告更專業!


*感謝您閱讀電腦玩物 Esor 的文章,電腦玩物專注人性化的高效率工作方法,您可以追蹤「電腦玩物 Facebook」,或是參考 Esor 的改進工作效率相關「課程」、「書籍」,更歡迎把電腦玩物加入你的 RSS 訂閱清單。

(轉貼本文時禁止修改,禁止商業使用,並且必須註明來自電腦玩物原創作者 esor huang(異塵行者),及附上原文連結:Google 試算表樞紐分析表範例教學,像 Excel 般資料透視整理

留言

  1. 在簡體中文EXCEL里,這個功能就叫“數據透視錶”,呵呵

    回覆刪除
  2. 請問App版有這個功能嗎?

    回覆刪除
  3. 有個試算表問題請教,怎麼把三個工作表的資料,合併到同一個工作表內?
    如:
    工作表A http://imgur.com/a/Btxt6
    工作表B http://imgur.com/YL4t4PA
    工作表C http://imgur.com/aXZKiJM
    要合併為工作表ABC: http://imgur.com/RVdbTzr

    回覆刪除
  4. 自己回答:
    ={FILTER( A!A2:E999,A!A2:A999<>"");FILTER(B!A2:E999,B!A2:A999<>"");FILTER('C'!A2:E999,'C'!A2:A999<>"")}

    回覆刪除
  5. 啊原来在简体中文版数据透视直接就是在数据下面。我就说怎么找不到。

    回覆刪除
  6. 您好,請問有人知道google試算表如何製作多層連動下拉選單嗎??

    因為我的資料過多....不好設定,尋求協助....

    回覆刪除
  7. 請問有辦法設定樞紐表自動更新嗎? A欄我想設定成以日期區分,然後看每日的總花費,但雲端版本似乎不能依照記帳表來自動更新,也不知道該怎麼讓它更新QQ

    回覆刪除
  8. 他透視表的資料跑出來,若無資料會直接空白
    請問下該怎麼讓空白的部分可以顯示為0嗎?
    因為目前我想另外建立函數算中位數,可是空白的格子好像會導致錯亂

    回覆刪除
  9. 請教如何資料重整?

    回覆刪除

張貼留言

為了避免垃圾廣告留言過多,開始測試「留言管理」機制,讓我可以更容易回應讀者留言,並更簡單過濾掉廣告,但只要不是廣告留言都會通過審核。

這個網誌中的熱門文章

全家沖印、 7-11 列印照片誰好用?立可得與 ibon教學+比較表

微軟 Bing AI 繪圖工具 Image Creator 免費開放,30秒輕鬆生成圖片

Google Maps 我的地圖完全教學!規劃自助旅行攻略

3個 ChatGPT 輸入到輸出實驗,第二大腦筆記用 AI 生成文章報告

Arc 瀏覽器 windows 版測試心得,用四大介面改造升級專注工作流

如何為筆記命名,建立搜尋規則?我的標題、分類、標籤命名法

Google 表單的 10 個專業問卷調查設計秘密教學

Draw.io 中文線上製作流程圖首選!專業水準完全免費

如何用 ChatGPT 加速生成一份有圖有文的簡報,實戰案例教學

Google 表單自動關閉回覆教學:時間人數到達就過期