完全掌握Excel2016高效办公PDF电子书下载

第1章 融会贯通Excel 2016 2

1.1 Excel能做什么 2

1.2 启动与设置Excel 2016 3

1.2.1 启动和退出Excel2016 3

1.2.2 新手入门：快速认识Excel2016的操作环境 4

1.2.3 必要的准备：定制个性化的办公环境 6

1.3 学习Excel的心得体会 10

1.3.1 明确Excel在工作中的地位 10

1.3.2 不要强记菜单命令，建立自己常用的功能区 10

1.3.3 学习Excel公式的几点经验 11

1.3.4 建好源数据表，巧用分类汇总表 11

1.3.5 遇问题及时咨询解决 12

1.4 Excel的知识体系结构与操作流程 12

1.5 办公实例：制作第一个Excel工作簿——会议日程表 13

1.5.1 实例描述 13

1.5.2 操作步骤 13

1.5.3 实例总结 16

1.6 提高办公效率的诀窍 16

窍门1：注意填写表格标题的位置 16

窍门2：改变Excel程序窗口的背景与主题 17

第2章 Excel基本操作与数据输入 19

2.1 初识Excel2016 19

2.2 工作簿的常用操作 20

2.2.1 创建工作簿文件的流程 20

2.2.2 新建工作簿 21

2.2.3 保存工作簿 22

2.2.4 让旧版Excel也能打开Excel2016的工作簿 23

2.2.5 打开与关闭工作簿 23

2.2.6 新旧Excel工作簿版本的兼容性 24

2.3 工作表的基本操作 25

2.3.1 设置新工作簿的默认工作表数量 25

2.3.2 插入工作表 26

2.3.3 切换工作表 26

2.3.4 删除工作表 27

2.3.5 重命名工作表 27

2.3.6 选定多个工作表 28

2.3.7 移动和复制工作表 28

2.3.8 隐藏或显示工作表 29

2.3.9 拆分工作表 30

2.3.10 冻结工作表 31

2.4 在工作表中输入数据——创建员工工资表 31

2.4.1 输入文本 32

2.4.2 输入数字 32

2.4.3 输入日期或时间 33

2.4.4 输入特殊符号 33

2.4.5 快速输入身份证号 34

2.4.6 快速输入大写人民币值 35

2.4.7 分数输入 35

2.4.8 自动输入小数点 36

2.5 快速输入工作表数据 36

2.5.1 轻松一个按键就能输入重复的数据 37

2.5.2 在多个单元格中快速输入相同的数据 37

2.5.3 快速填充整列数据 38

2.5.4 快速输入序列数据 39

2.5.5 设置自定义填充序列 41

2.6 设置数据验证 41

2.6.1 控制输入数值的范围 41

2.6.2 禁止输入重复数据 42

2.6.3 限制单元格中文本的长度 43

2.6.4 设置输入提示信息 43

2.7 办公实例：制作员工登记表 44

2.7.1 实例描述 44

2.7.2 实例操作指南 44

2.7.3 实例总结 47

2.8 提高办公效率的诀窍 47

窍门1：设置新建工作簿的默认格式 47

窍门2：通过“自动保存”功能避免工作表数据意外丢失 48

窍门3：速选所有数据类型相同的单元格 48

窍门4：一次删除所有空白的行 49

窍门5：查找特定格式的单元格 49

窍门6：为单元格添加批注 50

第3章 工作表的数据编辑与格式设置 52

3.1 工作表中的行与列操作 52

3.1.1 选择表格中的行和列 52

3.1.2 插入与删除行和列 52

3.1.3 隐藏或显示行和列 54

3.2 工作表中的单元格操作 54

3.2.1 选择单元格 55

3.2.2 插入与删除单元格 56

3.2.3 合并与拆分单元格 57

3.2.4 将单元格中的文字转成垂直方向 58

3.3 编辑表格数据 59

3.3.1 修改数据 59

3.3.2 移动表格数据 60

3.3.3 以插入方式移动数据 61

3.3.4 复制表格数据 62

3.3.5 复制单元格属性 62

3.3.6 交换行列数据 63

3.3.7 复制单元格中的特定内容 63

3.3.8 在保持表格列宽的前提下复制数据 64

3.3.9 删除单元格数据格式 65

3.3.10 删除单元格内容 66

3.4 设置工作表中的数据格式 66

3.4.1 设置字体格式 66

3.4.2 更改单元格中个别的文字格式 67

3.4.3 设置字体对齐方式 67

3.4.4 设置数字格式 68

3.4.5 设置日期和时间格式 70

3.4.6 自定义数字格式 71

3.4.7 设置表格的边框 74

3.4.8 设置表格的填充效果 76

3.4.9 调整表格列宽与行高 76

3.4.10 套用表格格式 77

3.4.11 查找与替换单元格格式 78

3.4.12 设置条件格式 80

3.5 美化工作表的外观 83

3.5.1 设置工作表标签颜色 83

3.5.2 绘制自由图形 84

3.5.3 插入艺术字 85

3.5.4 插入与设置文本框 86

3.5.5 插入与设置图片 88

3.5.6 插入与设置SmartArt图形 89

3.5.7 实例操作指南 90

3.6 办公实例——美化员工登记表 91

3.6.1 实例描述 92

3.6.2 实例总结 92

3.7 提高办公效率的诀窍 92

窍门1：在保持表格形状的前提下，将表格复制到其他工作表 92

窍门2：让文本在单元格内自动换行 93

窍门3：倾斜排版单元格数据 93

窍门4：巧妙实现欧元与其他货币的转换 93

窍门5：巧妙删除空白列 94

第4章 使用公式与函数处理表格数据 97

4.1 公式的输入与使用 97

4.1.1 基本概念 97

4.1.2 公式中的运算符 97

4.1.3 运算符的优先级 99

4.1.4 输入公式 99

4.1.5 编辑公式 100

4.2 单元格引用方式 100

4.2.1 相对引用单元格 101

4.2.2 绝对引用单元格 101

4.2.3 混合引用单元格 102

4.2.4 不同位置上的引用 103

4.3 使用自动求和 104

4.4 使用函数 105

4.4.1 常用函数的说明 105

4.4.2 使用函数向导输入函数 106

4.4.3 手动输入函数 107

4.4.4 使用嵌套函数 108

4.5 使用单元格名称 108

4.5.1 命名单元格或区域 109

4.5.2 定义常量和公式的名称 109

4.5.3 在公式和函数中使用命名区域 110

4.6 使用数组公式 110

4.6.1 数组公式入门 111

4.6.2 利用公式完成多项运算 111

4.6.3 公司员工考勤处理 112

4.6.4 快速计算特定范围数据之和 112

4.7 办公实例1：统计员工在职培训成绩 113

4.7.1 实例描述 113

4.7.2 操作步骤 113

4.7.3 实例总结 115

4.8 办公实例2：计算业绩奖金 116

4.8.1 实例描述 116

4.8.2 实例操作指南 116

4.8.3 实例总结 123

4.9 提高办公效率的诀窍 123

窍门1：快速定位所有包含公式的单元格 123

窍门2：将公式结果转化成数值 123

窍门3：使用ROUND函数进行四舍五入 123

窍门4：使用INT函数向下取整的最接近整数 124

窍门5：使用ABS函数求绝对值 124

窍门6：使用MOD函数返回余数 125

窍门7：使用RAND函数返回随机数 125

窍门8：使用DAYS360函数返回两日期间相差的天数 125

窍门9：使用WEEKDAY函数判断星期几 126

窍门10：设置单元格以星期名称的形式显示日期 126

窍门11：计算日期对应的中文星期数 126

窍门12：将单列竖排表格转换成三行的横排表格 127

第5章 公式审核与高级函数应用 129

5.1 公式审核 129

5.1.1 公式自动更正 129

5.1.2 公式中的错误信息 130

5.1.3 使用公式审核工具 131

5.2 统计函数的应用 133

5.2.1 MEDIAN函数——计算中值 133

5.2.2 STDEV.S函数——计算标准偏差 134

5.2.3 VAR.S函数——计算方差 135

5.2.4 COUNTA函数——计算非空白单元格个数 135

5.2.5 COUNTIF函数——计算符合条件的个数 136

5.2.6 FREQUENCY函数——计算符合区间的函数 136

5.3 财务函数 137

5.3.1 PV函数——计算现值 137

5.3.2 FV函数——计算未来值 138

5.3.3 PMT函数——计算每期的数值 139

5.3.4 RATE函数——计算利率 139

5.3.5 NPER函数——计算期数 140

5.3.6 折旧函数 141

5.4 数学和三角函数 143

5.4.1 ABS函数——计算绝对值 143

5.4.2 SQRT函数——计算平方根 143

5.4.3 RANDBETWEEN函数——求随机数 144

5.4.4 SUMIF函数——计算符合条件的总和 144

5.4.5 ROUND函数——将数字四舍五入 145

5.5 逻辑函数 147

5.5.1 IF函数——判断条件 147

5.5.2 AND函数——条件全部成立 148

5.5.3 OR函数——条件之一成立 149

5.6 查找与引用函数 150

5.6.1 VLOOKUP函数——自动查找填充数据 150

5.6.2 HLOOKUP函数——在列表中查找特定值 152

5.6.3 INDEX函数——返回指定行列交叉值 153

5.6.4 MATCH函数——返回数值中符合条件的单元格内容 154

5.7 日期及时间函数 155

5.7.1 TODAY函数——返回当前系统的日期 155

5.7.2 DATEDIF函数——计算日期间隔 155

5.8 文本函数 156

5.8.1 LEFT函数——提取前几个字符串 156

5.8.2 RIGHT函数——提取最后的字符串 157

5.8.3 MID函数——提取指定位置、字数的字符串 158

5.8.4 CONCATENATE函数——组合字符串 158

5.9 办公实例1：计算银行贷款管理表 159

5.9.1 实例描述 159

5.9.2 计算还清日期 160

5.9.3 判断是否到期 160

5.9.4 计算本月需要还款额 160

5.9.5 计算已还本金总额 161

5.9.6 计算已还利息总额 161

5.9.7 实例总结 162

5.10 办公实例2：员工档案管理 162

5.10.1 实例描述 162

5.10.2 利用身份证号码提取员工性别信息 162

5.10.3 利用身份证号提取员工出生日期信息 163

5.10.4 计算员工的年龄 164

5.10.5 计算员工的工龄 165

5.10.6 统计员工的性别人数 166

5.10.7 员工年龄分段统计 166

5.10.8 实例总结 167

5.11 提高办公效率的诀窍 167

窍门1：显示与隐藏公式 167

窍门2：计算某一年的天数 168

窍门3：返回日期前后指定工作日的日期 168

窍门4：计算会议结束的时间 168

窍门5：计算还款期最后一天的日期 169

窍门6：计算消费的结算日期 169

窍门7：返回绝对值增大的一组 169

窍门8：计算超几何分布 170

窍门9：使用SLN函数返回折旧值 170

窍门10：金额数字分列 170

窍门11：人民币金额大写公式 171

第6章 数据分析与管理 173

6.1 对数据进行排序 173

6.1.1 默认排序顺序 173

6.1.2 按列简单排序 173

6.1.3 按行简单排序 174

6.1.4 多关键字复杂排序 174

6.1.5 按姓名排序日常费用表 175

6.1.6 自定义排序 176

6.1.7 利用颜色进行排序 177

6.2 数据筛选 178

6.2.1 自动筛选 178

6.2.2 自定义筛选 179

6.2.3 高级筛选 180

6.3 分类汇总 182

6.3.1 创建分类汇总 183

6.3.2 分级显示分类汇总 183

6.3.3 嵌套分类汇总 184

6.3.4 删除分类汇总 185

6.3.5 组合与分级显示 185

6.4 办公实例：统计分析员工工资 187

6.4.1 实例描述 187

6.4.2 实例操作指南 188

6.4.3 实例总结 191

6.5 提高办公效率的诀窍 191

窍门1：通过Excel表进行排序和筛选 191

窍门2：对数据进行合并计算 192

窍门3：使用RANK函数排序 194

窍门4：对高于或低于平均值的数值设置格式 195

窍门5：快速删除表格中重复的数据 196

第7章 使用图表 198

7.1 图表类型介绍 198

7.2 即时创建图表 201

7.3 创建图表的基本方法 202

7.4 图表的基本操作 203

7.4.1 选定图表项 203

7.4.2 调整图表大小和位置 203

7.4.3 更改图表源数据 204

7.5 修改图表内容 207

7.5.1 添加并修饰图表标题 207

7.5.2 设置坐标轴及标题 208

7.5.3 添加图例 209

7.5.4 添加数据标签 209

7.5.5 显示数据表 210

7.5.6 更改图表类型 210

7.5.7 设置图表布局和样式 211

7.5.8 设置图表区与绘图区的格式 212

7.5.9 添加趋势线 213

7.6 迷你图的使用 214

7.6.1 插入迷你图 214

7.6.2 更改迷你图类型 215

7.6.3 显示迷你图中不同的点 216

7.6.4 清除迷你图 216

7.7 动态图表的使用 216

7.7.1 创建简单Excel动态图表 216

7.7.2 利用控件创建高级动态图表 219

7.8 办公实例：使用饼图创建问卷调查结果图 226

7.8.1 实例描述 226

7.8.2 实例操作指南 226

7.8.3 实例总结 228

7.9 提高办公效率的诀窍 228

窍门1：更改图表中个别数据点的形状 228

窍门2：将图表转换为图片 228

窍门3：将位数过多的刻度改以“千”为单位 229

第8章 工作表的安全与打印输出 231

8.1 工作簿和工作表的安全性设置 231

8.1.1 保护工作表 231

8.1.2 保护工作簿的结构 232

8.1.3 检查工作簿的安全性 233

8.1.4 为工作簿设置密码 234

8.2 页面设置 235

8.2.1 设置页边距 235

8.2.2 设置纸张方向 236

8.2.3 设置纸张大小 236

8.2.4 设置打印区域 237

8.2.5 设置打印标题 237

8.3 设置分页符 238

8.3.1 插入分页符 238

8.3.2 移动分页符 238

8.4 设置页眉和页脚 239

8.5 打印预览 240

8.6 快速打印工作表 240

8.7 办公实例：打印工资条 241

8.7.1 实例描述 241

8.7.2 实例操作指南 241

8.7.3 实例总结 243

8.8 提高办公效率的诀窍 243

窍门1：打印工作表默认的网格线 243

窍门2：打印工作表中的公式 244

窍门3：模仿复印机的功能缩放打印 244

窍门4：避免打印错误提示信息 245

窍门5：改变起始页码 246

窍门6：不打印单元格中的颜色与底纹 246

第9章 使用数据透视表及数据透视图 248

9.1 创建与应用数据透视表 248

9.1.1 了解数据透视表 248

9.1.2 数据透视表的应用 248

9.1.3 数据透视表的组成组件 249

9.1.4 创建数据透视表 250

9.2 创建数据透视表时注意的问题 251

9.2.1 遇到第一行不是标题行的情况 251

9.2.2 遇到第一列是标题的情况 252

9.2.3 遇到标题字段中包含空白单元格的情况 252

9.2.4 遇到数据源中包含空行和空格的情况 253

9.2.5 如果数据源中包含合计单元格 253

9.2.6 遇到数据源是二维数据表的情况 254

9.3 修改数据透视表 254

9.3.1 添加和删除数据透视表字段 254

9.3.2 改变数据透视表中数据的汇总方式 255

9.3.3 查看数据透视表中的明细数据 256

9.3.4.更新数据透视表数据 257

9.3.5 数据透视表自动套用样式 257

9.3.6 利用颜色增加数据透视表的信息量 258

9.4 数据透视图 259

9.4.1 数据透视图概述 259

9.4.2 创建数据透视图 260

9.5 切换器的使用 261

9.5.1 在数据透视表中插入切片器 261

9.5.2 通过切片器查看数据透视表中的数据 261

9.5.3 美化切片器 262

9.6 办公实例：分析公司费用支出 263

9.6.1 实例描述 263

9.6.2 实例操作指南 263

9.6.3 实例总结 265

9.7 提高办公效率的诀窍 265

窍门：设置自己喜欢的数据透视表样式 265

第10章 数据分析工具 268

10.1 模拟分析的方法 268

10.2 单变量求解 268

10.2.1 一元一次方程式的实例 269

10.2.2 学期成绩分析 270

10.2.3 商品理想售价的制订 272

10.2.4 损益平衡的财务分析 272

10.3 创建模拟运算表 276

10.3.1 模拟运算表概述 276

10.3.2 利用单变量模拟运算表求解数学方程式 276

10.3.3 利用单变量模拟运算表求解每月贷款偿还额 278

10.3.4 利用双变量模拟运算表求解数学方程式 279

10.3.5 利用双变量模拟运算表求解不同贷款与利率的偿还额 281

10.3.6 清除模拟运算表 281

10.4 使用方案 282

10.4.1 获取最佳的贷款方案 282

10.4.2 求解最佳的销售方案 285

10.4.3 方案的应用——5年盈收预测分析方案 288

10.5 办公实例：制作公司投资方案表 294

10.5.1 实例描述 294

10.5.2 实例操作指南 294

10.5.3 实例总结 296

10.6 提高办公效率的诀窍 297

窍门1：将工作表输出为PDF格式 297

窍门2：将Excel文档通过电子邮件发送给其他人 297

第11章 规划求解 299

11.1 认识规划求解 299

11.1.1 启动规划求解加载项 299

11.1.2 二元二次方程式的求解 300

11.1.3 Excel规划求解与单变量求解的差异 301

11.2 皮鞋销售数量分析 302

11.2.1 利用单变量求解调整销售量获得理想的边际效益 302

11.2.2 利用规划求解调整销量取得理想的边际利润 303

11.2.3 添加规划求解的约束 304

11.2.4 对变量设限的规划求解 305

11.3 鸡兔同笼问题 307

11.4 规划求解报告 309

11.5 办公实例：获取月开支计划 312

11.5.1 实例描述 312

11.5.2 实例操作指南 312

11.5.3 实例总结 315

第12章 使用Excel高效办公 317

12.1 使用样式制作统一格式的工作表 317

12.1.1 创建样式 317

12.1.2 修改样式 318

12.2 多人共享工作簿 319

12.2.1 在网络上打开工作簿 319

12.2.2 创建共享工作簿 320

12.2.3 撤销工作簿的共享状态 321

12.2.4 保护并共享工作簿 321

12.2.5 编辑与查看共享工作簿 322

12.2.6 解决修改中的冲突 323

12.2.7 完成对工作簿的修订 323

12.3 办公实例：制作公司月销售报表 324

12.3.1 实例描述 324

12.3.2 实例操作指南 325

12.3.3 实例总结 327

第13章 Excel VBA的应用 329

13.1 Excel VBA和宏概述 329

13.1.1 Excel VBA的主要用途 329

13.1.2 如何学习Excel VBA 329

13.1.3 宏和宏录制器 330

13.1.4 录制宏 331

13.1.5 查看录制的宏内容 333

13.1.6 保存带宏的工作簿 334

13.1.7 录制宏的应用实例1：记录自动排序的宏 334

13.1.8 录制宏的应用实例2：记录创建图表的宏 337

13.1.9 运行宏的几种方式 341

13.2 认识VBA的开发环境 343

13.2.1 VBA的启动方式与操作界面介绍 343

13.2.2 工程窗口 344

13.2.3 代码窗口与用户窗体 345

13.3 认识VBA的代码与过程 348

13.3.1 过程程序 348

13.3.2 事件程序 353

13.3.3 对象、属性和方法 358

13.3.4 VBA中的常用语句 360

13.4 函数的使用 375

13.4.1 使用VBA函数 375

13.4.2 调用工作表函数 375

13.4.3 自定义函数 376

13.5 办公实例：利用VBA编制员工销售业绩表 380

13.5.1 实例描述 380

13.5.2 实例操作指南 381

13.5.3 实例总结 385

13.6 提高办公效率的诀窍 386

窍门1：使用VBA控制图表对象的显示 386

窍门2：人民币大写转换 387

第14章 与其他软件协同办公 390

14.1 Excel与Word数据相互转换 390

14.1.1 复制Word表格至Excel工作表 390

14.1.2 复制Excel数据Word文档 390

14.2 Excel与文本数据间的协同操作 392

14.2.1 将Excel文件另存为文本数据 392

14.2.2 导入来自文本文件的数据 393

14.2.3 在Excel中实现文本分列 395

14.3 将Access数据库导入Excel做销售分析 397

14.3.1 将数据库导入到Excel 397

14.3.2 利用“数据透视表”分析导入的Access数据 398

14.4 将工作表、图表制作成PowerPoint简报 398

14.4.1 将工作表内容复制到幻灯片中 398

14.4.2 将单元格区域复制成幻灯片中的表格 399

14.4.3 指定单元格数据粘贴幻灯片中的方式 400

14.4.4 将工作表的图表复制到幻灯片中 402

14.5 办公实例：人事薪资系统——与Word的合并打印 403

14.5.1 实例描述 403

14.5.2 实例操作指南 403

14.5.3 实例总结 410

第15章 Excel的网络应用 412

15.1 在工作表中创建超链接 412

15.1.1 创建超链接 412

15.1.2 利用超链接串联其他表格 413

15.1.3 超链接到电子邮件 414

15.1.4 编辑与删除超链接 414

15.1.5 输入网址时自动套用格式 415

15.2 将工作簿保存为网页 416

15.2.1 将Excel工作表另存为网页格式 416

15.2.2 发布为网页 417

15.3 将网页上的数据导入Excel 418

15.3.1 复制网页数据 418

15.3.2 将网页数据导入工作簿并进行更新 419

15.4 将Excel文件保存到云网络 420

15.4.1 从Excel2016上传文件到网络空间 421

15.4.2 在Windows10中使用OneDrive 421

15.4.3 使用网页版OneDrive 422