揭開excel的面紗--一次關於數據分析師職位的分析實踐

不學不知道，原來excel的強大超出你的想像。

這次課程通過一次具體的分析實踐來學習excel在數據分析方面的強大功能。

數據分析步驟如下：

數據分析總共分五步：

提出問題>理解數據>數據清洗>構建模型>數據可視化

一、提出問題

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

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

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

修改excel之前先備份，修改excel之前先備份，修改excel之前先備份

重要的事說三遍！

二、理解數據

這份excel數據表列出了從網上抓取的關於數據分析師職位的一些具體信息，包括地區、公司名稱、公司大小、所在商區、學歷要求、職位id、福利、職位名稱以及薪水。

我們就要利用這份excel表來找尋之前問題的答案。

三、數據清洗

這部分是五步裡面最耗時的工作，一般要佔去60%的時間。數據清洗也可以分成七步。

1.選擇子集

隱藏掉意義不大的列，比如我們隱藏而不是刪除掉公司全名和公司ID兩列，留下公司簡稱即可。右鍵單擊列選擇隱藏。

隱藏後如下：

2. 列明重命名

可以雙擊列名進行修改對列名進行重命名。

3. 刪除重複值

選中全表、點擊數據選項卡下面的刪除重複值、取消全選、只選擇職位id然後確定。這樣相同的職位id行就被刪除了，總行數從6875變成5032。

4. 缺失值處理

通過選取城市列名發現這一列列只有5030行而不是5032，因此有兩個缺失值。

選取整個列點擊開始選項卡下面的查找和選擇按鈕，選擇「空值」然後確定來定位。

先填充一個單元格然後按ctrl+Enter即可全部補全。

5. 一致化處理

1）公司所屬領域

公司所屬領域有的是一個領域有的是兩個領域，因此需要進行拆分處理。

分列功能會覆蓋掉右邊單元格，因此要先複製到最後一列。選中這列右鍵複製，點擊最後列再右鍵粘貼即可，把原來的列隱藏。

選擇數據選項卡下面的分列對話框，選分隔符號",",選中連續分隔符號視為單個處理。

分列後如下：

2）薪水

薪水這一列顯示的是最低薪水-最高薪水，有兩種方式將其分成最低薪水和最高薪水兩列。第一種是使用「-」進行分列然後將k替換成空。

第二種方式是用文本查找函數進行獲取。我們使用第二種。

最低薪水截取：在最低薪水那一列中第一個單元格裡面使用公式=LEFT(M2,FIND("k",M2)-1)。這個函數的意義是找到M2單元格第一個k的位置，然後獲取k左側的全部字元，即是最低薪水。

最高薪水截取：獲取最高薪水的函數比較複雜，在最高薪水那一列中第一個單元格裡面使用公式

=MID(M2,FIND("-",M2)+1,LEN(M2)-FIND("-",M2)-1)。MID(單元格，開始截取的位置，截取總長度)。這個公式的意義是用MID函數在M2單元格從「-」後面的位置開始查找，最後截取的長度是字元串 總長度減去分隔符再減去一（減一是去掉最後的k）。

獲得了一個最高薪水和最底薪水之後把滑鼠放在單元格右下角會出現十字形游標，雙擊 即可將函數應用到整列。

然後檢查一下薪水有沒有錯誤，對於最低薪水1.點擊開始選項卡排序和篩選下面的篩選功能，然後會出現篩選的按鈕，2.點擊此按鈕出現篩選對話框，3.取消 全選 拉到最下面選擇#VALUE!。發現這些不正常的數值是 由於薪水單位K而不是k，利用替換功能 將 K換成k即可變為正常。

最高不正常原因 是因為有些薪水是15k以上，對這種行直接讓最低薪水=最高薪水然後應用到整個最高薪水列。

這時候計算平均薪水會顯示錯誤提示，因為單元格裡面數據類型不是數字。需要將字元串轉換 為 數字類型。

首先在最低薪水和最高薪水後面分別分別插入兩列，將最低薪水和最高薪水複製過去，選擇性粘貼，粘貼方式是數值，運算無，之後會看到單元格左上角出現綠色小三角。

然後選中此列，點擊數據選項卡的分列，分隔符號>勾選「連續分隔符號視為單個處理」>下一步>完成。發現數字右對齊，小三角消失。

然後利用平均值函數計算出平均薪水並應用到整列。

6.數據排序

選擇平均薪水列進行降序排列，點擊排序和篩選>降序>擴展選定區域。

7.異常值處理

利用數據透視表。點擊插入選項卡下面的數據透視表，選擇新工作表，其他默認，會新建一個sheet。

將職位名稱拖到行的位置再將職位名稱拖到值的位置，默認是求和。

統計相同職位名稱的職位出現多少次。

可以看到有些職位其實並不是數據分析師，比如開發工程師，我們要把這些職位去掉，利用函數對特定字元進行篩選。

=IF(COUNT(FIND({"數據運營","數據分析","分析師"},L2)),"是","否")

這個公式的 意義是如果L2中含有字元串"數據運營"或"數據分析"或"分析師"，COUNT會返回非零值，最後結果是「是」，否則COUNT返回零，IF結果是「否」。雙擊十字應用到整列。

然後利用篩選功能選擇「是」的崗位，保存為另一份文件。

四、構建模型

1. 在哪些城市找到數據分析師的幾率比較大

也就是說哪個城市數據分析師職位多。

插入數據透視表，行選擇城市，列拖入工作年限，值將城市拖入。

然後在其他排序選項裡面選擇按照城市求和降序排序。

可以右鍵單擊總計下面的 單元格 ，選擇值顯示方式：列匯總的百分比。按百分比方式顯示數據。可見北京的職位最多。

然後對平均薪水進行統計，安裝excel的數據分析功能。

點擊數據選項卡下面的數據分析按鈕，選擇描述統計。

可以看到平均薪水是14k。

2. 數據分析師的薪水如何

插入數據透視表，行選擇城市，列選擇平均薪水，但是默認求和。右鍵平均薪水下面的單元格值匯總方式選擇平均值。然後行標籤>其他排序選項，按照平均薪水的平均值降序排列。看到深圳的平均薪水是最高的。

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

插入數據透視表，將工作年限要求拖入 行，將 平均薪水 列 。修改 值 匯總依據為 平均值 。