Excel数据处理

  Excel是目前现代办公和数据处理中应用非常频繁的一种电子表格软件，该软件以其功能强大、操作方便、易于上手等特点，得到了公司办公人员和企业管理人员的青睐。

为了适应以上形势，让高等院校学生能够系统化地学习Excel数据处理的知识，以便使他们能够适应未来工作中计算机信息处理的需要；同时为了提高各类办公和管理人员应用Excel的操作技能，我们总结了近几年来Excel教学和实践的经验，组织了本教材的编写。

  本教材内容共11章，内容组织和体系安排围绕数据处理的整体流程展开。其中第1～2章介绍数据输入和格式设置的相关知识，这是数据处理的基础工作。输入部分特别强调为提高输入速度而需要掌握的各种输入技巧，并大篇幅地介绍数据有效性设置以及列表选择输入法的操作，数据格式设置部分重点讲解条件格式和自定义数据格式的设置；第3～4章介绍公式、引用、名称以及函数的知识，其中特别强调数组公式的使用、三维引用的方法以及名称的定义和应用，并分类型采用实例方法介绍主要函数的应用；第5章介绍数据图表的制作和编辑，其中在概括介绍数据图表制作的一般制作和编辑之后，重点强调复杂图表和动态数据图表的制作；第6～9章介绍各种数据分析功能，包括对数据进行的排序、筛选、汇总、合并、透视、查找、核对以及数据假设分析等内容；第10章介绍数据表格的屏幕显示和打印输出操作，其中屏幕显示介绍调整屏幕显示比例、拆分窗口、冻结窗格等操作，打印输出部分强调页面设置以及一些数据表打印的技巧；第11章对数据的安全与保密进行阐述，包括工作簿、工作表和单元格三个级别的安全与保密设置，以及宏的安全设置。

在整个教材的编写中，编者对Excel数据处理的内容进行了重新组织，对相关内容的介绍力求做到详略得当，并特别强调操作中的各种应用技巧和注意事项，运用了很多在实际办公中应用的数据处理实际例子，系统地介绍Excel数据处理的相关知识。全书的编写充分体现“结构体系合理，讲授用例实用，编写知识精选，内容详略得当”的特点。

为保证读者实践操作的方便性，本书在内容讲解中精心选择近100个各种不同的操作实例，每章课后还提供与本章内容相配套的习题，这些操作实例和习题作为原始素材，最终“以章为文件夹，以节为工作簿，以实际案例为工作表”进行归类整理，并放置到中国水利水电出版社的网站上（网址为：http://www.waterpub.com.cn/softdown/），读者可以从网站上直接下载。

  本书由周贺来总体策划，并确定整体编写大纲，刘学华作为本书副主编，同周贺来一起参与本书的统稿和定稿工作，书中各章参编情况如下：李会玲编写第1章，贾琳琳编写第2章，田源编写第3章，周溢辉编写第4章，张建编写第5章，蔡中民、牧笛编写第6章，王俊珺、赵超编写第7章，杨娜、范慧敏编写第8章，王静编写第9章，刘学华编写第10章，刘卫伟编写第11章，周贺来编写第12章，杨利红对全书图形表格进行了处理。

  本教材是为全国各大专院校信息管理、电子商务、电子政务、计算机应用、财务管理、会计电算化、工商管理、办公自动化等相关专业的学生编写的一本系统学习Excel知识的授课教材或辅导用书；也可作为政府公务员、各类事业单位办公人员、企业管理人员学习Excel操作技能的培训用书，同时，对于那些已经有了一定的Excel操作基础知识，急需提高操作效率或者扩大Excel应用的深度和广度的各类人员，本书也是一本很好的参考书。

在本书的编写过程中，参考了许多前人的资料，在此对他们表示衷心的感谢。

  本书主要是从数据处理和分析方面介绍Excel的主要功能，并不追求Excel所有知识的面面俱到，所以读者可能会发现Excel的某些知识本书并没有涉及，有些知识本书所论也并不完美。另外，鉴于编者水平有限，书中难免有疏漏和不足之处，恳请读者提出宝贵意见。读者在使用本书时，如果有什么问题、意见和建议，欢迎发送电子邮件联系，我们一定在第一时间给以信息反馈，以便促进我们进一步的交流、学习和提高。

  本书读者信息交流电子邮件地址为：zhouhelai@126.com。

序
前言
第1章  数据的输入与编辑 1
1.1  数据输入的一般操作 1
1.1.1  数据输入前的准备 1
1.1.2  单元格的选取方法 2
1.1.3  数据输入的基本方法 4
1.1.4  各类型数据输入的方法 4
1.2  特殊数据的快捷输入 6
1.2.1  选定连续区域进行数据输入 6
1.2.2  相邻单元格相同数据的输入 7
1.2.3  不相邻单元格相同数据的输入 7
1.2.4  多个工作表中相同数据的录入 8
1.2.5  带零和小数位较多数据的输入 9
1.2.6  利用自动更正工具快速输入数据 10
1.2.7  其他输入技巧介绍 11
1.3  有规律数据的序列输入法 12
1.3.1  拖动填充柄快速输入序列或者特定格式数据 12
1.3.2  根据输入前两个数据的规律快速拖动输入数据 13
1.3.3  自定义序列的应用 13
1.3.4  快速输入复杂的序列号 14
1.4  设置有效性对输入数据审核 15
1.5  下拉式列表选择输入的设计 20
1.5.1  利用数据有效性创建下拉式选择列表 20
1.5.2  利用右键菜单命令从下拉式列表中选择输入 22
1.5.3  利用窗体组合框创建下拉式列表 22
1.5.4  利用控件工具箱的组合框创建下拉式列表 24
1.5.5  级联式下拉式列表的设计方法 26
1.6  数据的编辑操作 28
1.6.1  单元格数据的一般编辑 28
1.6.2  选择性粘贴的典型运用 28
1.6.3  清除单元格数据 30
小结 31
习题一 31
第2章  单元格数据的格式设置 32
2.1  单元格格式的一般设置 32
2.1.1  使用“格式”工具栏 32
2.1.2  使用“单元格格式”对话框 32
2.1.3  将“单元格”按钮放到工具栏上 34
2.1.4  利用快捷键对单元格格式进行设置 34
2.1.5  自动套用格式 35
2.1.6  单元格格式的替换 36
2.1.7  单元格格式的复制 37
2.2  各种内置数字格式的使用 37
2.3  自定义数字格式的应用 39
2.3.1  自定义数字格式的创建与删除 39
2.3.2  自定义数字的格式代码设置 40
2.3.3  自定义数字格式应用实例之一：不显示零值 42
2.3.4  自定义数字格式的应用实例之二：缩放数值 42
2.3.5  自定义数字格式的应用实例之三：智能显示百分比 42
2.3.6  自定义数字格式的应用实例之四：分数的不同显示方式 43
2.3.7  自定义数字格式的应用实例之五：隐藏某些类型的内容 43
2.3.8  自定义数字格式的应用实例之六：日期和时间型数据的自定义格式 43
2.4  条件格式化的应用 44
2.4.1  设置基于数值的条件格式化 44
2.4.2  设置基于公式的条件格式化 46
2.4.3  条件格式化的删除 51
2.4.4  条件格式化操作的注意事项 51
小结 52
习题二 52
第3章  公式、引用与名称 55
3.1  公式及其应用 55
3.1.1  公式的规则 55
3.1.2  公式的编辑 56
3.1.3  公式的运算符号与优先顺序 57
3.1.4  公式结果的查看 59
3.1.5  公式返回的错误信息返回值 60
3.1.6  公式的循环引用问题 61
3.2  单元格的引用 63
3.2.1  对单元格地址的引用方式 63
3.2.2  跨表格数据的引用 64
3.2.3  单元格引用的综合应用举例 65
3.3  名称及其应用 70
3.3.1  使用名称的好处 70
3.3.2  名称的命名规则 71
3.3.3  定义单元格区域的名称 71
3.3.4  定义常量名称 74
3.3.5  名称的查看、修改和删除 75
3.3.6  名称的使用 77
3.3.7  名称应用的综合举例 80
3.4  数组公式及其应用 85
3.4.1  数组公式的建立 85
3.4.2  数组公式的编辑 86
3.4.3  二维数组的应用 87
3.4.4  数组公式的扩展 88
3.4.5  数组公式的应用举例 89
小结 91
习题三 91
第4章  函数及其应用 94
4.1  函数的基本知识 94
4.1.1  函数的功能与类型划分 94
4.1.2  函数的结构 96
4.1.3  函数的调用 97
4.1.4  函数的输入 98
4.1.5  使用Excel帮助理解函数 100
4.2  常用函数 101
4.2.1  自动求和按钮下对应的常用函数 101
4.2.2  其他常用计数函数 103
4.2.3  常用函数应用实例：学生成绩处理 103
4.3  数学函数 105
4.3.1  常用数学函数的功能介绍 105
4.3.2  数学函数应用实例一：利用随机函数快速获取虚拟数据 106
4.3.3  数学函数应用实例二：按实发工资总额计算每种面额钞票的张数 108
4.4  逻辑判断函数 109
4.4.1  逻辑函数的功能与用法介绍 110
4.4.2  逻辑函数应用实例一：销售提成计算 111
4.4.3  逻辑函数应用实例二：考试成绩评判 112
4.4.4  逻辑函数应用实例三：制作跳水比赛计分表模板 113
4.5  日期和时间函数 115
4.5.1  常用日期和时间函数介绍 115
4.5.2  日期和时间函数应用实例一：生日提醒 117
4.5.3  日期和时间函数应用实例二：会议日程安排 118
4.5.4  日期和时间函数应用实例三：加班工资计算 119
4.5.5  日期和时间函数应用实例四：制作万年历 120
4.6  文本函数 123
4.6.1  常用文本函数介绍 124
4.6.2  文本函数应用实例一：通讯地址拆分 126
4.6.3  文本函数应用实例二：电话号码升位 127
4.6.4  文本函数应用实例三：提取身份证信息 127
小结 130
习题四 130
第5章  数据图表处理 133
5.1  数据图表的基本知识 133
5.1.1  数据图表的类型 133
5.1.2  数据图表的制作工具 136
5.1.3  数据图表的各个组成部分 136
5.2  数据图表的创建与编辑 137
5.2.1  嵌入式图表和图表工作表 137
5.2.2  利用图表向导创建嵌入式图表 138
5.2.3  数据图表的格式设置与内容更新 140
5.2.4  利用快捷键建立单独的三维柱状图表 140
5.2.5  选取不连续区域制作饼图 142
5.2.6  图表中趋势线的添加 143
5.3  复杂数据图表的制作 145
5.3.1  组合图表的制作 146
5.3.2  双轴图表的制作 147
5.3.3  复合饼图的制作 148
5.3.4  多工作表合并图表 151
5.4  动态图表的制作 152
5.4.1  通过创建动态数据区域制作动态图表 153
5.4.2  利用CHOOSE函数和组合框建立动态图表 154
5.4.3  利用VLOOKUP和复选框控件制作动态图表 155
5.4.4  制作用来进行数据对比分析的双轴动态图表 157
小结 159
习题五 159
第6章  数据的排序与筛选 163
6.1  Excel数据库表格及其功能说明 163
6.1.1  Excel数据库表格样式及其特点 163
6.1.2  Excel数据库表格应该遵循的准则 164
6.1.3  Excel的数据库功能及其局限性 164
6.2  数据的排序 165
6.2.1  排序的规则 165
6.2.2  采用工具按钮法对单个数字关键字段排序 165
6.2.3  利用菜单法对汉字进行按笔画排序 166
6.2.4  自定义次序排序 169
6.2.5  多关键字排序 171
6.3  数据排序操作的应用技巧 172
6.3.1  实现排序后仍能快速返回原来的顺序 173
6.3.2  排序函数应用：RANK、LARGE和SMALL 173
6.3.3  隐藏部分数据使其不参与排序 175
6.3.4  使用数据排序法删除数据区域内的空行 175
6.4  数据的筛选 177
6.4.1  自动筛选的操作 177
6.4.2  高级筛选的操作 179
6.4.3  将公式结果用于高级筛选的条件区域 182
6.4.4  高级筛选时的注意事项 183
小结 184
习题六 184
第7章  数据的汇总、合并与透视 186
7.1  利用相关函数进行数据汇总 186
7.1.1  利用SUMIF函数实现单条件汇总 186
7.1.2  SUM函数和IF函数联合实现多条件汇总 187
7.1.3  利用SUMPRODUCT函数实现多条件汇总 188
7.1.4  利用DSUM函数进行数据库表格多条件汇总 189
7.2  数据库表格的分类汇总 190
7.2.1  分类汇总的基本知识 191
7.2.2  分类汇总的建立 191
7.2.3  多重分类汇总的操作 194
7.2.4  嵌套分类汇总的操作 195
7.3  特定情形下的数据汇总方法 195
7.3.1  数据的累加汇总处理 196
7.3.2  动态更新区域的数据汇总 196
7.3.3  不连续区域的数据汇总 197
7.3.4  数据区域中前若干个最大（小）数值的汇总 198
7.3.5  对含有错误值的单元格区域进行汇总 198
7.4  数据的多表合并 199
7.4.1  利用SUM函数实现多表数据合并 200
7.4.2  按位置进行合并计算 202
7.4.3  按分类进行合并计算 204
7.5  数据的透视分析 205
7.5.1  数据透视表的创建 205
7.5.2  创建数据透视表时的相关操作 209
7.5.3  创建数据透视图 210
7.5.4  编辑数据透视表 211
7.5.5  修改数据透视表的布局 212
7.5.6  新数据透视表中数据显示形式的修改 212
7.5.7  数据透视表的数据更新 214
7.5.8  删除数据透视表 214
7.5.9  制作数据透视报告 215
小结 216
习题七 217
第8章  数据的查询与核对 219
8.1  利用查找命令查询数据 219
8.1.1  用“查找”命令进行数据查找的操作步骤 220
8.1.2  在“查找”命令中利用通配符进行数据查找 221
8.2  利用查询与引用函数进行查询 222
8.2.1  使用CHOOSE函数从值的列表中选择一个值 223
8.2.2  用VLOOKUP和HLOOKUP函数进行表格查询 225
8.2.3  用MATCH和INDEX函数构造灵活的查询 229
8.2.4  利用INDIRECT函数和名称查询其他工作表中的数据 234
8.2.5  用OFFSET函数进行动态数据区域查询 235
8.3  数据库表格中的数据查询 238
8.3.1  利用记录单进行数据查询 238
8.3.2  利用数据库函数查询数据 239
8.4  利用名称查询数据 241
8.5  利用数组公式进行查询 242
8.6  数据表之间的数据核对 243
8.6.1  利用查询函数进行数据核对 243
8.6.2  利用条件格式化进行数据核对 245
8.6.3  利用合并计算法进行数据核对 246
小结 247
习题八 247
第9章  数据分析及其工具应用 250
9.1  公式审核：对公式和数据跟踪分析 250
9.1.1  “公式审核”工具栏 250
9.1.2  追踪引用单元格 251
9.1.3  追踪从属单元格 252
9.1.4  添加/编辑/删除批注 253
9.1.5  圈释无效数据 254
9.2  模拟运算表：实现数据敏感度分析 254
9.2.1  单变量模拟运算表 255
9.2.2  双变量模拟运算表 257
9.3  单变量求解：实现规划目标搜索 259
9.4  方案管理器：实现多因素决策分析 260
9.4.1  方案概述 260
9.4.2  创建方案 261
9.4.3  显示方案结果 263
9.4.4  建立方案摘要 263
9.5  规划求解：多约束条件下的优化分析 264
9.5.1  规划求解概述 264
9.5.2  输入规划模型 265
9.5.3  进行规划求解 267
9.5.4  分析求解结果 268
9.5.5  编辑规划求解参数 269
9.5.6  修改规划求解选项 269
9.6  分析工具库安装与使用简介 270
9.6.1  安装分析工具库 270
9.6.2  分析工具库的应用 270
小结 271
习题九 271
第10章  数据工作表的显示与打印 272
10.1  数据工作表的显示操作 272
10.1.1  通过调整显示比例来缩放显示范围 272
10.1.2  拆分窗口以便能够同时显示多个数据区域 274
10.1.3  冻结窗格以便使行（列）标题始终可见 274
10.1.4  通过“选项”进行视图方式的个性化设置 276
10.2  数据工作表打印的整体流程 276
10.2.1  用分页预览视图查看工作表 276
10.2.2  对打印的预期效果进行页面设置 277
10.2.3  通过打印预览查看打印的未来效果 280
10.2.4  根据需要在适当位置插入/删除人工分页符 281
10.2.5  根据实际打印需要进行打印操作选择 281
10.3  几种特殊表格的打印方法 282
10.3.1  顶端标题行的每页打印 282
10.3.2  左端标题列的每页打印 284
10.3.3  页面底部注释信息的每页打印 285
10.3.4  工资条的一种简便打印方法 288
10.3.5  Excel中套打表格的实现方法 289
小结 291
习题十 292
第11章  数据的安全与保密设置 293
11.1  工作簿级的安全与保密 293
11.1.1  为工作簿设置权限密码 293
11.1.2  对工作簿进行隐藏处理 294
11.1.3  保护工作簿的结构和窗口 296
11.2  工作表级的安全与保密 297
11.2.1  工作表的整体保护 297
11.2.2  隐藏工作表的一般方法 298
11.2.3  彻底隐藏工作表的操作 299
11.3  单元格级的安全与保密 300
11.3.1  保护工作表中特定的单元格区域 300
11.3.2  不同单元格区域设定不同的密码保护 301
11.3.3  隐藏/显示工作表中的行或者列 303
11.4  宏病毒及其安全保护 307
小结 308
习题十一 308
参考文献 310
 

1. Python办公自动化—玩转Excel [郝春吉 刘智杨 周永福 黄 诠]
2. 数据清洗 [黄源　刘智杨　孙大松]
3. Excel在会计工作中的应用（第三版） [主 编 赵艳莉 耿聪慧]
4. Excel高级应用实验指导 [主编 卢山]
5. Excel在统计中的应用（第三版） [主 编 王维鸿]
6. Excel在财务中的应用（第二版） [主编 李建军]
7. Excel高级应用实务 [主编 卢山]
8. Hadoop大数据开发 [主 编 刘春阳 张学龙 刘丽军]
9. Excel 数据处理与分析 [张志明　李红娟　段红玉]
10. 效率工作术——Excel 图表一本通 [文渊阁工作室　编著]
11. 效率工作术——Excel 函数一本通 [文渊阁工作室　编著]
12. Excel 2010数据统计分析实用案例教程 [苏志军 楼骏 汤巧英]
13. Hadoop & Spark大数据开发实战 [主编　肖睿　雷刚跃]
14. Excel 2016在会计工作中的应用 [主编 赵艳莉 耿聪慧]
15. Windows+Word+Excel+PowerPoint效率手册一本通 [文渊阁工作室　编著]
16. Excel 2010高级应用实例教程 [主编 金晓龙 石利平 梁竞敏]
17. Excel 2010在会计工作中的应用 [赵艳莉 耿聪慧 编著]
18. Excel在会计工作中的应用（第二版） [赵艳莉 耿聪慧]
19. Excel在财务中的应用 [李建军 贺琴]
20. Excel会计信息化管理与应用 [梁竞敏]
21. Excel在统计中的应用（第二版） [主编 王维鸿]
22. Excel高级应用实例教程 [金晓龙]
23. Excel在财会管理中的应用（第二版） [主 编 于向辉]
24. Excel在统计中的应用 [王维鸿 主 编]
25. Excel在会计工作中的应用 [赵艳莉 耿聪慧 主编]
26. Excel在市场营销与销售管理中的应用 [周贺来 编著]
27. EXCEL在财务管理与分析中的应用 [韩良智 等著]
28. Excel在财会管理中的应用 [于向辉 张国平 主编]
29. 活学活用Excel及常见问题速查手册 [庄东填 等编著]
30. Excel在财务管理与分析中的应用（第二版） [韩良智 编 著]