Excel數(shù)據(jù)分析與可視化一本通

數(shù)據(jù)分析篇 1
第1章 建立規(guī)范的數(shù)據(jù)源
1.1 必懂的3種數(shù)據(jù)類型 3
1.1.1 數(shù)值型數(shù)據(jù) 3
1.1.2 文本型數(shù)據(jù) 4
1.1.3 邏輯型數(shù)據(jù) 5
1.2 合理設(shè)置數(shù)據(jù)的格式 6
1.2.1 快速統(tǒng)一小數(shù)位數(shù) 6
1.2.2 將數(shù)值轉(zhuǎn)換成貨幣格式 7
1.2.3 啟用會計專用格式 8
1.2.4 設(shè)置負數(shù)的顯示方式 9
1.2.5 輸入百分比數(shù)值 9
1.2.6 轉(zhuǎn)換日期的顯示方式 10
1.2.7 讓身份證號碼完整顯示 11
1.2.8 各種符號的輸入方法 12
1.2.9 自定義數(shù)據(jù)格式 13
1.3 處理重復(fù)性工作的技巧 16
1.3.1 復(fù)制數(shù)據(jù) 16
1.3.2 自動填充增效百倍 17
1.3.3 批量完成數(shù)據(jù)更換 20
1.4 沒有規(guī)矩不成方圓——數(shù)據(jù)驗證 23
1.4.1 設(shè)置允許輸入的數(shù)值范圍 23
1.4.2 設(shè)置允許輸入的文本長度 24
1.4.3 使用下拉列表輸入數(shù)據(jù) 24
1.4.4 自定義驗證條件 25
1.4.5 自定義出錯警告對話框 26
1.4.6 刪除數(shù)據(jù)驗證條件 26
1.5 數(shù)據(jù)源的規(guī)范整理 27
1.5.1 去除多余標(biāo)題行和表頭 27
1.5.2 合理安排字段的位置 28
1.5.3 刪除無用的空白行/列 29
1.5.4 取消合并單元格 30
1.5.5 補全空缺信息 30
1.5.6 數(shù)據(jù)屬性行列分明 31
拓展練習(xí)：制作員工銷售提成表 33
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 36

第2章 公式與函數(shù)在數(shù)據(jù)分析中的應(yīng)用
2.1 公式與函數(shù)的自我介紹 38
2.1.1 HI！我是Excel公式 38
2.1.2 HI！我是Excel函數(shù) 39
2.2 公式與函數(shù)的應(yīng)用基礎(chǔ) 41
2.2.1 公式的輸入與編輯 41
2.2.2 填充與復(fù)制公式 42
2.2.3 單元格的引用形式 43
2.2.4 函數(shù)的輸入技巧 44
2.2.5 常見的錯誤值分析及公式審核 46
2.3 數(shù)組公式讓計算變得更簡單 48
2.3.1 什么是數(shù)組 48
2.3.2 使用數(shù)組公式時的注意事項 49
2.4 邏輯函數(shù)實現(xiàn)自動判斷 51
2.4.1 IF函數(shù)的應(yīng)用 51
2.4.2 IF函數(shù)執(zhí)行多重判斷 51
2.4.3 AND函數(shù)的應(yīng)用 52
2.4.4 OR函數(shù)的應(yīng)用 52
2.5 快速統(tǒng)計和匯總數(shù)據(jù) 53
2.5.1 SUM函數(shù)的應(yīng)用 53
2.5.2 SUMIF函數(shù)的應(yīng)用 54
2.5.3 COUNT函數(shù)的應(yīng)用 55
2.5.4 COUNTIF函數(shù)的應(yīng)用 56
2.5.5 AVERAGE函數(shù)的應(yīng)用 57
2.5.6 AVERAGEIF函數(shù)的應(yīng)用 57
2.5.7 MOD函數(shù)的應(yīng)用 58
2.6 數(shù)據(jù)的追蹤與查詢專業(yè)戶 59
2.6.1 VLOOKUP函數(shù)的應(yīng)用 59
2.6.2 MATCH函數(shù)的應(yīng)用 60
2.6.3 INDEX函數(shù)的應(yīng)用 60
2.6.4 ROW函數(shù)的應(yīng)用 61
2.7 一鍵計算日期與時間差 61
2.7.1 TODAY函數(shù)的應(yīng)用 62
2.7.2 DATEDIF函數(shù)的應(yīng)用 62
2.7.3 WEEKDAY函數(shù)的應(yīng)用 63
2.8 其他典型函數(shù)的應(yīng)用 64
2.8.1 RANK函數(shù)的應(yīng)用 64
2.8.2 ROUND函數(shù)的應(yīng)用 65
2.8.3 LEFT、MID、RIGHT函數(shù)的應(yīng)用 65
2.8.4 TEXT函數(shù)的應(yīng)用 66
拓展練習(xí)：制作工作服統(tǒng)計表 67
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 69

第3章 掌握日常數(shù)據(jù)分析與管理技能
3.1 輕松排序表格中的數(shù)據(jù) 71
3.1.1 執(zhí)行升序或降序操作 71
3.1.2 多個字段同時排序 71
3.1.3 根據(jù)不同要求排序 72
3.1.4 讓最頂端的數(shù)據(jù)不參與排序 73
3.1.5 自定義排序 73
3.2 快速篩選指定數(shù)據(jù) 74
3.2.1 啟用篩選 74
3.2.2 快速篩選指定數(shù)據(jù)方法 75
3.2.3 根據(jù)數(shù)據(jù)類型選擇篩選方式 75
3.2.4 在受保護的工作表中進行篩選 76
3.2.5 清除篩選 77
3.3 完成復(fù)雜條件下的篩選 77
3.3.1 高級篩選的條件設(shè)置原則 77
3.3.2 執(zhí)行高級篩選 78
3.3.3 篩選不重復(fù)值 78
3.3.4 在其他區(qū)域顯示篩選結(jié)果 79
3.4 察“顏”觀“色”完成數(shù)據(jù)分析 79
3.4.1 突出顯示符合條件的單元格 79
3.4.2 突出顯示金額最高的3個值 80
3.4.3 使用數(shù)據(jù)條顯示數(shù)值大小 81
3.4.4 制作銷量對比旋風(fēng)圖 81
3.4.5 用顏色顯示數(shù)據(jù)集中趨勢 82
3.4.6 自定義色階的漸變顏色 82
3.4.7 使用圖標(biāo)展示數(shù)據(jù) 83
3.4.8 重新分配圖標(biāo)的取值范圍 83
3.4.9 隱藏數(shù)值只顯示圖標(biāo) 84
3.5 數(shù)據(jù)分類匯總 84
3.5.1 單字段分類匯總 84
3.5.2 多字段分類匯總 86
3.5.3 復(fù)制分類匯總結(jié)果 86
3.5.4 清除分類匯總 87
3.6 多表合并計算 88
3.6.1 同一工作簿中的多表合并計算 88
3.6.2 選擇合并計算的函數(shù) 89
3.6.3 讓合并計算結(jié)果與數(shù)據(jù)源產(chǎn)生鏈接 89
拓展練習(xí)：分析發(fā)貨明細表 89
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 93

第4章 強大的多功能數(shù)據(jù)分析儀
4.1 創(chuàng)建數(shù)據(jù)透視表 95
4.1.1 數(shù)據(jù)透視表對數(shù)據(jù)源的要求 95
4.1.2 創(chuàng)建空白數(shù)據(jù)透視表 96
4.1.3 創(chuàng)建系統(tǒng)推薦的數(shù)據(jù)透視表 97
4.1.4 同時創(chuàng)建數(shù)據(jù)透視表和數(shù)據(jù)透視圖 98
4.2 字段的添加和顯示 98
4.2.1 了解數(shù)據(jù)透視表的核心概念 98
4.2.2 快速添加或刪除字段 99
4.2.3 移動字段 100
4.2.4 調(diào)整數(shù)據(jù)透視表字段窗格 101
4.2.5 更改字段名稱 101
4.2.6 展開或折疊字段 102
4.2.7 隱藏行字段中的折疊和展開按鈕 102
4.2.8 清除數(shù)據(jù)透視表中的所有字段 102
4.3 數(shù)據(jù)透視表的基本操作 103
4.3.1 更改數(shù)據(jù)源 103
4.3.2 刷新數(shù)據(jù)源 103
4.3.3 更改數(shù)據(jù)透視表名稱 104
4.3.4 快速獲得多個數(shù)據(jù)透視表 104
4.3.5 移動數(shù)據(jù)透視表 104
4.3.6 刪除數(shù)據(jù)透視表 105
4.4 以更佳的布局方式展示數(shù)據(jù) 105
4.4.1 數(shù)據(jù)透視表的3種布局形式 105
4.4.2 重復(fù)顯示所有項目標(biāo)簽 106
4.4.3 在每個項目后插入空行 107
4.4.4 控制是否顯示總計 107
4.4.5 調(diào)整分類匯總的顯示位置 107
4.4.6 批量為分類匯總行添加底紋 108
4.4.7 查看指定匯總行的明細數(shù)據(jù) 108
4.5 讓數(shù)據(jù)透視表看起來更美觀 109
4.5.1 套用數(shù)據(jù)透視表樣式 109
4.5.2 自定義數(shù)據(jù)透視表樣式 110
4.5.3 清除數(shù)據(jù)透視表樣式 110
拓展練習(xí)：創(chuàng)建薪酬管理數(shù)據(jù)透視表 111
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 114

第5章 多維度動態(tài)分析數(shù)據(jù)
5.1 數(shù)據(jù)透視表中的計算和匯總 116
5.1.1 對字段分組 116
5.1.2 更改值字段匯總方式 117
5.1.3 更改值的顯示方式 117
5.1.4 添加計算字段 118
5.1.5 使用函數(shù)創(chuàng)建新字段 119
5.1.6 用數(shù)據(jù)透視表合并多表數(shù)據(jù) 119
5.1.7 設(shè)置數(shù)值的小數(shù)位數(shù) 121
5.2 在數(shù)據(jù)透視表中執(zhí)行排序 122
5.2.1 拖拽法手動排序 122
5.2.2 對指定字段進行排序 123
5.2.3 多個字段同時排序 123
5.2.4 更改文本字段的自動排序依據(jù) 124
5.3 在數(shù)據(jù)透視表中執(zhí)行篩選 125
5.3.1 篩選行字段 125
5.3.2 篩選值字段 125
5.3.3 篩選分類匯總大于指定值的數(shù)據(jù) 126
5.3.4 添加篩選字段執(zhí)行篩選 127
5.3.5 分頁顯示篩選字段項目 127
5.3.6 分頁打印篩選結(jié)果 128
5.4 借助篩選工具完成篩選 128
5.4.1 創(chuàng)建切片器 128
5.4.2 設(shè)置切片器外觀 129
5.4.3 使用切片器篩選數(shù)據(jù) 129
5.4.4 降序排列切片器中的項目 130
5.4.5 日期字段的專用篩選器 130
5.4.6 日程表的應(yīng)用方法 131
拓展練習(xí)：用數(shù)據(jù)透視表分析電商銷售數(shù)據(jù) 131
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 136

第6章 高手都在用的數(shù)據(jù)分析工具
6.1 模擬運算 138
6.1.1 單變量求解 138
6.1.2 使用單變量模擬運算表 139
6.1.3 使用雙變量模擬運算表 140
6.2 方案分析 141
6.2.1 方案的創(chuàng)建 141
6.2.2 方案的編輯和刪除 142
6.2.3 創(chuàng)建方案摘要 142
6.2.4 方案的保護 143
6.3 規(guī)劃求解 143
6.3.1 加載規(guī)劃求解 143
6.3.2 建立規(guī)劃求解模型 144
6.3.3 使用規(guī)劃求解 144
拓展練習(xí)：計算貸款最佳償還期限及每期償還額 145
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 148

數(shù)據(jù)可視化篇 149
第7章 用圖表直觀呈現(xiàn)數(shù)據(jù)分析結(jié)果
7.1 圖表操作常識 151
7.1.1 認識常用的圖表類型 151
7.1.2 創(chuàng)建圖表 152
7.1.3 調(diào)整圖表的大小 153
7.1.4 移動圖表 154
7.1.5 更改圖表類型 154
7.1.6 創(chuàng)建復(fù)合圖表 155
7.2 圖表的快速布局及樣式設(shè)置 155
7.2.1 修改圖表標(biāo)題 155
7.2.2 快速更改圖表系列顏色 156
7.2.3 設(shè)置指定圖表系列的顏色 156
7.2.4 交換圖表坐標(biāo)軸 157
7.2.5 添加或刪除圖表系列 158
7.2.6 設(shè)置圖表字體 159
7.2.7 快速調(diào)整圖表布局 159
7.2.8 快速套用圖表樣式 159
7.3 圖表中的重要元素 160
7.3.1 常見的圖表元素 160
7.3.2 添加或刪除圖表元素 161
7.4 用微型圖表展示數(shù)據(jù)趨勢 161
7.4.1 迷你圖的類型 161
7.4.2 創(chuàng)建迷你圖 162
7.4.3 更改迷你圖類型 163
7.4.4 設(shè)置迷你圖樣式 163
7.4.5 設(shè)置重要標(biāo)記點的顏色 164
7.4.6 單獨編輯一組迷你圖中的其中一個 164
7.4.7 清除迷你圖 164
拓展練習(xí)：制作漸變銷售趨勢分析圖表 165
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 168

第8章 編輯圖表元素的不二法門
8.1 餅圖系列的典型編輯法 170
8.1.1 設(shè)置扇形的填充及邊框效果 170
8.1.2 旋轉(zhuǎn)餅圖角度 171
8.1.3 設(shè)置餅圖分離 172
8.2 柱形圖的經(jīng)典設(shè)置 173
8.2.1 更改三維柱形圖的柱體形狀 173
8.2.2 調(diào)整柱形的間距和寬度 173
8.2.3 用實物圖形替換柱形系列 174
8.2.4 設(shè)置透明柱形系列效果 175
8.3 坐標(biāo)軸的編輯技巧 176
8.3.1 修改垂直軸的取值范圍及刻度單位 176
8.3.2 翻轉(zhuǎn)坐標(biāo)軸 176
8.3.3 設(shè)置對數(shù)刻度 177
8.3.4 水平軸不顯示標(biāo)簽內(nèi)容的處理方法 177
8.3.5 調(diào)整坐標(biāo)軸值的數(shù)字格式 179
8.3.6 以萬為單位顯示坐標(biāo)軸值 179
8.4 數(shù)據(jù)標(biāo)簽的設(shè)置 180
8.4.1 將數(shù)據(jù)標(biāo)簽以百分比形式顯示 180
8.4.2 在數(shù)據(jù)標(biāo)簽中顯示單元格中的內(nèi)容 181
8.4.3 添加數(shù)據(jù)標(biāo)注 182
8.5 設(shè)置圖表背景 182
8.5.1 設(shè)置純色背景 182
8.5.2 設(shè)置漸變背景 182
8.5.3 設(shè)置圖片背景 183
8.5.4 隱藏背景 184
拓展練習(xí)：制作銷售分析玫瑰圖 184
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 189

第9章 數(shù)據(jù)在圖表中的完美演繹
9.1 創(chuàng)意餅圖的制作 191
9.1.1 制作階梯式三維立體餅圖 191
9.1.2 制作局部分離的復(fù)合餅圖 193
9.1.3 制作雙層復(fù)合餅圖 195
9.1.4 制作雙層補充說明圓環(huán)圖 196
9.2 樣式繁多的柱形圖 197
9.2.1 制作三等分背景板 197
9.2.2 制作色階對比效果圖表 199
9.2.3 制作數(shù)據(jù)對比旋風(fēng)圖 200
9.2.4 制作具有重要參考線的柱形圖 202
9.3 其他典型圖表的制作 204
9.3.1 制作帶中心發(fā)射線的漸變雷達圖 204
9.3.2 制作始終顯示最新信息的動態(tài)圖表 206
9.3.3 制作能突出最大值和最小值的折線圖 207
拓展練習(xí)：制作公司發(fā)展時間軸圖表 209
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 213

第10章 高大上的數(shù)據(jù)可視化看板
10.1 銷售數(shù)據(jù)的錄入和整理 215
10.1.1 用公式統(tǒng)計銷售數(shù)據(jù) 215
10.1.2 用圖標(biāo)展示完成率和環(huán)比 216
10.2 制作實際完成率分析圖 217
10.2.1 創(chuàng)建基礎(chǔ)圖表 217
10.2.2 設(shè)置柱形填充效果 218
10.2.3 設(shè)置數(shù)據(jù)標(biāo)簽 220
10.3 制作各店鋪完成率分析圖 220
10.3.1 制作完成率低于100%的圓環(huán)圖 220
10.3.2 制作完成率高于100%的圓環(huán)圖 221
10.4 制作兩個季度的銷售對比圖 222
10.4.1 創(chuàng)建組合圖表 222
10.4.2 設(shè)置標(biāo)簽格式 223
10.4.3 設(shè)置其他圖表元素 224
10.5 美化銷售數(shù)據(jù)分析可視化看板 225
10.5.1 設(shè)置數(shù)據(jù)源表格式 225
10.5.2 組合圖表 226
拓展練習(xí)：制作上半年財務(wù)運營數(shù)據(jù)看板 228
知識總結(jié)：用思維導(dǎo)圖學(xué)習(xí)數(shù)據(jù)分析 232

附錄
附錄A 完美輸出數(shù)據(jù)報表的方法 234
A.1 打印數(shù)據(jù)報表和圖表 234
A.1.1 設(shè)置黑白打印 234
A.1.2 打印網(wǎng)格線 235
A.1.3 設(shè)置打印份數(shù) 236
A.1.4 從指定位置分頁打印 236
A.1.5 控制圖表的打印 237
A.1.6 重復(fù)打印標(biāo)題行 238
A.1.7 打印指定區(qū)域 238
A.1.8 設(shè)置打印范圍 239
A.1.9 縮放打印 239
A.1.10 一張紙上打印一列超長內(nèi)容 240
A.1.11 為打印區(qū)域設(shè)置背景 241
A.2 設(shè)置打印頁面 242
A.2.1 設(shè)置紙張大小 242
A.2.2 設(shè)置橫向打印 243
A.2.3 調(diào)整頁邊距 243
A.2.4 設(shè)置居中打印 244
A.3 添加打印信息 245
A.3.1 添加打印日期和時間 245
A.3.2 打印logo 246
A.3.3 添加預(yù)定義的頁眉頁腳 247
A.3.4 設(shè)置奇偶頁不同的頁眉頁腳 248
A.4 導(dǎo)出數(shù)據(jù)分析結(jié)果 248
A.4.1 將報表導(dǎo)出成電子表格模板 248
A.4.2 將報表導(dǎo)出成PDF文件 249
A.4.3 以郵件形式發(fā)送數(shù)據(jù)報表 249

附錄B 不可不知的Excel操作術(shù)語 250