本書主要用于對商務(wù)數(shù)據(jù)的管理與分析，書中深入淺出地介紹了利用Excel 2010軟件對商務(wù)數(shù)據(jù)進(jìn)行編輯、分析和管理的方法，可以幫助用戶快速、高效地完成數(shù)據(jù)的處理與分析工作。全書共分為11章，其中第1章主要介紹商務(wù)數(shù)據(jù)的基礎(chǔ)知識；第2~5章主要介紹數(shù)據(jù)編輯與處理的方法，如數(shù)據(jù)的可視化、數(shù)據(jù)的排序以及數(shù)據(jù)的分類和篩選等，第6~11章主要介紹實際工作中不同類型數(shù)據(jù)的分析方法，并對其中的實用函數(shù)、公式和數(shù)據(jù)分析工具等知識的使用方法進(jìn)行講解。
目前大多數(shù)教材和零售書多注重Excel數(shù)據(jù)處理與分析的能力，缺乏了對商科類行業(yè)的具體應(yīng)用。本書是針對商科類學(xué)生使用的以案例為導(dǎo)向、注重行業(yè)應(yīng)用的教材，同時配套數(shù)據(jù)源、ppt等相關(guān)資源
劉亞男，安徽商貿(mào)職業(yè)技術(shù)學(xué)院教師，從事電子商務(wù)專業(yè)教學(xué)多年，指導(dǎo)該校全國職業(yè)技能大賽高職組電子商務(wù)賽項多年，經(jīng)驗豐富，參與編寫我社暢銷教材《網(wǎng)店金牌客服》
第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ù)的作用 ..................................................................................2
1.1.3? 商務(wù)數(shù)據(jù)的采集 ..................................................................................3
1.1.4? 將商務(wù)數(shù)據(jù)導(dǎo)入Excel..........................................................................5
1.2 商務(wù)數(shù)據(jù)的清洗 ................................................................................7
1.2.1? 清洗缺失的數(shù)據(jù) ..................................................................................7
1.2.2? 清洗重復(fù)的數(shù)據(jù) ..................................................................................9
1.2.3? 清洗錯誤的數(shù)據(jù) ................................................................................12
1.3 商務(wù)數(shù)據(jù)的分析 ..............................................................................15
1.3.1? 數(shù)據(jù)分析的基本步驟..........................................................................15
1.3.2? 數(shù)據(jù)分析方法....................................................................................16
1.3.3? 常用數(shù)據(jù)分析工具 .............................................................................19
1.3.4? 數(shù)據(jù)分析報告....................................................................................19
1.4 提高與技巧.....................................................................................20
1.4.1? 數(shù)據(jù)挖掘..........................................................................................20
1.4.2? 自助式商業(yè)智能工具..........................................................................20
第2章 數(shù)據(jù)的輸入與編輯 ....................................................................21
2.1 輸入數(shù)據(jù)........................................................................................22
2.1.1? 輸入數(shù)據(jù)的一般方法..........................................................................22
2.1.2? 輸入特殊字符....................................................................................23
2.1.3? 快速填充數(shù)據(jù)....................................................................................24
2.2 計算表格中的數(shù)據(jù) ...........................................................................25
2.2.1? 輸入公式..........................................................................................25
2.2.2? 復(fù)制公式..........................................................................................26
2.2.3? 插入函數(shù)..........................................................................................27
2.2.4? 應(yīng)用嵌套函數(shù)....................................................................................28
2.2.5? 常用函數(shù)介紹....................................................................................29
2.3 美化工作表.....................................................................................30
2.3.1? 套用預(yù)設(shè)樣式....................................................................................30
2.3.2? 應(yīng)用單元格內(nèi)置樣式..........................................................................32
2.3.3? 創(chuàng)建自定義樣式 ................................................................................32
2.4 提高與技巧.....................................................................................34
2.4.1? 表格內(nèi)容全部顯示 .............................................................................34
2.4.2? 清除格式保留數(shù)據(jù) .............................................................................35
2.4.3? 刪除文本中的空格 .............................................................................36
2.4.4? 輸入身份證號碼 ................................................................................37
2.4.5? 定義單元格名稱 ................................................................................37
2.4.6? 單元格選擇技巧 ................................................................................38
第3章 數(shù)據(jù)的突出顯示與可視化...........................................................39
3.1 添加條件格式..................................................................................40
3.1.1? 突出顯示數(shù)據(jù)條 ................................................................................40
3.1.2? 按規(guī)定顯示數(shù)據(jù) ................................................................................42
3.1.3? 修改條件格式....................................................................................45
3.2 商務(wù)數(shù)據(jù)可視化 ..............................................................................47
3.2.1? 商務(wù)數(shù)據(jù)可視化的種類.......................................................................47
3.2.2? 創(chuàng)建圖表..........................................................................................49
3.2.3? 編輯圖表..........................................................................................50
3.2.4? 美化圖表..........................................................................................53
3.3 分析圖表數(shù)據(jù)..................................................................................55
3.3.1? 添加趨勢線.......................................................................................55
3.3.2? 添加誤差線.......................................................................................57
3.4 提高與技巧.....................................................................................58
3.4.1? 單元格可視化....................................................................................58
3.4.2? 突出正負(fù)值.......................................................................................59
3.4.3? 創(chuàng)建復(fù)合餅圖....................................................................................60
第4章 數(shù)據(jù)的排序、篩選與分類匯總....................................................61
4.1 數(shù)據(jù)的排序.....................................................................................62
4.1.1? 簡單排序..........................................................................................62
4.1.2? 單一字段排序....................................................................................63
4.1.3? 多重字段排序....................................................................................65
4.1.4? 自定義排序.......................................................................................66
4.2 數(shù)據(jù)的篩選.....................................................................................68
4.2.1? 自動篩選..........................................................................................68
4.2.2? 自定義篩選.......................................................................................69
4.2.3? 高級篩選..........................................................................................70
4.3 數(shù)據(jù)的分類匯總 ..............................................................................71
4.3.1? 創(chuàng)建分類匯總....................................................................................71
4.3.2? 創(chuàng)建嵌套分類匯總 .............................................................................72
4.3.3? 分級查看匯總數(shù)據(jù) .............................................................................73
4.4 提高與技巧.....................................................................................75
4.4.1? 按筆畫排序.......................................................................................75
4.4.2? 用函數(shù)進(jìn)行排序 ................................................................................76
4.4.3? 讓序號列不參與排序..........................................................................76
4.4.4? 按字符數(shù)量排序 ................................................................................76
4.4.5? 控制單元格填充顏色的排列順序..........................................................77
4.4.6? 按字體顏色或單元格填充顏色篩選.......................................................77
4.4.7? 合并計算..........................................................................................78
第5章 使用數(shù)據(jù)透視圖表分析數(shù)據(jù)........................................................79
5.1 數(shù)據(jù)透視表的應(yīng)用 ...........................................................................80
5.1.1? 創(chuàng)建數(shù)據(jù)透視表 ................................................................................80
5.1.2? 設(shè)置數(shù)據(jù)透視表 ................................................................................82
5.1.3? 使用數(shù)據(jù)透視表 ................................................................................85
5.1.4? 美化數(shù)據(jù)透視表 ................................................................................91
5.2 切片器的應(yīng)用..................................................................................93
5.2.1? 插入切片器.......................................................................................94
5.2.2? 更改切片器樣式 ................................................................................95
5.2.3? 設(shè)置切片器.......................................................................................96
5.2.4? 刪除切片器.......................................................................................98
5.3 數(shù)據(jù)透視圖的應(yīng)用 ...........................................................................98
5.3.1? 創(chuàng)建數(shù)據(jù)透視圖 ................................................................................98
5.3.2? 使用數(shù)據(jù)透視圖 ..............................................................................100
5.3.3? 設(shè)置并美化數(shù)據(jù)透視圖.....................................................................103
5.4 提高與技巧...................................................................................107
5.4.1? 處理數(shù)據(jù)透視表的空白項..................................................................107
5.4.2? 對數(shù)據(jù)透視表使用條件格式 ..............................................................108
5.4.3? 改變數(shù)據(jù)透視表的報表布局 ..............................................................108
第6章 產(chǎn)品營銷數(shù)據(jù)分析..................................................................................109
6.1 單款產(chǎn)品銷售生命周期分析 ............................................................110
6.1.1? 統(tǒng)計對應(yīng)日期的銷售額.....................................................................110
6.1.2? 判斷單款產(chǎn)品的銷售生命周期 ...........................................................111
6.2 產(chǎn)品暢/滯銷款分析 ........................................................................115
6.2.1? 使用公式計算庫銷比........................................................................115
6.2.2? 突出顯示滯銷產(chǎn)品 ...........................................................................116
6.2.3? 創(chuàng)建餅圖查看庫存金額.....................................................................118
6.3 多店鋪銷售數(shù)據(jù)分析......................................................................121
6.3.1? 計算各店鋪的成交率和客單價 ...........................................................121
6.3.2? 使用迷你圖顯示各店鋪的銷量 ...........................................................124
6.3.3? 創(chuàng)建圓柱圖對比各店鋪的銷售額........................................................126
6.4 歷年銷售數(shù)據(jù)分析 .........................................................................129
6.4.1? 分析歷年上半年整體銷售數(shù)據(jù) ...........................................................130
6.4.2? 分析歷年上半年各月銷售數(shù)據(jù) ...........................................................132
6.5 提高與技巧...................................................................................136
6.5.1? 銷售額/銷售量分析 ..........................................................................136
6.5.2? 季節(jié)性產(chǎn)品銷量分析........................................................................137
6.5.3? 產(chǎn)品價格體系分析 ...........................................................................137
6.5.4? 產(chǎn)品區(qū)域銷售情況分析.....................................................................138
第7章 銷售費用分析.........................................................................139
7.1 銷售收入與成本分析......................................................................140
7.1.1? 計算銷售收入、成本、費用和稅金.....................................................140
7.1.2? 回歸分析銷售收入與成本..................................................................143
7.1.3? 利用圖表顯示銷售收入與成本的變化..................................................145
7.2 各項費用明細(xì)支出對比...................................................................149
7.2.1? 創(chuàng)建數(shù)據(jù)透視表統(tǒng)計各項費用 ...........................................................150
7.2.2? 使用數(shù)據(jù)透視圖分析人工成本 ...........................................................153
7.3 提高與技巧...................................................................................156
7.3.1? 調(diào)整數(shù)據(jù)透視表視圖效果..................................................................157
7.3.2? 在數(shù)據(jù)透視表中計算數(shù)據(jù)..................................................................157
7.3.3? 將數(shù)據(jù)透視表拆分成多個分表 ...........................................................158
第8章 營銷決策分析.........................................................................159
8.1 市場需求量分析 ............................................................................160
8.1.1? 添加分析工具庫 ..............................................................................160
8.1.2? 利用“指數(shù)平滑”工具分析市場需求量 ..............................................161
8.2 定價策略分析................................................................................164
8.2.1? 創(chuàng)建客戶可接受價格比例圖表 ...........................................................165
8.2.2? 建立求解變量模型 ...........................................................................166
8.2.3? 計算并分析最優(yōu)定價........................................................................168
8.3 銷售成本預(yù)測分析 .........................................................................171
8.3.1? 創(chuàng)建散點圖.....................................................................................171
8.3.2? 添加線性預(yù)測趨勢線........................................................................173
8.3.3? INT和FORECAST函數(shù)的使用.........................................................175
8.4 客戶消費能力分析 .........................................................................176
8.4.1? 不同年齡段客戶的消費能力分析........................................................177
8.4.2? 不同性別客戶的消費能力分析 ...........................................................179
8.5 提高與技巧...................................................................................181
8.5.1? 創(chuàng)建帶對比效果的柱形圖..................................................................181
8.5.2? 采用自選形狀作為折線圖的數(shù)據(jù)點.....................................................182
第9章 競爭對手分析.........................................................................183
9.1 競爭產(chǎn)品市場份額分析..................................................................184
9.1.1? 使用公式計算市場占有率 .................................................................184
9.1.2? 使用函數(shù)實現(xiàn)多條件求和..................................................................186
9.1.3? 創(chuàng)建市場占有率對比圖.....................................................................188
9.2 競爭對手價格差異分析...................................................................191
9.2.1? 定義名稱........................................................................................191
9.2.2? 創(chuàng)建動態(tài)圖表..................................................................................193
9.3 提高與技巧...................................................................................197
9.3.1? 通過INDEX函數(shù)和控件創(chuàng)建動態(tài)圖表.................................................197
9.3.2? 通過數(shù)據(jù)有效性創(chuàng)建動態(tài)圖表 ...........................................................198
第10章 訂單與庫存分析....................................................................199
10.1 客戶訂單記錄統(tǒng)計 .......................................................................200
10.1.1? 按產(chǎn)品名稱匯總訂單......................................................................200
10.1.2? 使用函數(shù)統(tǒng)計銷售總額...................................................................202
10.1.3? 創(chuàng)建銷售額動態(tài)圖表......................................................................203
10.2 產(chǎn)品出入庫情況分析 ....................................................................205
10.2.1? 計算產(chǎn)品的出入庫金額...................................................................206
10.2.2? 按多條件方式對數(shù)據(jù)進(jìn)行排序 .........................................................207
10.2.3? 計算安全庫存、最大庫存和最小庫存量 ............................................209
10.2.4? 設(shè)置條件格式進(jìn)行預(yù)警提示 ............................................................210
10.3 產(chǎn)品庫齡分析..............................................................................212
10.3.1? 計算產(chǎn)品的庫齡 ............................................................................212
10.3.2? 突出顯示庫齡長的產(chǎn)品...................................................................213
10.4 提高與技巧.................................................................................214
10.4.1? 突出顯示某個數(shù)據(jù)記錄...................................................................214
10.4.2? 自定義數(shù)據(jù)的圖標(biāo)集......................................................................214
第11章 財務(wù)數(shù)據(jù)分析.......................................................................215
11.1 利潤預(yù)測與分析 ..........................................................................216
11.1.1? 利潤預(yù)測......................................................................................216
11.1.2? 動態(tài)分析產(chǎn)品利潤 .........................................................................219
11.2 利潤比率分析..............................................................................223
11.2.1? 利潤表比率的計算 .........................................................................224
11.2.2? 利潤表比率變化趨勢分析................................................................226
11.3 提高與技巧.................................................................................229
11.3.1? 將圖表保存為模板 .........................................................................230
11.3.2? 單變量求解...................................................................................230
新書資訊