中文版Excel 2016电子表格制作实例教程（含微课）ISBN：9787830027070作 者：孙勇、刘建中、罗春定 价：48.00出版社：北京希望电子出版社使用层次：通用出版日期：2019-07-10
本书主要内容包括Excel 2016基础，Excel数据的录入与编辑，工作表格式设置与美化，Excel数据处理与分析，公式的使用，函数的使用，创建数据透视表与数据透视图，使用图表实现数据可视化，创建链接与引用，以及电子表格页面设置与打印等。
本书注重理论知识与实际应用相结合，以实例操作为主线，深入浅出地讲解了使用Excel 2016进行电子表格制作的各种知识与技巧本书既可作为应用型本科院校、职业院校的教材，也适合希望尽快掌握Excel 2016电子表格制作技能的电脑初、中级用户阅读。
第1章 Excel 2016基础1.1 工作簿的基本操作 ·······························11.1.1 新建工作簿 ······························1
1.1.2 保存工作簿 ······························21.1.3 打开工作簿 ······························31.1.4 关闭工作簿 ······························4
1.1.5 隐藏工作簿 ······························51.2 工作表的基本操作 ·······························51.2.1 插入工作表 ······························6
1.2.2 移动和复制工作表 ···················71.2.3 删除工作表 ······························91.2.4 重命名工作表 ························· 10
1.2.5 显示和隐藏工作表 ················· 111.2.6 保护工作表 ···························· 121.2.7 拆分工作表 ···························· 13
1.2.8 冻结工作表 ···························· 141.3 单元格的基本操作 ····························· 161.3.1 插入单元格 ···························· 16
1.3.2 删除单元格 ···························· 181.3.3 清除单元格 ···························· 191.3.4 命名单元格 ···························· 20
1.4 Excel 2016工作环境的设置··············· 211.4.1 显示与隐藏网格线 ················· 211.4.2 显示与隐藏标尺 ····················· 22
1.4.3 显示与隐藏功能区 ················· 231.4.4 设置自动保存信息时间间隔 ·· 231.5 综合实例——成绩单的设置 和保护 ··············································· 24
本章小结 ··················································· 25课后习题 ··················································· 25
第2章 Excel数据的录入与编辑2.1 录入与选择数据 ································ 272.1.1 手动输入数据 ························ 27
2.1.2 选择单元格 ···························· 302.2 快速填充数据 ···································· 312.2.1 使用填充柄快速填充数据 ····· 31
2.2.2 使用快捷键填充相同数据 ····· 322.2.3 使用“填充序列”功能········· 342.2.4 使用“快速填充”功能 ··········· 352.3 添加与管理批注 ································ 37
2.3.1 为单元格添加批注 ················ 372.3.2 编辑批注 ······························· 382.3.3 隐藏或显示批注 ···················· 39
2.3.4 更改批注的位置和显示 方式 ······································· 392.3.5 复制批注 ······························· 41
2.3.6 删除批注 ······························· 412.4 设置数据验证 ···································· 422.4.1 限制用户输入 ························ 42
2.4.2 创建下拉菜单 ························ 432.4.3 校验无效数据 ························ 452.5 综合实例——制作员工信息表 ········· 47
本章小结 ···················································· 49课后习题 ···················································· 49
2 中文版 Excel 2016 电子表格制作实例教程第3章 工作表格式设置与美化3.1 设置单元格数字格式 ·························523.1.1 应用预设数字格式 ·················52
3.1.2 自定义日期格式 ·····················543.1.3 使用千位分隔符 ·····················553.1.4 更改数值大小 ·························56
3.1.5 对齐小数点·····························573.1.6 添加单位 ································573.1.7 添加前导或后缀符号 ·············58
3.1.8 自定义数字显示方式 ·············593.1.9 自定义数字格式的准则 ·········603.2 设置格式化单元格 ·····························62
3.2.1 调整行高和列宽 ·····················623.2.2 设置单元格边框 ·····················643.2.3 设置单元格背景色和图案 ······643.2.4 设置工作表的背景图 ·············66
3.3 设置条件格式 ·····································663.3.1 编辑数据条规则 ·····················673.3.2 编辑图标集规则 ·····················68
3.3.3 编辑最前/最后规则 ················713.3.4 叠加多种条件格式 ·················723.3.5 使用公式创建条件格式 ·········733.4 套用表格样式 ·····································76
3.4.1 应用表样式·····························763.4.2 自定义表格样式 ·····················763.5 套用单元格样式 ·································79
3.5.1 应用单元格样式 ·····················793.5.2 创建新样式·····························793.5.3 修改和删除样式 ·····················80
3.6 综合实例——美化员工考核表 ··········82本章小结 ·····················································83课后习题 ·····················································84
第4章 Excel数据处理与分析4.1 数据排序 ··········································· 854.1.1 简单排序 ······························· 85
4.1.2 按单元格特征排序 ················ 864.1.3 恢复排序前的状态 ················ 874.1.4 按自定义序列排序 ················ 88
4.1.5 添加排序条件 ························ 904.2 数据筛选 ··········································· 904.2.1 自动筛选 ······························· 91
4.2.2 同字段多条件筛选 ················ 924.2.3 不同字段多条件筛选 ············ 934.2.4 将筛选结果复制到其他 工作表中 ······························· 93
4.2.5 利用排序和筛选功能分隔 销售清单 ······························· 944.3 数据分类汇总 ···································· 97
4.3.1 认识分类汇总 ························ 974.3.2 分类汇总数据 ························ 974.3.3 删除分类汇总 ························ 98
4.3.4 嵌套分类汇总 ························ 994.4 数据合并计算 ···································1014.4.1 按位置合并计算 ···················101
4.4.2 按分类合并计算 ···················1034.5 综合实例——分析与汇总商品 采购情况 ···········································104
本章小结 ···················································106课后习题 ···················································107
第5章 公式的使用5.1 认识公式 ·········································1085.1.1 公式的结构 ···························108
目录 35.1.2 运算符 ·································· 1085.1.3 运算符优先级 ······················· 1105.2 公式的基本操作 ······························· 111
5.2.1 输入公式 ······························ 1115.2.2 结合键盘和鼠标输入公式 ···· 1125.2.3 显示公式 ······························ 113
5.2.4 修改公式 ······························ 1145.2.5 复制公式 ······························ 1145.3 定义名称简化公式 ··························· 115
5.3.1 定义名称 ······························ 1155.3.2 在公式中引用名称 ··············· 1165.3.3 管理名称 ······························ 116
5.4 单元格的引用··································· 1185.4.1 相对引用 ······························ 1195.4.2 绝对引用 ······························ 120
5.4.3 混合引用 ······························ 1205.5 审核公式 ·········································· 1215.5.1 公式返回的错误值及其 产生原因 ······························ 121
5.5.2 检查错误 ······························ 1225.5.3 追踪引用单元格 ··················· 1235.5.4 追踪从属单元格 ··················· 124
5.6 综合实例——制作销售报表 ············ 124本章小结 ··················································· 126课后习题 ··················································· 126
第6章 函数的使用6.1 认识函数 ·········································· 1286.1.1 函数的含义 ·························· 128
6.1.2 函数的分类 ·························· 1286.1.3 输入函数 ······························ 1296.2 统计函数 ·········································· 131
6.2.1 常用统计函数 ······················· 1316.2.2 AVERAGE函数 ··················· 1336.2.3 MAX函数 ···························· 135
6.2.4 COUNTA函数 ····················· 1366.2.5 COUNT函数 ························ 1376.3 文本函数 ·········································· 138
6.3.1 常用文本函数 ······················· 1386.3.2 EXACT函数 ························ 1406.3.3 LOWER与UPPER函数 ······ 141
6.3.4 CONCATENATE函数 ·········· 1426.3.5 REPT函数 ···························· 1446.3.6 SUBSTITUTE函数 ·············· 145
6.4 逻辑函数 ·········································· 1466.4.1 常用逻辑函数 ······················· 1466.4.2 AND函数 ····························· 147
6.4.3 OR函数 ································ 1486.4.4 NOT函数 ····························· 1496.4.5 IF函数 ·································· 149
6.5 日期与时间函数 ······························· 1506.5.1 常用日期与时间函数 ··········· 1506.5.2 NOW函数：返回指定日期 · 152
6.5.3 HOUR函数：返回小时数 ··· 1536.5.4 DAYS360函数：计算两日期 之间的天数··························· 1546.6 查找与引用函数 ······························· 155
6.6.1 常用查找与引用函数 ··········· 1556.6.2 使用VLOOKUP函数计算 年龄所属分段 ······················· 1586.6.3 使用HLOOKUP函数模糊 查找计算提成 ······················· 159
6.7 综合实例——商品库存状态分析 ···· 160本章小结 ··················································· 162课后习题 ··················································· 163
4 中文版 Excel 2016 电子表格制作实例教程第7章 创建数据透视表与数据透视图7.1 数据透视表的基本操作 ··················· 1647.1.1 数据透视表的用途 ··············· 164
7.1.2 在新工作表中创建数据 透视表 ·································· 1647.1.3 在当前工作表中创建数据 透视表 ·································· 166
7.1.4 移动数据透视表 ··················· 1677.1.5 删除数据透视表 ··················· 1687.2 更改数据透视表的布局 ··················· 168
7.2.1 显示/隐藏分类汇总 ·············· 1687.2.2 设置报表布局 ······················· 1697.2.3 更改数据透视表样式 ··········· 170
7.3 设置数据透视表字段 ······················· 1717.3.1 更改字段名称 ······················· 1717.3.2 更改值的汇总方式 ··············· 172
7.3.3 以百分比显示数据 ··············· 1737.4 使用数据透视表筛选数据 ················ 1747.4.1 创建切片器··························· 174
7.4.2 使用切片器筛选数据 ··········· 1757.4.3 删除切片器··························· 1757.4.4 更改切片器样式 ··················· 176
7.4.5 添加筛选器··························· 1787.4.6 显示报表筛选页 ··················· 1787.5 使用数据透视图 ······························· 179
7.5.1 认识数据透视图 ··················· 1797.5.2 使用现有数据透视表创建 数据透视图 ·························· 1807.5.3 创建全新数据透视图 ··········· 181
7.5.4 使用数据透视图筛选数据 ···· 1827.5.5 转换为标准图表 ··················· 1837.6 综合实例——商品标题关键词 分析 ··················································· 184
本章小结 ···················································187课后习题 ···················································187
第8章 使用图表实现数据可视化8.1 认识图表 ··········································1898.1.1 图表的结构 ···························189
8.1.2 图表的类型 ···························1908.2 创建图表 ··········································1938.2.1 创建基本图表 ·······················193
8.2.2 创建组合图表 ·······················1948.2.3 调整图表的位置和大小 ········1958.2.4 更改图表类型 ·······················196
8.2.5 创建迷你图 ···························1978.3 编辑图表数据和元素 ·······················2008.3.1 更改和删除系列值 ···············200
8.3.2 添加系列 ······························2028.3.3 重新选择图表数据区域 ········2038.3.4 添加数据标签和网格线 ········2048.3.5 添加坐标轴标题 ···················205
8.3.6 将标题链接到单元格 ···········2068.4 美化图表 ··········································2078.4.1 应用图表样式 ·······················207
8.4.2 通过格式窗格设置图表 格式 ······································2088.4.3 插入形状 ······························210
8.4.4 插入图片 ······························ 2118.4.5 设置图片填充 ·······················2138.5 综合实例——使用甘特图制作 商品营销策划流程 ····································214
本章小结 ···················································217课后习题 ···················································217
目录 5第9章 创建链接与引用9.1 创建链接 ·········································· 2199.1.1 创建指向现有文件的链接 ···· 2199.1.2 创建指向特定网页的链接 ···· 220
9.1.3 创建指向工作表的链接 ······· 2219.1.4 创建指向特定单元格的 链接 ······································ 2229.1.5 删除链接 ······························ 223
9.2 数据引用 ·········································· 2249.2.1 引用本工作表内的单元格 ···· 2249.2.2 引用其他工作表的单元格 ···· 225
9.2.3 引用其他工作簿的单元格 ···· 2269.2.4 粘贴引用链接 ······················· 2279.3 嵌入与链接文件 ······························· 228
9.3.1 嵌入Word文档 ···················· 2289.3.2 嵌入PowerPoint演示文稿 ··· 2299.3.3 链接Excel工作簿 ················ 231
9.4 综合实例——在Word中调用 Excel工作表 ··································· 232本章小结 ··················································· 233
课后习题 ··················································· 234第10章 电子表格页面设置与打印10.1 设置页面打印版式 ························· 235
10.1.1 设置打印页面 ····················· 23510.1.2 设置页边距 ························· 23610.2 设置打印选项 ································· 237
10.2.1 设置打印区域 ····················· 23710.2.2 设置打印标题 ····················· 23810.2.3 设置打印行号和列标 ········· 239
10.2.4 设置草稿和单色方式 打印 ···································· 24010.2.5 设置网格线打印 ················· 24010.2.6 打印不连续的行或列 ········· 241
10.2.7 取消图片打印 ····················· 24110.2.8 打印设置 ···························· 24210.3 使用分页符 ····································· 243
10.3.1 插入分页符 ························· 24310.3.2 删除分页符 ························· 24410.4 设置页眉和页脚 ····························· 244
10.4.1 使用内置的页眉和页脚 ······ 24510.4.2 自定义页眉和页脚 ············· 24510.4.3 删除页眉和页脚 ················· 24710.5 添加水印效果 ································· 248
10.5.1 使用页眉和页脚添加 水印效果 ···························· 24810.5.2 通过插入艺术字添加 水印效果 ···························· 250
10.6 综合实例——设置与打印 工资表 ············································· 251本章小结 ··················································· 253
课后习题 ··················································· 253
亲爱的读者们，感谢您花时间阅读本文。如果您对本文有任何疑问或建议，请随时联系我。我非常乐意与您交流。
发表评论:
◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。