[Excel] 樞紐分析─值、各項目比較


很多人光聽到「樞紐分析」這個看起來有點學問的名稱,就覺得很可怕,但是當手上有資料要做成長率分析、差異量或是佔總額比率等統計分析的時候,利用樞紐分析就可以很簡單的產生出美觀的報表,不需要額外的函數,比自己寫公式還要簡單上百倍。

「樞紐分析」可以將資料分組、分群,過濾掉不需要的資料,保留有用的資訊,整合在一張報表中,並利用各式各樣排列組合的方式,表達出使用者所要的內容。
https://lazzzyj.blogspot.tw/
這裡先以「政府資料開放平台」中,新北市人口統計資訊作為範例。


我習慣先將資料轉換為表格,不管是在抓取資料或計算上面都會比較簡便,資料一有變動,表格的範圍會自動調整,不需要再用手動的方式去改變儲存格範圍。
*轉換為表格請參考:[Excel基礎] 範圍、名稱、表格(資料表)

選取整個表格用鍵盤快速鍵只需要1秒,千萬不要用滑鼠慢慢慢慢拉。先選到資料第一格,接著用鍵盤按住「Shift+Ctrl+End」整個範圍就都選起來啦!接著用「插入」→「表格」就可以了。https://lazzzyj.blogspot.tw/

快速鍵小教室:
Shift:多重連續選擇
Ctrl+上下左右:到資料的最上下左右邊
Crtl+Home:到最左上角的儲存格
Ctrl+End:到最右下角的儲存格

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
接著要產生樞紐分析表,將滑鼠點選表格中隨便一個儲存格上,按「插入」→「樞紐分析表」,確認之後就會在另一個工作表中出現配置畫面,或是要指定他產生的位址也可以。

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
右邊的「樞紐分析表欄位」就是剛剛表格中最第一列的標題,我所使用的資料有男生及女生的人口數,但是我想要看的是全部的人口數,除了在原始表格多一個欄位用函數計算以外,也可以用樞紐分析表工具來計算,就不會動到原始的資料。

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
使用「樞紐分析表工具」→「欄位、項目和集」→「計算欄位」,建立一個新的名稱,再告訴他計算的方法,「總人口=男生人口數+女生人口數」接著新增後確認。

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/

這時候「樞紐分析表欄位」會出現剛剛自行設定的「總人口」,便可以用它來計算。我現在的目標是要產生出一張可以表達出「新北市各區100至105年0歲人口總數」的表格,因此我先在篩選的區域將年齡設定為0歲,將區域拉到列,年度拉到欄,最後把總人口數拉到值的位置,便產生出下表:

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
此時加總的人口數沒有千分位符號不利於閱讀,因此可以點選欄位右邊的箭頭,並選擇「值欄位設定」,在左下角的「數值格式」直接設定儲存格格式。如果不是從樞紐分析表中設定,而是用一般的方式直接在儲存格上設定,那麼在重新整理資料時,之前所設定的格式經常都會被取消。
https://lazzzyj.blogspot.tw/
Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
上面是最基礎的樞紐分析表建立方式,但若僅顯示上述的資料,往往無法滿足於資料使用者所需要的分析。以同樣的資料來說,若加上人口的成長率,或是各區佔新北市人口的比率等,這張報表的可用性便大幅提升。而這些計算,在樞紐分析表中也可以輕易地完成。
https://lazzzyj.blogspot.tw/
一樣點選總人口的「值欄位設定」→「值的顯示方式」,當要顯示出人口成長率,便選擇「差異百分比」,而要比較差異的基礎,便是年度。依據所要比較的欄位,選擇要比較的基本項目,若是像消費者物價指數等使用「基期」的資料,便直接選擇該基期,而這裡要的是與前一年度比較的資料,便選擇「(前一)」。
https://lazzzyj.blogspot.tw/
Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/


產生出成長率。便可以輕易的看出區域間、以及整體的變化。

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
另一種是「父項列總和百分比」,指的就是「列加總」後該值所佔的百分比,與「列總和百分比」是不同的計算方式。可以試著資料選擇不同的計算方式來觀察其中的差異。

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
設定好之後,下表便顯示出各區佔新北市人口的比率,為確認值的顯示方式正確,可以看「總計」的地方,所有比率加總為100%,便是我所要表達的資料。

Excel樞紐分析,以值的顯示方式產出各類資料的比較
https://lazzzyj.blogspot.tw/
製作樞紐分析表不需要有強大的函數能力支撐,只要知道欄、列以及所要表達的資料,用滑鼠點點點就可以輕鬆完成,以後不要再怕這個好用的工具囉!

如果你認同我的努力,歡迎贊助本網站,讓我能有更多資源做得更好

支援付款方式:歐付寶帳戶、網路ATM、ATM櫃員機、信用卡及超商代碼
歐付寶會員編號:2282643


沒有留言:

張貼留言

Related Posts Plugin for WordPress, Blogger...