Excel 2016數據處理與分析（微課版）

第1章 Excel基礎 9
1．1　熟悉Excel 2016的介面　9
1．2　可定制的功能區與快速訪問工具列　10
1．2．1　可定制的功能區　11
1．2．2　可定制的快速訪問工具列　13
1．3　創建與保存工作簿　14
1．3．1　創建工作簿　15
1．3．2　保存工作簿　17
1．4　工作簿保護　18
1．5　檔案格式與相容性　20
1．5．1　Excel 2016檔案格式　21
1．5．2　檢查相容性　21

第2章 資料登錄　24
2．1　認識Excel中的資料類型　24
2．1．1　數值　24
2．1．2　日期和時間　25
2．1．3　文本　26
2．1．4　公式　27
2．1．5　邏輯值　27
2．1．6　錯誤值　27
2．2　認識自動填充　27
2．2．1　數值的填充　27
2．2．2　日期的自動填充　29
2．2．3　文本的自動填充　29
2．2．4　特殊文本的填充　30
2．2．5　自訂序列　30
2．2．6　填充公式　31
2．2．7　巧用右鍵和按兩下填充　32
2．2．8　快速填充　33
2．3　快速分析　35
2．4　資料登錄常用技巧　36
2．4．1　儲存格內容換行　37
2．4．2　自動校正的使用　38
2．4．3　正確輸入分數　39
2．4．4　在多個儲存格同時輸入資料　39

第3章　工作表日常操作　41
3．1　行和列的基本操作　41
3．1．1　選擇行和列　41
3．1．2　設置行高和列寬　42
3．1．3　插入行與列　45
3．1．4　移動和複製行和列　46
3．2　儲存格和區域　48
3．2．1　定位首末儲存格　49
3．2．2　水準、垂直方向定位　49
3．2．3　選取矩形區域　49
3．2．4　選取當前行或列的資料區域　50
3．2．5　選取與目前的儲存格相鄰的連續區域和非連續區域　51
3．2．6　選取多個工作表的相同區域　52
3．2．7　選取特殊的區域　53
3．3　工作視窗的視圖控制　56
3．3．1　工作簿的多視窗顯示　56
3．3．2　凍結窗格　58
3．4　工作表列印　60
3．4．1　快速列印　60
3．4．2　預覽列印　61
3．4．3　調整頁面設置　61
3．4．4　讓每一頁都列印出標題行　62
3．4．5　單色列印　63
3．4．6　一次列印多個工作表　65

第4章　資料格式化　66
4．1　數位格式化　66
4．1．1　快速應用數位格式　66
4．1．2　Excel內置的數位格式　68
4．1．3　自訂數位格式　70
4．1．4　將數字格式轉化為實際值　75
4．2　儲存格樣式　77
4．2．1　應用內置樣式　77
4．2．2　創建新樣式　78
4．3　【表格】樣式　79
4．3．1　創建【表格】　79
4．3．2　使用“套用表格格式”功能　80

第5章　資料整理　82
5．1　查找與替換功能　82
5．1．1　利用查找功能快速查詢資料　82
5．1．2　使用替換功能快速更改資料內容　84
5．1．3　使用萬用字元實現模糊查找　84
5．2　選擇性粘貼　86
5．2．1　粘貼選項　87
5．2．2　運算功能　88
5．2．3　跳過空儲存格　88
5．2．4　轉置　89
5．2．5　粘貼連結　89
5．3　分列功能　90
5．3．1　按“分隔符號號”提取目標欄位　90
5．3．2　快速將文本型數位轉換為數值型資料　92
5．3．3　轉換日期資料　93
5．4　刪除重複值　94

第6章　數據排序　97
6．1　簡單排序　97
6．2　以當前選中的區域排序　98
6．3　按多個關鍵字排序　99
6．4　自訂序列排列　101
6．5　按顏色排序　104
6．6　按筆劃排序　105
6．7　按行排序　106
6．8　返回排序前的狀態　107
6．9　利用排序功能製作工資條　108

第7章　數據篩選　110
7．1　篩選　110
7．1．1　認識篩選　110
7．1．2　按照數位特徵進行篩選　112
7．1．3　按照日期的特徵進行篩選　114
7．1．1　篩選多列數據　116
7．1．2　取消篩選　117
7．2　高級篩選　118
7．2．1　設置高級篩選的條件區域　118
7．2．2　簡單條件的高級篩選　119
7．2．3　“關係與”條件的高級篩選　120
7．2．4　“關係或”條件的高級篩選　121
7．2．5　使用計算條件的高級篩選　122
7．2．6　篩選不重複值　123
7．2．7　精確匹配的篩選條件　124
7．2．8　將篩選結果輸出到其它工作表　126

第8章　公式與函數　128
8．1　公式和函數基礎　128
8．1．1　什麼是公式與函數　128
8．1．2　公式中的運算子　129
8．1．3　公式中的資料類型　130
8．1．4　認識儲存格引用　131
8．2　Excel函數的概念和結構　135
8．2．1　函數的結構　135
8．2．2　可選參數與必需參數　136
8．2．3　函數的作用與分類　136
8．2．4　函數的輸入與編輯　138
8．2．5　檢查函數出錯原因　142
8．2．6　函數的自動重算和手動重算　144
8．3　認識名稱　145
8．3．1　名稱的概念　145
8．3．2　創建名稱　145
8．3．3　名稱的級別　147
8．3．4　在公式中使用名稱　148
8．3．5　編輯和刪除已有名稱　150
8．3．6　使用名稱的注意事項　151
8．4　資訊與邏輯函數的應用　152
8．4．1　IF函數判斷條件真假　152
8．4．2　邏輯函數與乘法、加法運算　153
8．4．3　遮罩函數公式返回的錯誤值　154
8．4．4　常用的IS類資訊函數　155
8．5　文本類函數的應用　156
8．5．1　認識文本連接子　156
8．5．2　全形字符和半形字元　157
8．5．3　字串提取　158
8．5．4　查找字元　161
8．5．5　替換字元　163
8．5．6　格式化文字　165
8．6　查找與引用類函數的應用　169
8．6．1　使用VLOOKUP函數進行資料查詢匹配　169
8．6．2　使用INDEX和MATCH函數組合進行資料查詢匹配　173
8．6．3　使用LOOKUP函數進行資料查詢匹配　175
8．6．4　使用INDIRECT函數進行資料查詢匹配　178
8．6．5　OFFSET函數在資料查詢中的應用　181
8．6．6　使用公式創建超連結　182
8．7　求和與統計類函數的應用　184
8．7．1　基本求和函數的應用　184
8．7．2　條件求和函數的應用　185
8．7．3　基礎計數函數的應用　190
8．7．4　條件計數類函數的應用　193
8．7．5　SUMPRODUCT函數應用　199
8．7．6　平均值函數的應用　202
8．7．7　其它常用數學統計函數的應用　206
8．7．8　FREQUENCY函數的應用　208
8．7．9　篩選狀態下的資料統計　210
8．8　數學類函數的應用　213
8．8．1　認識MOD函數　214
8．8．2　常用的取捨函數　215
8．9　日期和時間函數的應用　217
8．9．1　使用EDATE函數進行簡單的日期計算　218
8．9．2　認識DATEDIF函數　219
8．9．3　星期有關的計算　220
8．9．4　月份有關的計算　221
8．9．5　季度有關的計算　222
8．9．6　時間的加減計算　222
8．10　函數公式在資料排名上的應用　223
8．10．1　認識RANK函數　224
8．10．2　計算中國式排名　224
8．11　使用公式進行描述分析　226
8．12　使用公式進行預測分析　228
8．12．1　移動平均預測　228
8．12．2　線性回歸預測　228
8．12．3　指數回歸預測　229
8．12．4　多項式擬合和預測　230

第9章　資料驗證　234
9．1　認識資料驗證　234
9．2　設置輸入提示資訊和出錯警告提示　237
9．2．1　設置輸入資訊提示　237
9．2．2　設置出錯警告提示資訊　238
9．3　圈釋無效數據　239
9．4　利用資料驗證限制輸入重復資料　241
9．5　在資料登錄時提供下拉式功能表　242
9．5．1　製作下拉式功能表　243
9．5．2　製作二級下拉式功能表　244

第10章 獲取外部資料來源　249
10．1　常用的導入外部資料的方法　249
10．1．1　從文字檔導入數據　249
10．1．2　從Access資料庫檔導入資料　252
10．1．3　自網站獲取資料　254
10．1．4　通過【現有連接】導入Excel資料　256
10．2　使用Microsoft Query導入外部資料　257

第11章 使用樞紐分析表分析資料　263
11．1　初識樞紐分析表　263
11．1．1　便捷的多角度匯總　263
11．1．2　認識樞紐分析表結構　264
11．1．3　樞紐分析表常用術語　265
11．1．4　樞紐分析表可使用的資料來源　266
11．2　創建□ □個樞紐分析表　266
11．3　設置樞紐分析表佈局，多角度展示數據　268
11．3．1　改變樞紐分析表的整體佈局　268
11．3．2　樞紐分析表報表篩選器的使用　270
11．4　整理樞紐分析表欄位　273
11．4．1　重命名欄位　273
11．4．2　刪除欄位　273
11．4．3　隱藏欄位標題　274
11．4．4　活動欄位的折疊與展開　275
11．5　改變樞紐分析表的報告格式　276
11．5．1　報表佈局　276
11．5．2　分類匯總的顯示方式　278
11．6　套用樞紐分析表樣式　279
11．7　刷新樞紐分析表　279
11．7．1　手動刷新　280
11．7．2　打開文件時刷新　280
11．8　認識樞紐分析表切片器和日程表　281
11．8．1　插入切片器　282
11．8．1　設置切片器專案多選　283
11．8．2　清除切片器的篩選　284
11．8．3　多個樞紐分析表聯動　284
11．8．4　切片器樣式設置　285
11．8．5　刪除切片器　286
11．8．6　使用日程表查看不同階段的資料　286
11．8．7　日程表的簡單操作　287
11．9　樞紐分析表中的專案組合　288
11．9．1　日期項組合　288
11．9．2　數值項組合　289
11．9．3　取消組合及組合出錯的原因　291
11．10　選擇不同的資料匯總方式　291
11．10．1　對同一欄位使用多種匯總方式　292
11．10．2　豐富的值顯示方式　292
11．11　在樞紐分析表中使用計算欄位和計算項　297
11．11．1　創建計算欄位　298
11．11．2　添加計算項　299
11．12　使用資料透視圖展示資料　301
11．12．1　以資料表創建資料透視圖　302
11．12．2　以現有樞紐分析表創建資料透視圖　304
11．12．3　數據透視圖術語　305
11．12．4　資料透視圖的限制　306

第12章 資料分析工具的應用　309
12．1　模擬分析　309
12．1．1　使用模擬運算表進行假設分析　309
12．1．2　通過方案管理器進行多變數假設分析　312
12．1．3　單變數求解　315
12．2　規劃求解　317
12．2．1　在Excel中安裝規劃求解工具　317
12．2．2　求解□佳數位組合　318
12．3　分析工具庫　321
12．3．1　載入分析工具庫　321
12．3．2　百分比排名　322
12．3．3　描述統計　324
12．3．4　長條圖分析　327
12．3．5　相關分析　329

第13章 數據視覺化　332
13．1　條件格式　332
13．1．1　基於各類特徵設置條件格式　332
13．1．2　自訂規則的應用　336
13．1．3　內置的儲存格圖形效果樣式　338
13．1．4　修改和清除條件格式　342
13．1．5　調整規則優先順序　344
13．2　圖表應用　345
13．2．1　圖表的構成元素　345
13．2．2　圖表類型的選擇　346
13．2．3　製作帶平均線的直條圖　349
13．2．4　突出顯示□高值的折線圖　352
13．2．5　使用漏斗圖分析不同階段的轉化情況　353
13．2．6　使用瀑布圖表分析專案營收情況　358
13．2．7　動態顯示□近7天數據的直條圖　360
13．2．8　樹狀圖　363
13．3　認識迷你圖　364
13．3．1　創建迷你圖　365
13．3．2　更改迷你圖類型　365
13．3．3　突出顯示資料點　366
13．3．4　設置迷你圖樣式　366
13．3．5　清除迷你圖　367

第14章 Power Query和Power Pivot的簡單應用　369
14．1　Power Query　369
14．1．1　二維表轉換為一維表　369
14．1．2　合併同一工作簿中的多個工作表　372
14．1．3　合併同一資料夾下的多個工作簿　375
14．2　Power Pivot　378
14．2．1　利用資料模型進行非重複值計數　379
14．2．2　創建簡單的“關係”型資料模型　381
14．2．3　DAX語言基礎　386
14．2．4　合併同類項　388