利用EXCEL進行數據分析

本文的數據來源於 @秦路 ，是他爬取於網路，屬於網站方。

@秦路 先生在他的文章里詳細地介紹、演示了如何利用EXCEL進行數據分析。我之所以寫這篇文章是為了對知識的鞏固。

數據的具體情況是某招聘網站上關於數據分析師的詳細招聘信息，例如所在城市、公司名稱、職位名稱以及薪資待遇等。在進行數據分析之前，我們要明確分析目的，不能為了分析而分析。例如我們想知道：

不同城市對數據分析師的需求量；

數據分析師對工作年限的要求；

不同的工作年限對應的薪資待遇等。

只有帶著目的去分析數據，分析才有意義。

在對數據進行分析處理之前，我們應該先觀察數據，觀察數據包含了什麼信息。或者說數據有多少維度，每個維度是什麼意思。

從上圖中可以看到17列，就是說一共有17個維度。列名是英文，具體如下：

city：城市

companyFullName：公司全名

companyId：公司ID

companyLabelList：公司標籤

companyShortName：公司簡稱

companySize：公司大小

businessZones：公司所在商區

firstType：職位所屬一級類目

secondType：職業所屬二級類目

education：學歷要求

industryField：公司所屬領域

positionId：職位ID

positionAdvantage：職位福利

positionName：職位名稱

positionLables：職位標籤

salary：薪水

workYear：工作年限要求

1、數據清理

在數據清理之前，我們應該將數據複製到新的工作表裡，以便和原始數據區分開來。首先是確定哪些列我們不需要。例如這個表裡有公司全名和公司簡稱，我們只要一個就可以了。我們可以將公司全名隱藏（建議隱藏，因為不知道什麼時候要用到）。然後查看缺失值，直接選中該列，在表格的右下角可以查看該列的計數：

刪除重複項

我們對positionID進行刪除重複項，操作是Ctrl+A→數據→刪除重複項→取消全選→選擇positionID→確定

對薪資進行處理

觀察salary列我們可以看到它的描述是幾K到幾K。假設它的平均工資是它的實際工資，我們需要先確定它的最低工資和最高工資。這裡有兩種方法，第一種是分列，第二種是函數。我們用文本函數，因為分列後面也會演示。

Bottomsalary:

=LEFT(P2,SEARCH("k",P2)-1)

要說明的是，在EXCEL函數里，文本類型的數據要加雙引號；用search而不是find是因為find對大小寫敏感。

Topsalary:

=MID(P2,SEARCH("-",P2)+1,LEN(P2)-SEARCH("-",P2)-1)

然後都它們平均就可以了。我們只是計算了第二行的數據，現在將公式複製到其他行：

同時選中R2、S2、T2，複製，單擊R3，在名稱框內輸入R3：T5032

然後回車，粘貼。這樣就完成了其他行的計算。這時發現數據出現了錯誤

原因是有些薪資的描述是幾K以上，這樣就導致了我們的Topsalary里的search函數無法查找到「-」。這時可以用的IFERROR函數

=IFERROR((R2+S2)/2,R2)

然後將它複製到其他行就可以了。

對CompanyLabelList進行分列

可以看到它是由多個標籤聚合在一起的，統一格式有利於分析。所以我們直接用EXCEL的分列功能就可以了。因為分列會覆蓋後面的列，所以要將它複製到最後的列再進行操作。

選擇該列，直接點擊數據菜單下的分列按鈕

然後選擇分隔符號，點擊下一步，在這裡可以選擇合適的分隔符

最後點擊下一步，完成。到這裡就分列完成了。但是這些數據還是帶著標點符號，所有將四列都選中然後用替換將標點符號清除就可以了。

同樣的對PositionLabelList進行分列。

對PositionName進行數據的處理

觀察PositionName列發現它的描述是各式各樣的。數據分析崗主要分為兩大類，一個是偏技術的，另一個是偏運營的。這次主要想分析偏運營的，所有要將類似於大數據工程師這樣的崗位剔除掉。可以運用關鍵詞的思路將崗位區分開來。例如我們選擇數據分析、數據運營、分析師這些關鍵詞。在這裡要用到數組公式

=IF(COUNT(SEARCH({"數據分析","數據運營","分析師"},N2)),"1","0")

記得輸入公式後按shift+ctrl+enter它才會變成數組。1是包含，0是不包含。現在將包含有0的行去掉。將新增的列名該為1，然後再原來首行上增加一行：

然後選擇新增加的列，點擊數據菜單下的篩選按鈕，將包含0的行篩選出來

然後先按ctrl+A，然後按ctrl+G，點擊定位條件，選擇可見單元格，確定。

最後點擊右鍵，選擇刪除行就可以了。如果刪除行後沒有數據的話，點擊一下篩選按鈕就可以了。

到這裡數據的清理就可以了。然後是分析

數據分析

進行數據分析時直接全選，然後在新的工作表裡插入數據透視表。

1、不同城市對數據分析的需求數

從上圖可以看出北京的需求量是最大的，接近一半。之後是上海、深圳和廣州。這裡也側面地反映出北京的互聯網公司的數量是最多的。畢竟互聯網行業不同於其他行業，它是一個知識密集型的，而北京有著22所211大學。杭州緊跟在廣州之後，阿里巴巴對杭州互聯網行業的影響可見一斑。

對工作年限的要求

我們可以知道1-3年的缺口是最大的，其次是3-5年。而工作年限的要求居然排到了第三。在這裡是不是可以理解為數據分析是一個新興的職業？

不同工作年限對應的薪資

10年以上的薪資最高，是29.8K，需求量最大的1-3年的薪資是12.1K。整體來看薪資和年限成正相關。值得注意的是，這並不是真實的薪資，我們並沒有拿到實際數據分析師的收入數據。這只是招聘信息上的平均值。

所在行業

這裡可以看出移動互聯網行業的招聘數數最多的，其次是金融。這裡要說的是如何統計多行多列各文本出現的次數。在前面插入一個空白列如何按ALT+D+P，選擇多重合併計算區域

然後點擊下一步，再點擊下一步，將要統計的列和前面的空白列選中

點擊添加，完成。在新的工作表裡將數據透視表編輯器設置成如下就可以了。

以上。

最後想說的是，在數據分析中要確保數據源的準確，就是說我們要排除重複的、錯誤的、無關的數據。這是我重新做這個練習的體會。