Excel电子表格制作与应用实例大全PDF电子书下载

第1章　Excel 2007操作基础 1

1.1　熟悉Excel 2007的环境 1

1.1.1 Excel窗口基本组成 1

1.1.2　“文件”菜单 1

1.1.3　“快速访问”工具栏 1

1．快速工具栏 1

2．扩展工具栏 2

1.1.4　标题栏 2

1.1.5　功能区 2

1．“开始”选项卡 2

2．“插入”选项卡 2

3．“页面布局”选项卡 3

4．“公式”选项卡 3

5．“数据”选项卡 3

6．“审阅”选项卡 3

7．“视图”选项卡 3

1.1.6　状态栏与显示模式 3

1．状态栏 3

2．页面显示 3

1.1.7　其他组件 4

1．编辑栏 4

2．工作区 4

3．标签栏 4

4．滚动条 4

5．显示比例 4

1.2 Excel 2007的基本操作 4

1.2.1　创建Excel工作簿 5

1.2.2　保存Excel工作簿 5

1.2.3　打开Excel工作簿 6

1.2.4　关闭工作簿与退出Excel 6

1.3　打印工作表 6

1.3.1　设置页边距 7

1.3.2　设置纸张方向 7

1.3.3　设置纸张大小 8

1.3.4　设置打印区域 8

1.3.5　设置分页符 8

1.3.6　设置打印标题 9

1.3.7　打印设置 10

1．“打印机”选项区域 10

2．“打印范围”选项区域 10

3．“打印内容”选项区域 10

4．“份数”选项区域 10

第2章　输入、编辑与整理数据 11

2.1　设置数值格式 11

2.1.1　表格中可以使用的数字格式 11

2.1.2　创建或删除自定义数字格式 12

1．创建自定义数字格式 12

2．删除自定义数字格式 12

2.1.3　自定义数字格式的准则 12

1．包含文本和添加空格的准则 13

2．使用小数位／空格／颜色／条件的准则 13

3．货币／百分比／科学记数法的准则 14

4．有关日期和时间格式的准则 14

2.1.4　设置数据舍入精度 15

2.1.5　以百分比的形式显示数字 16

2.1.6　以真正的分数形式显示数字 16

2.1.7　完整地显示16位数字 16

2.1.8　正确显示货币格式的数字 17

2.1.9　正确显示日期和时间 17

2.1.10　转换数字格式 17

1．数据一个单元格或相邻单元格的转换 18

2．多个不相邻单元格的转换 18

2.1.11　转换日期格式 18

1．使用“错误检查”转换 18

2．使用DATEVALUE函数转换 18

2.1.12　显示与隐藏千位分隔符 19

2.2　选定单元格 20

2.2.1　选择一个单元格 20

2.2.2　选择单元格区域 20

1．拖拉法 20

2．“Shift”键法 20

3．“F8”键 20

4．名称框法 20

5．定位法 21

2.2.3　选择不相邻的单元格 21

1．“Ctrl”键法 21

2．“Shift+F8”键法 21

2.2.4　选择整个工作表 21

2.2.5　选择整行 21

2.2.6　选择整列 22

2.2.7　选中不同工作表的同一单元格 23

2.3　输入文本数据 23

2.4　输入数字数据 24

2.4.1　多位数数字的输入 24

2.4.2　输入分数 25

2.4.3　输入负数 25

2.4.4　输入小数 26

2.4.5　输入货币值 27

2.4.6　输入日期 28

2.4.7　输入时间 28

2.5　指定输入数据类型 29

2.6　删除和更改数据 29

2.6.1　删除单元格中的数据 29

2.6.2　更改单元格中的数据 30

2.7　复制与移动数据 30

2.7.1　使用菜单命令复制与移动数据 30

2.7.2　使用拖动法复制与移动数据 31

2.8　自动填充 32

2.8.1　在同一行或列中填充数据 32

2.8.2　填充大量相同的数据 33

2.8.3　手动控制创建序列 34

2.9　使用批注 36

2.9.1　添加批注 36

2.9.2　删除批注 37

2.9.3　设置批注格式 37

2.10　使用Office剪贴板 38

1．打开剪贴板 38

2．控制剪贴板的显示方式 38

3．将多个项目复制到剪贴板 39

4．粘贴项目 39

5．删除项目 39

第3章　编辑工作表 40

3.1 撤消与恢复操作 40

1．撤消操作 40

2．撤消多步操作 40

3．恢复操作 40

4．恢复多步操作 40

3.2 复制和移动单元格 40

3.2.1 复制单元格数据 40

1．复制单元格 41

2．使用“拖放”操作复制单元格 41

3．用拖放功能完成相同数据的复制 42

3.2.2 有选择地复制单元格数据 42

3.2.3 移动单元格数据 43

3.3 插入／删除单元格、行和列 44

3.3.1 插入或删除单元格 44

1．插入单元格 44

2．删除单元格 44

3.3.2 插入或删除行、列 45

1．插入列 45

2．插入行 45

3．删除行、列 45

3.4 查找与替换操作 45

3.4.1 查找命令 45

3.4.2 替换命令 46

3.5 工作表的格式编排 46

3.5.1 改变行高 46

1．使用行高命令 46

2．使用鼠标调整行高 47

3．调整列宽 47

4．使用鼠标调整列宽 47

3.5.2 取消网格线 48

3.5.3 改变单元格区域的颜色 48

3.5.4 为表格增加边框 48

3.5.5 改变字体、颜色及排列方式 50

1．选择“字体”选项卡 50

2．改变字体 50

3．改变字体的大小 51

4．改变字符 51

5．字符修饰 51

3.5.6 标题居中 51

3.5.7 对齐工具的使用 52

1．水平对齐格式 52

2．垂直对齐 52

3．任意角度文字的设定 53

3.5.8 数字显示格式的设定 53

1．改变数字格式 54

2．“####”符号的意义 54

3．颜色的使用 54

4．设置单元格的条件格式 55

3.5.9 日期格式的设定 55

3.5.10 复制格式 56

3.5.11 自动套用表格格式 56

3.5.12 建立模板 57

3.6 名称的使用 57

3.6.1 使用名称的优点 57

3.6.2 名称适用的范围 58

3.6.3 定义特殊名称 58

3.6.4 建立一个名称 58

3.6.5 自动命名 59

3.6.6 名称的修改和删除 60

3.6.7 利用名称建立中文公式 60

第4章　整理工作表与工作簿 62

4.1 插入工作表 62

4.1.1 插入单张工作表 62

1．快捷按钮法 62

2．菜单法 62

3．右键法 62

4．快捷键法 63

4.1.2 一次性插入多张工作表 63

4.2 删除工作表 63

4.2.1 右键删除法 63

4.2.2 菜单删除法 63

4.3 重命名工作表 64

4.4 移动或复制工作表 64

4.4.1 在工作簿内移动工作表 64

1．菜单法 64

2．右键法 65

3．拖拉法 65

4.4.1 在工作簿内复制工作表 65

1．菜单法 65

2．右键法 66

3．拖拉法 66

4.4.3 在工作簿间移动或复制工作表 66

1．移动工作表 66

2．复制工作表 67

4.5 查看工作簿窗口 67

4.5.1 工作簿视图 67

1．普通视图 68

2．“分页预览”视图 68

3．“页面布局”视图 68

4.5.2 同时显示多个工作簿 68

4.5.3 并排查看工作表 69

1．同一个工作簿中工作表的并排 69

2．不同工作簿中工作表的并排 70

4.5.4　拆分与冻结窗口 71

1．水平拆分窗口 71

2．垂直拆分窗口 71

3．冻结窗格 72

4.6　隐藏或显示工作簿的元素 73

4.6.1　隐藏或显示工作表 73

1．隐藏工作表 73

2．显示工作表 73

4.6.2　隐藏或显示窗口元素 74

4.6.3　设置工作表显示的比例 74

4.7　保护工作簿数据 75

4.7.1　保护工作表 75

4.7.2　保护工作簿 76

4.7.3　设置允许用户编辑区域 77

1．只锁定工作表中的特定单元格和区域 77

2．设置允许用户编辑区域 78

第5章　公式、数组和函数使用基础 80

5.1　公式应用基础 80

5.1.1　了解Excel公式 80

1．什么是公式 80

2．公式由哪些元素组成 80

5.1.2　公式的作用 80

5.1.3　公式的简单应用实例 81

1．计算商品金额数值 81

2．计算销售额总和 81

3．用公式合并字符 82

4．用函数合并字符 82

5.1.4　输入公式 82

5.1.5　公式的显示 83

5.1.6　公式的移动和复制 83

1．复制单元格对相对地址引用的影响 83

2．复制单元格对绝对引用位置的影响 84

5.1.7 控制重算的方式 84

5.1.8 使用自动求和按钮 84

1．自动求和按钮输入求和公式 84

2．对行或列相邻单元格的求和 84

3．进行合计运算 85

4．对多个选定区域求和 85

5.1.9 隐藏公式 85

5.2 公式的引用 86

5.2.1 相对引用 86

5.2.2 绝对引用 87

5.2.3 混合引用 87

5.3　数组公式 88

1．数组概念 88

2．数组与数组公式 88

3．什么情况下会返回一个集合 88

4．什么情况下会用到相关公式 89

5．如何复制公式 89

5.4 函数应用基础 89

5.4.1 初识函数 89

1．什么是嵌套函数 89

2．函数的结构 90

3．公式选项板 90

5.4.2 函数的种类 90

1．数据库函数 90

2．日期与时间函数 90

3．工程函数 90

4．财务函数 90

5．信息函数 91

6．逻辑函数 91

7．查询和引用函数 91

8．数学和三角函数 91

9．统计函数 91

10．文本函数 91

11．用户自定义函数 91

5.4.3 函数的参数 91

1．常量 91

2．逻辑值 92

3．数组 92

4．错误值 92

5．单元格引用 92

6．嵌套函数 93

7．名称和标志 93

5.4.4　函数输入方法 93

1．“插入函数”对话框 93

2．编辑栏输入 94

5.5　函数在数据处理的中的作用 94

5.5.1　简化公式 94

5.5.2　实现特殊运算 95

5.5.3　实现智能判断 96

5.5.4　提高工作效率 97

5.6　公式和函数中的运算符 98

5.6.1　运算符的类型 98

5.6.2　运算符的优先级 98

5.6.3　嵌套括号 99

5.7　纠正公式与函数中的错误 99

5.7.1　设置错误检查规则 99

5.7.2　追踪单元格纠正错误 100

1．在同一工作表中追踪单元格 100

2．在不同工作表中追踪单元格 100

5.7.3 更正#错误 101

1．列宽不足以显示内容 101

2．日期和时间为负数 101

5.7.4 更正#DIV/0！错误 101

1．公式中包含除以零（0）的计算 101

2．使用包含零作为除数的单元格的引用 101

3．宏中使用了返回#DIV/0 ！的函数或公式 102

5.7.5 更正#N/A错误 102

1．缺少数据 102

2．为工作表函数的参数赋予了不正确的值 102

3．在未排序的表中使用函数来查找值 102

4．行数或列数不一致 103

5．工作表函数中省略了必需参数 103

6．自定义工作表函数不可用 103

7．宏程序所输入的函数返回#N/A 103

5.7.6 更正#NAME？错误 104

1．使用EUROCONVERT函数 104

2．安装和加载“欧元转换工具” 104

3．使用不存在的名称 104

4．名称拼写错误 104

5．函数名称拼写错误 104

6．在公式中没有用双引号 104

7．区域引用中漏掉了冒号 104

8．引用的另一张工作表未使用单引号 104

5.7.7　更正#NULL！错误 105

1．使用了不正确的区域运算符 105

2．区域不相交 105

3．更改对非命名区域的引用 106

4．更改对命名区域的引用 106

5.7.8　更正#NUM！错误 106

1．需要数字参数的函数 106

2．迭代的工作表函数 106

5.7.9　更正#REF！错误 107

1．删除其他公式所引用的单元格 107

2．使用的对象链接指向的程序未运行 107

3．链接到了不可用的动态数据交换 107

4．宏程序所输入的函数返回#REF！ 107

5.7.10　更正#VALUE！错误 108

1．错误输入文本 108

2．误按“Enter”键 108

3．将单元格引用作为数组常量输入 108

4．为需要单个值的运算符提供区域 108

5．在矩阵工作表中使用了无效的矩阵 108

6．运行的宏程序所输入的函数 108

第6章　图表制作应用大全 110

6.1　创建与编辑图表 110

6.1.1　创建员工销售业绩柱形图 110

6.1.2　编辑员工销售业绩图表 110

6.2　改变图表位置和大小 111

6.2.1　改变图表位置 111

6.2.2　改变图表大小 112

6.2.3　调整图例和坐标轴标题位置 112

6.3　设置图表中文字的格式 114

6.3.1　设置标题文字格式 114

6.3.2　设置图例的字体格式 114

6.3.3　设置坐标轴及其标题字体格式 114

6.4　设置绘图区格式 115

6.4.1　改变绘图区大小 115

6.4.2　美化坐标轴 115

6.4.3　为背景墙添加图片填充效果 118

6.4.4　设置网格线 119

6.5　设置图表和图例格式 120

6.5.1　为图表区填充纹理背景 120

6.5.2　设置图例边框与填充效果 121

6.6　在图表中添加删除数据 122

6.7　更改图表类型 123

6.8　制作和编辑折线图 123

6.9　常用图表的制作 127

6.9.1　用柱状图比较类别轴上的数值 127

1．比较不同项目的数据 127

2．比较同类别的数据 128

6.9.2　用条形图查看各项目的差距 128

6.9.3　用饼图查看各项数据的比例 129

6.9.4　用圆环图查看各项数据的比例 130

6.9.5　用折线图查年变化趋势 131

6.9.6　用XY散点图比较数据 132

6.10　图表应用实例 133

6.10.1　用气泡图反映数据大小 133

6.10.2　用面积图表示数据成分及比例 133

6.10.3　用曲面图显示数据变化情况 134

6.10.4　用雷达图显示数据类别的变化 135

6.10.5　用股价图描绘股票走势 135

6.11 图表制作技巧 136

6.11.1　在图表中创建下拉列表框 136

6.11.2　在图表中创建滚动条 138

6.11.3　用图片表示数据系列 141

6.11.4　绘制有数据间隔的图表 142

6.11.5　为图表添加趋势线 143

第7章　数据透视表和数据透视图 145

7.1　数据透视表和数据透视图概述 145

7.1.1　数据透视表 145

7.1.2　数据透视图 146

1．报表筛选字段 146

2．值字段 146

3．系列字段 146

4．项 146

5．分类字段 146

6．自定义报表 147

7.1.3　比较数据透视表和透视图 147

7.1.4　数据透视图与图表的区别 147

1．交互 147

2．图表类型 147

3．图表位置 147

4．源数据 147

5．图表元素 147

6．格式 147

7．移动或调整项的大小 148

7.1.5　选择要创建的数据 148

1．选择需要分析的数据 148

2．使用外部数据 148

3．创建数据透视图 149

7.1.6　在数据透视表中选择数据 149

1．选择单个单元格 149

2．选择整个报表 149

3．选择字段中的所有项目 149

4．选择项目标签和（或）数据 150

5．选择一个项目的所有实例 150

6．选择某个项目的一个实例 150

7．选择多个项目 150

8．选择分类汇总和汇总 150

7.2　添加、更改或清除条件格式 150

7.2.1　了解有关条件格式 151

7.2.2　管理条件格式规则的优先级 151

1．多个条件格式规则评估为真 151

2．粘贴、填充和格式刷 151

3．条件格式和手动格式冲突 151

4．编辑条件格式规则优先级 152

7.2.3　使用双色刻度设置单元格格式 152

1．快速格式化 152

2．高级格式化 152

7.2.4　条件格式应用实例 153

1．突出显示一定比例的学生 154

2．突出显示指定学校的学生成绩 154

7.3　数据透视表和透视图的制作 155

7.3.1　数据透视表的创建 155

7.3.2　数据透视表的应用 156

1．添加统计字段 156

2．统计销售员业绩 156

3．按销量进行排序 156

4．统计各类图书的销量 156

7.3.3　数据透视图的创建 157

7.4　创建销售业绩数据透视表实例 157

7.4.1　创建数据透视表 157

7.4.2　使用数据透视表 158

7.5　创建销售业绩数据透视图实例 158

7.5.1　创建数据透视图 158

7.5.2　使用数据透视图 159

第8章　管理与分析数据 161

8.1　了解数据清单 161

8.2　建立和使用数据清单 161

8.2.1　准备工作 161

1．选择字段 162

2．安排字段位置 162

3．命名字段 162

8.2.2　建立一个数据库 162

8.2.3　输入数据 162

1．没有设定列标题 162

2．增加记录后的数据库 163

3．直接插入记录到数据库 163

8.2.4　编辑记录 164

8.2.5　删除一条记录 164

8.3　数据排序 164

8.3.1　快速排序 165

8.3.2　多条件排序 165

8.3.3　按笔划排序 165

8.3.4　自定义排序 166

8.3.5　用函数进行排序 166

8.3.6　让序号不参与排序 167

8.3.7　按列排序 167

8.3.8　多列排序 167

8.3.9　使用工具排序 168

8.3.10　排序数据顺序的恢复 168

8.4　筛选数据 168

8.4.1　使用“自动筛选”来筛选数据 168

8.4.2　建立自定义“自动筛选” 169

1．“自定义”实现单条件筛选数据 169

2．“自定义”实现双条件筛选数据 170

8.4.3　使用高级筛选 170

8.4.4　设定条件区域 171

1．要显示符合所有条件的行 171

2．要显示符合特定条件的行 172

8.4.5　设定准则的注意事项 172

8.4.6　复制符合复杂条件的筛选数据 172

8.5　汇总数据 173

8.5.1　自动分类汇总 173

8.5.2　多列自动分类汇总 174

8.5.3　自动分类汇总的嵌套 174

8.5.4　移去所有自动分类汇总 175

8.6　单变量求解 175

8.6.1　在Excel中建立模型 175

8.6.2　模型求解 175

8.7　数据表 176

8.7.1　单输入数据表 176

8.7.2　双输入数据表 177

8.8　假设分析的使用方案 178

8.8.1　建立模型 178

8.8.2　建立方案 178

8.8.3　显示方案 179

8.8.4　建立摘要报告 179

8.9　数据分析实例 180

8.9.1　直方图分析 180

8.9.2　描述统计分析 181

8.9.3　排位与百分比排位 181

8.9.4　相关系数与协方差 182

8.9.5　抽样分析工具 183

8.9.6　移动平均分析 184

8.9.7　回归分析 185

第9章　Excel高级应用 187

9.1　使用模板功能 187

9.1.1　创建模板 187

9.1.2　应用模板 187

9.1.3　下载模板 188

9.1.4　修改模板 189

9.2 SmartArt图形应用 190

9.2.1　认识SmartArt图形 190

1．SmartArt图形概述 190

2．选择布局时要考虑的事项 190

9.2.2　“文本”窗格 191

1．“文本”窗格简介 191

2．“文本”窗格中项目符号含义 192

3．设置“文本”窗格中文字格式 192

4．键盘快捷方式 192

9.2.3　创建SmartArt图形 193

1．创建SmartArt图形 193

2．更改整个SmartArt图形的颜色 193

3．SmartArt样式的应用 193

9.2.4　添加或删除填充或效果 194

1．填充、渐变及三维效果概述 194

2．向SmartArt图形中的形状添加填充 194

3．向SmartArt图形中的文本添加填充 194

4．对SmartArt图形中的形状应用效果 195

5．对SmartArt图形中的文本应用效果 196

6．从SmartArt图形中的形状中删除填充 197

7．从SmartArt图形中的形状中删除效果 197

9.2.5　更改形状、边框或颜色 197

1．更改颜色概述 197

2．更改形状的颜色 198

3．更改形状边框的颜色 198

4．更改整个SmartArt图形的颜色 198

9.2.6　调整形状或图形的大小 199

1．调整SmartArt概述 199

2．调整关联形状的大小 200

3．调整一个形状的大小 200

4．调整整个SmartArt图形的大小 200

9.3　使用宏 200

9.3.1　录制宏前的准备工作 200

9.3.2　录制宏 201

9.3.3　执行宏 202

9.3.4　编辑宏 204

9.3.5　调试宏 204

第10章　Excel 2007经典案例应用 206

10.1　利用Excel 2007完成试卷分析 206

10.1.1　利用FREQUENCY函数 206

10.1.2　利用COUNTIF函数 208

10.1.3　利用SUMPRODUCT函数 209

10.2　创建题库与答题卷制作 209

10.2.1　创建题库 209

10.2.2　答题卷制作 210

10.2.3　在答卷中显示试题 210

10.2.4　答案的限制 210

10.2.5　验证考生准考证号 211

10.2.6　实现随机抽题 212

10.2.7　记录成绩 212

10.2.8　评分和记录保存 213

10.2.9　其他设置 213

10.2.10　试题输入 214

10.2.11　隐藏工作表 214

10.3　销售业绩统计分析 214

10.3.1　创建“销售业绩表” 215

10.3.2　制作“区域销售统计分析图表” 217

1．制作“华北区域销售统计分析图表” 217

2．制作“其他区域销售统计分析图表” 219

10.3.3　制作“所有区域销售统计图表” 219

10.3.4　制作“年度占有率分析图” 221

10.4　设计实用万年历 224

10.4.1　设计表格 224

10.4.2　修饰表格 226

10.5　计算比赛成绩 227

10.5.1　统计名次 227

10.5.2　自动颁发奖项 228

10.6　排除重复条目 228

10.6.1　表格的空格处理 228

10.6.2　清除重复项目 229

10.6.3　拒绝输入重复数据 229

10.7　计算下年度预算 230

10.7.1　设计表格 230

10.7.2　数据分析 230

10.8　企业资源分配优化决策 231