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秒輕鬆生成圖片

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

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

個人知識管理的時間平衡心法,忙碌中我如何維持高效率輸入輸出

5 條「這時候,不要做」清單打破無意識行動模式的低效率循環

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

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

ChatGPT DALL-E 3 的7個特殊 AI 繪圖應用教學,勝過 Midjourney