Excel高效管理表格设计从入门到精通 全新版PDF电子书下载

第一部分 绪论篇 2

Chapter 1 Excel高效管理表格的设计原则 2

1.1 什么是Excel高效管理表格 2

1．数据日常管理方便 2

2．数据计算汇总方便 4

3．数据统计分析方便 6

4．表格清晰，便于查看和分析数据 7

5．便于打印 8

1.2 表格的类型 8

1．报告型表格 8

2．清单型表格 9

1.3 设计表格的基本原则 9

1．报告型表格的设计原则 10

2．清单型表格的设计原则 10

1.4 输入数据的基本原则 11

1．通过导入的方法获取原始数据 11

2．使用公式和函数自动输入数据 11

3．处理文本型数字时要小心 11

4．利用有效性限制输入数据 12

5．同列数据必须为同一类型数据 12

6．尽量少用复制粘贴的方法输入数据 13

7．尽可能使用填充的方法输入固定的序列数据 13

1.5 美化修饰表格的基本原则 13

1．设置单元格的行高和列宽 14

2．设置单元格的数字格式 14

3．设置单元格的字体格式 15

4．设置单元格的对齐方式 16

5．设置单元格的框线 16

6．设置工作表背景和单元格填充效果 16

7．关于合并单元格 17

8．关于条件格式的使用 17

第二部分 基础与技能篇 20

Chapter 2 常用函数及其应用 20

2.1 逻辑判断函数 20

1．IF函数 20

2．AND函数 20

3．OR函数 21

4．IFERROR函数 21

5．逻辑判断函数的简单应用 21

2.2 信息函数 21

1．IS类函数 22

2．IS类函数的使用方法和案例 22

2.3 计数函数 22

1．COUNT函数 22

2．COUNTA函数 23

3．COUNTIF函数 23

4．COUNTIFS函数 23

5．计数函数的基本应用 24

2.4 日期和时间函数 25

1．TODAY函数 25

2．DATE函数 25

3．YEAR、MONTH、DAY函数 26

4．WEEKDAY函数 27

5．EDATE函数 28

6．EOMONTH函数 28

7．WEEKNUM函数 29

8．YEARFRAC函数 29

9．DATEDIF函数 29

2.5 求和函数 30

1．SUM函数 30

2．SUMIF函数 30

3．SUMPRODUCT函数 31

4．SUMIFS函数 33

2.6 四舍五入函数 36

1．ROUND函数 36

2．ROUNDUP函数 36

3．ROUNDDOWN函数 37

2.7 数学函数 37

1．ABS函数 37

2．INT函数 38

3．MOD函数 38

2.8 文本函数 39

1．LEN函数 39

2．LEFT函数、RIGHT函数和MID函数 39

3．FIND函数 40

4．TEXT函数 40

5．TRIM函数 41

6．CLEAN函数 41

2.9 查找和引用函数 41

1．VLOOKUP函数 42

2．MATCH函数和INDEX函数 43

3．OFFSET函数 45

4．ROW函数和COLUMN函数 47

5．INDIRECT函数 47

6．查找数据出现错误怎么办 49

7．查找函数综合应用：查找重复记录 49

8．查找函数综合应用：查找不重复记录 50

9．查找函数综合应用：根据多列条件查找数据 51

Chapter 3 公式基础知识 53

3.1 公式的构成 53

1．公式的元素 53

2．公式的运算符 54

3．公式运算符的优先顺序 55

3.2 单元格的引用 55

1．什么是引用 55

2．引用方式 55

3．引用方式对公式的影响 57

4．绝对引用应用实例 58

5．相对引用应用实例 58

6．混合引用应用实例 59

3.3 输入嵌套函数公式 59

3.4 公式返回的错误信息及原因 62

3.5 编辑公式 63

3.6 复制和移动公式 63

3.7 如何复制公式本身或公式的一部分 64

1．复制公式本身 64

2．复制公式的一部分 65

3.8 将公式转换为值 65

1．将整个公式转换为值 65

2．查看公式中某些表达式的计算结果 66

3.9 使用数组公式 66

1．数组公式的特征 67

2．数组公式的基本输入方法 67

3．在连续单元格区域内输入数组公式 67

4．在单个单元格内输入数组公式 68

5．数组公式的编辑 69

6．删除数组公式 69

7．数组公式的应用 69

Chapter 4 使用名称简化公式，提高数据管理效率 71

4.1 使用名称的好处 71

4.2 定义名称的规则 72

4.3 在Excel 2003中定义名称的方法 72

1．使用定义命令：可以任意定义名称 72

2．使用指定命令：根据表格的行标题或列标题定义名称 73

3．使用名称框：定义名称的简捷方法 74

4.4 在Excel 2007中定义名称的方法 74

1．利用“新建名称”对话框 75

2．利用“名称管理器”对话框 75

3．利用“以选定区域创建名称”对话框 76

4.5 如何快速批量为行数不同的多列数据区域定义名称 76

4.6 定义动态的数据区域名称 77

4.7 使用名称 81

4.8 重命名现有名称 81

4.9 重新定义名称 81

4.10 删除名称 81

Chpter 5 使用数据有效性控制数据输入 83

5.1 Excel 2003中数据有效性的使用方法 83

5.2 Excel 2007中数据有效性的使用方法 85

5.3 数据有效性的基本应用 86

1．只允许输入规定的整数 86

2．只允许输入规定的小数 86

3．只允许输入固定期间的日期 87

4．只允许输入当天的日期 87

5．只允许输入当天以前的日期 88

6．只允许输入当天以后的日期 88

7．只允许输入当天以前一周内的日期 89

8．只允许输入当天以后一周内的日期 89

9．只允许输入当月的日期 90

10．只允许输入规定的时间 90

11．只允许输入规定长度的文本数据 90

12．只允许输入规定的序列数据 91

5.4 数据有效性的高级应用 92

1．只能输入其他工作表的序列数据 92

2．多重限制只能输入其他工作表的某列数据 93

3．只能输入每个月的月底日期 95

4．只能输入小于当月工作日天数的请假天数 95

5．只能输入等于或大于上一个单元格的日期 96

6．只能输入数字 97

7．只能输入正整数或负整数 97

8．只能输入文本 98

9．只能输入规定格式的数字 99

10．禁止输入重复的数据 99

11．当特定单元格输入数据后才可输入数据 100

12．禁止在输入数据时出现空行 101

13．必须在上一行输完数据后才能在下一行输入新数据 102

14．禁止在几列同时输入数据 103

5.5 综合应用：必须满足多个条件才能输入数据 104

1．员工信息管理表格 104

2．订单管理表格 105

5.6 清除数据有效性 106

Chapter 6 使用条件格式美化表格 108

6.1 Excel 2003中条件格式的使用方法 108

1．根据单元格数值大小设置格式 109

2．根据单元格公式计算值设置格式 110

3．删除条件格式 111

6.2 Excel 2007中条件格式的使用方法 111

1．一般的条件格式设置 112

2．设置最大（最小）的前N项数据格式 113

3．设置高于（低于）平均值的数据格式 114

4．标识重复数据 115

5．标识指定时间的数据 115

6．利用数据条标识数据的相对大小 116

7．利用色阶标识数据的相对大小 117

8．利用图标集标识数据的相对大小 117

9．根据公式的计算结果设置条件格式 118

10．管理条件格式规则优先级 119

11．清除条件格式 119

6.3 Excel 2003中复杂条件格式应用案例 120

1．设置最大的前N项数据格式 120

2．设置最小的前N项数据格式 120

3．设置高于平均值的数据格式 121

4．设置低于平均值的数据格式 121

5．标识重复数据 122

6．标识指定时间的数据 122

6.4 条件格式综合应用 123

1．对数据区域每隔一行填充为不同的颜色 123

2．将工作表的单元格设置为棋盘样式 124

3．只有输入数据后单元格才有边框 127

4．只有在某行几个单元格都输入数据后才有边框 128

5．只有在某行几个单元格都输入数据后才有边框，并隔行设置单元格颜色 128

6．隐藏单元格的特定数据 129

7．隐藏单元格的错误值 130

Chapter 7 自定义数字显示格式，让表格数据整洁美观 131

7.1 为什么要自定义数字显示格式 131

7.2 自定义数字显示格式的基本方法 132

7.3 缩位显示数字 133

1．缩小百位显示数字 133

2．缩小千位显示数字 133

3．缩小万位显示数字 134

4．缩小十万位显示数字 134

5．缩小百万位显示数字 134

6．缩小十亿位显示数字 135

7.4 将数字显示为其他的形式 136

1．数字的自定义格式代码结构 136

2．将负数显示为正数 136

3．将正数显示为负数 136

4．隐藏单元格的数据 137

5．将输入数字的小数表示为上标 138

6．将负数用红色字体表示 138

7．在自定义数字格式中使用条件表达式 139

7.5 自定义日期格式 140

1．将日期显示为中文的年月日 140

2．将日期显示为中文的星期 141

3．将日期显示为中文的年月日和星期 141

4．将日期显示为英文的年月日 141

5．将日期显示为英文的星期 142

6．将日期显示为英文的年月日和星期 142

7．将日期显示为纯数字格式 142

8．将日期显示为带句点的数字格式 143

7.6 自定义时间格式 143

1．将时间显示为24小时制的标准格式 143

2．将时间显示为12小时制的格式 143

3．将时间显示为中文格式 143

4．将时间显示为计时格式 144

5．显示超过24小时的累计时间 145

7.7 自定义电话号码格式 145

Chapter 8 使用窗体控件，设计更加灵活的表格 146

8.1 Excel 2003窗体控件 146

8.2 Excel 2007表单控件 146

8.3 在工作表插入窗体控件的方法 147

8.4 建立窗体控件与工作表单元格的链接 147

8.5 窗体控件应用之一：使用单选按钮 148

1．单选按钮简介 148

2．单选按钮的简单应用 148

3．如何选择多个单选按钮 149

8.6 窗体控件应用之二：使用复选框 150

1．复选框简介 150

2．复选框的简单应用 151

8.7 窗体控件应用之三：使用组合框 152

1．组合框简介 152

2．组合框的简单应用 153

8.8 窗体控件应用之四：使用滚动条 155

1．滚动条简介 155

2．滚动条的简单应用 155

8.9 窗体控件应用之五：使用数值调节钮 157

1．数值调节钮简介 157

2．数值调节钮的简单应用 157

Chapter 9 使用ActiveX控件，设计更加灵活的表格 160

9.1 在Excel 2003中使用ActiveX控件 160

9.2 在Excel 2007中使用ActiveX控件 161

9.3 在工作表插入ActiveX控件的方法 161

9.4 设置ActiveX控件属性，与工作表建立链接 162

9.5 ActiveX控件应用之一：使用选项按钮 163

1．选项按钮简介 163

2．选项按钮的简单应用 163

3．ActiveX选项按钮的缺点 164

9.6 ActiveX控件应用之二：使用复选框 164

1．复选框简介 164

2．复选框的简单应用 165

9.7 ActiveX控件应用之三：使用组合框 166

1．组合框简介 166

2．组合框的简单应用 167

9.8 ActiveX控件应用之四：使用图像控件 168

1．图像控件简介 168

2．图像控件的简单应用之一：显示图片 169

3．图像控件的简单应用之二：显示单元格数据 170

Chapter 10 表格与图表图片及Flash的结合 171

10.1 表格与图片结合的表格 171

10.2 表格与图表结合的表格 175

10.3 在工作表上插入Flash动画 176

Chapter 11 限制操作区域，防止输入垃圾数据 177

11.1 隐藏行和列 177

11.2 限制滚动区域 177

11.3 保护工作表 180

Chapter 12 使用筛选工具辅助制作表格 181

12.1 Excel 2003中筛选工具的使用方法 181

1．自定义筛选 181

2．高级筛选 182

3．列表工具 183

12.2 Excel 2007中筛选工具的使用方法 185

1．自动筛选和高级筛选 185

2．使用表工具 187

12.3 如何对含有合并单元格标题的表格进行筛选 187

12.4 如何对含有很多空行的表格进行筛选 188

12.5 复制筛选结果 189

Chapter 13 使用导入外部数据工具辅助制作表格 190

13.1 Excel 2003中导入外部数据工具的使用方法 190

1．完整导入工作表数据 190

2．有条件地导入工作表数据 192

13.2 Excel 2007中导入外部数据工具的使用方法 194

1．完整导入工作表数据 194

2．有条件地导入工作表数据 195

Chapter 14 使用数据透视表工具辅助制作表格 196

14.1 Excel 2003中制作数据透视表的基本方法 197

14.2 Excel 2007中使用数据透视表的基本方法 200

1．制作数据透视表的基本方法 201

2．设置数据透视表选项 202

3．对数据透视表字段进行基本设置 203

4．改变报表的布局 203

5．设置数据透视表样式选项 204

6．使用数据透视表样式 205

7．数据筛选 205

14.3 通过字段组合制作各种汇总报表 206

1．对日期进行组合，制作年、季度和月的汇总表 206

2．对城市、商品进行组合，制作各地区和各类商品的汇总表 207

14.4 通过设置字段的显示方式制作各种统计分析报表 209

14.5 将数据透视表转换为静态报表，并进行相应的修饰和美化 211

Chapter 15 使用宏和VBA提高表格管理的效率和安全性 212

15.1 使用自定义函数提高公式的准确性 212

1．设计自定义函数 212

2．保存自定义函数 214

3．在Excel 2003中将自定义函数做成加载宏 214

4．在Excel 2007中将自定义函数做成加载宏 215

15.2 使用工作簿和工作表的事件来控制数据的输入 216

15.3 使用VBA设计自动报表生成系统 218

15.4 隐藏重要的工作表 218

15.5 使用VBA禁止删除、重命名、移动和复制工作表 219

Chapter 16 保护工作表的公式和重要数据 221

16.1 保护工作表的公式 221

16.2 为不同的单元格区域设置不同的保护密码 223

16.3 保护工作表的重要数据 224

16.4 隐藏工作表的重要数据 225

16.5 禁止他人复制数据 225

Chapter 17 表格重复使用和共享 226

17.1 制作模板，让表格重复使用 226

1．Excel 2003中制作及使用模板 226

2．Excel 2007中制作及使用模板 227

17.2 保存为共享工作簿，实现网上协同作业 228

1．Excel 2003中创建共享工作簿 228

2．Excel 2007中创建共享工作簿 229

第三部分 实践篇 232

Chapter 18 常用人力资源高效管理表格设计与应用 232

18.1 员工招聘面试评估表 232

1．面试评估表结构设计 233

2．使用有效性快速输入规定的序列数据 234

3．使用有效性限制只能输入规定的数字 235

4．使用单选按钮对应聘人员的各方面进行评价 235

5．计算评价结果 239

6．保护工作表 240

7．打印面试评估表 240

8．扩展应用：自动保存面试评估结果，筛选录用人员 240

9．系统完善：与应聘人员信息管理系统的结合 244

18.2 员工培训信息管理表 244

1．设计员工信息管理表格 245

2．设计课程信息管理表格 246

3．设计培训信息管理表格 246

4．利用数据透视表编制各种统计汇总分析报表 251

5．设计一个系统管理界面，便于操作管理 252

18.3 员工基本信息管理表 254

1．设计员工基本信息管理表格结构 254

2．设置有效性，输入规定的工号 255

3．设置有效性，快速输入部门名称 256

4．设置有效性，只允许输入不重复的15位或18位身份证号码 256

5．设置有效性，只允许输入员工的入职日期数据 257

6．设置有效性，快速输入员工学历 257

7．利用公式从身份证号码中获取员工的性别 257

8．利用公式从身份证号码中获取员工的户口所在地 257

9．利用公式从身份证号码中获取员工的出生日期 258

10．多行输入教育经历和工作经历 258

11．插入员工照片，限制输入任何数据 258

12．设置条件格式，美化表格 259

13．保护公式，限制编辑区域 259

14．设计员工信息查询系统 260

15．使用ActiveX控件设计员工信息精确查询系统 261

16．员工基本信息的多维度分析报表 264

17．设计一个系统管理界面 266

18.4 员工日常考勤管理与统计表 266

1．完整月的日常考勤表 266

2．制作完整月日常考勤的月汇总表 272

3．设计辅助区域，以备进行考勤统计汇总分析 273

4．复制工作表，得到全年日常考勤表和月汇总表 274

5．制作各种考勤汇总统计报表 274

6．案例研究：制作跨月考勤表和汇总表 282

7．系统完善 283

18.5 员工薪酬管理表 283

1．设计员工薪酬清单 283

2．制作基本的汇总表 284

3．制作个税代扣代缴表 285

4．制作社会保险汇总表 286

5．制作住房公积金汇总表 287

6．制作每个员工的全年薪酬明细表 288

7．制作企业薪酬统计分析报表和图表 290

Chapter 19 常用销售管理表格设计制作与应用 293

19.1 市场问卷调查表 293

1．市场问卷调查表结构与效果 293

2．市场问卷调查表结构设计 294

3．利用有效性控制输入数据 295

4．利用单选按钮选择回答一个问题 295

5．利用复选框选择回答多个问题 297

6．利用数值调节钮输入日期 298

7．表格的修饰与美化 299

8．保护工作表，只允许操作输入数据的单元格和控件 300

9．保存调查表的结果 300

10．制作模板，通过邮件发布给客户 301

11．将收到的调查表进行汇总分析 301

19.2 使用制作销售日常管理台账 301

1．设计产品资料管理表格 301

2．设计客户资料管理表格 303

3．设计日常销售管理台账：设计表格结构 304

4．设计日常销售管理台账：输入日期 304

5．设计日常销售管理台账：输入订单号 305

6．设计日常销售管理台账：输入客户名称 305

7．设计日常销售管理台账：输入产品编码 306

8．设计日常销售管理台账：输入产品名称 306

9．设计日常销售管理台账：输入产品报价 306

10．设计日常销售管理台账：输入产品数量 306

11．设计日常销售管理台账：计算订单产品金额 307

12．设计日常销售管理台账：标识某订单是否已经发货 307

13．设计日常销售管理台账：利用条件格式使工作表整洁美观 307

14．设计日常销售管理台账：醒目标识要输入数据的列 308

15．设计日常销售管理台账：定义相关名称 308

16．设计日常销售管理台账：保护工作表 308

19.3 销售管理台账的应用——设计出库单表格 308

19.4 销售管理台账的应用——利用函数制作出库单 310

1．查询销售台账表单中不重复的订单号及其对应的客户名称 310

2．使用ActiveX组合框控件输入某个订单号及对应客户名称 310

3．填列出库单的标头项目 312

4．填列出库单表格内容项目 312

5．处理辅助查询单与单元格区域 314

6．保护工作表 314

7．如何编制并打印出库单 314

19.5 销售管理台账的应用——利用导入数据＋筛选制作出库单 315

1．设计标题 315

2．导入销售台账表单的数据 316

3．筛选需要的数据，以准备制作出库单 317

4．制作出库单 318

19.6 销售管理台账的应用——利用数据透视表制作出库单 318

19.7 销售管理台账的应用——利用VBA制作出库单 319

1．设计出库单表格 319

2．引用Microsoft Active Data Obiects 2.x Library对象库 319

3．设置有效性，快速选择输入客户名称 320

4．插入ActiveX组合框控件 320

5．编写程序代码 320

6．为出库单的订单打上已发货标记 323

19.8 销售管理台账的应用——制作订单明细表 324

1．制作某个客户的已发货／未发货订单明细表 324

2．制作某个产品的已发货／未发货订单明细表 325

3．制作某段日期内的已发货／未发货订单明细表 325

19.9 制作销售统计分析报表 325

1．制作销售月汇总表及变化趋势图表 326

2．制作销售季度汇总表及变化趋势图表 330

3．制作销售年度汇总表及变化趋势图表 330

4．制作销售月累计报表 331

5．制作月销售同比分析报表 332

6．制作月销售环比分析报表 335

7．制作能够考察地区和商品类别的汇总报表 338

8．考察某产品在各个城市的市场占有率统计分析表 341

9．考察某城市中各个产品的销售分布统计分析表 342

Chapter 20 常用财务管理表格设计与应用 343

20.1 日常费用管理表 343

1．日常费用管理台账的结构 343

2．日常费用管理台账的设计 344

3．日常费用统计分析表 348

20.2 会计凭证管理 350

1．通用记账凭证的格式 350

2．基于Excel的记账凭证的结构样式 351

3．设计会计科目表 352

4．用于输入数据的记账凭证结构设计及美化 352

5．用于打印输出的记账凭证的结构设计及美化 354

6．将金额数字转换为大写 356

7．建立两个格式的记账凭证之间的联系 356

8．记账凭证的使用 357

20.3 制作记账凭证清单 358

1．设计记账凭证清单 358

2．利用VBA将记账凭证数据自动保存到记账凭证清单 358

20.4 使用查询并打印会计凭证 360

1．查询制作会计凭证 360

2．打印会计凭证 361

20.5 编制会计核算表格 362

1．制作总分类账试算平衡表 362

2．制作科目汇总表 363

3．制作总分类账表 363

4．制作明细分类账表 365

20.6 制作预算与实际对比分析报表和图表 366

1．报表和图表效果 366

2．制作预算和实际执行情况对比分析报表 367

3．制作预算和实际执行情况的基本对比分析图表 368

4．制作预算和实际执行情况的动态交互对比分析图表 375