基本介紹
- 書名：Excel數據分析與行銷決策從入門到精通
- 作者：恆盛傑資訊
- ISBN：9787111563259
- 類別：微軟Office
- 定價：¥49.80
- 出版社：機械工業出版社
- 出版時間：2017年04月
- 裝幀：平裝-膠訂
- 開本：16開
基本信息,內容簡介,目錄,
基本信息
作者：恆盛傑資訊 編著 |
ISBN(書號)：978-7-111-56325-9 |
出版日期：2017-04 |
版次：1/1 |
開本：16 |
定價：￥49.80 |
內容簡介
本書針對市場分析與行銷決策工作中的信息錄入、數據統計、表格製作、表單設計和圖表展示等套用需求，彙編出最貼近實際工作的知識和技巧，全面系統地介紹了Excel的技術特點和套用方法，深入揭示隱藏於高效辦公背後的原理和概念，並配合大量典型的套用實例，幫助讀者全面掌握Excel在市場分析與行銷決策工作中的套用技術。
目錄
前言
如何獲取雲空間資料
第1 章 高效製作專業的銷售報表
1.1 快速填充數據 ······················· 9
1.1.1 通過控制柄填充數據 ·························9
1.1.2 通過對話框填充數據 ······················· 11
套用技巧 快速建立部門年度銷售規劃表 ····· 12
1.1.3 通過快捷選單填充數據 ···················· 15
1.2 利用數據有效性快速設計表單 ············· 16
1.2.1 製作下拉列錶快速填充數據 ·············· 17
1.2.2 限制輸入整數 ···························· 18
1.2.3 限制輸入日期 ···························· 19
套用技巧 防止輸入周末日期 ················· 19
1.2.4 限制文本長度 ····························· 20
1.2.5 設定提示訊息 ···························· 21
1.2.6 設定錯誤警告 ···························· 21
套用技巧 圈釋無效數據 ······················· 22
套用技巧 限制重複錄入報價單 ·············· 24
1.3 使用樣式快速美化工作表 ··················· 25
1.3.1 套用預設樣式 ··························26
套用技巧 快速刪除單元格中的樣式 ········ 27
1.3.2 新建樣式 ······························ 29
第2章 使用條件格式和迷你圖分析數據
2.1 條件格式及其套用 ···························· 33
2.1.1 添加條件格式 ·································33
2.1.2 管理條件格式 ································39
套用技巧 判斷輸入的身份證號碼是否正確 ···40
2.2 使用迷你圖顯示數據趨勢 ··················· 41
2.2.1 什麼是迷你圖 ································42
2.2.2 什麼情況下可以使用迷你圖 ·················42
2.2.3 迷你圖的創建 ··································42
2.2.4 迷你圖的修改 ··································43
套用技巧 使用迷你圖分析成本費用 ··········45
第3章 使用排序和篩選快速分析數據
3.1 數據的排序 ····································· 47
3.1.1 單一欄位的排序 ···································47
套用技巧 排序字母與數字混合內容 ··········50
3.1.2 多重欄位的排序 ···································53
套用技巧 隨機排序 ···································53
3.1.3 按照用戶需要自定義序列排序 ··············55
3.1.4 對Excel表中的數據進行排序 ················56
3.2 數據篩選的套用 ······························· 58
3.2.1 快速查找數據 ································58
3.2.2 在“搜尋”框中設定關鍵字以進行篩選 ······59
套用技巧 排名靠前或靠後的數字 ··············59
3.2.3 自定義篩選方式 ···································60
套用技巧 選擇不重複的記錄 ·····················62
3.2.4 詳解高級篩選 ································62
套用技巧 使用計算條件的高級篩選 ··········66
3.3 利用篩選函式快速篩選數據 ················ 67
套用技巧 使用計算條件的高級篩選 ··········67
3.3.1 利用GESTEP函式進行一次篩選 ··········68
3.3.2 用IF、GESTEP函式進行二次篩選 ·······70
3.3.3 利用INDEX函式顯示符合條件記錄 ······71
第4章 利用公式與函式快速統計報表
4.1 利用公式快速計算Excel數據 ·············· 73
4.1.1 公式的簡介 ·································73
4.1.2 公式的輸入 ·································75
4.1.3 公式的修改 ··································77
4.1.4 公式的複製 ··································78
4.2 單元格的引用功能 ···························· 79
4.2.1 相對引用 ·································80
4.2.2 絕對引用 ··································81
4.2.3 混合引用 ····································82
4.2.4 調用外部數據 ··································82
4.3 使用名稱快速錄入公式 ······················ 85
4.3.1 什麼是名稱 ·······························86
4.3.2 定義名稱的規則 ···································87
4.3.3 定義名稱 ·····································87
4.3.4 利用名稱快速錄入公式 ·························89
4.4 使用函式快速統計數據 ······················ 90
4.4.1 函式的插入和修改 ································91
套用技巧 利用統計函式計算銷量最大值和最小值 ························91
4.4.2 使用嵌套函式 ·································93
套用技巧 分段統計銷量 ····························93
4.5 解決常見的公式錯誤 ························· 94
4.5.1 “####”錯誤值的處理方法 ·················94
4.5.2 “#DIV/0!”錯誤值的處理方法 ·············95
4.5.3 “#N/A”錯誤值的處理方法 ·················97
第5章 使用分類快速匯總表單數據
5.1 創建數據分類匯總 ···························· 98
5.1.1 創建分類匯總 ···································98
5.1.2 創建嵌套分類匯總 ······························101
5.1.3 利用分類函式快速匯總數據 ···············103
5.1.4 刪除分類匯總 ···································107
套用技巧 快速匯總每天的銷售記錄 ········108
5.2 分級查看分類匯總 ···························108
5.2.1 按照級別查看匯總數據 ·······················108
5.2.2 顯示或隱藏分級數據 ··························109
5.2.3 分頁顯示分類匯總 ······························110
5.2.4 使用樣式自定義顯示分級數據 ············112
5.3 組合與取消組合分級數據 ·················· 112
5.3.1 組合數據 ·······································113
5.3.2 取消組合數據 ·····································115
5.4 對數據進行合併計算 ························ 116
5.4.1 按位置合併計算 ·································117
5.4.2 按分類合併計算 ·································120
第6章 快速創建圖表分析數據
6.1 靈活地掌握各種圖表類型 ··················122
6.2 創建與編輯圖表 ······························125
6.2.1 創建圖表 ·····································125
6.2.2 快速添加圖表數據系列 ·······················127
6.2.3 更改圖表大小 ···································129
套用技巧 創建銷售業績下拉選單式圖表 ······130
6.2.4 圖表的快速布局和樣式設定 ···············130
6.3 圖表各部分細節的修飾 ·····················132
6.3.1 手動更改圖表元素的布局 ···················133
套用技巧 為成本結構圖添加百分比說明 ······135
6.3.2 手動更改圖表元素的樣式 ···················137
套用技巧 設定背景透明的銷量對比圖表 ······137
6.4 預測與分析圖表數據 ························142
6.4.1 為圖表添加趨勢線 ······························142
套用技巧 繪製營業額水平參照線 ············143
6.4.2 為圖表添加誤差線 ······························144
第7章 利用數據透視錶快速分組數據
7.1 什麼是數據透視表 ···························147
7.2 適用於數據透視表的情況 ··················148
7.3 基本數據透視表的製作 ·····················150
7.3.1 單一行標籤數據透視表 ·······················151
7.3.2 多重行標籤數據透視表 ·······················152
7.3.3 有關欄位勾選的順序 ··························153
7.3.4 行標籤、列標籤與Σ數值的概念 ········154
7.3.5 設定透視表欄位 ·································155
7.4 二維數據透視表 ······························157
7.4.1 顯示組分類匯總 ·································158
7.4.2 調整報表的數字格式 ··························160
7.4.3 數據分組設定 ·····································160
7.4.4 更理想的布局方式 ······························161
7.4.5 細部數據的摺疊與展開 ·······················162
7.5 讓匯總數據更多元化 ························163
7.5.1 更改文字標籤或欄位名稱 ···················164
7.5.2 數據透視表的匯總方式 ·······················164
7.6 報表篩選 ·······································165
7.6.1 頁欄位的篩選 ·····································166
7.6.2 標籤或欄位的篩選 ······························167
7.7 數據透視表的排序 ···························168
7.7.1 快速對標籤或欄位排序 ·······················168
7.7.2 排序報表中的數值 ······························170
7.8 計算欄位 ·······································171
套用技巧 用透視表分析企業銷售業績 ····172
7.9 使用切片器篩選報表中的數據 ············174
7.9.1 創建切片器並進行篩選 ·······················175
7.9.2 調整切片器的大小 ······························176
7.9.3 更改切片器的位置 ······························177
7.9.4 套用切片器樣式 ·································177
第8章 銷售數據的統計與分析
8.1 創建區域銷售表 ······························179
8.2 分析各地區銷售額情況 ·····················182
8.2.1 使用函式統計各地區銷售額 ···············182
8.2.2 排序各地區銷售額 ······························184
8.2.3 按名稱統計銷售額 ······························185
8.3 創建各地區銷售額動態比較圖 ············188
8.3.1 創建各地區銷售額比較圖和結構餅圖 ···188
8.3.2 在工作表中創建選項按鈕和複選框 ·····193
8.3.3 設定公式創建動態數據區域 ···············196
第9章 商品行銷數據分析
9.1 定價策略分析 ·································198
9.1.1 計算價格保留區域百分比 ···················198
9.1.2 創建消費者可接受價格範圍圖表 ········199
9.1.3 繪製文本框標識圖表 ··························202
9.2 市場競爭形勢分析 ···························202
9.2.1 計算銷售差額 ·····································203
9.2.2 創建競爭產品銷量對比圖 ···················204
9.3 品牌區域差異化分析 ························206
9.3.1 創建差異化分析表 ······························206
9.3.2 定義名稱 ······································207
9.3.3 創建品牌區域差異化動態圖表 ············208
9.4 銷售渠道分析 ································· 211
9.4.1 創建銷量比例統計表 ··························211
9.4.2 創建銷售渠道分析圖 ··························212
9.4.3 添加總銷量增長率系列 ·······················214
第10章 產品銷售的分期付款方式分析
10.1 使用單變數模擬運算表求解每月還款額 ···························215
10.1.1 創建單變數模擬運算表 ······················215
10.1.2 將模擬運算表的結果轉換為常量 ·······217
10.2 利用單變數求解逆算年限 ················218
10.2.1 利用單變數求解計算年限的精確值 ····218
10.2.2 確定可操作年限及對應的每期付款額 ···220
10.3 使用雙變數模擬運算表計算還款額 ····221
10.4 將模擬運算錶轉換為直觀的圖表 ·······225
第11章 銷售費用的分析與預測
11.1 使用直線法預測銷售費用 ················229
11.1.1 線性擬合預測銷售費用 ······················229
11.1.2 使用TREND函式預測銷售費用 ·········231
11.2 使用指數法預測銷售費用 ················233
11.2.1 使用LOGEST函式預測銷售費用 ·······233
11.2.2 使用GROWTH函式預測銷售費用 ·····235
11.3 使用圖表法預測銷售費用 ················236
11.3.1 創建實際銷售費用分析圖 ··················236
11.3.2 線性趨勢線預測分析 ·························237
11.3.3 指數趨勢線預測分析 ·························239
11.4 使用分析工具預測銷售費用 ·············240
11.4.1 用“移動平均法”預測銷售費用 ·······240
11.4.2 用“指數平滑法”預測銷售費用 ·······244
第12章銷售成本與利潤分析
12.1 銷售收入與成本 ····························247
12.1.1 銷售收入、成本、費用、稅金年度分析 ···················247
12.1.2 分析產品銷售成本 ·····························252
12.2 關於企業銷售利潤最大化分析 ··········255
12.2.1 按成本習性分析銷售成本 ··················256
12.2.2 載入“規劃求解”工具 ······················258
12.2.3 通過減少廣告費增加利潤 ··················258
12.2.4 通過減少展覽費增加利潤 ··················260
12.2.5 通過減少銷售成本增加利潤 ··············261
12.2.6 創建規劃求解報告 ··························263
12.2.7 合併方案 ·······························264
如何獲取雲空間資料
第1 章 高效製作專業的銷售報表
1.1 快速填充數據 ······················· 9
1.1.1 通過控制柄填充數據 ·························9
1.1.2 通過對話框填充數據 ······················· 11
套用技巧 快速建立部門年度銷售規劃表 ····· 12
1.1.3 通過快捷選單填充數據 ···················· 15
1.2 利用數據有效性快速設計表單 ············· 16
1.2.1 製作下拉列錶快速填充數據 ·············· 17
1.2.2 限制輸入整數 ···························· 18
1.2.3 限制輸入日期 ···························· 19
套用技巧 防止輸入周末日期 ················· 19
1.2.4 限制文本長度 ····························· 20
1.2.5 設定提示訊息 ···························· 21
1.2.6 設定錯誤警告 ···························· 21
套用技巧 圈釋無效數據 ······················· 22
套用技巧 限制重複錄入報價單 ·············· 24
1.3 使用樣式快速美化工作表 ··················· 25
1.3.1 套用預設樣式 ··························26
套用技巧 快速刪除單元格中的樣式 ········ 27
1.3.2 新建樣式 ······························ 29
第2章 使用條件格式和迷你圖分析數據
2.1 條件格式及其套用 ···························· 33
2.1.1 添加條件格式 ·································33
2.1.2 管理條件格式 ································39
套用技巧 判斷輸入的身份證號碼是否正確 ···40
2.2 使用迷你圖顯示數據趨勢 ··················· 41
2.2.1 什麼是迷你圖 ································42
2.2.2 什麼情況下可以使用迷你圖 ·················42
2.2.3 迷你圖的創建 ··································42
2.2.4 迷你圖的修改 ··································43
套用技巧 使用迷你圖分析成本費用 ··········45
第3章 使用排序和篩選快速分析數據
3.1 數據的排序 ····································· 47
3.1.1 單一欄位的排序 ···································47
套用技巧 排序字母與數字混合內容 ··········50
3.1.2 多重欄位的排序 ···································53
套用技巧 隨機排序 ···································53
3.1.3 按照用戶需要自定義序列排序 ··············55
3.1.4 對Excel表中的數據進行排序 ················56
3.2 數據篩選的套用 ······························· 58
3.2.1 快速查找數據 ································58
3.2.2 在“搜尋”框中設定關鍵字以進行篩選 ······59
套用技巧 排名靠前或靠後的數字 ··············59
3.2.3 自定義篩選方式 ···································60
套用技巧 選擇不重複的記錄 ·····················62
3.2.4 詳解高級篩選 ································62
套用技巧 使用計算條件的高級篩選 ··········66
3.3 利用篩選函式快速篩選數據 ················ 67
套用技巧 使用計算條件的高級篩選 ··········67
3.3.1 利用GESTEP函式進行一次篩選 ··········68
3.3.2 用IF、GESTEP函式進行二次篩選 ·······70
3.3.3 利用INDEX函式顯示符合條件記錄 ······71
第4章 利用公式與函式快速統計報表
4.1 利用公式快速計算Excel數據 ·············· 73
4.1.1 公式的簡介 ·································73
4.1.2 公式的輸入 ·································75
4.1.3 公式的修改 ··································77
4.1.4 公式的複製 ··································78
4.2 單元格的引用功能 ···························· 79
4.2.1 相對引用 ·································80
4.2.2 絕對引用 ··································81
4.2.3 混合引用 ····································82
4.2.4 調用外部數據 ··································82
4.3 使用名稱快速錄入公式 ······················ 85
4.3.1 什麼是名稱 ·······························86
4.3.2 定義名稱的規則 ···································87
4.3.3 定義名稱 ·····································87
4.3.4 利用名稱快速錄入公式 ·························89
4.4 使用函式快速統計數據 ······················ 90
4.4.1 函式的插入和修改 ································91
套用技巧 利用統計函式計算銷量最大值和最小值 ························91
4.4.2 使用嵌套函式 ·································93
套用技巧 分段統計銷量 ····························93
4.5 解決常見的公式錯誤 ························· 94
4.5.1 “####”錯誤值的處理方法 ·················94
4.5.2 “#DIV/0!”錯誤值的處理方法 ·············95
4.5.3 “#N/A”錯誤值的處理方法 ·················97
第5章 使用分類快速匯總表單數據
5.1 創建數據分類匯總 ···························· 98
5.1.1 創建分類匯總 ···································98
5.1.2 創建嵌套分類匯總 ······························101
5.1.3 利用分類函式快速匯總數據 ···············103
5.1.4 刪除分類匯總 ···································107
套用技巧 快速匯總每天的銷售記錄 ········108
5.2 分級查看分類匯總 ···························108
5.2.1 按照級別查看匯總數據 ·······················108
5.2.2 顯示或隱藏分級數據 ··························109
5.2.3 分頁顯示分類匯總 ······························110
5.2.4 使用樣式自定義顯示分級數據 ············112
5.3 組合與取消組合分級數據 ·················· 112
5.3.1 組合數據 ·······································113
5.3.2 取消組合數據 ·····································115
5.4 對數據進行合併計算 ························ 116
5.4.1 按位置合併計算 ·································117
5.4.2 按分類合併計算 ·································120
第6章 快速創建圖表分析數據
6.1 靈活地掌握各種圖表類型 ··················122
6.2 創建與編輯圖表 ······························125
6.2.1 創建圖表 ·····································125
6.2.2 快速添加圖表數據系列 ·······················127
6.2.3 更改圖表大小 ···································129
套用技巧 創建銷售業績下拉選單式圖表 ······130
6.2.4 圖表的快速布局和樣式設定 ···············130
6.3 圖表各部分細節的修飾 ·····················132
6.3.1 手動更改圖表元素的布局 ···················133
套用技巧 為成本結構圖添加百分比說明 ······135
6.3.2 手動更改圖表元素的樣式 ···················137
套用技巧 設定背景透明的銷量對比圖表 ······137
6.4 預測與分析圖表數據 ························142
6.4.1 為圖表添加趨勢線 ······························142
套用技巧 繪製營業額水平參照線 ············143
6.4.2 為圖表添加誤差線 ······························144
第7章 利用數據透視錶快速分組數據
7.1 什麼是數據透視表 ···························147
7.2 適用於數據透視表的情況 ··················148
7.3 基本數據透視表的製作 ·····················150
7.3.1 單一行標籤數據透視表 ·······················151
7.3.2 多重行標籤數據透視表 ·······················152
7.3.3 有關欄位勾選的順序 ··························153
7.3.4 行標籤、列標籤與Σ數值的概念 ········154
7.3.5 設定透視表欄位 ·································155
7.4 二維數據透視表 ······························157
7.4.1 顯示組分類匯總 ·································158
7.4.2 調整報表的數字格式 ··························160
7.4.3 數據分組設定 ·····································160
7.4.4 更理想的布局方式 ······························161
7.4.5 細部數據的摺疊與展開 ·······················162
7.5 讓匯總數據更多元化 ························163
7.5.1 更改文字標籤或欄位名稱 ···················164
7.5.2 數據透視表的匯總方式 ·······················164
7.6 報表篩選 ·······································165
7.6.1 頁欄位的篩選 ·····································166
7.6.2 標籤或欄位的篩選 ······························167
7.7 數據透視表的排序 ···························168
7.7.1 快速對標籤或欄位排序 ·······················168
7.7.2 排序報表中的數值 ······························170
7.8 計算欄位 ·······································171
套用技巧 用透視表分析企業銷售業績 ····172
7.9 使用切片器篩選報表中的數據 ············174
7.9.1 創建切片器並進行篩選 ·······················175
7.9.2 調整切片器的大小 ······························176
7.9.3 更改切片器的位置 ······························177
7.9.4 套用切片器樣式 ·································177
第8章 銷售數據的統計與分析
8.1 創建區域銷售表 ······························179
8.2 分析各地區銷售額情況 ·····················182
8.2.1 使用函式統計各地區銷售額 ···············182
8.2.2 排序各地區銷售額 ······························184
8.2.3 按名稱統計銷售額 ······························185
8.3 創建各地區銷售額動態比較圖 ············188
8.3.1 創建各地區銷售額比較圖和結構餅圖 ···188
8.3.2 在工作表中創建選項按鈕和複選框 ·····193
8.3.3 設定公式創建動態數據區域 ···············196
第9章 商品行銷數據分析
9.1 定價策略分析 ·································198
9.1.1 計算價格保留區域百分比 ···················198
9.1.2 創建消費者可接受價格範圍圖表 ········199
9.1.3 繪製文本框標識圖表 ··························202
9.2 市場競爭形勢分析 ···························202
9.2.1 計算銷售差額 ·····································203
9.2.2 創建競爭產品銷量對比圖 ···················204
9.3 品牌區域差異化分析 ························206
9.3.1 創建差異化分析表 ······························206
9.3.2 定義名稱 ······································207
9.3.3 創建品牌區域差異化動態圖表 ············208
9.4 銷售渠道分析 ································· 211
9.4.1 創建銷量比例統計表 ··························211
9.4.2 創建銷售渠道分析圖 ··························212
9.4.3 添加總銷量增長率系列 ·······················214
第10章 產品銷售的分期付款方式分析
10.1 使用單變數模擬運算表求解每月還款額 ···························215
10.1.1 創建單變數模擬運算表 ······················215
10.1.2 將模擬運算表的結果轉換為常量 ·······217
10.2 利用單變數求解逆算年限 ················218
10.2.1 利用單變數求解計算年限的精確值 ····218
10.2.2 確定可操作年限及對應的每期付款額 ···220
10.3 使用雙變數模擬運算表計算還款額 ····221
10.4 將模擬運算錶轉換為直觀的圖表 ·······225
第11章 銷售費用的分析與預測
11.1 使用直線法預測銷售費用 ················229
11.1.1 線性擬合預測銷售費用 ······················229
11.1.2 使用TREND函式預測銷售費用 ·········231
11.2 使用指數法預測銷售費用 ················233
11.2.1 使用LOGEST函式預測銷售費用 ·······233
11.2.2 使用GROWTH函式預測銷售費用 ·····235
11.3 使用圖表法預測銷售費用 ················236
11.3.1 創建實際銷售費用分析圖 ··················236
11.3.2 線性趨勢線預測分析 ·························237
11.3.3 指數趨勢線預測分析 ·························239
11.4 使用分析工具預測銷售費用 ·············240
11.4.1 用“移動平均法”預測銷售費用 ·······240
11.4.2 用“指數平滑法”預測銷售費用 ·······244
第12章銷售成本與利潤分析
12.1 銷售收入與成本 ····························247
12.1.1 銷售收入、成本、費用、稅金年度分析 ···················247
12.1.2 分析產品銷售成本 ·····························252
12.2 關於企業銷售利潤最大化分析 ··········255
12.2.1 按成本習性分析銷售成本 ··················256
12.2.2 載入“規劃求解”工具 ······················258
12.2.3 通過減少廣告費增加利潤 ··················258
12.2.4 通過減少展覽費增加利潤 ··················260
12.2.5 通過減少銷售成本增加利潤 ··············261
12.2.6 創建規劃求解報告 ··························263
12.2.7 合併方案 ·······························264
