Part 01 Excel操作环境的设置及工作簿的操作 24
Excel基本操作 24
001 Excel 2010的操作界面及组成元素 24
002 通过“帮助”了解Excel 2010的新增功能 25
003 了解Excel 2010的保存类型 26
004 了解新增的【文件】选项卡 27
005 检查与早期版本的兼容性 28
006 Excel 2010中查看Excel 2003的命令 29
007 向功能区添加组和命令 31
008 导入自定义功能区 34
009 隐藏以及重新显示功能区 36
010 向快速访问工具栏添加常用命令，以便快速执行相应命令 37
011 使用快捷键快速执行命令 38
012 初始化功能区 39
013 设置新建工作簿的默认值 40
环境设置 41
014 更改工作表屏幕颜色和用户界面 41
015 自定义列表中添加常用职位序列 42
016 将文档保存为早期版本格式的工作簿 44
017 通过自动保存功能创建自动恢复文件 45
工作簿管理 47
018 设定工作簿密码 47
019 锁定工作簿窗口或结构 48
020 创建使用MS模板的工作簿 49
021 将已设置安全性文件指定为受信任文档 50
022 将工作簿标记为最终状态 51
023 工作簿中输入信息 52
024 删除工作簿属性及个人信息 53
025 固定最近使用的文档和文件夹 55
026 将工作簿另存为PDF文件 56
工作表的基本操作 57
027 更改工作表名称和颜色 57
028 快速添加工作表 58
029 快速选择不在屏幕中显示的工作表 59
030 将特定工作表拖动到您所希望的位置 60
031 将特定工作表复制到其他工作簿 61
032 隐藏或取消隐藏工作表 62
033 以电子邮件形式发送特定工作表 64
034 为工作表添加背景图 66
035 保护工作表并使用被许可的编辑功能 67
036 消除文档中显示的虚线 69
Part 02 数据操作 72
输入数据 72
037 数据的分类 72
038 临时禁用自动更正功能 74
039 输入各类型数值数据 76
040 显示多种形式的数据 77
041 一个单元格中输入两行数据 78
042 纵向输入数据 79
043 将简体中文转换成繁体中文 80
044 工作表中插入符号 81
045 快速插入常用符号 82
046 以对象形式插入公式 83
047 输入日期和时间数据 85
048 使用快捷键输入日期和时间数据 87
049 输入经过翻译的数据 88
050 利用小数点位数插入数字 89
自动填充数据 90
051 使用填充柄自动填充数据 90
052 填充工作日数据 92
053 填充连续性数据 93
054 填充自定义序列数据 94
055 填充按特定间隔递增的数据 95
056 使用公式填充排列序号并进行排序 96
数据有效性 98
057 应用数据有效性 98
058 输入指定范围内的金额（一） 100
059 输入指定范围内的金额（二） 102
060 基于文本长度应用数据有效性 104
061 通过输入法模式自动切换到英文输入法 105
062 将数据输入限制为下拉列表中的值 106
063 基于已定义名称的单元格区域输入数据 107
064 禁止重复输入数据 109
065 设置需要满足多项条件的数据有效性 110
066 只允许输入不包含特定文字的数据 112
067 通过双重有效性条件创建分类 113
068 标示无效数据 116
069 按列的顺序输入数据 117
070 更改单个单元格的有效性条件，并把更改内容应用到具有相同设置的其他单元格 118
071 查找数据有效性的应用范围 119
072 清除数据有效性 120
转换数据 122
073 将文本格式下输入的数字数据转换成数值数据 122
074 将数据转换成日期格式数据 124
075 删除空白字符 125
076 通过替换功能删除连字符（-） 126
077 将分成两排输入的数据合并成一排 128
078 将公式结果复制为数据值 129
079 删除空单元格中包含的记录 130
080 一次性向所有空单元格输入0值 132
Part 03 编辑文档 134
页面视图 134
081 单元格中用公式代替计算结果 134
082 冻结工作表窗格 135
083 使用自定义视图功能保存和应用显示设置 136
084 页面布局视图和分页预览视图 138
085 状态栏上显示其他函数 140
086 比较两个工作簿 141
087 全屏显示选定区域 142
选择单元格 144
088 使用鼠标和键盘选取单元格区域 144
089 使用键盘一次性选择某个单元格区域 146
090 通过名称框选择特定单元格或区域 147
091 使用快捷键选择当前区域和所有单元格 148
092 通过定位条件功能查找包含批注的单元格 149
093 通过定位条件功能查找包含公式的单元格 150
094 仅复制可见单元格 151
编辑数据和单元格 153
095 插入行和列 153
096 删除行和列 155
097 清除内容和格式，但保留单元格 156
098 移动数据库中的分类（字段） 158
099 将多个不相邻的单元格复制到同一个区域 159
100 详解“选择性粘贴”命令 160
101 复制包含链接和列宽的源数据 161
102 将复制的源数据粘贴为链接的图片 163
103 粘贴时转置行和列 165
104 查找带格式的单元格 167
105 一次性查找并更改格式相同的单元格 169
页面和打印设置 171
106 设置打印环境 171
107 打印居中对齐的文档 172
108 只打印特定区域 173
109 打印时将工作表缩减到一个页面中 175
110 打印网格线和标题 176
111 每页都打印行标题 177
112 在希望位置对工作表分页 178
113 打印指定页面和工作表 179
114 不打印单元格中显示的错误提示 180
115 只打印图表 181
116 打印文档时排除图表 182
117 打印工作表背景 183
118 利用标签打印向导打印标签 185
设置页眉和页脚 188
119 页面布局中设置页眉和页脚 188
120 页眉中插入徽标并调整尺寸 190
121 为偶数页和奇数页设置不同的页眉和页脚 192
122 插入页码和总页数 194
123 自定义起始页码 196
124 插入当前日期后更改显示格式 197
Part 04 设置格式 200
设置单元格格式（不包括数字格式） 200
125 更改Excel的默认字体和字号 200
126 通过单元格样式和主题美化标题 201
127 使用键盘加速键设置字体格式 202
128 通过键盘设置单元格格式 203
129 重复使用上一次使用过的格式 204
130 保存经常使用的单元格样式 205
131 引用其他工作簿的单元格样式 206
132 使用拼音字段功能，为数据添加注释 207
133 缩进文句并分散对齐文字 208
134 根据单元格宽度排列数据 209
135 合并单元格及垂直对齐文本 211
136 在单元格区域中居中对齐数据 212
137 设置和删除单元格边框 213
138 使用渐变效果为单元格填充颜色 215
按分类设置数字格式 216
139 设置千位分隔样式和货币符号格式 216
140 数字数据中应用小数位数 218
141 为数字数据设置百分比样式 219
142 设置日期和时间数据的格式 220
143 不使用公式计算日期和时间数据 221
144 使用中文大写显示数字 223
自定义数字格式 224
145 了解自定义数字格式 224
146 为数字数据添加文本格式的单位 226
147 按千、百万单位省略数字数据的位数 227
148 以001,002,003形式显示序号 229
149 使用“？”代码对齐序号 230
150 根据分数显示“最佳”、“优秀”和“及格” 231
151 隐藏不想显示的数据 232
152 了解日期和时间格式中使用的符号和类型 233
153 显示包含汉字和星期的日期 235
154 将日期数据显示为星期 236
155 显示总经过时间 237
156 使用“_”和“＊”符号添加空格和重复字符 239
157 根据位数设置电话号码格式 241
158 使用＊代码填充目录线 243
条件格式 244
159 查找并标记聘用日期相同的数据 244
160 查找并标记营业实绩优于平均的月份 245
161 通过项目选取规则指定条件格式 247
162 使用数据条表示数据大小 248
163 为负数值指定不同的颜色 249
164 省略数据值，只显示数据条 251
165 通过图标集注释营业利润 253
166 根据利润率更改图标的阈值 254
167 隐藏图标集中的部分图标 255
168 使用色阶格式显示销量变化 256
169 显示上年对比销量提升的商品 257
170 突出显示销售率排在前20％的商品 259
171 只对每日变更的特价商品指定格式 261
172 每十行设置颜色区分数据 263
173 显示满足多项条件的记录 264
174 仅对星期一售出的数据设置格式 265
175 输入数据的时候显示边框 266
176 重复数据的第二个数据开始设置格式 268
177 在数据透视表中使用条件格式比较数据 269
178 快速删除条件格式 270
Part 05 创建公式 272
公式计算选项 272
179 查看公式求值步骤 272
180 在单元格中检查和编辑公式 273
181 同时更改和计算多项数据 274
182 隐藏重要的公式 275
公式的基本操作 277
183 创建公式的方法和运算符简介 277
184 在公式中通过多种途径引用值 278
185 更改链接文档的位置 281
186 快速打开引用文档 283
187 不再显示更新链接消息栏 284
188 公式中查看引用单元格和从属单元格 285
189 检查公式中的错误 286
190 错误检查和追踪错误 288
191 更正公式中的循环引用 290
192 计算以文本格式输入的公式 291
使用名称 293
193 定义和确认名称 293
194 使用数据区域的标签定义名称 294
195 修改和删除定义的名称 296
196 选用定义的名称 298
197 使用相对引用定义名称 300
198 使用名称简化重复的公式 301
199 筛选并删除有错误的名称 302
创建表格 303
200 Excel 2010程序的表格简介和计算构造 303
201 创建表格并设置表格样式 305
202 自定义更改表格样式 306
203 自动向表格添加数据 308
206 表格中设置各个项目的汇总 309
205 仅汇总特定区域的数据 310
206 更改表名称和设计 311
207 在表格中以结构化引用形式运算公式 312
208 在表格中使用常规引用形式运算公式 314
209 在表格中自动填充公式结果 315
210 将表格转换成普通数据区域 316
Part 06 应用函数 318
自动求和 318
211 了解函数使用基础 318
212 通过求和命令快速计算数据 320
213 指定计算区域后一次性求和 322
214 计算最大值和最小值 323
215 COUNT,COUNTA,COUNTBLANK函数简介 325
统计／汇总函数 326
216 ～IF函数和～IFS函数简介 326
217 计算平均完成率-AVERAGEIFS函数 327
218 对销量超过平均的商品计算销售额-SUMIFS函数 329
219 按分类计算小计-SUBTOTAL函数 331
220 仅对筛选的数据计算小计-SUBTOTAL函数 334
221 使用混合引用复制函数公式 336
文本／数据编辑函数 338
222 提取电子邮件中的ID-FIND,LEFT函数 338
223 使用分列功能提取ID 339
224 隐藏身份证号码的后几位数①-REPLACE函数 341
225 将日期更改为星期-TEXT函数 342
226 从订单编号中提取商品名称-LEN,RIGHT,SEARCH函数 343
227 将单元格数据连接成一个句子-CONCATENATE,TEXT函数 344
228 根据身份证号查出性别-CHOOSE,MID函数 345
逻辑／判断函数 346
229 按销售额和地区计算奖金-IF,OR函数 346
230 返回对平均值的评价-IF,VLOOKUP函数 347
231 通过多个条件和引用表进行评价-IF,VLOOKUP函数 349
232 IF函数和IS～函数简介 350
233 自动为数据添加序列号-IF,ROW函数 352
234 根据进出库明细计算税前金额-IF,ISBLANK函数 353
235 填充序号时忽略空单元格-COUNTA,IF函数 354
236 按5000元单位计算售价-IF,RIGHT,ROUND系列函数 355
数学／计算函数 357
237 截去指定位数以下的值-TRUNC函数 357
238 计算累计销售额-SUM函数 358
239 ROUND系列函数简介 359
240 七舍八入计算售价-ROUND函数 360
241 基于库存计算采购量CEILING.PRECISE函数 361
日期／时间函数 363
242 返回当前日期和时间-NOW,TODAY函数 363
243 基于身份证号码返回出生年月日-DATE,MID函数 364
244 基于出生年月日返回周岁-DATEDIF,TODAY函数 365
245 计算在职时间-CONCATENATE,DATEDIF,TODAY函数 366
246 计算项目开始日期的星期数-CHOOSE,WEEKDAY函数 367
247 计算项目结束日期-WORKDAY函数 368
248 计算某个日期为所属月份的第几周-MONTH,WEEKNUM函数 369
249 计算特定作业期间内休息日数-DATEDIF,NETWORKDAYS函数 370
250 检查项目进度-IF,NETWORKDAYS,TEXT函数 371
251 计算星期一、星期二休息时的工作天数-NETWORKDAYS,INTL函数 372
252 计算N年、N月、N日以后的日期-DATE,DAY,MONTH,YEAR函数 373
253 计算贷款的首期利息交付日和贷款到期E-EDATE,EOMONTH函数 374
254 判断闰年与否-DAY,DATE,IF函数 375
255 根据工作时间计算工作津贴-DAY,HOUR,IF,TIME函数 376
统计／趋势分析函数 379
256 比较兼容性函数类别和新增函数 379
257 计算包括未应试情况在内的平均成绩-AVERAGEA函数 380
258 计算销售排行①-RANK.EQ函数 381
259 计算销售排行②-RANK.AVG函数 382
260 按商品分类计算排行-RANK.EQ函数 383
261 按成绩返回百分位排名和等级-PERCENTRANK.INC,VLOOKUP函数 384
262 返回众数-MODE.SNGL函数 386
263 返回指定排名值-LARGE,SMALL函数 387
264 基于几何平均值返回平均增长率-GEOMEAN函数 388
265 按分数段返回分数的出现频率-FREQUENCY函数 390
266 返回线性趋势的预测值-FORECAST函数 392
267 根据线性回归预测法分析销售趋势-TREND函数 393
268 根据指数回归拟合曲线分析销售趋势-GROWTH函数 395
269 计算R2决定系数和最适合的预测值-GROWTH,RSQ函数 396
查找与引用函数 398
270 查找行和列的位置-COLUMN,ROW函数 398
271 根据编码返回产地-CHOOSE函数 400
272 使用部分字符锁定商品所在位置-MATCH函数 401
273 引用商品列表中的单价计算销售额-VLOOKUP函数 402
274 使用姓名对数据分组-HLOOKUP函数 403
275 根据职位和工资等级计算基本工资-INDEX,MATCH函数 404
276 在明细表中按职位和类型计算津贴额-COLUMN,INDEXMATCH函数 405
277 根据工号编制培训证明-LOOKUP函数 406
278 通过定义名称设置双重有效性-INDIRECT函数 409
279 通过单价表返回每种商品的单价-INDEX,INDIRECT,MATCH,OFFSET函数 413
280 创建动态引用区域-COUNTA,OFFSET函数 415
281 只显示最近五个项目-COUNTA,OFFSET函数 417
282 根据多个工作表中的分散数据创建商品目录-INDIRECT,ADDRESS函数 418
财务函数 419
283 计算投资达到目标金额的期限-NPER函数 419
284 计算某项投资5年后的到期总金额-FV函数 421
285 计算长期储蓄的月存款额-PMT函数 422
286 等额偿还贷款本息-IPMT,PPMT函数 423
287 基于定额法计算固定资产的折旧值-SLN函数 425
288 基于定率法计算固定资产的折旧值-DB函数 426
数据库／信息函数 427
289 数据库函数的参数类型 427
290 找出销售额最高的营业员-DGET,MAX函数 428
291 基于地区代理店和数量返回总销售额-DSUM函数 429
292 搜索系统信息和单元格信息-CELL,INFO函数 430
293 将文本值转化成0值-N函数 432
294 根据身份证号码的偶数、奇数判断性别-IF,ISEVEN,MID函数 433
函数技巧 435
295 轻松应用复杂的嵌套函数的技巧 435
296 为函数公式添加注释-IF,ISEVEN,MID,N函数 438
297 忽略错误并计算函数公式-AGGREGATE函数 439
298 将包含错误的工作簿修饰得更整洁美观-IFERROR函数 441
299 引用多个条件计算销售数量-INDEX函数 443
300 创建简单重复的函数公式 444
301 在Excel程序中发送电子邮件-HYPERLINK函数 446
数组公式 448
302 数组公式中设置常量 448
303 对满足多个条件的值求和-RIGHT,SUM函数 450
304 按月份计算满足多个条件的销售额-MONTH,SUM函数 451
305 按季度求和-COLUMN,IF,MOD,SUM函数 452
306 计算销量前十位商品的总销量-INDIRECT,LARGE,ROW,SUM函数 453
307 计算各分组的最高销售额-MAX,N函数 454
308 计算首次交易日-IF,MIN函数 455
Part 07 用图表形式显示数据 458
创建图表 458
309 图表的组成元素和创建方法 458
310 创建并简单修饰图表 460
311 图表布局中显示数值轴和网格线 462
312 设置图表标题和数据系列格式 463
313 取消图表中的图例，显示图表标签 464
314 复制图表的格式 465
编辑图表 467
315 在图表中只显示特定年份 467
316 在图表中编辑分类轴标签 469
317 设置图表的次坐标轴并更改图表类型 471
318 修饰折线图的数据标记 473
319 在图表中更改数值轴的最大值和最小值 475
320 在图表中更改类别标签的顺序（逆序显示） 477
321 数据区域标题和图表标题联动 478
322 在图表中重叠显示数据系列 479
323 基于主要数据和其他数据创建复合饼图 480
324 更改图表中负数值数据系列的颜色 482
325 更改图表中分类轴标签的位置 483
326 锁定图表的大小和位置 484
327 更改默认图表 486
328 保存和使用专属于自己的图表样式 487
329 在图表中设置系列线 489
分析图表 490
330 工作表中添加线性趋势线并指定周期 490
331 图表中设置R平方值后，查看回归分析类型 492
332 针对值间差距过大的数据创建图表 494
333 创建可对比数据的条形图 497
334 为创建图表而定义名称 499
335 图表中设置动态数据区域 501
336 利用圆环图显示小计 503
337 创建气泡图 505
迷你图 508
338 使用迷你图显示数据的趋势 508
339 在迷你图中显示高点和低点 509
340 删除部分单元格中的迷你图 510
341 更改迷你图的类型 511
342 通过盈亏迷你图显示目标达成情况 512
343 显示不规则日期的趋势 514
Part 08 创建数据库 516
排序 516
344 通过功能区升序排列文本 516
345 在表格中降序排列数字数据 517
346 使用多个条件排序 518
347 以行为条件排序 520
348 按颜色对数据进行排序 522
349 按自定义序列对数据进行排序 524
350 数据区域中重新对字段位置进行排序 526
自动筛选 528
351 按所选单元格的值筛选数据 528
352 按关键词筛选数据 529
353 基于自定义自动筛选功能筛选数据 530
354 按单元格颜色筛选数据 531
355 基于两个字段的条件筛选数据 532
356 基于低于平均值的金额筛选数据 534
357 筛选并排序销售额在最大15％以内的数据 535
358 通过函数按分组筛选数据 537
359 以包含合并单元格字段为条件筛选数据 539
360 自动筛选中取消对日期的分组 541
361 仅对筛选结果进行更改操作 543
高级筛选 545
362 设置高级筛选中使用的条件 545
363 根据条件筛选结果后复制到其他工作表 546
364 在两个以上字段中使用OR条件筛选数据 548
365 只筛选出期望字段中的数据 550
366 筛选特定日期（星期数）里订购的商品 552
367 筛选本月过生日的国外区营业员 554
368 筛选文本准确匹配的数据 556
369 仅筛选偶数行的数据 558
370 通过高级筛选功能筛选唯一项 560
371 通过删除重复项，筛选唯一项 561
372 筛选新列表后添加到总列表 562
分类汇总 564
373 按地区分类汇总 564
374 使用嵌套分类汇总统计各季度销售额 566
375 复制汇总值创建统计表 569
376 将汇总更改为小计后设置条件格式 571
377 删除分类汇总 573
数据透视表 574
378 数据透视表中选择要分析的数据 574
379 创建数据透视表 575
380 更改数据透视表样式并设置选项 577
381 更改行标签和列标签的项和顺序 578
382 将汇总值更改为平均值 580
383 按年或按季度分组日期数据 582
384 删除数据透视表中的分类汇总 583
385 使用多重合并功能创建数据透视表 584
386 更改数据透视表的字段名称和项名称 587
387 导出已经过分析的数据列表 589
388 数据透视表中只显示必要的项目 590
389 报表筛选中创建多项报表 592
390 在单独工作表中显示报表筛选的各项目 593
391 更改布局并在项目间插入空行 594
392 字段中显示无数据的项目 595
393 空单元格中重复显示标签 597
394 引用数据透视表中的值返回汇总数据-GETPIVOTDATA 598
395 插入计算字段添加“税额”项 601
396 插入计算项来计算增长率 603
397 报表中插入累计值 605
398 按排名显示销售实绩 606
399 父行汇总百分比形式显示数据 608
400 按姓名对项目分组 609
401 通过数据透视表选项修饰数据透视表报表 610
402 更新数据透视表的源数据 612
403 创建数据透视图 613
404 隐藏数据透视表中的筛选按钮 615
405 选择并删除和移动数据透视表 616
切片器 618
406 插入切片器 618
407 设置切片器样式 620
408 连接其他数据透视表共享切片器 622
409 删除切片器 624
Part 09 数据工具和连接分析外部数据源 626
PowerPivot 626
410 安装PowerPivot加载项 626
411 使用PowerPivot分析Excel数据 628
412 链接Excel数据创建数据透视表和透视图 632
413 通过PowerPivot分析Access数据库 635
414 创建数据表之间的关系以对数据进行分析 639
合并及连接外部数据源 642
415 使用三维引用合并多个工作表的数值 642
416 使用合并计算功能合并数据 644
417 基于多个列合并计算数据 646
418 在Excel中导入文本数据 650
419 在Excel中导入网站数据 652
420 在Excel表格中导入Access DB表 654
421 删除工作簿的数据连接 656
模拟分析 658
422 模拟分析简介 658
423 使用单变量求解查找减少率 660
426 方案管理器中添加方案 662
425 方案管理器中创建摘要报告 664
426 使用两个变量创建奖金查阅表（模拟运算表） 666
427 使用三个变量创建奖金查阅表（模拟运算表） 668
428 功能区中添加规划求解和分析工具 670
429 使用规划求解求得最合理的商品配送条件 671
430 在规划求解中保存方案 675
数据分析 681
431 统计分析简介 681
432 使用描述统计分析工具计算人口统计值 682
433 通过设置接收区域创建直方图 684
434 计算各家经销商的销售分部-排位与百分比排位 688
635 使用移动平均工具预测销售趋势-时间序列分析 691
436 使用指数平滑工具预测销售趋势-时间序列分析 694
437 使用方差分析工具分析商品分类和订单量的关系 696
438 使用回归分析工具分析广告费用和销售额的关系 698
Part 10 使用控件工具和宏 702
控件工具和宏 702
439 在功能区中显示【开发工具】选项卡 702
440 使用列表框创建证明书标题列表 703
441 使用组合框创建综合证明书 705
442 使用复选框创建调查问卷格式 707
443 使用数值调节钮调整数据值 711
444 录制宏 715
445 连接表单控件和宏 718
446 创建宏按钮控件 720
创建宏 722
447 运行宏的各种方法 722
448 使用VBA编辑器编辑宏 723
449 删除宏 725
450 用复制的宏创建个人宏工作簿 727
451 导入宏文件 732
452 在“立即窗口”中执行VBA命令 735
453 处理宏错误 736
实用宏 739
454 通过VBA设置条件格式 739
455 通过宏把工作表拆分成符合条件的多个工作表后复制数据 742
456 创建从字符串提取数字的自定义函数 746
457 在所有工作簿中使用自定义函数 748
458 计算显示为字符串的公式 752
459 使用宏提取新增数据 754
460 使用宏合并相同的分类项 759
461 功能区中添加宏按钮 761
462 使用宏程序管理单元格中输入的批注 764