第 1章　商務(wù)數(shù)據(jù)分析基礎(chǔ)....................................................................... 1

1.1　商務(wù)數(shù)據(jù)概述.......................................................................................　2

1.1.1　商務(wù)數(shù)據(jù)的定義......................................................................................　2

1.1.2　商務(wù)數(shù)據(jù)的作用 .....................................................................................　3

1.1.3　商務(wù)數(shù)據(jù)的采集 .....................................................................................　4

1.1.4　將商務(wù)數(shù)據(jù)導(dǎo)入Excel .............................................................................7

1.2　商務(wù)數(shù)據(jù)的清洗 ...................................................................................　9

1.2.1　清洗缺失數(shù)據(jù).........................................................................................　9

1.2.2　清洗重復(fù)數(shù)據(jù) .......................................................................................　11

1.2.3　清洗錯(cuò)誤數(shù)據(jù) .......................................................................................13

1.3　商務(wù)數(shù)據(jù)的分析 ..................................................................................16

1.3.1　數(shù)據(jù)分析的基本步驟..............................................................................16

1.3.2　數(shù)據(jù)分析方法 .......................................................................................17

1.3.3　常用的數(shù)據(jù)分析工具 ............................................................................　20

1.3.4　數(shù)據(jù)分析報(bào)告 .......................................................................................21

1.4　本章實(shí)訓(xùn) ............................................................................................21

1.4.1　采集網(wǎng)頁(yè)數(shù)據(jù)........................................................................................21

1.4.2　清洗店鋪訂單表數(shù)據(jù) ............................................................................　22

第　2章 數(shù)據(jù)的輸入與編輯 ...................................................................25

2.1　輸入數(shù)據(jù) ...........................................................................................　26

2.1.1　輸入數(shù)據(jù)的一般方法.............................................................................　26

2.1.2　輸入特殊字符 ......................................................................................　27

2.1.3　快速填充數(shù)據(jù) ......................................................................................　28

2.2　計(jì)算表格中的數(shù)據(jù)..............................................................................　29

2.2.1　輸入公式.............................................................................................　30

2.2.2　復(fù)制公式.............................................................................................　30

II　Excel商務(wù)數(shù)據(jù)處理與分析（微課版）（第 2版）

2.2.3　插入函數(shù)..............................................................................................31

2.2.4　應(yīng)用嵌套函數(shù)......................................................................................　33

2.3　常用函數(shù)介紹 ....................................................................................　34

2.3.1　求和函數(shù)SUM.....................................................................................　34

2.3.2　平均值函數(shù)AVERAGE .......................................................................　35

2.3.3　取整函數(shù)INT.......................................................................................　35

2.3.4　排名函數(shù) ............................................................................................　35

2.3.5　邏輯函數(shù)IF .........................................................................................　35

2.3.6　統(tǒng)計(jì)函數(shù)COUNTIF ............................................................................　36

2.3.7　查找函數(shù)VLOOKUP...........................................................................　36

2.3.8　查找函數(shù)MATCH ...............................................................................　36

2.3.9　查找與引用函數(shù)INDEX .......................................................................　37

2.3.10　其他函數(shù)...........................................................................................　38

2.4　智能表格 .............................................................................................41

2.4.1　創(chuàng)建智能表格 .......................................................................................41

2.4.2　在智能表格中添加數(shù)據(jù).........................................................................　42

2.4.3　分析智能表格中的數(shù)據(jù).........................................................................　44

2.5　本章實(shí)訓(xùn) ............................................................................................　45

2.5.1　分析廣告投放數(shù)據(jù)統(tǒng)計(jì)表......................................................................　45

2.5.2　分析會(huì)員消費(fèi)記錄數(shù)據(jù).........................................................................　47

第3章　數(shù)據(jù)的突出顯示與可視化...........................................................50

3.1　添加條件格式......................................................................................51

3.1.1　突出顯示數(shù)據(jù)條 ....................................................................................51

3.1.2　按規(guī)定顯示數(shù)據(jù)...................................................................................　53

3.1.3　修改條件格式 ......................................................................................　56

3.2　數(shù)據(jù)可視化........................................................................................　58

3.2.1　數(shù)據(jù)可視化的種類................................................................................　58

3.2.2　創(chuàng)建圖表.............................................................................................　60

3.2.3　編輯圖表..............................................................................................61

3.2.4　美化圖表 ............................................................................................　63

3.3　分析圖表數(shù)據(jù) ....................................................................................　66

3.3.1　添加趨勢(shì)線..........................................................................................　66

3.3.2　添加誤差線 .........................................................................................　67

3.4　本章實(shí)訓(xùn)...........................................................................................　68

3.4.1　分析客服咨詢轉(zhuǎn)化率數(shù)據(jù)......................................................................　69

3.4.2　分析店鋪客流量數(shù)據(jù)............................................................................　70

目　錄

CONTENTS　III

第4章　數(shù)據(jù)的排序、篩選與分類匯總....................................................72

4.1　數(shù)據(jù)的排序........................................................................................　73

4.1.1　簡(jiǎn)單排序 .............................................................................................　73

4.1.2　單一字段排序 ......................................................................................　74

4.1.3　多重字段排序 ......................................................................................　76

4.1.4　自定義排序..........................................................................................　77

4.2　數(shù)據(jù)的篩選........................................................................................　79

4.2.1　自動(dòng)篩選.............................................................................................　79

4.2.2　自定義篩選 .........................................................................................　80

4.2.3　高級(jí)篩選 .............................................................................................81

4.3　數(shù)據(jù)的分類匯總.................................................................................　83

4.3.1　創(chuàng)建分類匯總 ......................................................................................　83

4.3.2　創(chuàng)建嵌套分類匯總 ...............................................................................　84

4.3.3　分級(jí)查看匯總數(shù)據(jù) ...............................................................................　85

4.4　本章實(shí)訓(xùn)...........................................................................................　87

4.4.1　分析市場(chǎng)營(yíng)銷活動(dòng)數(shù)據(jù).........................................................................　87

4.4.2　分析客服營(yíng)銷活動(dòng)數(shù)據(jù).........................................................................　88

第5章　使用數(shù)據(jù)透視圖表分析數(shù)據(jù).......................................................90

5.1　數(shù)據(jù)透視表的應(yīng)用 ...............................................................................91

5.1.1　創(chuàng)建數(shù)據(jù)透視表 ....................................................................................91

5.1.2　設(shè)置數(shù)據(jù)透視表...................................................................................　92

5.1.3　使用數(shù)據(jù)透視表...................................................................................　94

5.1.4　美化數(shù)據(jù)透視表...................................................................................　97

5.2　切片器的應(yīng)用 ....................................................................................99

5.2.1　插入切片器..........................................................................................　99

5.2.2　更改切片器樣式..................................................................................100

5.2.3　設(shè)置切片器 ........................................................................................　101

5.2.4　使用切片器 ........................................................................................103

5.3　數(shù)據(jù)透視圖的應(yīng)用............................................................................　104

5.3.1　創(chuàng)建數(shù)據(jù)透視圖..................................................................................105

5.3.2　使用數(shù)據(jù)透視圖..................................................................................106

5.3.3　設(shè)置并美化數(shù)據(jù)透視圖........................................................................108

5.4　本章實(shí)訓(xùn).........................................................................................　109

5.4.1　分析店鋪PC端與無(wú)線端訪客數(shù)據(jù).........................................................　110

5.4.2　分析店鋪搜索詞匯總數(shù)據(jù) .....................................................................111

IV　Excel商務(wù)數(shù)據(jù)處理與分析（微課版）（第 2版）

第6章　市場(chǎng)環(huán)境分析..........................................................................113

6.1　競(jìng)爭(zhēng)產(chǎn)品市場(chǎng)份額分析 ......................................................................114

6.1.1　使用公式計(jì)算市場(chǎng)占有率 .....................................................................　114

6.1.2　使用函數(shù)實(shí)現(xiàn)多條件求和.....................................................................　116

6.1.3　創(chuàng)建市場(chǎng)占有率對(duì)比圖........................................................................　118

6.2　競(jìng)爭(zhēng)對(duì)手價(jià)格差異分析......................................................................121

6.2.1　定義名稱............................................................................................122

6.2.2　創(chuàng)建動(dòng)態(tài)圖表.....................................................................................123

6.3　本章實(shí)訓(xùn)..........................................................................................127

6.3.1　分析店鋪售后評(píng)價(jià)數(shù)據(jù)........................................................................127

6.3.2　分析店鋪退款數(shù)據(jù) ..............................................................................128

第7章　產(chǎn)品營(yíng)銷數(shù)據(jù)分析..................................................................................130

7.1　單款產(chǎn)品銷售生命周期分析................................................................131

7.1.1　統(tǒng)計(jì)對(duì)應(yīng)日期的銷售金額 .....................................................................　131

7.1.2　判斷單款產(chǎn)品的銷售生命周期..............................................................132

7.2　產(chǎn)品暢滯銷款分析.............................................................................135

7.2.1　使用公式計(jì)算庫(kù)銷比 ...........................................................................136

7.2.2　突出顯示滯銷產(chǎn)品 ..............................................................................136

7.2.3　創(chuàng)建餅圖查看庫(kù)存金額........................................................................138

7.3　多店鋪銷售數(shù)據(jù)分析 ........................................................................　140

7.3.1　計(jì)算各店鋪的成交率和客單價(jià)..............................................................　141

7.3.2　使用迷你圖顯示各店鋪的銷量..............................................................143

7.3.3　使用柱形圖對(duì)比各店鋪的銷售額 ..........................................................146

7.4　歷年銷售數(shù)據(jù)分析............................................................................　148

7.4.1　分析歷年上半年整體銷售數(shù)據(jù)..............................................................149

7.4.2　分析歷年上半年各月銷售數(shù)據(jù)..............................................................　151

7.5　本章實(shí)訓(xùn)..........................................................................................154

7.5.1　分析店鋪周銷售數(shù)據(jù) ...........................................................................154

7.5.2　分析店鋪運(yùn)營(yíng)數(shù)據(jù) ..............................................................................155

第8章　訂單與庫(kù)存分析.....................................................................　157

8.1　客戶訂單記錄統(tǒng)計(jì)分析 ......................................................................158

8.1.1　按產(chǎn)品名稱匯總訂單............................................................................158

8.1.2　使用函數(shù)統(tǒng)計(jì)銷售總額........................................................................160

目　錄

CONTENTS　V

8.1.3　創(chuàng)建銷售額動(dòng)態(tài)圖表 ...........................................................................　161

8.2　產(chǎn)品庫(kù)存明細(xì)表分析 .........................................................................163

8.2.1　計(jì)算產(chǎn)品的出入庫(kù)金額........................................................................164

8.2.2　按多條件方式排序數(shù)據(jù)........................................................................165

8.2.3　計(jì)算安全庫(kù)存、最大庫(kù)存和最小庫(kù)存....................................................167

8.2.4　設(shè)置條件格式進(jìn)行預(yù)警提示.................................................................168

8.3　產(chǎn)品庫(kù)齡分析 ...................................................................................170

8.3.1　計(jì)算產(chǎn)品庫(kù)齡 .....................................................................................170

8.3.2　突出顯示庫(kù)齡長(zhǎng)的產(chǎn)品........................................................................　171

8.4　本章實(shí)訓(xùn) ...........................................................................................173

8.4.1　分析電商平臺(tái)中的產(chǎn)品庫(kù)存數(shù)..............................................................173

8.4.2　分析庫(kù)存產(chǎn)品周轉(zhuǎn)率...........................................................................174

第9章　銷售費(fèi)用分析.........................................................................　176

9.1　銷售收入與成本分析..........................................................................177

9.1.1　計(jì)算銷售收入、成本、費(fèi)用和稅金........................................................177

9.1.2　回歸分析銷售收入與成本.....................................................................180

9.1.3　利用圖表顯示銷售收入與成本之間的變化 .............................................182

9.2　產(chǎn)品費(fèi)用結(jié)構(gòu)表分析 ........................................................................　184

9.2.1　創(chuàng)建數(shù)據(jù)透視表統(tǒng)計(jì)各項(xiàng)費(fèi)用..............................................................185

9.2.2　使用數(shù)據(jù)透視圖分析人工成本..............................................................188

9.3　本章實(shí)訓(xùn).........................................................................................　190

9.3.1　分析店鋪銷售成本利潤(rùn)表.....................................................................190

9.3.2　分析銷售收入成本表...........................................................................　191

第　10章 財(cái)務(wù)數(shù)據(jù)分析.......................................................................　193

10.1　利潤(rùn)預(yù)測(cè)與分析..............................................................................　194

10.1.1　利潤(rùn)預(yù)測(cè)...........................................................................................194

10.1.2　動(dòng)態(tài)分析產(chǎn)品利潤(rùn).............................................................................197

10.2　利潤(rùn)比率分析.................................................................................200

10.2.1　利潤(rùn)表比率的計(jì)算.............................................................................201

10.2.2　利潤(rùn)表比率變化趨勢(shì)分析..................................................................　202

10.3　本章實(shí)訓(xùn) .......................................................................................　205

10.3.1　分析店鋪營(yíng)業(yè)利潤(rùn)表.........................................................................　205

10.3.2　分析店鋪銷售利潤(rùn)表 ........................................................................　206

VI　Excel商務(wù)數(shù)據(jù)處理與分析（微課版）（第 2版）

第　11章 營(yíng)銷決策分析.......................................................................208

11.1　市場(chǎng)需求量分析 ..............................................................................209

11.1.1　添加分析工具庫(kù) ................................................................................　209

11.1.2　利用“指數(shù)平滑”工具分析市場(chǎng)需求量 ...............................................210

11.2　定價(jià)策略分析 ..................................................................................213

11.2.1　創(chuàng)建客戶可接受價(jià)格比例圖表.............................................................214

11.2.2　建立求解變量模型.............................................................................215

11.2.3　計(jì)算并分析最優(yōu)定價(jià)..........................................................................217

11.3　銷售成本預(yù)測(cè)分析 ..........................................................................　219

11.3.1　創(chuàng)建散點(diǎn)圖 ......................................................................................　220

11.3.2　添加線性預(yù)測(cè)趨勢(shì)線..........................................................................221

11.3.3　INT函數(shù)和FORECAST.LINEAR函數(shù)的使用....................................　222

11.4　客戶消費(fèi)能力分析 ..........................................................................224

11.4.1　不同年齡段客戶的消費(fèi)能力分析 ........................................................　224

11.4.2　不同性別客戶的消費(fèi)能力分析 ...........................................................　226

11.5　本章實(shí)訓(xùn)........................................................................................228

11.5.1　分析產(chǎn)品銷量預(yù)測(cè)表 .........................................................................　228

11.5.2　分析客戶消費(fèi)金額............................................................................　229