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

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

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

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

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

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