利用Excel進行數據分析的基礎過程

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

數據分析步驟：

• 提出問題
• 理解數據

1. 根據研究的問題採集相關的數據
2. 查看數據集的信息，包括描述統計信息，從整體上理解數據或理解每個Excel欄位是什麼

• 數據清洗（數據預處理）

1. 選擇子集（將不需要用的列隱藏，不要刪除以保證原始數據的完整，萬一以後要用到）
2. 列名重命名（個別列名缺失補全，或繁雜的列名換成自己熟悉理解的列名）
3. 刪除重複項
4. 缺失值處理
5. 一致化處理（數據是否有統一的標準或者命名）
6. 數據排列
7. 異常值處理

• 構建模型

1. 簡單的：得出一些業務指標
2. 複雜的：運用經濟學習演算法來訓練模型

• 數據可視化

用圖表的形式與他人展示交流你的研究結果

上面列出了Excel進行數據分析的整體架構，下面運用某招聘網站爬取的數據作為案例，按順序進行具體操作，並學習一些常用的Excel操作與函數。

提出問題

1. 在哪些城市找到數據分師工作的機會比較大？
2. 數據分師的薪水如何？
3. 根據工作經驗的不同，薪酬是怎樣變化的？

理解數據

根據我們的問題可以知道我們主要需要關註：城市，薪水，工作經驗，教育水平，行業領域這些數據。我們表格裡面給出了城市、公司全稱、公司ID（用來唯一標識這個公司的欄位）、公司簡稱、公司大小、公司所在商區、職位所屬（招聘職位所屬部門）、教育要求（應聘該職位所需學歷）、公司所屬領域（公司的業務範圍所屬）、職位ID（唯一標識該職位的欄位）、職位福利、職位名稱、薪水、工作年限要求這些欄位。

數據清洗

好習慣：原始數據複製一份，複製的數據重命名加上「清洗數據」這個後綴，後面清洗數據的操作都將在該複製數據上進行。這樣的好處是，如果在清洗數據的過程中出現不可逆的錯誤，我們的原始數據還在，可以再次複製原始數據重新來過。

1.選擇子集

之前理解數據的時候有看過數據表裡所有的欄位，其中公司全稱和公司簡稱是重複的內容，公司大小、公司所在商區、職位所屬、職位福利這些是我們暫時用不上的數據，所以進行列隱藏操作。（選中列 滑鼠右擊，選擇「隱藏」）如圖所示：

如果要取消隱藏，點擊左上角的三角（行號和列號交叉處）全選表格 滑鼠右擊，選擇「取消隱藏」

2.列名重命名

列名的重命名，滑鼠雙擊需要重命名的列名單元格，重新編輯一下即可。

3.刪除重複項

觀察表格，重複項可能存在於同一個公司招聘同一個職位多次。而這裡有公司ID和職位ID兩個唯一標識公司和職位的欄位，我們可以將公司ID和職位ID都重複的數據刪掉。（全選表格 選擇「數據」標籤 按下「刪除重複項」按鈕 按下「取消全選」按鈕 勾選「公司ID」和「職位ID」 點擊「確定」）如圖所示：

經過上述操作，我們發現了1843個重複值並刪除，保留下來5031個唯一值。

4.缺失值處理

原始數據有時會有缺失數據，導致缺失的原因有錄入人員沒有填寫改部分信息，或者技術原因爬取數據的時候沒有爬取到。首先我們需要判別每一列有無缺失數據，選中列 在表格的右下角會有個「計數」統計該列有多少數據。通過該數據我們可以看到只有「城市」列有5030個數據，其他列都是5032，也就是「城市」列少了兩個數據。

缺失值的4種處理方法如下：（根據情況靈活運用）

• 人工手動補全
• 刪除缺失的數據
• 用平均值代替缺失
• 用統計模型計算出的值去代替缺失值

我們這裡缺失的數據較少可以直接手動補全。如果遇到缺失數量很多，同時缺失值一致的時候我們則需要學習

a.如何快速定位缺失值

（選中列 「開始」標籤中點擊「查找與選擇」下拉框中選擇「定位條件」選擇「空值」並點擊「確定」）即可選中所有缺失值。

b.如何一次性補全多個缺失值

在選中所有缺失值的情況下，輸入內容如我們這裡的「上海」，輸入完按Ctrl+Eneter，這時其他選中的缺失值的單元格中都填入了「上海」

Ctrl+Eneter快捷鍵：在不連續的單元格中同時輸入同一個數據或公式時適用。

5.一致化處化

這裡要結合我們的之前提出的問題來進行，「城市」、「教育水平」、「工作經驗」都沒有問題有對應的欄位，但是薪水給出的是一個範圍如「7k-9k」，我們不能用範圍來進行比較，因此我們需要用這個範圍得出平均薪水來進行比較。

如何得出平均薪水呢？我們有公式：（最低薪水+最高薪水）/2 。那我們就要從薪水範圍中把最低薪水和最高薪水分開以方便計算。這裡有兩種方法操作：a.分列法 b.函數法。

a.分列法

我們可以看到薪水範圍那一列一般都是最低薪水和最高薪水中間用「-」連接，因此我們可以用「分列」功能，用分隔字元進行分列，並用「替換」功能將「k」這個單位去掉。

注意：將需要分列的那一列複製粘貼到最後的空白列進行分列操作（直接分列操作會覆蓋後一列的內容），原本那一列隱藏即可。

此處用到的快捷鍵為：Ctrl+C 複製；Ctrl+V 粘貼；Ctrl+F 查找或替換。

b.函數法

此處需要混用LEFT，RIGHT，MID，LEN，FIND函數，作用及用法如下：

截取字元串中的內容：LEFT，RIGHT，MID函數

=LEFT/RIGHT（字元串所在單元格位置，從字元串左/右端開始到X位置進行截取）

=MID（字元串所在單元格位置，開始截取的位置，截取長度）

獲取字元串長度：LEN函數

=LEN（字元串所在單元格位置）

獲取某個文本在字元串中的位置：FIND函數

=FIND（"文本內容"，字元串所在單元格位置，開始查找的字元的序號）

即=FIND（查找什麼，在哪找，從什麼位置開始查找）

觀察字元串以及其位置，我們可以發現「7」在第一個「k」的左邊，即最低薪水的是從字元串的左邊進行截取，截取長度為「k」所在位置減1，以第一個數據為例，字元串所在位置為M2

最低薪水=LEFT(M2,FIND("k",M2)-1)

注意：寫公式的時候一定要在英文輸入狀態下寫。

「19」在「-」和第二個「k」之間，即最高薪水的開始截取位置是「-」所在位置加1，截取長度為字元串總長度減「-」所在位置的長度再減第二個「k」的長度1

最高薪水=MID(M2,FIND("-",M2,1)+1,LEN(M2)-FIND("-",M2,1)-1)

到這裡還不算結束，我們要篩選看下有沒有計算出錯的數值，並查明原因進行更正。

由篩選可以看出最低薪水出現錯誤是因為「k」和「K」大小寫的問題，將這部分出錯數據公式中小寫的「k」改成大寫的「K」就可以了,或者原薪水列將「K」替換成「k」。最高薪水出現錯誤是因為「15k以上」，只有最低值沒有最高值，將這部分出錯的數據刪掉，讓最高薪水=最低薪水即可。操作如下：

此時平均薪水就可以用Average函數進行計算了，在最低最高平均薪水三個單元格都用函數填好的情況下，我們可以選中三個單元格，將滑鼠移到單元格右下角，當滑鼠變成「+」的時候雙擊就可以一次性用公式填充好下面的單元格了，如果沒有填充就在變成「+」時按住滑鼠拖動到最下面進行填充。

但是我們實際操作時發現輸入Average函數得到的是

顯示的錯誤原因是：「被零除」錯誤

Excel中單元格內容有以下4種類型，我們這裡「7」，「9」就是紅框表示的字元串形成存儲的數值，屬於字元串類型，無法進行計算。

字元串形成存儲的數值，即字元串類型的數值有兩種表現形式

1.位於單元格的左邊，左對齊 2.左上角有個綠色的小三角

那如何將字元串類型變成數值類型用來計算呢？

首先我們將最低薪水和最高薪水列的數據填充完畢（單元格右下角滑鼠變「+」時按住拖動，或雙擊），此時這兩列都還是公式，我們選中兩列複製選擇性粘貼為數值到後面的空白列，這時可以看到每個數的左上角都有一個綠色的小三角。我們也有兩種方法轉換成數值類型

a.選中所有字元串類型的數字，點擊最上面的「！」，下拉菜單中點擊轉換為數字

b.選中列 分列 選擇「分隔符號」 勾選「連續分隔符號視為單個處理」 「常規」不動，點擊「下一步」 點擊「完成」

轉換完了我們就可以計算平均薪水了，公式及批量填充如圖

6.數據排列

數據排列分很多種升序排列，降序排列，按單元格顏色排列，按字體顏色排列等，目的就是讓數據有序，讓數據按我們想要的順序來，我們最常用的就是數值按升序降序排列，這裡我們可以讓平均薪水按降序排列。（選擇一部分數據進行排序的示範）

7.異常值處理

異常值的處理需要用到Excel的數據透視表，我們先來了解一下數據透視表的原理：

拿我們這裡的城市平均薪水舉例：

再看下我們直接用數據透視表得到的結果：

接下來我們利用數據透視表來看下有沒有異常值，比如不是數據分析師的職位。

全選數據 選中「插入」標籤 選中「數據透視表」 數據透視表中拖動「職位名稱」到行標籤和數值區（數值區設置為計數）點擊生成的數據透視表「計數項：職位名稱」單元格右邊的小三角點擊「其他排序選項」點擊「降序排序」依據「計數項：職位名稱」

由此我們得到了所有職位名稱的匯總，並出現較多次數的職位名稱排在最上面。可以看出職位名稱五花八門非常繁雜，並且其中摻雜著不是數據分析師的職位（這就是異常值），下面我們要做的就是去掉這些異常值。

思路：先找出屬於數據分析師的職位判斷為是，不屬於數據分析師的職位判斷為否，篩選出「是」的數據，就是我們要進行分析的數據分析師的職位數據。

那如何進行這個判斷呢？ 字元串內容包含「數據運營」，「數據分析」，「分析師」這三個內容就是數據分析師，不包含這些內容的則不是數據分析師

下面就是用函數將思路實現：

獲取某個文本在字元串中的位置：Find函數

FIND（{「數據運營」,「數據分析」,「分析師」}，L2）

但是上面這個函數直接用在表格中，你會發現返回值一直都是#VALUE!，是錯誤的，其實我們這裡的{「數據運營」,「數據分析」,「分析師」}是一組數，當它作為FIND函數的第一個參數時，FIND會拿數組裡的每一個文本去L列的單元格中查找位置，如果查找到會返回位置，如果查找不到則返回錯誤值，結果依舊是一組數。如L2單元格中含「數據運營」不含其他兩個，則FIND函數返回的結果是{1，#VALUE!，VALUE!}，但單元格里顯示的結果不會是這樣，只會是一個VALUE!。這時我們就需要另一個函數來幫助返回一個可以識別的結果。

統計一組值中數字的個數：COUNT函數

如果全部都沒有查找到，COUNT返回的結果是0，如果有一個數字查找成功則返回數字1，兩個數字查找到則返回數字2，三個數字被查找到則返回數字3。用這個可以識別FIND函數的返回結果，結果為「0」是不包含指定文本內容，「1或2或3」則是包含指定文本內容的。下面是列出了不同返回值所對應的職位名稱，可以看出確實是這樣的。

根據指定的條件內容來判斷「TURE」or「FALSE」:IF函數

IF(條件，條件正確返回的結果，條件錯誤返回的結果)

=IF（COUNT(FIND({"數據運營","數據分析","分析師"},L2))>0，「是」，「否」）

即COUNT函數返回的結果大於0則為"是"，否則為"否"，這就實現了我們之前的思路。

（這裡公式里的>0是可以省略的，此時這裡的條件值只要不是"0"或者空值，都是返回條件正確所返回的結果，也就是我們公式里的"是"）

至此，我們的數據清洗工作結束，篩選出結果為"是"的數據，這就是我們要進行分析的數據。

讓我們把篩選出的數據複製粘貼到一個新的表格中，文件加上後綴」清洗數據結果「，後面我們將在這個表格中進行後續的構建模型和數據可視化來分析數據。

構建模型與數據可視化

這裡需要結合我們前面提出的問題，主要利用數據透視表進行。

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

這裡我們需要看到城市與職位之間的關係，首先匯總每個城市所招數據分析師職位的數量，並按計數項降序排列，由此可以看出數據分析這一崗位的主要需求集中在北上廣深及新一線城市，如果去這些城市找數據分析的職位可以提高成功的概率。另外我們選中任一匯總的數據 右擊選中「值顯示方式」 選擇「列匯總的百分比」 得到圖中每個城市職位佔總數的比值。

問題2：數據分師的薪水如何？

這裡我們利用到excel的數據分析功能來進行平均薪水的描述統計分析，首先我們需要安裝excel的數據分析功能，如何安裝步驟如下：

第一步：在excel中依次點擊「文件」 「選項」 「載入項」

第二步：」管理（A）：「處選擇 」excel載入項「 點擊」轉到「

第三步：勾選」分析工具庫「 點擊」確定「

完成上述步驟，在」數據「標籤中就多了一個」數據分析「功能按鈕。點擊該按鈕，並按下圖所示操作，我們就得到了平均薪水的描述統計信息。

並我們用數據透視表功能，對各城市的平均薪水進行匯總並計算平均值，得出各城市的平均薪水，並按照平均值項降序排序，得到如下信息。

由上面的平均薪水的描述信息我們可以看出，整體平均薪水在14.16k中位數為12.5k，這些信息表明了數據分析師職位的整體薪資水平範圍還是不錯的。結合數據透視表可以看出，一線城市的薪水在15k左右，其中深圳的平均薪水最高，其次是北京，上海，杭州。

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

同樣我們用數據透視表來看工作經驗和薪酬之間的關係，首先看不同工作經驗的平均薪水，可以看到隨著工作經驗的提升沒數據分析師的薪水也在不斷提升，10年以上工作經驗的人能獲得相當豐厚的薪酬。再看不同城市不同工作經驗的平均薪水，整體的走向和之前是一樣的。

至此，我們利用Excel進行數據分析的整個過程就結束了，但是excel還有很多功能和函數我們還沒有用到，一次性學會也不可能，只有在實踐過程中邊做邊學，努力充實自己！