性別及婚姻狀況資料分析及圖表視覺化製作

小琪在順利的完成的職級資料分析後，對用EXCEL進行資料分析的興趣大增，又開始著手分析性別及婚姻狀況的資料。

小琪依然打算從兩個維度分析這組資料，第一維度是看各部門的性別及婚姻狀況結構，第二個維度是看這兩個專案在各部門的分佈情況。

無論進行何種分析，首先都要製作彙總表，並進行統計。（如圖 3162所示）

圖3-162

以“總經辦”、“男”員工人數所在單元格為例，公式為：

=COUNTIFS（人員檔案常用資訊！$C：$C，$B5，人員檔案常用資訊！$J：$J，C$4，人員檔案常用資訊！$Q：$Q，“”）

將此公式橫向、縱向拖動，即可完成性別表格的人數統計。

以“總經辦”、“已婚”員工人數所在單元格為例，公式為：

=COUNTIFS（人員檔案常用資訊！$C：$C，$B5，人員檔案常用資訊！$G：$G，E$4，人員檔案常用資訊！$Q：$Q，“”）

將此公式橫向、縱向拖動，即可完成婚姻狀況表格的人數統計。

在將彙總表製作完成後，小琪便開始考慮用何種圖來表達這兩種資料呢？

“性別只有兩種情況‘男’和‘女’，而婚姻狀況的分類也不多，只有三項，這種資料用餅圖最好！”

因此小琪果斷決定這兩組資料用餅圖來進行資料視覺化的呈現。

一、各部門的性別及婚姻狀況結構分析

Step1：插入控制元件，並設定控制元件格式。（如圖 3163所示）

圖3-163

Step2：選擇合適的圖表型別，並製作輔助資料區域。（如圖 3164所示）

圖3-164

以部門下的“後勤部”所在單元格為例，公式為：

=INDEX（B$5：B$16，$B$17，1）

隨後，將公式橫向拖動，即可完成輔助資料區域的製作。

Step3：製作動態圖表，並進行美化。

最終效果如圖 3165所示：

圖3-165

二、性別及婚姻狀況在各部門的分佈情況

Step1：插入控制元件，並設定控制元件格式。由於本例需要對兩組資料進行分析，因此需要設定兩組控制元件。如圖 3166和圖 3167所示。

圖3-166

圖3-167

Step2：選擇合適的圖表型別，並製作輔助資料區域。（如圖 3168所示）

圖3-168

小琪按老習慣，製作了兩軸柱形圖的輔助資料區域。然後便開始製作公式：

首先是性別列下方的“男”所在單元格的公式：=INDEX（$C$4：$D$4，1，B24）

接著又製作了“婚姻狀況”列下方的“已婚”所在單元格的公式：=INDEX（$E$5：$G$16，MATCH（C$29，$B$5：$B$16，0），$C$24）

由於此公式不是一個通用公式，所以小琪必須將公式進行逐個修改，而這一份輔助資料區域中，由於同時分析性別和婚姻狀況，所以要修改的公式是以前的兩倍，這樣一來工作效率就慢了很多。

恰在此時，小琪的救星顧城走出了辦公室！

顧城路過小琪身邊，看到小琪正在一一修改公式，便說道：“小琪，你這樣修改公式太慢了！要學會用EXCEL提高工作效率，為什麼不製作一個通用的公式呢？”

“顧總，這個輔助表跟彙總表的結構不一樣，不能像分析各部門的性別及婚姻狀況結構那樣直接拖動公式的！”

“小琪，之前你剛開始接觸資料分析，對函式應用還不熟練，公式能簡化就簡化，但現在你已經自己做過幾個數據分析圖表了，對INDEX函式應用的也算比較熟練了，為什麼不多動動腦子，看一下你現在製作的INDEX函式是否可以配合其它函式，製成一個巢狀公式，來實現更加智慧的自動取數功能呢？”

“對了，再提示你一下，你在做人員檔案查詢卡的時候，我就告訴過你INDEX和MATCH函式的巢狀用法，你再仔細考慮一下！”說完，顧城拿著手中的檔案，行色匆匆的走了。

小琪在顧城走後，急忙找到顧城教她做的人員檔案查詢卡，開始重新研究當時製作的公式。

……

小夥伴們，小琪最終是如何解決這個問題的呢？

咱們明天接著聊！

關注小編，不僅有文字版的職場故事，還有影片操作版的EXCEL講解喲！