用EXCEL數據分析--強大的數據透視圖

用EXCEL數據分析--強大的數據透視圖

來自專欄猴子聊數據分析4 人贊了文章

我們以一份爬蟲得來的數據，數據分析師招聘網站的數據，利用Excel進行數據分析師職業的 城市、薪水、經驗等情況的分析。

數據分析步驟：提出問題-理解數據-數據清洗-構建模型-數據可視化

一、提出問題

首先，我們要明確此次數據分析的目的，為了解決什麼問題：

1、在哪些城市找到數據分師工作的機會比較大？

2、數據分師的薪水如何？

3、根據工作經驗的不同，薪酬是怎樣變化的

二、理解數據

第二步，我們要理解表格中的各個欄位（列名）表示什麼意思。

職位ID 表示職位的唯一表示，也就是每一行數據的唯一標識------用於去掉重複ID

三、數據清洗=數據預處理：去掉無效、重複數據，以取得符合我們要求的數據

第三步，我們開始進行「數據清洗」，這是數據分析工作中最耗時的一部分，要佔去60%的時間。「數據清洗」分成以下七個步驟：

選擇子集--列名重命名--刪除重複值-缺失值處理-缺失值處理-一致化處理--數據排序--異常值處理

1.選擇子集

只選擇對數據分析有意義的欄位，無意義的欄位隱藏掉，即隱藏不需要分析的列。（盡量不刪，保證數據的完整性）：隱藏 公司ID ，留 職位ID；隱藏公司全名， 留 公司簡稱

2、列名重命

將列名更改為我們容易理解的形式。

3、刪除重複值

對重複數據進行刪除，這裡我們對「職場ID」列進行刪除重複值處理：

操作步驟：Excel選項卡：數據--刪除重複項-取消全選-勾選

職位ID--確定-確定

PS：刪除重複值之後一定要按「Ctrl + S」保存文件，切記切記切記！

4、缺失值處理

①通過人工手動補全

②刪除缺失的數據

③用平均值代替缺失值

④用統計模型計算出的值去代替缺失值

通過選取「城市」與「職場ID」列，在右下角計數項發現「城市」列有2個缺失值=職場ID 計數值 [減去] 城市計數值。（ID是唯一，不會缺失）

如何快速定位？ 開始--查找和選擇--定位條件--空值-確定-空單元就被定位了--（較少）-選擇手工補全

另：空值多，如何批量一次性補全缺失值？開始--查找和選擇--定位條件-確定-空值被定為了--在第一個空單元格輸入 自己判定的值（比如，上海）---輸入完後同時 按住ctrl+enter，其他單元格就會被填充（上海）

練習掌握:

1)如何統計缺失了多少數據？

2）如何定位到所有缺失值？

3）如何用人工一次性補全所有的缺失值？

5、一致化處理～～～拆分處理

我們需要對數據進行統一的命名和處理。比如 公司的所屬領域是「企業服務，數據服務」

（1）對「公司所屬領域」進行分列拆分處理：將數據先複製到最後一列（分列功能會覆蓋右邊單元格），隱藏原始列----進行分列：選中要進行拆分的列-選項卡-數據---分列--分隔符號---下一步---勾選 其他，併入輸入

「，」且 勾選 連續分隔符視為單個處理（注意，將輸入法 切到 中文狀態 ，因為 中文的逗號和英文的不是一種字元）---下一步--完成，就會看到 在右邊生了一列出來，如圖，

注意：對 拆分出來的一頁 添加列名：公司所屬領域2，原來列 列名 改為 公司所屬領域1 ，否則 數據透視時 會出現警告：」數據表欄位名無效

（2）接下來，我們將薪水處理成「最低薪水」、「最高薪水」、平均薪水，用於存放清洗後的薪水數據。這裡有兩個方法可以實現：

使用分列功能，將「-」作為分隔符號，然後用「查找替換」功能替換掉「k」。

第二種是利用函數：

FIND函數的意思是查找一個字元串在另一個字元串中出現的起始位置，用find 函數查找分隔符 K或者「 -

find函數：

Find函數用來對原始數據中某個字元串進行定位，以確定其位置。Find函數進行定位時，總是從指定位置開始，返回找到的第一個匹配字元串的位置，而不管其後是否還有相匹配的字元串。

LEFT函數（從左開始截取字元串），MID函數（從中間截取字元串），Len函數（計算字元串長度），百度查函數用法，很容易看明白。

利用LEFT函數截取最低薪水，同理，我們通過MID函數截取出最高薪水，如下圖示例

將滑鼠放到單元格右下角 滑鼠自動變成 黑十字架--雙擊十字架

將 函數應用到整列，效果如圖

接下來處理 錯誤值 #VALUE! :

檢查 最低薪水列：用函數計算完最低薪水和最高薪水後，我們要檢查一下是否有錯誤值：選中最低薪水列--開始選項卡--點擊排序和篩選下的「篩選」，查看下列里是否存在「#VALUE!」----篩選對話框最上面，取消 全選----勾選錯誤值 #VALUE!--確定---發現 K 是大寫，而我們的篩選條件是 小寫的 k----使用替換：（從最低薪水處篩選錯誤值查明：是「k」的大小寫問題。使用查找替換功能將所有K替換成k即可）

o

替換K為k：選中 薪水 列 --點擊選項卡 開始--查找和篩選--替換--在對話框中輸入，查找K ，替換為k --全部替換---再篩選 ，檢查，未發現 錯誤值

o

檢查 最高薪水列：查找--有 錯誤值#VALUE----將這些錯誤值處理：=最低薪水 ---雙擊 十字架 應用到整列---再次 篩選 檢查///（從最高薪水處篩選錯誤值查明：是薪水列的格式「XXk以上」不規範的問題，此時將最高薪水等同於最低薪水即可。）

接下來，我們計算「平均薪水」。其中報出錯，1，因為最低薪水和最高薪水的數據類型不是數字，而是文本，所以無法完成計算；2，有空單元格，空單元被視作0，0不能作為除數！

於是，我們需要將

最低薪水和 最高薪水 從文本格式轉換為數字格式：選中最低薪水列--選擇性粘貼-彈出對話框--選擇，點選 數值 且 無運算---確定---發現被複制過來的單元格 左上角有個 綠色三角，這是字元串類型標誌---處理方法-分列：選中該列--選項卡 數據--分列--點選亮 分隔符--下一步----分隔符

不勾選任何，勾選 連續分隔符視為單個處理---完成----隱藏被複制列---- 最低薪水 和 最高薪水 的函數計算結果變成數值，如圖：

這樣「平均薪水」就計算正常了。

§

這樣我們的數據一致化處理就完成了。其實對於薪水處理，也有個更簡單的方法，不必運用複雜的函數，只要用Excel2013以上版本提供的一個新功能「快速填充」即可實現：

結果是一樣的，還省去了查找錯誤值、轉換文本為數字的時間。

6、數據排序

第六步，我們對「平均薪水」進行降序排列：

7、異常值處理

首先科普下 數據透視表的原理：

數據透視 就是 數據處理模式 ，Split 數據分組，Apply應用函數，Combine 組合結果 的過程體現 ：

數據分組：按照某種規則對數據分類

應用函數：對分組後的數據 分析進行 計算

組合結果：對應用函數的計算結果進行匯總，將計算結果合併到一張表裡。

例如，分析：航班 航行距離 與 到達延誤時間的關係，以及 地點對延誤時間的影響 ，如圖

插入（不要全選，它會自動選擇數據區域）--數據透視圖--對話框-選擇區域：整張表--點選 新工作表--在右方 數據透視圖欄位 中 勾選 職位名稱-----將其拖入到下方的 行（軸） 和 求和 （設置要分析的行和列），如圖，

o

說明：數據透視表中的 行 和 列 對應 數據分組，求和 對應的是 應用函數 ，數據透視表 報表 對應 組合結果，這些都和在數據透視欄位中設置

利用數據透視表觀察有沒有異常值。比如我們從職位名稱的計數項可以發現很多跟數據分析不相關的職位：接下來我們要處理掉這些異常值：----對 行標籤 進行 排序：點 行標籤 下來箭頭--其他排序選項--選擇降序排序 選擇依據---計數項：職位名稱--確定-----處理異常值，把不屬於 數據 職位的名稱去掉： 利用函數「=IF(COUNT(FIND({"數據運營","數據分析","分析師"},L4)),"是","否")」，來識別異常值---然後 篩選掉異常值

-----（英文數據透視表 是對所有的數據進行分析，而不是只分析篩選過後的結果），所以---篩選處分析師職位 後 複製---粘貼到新的工作表中，命名為數據分析結果----再進行 數據透視

四、構建模型

1、在哪些城市找到數據分師工作的機會比較大？

模型分析：哪個城市機會多=哪個城市的職位多，所以 圖的縱軸表示 職位數/ / /而數據透視表欄位中

行（圖的橫軸，表格是行 縱向排列） 應該是 城市 ，即圖橫軸 表示 城市；計數項應該也是 城市 ，即城市的職位 ；數據透視表欄位 中的 列 應該是 工作年限 ，即 對 某個城市進行細分，即

某個城市中 不同工作年限 的職位情況

步驟：全選--選項卡，數據透視圖----將數據透視表欄位 中的 城市 拖入到 行，將 工作年限要求 拖入到列

，將城市 拖入到 統計計數 -----點選 表格 城市行標籤---其他排序選項---降序排序--計數項：城市，如圖

o

利用數據透視表可以看出：北京的數據分析工作機會最多，往後是上海、深圳、廣州、杭州。年限要求來看，1-3年的需求量較大，其次是3-5年，說明對年輕人需求將多，行業新興。

按百分比顯示每一列的數據： 在總計 的任意單元格上，右擊--值顯示方式--列匯總百分比

接下來我們利用Excel的數據分析工具對平均薪水進行描述統計：

2、數據分師的薪水如何？

o

首先，安裝EXCEL 的數據分析功能：選項卡，文件--選項---載入項---管理，選擇 EXCEL 載入項---轉到---勾選 分析工具庫---確定

步驟：選項卡，數據--右邊，數據分析---對話框中，勾選 描述統計----確定----輸入區域，框選中平均薪水列---分組方式--逐列----勾選

標誌位於第一行(表明第一行是列名不包括在計算機裡面)-----點選中

新工作表組---輸入： 薪水描述統計(工作表名稱)-----勾選 歸總統計、平均數置信度95%、第K值大致5、第K值小值5----確定，結果如圖

數據分析師的薪水如何？薪水-城市：

利用 數據透視圖

可以看出：深圳的數據分析師平均薪水最高，其次是北京，上海，杭州

3、根據工作經驗的不同，薪酬是怎樣變化的？

可以看出：隨著工作經驗的增長，數據分析師的薪酬也在不斷增加。

o

難點：建模，建模要在我們腦海中理解處理數據的目的，要反應什麼關係，數據透視圖就是生成 一個 X-Y 圖，要先弄清楚 哪些數據是X 軸，比如 城市、工作年限，這些是變數，對應 數據透視欄位的行 ；而 Y軸，比如 職位數、平均薪水，這些是應變數，對應 數據透視表欄位中 的 「∑值」；而 X X 並列，比如 城市+年限，對應 欄位的 圖例（系列）

通過上面的分析，我們可以得到的以下分析結論有：

1）數據分析這一崗位，有大量的工作機會集中在北上廣深以及新一線城市，如果你將來去這些城市找工作，可以提高你成功的條件概率。

2）從待遇上看，數據分析師留在深圳發展是個不錯的選擇，其次是北京、上海。

3）數據分析是個年輕的職業方向，大量的工作經驗需求集中在1-3年。

對於數據分析師來說，5年似乎是個瓶頸期，如果在5年之內沒有提升自己的能力，大概以後的競爭壓力會比較大。

4）隨著經驗的提升，數據分析師的薪酬也在不斷提高，10年以上工作經驗的人，能獲得相當豐厚的薪酬。

推薦閱讀：