《Excel數(shù)據(jù)分析基礎(chǔ)與實戰(zhàn)》同步第10章 分析用戶行為

分析用戶行為21-12月-231分析用戶復購率目錄分析客單價2分析用戶支付偏好3客單價的本質(zhì)是一定時期內(nèi)，每個用戶的平均消費金額。離開了“一定時期”這個范圍，客單價這個指標是沒有任何意義的。若用、、分別表示第天的客單價、總的銷售額和用戶數(shù)量，則客單價的計算公式如下。

在【訂單數(shù)據(jù)】工作表中，計算客單價，需要先計算消費金額與用戶數(shù)量。計算客單價

在【訂單數(shù)據(jù)】工作表中，通過數(shù)據(jù)透視表計算每日消費金額和用戶數(shù)量，具體操作步驟如下。（1）打開【創(chuàng)建數(shù)據(jù)透視表】對話框。打開【訂單數(shù)據(jù)】工作表，單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【插入】選項卡的【表格】命令組中，單擊【數(shù)據(jù)透視表】圖標，彈出【創(chuàng)建數(shù)據(jù)透視表】對話框，如圖所示。計算客單價1.計算消費金額和用戶數(shù)量（2）創(chuàng)建空白數(shù)據(jù)透視表。單擊步驟（1）中圖形所示的【確定】按鈕，創(chuàng)建一個空白數(shù)據(jù)透視表，并顯示【數(shù)據(jù)透視表字段】窗格，如圖所示。計算客單價（3）計算每日消費金額和用戶數(shù)量。將“日期”字段拖拽至【行】區(qū)域，“客戶ID”和“消費金額”字段拖拽至【值】區(qū)域，如圖所示。計算客單價（4）設置值字段。在步驟（3）圖形所示的【數(shù)據(jù)透視表字段】窗格的【值】區(qū)域中，單擊“客戶ID”字段旁的倒三角符號，選擇【值字段設置】，彈出【值字段設置】對話框，在【值匯總方式】選項卡中選擇【計算類型】列表下的【計數(shù)】選項，如左圖所示，單擊【確定】按鈕，計算結(jié)果如右圖所示。計算客單價通過消費金額和用戶數(shù)量計算客單價，具體操作步驟如下。（1）創(chuàng)建【客單價】工作表。創(chuàng)建新的工作表并重命名為【客單價】，將左圖計算出的數(shù)據(jù)復制到【客單價】工作表中，字段名依次為“日期”“用戶數(shù)量”和“消費金額”，如右圖所示。計算客單價2.計算客單價（2）計算客單價。在單元格D1的位置添加“客單價”輔助字段，并在單元格D2中輸入“=C2/B2”，按Enter鍵即可計算2018年8月1日的客單價，將鼠標指針移到單元格D2的右下角，當指針變?yōu)楹谏哟值摹?”指針時，雙擊左鍵即可計算其他日期的客單價，如圖所示。計算客單價（3）設置單元格格式。選擇步驟（2）圖形中的單元格區(qū)域D2:D32，右鍵單擊所選內(nèi)容，在彈出的快捷菜單中選擇【設置單元格格式】命令，在【設置單元格格式】對話框中，選擇【分類】列表框下的【數(shù)值】選項，并將【小數(shù)位數(shù)】設為2，如左圖所示，單擊【確定】按鈕，如右圖所示。計算客單價基于得到的客單價數(shù)據(jù)，繪制折線圖分析客單價，具體操作步驟如下。（1）打開【插入圖表】對話框。選擇左圖中的單元格區(qū)域A2:A32和D2:D32，在【插入】選項卡的【圖表】命令組中，單擊按鈕，彈出【插入圖表】對話框，如圖所示。繪制帶數(shù)據(jù)標記的折線圖分析客單價（2）選擇繪圖類型。切換至步驟（2）圖形所示的【所有圖表】選項卡，單擊【折線圖】選項，選擇【帶數(shù)據(jù)標記的折線圖】如圖所示。繪制帶數(shù)據(jù)標記的折線圖分析客單價（3）繪制折線圖。單擊【確定】按鈕，即可繪制帶數(shù)據(jù)標記的折線圖，如圖所示。繪制帶數(shù)據(jù)標記的折線圖分析客單價（4）修改圖表元素，具體操作步驟如下。單擊【圖表標題】文本激活圖表標題文本框，更改圖表標題為“客單價”。選中折線圖，單擊折線圖右邊的按鈕，在彈出的快捷菜單中勾選【坐標軸標題】，將橫坐標軸標題改為“日期”，縱坐標軸標題改為“客單價（元）”。圖表標題和坐標軸標題的字體改為宋體，圖標標題、坐標軸標題及其標簽的字體顏色改為黑色。繪制帶數(shù)據(jù)標記的折線圖分析客單價由圖可知，本月客單價處于5~7元之間，大部分的客單價主要分布在6.4元左右，整體偏低，說明用戶較偏向于購買單價較低的商品。繪制帶數(shù)據(jù)標記的折線圖分析客單價1分析用戶復購率目錄分析客單價2分析用戶支付偏好3復購率是指購買兩次或兩次以上的用戶占總用戶數(shù)的比率，若重復購買率越多，則反應出消費者對品牌的忠誠度就越高，反之越低。若用表示復購率、購買兩次或兩次以上的用戶數(shù)量、用戶總數(shù)量，復購率的計算公式如下。

在【訂單數(shù)據(jù)】工作表中，計算復購率，首先需要計算各購買次數(shù)用戶數(shù)量，其次計算各區(qū)間購買次數(shù)用戶數(shù)量，最后計算總區(qū)間購買次數(shù)用戶數(shù)量，具體操作步驟如下。計算復購率在【訂單數(shù)據(jù)】工作表中，通過COUNTIF函數(shù)計算各購買次數(shù)的用戶數(shù)量，具體操作步驟如下。（1）創(chuàng)建【復購率】工作表。創(chuàng)建新的工作表并重命名為【復購率】，將【訂單數(shù)據(jù)】工作表中的E列和F列數(shù)據(jù)復制到【復購率】工作表中的A列和B列，如圖所示。計算復購率1.計算各購買次數(shù)的用戶數(shù)（2）對“訂單ID”和“客戶ID”字段去重。單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【數(shù)據(jù)】選項卡的【數(shù)據(jù)工具】命令組中，單擊【刪除重復值】圖標，如左圖所示。彈出【刪除重復項】對話框，如右圖所示。計算復購率單擊【確定】按鈕，即可將重復值進行刪除，并彈出【MicrosoftExcel】所刪除的個數(shù)的提示框，如圖所示。計算復購率（3）對“客戶ID”字段二次去重。將“客戶ID”字段復制到D列中，如左圖所示。在【數(shù)據(jù)】選項卡的【數(shù)據(jù)工具】命令組中，單擊的【刪除重復值】圖標，彈出【刪除重復值】對話框，如右圖所示。計算復購率單擊【確定】按鈕，彈出【MicrosoftExcel】提示框，如圖所示。即為第二次去重后的“客戶ID”字段。計算復購率（4）添加“購買次數(shù)”輔助字段。在單元格E1添加“購買次數(shù)”輔助字段，如圖所示。計算復購率（5）計算每個客戶ID的購買次數(shù)。在單元格E2中，使用COUNTIF函數(shù)統(tǒng)計購買次數(shù)，如上圖所示，按Enter按鍵之后即可計算出該客戶ID的購買次數(shù)。將鼠標指針移到單元格E2的右下角，當指針變?yōu)楹谏哟值摹?”指針時，雙擊左鍵即可計算其他客戶ID的購買次數(shù)，如下圖所示。計算復購率（6）對“購買次數(shù)唯一值”字段去重，并進行升序排序。將“購買次數(shù)”字段粘貼至G列，并將字段重命名為“購買次數(shù)唯一值”，如圖所示。計算復購率在【數(shù)據(jù)】選項卡的【數(shù)據(jù)工具】命令組中，單擊【刪除重復值】圖標，彈出【刪除重復值】對話框，如左圖所示；單擊【確定】按鈕，彈出【MicrosoftExcel】提示框，如右圖所示，即為去重后的“購買次數(shù)唯一值”。計算復購率再對去重后的“購買次數(shù)唯一值”字段進行升序排序，最終得到的效果如圖所示。計算復購率（7）添加“用戶數(shù)”輔助字段。在單元格H1添加“用戶數(shù)”輔助字段，如圖所示。計算復購率（8）計算各購買次數(shù)的用戶數(shù)。在單元格H2中，使用COUNTIF函數(shù)統(tǒng)計用戶數(shù)，如上圖所示。按Enter按鍵之后即可計算出該購買次數(shù)的用戶數(shù)量。將鼠標指針移到單元格H2的右下角，當指針變?yōu)楹谏哟值摹?”指針時，雙擊左鍵即可計算各購買次數(shù)的用戶數(shù)，如下圖所示。計算復購率在【復購率】工作表中，通過SUM函數(shù)計算各區(qū)間購買次數(shù)的用戶數(shù)量，具體操作如下。（1）添加“購買次數(shù)區(qū)間”和“總用戶數(shù)”輔助字段。在單元格I1和J1中添加“購買次數(shù)區(qū)間”和“總用戶數(shù)”輔助字段，如圖所示。計算復購率2.計算各區(qū)間購買次數(shù)的用戶數(shù)（2）輸入“購買次數(shù)區(qū)間”字段的值。在單元格區(qū)域I1:I7中依次填入“1~4次”“5~8次”“9~12次”“13~16次”“17~20次”和“20以上”，如圖所示。計算復購率（3）計算各購買次數(shù)區(qū)間的總用戶數(shù)。在單元格區(qū)域J1:J7中分別使用SUM函數(shù)統(tǒng)計各區(qū)間段的值，如上圖所示。最終計算的各個區(qū)間的總用戶數(shù)，如下圖所示。計算復購率并在單元格J9中也使用SUM函數(shù)統(tǒng)計所有區(qū)間的總用戶數(shù)，如圖所示。計算復購率通過各購買次數(shù)區(qū)間的總用戶數(shù)和總區(qū)間的總用戶數(shù)計算復購率，具體操作步驟如下。（1）添加“復購率”輔助字段。在單元格K1中添加“復購率”輔助字段，如圖所示。計算復購率3.計算復購率（2）計算各購買次數(shù)區(qū)間復購率。在單元格K2中輸入“=J2/$J$9”，按Enter按鍵即可計算各購買次數(shù)區(qū)間的復購率，將鼠標指針移到單元格K2的右下角，當指針變?yōu)楹谏哟值摹?”指針時，雙擊左鍵即可計算其他區(qū)間的復購率，如圖所示。計算復購率（3）設置單元格格式。選擇步驟（2）圖中的單元格區(qū)域K2:K7，右鍵單擊所選內(nèi)容，在彈出的快捷菜單中選擇【設置單元格格式】命令，在【設置單元格格式】對話框中，選擇【分類】列表框下的【百分比】選項，并將【小數(shù)位數(shù)】設為2，如右圖所示。單擊【確定】按鈕即可設置成功，設置結(jié)果如下圖所示。注：由于設置了小數(shù)位數(shù)，所以復購率的占比總和會產(chǎn)生一定的誤差，如此處的復購率的占比總和為99.99%。計算復購率基于下圖所示的數(shù)據(jù)，繪制餅圖分析復購率，具體操作步驟如下。（1）選擇下圖“購買次數(shù)區(qū)間”字段的單元格區(qū)域I2:I7和“復購率”字段的單元格區(qū)域K2:K7。繪制餅圖分析用戶復購率（2）繪制餅圖。在【插入】選項卡的【圖表】命令組中單擊按鈕，彈出【插入圖表】對話框，切換至【所有圖表】選項卡，選擇【餅圖】選項，單擊【確定】按鈕，即可繪制餅圖，如圖所示。繪制餅圖分析用戶復購率（3）修改圖表元素，具體操作步驟如下。單擊【圖表標題】文本激活圖表標題文本框，更改圖表標題為“用戶復購率”。選中餅圖，單擊餅圖右邊的按鈕，在彈出的快捷菜單中勾選【數(shù)據(jù)標簽】命令即可添加數(shù)據(jù)標簽，并將數(shù)據(jù)標簽外移。圖表標題的字體改為宋體，圖標標題、圖例、數(shù)據(jù)標簽的字體顏色改為黑色。將圖例移至右上角。繪制餅圖分析用戶復購率由圖可知，8月份復購次數(shù)為1~4次的復購率最大，占總用戶復購率的42.54%，說明用戶的流動性較強，這也與實際情況相符合。繪制餅圖分析用戶復購率1分析用戶復購率目錄分析客單價2分析用戶支付偏好3新零售智能銷售設備的支付方式有3種：微信、支付寶和現(xiàn)金，支付方式的占比是指使用各類支付方式的交易次數(shù)與總交易次數(shù)的比值。如果用表示使用某種支付方式占比、交易次數(shù)，表示總交易次數(shù)，那么支付方式占比的具體計算公式如下式所示。

在【訂單數(shù)據(jù)】工作表中，計算支付方式占比，需要通過COUNTIF函數(shù)計算各支付方式交易的次數(shù)和運用SUM函數(shù)計算總交易次數(shù)，具體操作步驟如下。計算支付方式占比在【訂單數(shù)據(jù)】工作表中，需要通過COUNTIF函數(shù)計算各支付方式交易的次數(shù)，具體操作步驟如下。（1）對“訂單ID”字段去重。單擊數(shù)據(jù)區(qū)域內(nèi)任一單元格，在【數(shù)據(jù)】選項卡的【數(shù)據(jù)工具】命令組中，單擊【刪除重復值】圖標，彈出【刪除重復項】對話框，勾選“訂單ID”，如左圖所示。單擊【確定】按鈕，即可將重復值進行刪除，并彈出【MicrosoftExcel】提示框，如右圖所示。計算支付方式占比1.計算各支付方式交易次數(shù)（2）添加“支付方式”“交易次數(shù)”“交易總次數(shù)”和“占比”輔助字段。打開【訂單數(shù)據(jù)】工作表，分別在單元格Q1、R1、S1和T1的位置分別添加“支付方式”“交易次數(shù)”“總交易次數(shù)”和“占比”4個輔助字段，并將“微信”“現(xiàn)金”和“支付寶”3種支付方式填入表中，如圖所示。計算支付方式占比（3）計算各支付方式交易次數(shù)。在單元格R2中輸入“=COUNTIF($G$2:$G$54890,Q2)”，如上圖所示。按Enter鍵即可計算使用微信支付的交易次數(shù)，將鼠標指針移到單元格R2的右下角，當指針變?yōu)楹谏哟值摹?”指針時，雙擊左鍵即可計算各支付方式的交易次數(shù)，如下圖所示。計算支付方式占比在【訂單數(shù)據(jù)】工作表中，需要通過SUM函數(shù)計算總交易次數(shù)，具體操作步驟如下。計算總支付方式的總交易次數(shù)。在單元格S2中輸入“=SUM($R$2:$R$4)”，按Enter鍵即可計算總交易次數(shù)，將鼠標指針移到單元格S2的右下角，當指針變?yōu)楹谏哟值摹?”指針時，雙擊左鍵即可計算總交易次數(shù)，如圖所示。計算支付方式占比2.計算總交易次數(shù)通過各支付方式交易次數(shù)次數(shù)和總交易次數(shù)計算各支付方式占比，具體操作如下。（1）設置單元格格式。選擇【訂單數(shù)據(jù)】工作表中的單元格區(qū)域T2:T4，右鍵單擊所選內(nèi)容，在彈出的快捷菜單中選擇【設置單元格格式】命令，在【設置單元格格式】對話框中，選擇【分類】列表框下的【百分比】選項，并將【小數(shù)位數(shù)】設為2，如圖所示，單擊【確定】按鈕。計算支付方式占比3.計算各支付方式占比（2）計算支付方式占比。在單元格T2中輸入“=R2/S2”，按Enter鍵即可計算微信支付占比。將鼠標指針移到單元