多的是你不知道的excel
一般我們最先接觸的數據分析就是在EXCEL上，但從開始接觸到現在，依然只是用最表面的數據做一些最基礎的記錄和計算，從來沒有深入研究過函數、匹配、透視表等內容。這篇文章就從各個細節手把手的記錄了最基礎的EXCEL數據分析過程，為後期學習數據的可視化提供基礎。
首先，要知道數據分析的過程分為以下5個步驟
下面就按照實踐案例「各個城市數據分析師的招聘信息」來對數據分析師這個職位做一個簡單的分析。用爬蟲軟體對拉勾網上的數據分析師招聘信息進行抓取，導入到Excel中進行分析。
一、提出問題
1）在哪些城市找到數據分析師工作的機會比較大？
2）數據分析師的薪水如何？3）根據工作經驗的不同，薪酬是怎樣變化的？二、理解數據
列名（欄位）：理解數據表上各個列名的含義及意義。
三、數據清洗
*數據類型：首先了解一下Excel中的數據類型，分為3類，字元串、數字和邏輯類型。如下圖
數據清洗的步驟如下圖：
1、選擇子集：選取需要的欄位列，其他不需要的可以隱藏
2、列明重命名：部分英文或其他想改變列名的進行修改列名
3、刪除重複值：全選——刪除重複項——選擇具有唯一標識的列名（如「職位ID」）——顯示出n個重複值——刪除
4、缺失值處理：
- 手動填補（數據較少）：開始-查找-定位-空值
*Ctrl+Eneter快捷鍵：在不連續的單元格中同時輸入同一個數據或公式時很好用
- 刪除缺失的數據
- 用平均值代替缺失值
- 用統計模型計算出的值去代替缺失值
5、一致化處理
數據是否有統一的標識，如所屬領域，有單個的有多個的，需要進行分裂。（注意：分裂的時候會覆蓋右側的單元格內容，需複製到最後一列進行分裂，之前的可以隱藏）
對要進行計算的列，進行一致化和可計算化處理。如，將薪水範圍一列處理為「最低薪水-最高薪水-平均薪水」三列，方便我們對薪資的分析。
*函數
- 平均值
- FIND，返回一個字元串在另一個字元串中出現的起始位置。如，FIND=("K",Q2)
- MID，從文本字元串中指定的位置開始，返回指定長度的字元串。如，MID=(Q5,3,2)
- LEFT，從一個文本字元串的第一個字元開始返回指定個數的字元。如，LEFT=(Q6,2)
- RIGHT，從一個文本字元串的最後一個字元開始返回指定個數的字元。如，RIGHT=(Q6,3)
「最低薪水」用LEFT和FIND函數；「最高薪水」用MID、FIND和LEN函數；可根據公式得出以下結果：
然後檢查一下有沒有錯誤值，通過篩選，可看出有錯誤值，首先看最低薪水一列，是因為薪水的「k」為大寫的「K」，所以造成錯誤。
對這些錯誤值進行查找替換，將K全部替換成k，最低薪水一列的錯誤值就沒有了。
再看最高薪水一列，篩選錯誤值，可以看到是因為工資寫的是多少k以上，所以就可以直接將最高薪水=最低薪水，應用到整列。
修改後，重複篩選下，直到沒有錯誤出現為止。
算平均薪水，使用平均值函數後，顯示錯誤，這裡是因為最低薪水和最高薪水兩列都是字元型數據，不能作為數字進行運算，所以，要對其進行修改。
在最低薪水後插入一列，複製最低薪水一列，選擇性粘貼選擇「數值」粘貼到空白列，但依然還是有綠色的標籤在，所以繼續用分列的功能將數據分列出來。
最後將前面的最低薪水一列隱藏。最高薪水用同方法處理。
6、數據排序，將平均薪水這一列按照降序排列
7、異常值處理
需要使用到數據透視表，其原理是
在對職位名稱的計數項進行降序排列後，可以看到有許多不是數據分析師的招聘需求，也在招聘信息里，因此需要排除。
在職位名稱後，插入一列「數據分析師職位名稱」，引用IF、COUNT、FIND函數判斷是否符合數據分析師的職位名稱。函數=IF(COUNT(FIND({"數據運營","數據分析","分析師"},L2)),"是","否")。
FIND函數找出包含這3個標籤的單元格，返回查找字元串在被查找字元串中開始的位置的數字，一定是大於等於1的數字，未包含的則返回錯誤值。
COUNT函數返回包含數字的單元格，返回的值為包含數字單元格的數字個數。包含這3個標籤的顯示為1，未包含的顯示為0。
IF函數，對COUNT函數結果「1」的值返回為真值，「0」返回為假值。
IF函數
COUNT函數
輸入後就出現了是和否的結果，然後篩選出是的，複製所有的結果到新的EXCEL表，保存為數據清洗結果。
到這裡數據清洗的步驟就基本完成了，下面開始構建模型。
四、構建模型
1、在哪些城市找到數據分師工作的機會比較大？
根據情況，我們需要了解哪個城市數據分析師的工作最好找，就需要看哪個城市的數量最多，還有各城市對於工作年限的要求，根據數據透視表我們構建分析模型。
得出，北京的崗位最多，其次是上海、深圳、廣州、杭州這些大城市。1年以下經驗的職位最多，所以還是有希望的。
也可以通過在總計下面的單元格，右鍵，選擇「列匯總的百分比」
就得出以下結果，也可以看出各個城市的數據分析師招聘數量在總的數量中的佔比。
2、數據分析師的薪水如何？
接下來進行對平均薪水的描述統計，需要安裝分析工具，具體操作如下：
進行描述統計分析，得出平均薪水的平均值，中位數，眾數等描述統計數據。可以看出平均薪水的平均值為14k，眾數為15k，中位數為12.5k，整體來看還是比較不錯的。
再對各個城市的平均薪水進行分析，深圳最高，為15.3K，其次是北京，上海，杭州。
3、根據工作經驗的不同，薪酬是怎樣變化的？
通過透視表，可以看到應屆生、1年以下和不限年限的薪水差距較小，隨著時間變長，薪水增長幅度也變大。
數據可視化的內容在後續的文章中會添加，下面我們再看幾個整理數據的小技巧。
五、數據整理小技巧
（1）日期數據處理
格式不統一時：通過分列里的日期數據，然後設置單元格格式進行調整。
單日數據變月數據：數據透視表，日期下面的單元格右鍵，創建組，選擇「月」「年」就將日期統計方式變成了按月統計。
其中還包括按周匯總數據，天數選擇7天，同時選擇起始的日期。
還可以在數據透視表值欄位設置中選擇最大值，最小值，平均值等，得出每個月中單日數據的最大值或其他。
（2）VLOOKUP函數
1、精確查找姓名對應的班級，進行匹配
2、問題是，遇到重複值只會返回第一個值，這裡用輔助列對數據進行合併，再進行VLOOKUP匹配。所以我們一般選擇查找值時一般選擇具有唯一性的數值，防止重複值被掩蓋。
3、對數據進行分組：建立一個分組定義，進行VLOOKUP查找，選擇模糊匹配「1」，因為這裡匹配的是一個範圍。如下列將消費水平進行分組的例子。
*這裡引入3種引用方式
相對引用：當把公式複製到其他單元格時，行號和列號會隨之變化。
絕對引用：所有單元格引用同一單元格內容，就需要絕對引用，在行號和列號前都需加「$」符號。
推薦閱讀：