Excel高效管理表格设计从入门到精通 全新版PDF|Epub|txt|kindle电子书版本网盘下载

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