Excel數據分析篇：如何用Excel做一次完整的數據分析
今天這篇講實操，教大家用Excel做一次簡單的分析。一是讓大家了解資料分析是一個怎樣的流程；其次熟練Excel的操作(學的知識要利用起來)，包括公式，數據透視表等。
這裡我用Python在智聯招聘上爬取了約1800條的BI工程師的職位信息，并且将崗位名稱、公司名稱、薪水、所在城市、所屬行業、學曆要求、工作年限這些關鍵信息用CSV文件保存下來。
操作版本：Excel 2016 ，WIN 10
一個完整的數據分析都需要經曆這樣幾個步驟：
§ 數據采集 — — 這裡我已經用Python爬好了；
§ 明确分析目的 — — 你拿這數據要得到什麼信息，解決什麼問題；
§ 觀察資料 — — 各個數據字段的含義，中英文釋義；
§ 數據清洗 — — 無效值、缺失值、重複值處理，數據結構是否一緻等；
§ 分析過程 — — 圍繞目的展開分析；
§ 制作可視化 — — 做圖表做可視化報告。
一、明确目的
數據分析的大忌是不知道分析的方向和目的，拿着一堆數據不知所措。數據用來解決什麼問題？
是進行彙總統計制作成報表？
是進行數據可視化，作為一張信息圖？
是驗證某一類業務假設？
是希望提高某一個指标的KPI？
要知道一切數據分析都是以業務為核心目的，所以要找到業務問題的思考點。關于找到問題的切入點，之前資料分析思維篇講過。永遠不要妄圖在一堆數據中找結論，目标在前，數據在後，哪怕是把數據做個平均值比較，也比沒有方向好。每一步嘗試都會引發進一步思考，比如為什麼這個值這麼低，原因在哪裡，這個差異波動有何規律……
所以，分析前不妨先來看一下我們爬的數據：
假設我是一個BI工程師，我想知道：
目前BI工程師的平均薪資水平如何，薪資的區間分布如何
各地區對BI工程師的需求量是多少，哪些地區設崗最多。
不同年限的BI工程師薪資差異如何，3年後我差不多是什麼樣的價位？
薪水較高的公司有哪些？
帶着這樣的問題，那我們的分析就有了方向，後續則是将目标拆解為實際分析展示的過程。
二、了解資料概況
拿到數據肯定是要先看一下的，你想要的數據全不全，拿到的數據有哪些可分析之處。主要就是看數據字段，要了解數據字段的含義：
JobName — — 崗位名稱
Company — — 公司名
Salary — — 薪水
City — — 城市
Jobtype — — 崗位領域
Edulevel — — 學曆要求
WorkingExp — — 工作年限要求
三、數據清洗
接下來進行數據清洗。數據清洗一般包括無效值、缺失值、重複值處理；資料是否有亂碼，錯位現象；資料口徑問題，兩張表的關聯ID名是否一緻；還有是否有統一的标準或命名，如公司名全寫或縮寫的區分。數據轉換則是将數據規整為統一格式處理。因為這是隻是Excel級别的數據分析，且就一張簡單的數據表，不會有太多複雜的操作。這裡簡單總結下。
1、有無缺失值
數據的缺失會很大程度影響分析結果。資料缺失的原因很多，比如數據采集的時候，因為技術的原因，爬蟲沒有完全抓去。但工作上更多的原因是資料入庫的時候就沒有收集全，有沒填有遺漏，這又是數據規範數據治理的話題了。一般來說，如果某一字段數據缺失超過40%~50%，就沒有分析意義了，考慮删除或作其他措施。
看數據有沒有缺失，隻要在Excel中選中該列看計數。
這裡，eduLevel有缺失（1759/1800）但不多，不影響實際分析。
2、髒數據處理
發現jobName列裡面有一些類似BIM工程師的崗位信息，這些應該都是土木行業的工程師，爬去時沒做過濾，還有包含“bim”“BIOS””BIW”等字段。
因為包含多重過濾，這裡我建立輔助列，設立判斷條件，然後進行篩選過濾。
=IF(OR(COUNTIF(A5,”*”&{“bim”,”BIM”,”BIOS”,”BIW”}&”*”)),1,”0")
公式的意思是，如果含有這些字段中的任何一個則為1，否則為0。這裡我們需要篩選出結果為0的數據，總計篩選下來600多條，數據還是很髒的。
多重篩選，還可以用數據選項卡裡的高級篩選功能，就不掩飾了。
3、重複數據
重複數據一般對唯一标識字段來處理，比如用戶ID，訂單ID，公司ID這些，這些字段都代表這一行數據是唯一存在的。嚴格來講，這裡的表應該存在公司ID這一字段，爬取數據的問題，我這就懶得再重爬了，就對Company字段做重複值處理。
這裡有一個快速竅門，使用Excel的删除重複項功能，快速定位是否有重複數據。對company列進行重複項删除操作：
隻剩下562個值了。到此，一些髒數據基本清理的差不多了。
最後，salary有一些數據是“薪資面議”，“校招”的，這裡也一并過濾掉。Jobtype過濾掉汽車、電子等行業，隻留包含IT互聯網行業，最後剩下不到500條數據。
4、數據再加工
一者是salary薪水用了幾K表示，這是文本，不能直接用于計算。而且還是一個範圍，後續得按照最高薪水和最低薪水拆成兩列。
二者由于城市字段存儲有的數據為“城市-區域”格式，例如“上海-徐彙區”，為了方便分析每個城市的數據，最後新增列“城市”，截取“-”前面的真實城市數據。
為了方便整理，和原數據區分，也防止原數據丢失，這裡把之前處理的數據複制粘貼到另一張表裡。
① 薪水處理
将salary拆成最高薪水和最低薪水有三種辦法。
一是直接分列，以”-”為拆分符，得到兩列數據，然後利用替換功能删除 k這個字符串。得到結果。
二是自動填充功能，填寫已填寫的内容自動計算填充所有列。
三是利用文本查找，重點講一下這個。
寫公式的思路是，先查找第一個K出現的位置，然後再-1，去除掉K。所以公式是：
=LEFT(C2,FIND(“K”,C2,1)-1)
同樣的思路，最高薪水需要利用find查找”-”位置,然後截取 從”-” 到最後第二個位置的字符串。
=MID(C2,FIND(“-”,C2,1)+1,LEN(C2)-FIND(“-”,C2,1)-1)
這裡，在新增數據列，平均薪水，來近似代表實際的準确薪資。平均薪水=(薪水下限+薪水上限)/2，即可得到每個崗位的平均薪水。
②真實城市截取
由于城市字段存儲有的數據為“城市-區域”格式，例如“上海-徐彙區”，為了方便分析每個城市的數據，最後新增列“城市”，截取“-”前面的真實城市數據。
=IF(COUNTIF(G2,”*-*”)=0,G2,LEFT(G2,FIND(“-”,G2,1)-1))
至此，所有數據清洗加工完畢，食材已經全部準備好，下面可以正式開始數據可視化的美食下鍋烹饪了。
四、分析過程
分析過程有很多玩法，因為這裡主要數據均是文本格式，資料又很簡單，所以偏向彙總統計的計算。如果數值型的資料比較多，就會涉及到統計、比例等概念。如果有時間類數據，那麼還會有趨勢、變化的概念。
整體分析使用數據透視表完成，先利用數據透視表獲得彙總型統計。
1、BI工程師需求概況分析
這裡我簡單加了一下增材區分，增加數據大小的辨識度。（條件格式 — — 色階）
看來北上廣深的BI工程師崗位遠多于其他城市，成都杭州武漢梯隊次之。1~3年以及3~5年經驗的缺口相當。
2、BI工程薪資情況分析
各經驗年齡的平均薪資狀況，差距梯度還是很明顯的。
目前市面上BI工程的薪資主要分許在7~17K左右區間。23~26K，應該是5~10年左右經驗的崗位也相當。
3、薪資變化随着經驗的增長，學曆影響力的大小
整體來說，BI工程師大專和本科的薪資差異并不是很大，3~5年經驗，本科稍占優勢。到5~10年，基本拉平，也就是說學曆因素影響比重更弱，這時候更看重經驗。
其他的分析過程就不多做贅述了，主要是使用數據透視表和數據透視圖進行多維度（城市，學曆，工作經驗）的分析，沒有其他複雜的技巧。
關于資料透視圖和數據透視表。選中所要分析的數據列，2013版以上的Excel基本上都很智能的幫你推薦圖标，生成透視接口，隻要分清楚拖拽的字段事到列，到值還是到行即可。然後視情況多數據做一定篩選，因為數據清洗得不一定很徹底，我在制作的過程中就忽略了一些字段的空缺值，又回過頭做了過濾。
最後
到此，一個簡單的數據分析基本結束了。因為資料簡單，并沒有涉及過多的資料整合，表合并，專業資料統計回歸等操作。
整個數據分析過程最費時間的數據清理，大約占據70%，隻要明确了目的，可視化分析師很簡單的。
其次，也可以看到，用Excel做分析，更多的優勢是數據的簡單處理。随便過濾、查詢、定位救你呢了解數據的概況。但在可視化方面比較雞肋，行列值選擇，以及複雜的圖表制作都有一些難度，一句話總結Excel可視化要想做的好看還是要費點時間的。
所以我在分析的時候，基本上就是用Excel看看數據全貌，簡單處理下。分析、可視化什麼的還是會交給BI。後面，我會再出一篇用BI制作的教程。
最新文章
-
太師修行問答48：人生隻是演戲瑜伽也能修禅，能達到幾禅
7分鐘前 -
![[書法]閑暇草書彙03](https://m.74hao.com/zb_users/upload/2024/10/202410161729086639361289.png)
[書法]閑暇草書彙03
24分鐘前 -
3D技巧：兩碼和差選号法
46分鐘前 -
“落日無情最有情，遍催萬樹暮蟬鳴”楊萬裡詩五首，風格清新自然
1小時前 -
《玄理賦》五行生克的太過不及 等
1小時前 -
《老山1984-1989，中越老山之戰實錄》連載之二：118團8連收敲掉54
2小時前 -
攝影：故鄉的秋色（2）
2小時前 -
論入宅歸火擇吉（續）第二，坐向要吉 坐向要吉，主要是指所選日課
2小時前
有話要說...