Excel应用与技巧大全 数据处理与分析PDF电子书下载

第1章 熟悉和定制Excel 2013 1

1.1 初识Excel 2013 2

1.1.1 Excel在办公中的应用 2

1.1.2 Excel 2013新增功能 3

1 全新的启动菜单 3

2 独立的工作簿窗口 3

3 即时数据分析 3

技巧1-1 取消选择时显示“快速分析”选项 3

4 瞬间填充整列数据 4

5 全新的图表和数据透视表的推荐功能 4

6 联机保存和共享文件 4

1.2 Excel 2013的启动与退出 5

1.2.1 启用Excel 2013 5

1 从“开始”菜单启动 5

2 固定到任务栏 5

技巧1-2 启动Excel 2013的安全模式 6

1.2.2 退出Excel 2013 6

1 单击“关闭”命令 6

2 单击“关闭”按钮 6

1.3 熟悉Excel 2013的工作环境 6

1.3.1 认识界面的组成 7

技巧1-3 隐藏编辑栏 7

1.3.2 工作簿与工作表 7

技巧1-4 修改默认的工作表数 7

技巧1-5 工作表标签可以从右到左显示 8

1.3.3 快速访问工具栏 8

1.3.4 “文件”按钮和Backstage视图 8

技巧1-6 设置最近使用的工作簿显示数量 8

1.3.5 认识功能区 8

1.4 定制Excel 2013工作环境 9

1.4.1 使用Microsoft账户登录 9

技巧1-7 轻松切换和注销账户 9

1.4.2 定制用户账户信息 9

1.4.3 定制Office背景和主题 10

技巧1-8 禁止显示浮动工具栏 10

1.4.4 自定义快速访问工具栏 10

示例1-1 将“记录单”添加到快速访问工具栏中 10

技巧1-9 自定义屏幕提示样式 11

1.4.5 自定义选项卡和组 11

示例1-2 让“照相机”按钮显示在功能区中 12

1.4.6 自定义状态栏 12

技巧1-10 取消消息栏警报 13

1.4.7 在功能区下方显示快速访问工具栏 13

1.4.8 自由控制功能区的显示状态 13

技巧1-11 在工作区域不显示滚动条 13

第2章 Excel的基本操作 14

2.1 工作簿的基本操作 15

2.1.1 打开和关闭工作簿 15

示例2-1 快速打开最近使用的工作簿 16

技巧2-1 锁定常用文档便于多次打开 16

技巧2-2 同时打开多个工作簿 17

技巧2-3 打开工作簿的副本 17

技巧2-4 打开受损的工作簿 17

技巧2-5 启用Excel时自动打开某个工作簿 17

技巧2-6 使早期版本创建的工作簿能够与当前版本兼容 18

2.1.2 新建工作簿 18

示例2-2 使用模板创建库存列表工作簿 18

技巧2-7 使用快捷键新建工作簿 19

技巧2-8 通过“新建”按钮新建工作簿 19

2.1.3 保存工作簿 20

示例2-3 保存新建的库存列表工作簿 20

技巧2-9 将编辑好的工作簿保存为模板 21

技巧2-10 更改默认的文件保存格式 21

技巧2-11 查看工作簿的保存路径 21

技巧2-12 高质量保存工作簿中的图片 22

技巧2-13 设置自动保存工作簿的时间 22

技巧2-14 恢复未保存的工作簿 22

技巧2-15 为工作簿“减肥” 22

2.2 工作表的基本操作 23

2.2.1 新建和删除工作表 23

技巧2-16 选择工作表 23

技巧2-17 同时选定多个工作表 23

2.2.2 插入工作表 23

技巧2-18 同时插入多张工作表 24

2.2.3 重命名工作表 24

示例2-4 在各部门信息工作簿中创建财务部工作表 24

2.2.4 移动和复制工作表 25

技巧2-19 使用鼠标快速移动和复制工作表 25

2.2.5 设置工作表标签颜色 25

示例2-5 设置部门信息表的工作表标签颜色 25

技巧2-20 改变工作表标签的字号 26

2.3 行和列的基本操作 26

2.3.1 选择行和列 26

技巧2-21 选择多行或多列 26

2.3.2 行和列的插入与删除 27

2.3.3 移动和复制行与列 27

2.3.4 设置行高和列宽 27

示例2-6 根据单元格中的内容自动调整列宽 27

技巧2-22 使用双击法快速让列宽与内容匹配 28

技巧2-23 手动调整行高和列宽 28

技巧2-24 将列宽与另一列匹配 28

技巧2-25 更改工作表的默认列宽 28

2.4 单元格和单元格区域的基本操作 29

2.4.1 A1引用样式和R1C1引用样式 29

技巧2-26 将A1引用样式更改为R1C1引用样式 29

2.4.2 选择单元格和单元格区域 29

技巧2-27 使用Shift键快速选择单元格区域 29

技巧2-28 通过名称快速选择单元格区域 30

技巧2-29 快速选中最后一个单元格 30

技巧2-30 改变选中单元格的背景颜色 30

2.4.3 插入单元格和单元格区域 30

2.4.4 删除单元格和单元格区域 30

2.4.5 合并单元格 31

示例2-7 合并标题行 31

技巧2-31 跨越合并 32

2.5 工作窗口视图的控制 32

2.5.1 新建窗口 32

2.5.2 重排窗口 32

示例2-8 比较本年度和上年度的销售额 33

技巧2-32 并排查看 34

技巧2-33 同步滚动工作簿 34

技巧2-34 重设窗口的位置 34

2.5.3 冻结窗格 34

示例2-9 冻结员工信息表的标题行和名称列 35

技巧2-35 快速切换窗口 35

2.5.4 拆分窗口 35

2.5.5 窗口的缩放 36

技巧2-36 自定义显示比例 36

技巧2-37 仅在窗口中显示表格 36

技巧2-38 让Excel自动滚屏 36

2.5.6 自定义视图 36

示例2-10 自定义视图 37

技巧2-39 快速显示自定义的视图 37

第3章 数据安全很重要 38

3.1 保护工作簿 39

3.1.1 用密码进行加密 39

示例3-1 将员工信息工作簿加密 39

技巧3-1 分别设置打开权限和修改权限密码 40

3.1.2 标记为最终状态 41

示例3-2 将项目时间表标记为最终状态避免修改 41

技巧3-2 将工作簿保存为“只读”状态进行保护 42

3.1.3 保护工作簿结构 42

示例3-3 保护销售日报表的工作簿结构使其仅供查看 42

3.1.4 隐藏工作簿窗口 43

示例3-4 隐藏促销计划工作簿窗口 43

技巧3-3 隐藏文档保护工作簿 43

技巧3-4 清除最近使用的工作薄记录 44

3.2 保护工作表 44

3.2.1 禁止编辑工作表 44

示例3-5 禁止编辑员工信息工作簿导航页工作表 44

技巧3-5 允许用户对数据进行排序 45

3.2.2 隐藏工作表 45

示例3-6 隐藏工程价格计算工作表 45

技巧3-6 隐藏工作表标签进行保护 46

3.2.3 允许编辑的区域 46

示例3-7 在采购申请单中设置仅数量所在区域可填写 46

3.2.4 指定编辑可编辑区域的人员 47

示例3-8 指定人员使用密码编写采购单价 47

3.2.5 隐藏行和列 49

技巧3-7 隐藏工作表中的公式使其不可见 49

技巧3-8 禁止复制隐藏的行或列中的数据 50

3.3 添加数字签名保护工作表 50

3.3.1 添加不可见的数字签名 50

示例3-9 添加不可见的数字签名 50

3.3.2 添加Microsoft Office签名行 51

示例3-10 为采购申请单添加签名行 51

3.3.3 添加图章签名行 53

3.4 使用“文档检查”保护私有信息 53

第4章 数据的高效录入 54

4.1 输入和编辑数据 55

4.1.1 在单元格中输入数据 55

技巧4-1 修改Enter键的默认设置 55

4.1.2 在多个单元格中同时输入数据 55

示例4-1 在相应的单元格中输入相同的部门 56

技巧4-2 在固定单元格中输入数据 56

4.1.3 在多个工作表中同时输入数据 57

示例4-2 在各月份工作表中同时创建统计表 57

技巧4-3 强制换行 57

技巧4-4 让Excel会多种语言 58

4.1.4 编辑单元格内容 58

4.1.5 删除单元格中的内容 58

4.2 手动录入数据的技巧 58

4.2.1 分数的输入 59

4.2.2 指数上标的输入 59

示例4-3 输入方程表达式 59

技巧4-5 为中文添加拼音标注 60

4.2.3 插入符号或特殊符号 61

示例4-4 用“√”表示签到 61

4.2.4 自动输入小数点 62

示例4-5 输入成本时自动添加小数点 62

4.3 使用“设置单元格格式”快速输入指定类型数据 62

4.3.1 输入文本型数据 63

示例4-6在产品生产表中输入以0开头的产品编号 63

技巧4-6 输入身份证号码 64

4-3-2 自动添加货币符号 64

示例4-7 输入单价时自动添加货币符号 64

4.3.3 输入中文大写数字 65

示例4-8 为借款单输入中文大写数字 65

4.3.4 自定义数字格式 65

示例4-9 自定义利润表中金额的显示格式 65

技巧4-7 快速让数字缩小1000、1万、10万倍显示 66

4.4 使用“数据验证”高效输入数据 67

4.4.1 只允许输入整数 67

示例4-10 在办公用品领用登记表中设置只允许输入整数 67

技巧4-8 限制单元格中的输入法模式 68

4.4.2 制作下拉列表快速输入 69

示例4-11 在新员工统计表中快速输入性别 69

4.4.3 输入指定长度的文本 70

示例4-12 在新员工登记表中设置联系电话的长度为11位 70

4.4.4 自定义数据验证的条件 71

示例4-13 限制身份证号码为18位并以“110”开头 71

技巧4-9 圈释出无效的身份证号码 72

技巧4-10 禁止输入重复数据 72

技巧4-11 出错警告详解 73

4.5 其他高效输入数据的方法 73

4.5.1 使用“记录单”输入数据 73

示例4-14 使用“记录单”快速输入员工信息 73

4.5.2 记忆式键入 75

第5章 根据已有数据输入相关数据 76

5.1 使用“复制粘贴”快速输入重复数据 77

5.1.1 移动或复制单元格内容 77

示例5-1 快速复制出统计表 77

技巧5-1 仅复制可见的单元格 78

技巧5-2 复制工作表中特定的内容或属性 79

技巧5-3 互换相邻两个单元格区域数据的位置 80

技巧5-4 快速互换表格中行和列数据的位置 80

5.1.2 使用“剪贴板”复制并粘贴多个项目 80

示例5-2 使用“剪贴板”快速输入各产品类别 81

技巧5-5 隐藏或显示“粘贴选项”按钮 82

5.2 自动填充数据 82

5.2.1 快速输入相同数据 82

示例5-3 快速输入相同的产品单位 82

5.2.2 快速填充有规律的数据 83

示例5-4 填充产品编号 83

5.2.3 自定义输入填充序列 84

示例5-5 填充值班表中的员工姓名 84

5.2.4 从右键的“从下拉列表中选择”输入 85

技巧5-6 快速填充等差序列 85

技巧5-7 快速填充等比序列 86

5.3 快速填充数据 86

5.3.1 分列数据 87

示例5-6 快速分列显示姓和名 87

技巧5-8 使用“分列”功能分列显示数据 88

5.3.2 合并数据 88

示例5-7 自动合并所在省和市 88

第6章 使用外部数据库文件 90

6.1 使用超链接链接外部数据 91

6.1.1 创建超链接 91

示例6-1 为年度销售额链接季度销售数据 91

技巧6-1 使用函数创建超链接 92

6.1.2 编辑超链接 92

示例6-2 更改超链接的样式 93

技巧6-2 快速修改超链接样式 94

示例6-3 激活超链接 94

6.1.3 删除超链接 95

示例6-4 删除超链接 95

6.2 获取文本文件中的数据 96

6.2.1 创建文本文件 96

示例6-5 创建工作日志文本文件 96

6.2.2 将文本文件导入到工作表中 97

示例6-6 将工作日志文本文件导入到工作表中 97

技巧6-3 使用“粘贴选项”导入文本文件数据 99

6.3 获取Access数据库中的数据 99

示例6-7 获取Access数据库中的员工信息 99

6.4 获取网站中的数据 100

示例6-8 获取网站上的统计数据表格 100

6.5 将Word中的数据在Excel中显示 101

6.5.1 使用“选择性粘贴”功能导入数据 101

示例6-9 将Word中的员工离职申请表复制到Excel中 102

6.5.2 使用“对象”功能导入数据 103

示例6-10 在调职申请表中插入Word中的说明文本 103

6.6 刷新导入的数据 104

6.6.1 全部刷新 105

示例6-11 刷新工作日志中的所有数据 105

6.6.2 局部刷新 106

6.6.3 取消刷新 106

第7章 优化工作表中的数据 107

7.1 拼写和语法检查 108

7.1.1 进行拼写和语法检查 108

示例7-1 检查项目报价表中产品名称的拼写和语法 108

7.1.2 自动更正拼写 109

示例7-2 将“（a）”自动更正为“＠” 109

技巧7-1 利用朗读功能提高检查的速度 110

7.2 更改数据格式 110

7.2.1 更改数据类型 111

示例7-3 更改借款单中的数字金额为中文大写格式 111

7.2.2 增加或减少小数位数 112

示例7-4 增加销售表中金额的小数位数 112

7.2.3 添加千位分隔符 113

示例7-5 为财务报表添加千位分隔符 113

7.3 查找与替换数据 114

7.3.1 查找替换单元格内容 114

示例7-6 将检验统计表中的“是”替换为“合格” 115

技巧7-2 查找符合特定格式的单元格 116

7.3.2 查找和替换单元格中的格式 116

示例7-7 将检验统计表中表示不合格的文本替换为红色填充色 116

7.3.3 查找替换单元格中的特殊符号 118

示例7-8 将客户联系表中的“＠”符号替换为“＃”号 118

技巧7-3 设置模糊查找和精确查找 119

7.4 强大的定位功能 119

7.4.1 定位常量、批注和空值 120

示例7-9 将销售统计表中的空格快速填充为“0” 120

7.4.2 定位行／列内容差异单元格 121

7.4.3 定位从属和引用单元格 121

示例7-10 检查公式的正确性 121

第8章 格式化工作表中的数据 123

8.1 数据的显示方式 124

8.1.1 自动换行 124

示例8-1 使用“自动换行”显示隐藏文本 124

8.1.2 调整文本显示方向 125

示例8-2 调整调职申请表的文本显示方向 125

8.2 格式化数据 126

8.2.1 设置字体、字号、字形和颜色 126

示例8-3 为员工信息表设置字体 126

技巧8-1 更改默认的字体和字号 127

技巧8-2 为同一个单元格中的文本设置不同的格式 127

8.2.2 设置对齐方式 127

示例8-4 为员工信息表设置对齐方式 127

8.2.3 为单元格添加边框 128

示例8-5 为员工培训成绩表添加边框 128

技巧8-3 制作带斜线表头的表格 130

8.2.4 为单元格添加底纹 130

示例8-6 为员工培训成绩表添加底纹 130

8.3 单元格样式的应用 131

8.3.1 套用内置单元格样式 131

示例8-7 为车辆出入登记表套用内置单元格样式 131

技巧8-4 在工作簿间复制单元格样式 133

8.3.2 自定义单元格样式 133

示例8-8 为销售日报表自定义单元格样式 133

8.4 插入图形和图片增加工作表效果 137

8.4.1 插入与编辑图片和剪贴画 137

示例8-9 在促销计划表中添加剪贴画 137

技巧8-5 为表格设置图案背景 138

技巧8-6 删除图片背景 138

8.4.2 插入与编辑艺术字 138

示例8-10 为促销计划表添加艺术字样式的标题 138

8.4.3 绘制与编辑形状 140

示例8-11 创建销售流程图 140

8.4.4 添加SmartArt图形 141

示例8-12 创建公司组织结构图 141

第9章 “条件格式”突出显示重要值 143

9.1 应用条件格式 144

9.1.1 突出显示单元格规则 144

示例9-1 突出显示销售表中的特定单元格 144

9.1.2 项目选取规则 145

示例9-2 使用项目选取规则快速选择销售表中的数据 145

9.1.3 数据条 146

示例9-3 在销售表中使用数据条分析数据 146

技巧9-1 隐藏数据只显示数据条 147

9.1.4 色阶 147

示例9-4 在销售表中使用色阶分析数据 147

9.1.5 图标集 148

示例9-5 在销售表中使用图标集分析数据 148

9.2 新建规则 148

9.2.1 只为包含以下内容的单元格设置格式 148

示例9-6 在成绩表中设置只为包含以下内容的单元格设置格式 148

9.2.2 仅对排名靠前或靠后的数值设置格式 150

示例9-7 在成绩表中设置仅对排名靠前或靠后的数值设置格式 150

9.2.3 仅对高于或低于平均值的数值设置格式 152

示例9-8 在成绩表中设置仅对高于或低于平均值的数值设置格式 152

9.3 条件格式的相关操作 153

9.3.1 管理规则 153

示例9-9 编辑销售表中的规则 153

9.3.2 重设图标集规则 154

示例9-10 重设销售表中的图标集规则 154

9.3.3 重设色阶规则 155

示例9-11 重设销售表中的色阶规则 155

9.3.4 定位条件格式 156

示例9-12 清除销售表中所选单元格的规则 156

9.3.5 清除规则 157

1 清除某一条规则 157

示例9-13 清除销售表中的数据条规则 157

2 清除所选单元格的规则 158

示例9-14 清除销售表中所选单元格的规则 158

3 清除整个工作表的规则 158

示例9-15 清除销售表中的全部规则 158

第10章 简单的数据处理 159

10.1 数据的排序 160

10.1.1 简单排序 160

示例10-1 按照交通费用的高低进行排序 160

10.1.2 多关键字排序 160

示例10-2 在费用表中按多个关键字进行排序 161

技巧10-1 删除排序条件 162

10.1.3 按笔画排序 162

示例10-3 按费用表中的姓名进行降序排序 162

技巧10-2 按字母进行排序 163

10.1.4 按颜色排序 163

示例10-4 按颜色排序费用表中的数据 163

10.1.5 自定义排序 164

示例10-5 自定义排序费用表中的数据 164

10.2 数据的筛选 166

10.2.1 自动筛选 166

示例10-6 自动筛选费用表中的数据 166

10.2.2 自定义筛选 167

示例10-7 自定义筛选费用表中的数据 167

技巧10-3 筛选文本 168

技巧10-4 筛选空值 168

技巧10-5 筛选日期 169

技巧10-6 按单元格颜色筛选 169

10.2.3 使用搜索框进行筛选 169

示例10-8 使用搜索框在费用表中进行筛选 169

10.2.4 使用通配符进行筛选 170

示例10-9 使用通配符在费用表中进行模糊筛选 170

10.2.5 高级筛选 171

示例10-10 在费用表中进行高级筛选 171

技巧10-7 将筛选结果复制到其他位置 172

10.3 数据的分级显示 172

10.3.1 手动建立分级显示 172

示例10-11 在销售记录表中手动建立分级显示 172

技巧10-8 取消创建的组 174

技巧10-9 在列方向创建组 174

10.3.2 自动生成分级显示 174

示例10-12 在销售记录表中自动生成分级显示 174

10.4 数据的分类汇总 175

10.4.1 简单的分类汇总 175

示例10-13 在费用表中进行简单的分类汇总 175

10.4.2 嵌套分类汇总 176

示例10-14 嵌套分类汇总费用表中的数据 176

技巧10-10 显示与隐藏分类汇总明细 177

10.4.3 删除分类汇总 178

示例10-15 删除费用表中的分类汇总 178

技巧10-11 分页显示分类汇总数据 178

10.5 数据的合并计算 179

10.5.1 按位置合并计算 179

示例10-16 按位置合并计算产品利润表中一季度产品利润 179

10.5.2 按分类合并计算 180

示例10-17 按分类合并计算产品利润表中的一季度产品利润 180

第11章 公式和函数的基础知识 182

11.1 初识公式和函数 183

11.1.1 公式中的运算符 183

1 算术运算符 183

2 比较运算符 183

3 文本运算符 184

4 引用运算符 184

11.1.2 运算符的优先级 184

11.1.3 函数的概念与结构 184

11.1.4 函数的种类和参数类型 185

11.2 单元格的引用方式 186

11.2.1 相对引用 186

示例11-1 使用相对引用计算成绩表中的平均分 186

11.2.2 绝对引用 187

示例11-2 使用绝对引用计算成绩表中的总评 187

11.2.3 混合引用 187

示例11-3 使用混合引用完善乘法表 188

11.3 输入与编辑公式 189

11.3.1 输入公式 189

示例11-4 在成绩表中输入公式计算总分 189

技巧11-1 修改公式 190

11.3.2 复制公式 190

示例11-5 将成绩表中含有公式的单元格复制到其他单元格中 190

技巧11-2 粘贴数值 191

11.3.3 设置公式的显示 191

示例11-6 将成绩表中的公式显示出来 191

技巧11-3 隐藏公式 192

技巧11-4 修改计算精度 192

11.4 使用“名称”简化公式 192

11.4.1 定义名称 192

1 使用名称框定义名称 193

示例11-7 使用名称框定义产品名称表中的产品数量 193

2 在“新建名称”对话框中定义名称 193

示例11-8 使用“新建名称”对话框定义产品名称表中的产品价格 193

3 根据所选内容快速创建名称 194

示例11-9 根据所选内容快速创建产品名称表中的名称 194

11.4.2 在公式中应用名称 195

示例11-10 在产品名称表的公式中应用名称 195

11.4.3 管理名称 196

示例11-11 使用名称管理器编辑、删除和筛选产品名称表中的名称 196

技巧11-5 定义表名称 198

11.5 函数的输入与应用 198

11.5.1 使用“自动求和”功能输入函数 198

示例11-12 使用“自动求和”功能计算总成绩表中的总分 198

11.5.2 使用函数库插入已知类别的函数 199

示例11-13 使用函数库在总成绩表中插入已知类别的函数 199

技巧11-6 搜索需要的函数 200

技巧11-7 在公式中使用记忆式键入功能 200

11.5.3 嵌套函数的应用 201

示例11-14 使用嵌套函数计算季度平均表中的平均销售额 201

11.5.4 分步求解嵌套函数 202

示例11-15 分步求解季度平均表中的嵌套函数 202

11.6 公式的审核 203

11.6.1 公式与函数运算的常见错误解析 203

技巧11-8 设置错误检查规则 203

技巧11-9 使用“错误检查”工具 204

11.6.2 用追踪箭头标识公式 204

示例11-16 使用追踪箭头标识成绩表中单元格的公式 204

技巧11-10 移去箭头 205

技巧11-11 快速定位所有包含公式的单元格 205

第12章 常用函数在办公中的应用 206

12.1 逻辑函数 207

12.1.1 IF函数 207

示例12-1 使用IF函数判断学生的平均分数等级 207

12.1.2 AND函数 208

示例12-2 使用AND函数判断单元格的参数逻辑值 208

12.1.3 NOT函数 208

示例12-3 使用NOT函数对参数值进行求反 209

12.1.4 OR函数 209

示例12-4 使用OR函数判断值的真假 210

12.2 日期和时间函数 210

12.2.1 DATE函数 210

示例12-5 使用DATE数计算任务完成时间 210

12.2.2 NOW函数 211

示例12-6 使用NOW函数计算销售表的当前制表时间 211

12.2.3 YEAR函数 211

示例12-7 使用YEAR函数计算任务开始年份 212

12.2.4 DAYS360函数 212

示例12-8 使用DAYS360计算任务完成所需天数 212

12.2.5 WEEKDAY函数 213

示例12-9 使用WEEKDAY函数求日前所代表的星期数 213

12.3 查找与引用函数 214

12.3.1 LOOKUP函数 214

1 向量形式 214

示例12-10 使用LOOKUP函数的向量形式查找产品数量和销售额 214

2 数组形式 216

示例12-11 使用LOOKUP函数的数组形式查找产品销售额 216

12.3.2 VLOOKUP函数 217

示例12-12 使用VLOOKUP数根据员工编号显示员工姓名 217

12.3.3 HLOOKUP函数 217

示例12-13 使用HLOOKUP函数引用相对区域相对年的销售利润 218

12.3.4 INDEX和MATCH函数 218

示例12-14 使用INDEX和MATCH的嵌套函数查找积分数可兑换的物品 219

12.3.5 ROW和COLUMN函数 219

示例12-15 使用ROW和COLUMN函数计算单元格所对应的行号与列标 220

12.3.6 OFFSET函数 220

示例12-16 使用OFFSET函数返回指定单元格中的内容 221

12.4 文本函数 221

12.4.1 FIND函数 221

示例12-17 在工作表中查找文本值中的另一个文本值的位置 222

12.4.2 MID函数 222

示例12-18 使用MID函数在文本中查找指定字符 222

12.4.3 TEXT函数 223

示例12-19 使用TEXT函数将日期显示为文本 223

12.4.4 SEARCH函数 224

示例12-20 使用SEARCH函数查找文本中的文本值 224

12.5 数学和三角函数 225

12.5.1 SUMIF函数 225

示例12-21 使用SUMIF函数求销售部的总费用金额 225

12.5.2 SUMPRODUCT函数 225

示例12-22 利用SUMPRODUCT函数计算商品总额 225

12.5.3 ROUND函数 226

示例12-23 使用ROUND函数返回工作表中数字四舍五入后的数字 226

12.5.4 RAND函数 227

示例12-24 使用RAND函数返回单元格中的随机数 227

12.6 统计函数 228

12.6.1 MAX和MIN函数 228

示例12-25 使用MAX和MN函数求最大值与最小值 228

12.6.2 COUNT和COUNTIF函数 228

示例12-26 使用COUNT和COUNTIF函数求学生人数和满足指定要求的人数 229

12.6.3 AVERAGE函数 229

示例12-27 使用AVERAGE函数求平均成绩 229

12.6.4 LARGE和SMALL函数 230

示例12-28 使用LARGE/SMALL函数求第k个最大值和最小值 230

12.7 财务函数 230

12.7.1 PMT函数 230

示例12-29 利用PMT函数计算贷款偿还金额 231

12.7.2 PV和FV函数 231

示例12-30 使用PV和FV函数计算未来值与现值 232

12.7.3 NPV函数 232

示例12-31 使用NPV函数计算投资净现值 233

12.7.4 SLN函数 233

示例12-32 使用SLN函数来运用线性折旧法求折旧费 233

12.7.5 DB函数 234

示例12-33 使用DB函数利用固定余额递减法计算固定资产折旧值 234

第13章 使用迷你图分析数据趋势 235

13.1 创建迷你图 236

13.1.1 创建单个迷你图 236

示例13-1 在表中创建单个折线迷你图 236

13.1.2 创建迷你图组 237

示例13-2 在工作表中创建迷你图组 237

13.2 编辑迷你图 238

13.2.1 更改迷你图类型 238

示例13-3 更改表中的迷你图类型 238

13.2.2 编辑组位置和数据 239

示例13-4 编辑迷你图组的位置和数据 239

13.2.3 编辑单个迷你图的数据 239

示例13-5 编辑单个迷你图的数据 240

13.2.4 切换行或列 240

示例13-6 在迷你图中切换行／列 240

技巧13-1 隐藏和空单元格设置 241

13.3 设置迷你图的显示属性 241

13.3.1 添加迷你图显示点 241

示例13-7 为表中的迷你图添加显示点 241

13.3.2 更改显示点的颜色 242

示例13-8 更改表中迷你图显示点的颜色 242

13.4 设置迷你图的样式 242

13.4.1 套用迷你图样式 242

示例13-9 为表中的迷你图套用样式 243

13.4.2 更改迷你图颜色和线条粗细 243

示例13-10 更改表中迷你图的颜色和线条粗细 243

13.5 清除迷你图 244

13.5.1 清除单个迷你图 244

示例13-11 清除工作表中的单个迷你图 244

13.5.2 清除迷你图组 245

示例13-12 清除工作表中的迷你图组 245

技巧13-2 组合与取消组合迷你图 245

第14章 创建基本图表直观展示数据 246

14.1 常用基本图表的创建 247

14.1.1 纵向比较数据的柱形图 247

示例14-1 创建簇状柱形图纵向比较不同年份的销售量 247

14.1.2 分析数据趋势的折线图 248

示例14-2 创建折线图分析每年人口增长率的变化趋势 248

14.1.3 显示整体比例的饼图 249

示例14-3 创建三维饼图显示各项费用金额和总金额的比例关系 249

14.1.4 横向比较的条形图 250

示例14-4 创建簇状条形图横向比较各个部门的人数情况 250

14.1.5 强调变化幅度的面积图 251

示例14-5 创建三维面积图强调不同产品销售数量的变化情况 251

14.1.6 显示值集关系的散点图 252

示例14-6 创建带直线和数据标记的散点图显示产品单价与销量的关系 252

14.1.7 比较多个类别的雷达图 253

示例14-7 插入带数据标记的雷达图比较各科的成绩情况 253

14.1.8 可自定义的组合图 253

示例14-8 创建簇状柱形图-折线图比较不同年份下不同费用的情况 254

14.2 编辑图表数据 254

14.2.1 更改图表的位置 254

示例14-9 将柱形图放置到新的工作表中 255

14.2.2 更改图表的大小 255

示例14-10 更改饼图的大小 256

技巧14-1 使用功能组更改图表大小 256

14.2.3 更改图表的类型 257

示例14-11 将工作表中的面积图更改为带数据标记的折线图 257

技巧14-2 更改系列图表类型 257

14.2.4 更改图表的数据源 258

示例14-12 更改柱形图中的数据区域 258

14.2.5 更改图表布局和样式 258

示例14-13 更改图表的布局和样式 259

技巧14-3 在图表中快速切换行列 259

技巧14-4 鼠标悬停时不显示数据点的值 260

14.3 编辑图表元素 260

14.3.1 编辑图表标题 260

示例14-14 编辑条形图的图表标题位置和填充效果 260

技巧14-5 链接图表标题 261

14.3.2 设置图例 261

示例14-15 设置条形图中的图例位置 261

技巧14-6 将图表另存为图片 262

技巧14-7 图表背景设置 263

14.3.3 编辑数据标签 263

示例14-16 编辑饼图中的数据标签显示位置和数据标签格式 263

技巧14-8 设置网格线 264

技巧14-9 快速清除图表的自定义格式 264

14.3.4 设置数据系列格式 264

示例14-17 设置柱形图中数据系列格式 264

技巧14-10 快速更改数据系列填充颜色 265

14.4 添加辅助线进行分析和预测 266

14.4.1 为图表添加并设置误差线 266

示例14-18 在柱形图中添加并设置误差线 266

14.4.2 为图表添加并设置趋势线 267

示例14-19 为折线图添加并设置趋势线 267

技巧14-11 取消趋势线 268

第15章 创建高级图表更专业地分析数据 269

15.1 显示项目进度的甘特图 270

15.1.1 甘特图的类型 270

1 标准甘特图 270

2 带有多个项目的甘特图 270

3 使用函数和条件格式创建的甘特图 270

15.1.2 巧妙利用条形图创建甘特图 270

示例15-1 利用条形图创建的甘特图分析项目进度情况 270

15.2 表现数据阶梯型递减的漏斗图 272

示例15-2 创建漏斗图表现每个环节的人数情况 272

15.3 反映数据增减变动的瀑布图 275

15.3.1 简单瀑布图 275

示例15-3 创建简单的瀑布图反映不同产品销量的变动情况 275

15.3.2 负值瀑布图 277

示例15-4 创建带负值瀑布图反映员工的实发工资情况 277

15.3.3 用涨跌柱线创建瀑布图 279

示例15-5 创建涨跌瀑布图表分析收入影响因素 279

15.4 使用散点图创建显示阶梯变动趋势的阶梯图 282

示例15-6 创建阶梯图反映每年的利润额变化趋势 282

15.5 用于寻找关键点的柏拉图 285

15.5.1 创建从0开始的柏拉图 285

示例15-7 创建从0开始的柏拉图分析产品产生问题的原因 285

15.5.2 创建不从0开始的柏拉图 290

示例15-8 创建不从0开始的柏拉图分析产品产生问题的原因 291

15.6 处理数值差距较大的断层图 294

15.6.1 多个图表创建断层图 294

示例15-9 使用多个柱形图创建断层图 294

15.6.2 模拟Y轴法创建断层图 297

示例15-10 使用模拟Y轴法创建断层图分析不同月份的销售收入 297

15.7 创建分析任务完成进度的温度计图表 301

示例15-11 创建温度计图表分析任务的完成情况 301

15.8 交互性强的动态图表 304

15.8.1 利用数据验证和函数创建动态图表 305

示例15-12 使用辅助区域建立动态图表分析不同年份的不同地区销售额 305

15.8.2 利用定义名称和函数创建动态图表 307

示例15-13 利用定义名称和函数创建动态图表分析不同月份的销售额 307

15.8.3 利用窗体控件创建动态图表 309

示例15-14 利用窗体控件创建的动态图表分析不同年级不同班级的人数情况 309

第16章 使用数据透视表全面分析数据 312

16.1 创建数据透视表 313

示例16-1 基于工作簿中的数据创建数据透视表 313

技巧16-1 导入外部数据创建数据透视表 314

技巧16-2 将数据透视表放置在现有工作表中 314

16.2 设置数据透视表的字段 314

16.2.1 更改数据透视表字段列表视图 314

示例16-2 更改数据透视表中的字段列表视图 314

16.2.2 向数据透视表添加字段 315

示例16-3 向数据透视表添加合适的字段 315

技巧16-3 使用鼠标右键添加 316

16.2.3 在数据透视表中移动字段 316

示例16-4 使用菜单命令在数据透视表中移动字段 316

技巧16-4 调整字段顺序 317

技巧16-5 删除字段 317

16.2.4 字段设置 317

示例16-5 通过菜单命令设置数据透视表中的字段 317

技巧16-6 通过功能区设置字段 318

16.3 编辑数据透视表 318

16.3.1 选择数据透视表 318

示例16-6 选择工作表中的整个数据透视表 318

技巧16-7 选择数据透视表的值或标签区域 319

16.3.2 移动数据透视表 319

示例16-7 移动数据透视表到现有工作表中 319

16.3.3 重命名数据透视表 320

示例16-8 重命名工作表中的数据透视表名称 320

技巧16-8 快速更新数据透视表 320

16.3.4 更改数据透视表的数据源 320

示例16-9 更改数据透视表的数据源区域 321

技巧16-9 显示和隐藏数据透视表中的明细数据 321

技巧16-10 清除数据透视表 322

16.4 设置数据透视表的布局和格式 322

16.4.1 改变数据透视表的整体布局 322

示例16-10 改变数据透视表的整体布局 322

技巧16-11 在项目后插入或删除空行 323

16.4.2 应用数据透视表样式 323

示例16-11 为数据透视表应用样式 323

16.5 在数据透视表中分析与处理数据 323

16.5.1 在数据透视表中排序 323

示例16-12 对数据透视表中的值进行升序排序 324

16.5.2 在数据透视表中筛选 324

示例16-13 在数据透视表中筛选费用字段 324

16.5.3 在数据透视表中插入切片器筛选数据 325

示例16-14 在数据透视表中插入切片器以筛选数据 325

技巧16-12 设置按钮行列及大小 326

技巧16-13 为切片器应用样式 326

16.6 数据透视图 326

16.6.1 根据数据透视表创建数据透视图 327

示例16-15 根据数据透视表创建数据透视图 327

16.6.2 根据源数据创建数据透视图 327

示例16-16 根据源数据创建数据透视图 327

16.6.3 使用字段按钮在数据透视图中筛选 329

示例16-17 使用字段按钮在数据透视图中筛选字段 329

技巧16-14 隐藏数据透视图中的字段按钮 330

第17章 模拟分析和规划求解 331

17.1 方案管理器 332

17.1.1 创建方案 332

示例17-1 在利润预测表中创建方案 332

技巧17-1 删除方案 334

17.1.2 显示方案 335

示例17-2 显示利润预测表中的方案三 335

技巧17-2 重新编辑方案 335

技巧17-3 合并方案 336

17.1.3 创建方案摘要 336

示例17-3 创建利润预测表的方案摘要 336

技巧17-4 创建方案数据透视表 337

17.2 单变量求解 337

示例17-4 利用单变量求解期望工资表中的期望工资 337

17.3 模拟运算表 338

17.3.1 单变量模拟运算表 338

示例17-5 建立销售业绩表的单变量模拟运算表 338

17.3.2 双变量模拟运算表 339

示例17-6 建立折旧表的双变量模拟运算表 339

技巧17-5 不重新计算模拟运算 341

技巧17-6 将模拟运算结果转换为常量 341

17.4 规划求解 341

17.4.1 加载规划求解 342

示例17-7 在空白工作簿中加载规划求解 342

17.4.2 建立规划求解模型 343

示例17-8 在产品最大利润表中建立规划求解模型 343

17.4.3 规划求解 344

示例17-9 规划求解产品最大利润表中的最大总利润 344

技巧17-7 快速清除规划求解中的所有参数 345

技巧17-8 忽略规划求解中的整数约束 345

17.4.4 生成规划求解结果报告 346

示例17-10 生成产品最大利润表的规划求解结果报告 346

第18章 使用数据分析工具库分析复杂数据 347

18.1 加载分析工具库 348

示例18-1 在工作簿中加载分析工具库 348

18.2 方差分析 349

18.2.1 单因素方差分析 349

示例18-2 使用单因素方差分析地区销售表中不同地区的销售额情况 349

18.2.2 双因素方差分析 350

1 可重复双因素分析 350

示例18-3 不同设备生产不同产品的可重复双因素分析 350

2 无重复双因素分析 351

示例18-4 不同产品和销售地区对销售量影响的无重复双因素分析 351

18.3 统计分析 352

18.3.1 描述统计 352

示例18-5 描述统计成绩表中的数据 352

18.3.2 直方图 353

示例18-6 创建直方图分析员工工资分布 354

18.3.3 假设检验 356

1 t-检验 356

示例18-7 使用t-检验检验产品的标准性 356

2 F-检验：双样本方差 357

示例18-8 使用F-检验分析不同部门的工作量 357

3 z-检验：双样本平均差检验 358

示例18-9 使用z-检验法分析产品生产情况是否符合设计标准 358

18.4 预测分析 360

18.4.1 时间序列预测 360

1 移动平均 360

示例18-10 利用移动平均法预测来年员工增长率 360

2 指数平滑 361

示例18-11 利用指数平滑法预测销售额 361

18.4.2 回归分析预测 363

1 相关系数 363

示例18-12 费用与销售业绩的相关系数分析 363

2 回归 364

示例18-13 利用回归分析产品的满意度 364

第19章 宏与VBA数据自动化处理 366

19.1 认识宏和VBA 367

19.1.1 认识VBA宏 367

示例19-1 认识宏的效果 367

19.1.2 认识VBA开发环境 368

1 VBA开发环境 368

示例19-2 进入VBA的开发环境 368

技巧19-1 用快捷键快速进入VBA环境 369

2 VBA开发环境的主要菜单栏及其功能 369

3 获取帮助 371

19.2 宏的基本操作 372

19.2.1 录制和保存宏 372

1 录制宏 372

2 保存宏 372

示例19-3 录制一个宏，将选中单元格的字体颜色变为红色加粗 372

技巧19-2 在“开发工具”选项卡下录制宏 373

19.2.2 查看和执行宏 373

示例19-4 执行录制好的宏 374

技巧19-3 利用快捷键执行宏命令 374

19.2.3 编辑宏 374

示例19-5 编辑“红色加粗”宏中的字体大小 375

19.3 VBA程序设计基础 376

19.3.1 VBA的语法基础 376

1 VBA字符集和标识符 376

示例19-6 查看系统保留的关键字 376

2 VBA中的常量和变量 377

示例19-7 启用自动语法检测功能 378

3 VBA的数据类型 379

4 VBA的数组类型 379

19.3.2 VBA运算符与表达式 380

1 算术运算符 380

2 比较运算符 380

3 逻辑运算符 381

4 运算符的优先级 381

19.3.3 变量的使用 382

1 变量的声明 382

2 变量的生存周期 382

19.4 VBA的代码结构 383

19.4.1 VBA流程控制 383

1 顺序结构 383

示例19-8 给定义的变量赋值，并打印在“立即窗口”中 383

2 循环结构 384

3 选择结构 386

示例19-9 根据不同的售价，用不同的字体格式标识“好好吃蛋糕店”的货物 388

19.4.2 VBA函数的使用 389

1 定义VBA过程 389

2 VBA函数参数传递 390

示例19-10 显示员工信息 390

3 常用内部函数简介 391

19.4.3 VBA中的对象、方法和属性 392

1 VBA对象的概念 393

2 对象的属性和方法 393

示例19-11 新建一个工作表，对新建的工作表操作，确定是否要删除 394

第20章 VBA在Excel中的数据应用 395

20.1 Range对象操作单元格 396

20.1.1 实现单元格的选择和查找 396

示例20-1 在工资表中查找基本工资为1500的职工，并将其基本工资调整为1800 396

20.1.2 实现单元格的复制、剪切和插入 398

1 实现复制功能的Copy方法 398

示例20-2 复制单元格 398

2 实现剪切功能的Cut方法 399

示例20-3 将单元格区域A1:C4中的内容剪切到单元格区域D1:F4中 399

3 实现插入功能的Insert方法 399

示例20-4 在工作表中插入3列和5行 399

技巧20-1 插入一个单元格 401

20.1.3 实现单元格的替换、清除和删除 401

1 实现替换功能的Replace方法 401

示例20-5 将大写字母替换成小写字母 401

2 实现清除功能的Clear方法 402

示例20-6 清除单元格中的内容 402

3 实现删除功能的Delete方法 402

示例20-7 删除两行单元格 403

20.1.4 实现在选定的单元格内的运算 403

示例20-8 统计员工工资 403

20.2 Application对象、Worksheets集合控制工作表 404

20.2.1 Application对象 404

示例20-9 允许用户修改Excel标题栏名称 405

20.2.2 Workbook对象和Workbooks集合 406

示例20-10 新建一个工作簿，根据用户的需要设定工作簿名称和密码 407

20.2.3 Worksheet对象和Worksheets集合 408

1 新建工作表 408

2 保存和重命名工作表 408

技巧20-2 批量重命名工作表 408

3 复制和删除工作表 409

技巧20-3 隐藏工作表 409

示例20-11 复制员工信息登记表，并根据员工姓名重命名工作表 409

第21章 通过控件和窗体创建数据的交互式任务 412

21.1 工作表界面设计 413

21.1.1 表单控件和ActiveX控件 413

技巧21-1 显示简单的消息框 414

21.1.2 控件的类型 414

示例21-1 设计最简单的调查问卷 414

21.1.3 设置控件属性 416

1 按钮控件 416

2 标签控件 416

3 选项按钮控件 416

4 复选框控件 416

5 列表框控件 417

6 组合框控件 417

7 数值调节钮控件 417

示例21-2 为按钮指定宏 417

技巧21-2 为图片指定宏 419

21.1.4 产品满意度调查表 419

示例21-3 产品满意度调查问卷 419

2 1.2 用户窗体设计 421

21.2.1 用户窗体的属性 421

1 Caption属性和名称属性 422

2 BackColor和BorderColor属性 422

3 背景图片设置 422

4 设置窗体大小 422

示例21-4 创建一个用户窗体并运行 422

21.2.2 向窗体添加控件 423

1 标签控件 424

2 文本框控件 424

3 选项按钮控件 424

4 复选框控件 424

5 列表框控件 424

6 复合框控件 424

7 命令按钮控件 424

8 多页控件 424

9 滚动条控件 425

10 框架控件 425

技巧21-3 使用代码在工作表中添加控件 425

21.2.3 友好界面设计 425

1 用户窗体的整体格局 425

2 控件的显示 425

3 默认控件次序 426

示例21-5 设置日期提醒界面 426

第22章 数据的网络协作与共享 429

22.1 在局域网上共享工作簿 430

22.1.1 设置共享工作簿 430

示例22-1 将销售数据工作簿共享到局域网上 430

技巧22-1 取消共享工作簿 431

22.1.2 多人协同编辑工作簿并显示修订 431

示例22-2 编辑工作簿并显示修订 431

技巧22-2 为特殊信息添加批注 432

22.1.3 接受或拒绝修订 433

示例22-3 接受其他用户做的修订 433

技巧22-3 拒绝修订 433

22.2 在Internet上共享工作簿 433

22.2.1 将数据保存到Web网页中 434

示例22-4 将数据共享到Web网页中 434

22.2.2 使用电子邮件发送Excel工作簿 434

示例22-5 将工作簿以邮件方式发送给指定的人 435

22.3 与其他应用程序共享工作簿 435

22.3.1 与Word间共享数据 435

示例22-6 将Word中的数据导入Excel中 435

技巧22-4 将Word文档链接到文件 436

技巧22-5 直接在Excel中创建文档 437

22.3.2 共享来自文本文件的数据 437

示例22-7 将文本文件中的产品销售数据导入Excel中 437

22.3.3 共享来自Access的数据 438

示例22-8 将Access数据库中的客户资料导入Excel中 438

技巧22-6 与PowerPoint共享数据 439

22.3.4 共享来自网站的数据 439

示例22-9 将网站中的汽车销量数据导入Excel中 440

第23章 页面布局与打印输出数据 441

23.1 打印前的页面布局设置 442

23.1.1 调整纸张大小和方向 442

示例23-1 设置商品销售记录表的页面大小和方向 442

技巧23-1 在对话框中设置页面大小和方向 443

23.1.2 设置页边距 443

示例23-2 设置员工加班记录表的页边距 443

技巧23-2 精确设置页边距的值 443

技巧23-3 快速启动“页面设置”对话框 444

23.1.3 添加页眉和页脚 444

1 添加预设页眉页脚 444

示例23-3 为工作表设置系统预设的页眉页脚 444

技巧23-4 设置奇偶页或首页不同的页眉页脚 445

2 自定义页眉页脚 445

示例23-4 为会议议程安排表定义页眉页脚 445

技巧23-5 在页眉页脚中插入图片 446

技巧23-6 在“插入”选项卡下插入页眉页脚 446

23.1.4 设置打印区域 447

示例23-5 打印业绩排名为前十名的员工信息 447

技巧23-7 设置打印顺序 447

23.2 插入分页符 448

示例23-6 在工作表中插入分页符 448

技巧23-8 调整分页符位置 449

23.3 预览打印效果 449

23.3.1 打印预览 449

示例23-7 预览商品销售数据表 449

23.3.2 在预览模式下调整页面 450

示例23-8 重设招聘信息表的页面设置 450

技巧23-9 设置打印的起始页码 451

技巧23-10 设置错误值的打印形式 451

23.4 开始打印工作表 451

23.4.1 设置打印份数 451

示例23-9 打印5份招聘信息表 451

技巧23-11 调整打印的页数范围 452

23.4.2 以报表形式打印工作表 452

示例23-10 打印有相同标题的多页工作表 452