太秀了！用Excel也能實作和Python資料分析一樣的功能！

作者：Cherich_sun
來源：公眾號「杰哥的IT之旅」ID：Jake_Internet
太秀了！用Excel也能實作和Python資料分析一樣的功能！

這是一篇關于如何用excel做資料分析的案例，目的是幫助大家，在遇到小型資料樣本時，快速利用excel做分析，所以本篇文章的重點是分析思路+資料處理+可視化的實作，因為資料來源于網路，所以不做深入決議，

需求說明

分析某電商企業銷售趨勢，找到影響銷售增長的因素，同時分析不同市場產品銷售狀況，找到不同市場的銷售差異，挖掘不同產品的銷售狀況，找到不同產品的銷售差異，對用戶群體進行分析，對企業用戶的畫像和價值進行挖掘，從上述分析中得出結論，并根據分析結果，提出自己對此企業未來發展的一些想法和建議，

資料說明

本專案資料為某電商平臺全年每日訂單詳情資料和用戶資訊資料，包括兩個資料表，銷售訂單表和用戶資訊表，

其中銷售訂單表為每個訂單的情況明細，一個訂單對應一次銷售，這兩個表的表結構分別為：

銷售訂單表結構：

用戶資訊表結構：

一、分析思路

本專案主要分為三個維度，分別是用戶畫像，針對用戶群體進行分析，找出平臺的主力軍特點有哪些；商品維度上，找出商品在不同市場的銷售差異等；用戶價值維度上，主要根據用戶的消費行為，挖掘出其內在的規律，分析的詳細思路，如下：

二、資料處理（Excel）

1、資料清洗

最終資料狀態：“多一分則肥，少一分則瘦”，那么常用的清洗方法主要有以下三種，

① 重復資料處理

函式法 COUNTIF()
重復標記 =COUNTIF(A:A,A2)
第二次重復標記 =COUNTIF(A$2:A2,A2)
兩種結果對比如下 ：

高級篩選法 選中將要處理的資料區域，點擊選單欄里【資料】——【排序和篩選】里選擇【高級】，會彈出高級篩選，如下操作：


條件格式 選中將要處理的資料區域，點擊選單欄里【開始】——【條件格式】里選擇【突出顯示單元格規則】——【重復值】，如下操作：

資料透視表 資料透視表一樣能計算資料重復的頻次，而且比COUNTIF更加簡單易用，只需要拖動，首先，選中第一行標題資料左側，按Shift+Ctrl+End/下箭頭，選中全部資料后——【插入】——【資料透視表】，如下：
我們上面已經學會了各種重復值的處理，那么在實際業務中，通常會洗掉重復值，選中全部資料——點擊——選單欄里【資料】——【洗掉重復項】，如下:
② 缺失資料處理

一般可以接受的缺失值的標準是在10%以下，通常的處理方式如下：

• 平均值填充；
• 統計模型計算出來的值；
• 將缺失值的記錄洗掉掉；
• 保留，只在做相應的分析中做必要的排除

批量填充 如何把下面的表格的合并單元格拆分開，轉化成規范的資料，
選擇要轉換的區域——【開始】——【合并后居中】——即取消單元格合并——繼續選中要轉換的區域——按Ctrl+G——彈出【定位】——【定位條件】，選擇空值——確定——繼續在A3單元格中輸入"=",按上箭頭，再按Ctrl+Enter，自動填充完成，


注：批量去除公式：選中資料，粘貼為數值，這樣會提升excel的整體運行效率，下面，我們需要利用批量填充，處理銷售訂單表中的產品名稱欄位，批量洗掉掉數值，只保留產品名，新建一列空白列，先輸入幾個正確的產品名稱，按Ctrl+E，快速智能填充，
查找和替換 快捷鍵分別是：Ctrl+F；Ctrl+H

③ 空格資料處理

直接替換空格 Ctrl + H
特殊空格，要用trim()

2、資料合并

① 欄位合并

• CONCATENATE()
  
• &
  
• DATE()
  
  ② 欄位匹配

能夠實作的是表里沒有的資料，但是其他表有的，通過對應的共同關鍵字（資料型別必須相同）進行匹配，現在，我們將用戶表和訂單表進行合并，使得訂單表的欄位更加豐富，為接下來分析（消費的主力軍特征）做準備，想要實作的結果，如下：
實作方式 VLOOKUP，語法如下：

VLOOKUP(要查找的值,查找的范圍,屬于查找范圍的第幾列（序列號）,模糊/精確查找) 通過上面的語法，我們能夠成功的獲取到性別這一列資料，但是還有幾個欄位，如果通過復制粘貼的形式，修改序列號的話，效率太慢了，我們有更高效的方式，如下：

如上，序列號的位置通過COLUMN函式定位列，記得對行加絕對參考$，這樣的話，直接向右，向下拖拽，就會自動補全欄位內容，

3、資料抽取

① 欄位拆分

分列 現在我們已經成功合并成了一個大表，接下來，要對付款時間進行分列，拆分成年，月形式，方便后面作可視化，首先選中【單價】列右鍵——【插入】——同樣的方式插入三個空白列——復制一列【付款時間列】，再分別命名列名為年、月——選中【付款時間】列——【資料】——【分列】——【下一步】——【其他】輸入"/"——【完成】


函式（截取字串）：LEFT();RIGHT();MID()

② 隨機抽樣

RAND():回傳0~1之間的小數; 如果想回傳 60~70 之間的，=INT(RAND()*10+60)
RANDBETWEEN()：回傳介于指定數字之間的亂數，=RANDBETWEEN(60，70)

4、資料計算

① 簡單計算

② 函式計算

日期計算 求年齡函式實作方式：=DATEDIF(D2,TODAY(),“Y”)

資料分組 專案的需求是根據年齡對用戶打標簽（小于21歲，標記為"00后"；大于21歲并且小于31，標記為"90后"；大于31歲并且小于41，標記為"80后"；大于41歲并且小于51，標記為"70后"），有下面兩種實作方式，

• 方式一：

IF() IF(W2<21,“00后”,IF(AND(W2>21,W2<31),“90后”,IF(AND(W2>31,W2<41),“80后”,“70后”)))

• 方式二：

VLOOKUP() 這種方式我們僅需要設定閾值和顯示標簽，值得注意的是要VLOOKUP的第三個引數是模糊匹配，

5、資料轉換

① 資料表行列轉換

實作如下效果，選中要轉換的資料——右鍵，復制——選擇空白單元格——【開始】——【粘貼】——【選擇性粘貼】——選中【轉置】——完成


② 二維表轉為一維表

點擊【檔案】——【選項】——【自定義功能】——在【不在功能區中的命令】中找到【資料透視表和資料透視圖向導】并選中——在右側的【資料】選項卡下面添加【新建選項卡】并選中它——【添加】——確定，最終，在【資料】里出現了"資料透視表和資料透視圖向導"，如下：

點擊【資料透視表和資料透視圖向導】——選擇【多重合并計算資料區域】——【創建單頁欄位】——下一步——選定將要操作的區域——下一步——選擇【新作業表】——完成——雙擊【總計】的值——即實作二維表轉為一維表，如下：

三、可視化

可視化圖形大多數比較簡單，相信大家都能輕而易舉的實作，那么，一些特殊的圖形，因為也很重要，

1、用戶畫像
圖表說明：平臺的主力軍的特征主要是：女性用戶；90后人群單身人群‘’學歷主要是中專、博碩；地域集中在天津地區，女生購買偏好飲料、日用品，男性購買偏好是飲料、海鮮產品，

2、產品優勢

圖表說明：整體的銷售額處于下降趨勢，從10月份開始慢慢回升，根據二八法則，可以看出天津、南京、北京占總銷售額的43%，可以考慮作為重點投放市場，最受歡迎的品類是飲料、日用品、肉/家禽，

3、用戶價值

注：因為上面的資料集里用戶量有限，做同期群后不明顯，所以這里是一個新的用戶資料集，

圖表說明：通過RFM模型已經對用戶做好分類，可以針對不同型別用戶采取相應的運營策略；重點維護消費頻率在82~100之間的用戶，同期群分析，我們可以看出平臺的新用戶是逐漸遞增的，但是留存率較低，

公眾號：杰哥的IT之旅，后臺回復：「20210226」，即可獲取本文完整資料，

原創不易，碼字不易， 覺得這篇文章對你有點用的話，麻煩你為本文點個贊，留言或轉發一下，因為這將是我輸出更多優質文章的動力，感謝！

????????