Excel數據分析與可視化一本通

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

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

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

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

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

第6章 高手都在用的數據分析工具
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
拓展練習：計算貸款最佳償還期限及每期償還額 145
知識總結：用思維導圖學習數據分析 148

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

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

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

第10章 高大上的數據可視化看板
10.1 銷售數據的錄入和整理 215
10.1.1 用公式統(tǒng)計銷售數據 215
10.1.2 用圖標展示完成率和環(huán)比 216
10.2 制作實際完成率分析圖 217
10.2.1 創(chuàng)建基礎圖表 217
10.2.2 設置柱形填充效果 218
10.2.3 設置數據標簽 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 設置標簽格式 223
10.4.3 設置其他圖表元素 224
10.5 美化銷售數據分析可視化看板 225
10.5.1 設置數據源表格式 225
10.5.2 組合圖表 226
拓展練習：制作上半年財務運營數據看板 228
知識總結：用思維導圖學習數據分析 232

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

附錄B 不可不知的Excel操作術語 250