Excel數據分析與可視化一本通：數據處理·統計分析·圖表展示

數據分析篇 1
第1章 建立規範的數據源
1.1 必懂的3種數據類型 3
1.1.1 數值型數據 3
1.1.2 文本型數據 4
1.1.3 邏輯型數據 5
1.2 合理設置數據的格式 6
1.2.1 快速統一小數位數 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 沒有規矩不成方圓——數據驗證 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 數據源的規範整理 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 邏輯函數實現自動判斷 51
2.4.1 IF函數的應用 51
2.4.2 IF函數執行多重判斷 51
2.4.3 AND函數的應用 52
2.4.4 OR函數的應用 52
2.5 快速統計和匯總數據 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 數據的追蹤與查詢專業戶 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
拓展練習：製作工作服統計表 67
知識總結：用思維導圖學習數據分析 69

第3章 掌握日常數據分析與管理技能
3.1 輕鬆排序表格中的數據 71
3.1.1 執行昇冪或降冪操作 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 執行 篩選 78
3.3.3 篩選不重複值 78
3.3.4 在其他區域顯示篩選結果 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
拓展練習：分析發貨明細表 89
知識總結：用思維導圖學習數據分析 93

第4章 強大的多功能數據分析儀
4.1 創建數據透視表 95
4.1.1 數據透視表對數據源的要求 95
4.1.2 創建空白數據透視表 96
4.1.3 創建系統 的數據透視表 97
4.1.4 同時創建數據透視表和數據透視圖 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
拓展練習：創建薪酬管理數據透視表 111
知識總結：用思維導圖學習數據分析 114

第5章 多維度動態分析數據
5.1 數據透視表中的計算和匯總 116
5.1.1 對欄位分組 116
5.1.2 改值欄位匯總方式 117
5.1.3 改值的顯示方式 117
5.1.4 添加計算欄位 118
5.1.5 使用函數創建新欄位 119
5.1.6 用數據透視表合併多表數據 119
5.1.7 設置數值的小數位數 121
5.2 在數據透視表中執行排序 122
5.2.1 拖拽法手動排序 122
5.2.2 對 欄位進行排序 123
5.2.3 多個欄位同時排序 123
5.2.4 改文本欄位的自動排序依據 124
5.3 在數據透視表中執行篩選 125
5.3.1 篩選行欄位 125
5.3.2 篩選值欄位 125
5.3.3 篩選分類匯總大於 值的數據 126
5.3.4 添加篩選欄位執行篩選 127
5.3.5 分頁顯示篩選欄位專案 127
5.3.6 分頁列印篩選結果 128
5.4 借助篩選工具完成篩選 128
5.4.1 創建切片器 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 方案的創建 141
6.2.2 方案的編輯和刪除 142
6.2.3 創建方案摘要 142
6.2.4 方案的保護 143
6.3 規劃求解 143
6.3.1 載入規劃求解 143
6.3.2 建立規劃求解模型 144
6.3.3 使用規劃求解 144
拓展練習：計算貸款 償還期限及每期償還額 145
知識總結：用思維導圖學習數據分析 148

數據視覺化篇 149
第7章 用圖表直觀呈現數據分析結果
7.1 圖表操作常識 151
7.1.1 認識常用的圖表類型 151
7.1.2 創建圖表 152
7.1.3 調整圖表的大小 153
7.1.4 移動圖表 154
7.1.5 改圖表類型 154
7.1.6 創建複合圖表 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 創建迷你圖 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 創意圓形圖的製作 191
9.1.1 製作階梯式三維立體圓形圖 191
9.1.2 製作局部分離的複合圓形圖 193
9.1.3 製作雙層複合圓形圖 195
9.1.4 製作雙層補充說明圓環圖 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 製作帶中心發射線的漸變雷達圖 204
9.3.2 製作始終顯示 資訊的動態圖表 206
9.3.3 製作能突出 值和 小值的折線圖 207
拓展練習：製作公司發展時間軸圖表 209
知識總結：用思維導圖學習數據分析 213

0章 高大上的數據視覺化看板
10.1 銷售數據的錄入和整理 215
10.1.1 用公式統計銷售數據 215
10.1.2 用圖示展示完成率和環比 216
10.2 製作實際完成率分析圖 217
10.2.1 創建基礎圖表 217
10.2.2 設置柱形填充效果 218
10.2.3 設置數據標籤 220
10.3 製作各店鋪完成率分析圖 220
10.3.1 製作完成率低於 的圓環圖 220
10.3.2 製作完成率高於 的圓環圖 221
10.4 製作兩個季度的銷售對比圖 222
10.4.1 創建組合圖表 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 列印 區域 238
A.1.8 設置列印範圍 239
A.1.9 縮放列印 239
A.1.10 一張紙上列印一列超長內容 240
A.1.11 為列印範圍設置背景 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 以郵件形式發送數據報表 249

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