如何七周成為數據分析師03:手把手教你Excel實戰

34 評論 46835 瀏覽 190 收藏 24 分鐘

本文是?《如何七周成為數據分析師》的第三篇教程,如果想要了解寫作初衷,可以先行閱讀七周指南。溫馨提示:如果您已經熟悉Excel,大可不必再看這篇文章,或只挑選部分。

在??《如何七周成為數據分析師01:常見的Excel函數全部涵蓋在這里了》?和《如何七周成為數據分析師02:Excel技巧大揭秘》?后,今天這篇文章講解實戰,如何運用上兩篇文章的知識進行分析。內容是新手向的基礎教程。

為了更好的了解數據分析師這個崗位,我用爬蟲爬取了招聘網站上約5000條的數據分析師職位數據。拿數據分析師進行數據分析。

數據真實來源于網絡,屬于網站方,請勿用于商業用途。

操作版本:Excel 2016 Mac版。文件大小約2M。

演示過程分為五個步驟:明確目的,觀察數據,清洗數據,分析過程,得出結論。

這也是通常數據分析的簡化流程。

明確目的

數據分析的大忌是不知道分析方向和目的,拿著一堆數據不知所措。一切數據分析都是以業務為核心目的,而不是以數據為目的。

  • 數據用來解決什么問題?
  • 是進行匯總統計制作成報表?
  • 是進行數據可視化,作為一張信息圖?
  • 是驗證某一類業務假設?
  • 是希望提高某一個指標的KPI?

永遠不要妄圖在一堆數據中找出自己的結論,太難。目標在前,數據在后。哪怕給自己設立一個很簡單的目標,例如計算業務的平均值,也比沒有方向好。因為有了平均值可以想數字比預期是高了還是低了,原因在哪里,數據靠譜嗎?為了找出原因還需要哪些數據。

既然有五千多條數據分析師的崗位數據。不妨在看數據前想一下自己會怎么運用數據。

  • 數據分析師是一個什么樣的崗位?
  • 它的工資和薪酬是多少?
  • 它有什么特點,需要掌握哪些能力?
  • 哪類公司更會招聘數據分析師?

等等。

有了目標和方向后,后續則是將目標拆解為實際過程。

觀察數據

拿出數據別急切計算,先觀察數據。

字段名稱都是英文,我是通過Json獲取的數據,所以整體數據都較為規整。絕大部分數據源的字段名都是英文。因為比起拼音和漢字,它更適合編程環境下。

先看一下columns的含義:

city:城市
companyFullName:公司全名
companyId:公司ID
companyLabelList:公司介紹標簽
companyShortName:公司簡稱
companySize:公司大小
businessZones:公司所在商區
firstType:職位所屬一級類目
secondType:職業所屬二級類目
education:教育要求
industryField:公司所屬領域
positionId:職位ID
positionAdvantage:職位福利
positionName:職位名稱
positionLables:職位標簽
salary:薪水
workYear:工作年限要求

數據基本涵蓋了職位分析的所需。職位中的職位描述沒有抓下來,一來純文本不適合這次初級分析,二來文本需要分詞以及文本挖掘,后續有機會再講。

首先看一下哪些字段數據可以去除。companyId和positionId是數據的唯一標示,類似該職位的身份證號,這次分析用不到關聯vlookup,我們先隱藏。companyFullName和companyShortName則重復了,只需要留一個公司名稱,companyFullName依舊隱藏。

盡量不刪除數據,而是隱藏,保證原始數據的完整,誰知道以后會不會用到呢?

接下來進行數據清洗和轉換。因為只是Excel級別的數據分析,不會有啞變量離散化標準化的操作。我簡單歸納一下。

數據有無缺失值

數據的缺失值很大程度上影響分析結果。引起缺失的原因很多,例如技術原因,爬蟲沒有完全抓去,例如本身的缺失,該崗位的HR沒有填寫。

如果某一字段缺失數據較多(超過50%),分析過程中要考慮是否刪除該字段,因為缺失過多就沒有業務意義了。

Excel中可以通過選取該列,在屏幕的右下角查看計數,以此判別有無缺失。

companyLabelList、businessZones、positionLables都有缺失,但不多。不影響實際分析。

數據是否一致化

一致化指的是數據是否有統一的標準或命名。例如上海市數據分析有限公司和上海數據分析有限公司,差別就在一個市字,主觀上肯定會認為是同一家公司,但是對機器和程序依舊會把它們認成兩家。會影響計數、數據透視的結果。

我們看一下表格中的positionName

各類職位千奇百怪啊,什么品牌保護分析師實習生、足球分析師、商業數據分析、大數據業務分析師、數據合同管理助理。并不是純粹的數據分析崗位。

為什么呢?這是招聘網站的原因,有些職位明確為數據分析師,有些職位要求具備數據分析能力,但是又干其他活。招聘網站為了照顧這種需求,采用關聯法,只要和數據分析相關職位,都會在數據分析師的搜索結果中出現。我的爬蟲沒有過濾其他數據,這就需要手動清洗。

這會不會影響我們的分析?當然會。像大數據工程師是數據的另外發展方向,但不能歸納到數據分析崗位下,后續我們需要將數據分析強相關的職位挑選出來。

數據是否有臟數據

臟數據是分析過程中很討厭的環節。例如亂碼,錯位,重復值,未匹配數據,加密數據等。能影響到分析的都算臟數據,沒有一致化也可以算。

我們看表格中有沒有重復數據。

這里有一個快速竅門,使用Excel的刪除重復項功能,快速定位是否有重復數據,還記得positionId么?因為它是唯一標示,如果重復了,就說明有重復的職位數據??磥聿粍h除它是正確的。

對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,數據挖掘也出現在前列??磥聿簧贁祿治鰩煹囊笳莆諗祿诰颍瑢撕灪托剿P聯,是另外一種分析思路。職位標簽并不是最優的解法,了解一個職位最好的必然是職位描述。

分析過程不多做篇幅了,這次實戰比較簡單,后續文章會再講解, 主要使用數據透視表進行多維度分析,沒有其他復雜的技巧。下圖很直觀的展現了多維度的應用。

我們的分析也屬于多維度,城市、工作年限、企業大小、企業領域等,利用不同維度形成一個直觀的二位表格,而維度則是通過早期的數據清洗統一化標準化。這是一種很常見的分析技巧。

后續的數據報告,涉及到可視化制作,因為字不如表、表不如圖,就放在第二周講解。

最后多強調幾下:

  1. 最好的分析,是拿數據分析師們的在職數據,而不是企業招聘數據。
  2. 承認招聘數據的非客觀性,招聘要求與對數據分析師的實際要求是有差異的。

如果這個數據大家看到其他好玩的,可以一并留言告訴我。

Excel的內容差不多就結束了,之后會開始第二周數據可視化的講解。

相關閱讀

互聯網數據分析能力的養成,需一份七周的提綱

如何七周成為數據分析師01:常見的Excel函數全部涵蓋在這里了

如何七周成為數據分析師02:Excel技巧大揭秘

#專欄作家#

秦路,微信公眾號ID:tracykanc,人人都是產品經理專欄作家。

本文由 @秦路?原創發布于人人都是產品經理。未經許可,禁止轉載。

題圖來自PEXELS,基于CCO協議

更多精彩內容,請關注人人都是產品經理微信公眾號或下載App
評論
評論請登錄
  1. 文章里的圖片都看不到呢~~~~

    來自山東 回復
  2. 學不會。還是買本書好好看。

    回復
  3. 老師,可以分享一下數據嗎?749958200@qq.com 謝謝~

    來自上海 回復
  4. 老師,可以分享源數據嗎?997134105@qq.com,非常感謝~

    來自浙江 回復
  5. 老師,可以分享下源數據嗎?1534701682@qq.com

    來自四川 回復
    1. 拿到數據了 嗎

      來自四川 回復
    2. 請問能發一下源文件嗎?

      來自四川 回復
    3. 來自四川 回復
  6. 老師,可以分享源數據嗎

    來自北京 回復
    1. 拿到數據了嗎

      來自四川 回復
  7. 老師,能分享一下源數據嗎?

    來自重慶 回復
  8. 這個數據 老師還有嗎 可以讓我吸晴裝一份嗎

    回復
  9. 老師,能分享一下案例數據嗎?謝謝

    來自安徽 回復
  10. 請教~輸完公式后,只返回那一行的值啊,有沒有不用往下拖就能對整列做相同操作呢?

    來自安徽 回復
  11. 數據下載,https://pan.baidu.com/s/1pK895v9,提取密碼 6x2v,不謝

    回復
    1. 鏈接封了,能方便再發一次嗎?拜謝!!?。?/p>

      來自安徽 回復
    2. 您好。您有數據了嗎,可以發我一份嗎,郵箱13020039292@qq.com

      來自北京 回復
    3. 同求數據下載鏈接,拜謝??!

      來自廣東 回復
    4. 您好,可以共享一下數據嗎?786954743@qq.com. 多謝!

      來自遼寧 回復
    5. 您好,可以分享一下數據嗎?1542037307@qq.com 謝謝

      來自重慶 回復
    6. 同求,郵箱812541076@qq.com

      來自上海 回復
    7. 大神,求分享數據~~~

      來自陜西 回復
    8. 可以分享下數據嗎,謝謝 594525322@qq.com

      來自江蘇 回復
    9. 你好,請問有源數據嘛,麻煩發一份,郵箱2428054774@qq.com

      來自浙江 回復
  12. “將topSalary等于bottomSalary”,如何操作的呢?

    來自北京 回復
    1. 有沒有解決了,我也有同樣的困擾

      來自浙江 回復
    2. 我的salary是P列,top和bottom分別是S列和R列。
      =IF(ISERROR(MID(P2,SEARCH(“-“,P2,1)+1,LEN(P2)-SEARCH(“-“,P2,1)-1)),R2,MID(P2,SEARCH(“-“,P2,1)+1,LEN(P2)-SEARCH(“-“,P2,1)-1))
      思路是判斷bottom是否有錯誤,正確則正常顯示,錯誤則顯示top的值。就是寫的太長了,不知道有啥簡便的方式沒。

      來自北京 回復
    3. ?? 主要是求top時函數長,方法我覺得可以

      來自浙江 回復
  13. 老師,能分享一下案例數據嗎?謝謝

    來自北京 回復
  14. 能不能把案例的源數據發給我,我想跟著做一遍!謝謝老師!

    來自福建 回復
  15. 希望大神可以開一個如何爬取數據的課 ??

    來自江蘇 回復
  16. 有源數據提供么?

    來自河北 回復
  17. 這份數據能下載嗎?自己不會爬數據啊。。

    來自浙江 回復
  18. 認知數據,努力學習,了解概率學,爭取趕上下一波人工智能大數據列車

    來自湖北 回復