七周成為數據分析師:數據分析:手把手教你Excel實戰
作者:秦路 公眾號:秦路(tracykanc)
配套七周成為數據分析師視頻教程:磨劍之作,七周成「師」!秦路主講,七周成為數據分析師
本文是如何七周成為數據分析師的第三篇教程,配套視頻教程:磨劍之作,七周成「師」!秦路主講,七周成為數據分析師,如果想要了解寫作初衷,可以先行閱讀七周指南。溫馨提示:如果您已經熟悉Excel,大可不必再看這篇文章,或只挑選部分。
在Excel函數和Excel技巧後,今天這篇文章講解實戰,如何運用上兩篇文章的知識進行分析。內容是新手向的基礎教程。
曾經有童鞋向我反應沒有Excel數據練習,所以這次提供真實數據。為了更好的了解數據分析師這個崗位,我用爬蟲爬取了招聘網站上約5000條的數據分析師職位數據。拿數據分析師進行數據分析。
數據真實來源於網路,屬於網站方,請勿用於商業用途。
因為微信外鏈限制,請在會話頁發送關鍵詞「練習數據」獲取下載地址。
操作版本:Excel 2016 Mac版。文件大小約2M。
原始數據最好另外保存一份,後期數據可視化、SQL、Python的教程都會用到(主要我懶得找其他數據了)。
演示過程分為五個步驟:明確目的,觀察數據,清洗數據,分析過程,得出結論。
這也是通常數據分析的簡化流程。
————
明確目的
數據分析的大忌是不知道分析方向和目的,拿著一堆數據不知所措。一切數據分析都是以業務為核心目的,而不是以數據為目的。
數據用來解決什麼問題?
是進行匯總統計製作成報表?
是進行數據可視化,作為一張信息圖?
是驗證某一類業務假設?
是希望提高某一個指標的KPI?
永遠不要妄圖在一堆數據中找出自己的結論,太難。目標在前,數據在後。哪怕給自己設立一個很簡單的目標,例如計算業務的平均值,也比沒有方向好。因為有了平均值可以想數字比預期是高了還是低了,原因在哪裡,數據靠譜嗎?為了找出原因還需要哪些數據。
既然有五千多條數據分析師的崗位數據。不妨在看數據前想一下自己會怎麼運用數據。
數據分析師是一個什麼樣的崗位?
它的工資和薪酬是多少?
它有什麼特點,需要掌握哪些能力?
哪類公司更會招聘數據分析師?
等等。
有了目標和方向後,後續則是將目標拆解為實際過程。
觀察數據
拿出數據別急切計算,先觀察數據。
欄位名稱都是英文,我是通過Json獲取的數據,所以整體數據都較為規整。絕大部分數據源的欄位名都是英文。因為比起拼音和漢字,它更適合編程環境下。
先看一下columns的含義:
city:城市
companyFullName:公司全名companyId:公司IDcompanyLabelList:公司介紹標籤companyShortName:公司簡稱companySize:公司大小businessZones:公司所在商區
firstType:職位所屬一級類目 secondType:職業所屬二級類目education:教育要求industryField:公司所屬領域 positionId:職位IDpositionAdvantage:職位福利positionName:職位名稱 positionLables:職位標籤salary:薪水workYear:工作年限要求
數據基本涵蓋了職位分析的所需。職位中的職位描述沒有抓下來,一來純文本不適合這次初級分析,二來文本需要分詞以及文本挖掘,後續有機會再講。
首先看一下哪些欄位數據可以去除。companyId和positionId是數據的唯一標示,類似該職位的身份證號,這次分析用不到關聯vlookup,我們先隱藏。companyFullName和companyShortName則重複了,只需要留一個公司名稱,companyFullName依舊隱藏。
盡量不刪除數據,而是隱藏,保證原始數據的完整,誰知道以後會不會用到呢?
接下來進行數據清洗和轉換。因為只是Excel級別的數據分析,不會有啞變數離散化標準化的操作。我簡單歸納一下。
數據有無缺失值
數據的缺失值很大程度上影響分析結果。引起缺失的原因很多,例如技術原因,爬蟲沒有完全抓去,例如本身的缺失,該崗位的HR沒有填寫。
如果某一欄位缺失數據較多(超過50%),分析過程中要考慮是否刪除該欄位,因為缺失過多就沒有業務意義了。
Excel中可以通過選取該列,在屏幕的右下角查看計數,以此判別有無缺失。
companyLabelList、businessZones、positionLables都有缺失,但不多。不影響實際分析。
數據是否一致化
一致化指的是數據是否有統一的標準或命名。例如上海市數據分析有限公司和上海數據分析有限公司,差別就在一個市字,主觀上肯定會認為是同一家公司,但是對機器和程序依舊會把它們認成兩家。會影響計數、數據透視的結果。
我們看一下表格中的positionName
各類職位千奇百怪啊,什麼品牌保護分析師實習生、足球分析師、商業數據分析、大數據業務分析師、數據合同管理助理。並不是純粹的數據分析崗位。
為什麼呢?這是招聘網站的原因,有些職位明確為數據分析師,有些職位要求具備數據分析能力,但是又干其他活。招聘網站為了照顧這種需求,採用關聯法,只要和數據分析相關職位,都會在數據分析師的搜索結果中出現。我的爬蟲沒有過濾其他數據,這就需要手動清洗。
這會不會影響我們的分析?當然會。像大數據工程師是數據的另外發展方向,但不能歸納到數據分析崗位下,後續我們需要將數據分析強相關的職位挑選出來。
數據是否有臟數據
臟數據是分析過程中很討厭的環節。例如亂碼,錯位,重複值,未匹配數據,加密數據等。能影響到分析的都算臟數據,沒有一致化也可以算。
我們看錶格中有沒有重複數據。
這裡有一個快速竅門,使用Excel的刪除重複項功能,快速定位是否有重複數據,還記得positionId么?因為它是唯一標示,如果重複了,就說明有重複的職位數據。看來不刪除它是正確的。
對positionId列進行重複項刪除操作
有1845個重複值。數據重複了。這是我當時爬取完數據時,將北京地區多爬取一次人為製作出的臟數據。接下來全選所有數據,進行刪除重複項,保留5032行(含表頭欄位)數據。
數據標準結構
數據標準結構,就是將特殊結構的數據進行轉換和規整。
表格中,companyLableList就是以數組形式保存(JSON中的數組)
看來福利倒是不錯,哈哈,不過這會影響我們的分析。businessZones、positionAdvantage和positionLables也是同樣問題,我們後續得將這類格式拆分開來。
薪水的話用了幾K表示,但這是文本,並不能直接用於計算。而且是一個範圍,後續得按照最高薪水和最低薪水拆成兩列。
OK,數據大概都了解了,那麼下一步就是將數據洗乾淨。
數據清洗
數據清洗可以新建Sheet,方便和原始數據區分開來。
先清洗薪水吧,大家肯定對錢感興趣。將salary拆成最高薪水和最低薪水有三種辦法。
一是直接分列,以"-"為拆分符,得到兩列數據,然後利用替換功能刪除 k這個字元串。得到結果。
二是自動填充功能,填寫已填寫的內容自動計算填充所有列。但我這個版本沒有,就不演示了。
三是利用文本查找的思想,重點講一下這個。先用 =FIND("k",O2,1)。查找第一個K(最低薪酬)出現的位置。
我們知道第一個k出現的位置,此時=LEFT(O2,FIND("k",O2,1))得到的結果就是 7K,要去除掉k,FIND("k",O2,1)再減去1即可。
最高薪水也是同樣的思路,但不能使用k,因為第二個薪水位置不固定。需要利用find查找"-"位置,然後截取 從"-" 到最後第二個位置的字元串。
=MID(O2,FIND("-",O2,1)+1,LEN(O2)-FIND("-",O2,1)-1)
因為薪水是一個範圍,我們不可能拿範圍計算平均工資。那怎麼辦呢?我們只能取最高薪水和最低薪水的平均數作為該崗位薪資。這是數據來源的缺陷,因為我們並不能知道應聘者實際能拿多少。這是薪水計算的誤差。
我們檢查一下有沒有錯誤,利用篩選功能快速定位。
居然有#VALUE!錯誤,看一下原因。
原來是大寫K,因為find對大小寫敏感,此時用search函數,或者將K替換成k都能解決。
另外還有一個錯誤是很多HR將工資寫成5K以上,這樣就無法計算topSalar。為了計算方便,將topSalary等於bottomSalary,雖然也有誤差。
這就是我強調數據一致性的原因。
companyLabelList是公司標籤,諸如技能培訓啊、五險一金啊等等。直接用分列即可。大家需要注意,分列會覆蓋掉右列單元格,所以記得複製到最後一列再分。
符號用搜索替換法刪除即可。
positionLables、positionAdvantage、businessZones同樣也可以用分列法。如果觀察過數據會知道,companyLabelList公司標籤都是固定的內容,而其他三個不是。這些都是HR自己填寫,所以就會有各種亂七八糟不統一的描述。
這些內容均是自定義,沒有特別大的分析價值。如果要分析,必須花費很長的時間在清洗過程。主要思路是把這些內容統一成幾十個固定標籤。在這裡我將不浪費時間講解了,主要利用Python分詞和詞典進行快速清洗。
因為時間和性價比問題,positionAdvantage和businessZones我就不分列了。只清洗positionLables職位標籤。某一個職位最多的標籤有13個。
[實習生, 主管, 經理, 顧問, 銷售, 客戶代表, 分析師, 職業培訓, 教育, 培訓, 金融, 證券, 講師]
這個職位叫金融證券分析師助理講師助理,我真不知道為什麼實習生、主管、經理這三個標籤放在一起,我也是嗶了狗了。反正大家數據分析做久了,會遇到很多Magic Data。
接下來是positionName,上文已經講過有各種亂七八糟或非數據分析師職位,所以我們需要排除掉明顯不是數據分析師的崗位。
單獨針對positionName用數據透視表。統計各名稱出現的次數。
出現次數為3次以下的職位,有約一千,都是各類特別稱謂,HR你們為什麼要這樣寫…要這樣寫…這樣寫。更改職位名稱似乎不現實,那就用關鍵詞查找的思路,找出包含有數據分析、分析師、數據運營等關鍵詞的崗位。雖然依舊會有金融分析師這類非純數據的崗位。
用find和數組函數結合,shift+ctrl+enter輸入。就得到了多條件查找後的結果。
=IF(COUNT(FIND({"數據分析","數據運營","分析師"},M33)),"1","0")
單純的find 只會查找數據分析這個詞,必須嵌套count才會變成真數組。
1為包含,0不包含。將1過濾出來,這就是需要分析的最終數據。
當然大家如果感興趣,也可以看一下大數據工程師,數據產品經理這些崗位。
分析過程
分析過程有很多玩法。因為主要數據均是文本格式,所以偏向匯總統計的計算。如果數值型的數據比較多,就會涉及到統計、比例等概念。如果有時間類數據,那麼還會有趨勢、變化的概念。
整體分析使用數據透視表完成,先利用數據透視表獲得匯總型統計。
看來北京的數據分析崗位機會遠較其他城市多。1-3年和3-5年兩個時間段的缺口更大。應屆畢業生似乎比1年一下經驗的更吃香。爬取時間為11月,這時候校招陸續開始,大公司會
有線下校招,實際崗位應該更多。小公司則傾向發布。這是招聘網站的限制。
看一下公司對數據分析師的缺口如何。
似乎是公司越大,需要的數據分析師越多。
但這樣的分析並不準確。因為這只是一個匯總數據,而不是比例數據,我們需要計算的是不同類型企業人均招聘數。
如果北京的互聯網公司特別多,那麼即使有1000多個崗位發布也不算缺口大,如果南京的互聯網公司少,即使只招聘30個,也是充滿需求的。
還有一種情況是企業剛好招聘滿數據分析師,就不發布崗位了,數據包含的只是正在招聘數據分析師的企業,這些都是限制分析的因素。我們要明確。
有興趣大家可以深入研究。
看一下各城市招聘Top5公司。
北京的美團以78個數據分析職位招聘力壓群雄,甚至一定程度上拉高了北京的數據。而個推則在上海和杭州都發布了多個數據分析師職位,不知道是HR的意外,還是要大規模補充業務線(在我寫這篇文章的時候,約有一半職位已經下線)。
比較奇怪的是阿里巴巴並沒有在杭州上榜,看來是該階段招聘需求不大,或者數據分析師有其他招聘渠道。
沒有上榜不代表不要數據分析師,但是上榜的肯定現階段對數據分析師有需求。
我們看一下數據分析師的薪水,可能是大家最感興趣的了。
我們看到南京、西安在應屆生中數據最高,是因為招聘職位不多,因為單獨一兩個企業的高薪影響了平均數,其餘互聯網二線城市同理。當工作年限達到3年以上,北上深杭的數據分析師薪資則明顯高於其他城市。
數據會有誤差性么?會的,因為存在薪資極值影響。而數據透視表沒有中位數選項。我們也可以單獨用分位數進行計算,降低誤差。
薪資可以用更細的維度計算,比如學歷、比如公司行業領域,是否博士生遠高於本科生,是否金融業薪資高於O2O。
另外數據分析師的薪資,可能包括獎金、年終獎、季度獎等隱形福利。部分企業會在positionAdvantage的內容上說明,大家可以用篩選過濾出16薪這類關鍵詞。作為橫向對比。
我們看一下數據分析的職位標籤,數據透視後匯總。
分析師、數據、數據分析是最多的標籤。除此以外,需求分析,BI,數據挖掘也出現在前列。看來不少數據分析師的要求掌握數據挖掘,將標籤和薪水關聯,是另外一種分析思路。職位標籤並不是最優的解法,了解一個職位最好的必然是職位描述。
分析過程不多做篇幅了,這次實戰比較簡單,後續文章會再講解, 主要使用數據透視表進行多維度分析,沒有其他複雜的技巧。下圖很直觀的展現了多維度的應用。
我們的分析也屬於多維度,城市、工作年限、企業大小、企業領域等,利用不同維度形成一個直觀的二位表格,而維度則是通過早期的數據清洗統一化標準化。這是一種很常見的分析技巧。
後續的數據報告,涉及到可視化製作,因為字不如表、表不如圖,就放在第二周講解。
最後多強調幾下:
1.最好的分析,是拿數據分析師們的在職數據,而不是企業招聘數據。
2.承認招聘數據的非客觀性,招聘要求與對數據分析師的實際要求是有差異的。
如果這個數據大家看到其他好玩的,可以一併留言告訴我。
Excel的內容差不多就結束了,之後會開始第二周數據可視化的講解。
磨劍之作,七周成「師」!秦路主講,七周成為數據分析師 磨劍之作,七周成「師」!秦路主講,七周成為數據分析師 雙十一狂歡,全場5折 11.11狂歡
關鍵字:已有 1300人學習 數據分析思維、業務、Excel、數據可視化、MySQL、統計學、Python 已連載到82課時。
推薦閱讀:
※【連載之八】從一個小問題洞察掙錢秘籍,卻被90%的數據分析師忽略
※我有一個夢想——成為數據分析師
※【原創】《如何系統性的搭建自己的知識體系》——個體加速成長的秘密
※七周成為數據分析師:Excel技巧:好用到哭的多級菜單
※軟硬兼備方能成為一名合格的數據分析師