Excel高效辦公：數(shù)據(jù)處理與分析（全新精華版）

Chapter 01   輸入數(shù)據(jù)有捷徑
1.1 常規(guī)數(shù)據(jù)的輸入 ............................ 2
1.1.1 數(shù)據(jù)輸入的方式 .............................2
1.1.2 輸入數(shù)值型數(shù)據(jù) .............................3
1 輸入負數(shù) ............................................ 3
2 輸入分數(shù) ............................................ 3
3 輸入小數(shù) ............................................. 4
4 輸入帶有默認小數(shù)位數(shù)的數(shù)字 ..................... 5
5 輸入以 0 開頭的數(shù)字 ................................ 5
6 輸入帶單位的數(shù)據(jù) .................................... 7
1.1.3 輸入時間日期型數(shù)據(jù) ........................... 8
1 輸入日期數(shù)據(jù) .......................................... 8
2 輸入時間數(shù)據(jù) ........................................... 9
3 輸入當前的時間和日期 ............................ 9
1.1.4 輸入文本型數(shù)據(jù) ................................. 10
1.2  特殊數(shù)據(jù)的輸入 ................................. 11
1.2.1 身份證號碼的輸入 ............................... 11
1.2.2 特殊符號的輸入 ................................... 12
1.3  更高效的數(shù)據(jù)輸入 . ................................ 13
1.3.1 輸入有規(guī)律的數(shù)據(jù) .............................. 13
1 填充序列 ................................................. 13
2 對話框填充 ............................................ 14
3 自定義填充 ............................................. 15
1.3.2 輸入相同數(shù)據(jù) . ................................... 16
1 使用組合鍵輸入相同數(shù)據(jù) ...................... 16
2 使用成組工作表輸入相同數(shù)據(jù) .................... 17
1.3.3 數(shù)據(jù)自動輸入 . ......................................... 18
1 記憶式輸入數(shù)據(jù) ......................................... 18
2 使用下拉列表輸入數(shù)據(jù) .............................. 18
1.3.4 有效性數(shù)據(jù)輸入 ...................................... 19
Chapter 02   格式化數(shù)據(jù)很關鍵
2.1 單元格格式設置 ..................................... 23
2.1.1 設置字體格式 . ......................................... 23
1 設置字體和字號 ......................................... 23
2 設置字體的顏色和字形 .............................. 24
2.1.2 設置對齊方式 . ......................................... 25
2.1.3 添加邊框 ................................................. 26
2.1.4 設置底紋顏色 ......................................... 27
1 設置填充顏色 ............................................. 27
2 設置填充圖案 ............................................. 29
3 添加背景圖片 ............................................. 29
2.2 單元格樣式的使用 . ................................ 30
2.2.1 內置單元格樣式 ...................................... 30
2.2.2 自定義單元格樣式 ................................... 31
2.2.3 樣式的編輯 ............................................. 33
1 修改樣式 .................................................... 33
2 刪除樣式 .................................................... 34
3 合并樣式 .................................................... 34
2.3 表格格式的使用 ..................................... 35
2.3.1 套用表格格式 . ......................................... 35
2.3.2 自定義表格格式 ...................................... 36
2.3.3 撤銷表格格式 . ......................................... 38
Chapter 03   公式與函數(shù)不得不學
3.1 公式與函數(shù)的簡介 . ................................ 40
3.1.1 公式與函數(shù)的組成 .................................. 40
3.1.2 運算符 . .................................................... 40
1 算術運算符 ................................................ 40
2 比較運算符 ................................................ 41
3 文本運算符 ................................................ 42
4 引用運算符 ................................................ 42
5 運算符的運算規(guī)律 ..................................... 43
3.1.3 單元格的引用 .......................................... 43
3.2 公式與函數(shù)的基本操作 .................. 46
3.2.1 公式與函數(shù)的輸入 .................................. 46
1 公式的輸入 ................................................ 46
2 函數(shù)的輸入 ................................................ 47
3.2.2 公式與函數(shù)的編輯 .................................. 49
1 公式與函數(shù)的修改 ..................................... 49
2 公式與函數(shù)的復制 ..................................... 50
3 公式與函數(shù)的隱藏 ..................................... 52
3.2.3 名稱的使用 ............................................. 53
1 定義名稱 .................................................... 53
2 管理名稱 .................................................... 56
3.2.4 公式的審核 ............................................. 57
1 顯示公式 .................................................... 57
2 檢查公式錯誤 ............................................ 57
3 常見的錯誤信息 ......................................... 59
4 查找循環(huán)引用 ............................................. 60
3.3 統(tǒng)計函數(shù) ................................................... 60
1 AVEDEV 函數(shù) ........................................... 60
2 AVERAGE 函數(shù) ......................................... 61
3 AVERAGEA 函數(shù) ....................................... 61
4 AVERAGEIF 函數(shù) ...................................... 61
5 AVERAGEIFS 函數(shù) .................................... 62
6 COUNT 函數(shù) .............................................. 63
7 COUNTA 函數(shù) ........................................... 63
8 COUNTBLANK 函數(shù) .................................. 63
9 COUNTIF 函數(shù) ........................................... 64
10 COUNTIFS 函數(shù) ........................................ 65
11 MAX 函數(shù) ................................................. 65
12 MAXA 函數(shù) ............................................... 65
13 MIN 函數(shù) ................................................... 66
14 MINA 函數(shù) ................................................ 66
15 RANK 函數(shù) ................................................ 67
16 MODE 函數(shù) ............................................... 67
17 SMALL 函數(shù) .............................................. 68
3.4 查找與引用函數(shù) ..................................... 68
1 ADDRESS 函數(shù) ......................................... 68
2 AREAS 函數(shù) .............................................. 69
3 CHOOSE 函數(shù) ............................................ 69
4 COLUMN 函數(shù) ........................................... 70
5 ROW 函數(shù) ................................................. 70
6 COLUMNS 函數(shù) .......................................... 71
7 ROWS 函數(shù) ................................................ 71
8 FORMULATEXT 函數(shù) ................................ 71
9 HLOOKUP 函數(shù) ......................................... 72
10 VLOOKUP 函數(shù) ......................................... 72
11 LOOKUP 函數(shù) ........................................... 73
12 INDEX 函數(shù) ............................................... 74
13 INDIRECT 函數(shù) .......................................... 75
14 MATCH 函數(shù) ............................................. 75
15 OFFSET 函數(shù) ............................................ 76
16 TRANSPOSE 函數(shù) ..................................... 77
3.5 日期與時間函數(shù) ..................................... 78
3.5.1 日期函數(shù) ................................................. 78
1 DATE 函數(shù) ................................................ 78
2 DATEVALUE 函數(shù) ..................................... 79
3 YEAR 函數(shù) ................................................ 80
4 MONTH 函數(shù) ............................................ 80
5 DAY 函數(shù) .................................................. 81
6 DAYS 函數(shù) ................................................ 81
7 DAYS360 函數(shù) ......................................... 81
8 NETWORKDAYS 函數(shù) ............................... 82
9 WORKDAY 函數(shù) ........................................ 83
10 EDATE 函數(shù) .............................................. 83
11 EOMONTH 函數(shù) ........................................ 83
12 TODAY 函數(shù) ............................................. 84
13 NOW 函數(shù) ................................................. 85
14 DATEDIF 函數(shù) ........................................... 85
15 WEEKDAY 函數(shù) ........................................ 86
3.5.2 時間函數(shù) ................................................. 86
1 TIME 函數(shù) ................................................. 86
2 TIMEVALUE 函數(shù) ...................................... 87
3 HOUR 函數(shù) ............................................... 87
4 MINUTE 函數(shù) ............................................ 89
5 SECOND 函數(shù) ............................................ 89
3.6 數(shù)學與三角函數(shù) ..................................... 90
1 ABS 函數(shù) ................................................... 90
2 CEILING 函數(shù) ............................................. 90
3 FLOOR 函數(shù) .............................................. 90
4 INT 函數(shù) .................................................... 91
5 MOD 函數(shù) ................................................. 91
6 PRODUCT 函數(shù) ......................................... 92
7 SUM 函數(shù) .................................................. 92
8 SUMIF 函數(shù) ............................................... 93
9 SUMIFS 函數(shù) ............................................. 93
10 SUMPRODUCT 函數(shù) ................................. 94
11 RAND 函數(shù) ................................................ 94
12 ROUND 函數(shù) ............................................. 95
3.7 邏輯函數(shù) ................................................... 95
1 AND 函數(shù) .................................................. 95
2 OR 函數(shù) ..................................................... 96
3 IF 函數(shù) ....................................................... 96
4 IFERROR 函數(shù) ........................................... 97
5 IFNA 函數(shù) ................................................. 97
6 NOT 函數(shù) .................................................. 98
7 FALSE 和 TRUE 函數(shù) ................................ 98
3.8 文本函數(shù) ................................................... 98
1 CONCATENATE 函數(shù) ................................ 98
2 EXACT 函數(shù) .............................................. 99
3 FIND 函數(shù) .................................................. 99
4 FINDB 函數(shù) ............................................. 100
5 LEFT 函數(shù) ............................................... 100
6 RIGHT 函數(shù) ............................................. 100
7 LEN 函數(shù) ................................................. 101
8 LOWER 函數(shù) ........................................... 101
9 UPPER 函數(shù) ............................................ 101
10 MID 函數(shù) ................................................. 102
11 REPLACE 函數(shù) ........................................ 103
12 SUBSTITUTE 函數(shù) .................................. 103
13 TEXT 函數(shù) .............................................. 103
14 TRIM 函數(shù) ............................................... 104
3.9 其他函數(shù) ................................................. 104
1 DB 函數(shù) ................................................... 104
2 SYD 函數(shù) ................................................. 105
3 ACCRINT 函數(shù) ......................................... 105
4 INTRATE 函數(shù) ......................................... 106
5 COUPDAYS 函數(shù) ..................................... 106
6 CUMIPMT 函數(shù) ....................................... 107
7 PMT 函數(shù) ................................................ 107
8 EFFECT 函數(shù) ........................................... 108
9 PV 函數(shù) ................................................... 108
10 DSUM 函數(shù) ............................................. 108
11 DCOUNT 函數(shù) ......................................... 109
12 TYPE 函數(shù) ............................................... 109
Chapter 04   數(shù)據(jù)管理與分析
4.1 數(shù)據(jù)排序 .................................................. 111
4.1.1 基本排序 ................................................ 111
1 按單個字段進行排序 ................................ 111
2 按多個字段進行排序 ................................ 112
4.1.2 特殊排序 ................................................ 113
1 排序時列序號不變 ............................. 113
2 按筆劃排序 .............................................. 114
3 按行排序 ................................................... 115
4 按單元格底紋排序 ................................... 116
4.1.3 自定義排序 ............................................ 117
4.2 數(shù)據(jù)篩選 ................................................. 118
4.2.1 快速篩選 ............................................... 118
4.2.2 自定義篩選 ............................................ 119
1 數(shù)字篩選 .................................................. 119
2 前 10 項篩選 ............................................ 120
3 文本篩選 ................................................... 121
4 日期篩選 .................................................. 122
5 底紋顏色篩選 ........................................... 122
4.2.3 高級篩選 ............................................... 123
1 高級篩選的“與”關系 ............................. 123
2 高級篩選的“或”關系 ............................. 124
4.3 分類匯總 ................................................. 125
4.3.1 簡單分類匯總 . ....................................... 125
4.3.2 多重分類匯總 . ....................................... 126
4.3.3 復制分類匯總的結果 . ............................ 128
4.3.4 隱藏分級顯示 . ....................................... 129
4.3.5 分頁顯示分類匯總 ................................ 130
4.4 合并計算 ................................................. 132
4.4.1 按位置合并計算 .................................... 132
4.4.2 復雜結構多表合并計算 ......................... 133
4.4.3 設置合并計算的函數(shù) . ............................ 135
4.4.4 編輯引用區(qū)域 ........................................ 135
1 修改引用區(qū)域 ........................................... 135
2 刪除引用區(qū)域 ........................................... 137
4.5 條件格式 ................................................. 137
4.5.1 創(chuàng)建條件格式 . ....................................... 137
1 突出顯示單元格 ....................................... 137
2 突出顯示文本 ........................................... 138
3 前 / 后規(guī)則 ....................................... 139
4 數(shù)據(jù)條 ...................................................... 140
5 色階 ......................................................... 141
6 圖標集 ...................................................... 142
4.5.2 管理條件格式 . ....................................... 143
1 編輯條件格式 .......................................... 143
2 刪除條件格式 ........................................... 144
3 復制條件格式 ........................................... 145
4.5.3 條件格式的優(yōu)先級 ................................ 145
Chapter 05   數(shù)據(jù)的動態(tài)統(tǒng)計分析
5.1 創(chuàng)建數(shù)據(jù)透視表 .................................. 148
1 快速創(chuàng)建數(shù)據(jù)透視表 ............................... 148
2 創(chuàng)建空白的數(shù)據(jù)透視表 ............................ 149
3 在透視表中添加內容 ................................ 150
5.2 設置數(shù)據(jù)透視表字段 .......................... 152
5.2.1 自定義字段名稱 .................................... 152
5.2.2 活動字段的展開和折疊 ......................... 153
5.2.3.隱藏字段標題 . ....................................... 154
5.2.4 刪除字段 ............................................... 155
5.2.5 設置字段的匯總方式 ............................ 155
1 設置“值”區(qū)域的字段 ............................. 155
2 設置“行”區(qū)域的字段 ............................. 157
5.2.6 設置數(shù)據(jù)的顯示方式 ............................ 158
5.3 編輯數(shù)據(jù)透視表 ................................... 160
5.3.1 移動數(shù)據(jù)透視表 .................................... 160
5.3.2 刷新數(shù)據(jù)透視表 ..................................... 161
1 手動刷新數(shù)據(jù)透視表 ............................... 161
2 自動刷新數(shù)據(jù)透視表 ................................ 163
5.3.3 更改源數(shù)據(jù) ........................................... 163
5.3.4 數(shù)據(jù)透視表的排序 ................................ 164
1 對數(shù)值進行排序 ....................................... 164
2 對“行”區(qū)域字段進行排序 ..................... 165
5.3.5 數(shù)據(jù)透視表的篩選 ................................ 166
1 使用字段下拉列表篩選 ............................ 166
2 使用標簽篩選 ........................................... 167
3 使用值篩選 .............................................. 168
5.3.6 應用條件格式 . ....................................... 169
5.3.7 分頁顯示 ............................................... 170
5.3.8.按部門分頁打印 ..................................... 171
5.4 數(shù)據(jù)透視表的布局 . .............................. 172
5.4.1 數(shù)據(jù)透視表的布局調整 ......................... 172
5.4.2 數(shù)據(jù)透視表的布局形式 ......................... 174
5.4.3 數(shù)據(jù)透視表的經典布局 ......................... 175
5.5 數(shù)據(jù)透視表的美化 . .............................. 176
5.5.1 套用數(shù)據(jù)透視表的樣式 ......................... 176
5.5.2 自定義數(shù)據(jù)透視表樣式 ......................... 178
5.6 數(shù)據(jù)篩選器 ............................................ 180
5.6.1 切片器 ................................................... 180
1 插入切片器 .............................................. 180
2 篩選字段 .................................................. 181
3 多列顯示切片器內的字段 ........................ 182
4 對切片器內的字段排序 ............................ 183
5 隱藏切片器 .............................................. 184
5.6.2 美化切片器 ........................................... 184
1 套用切片器的樣式 ................................... 184
2 自定義切片器樣式 ................................... 185
5.6.3 日程表 ................................................... 186
1 插入日程表 .............................................. 186
2 利用日程表進行篩選 ................................ 187
3 設置日程表樣式 ....................................... 187
5.7 數(shù)據(jù)透視圖 ............................................ 188
5.7.1 創(chuàng)建數(shù)據(jù)透視圖 .................................... 188
5.7.2 編輯數(shù)據(jù)透視圖 ................................... 189
1 更改數(shù)據(jù)透視圖的類型 ............................ 189
2 設置數(shù)據(jù)透視圖的布局 ............................ 190
3 刪除數(shù)據(jù)透視圖 ....................................... 191
5.7.3 美化數(shù)據(jù)透視圖 .................................... 192
Chapter 06   數(shù)據(jù)分析工具的借用
6.1 模擬運算表 ............................................ 195
6.1.1 單變量模擬運算表 ................................ 195
6.1.2 雙變量模擬運算表 ................................ 196
6.2 單變量求解 ............................................ 197
6.3 方案分析 ................................................. 199
6.3.1 方案的創(chuàng)建 ........................................... 199
6.3.2 方案的基本操作 .................................... 200
1 編輯方案 .................................................. 201
2 刪除方案 .................................................. 201
3 創(chuàng)建方案摘要 ........................................... 202
6.3.3 方案的保存 ........................................... 202
6.4 審核與跟蹤 ........................................... 204
6.4.1 跟蹤引用單元格 .................................... 204
6.4.2 跟蹤從屬單元格 .................................... 205
Chapter 07   數(shù)據(jù)的可視化處理
7.1 圖表概述 ................................................. 207
7.1.1 圖表的類型 ........................................... 207
1 柱形圖 ...................................................... 207
2 折線圖 ...................................................... 208
3 餅圖 ......................................................... 208
4 條形圖 ...................................................... 209
5 面積圖 ...................................................... 209
6 X Y 散點圖 .............................................. 210
7 曲面圖 ...................................................... 210
8 股價圖 ....................................................... 211
9 雷達圖 ....................................................... 211
10 樹狀圖 ...................................................... 212
11 旭日圖 ...................................................... 212
12 直方圖 ...................................................... 212
13 箱形圖 ...................................................... 213
14 瀑布圖 ...................................................... 213
15 組合圖表 .................................................. 213
7.1.2 圖表的創(chuàng)建 ........................................... 214
1 快捷鍵創(chuàng)建圖表 ....................................... 214
2 功能區(qū)創(chuàng)建圖表 ....................................... 214
3 對話框創(chuàng)建圖表 ....................................... 214
7.2 圖表的編輯 ............................................ 215
7.2.1 圖表的布局設置 .................................... 215
1 圖表標題的編輯 ....................................... 215
2 圖例的編輯 .............................................. 217
3 數(shù)據(jù)標簽和數(shù)據(jù)表的添加 ........................ 217
4 坐標軸的編輯 ........................................... 217
5 快速布局 .................................................. 218
7.2.2 圖表類型的更改. ................................... 219
7.2.3 圖表數(shù)據(jù)源的更改. ............................... 219
1 刪除數(shù)據(jù)系列 ........................................... 219
2 添加數(shù)據(jù)系列 ........................................... 220
7.3 圖表的美化 ..................................... 221
7.3.1 圖表樣式的應用 .................................... 221
7.3.2 形狀樣式的應用 .................................... 222
7.3.3 藝術字樣式的設置 ................................ 223
7.4 圖表分析 ................................................. 224
7.4.1 趨勢線 . .................................................. 224
7.4.2 誤差線 ................................................... 226
7.4.3 漲/跌柱線 ............................................. 227
7.5 迷你圖 ........................................................ 227
7.5.1 單個迷你圖的創(chuàng)建 ................................ 227
7.5.2 一組迷你圖的創(chuàng)建 ................................ 229
1 填充法 ...................................................... 229
2 插入法 ...................................................... 229
7.5.3 迷你圖類型的更改 ................................ 230
1 更改一組迷你圖類型 ............................... 230
2 更改一組迷你圖中的單個迷你圖類型 ...... 230
7.5.4 自定義迷你圖 ........................................ 231
1 迷你圖值點的顯示 ................................... 231
2 迷你圖樣式的設計 ................................... 232
3 迷你圖坐標的設置 ................................... 234
Chapter 08   獲取外部數(shù)據(jù)有方法
8.1 外部數(shù)據(jù)的獲取 ................................... 236
8.1.1 Access數(shù)據(jù)的導入 . ............................... 236
8.1.2 網站數(shù)據(jù)的導入 .................................... 237
8.1.3 文本數(shù)據(jù)的導入 .................................... 238
8.1.4 使用Microsoft.Query導入外部數(shù)據(jù) . .... 239
8.1.5 使用“現(xiàn)有連接”導入外部數(shù)據(jù) ........... 241
8.2 數(shù)據(jù)連接設置 ........................................ 242
8.2.1 連接提醒的設置 .................................... 242
8.2.2 連接屬性的更改 .................................... 243
8.2.3 連接的斷開. .......................................... 245
Chapter 09   生產管理數(shù)據(jù)處理
9.1 實例簡介 ................................................. 247
1 生產函數(shù) .................................................. 247
2 XY 散點圖 ............................................... 247
3 生產計劃表 .............................................. 247
4 質量管理 .................................................. 247
5 SmartArt 圖形 ........................................ 247
6 ABC 分類庫存控制法 .............................. 247
9.2 生產函數(shù) ................................................ 248
9.2.1 生產函數(shù)的簡述 .................................... 248
9.2.2 生產函數(shù)的類型 .................................... 248
1 固定替代比例生產函數(shù) ............................ 248
2 固定投入比例生產函數(shù) ............................ 249
3 柯布 - 道格拉斯生產函數(shù) ........................ 249
9.2.3 繪制生產函數(shù)關系圖 . ............................ 249
9.2.4 保存生產函數(shù)關系圖 . ............................ 251
9.3 生產計劃表 ............................................ 254
9.3.1 創(chuàng)建生產計劃表 .................................... 254
1 創(chuàng)建生產計劃表并輸入數(shù)據(jù) .................... 254
2 設置生產計劃表格式 ............................... 255
9.3.2 在表格中添加函數(shù) ................................ 257
9.4 質量管理 ................................................. 258
9.4.1 質量管理的發(fā)展過程 ............................ 258
9.4.2 精益質量管理模型 ................................ 259
1 創(chuàng)建管理模型 ........................................... 259
2 設置管理模型的格式 ............................... 261
9.4.3 質量評價 ............................................... 263
9.5 生產庫存管理 ........................................ 263
9.5.1 ABC分類庫存控制法 ............................. 263
9.5.2 制作ABC分析表 .................................... 264
1 創(chuàng)建分析表 .............................................. 264
2 分析表數(shù)據(jù)分類方法 ............................... 266
Chapter 10   進銷存數(shù)據(jù)處理
10.1 實例簡介 ............................................. 268
1 采購成本分析 ........................................... 268
2 采購申請單 .............................................. 268
3 采購統(tǒng)計表 .............................................. 268
4 銷售收入 .................................................. 268
5 銷售統(tǒng)計表 .............................................. 268
6 定價和成本 .............................................. 268
7 庫存的數(shù)據(jù)管理 ....................................... 269
10.2 采購數(shù)據(jù)處理 ..................................... 269
10.2.1 采購成本分析 ...................................... 269
1 成本分析表的創(chuàng)建 ................................... 269
2 成本分析表的美化 ................................... 271
10.2.2 節(jié)約采購成本 ...................................... 272
1 價值分析法與價值工程法 ........................ 272
2 談判 ......................................................... 272
3 杠桿采購 .................................................. 272
4 聯(lián)合采購 .................................................. 272
5 價格與成本分析 ....................................... 272
10.2.3 制作采購申請單 .................................. 273
1 設置采購申請單的格式 ............................ 273
2 設置表格內數(shù)據(jù) ....................................... 274
10.2.4 創(chuàng)建采購統(tǒng)計表 .................................. 276
1 統(tǒng)計表的數(shù)據(jù)驗證 ................................... 276
2 統(tǒng)計表的排序和篩選 ............................... 279
3 統(tǒng)計表的分析 ........................................... 281
10.3 銷售數(shù)據(jù)管理 ..................................... 282
10.3.1 銷售收入 ............................................. 282
10.3.2 收入確認的方法 .................................. 282
1 銷售點法 .................................................. 282
2 現(xiàn)金法 ...................................................... 283
10.3.3 制作銷售統(tǒng)計表 .................................. 284
1 設置統(tǒng)計表格式 ....................................... 284
2 使用分類匯總分析銷售數(shù)據(jù) .................... 286
3 使用函數(shù)計算銷售數(shù)據(jù) ............................ 287
4 使用數(shù)據(jù)透視表分析銷售數(shù)據(jù) ................. 289
10.3.4 定價和成本的計算 .............................. 291
1 變動成本法 .............................................. 291
2 完全成本法 .............................................. 292
3 目標利潤定價法 ....................................... 293
10.4 庫存數(shù)據(jù)管理 ..................................... 293
10.4.1 制作入庫單 . ......................................... 293
10.4.2 庫存統(tǒng)計表 . ......................................... 295
10.4.3.庫存的控制 . ......................................... 297
1 使用條件格式標記庫存 ............................ 297
2 使用函數(shù)提醒是否進貨 ............................ 298
Chapter 11   投資經濟數(shù)據(jù)處理
Chapter 12   薪酬體系數(shù)據(jù)處理