實戰:如何用Excel做一次完整的資料分析

數據分析那些事
數據分析不是個事
10 min readMar 1, 2019

此文是《10周入門數據分析》系列的第5篇
想了解學習路線,可以先閱讀「10周計劃」

前一篇分享了Excel資料分析必掌握的43個公式,今天這篇講實操,教大家用Excel做一次簡單的分析。一是讓大家瞭解資料分析是一個怎樣的流程;其次熟練Excel的操作(學的知識要利用起來),包括公式,樞紐分析表等。

這裡我用Python在智聯招聘上爬取了約1800條的BI工程師的職位資訊,並且將崗位名稱、公司名稱、薪水、所在城市、所屬行業、學歷要求、工作年限這些關鍵資訊用CSV檔保存下來。

From 數據分析那些事

操作版本:Excel 2016 ,WIN 10

一個完整的資料分析都需要經歷這樣幾個步驟:

§ 資料獲取 — — 這裡我已經用Python爬好了;

§ 明確分析目的 — — 你拿這資料要得到什麼資訊,解決什麼問題;

§ 觀察資料 — — 各個資料欄位的含義,中英文釋義;

§ 資料清洗 — — 無效值、缺失值、重複值處理,資料結構是否一致等;

§ 分析過程 — — 圍繞目的展開分析;

§ 製作視覺化 — — 做圖表做視覺化報告。

一、明確目的

資料分析的大忌是不知道分析的方向和目的,拿著一堆資料不知所措。資料用來解決什麼問題?

是進行匯總統計製作成報表?

是進行資料視覺化,作為一張資訊圖?

是驗證某一類業務假設?

是希望提高某一個指標的KPI?

要知道一切資料分析都是以業務為核心目的,所以要找到業務問題的思考點。關於找到問題的切入點,之前資料分析思維篇講過。永遠不要妄圖在一堆資料中找結論,目標在前,資料在後,哪怕是把資料做個平均值比較,也比沒有方向好。每一步嘗試都會引發進一步思考,比如為什麼這個值這麼低,原因在哪裡,這個差異波動有何規律……

所以,分析前不妨先來看一下我們爬的資料:

From 數據分析那些事

假設我是一個BI工程師,我想知道:

目前BI工程師的平均薪資水準如何,薪資的區間分佈如何

各地區對BI工程師的需求量是多少,哪些地區設崗最多。

不同年限的BI工程師薪資差異如何,3年後我差不多是什麼樣的價位?

薪水較高的公司有哪些?

帶著這樣的問題,那我們的分析就有了方向,後續則是將目標拆解為實際分析展示的過程。

二、瞭解資料概況

From 數據分析那些事

拿到資料肯定是要先看一下的,你想要的資料全不全,拿到的資料有哪些可分析之處。主要就是看資料欄位,要瞭解資料欄位的含義:

JobName — — 崗位名稱

Company — — 公司名

Salary — — 薪水

City — — 城市

Jobtype — — 崗位領域

Edulevel — — 學歷要求

WorkingExp — — 工作年限要求

三、數據清洗

接下來進行資料清洗。資料清洗一般包括無效值、缺失值、重複值處理;資料是否有亂碼,錯位現象;資料口徑問題,兩張表的關聯ID名是否一致;還有是否有統一的標準或命名,如公司名全寫或縮寫的區分。資料轉換則是將資料規整為統一格式處理。因為這是只是Excel級別的資料分析,且就一張簡單的資料表,不會有太多複雜的操作。這裡簡單總結下。

1、有無缺失值

資料的缺失會很大程度影響分析結果。資料缺失的原因很多,比如資料獲取的時候,因為技術的原因,爬蟲沒有完全抓去。但工作上更多的原因是資料入庫的時候就沒有收集全,有沒填有遺漏,這又是資料規範資料治理的話題了。一般來說,如果某一欄位資料缺失超過40%~50%,就沒有分析意義了,考慮刪除或作其他措施。

看資料有沒有缺失,只要在Excel中選中該列看計數。

這裡,eduLevel有缺失(1759/1800)但不多,不影響實際分析。

2、髒資料處理

發現jobName列裡面有一些類似BIM工程師的崗位資訊,這些應該都是土木行業的工程師,爬去時沒做過濾,還有包含“bim”“BIOS””BIW”等欄位。

因為包含多重過濾,這裡我建立輔助列,設立判斷條件,然後進行篩選過濾。

From 數據分析那些事

=IF(OR(COUNTIF(A5,”*”&{“bim”,”BIM”,”BIOS”,”BIW”}&”*”)),1,”0")

公式的意思是,如果含有這些欄位中的任何一個則為1,否則為0。這裡我們需要篩選出結果為0的資料,總計篩選下來600多條,資料還是很髒的。

多重篩選,還可以用資料選項卡裡的高級篩選功能,就不掩飾了。

3、重復資料

重復資料一般對唯一標識欄位來處理,比如使用者ID,訂單ID,公司ID這些,這些欄位都代表這一行資料是唯一存在的。嚴格來講,這裡的表應該存在公司ID這一欄位,爬取資料的問題,我這就懶得再重爬了,就對Company欄位做重複值處理。

這裡有一個快速竅門,使用Excel的刪除重複項功能,快速定位是否有重復資料。對company列進行重複項刪除操作:

From 數據分析那些事

只剩下562個值了。到此,一些髒資料基本清理的差不多了。

最後,salary有一些資料是“薪資面議”,“校招”的,這裡也一併過濾掉。Jobtype過濾掉汽車、電子等行業,只留包含IT互聯網行業,最後剩下不到500條資料。

4、資料再加工

一者是salary薪水用了幾K表示,這是文本,不能直接用於計算。而且還是一個範圍,後續得按照最高薪水和最低薪水拆成兩列。

二者由於城市欄位存儲有的資料為“城市-區域”格式,例如“上海-徐匯區”,為了方便分析每個城市的資料,最後新增列“城市”,截取“-”前面的真實城市資料。

為了方便整理,和原資料區分,也防止原資料丟失,這裡把之前處理的資料複製粘貼到另一張表裡。

① 薪水處理

將salary拆成最高薪水和最低薪水有三種辦法。

一是直接分列,以”-”為拆分符,得到兩列資料,然後利用替換功能刪除 k這個字串。得到結果。

二是自動填充功能,填寫已填寫的內容自動計算填充所有列。

三是利用文本查找,重點講一下這個。

寫公式的思路是,先查找第一個K出現的位置,然後再-1,去除掉K。所以公式是:

=LEFT(C2,FIND(“K”,C2,1)-1)

From 數據分析那些事

同樣的思路,最高薪水需要利用find查找”-”位置,然後截取 從”-” 到最後第二個位置的字串。

=MID(C2,FIND(“-”,C2,1)+1,LEN(C2)-FIND(“-”,C2,1)-1)

這裡,在新增資料列,平均薪水,來近似代表實際的準確薪資。平均薪水=(薪水下限+薪水上限)/2,即可得到每個崗位的平均薪水。

From 數據分析那些事

②真實城市截取

由於城市欄位存儲有的資料為“城市-區域”格式,例如“上海-徐匯區”,為了方便分析每個城市的資料,最後新增列“城市”,截取“-”前面的真實城市資料。

=IF(COUNTIF(G2,”*-*”)=0,G2,LEFT(G2,FIND(“-”,G2,1)-1))

至此,所有資料清洗加工完畢,食材已經全部準備好,下面可以正式開始資料視覺化的美食下鍋烹飪了。

四、分析過程

分析過程有很多玩法,因為這裡主要資料均是文本格式,資料又很簡單,所以偏向匯總統計的計算。如果數值型的資料比較多,就會涉及到統計、比例等概念。如果有時間類資料,那麼還會有趨勢、變化的概念。

整體分析使用樞紐分析表完成,先利用樞紐分析表獲得匯總型統計。

1、BI工程師需求概況分析

From 數據分析那些事

這裡我簡單加了一下增材區分,增加資料大小的辨識度。(條件格式 — — 色階)

看來北上廣深的BI工程師崗位遠多於其他城市,成都杭州武漢梯隊次之。1~3年以及3~5年經驗的缺口相當。

2、BI工程薪資情況分析

From 數據分析那些事

各經驗年齡的平均薪資狀況,差距梯度還是很明顯的。

From 數據分析那些事

目前市面上BI工程的薪資主要分許在7~17K左右區間。23~26K,應該是5~10年左右經驗的崗位也相當。

3、薪資變化隨著經驗的增長,學歷影響力的大小

From 數據分析那些事

整體來說,BI工程師大專和本科的薪資差異並不是很大,3~5年經驗,本科稍佔優勢。到5~10年,基本拉平,也就是說學歷因素影響比重更弱,這時候更看重經驗。

其他的分析過程就不多做贅述了,主要是使用樞紐分析表和資料透視圖進行多維度(城市,學歷,工作經驗)的分析,沒有其他複雜的技巧。

關於資料透視圖和樞紐分析表。選中所要分析的資料列,2013版以上的Excel基本上都很智慧的幫你推薦圖示,生成透視介面,只要分清楚拖拽的欄位事到列,到值還是到行即可。然後視情況多資料做一定篩選,因為資料清洗得不一定很徹底,我在製作的過程中就忽略了一些欄位的空缺值,又回過頭做了過濾。

From 數據分析那些事

最後

到此,一個簡單的資料分析基本結束了。因為資料簡單,並沒有涉及過多的資料整合,表合併,專業資料統計回歸等操作。

整個資料分析過程最費時間的資料清理,大約佔據70%,只要明確了目的,視覺化分析師很簡單的。

其次,也可以看到,用Excel做分析,更多的優勢是資料的簡單處理。隨便過濾、查詢、定位救你呢瞭解資料的概況。但在視覺化方面比較雞肋,行列值選擇,以及複雜的圖表製作都有一些難度,一句話總結Excel視覺化要想做的好看還是要費點時間的。

所以我在分析的時候,基本上就是用Excel看看資料全貌,簡單處理下。分析、視覺化什麼的還是會交給BI。後面,我會再出一篇用BI製作的教程。

關於學習計畫

本文是《10周入門資料分析》系列的第5篇,小編因爲在大陸工作了5年,所以爬取了大陸比較火的招聘網站,而且這篇文章,是小編之前做的,也在大陸發佈過,表格有些不清晰,請見諒。

可以戳下“瞭解更多”關注小編主頁。

想瞭解更多的資料分析知識,請關注我的Facebook, 期待你與我互動起來啦~

--

--

數據分析那些事
數據分析不是個事

這是一個專注於數據分析職場的內容部落格,聚焦一批數據分析愛好者,在這裡,我會分享數據分析相關知識點推送、(工具/書籍)等推薦、職場心得、熱點資訊剖析以及資源大盤點,希望同樣熱愛數據的我們一同進步! 臉書會有更多互動喔:https://www.facebook.com/shujvfenxi/