如何通過使用EXCEL進行數據分析

  在「關於EXCEL的頓悟」這篇文章中我指出,EXCEL的主要功能就是數據處理和數據分析,其中數據分析的功能更重要。因為在實際工作中我們經常需要向公司,向主管提交各種表格或者圖表。簡單一點的就直接將EXCEL文件提交上去,要求高一點的還要做成PPT的格式後在提交上去。這些數據表格或者圖表有兩個作用。初級層次的作用是可以向主管匯報我們的工作成果。高級層次的作用是向主管提供輔助其決策的數據。如果員工提供的表格或者圖表經常是基於第二種目的,那麼說明這位員工的職位不會太低。很有可能就是中層管理人員。就算不是管理人員也肯定是薪酬等級比較高的專業技術類崗位,譬如企劃部門中的業務數據分析人員,總經辦的戰略制定人員,市場行銷部的專業策劃人員,高級薪酬專員(主管)等。

羅嗦這麼多還是對數據分析的定義和作用做一下簡單的概括。數據分析是為了提取有用信息和形成結論,而對數據加以詳細研究和概括、總結的過程。在實際工作中,數據分析能夠幫助管理者進行判斷和決策,以便採取適當策略與行動。譬如,公司高層希望通過市場分析和研究,把握當前產品的市場動向,制定合理的產品研發和銷售計劃。這就必須依賴數據分析才能完成。

在開始正題之前,再闡述下EXCEL的兩個功能(數據處理、數據分析)間的關係。應該說它們是互為因果關係。譬如,做數據分析的前提是要有比較完全、準確的原始數據(字段要全,數據要準)。但在設計原始數據表的字段時又要參考數據分析的目的,根據分析目的來設計到底要設幾個字段。譬如,如果你要分析公司不同部門、不同工齡段的員工其薪酬水平對比,那麼在原始數據表中至少要包含部門、工齡、崗位薪水這三個字段。

關於數據分析有專門的學術理論和方法。譬如我在浙大讀MBA的時侯有兩門課(管理運籌學、商務與經濟統計)就是講在實際的管理工作中如何進行數據分析。應該說這兩門課已經是屬於實戰型或者工具型的了。但我認為還是認為太專業、太複雜。我們感覺99%的同學在MBA畢業後的工作中,裡面教的99%的內容是用不上的。據根工作經驗,我認為99%的職場人士中所做的99%的數據分析工作可以用EXCEL處理,並不需要用專業的數據分析軟體。而且也只用到了EXCEL中20%的功能。具體來說你只要會用數據透視圖就行,而且只需了解生成透視圖,會拖拉字段就行,不需要了解關於數據透視圖的全部功能。另外,常見的數據分析方法也就大概五種。寫到這裡,我想等我有空了一定要寫一篇文章,叫《EXCEL應納入MBA的必修課》。不少課程的實用性還真沒EXCEL強。把常見的管理方法總結一下,然後教學生們如何通過EXCEL來做到,這樣才更體現MBA的實戰性特點。

好了,不羅嗦了。下面從兩方面來講如何通過使用EXCEL進行數據分析。首先,介紹下常見的數據分析方法。其次,介紹通過使用數據透視圖來做到交叉分析表的注意事項。

第一,常見的數據分析方法。根據個人的實踐經驗,一共就五種。其它高級一點的等以後有機會再寫文章交流。

1.對比分析法。指將兩個或者兩個以上的數據進行比較,分析它們的差異,從而揭示這些數據所代表的事務發展變化情況和規律性。對比分析法的特點:可以非常直觀地看出事物某方面的變化或差距,並且可以準確、量化地表示出這種變化或差距是多少。對比分析法分為靜態比較和動態比較兩類。靜態比較指在同一時間條件下對不同總體指標的比較,如不同部門、不同地區、不同國家的比較。這也叫橫向比較,簡稱橫比。動態比較是在統一總體條件下,對不同時期指標數值的比較。也叫縱向比較,簡稱縱比。下面舉一些例子:

如何通過使用EXCEL進行數據分析

如何通過使用EXCEL進行數據分析

2.分組分析法。就是把總體中具有不同性質的對象分開,把性質相同的對象合併在一起,保持各組內對象屬性的一致性、組與組之間屬性的差異性,以便進一步運用各種數據分析方法來解構內在的數量關係,因此分組發必須與對比法結合使用。分組分析法的關鍵是確定組數和組距。至於組的數量一般根據數據特點及業務特點來定。總體原則是組數不要太多也不要太少。譬如,要分析不同工齡段、不同崗位等級員工的平均薪水情況。一般的員工都是每過2-3年是一道坎,若沒跳槽又會穩定一段時間。所以我們就以3年為一組,起始年齡從22歲開始,截止統計年齡到37歲止。一般來說大於37的員工就失去分析意義。這樣一共分5組。組距=(最大值-最小值)/組數,結果為3年。當然,根據數據分布情況,組距也可以是不等長的。這裡,我們發現,在原始數據中都是一條一條單獨的數據,分組數據是靠我們通過數據處理的方式產生的。當然,生成分組數據也是有比較方便的方法的,我們可以用VLOOKUP函數來完成。具體如何做在其它文章介紹。示例如下:

如何通過使用EXCEL進行數據分析

3.結構分析法。指被分析研究總體內各部分與總體之間進行對比的分析方法,即各部分占總體的百分比。示例如下:

如何通過使用EXCEL進行數據分析

4.平均分析法。指運用計算平均數的方法來反映總體在一定時間、地點條件下某一數量特徵的一般水平。平均數有算術平均數、調和平均數、幾何平均數、眾數平均數等。常用的就是算術平均數。計算公式=總數/個數。示例如下:

如何通過使用EXCEL進行數據分析

5.二維表法。在我的「關於EXCEL的頓悟」這篇文章中我已對二維表的定義進行了解釋。也做了示例。這種數據分析方法是平時大家用的最多的。用專業術語來講,它是交叉分析法的一種。它通常用於分析多個變量(字段)之間的關係,即同時將多個有一定關係的變量及其值交叉排列在一張表格內,使各變量值成為不同變量的交叉結點,形成交叉表,從而分析交叉表中變量之間的關係,所以也叫交叉表分析法。這裡特意示例一張三個變量的表格:

所屬部門

職位

本科

研究生

專科

銷售部

經理

1

職員

4

主管

1

財務部

經理

1

職員

3

主管

1

技術部

經理

1

職員

3

2

主管

1

第二,相信大家都會用數據透視圖。就算現在還不會,只要花短短幾分鐘就可以馬上學會。但相信大家感覺在用透視圖時對到底要分析那些字段比較困惑。還有就是這些字段應該是放在行字段的地方還是列字段的地方也比較困惑。對於變量超過三個時這些困惑可能更加強烈。應該說,還是有一些方法可以較少這些困惑。首先從字段擺放位置來說,我們建議列字段上最好不多於一個變量。因為瘦瘦高高的表比矮矮扁扁的表好看或者方便看。另外,在做表格前,需要自己先表達清楚你的分析結構。我們可以嘗使用「欲分析,***在***的***,分析目的為***」這樣的語法結構來表達你的分析結構。「在」之前的內容就是放在行字段的變量。「的」之前的內容就是放在列字段的變量。「的」之後的內容就是放在數值區域內的字段和匯總方式。如果同一個內容區域中有多個變量,那麼最前的就是第一字段,依次類推。譬如,我想分析下不同部門,不同崗位的員工,其離職員工和在職的平均薪水的差異。我試圖從這個分析緯度看看部門內部的薪酬公平性對離職的是否有影響。如果通過數據分析上可以看出規律的話,我們可以通過訪談的方式來進一步驗證了。這時,我們可以用上面的語法結構幫助我們表達分析結構:欲分析,不同「部門」、其不同「職位」,在「在職狀態」(已離職和在職員工)的平均薪水,分析目的為部門內部的薪酬公平性對離職的是否有影響。對這句話進行語法分析,我們得到如下信息:第一行字段為「部門」,第二行字段為職位,第一列字段為在職狀態,數值字段為職位薪水,匯總函數為計算平均值。分析結果如下:

所屬部門

職位

離職

在職

人力資源部

經理

3500

助理

2400

專員

2400

2600

銷售部

經理

3200

業務員

1800

1800

財務部

出納

2000

會計

2200

2200

經理

3600

行政部

經理

3000

助理

1900

助理

2200

技術部

技術員

2600

2600

經理

3800

主管

2500

這裡我們要注意,變量的前後位置不一樣,會導致分析目的完全不一樣。譬如,我們把部門和職位的位置換一下後的分析結果如下:

職位

所屬部門

離職

在職

經理

人力資源部

3500

銷售部

3200

財務部

3600

行政部

3000

技術部

3800

3800

助理

人力資源部

2400

2400

行政部

2200

2200

它的語法結構會變成這樣:欲分析,不同「職位」、不同「部門」,在「在職狀態」(已離職和在職員工)的平均薪水,分析目的為公司內各職位在不同部門的薪酬差異及薪酬公平性對離職的是否有影響。

這篇文章我們概括的介紹了下常用的數據分析方法,以及在用EXCEL製作交叉分析表的一些方法。到時,我再寫文章介紹生成數據分析結果後,如何選擇合適的圖表來展示你的分析結果。如果對圖表使用得當可以幫助你讓你老板或者客戶快速的理解你的數據報告,從而體現出你的價值。