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

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

注意：本站所有压缩包均有解压码： 点击下载压缩包解压工具

第一部分 设计原则篇2

Chapter1Excel高效管理表格的设计原则2

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

1.便于数据日常管理2

2.便于数据计算汇总4

3.便于数据统计分析6

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

5.便于打印8

1.2表格的类型8

1.报告型表格8

2.清单型表格9

1.3设计表格的基本原则10

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

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

1.4输入数据的基本原则11

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

2.使用公式和函数自动输入数据12

3.处理文本型数字时要小心12

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

5.同列数据必须为同一类型数据13

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

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

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

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

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

3.设置单元格的字体格式16

4.设置单元格的对齐方式18

5.设置单元格的框线18

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

7.关于合并单元格20

8.关于条件格式的使用20

第二部分 基础与技能篇22

Chapter2 快速定位单元格的方法和技巧22

2.1利用鼠标选择单元格22

1.选择连续的单元格区域22

2.选择不连续的单元格区域22

3.选择单行22

4.选择连续的多行22

5.选择不连续的多行23

6.选择单列23

7.选择连续的多列23

8.选择不连续的多列23

9.选取工作表的所有单元格23

10.选择多个工作表的相同单元格区域23

2.2利用名称框选择单元格24

1.选择连续的单元格区域24

2.选择不连续的单元格区域24

3.选择单行24

4.选择连续的多行24

5.选择不连续的多行25

6.选择单列25

7.选择连续的多列25

8.选择不连续的多列25

9.选择多个工作表的相同单元格区域25

2.3利用“定位”对话框选择单元格25

1.打开“定位”对话框的快捷方法25

2.利用“定位”对话框选择单元格25

2.4利用“定位条件”对话框选择特殊的单元格26

1.“定位条件”对话框概述26

2.如何在Excel 2007中快速打开“定位条件”对话框27

3.选择有计算公式的单元格区域27

4.选择计算公式出错的单元格区域28

5.选择为常量的单元格区域29

6.选择为数字常量的单元格区域29

7.选择为文本常量的单元格区域29

8.选择为逻辑值常量的单元格区域30

9.选择为空值的单元格区域30

10.选择当前单元格区域31

11.选择可见的单元格区域32

12.选择包含数组公式的单元格区域33

13.选择有批注的单元格区域33

14.选择有条件格式的单元格区域33

15.选择有数据有效性的单元格区域34

16.选择数据区域的最后一个单元格35

17.选择除公式外的所有不为空的单元格36

2.5利用快捷键定位单元格36

1.定位到单元格区域的边缘行或列的单元格36

2.定位到工作表的第一行（列）和最后一行（列）36

3.快速定位到数据区域的第一个单元格和最后一个单元格37

2.6利用定义的名称定位单元格区域37

1.在名称框中定位到某名称代表的单元格区域37

2.在名称框中定位到几个名称代表的单元格区域37

2.7利用“查找和替换”对话框选择单元格38

1.选取含有指定数据的单元格区域38

2.选取含有特定字符的单元格区域40

3.选取指定填充颜色的单元格区域40

4.选取指定字体格式的单元格区域42

5.选取区分字母大小写的单元格区域42

6.选取单元格完全匹配的单元格区域43

7.选取当前工作簿中所有满足匹配条件的单元格区域43

Chapter3 常用函数及其应用44

3.1逻辑判断函数44

1.IF（）函数44

2.AND（）函数44

3.OR（）函数45

4.IFERROR（）函数45

5.逻辑判断函数简单应用45

3.2信息函数46

1.IS类函数46

2.IS类函数的使用方法和案例46

3.3计数函数47

1.COUNT（）函数47

2.COUNTA（）函数47

3.COUNTIF（）函数47

4.COUNTIFS（）函数48

5.计数函数的基本应用集锦48

3.4日期和时间函数49

1.TO DAY（）函数49

2.DATE（）函数50

3.YEAR（）、 MONTH（）、 DAY（）函数50

4.WEEKDAY（）函数51

5.EDATE（）函数52

6.EOMONTH（）函数52

7.WEEKNUM（）函数53

8.YEARFRAC（）函数53

9.DATEDIF（）函数53

3.5求和函数54

1.SUM（）函数54

2.SUMIF（）函数54

3.SUMPRODUCT（）函数54

4.SUMIFS（）函数57

3.6四舍五入函数59

1.ROUND（）函数60

2.ROUNDUP（）函数60

3.ROUNDDOWN（）函数60

3.7数学函数61

1.ABS（）函数61

2.INT（）函数61

3.MOD（）函数62

3.8文本函数63

1.LEN（）函数63

2.LEFT（）函数、RIGHT（）函数和MID（）函数63

3.FIND（）函数63

4.TEXT（）函数63

5.TRIM（）函数64

6.CLEAN（）函数64

3.9查找和引用函数65

1.VLOOKUP（）函数65

2.MATCH（）函数和INDEX（）函数67

3.OFFSET（）函数68

4.ROW（）函数和COLUMN（）函数71

5.INDIRECT（）函数71

6.查找数据出现错误怎么办73

7.查找函数综合应用：查找重复记录73

8.查找函数综合应用：查找不重复记录74

Chapter4 公式基础知识76

4.1公式的构成76

1.公式的元素76

2.公式的运算符77

3.公式运算符的优先顺序78

4.2单元格的引用78

1.什么是引用78

2.引用方式78

3.引用方式对公式的影响80

4.绝对引用应用实例81

5.相对引用应用实例8

6.混合引用应用实例82

4.3输入公式82

1.直接用键盘输入公式83

2.输入复杂公式83

4.4公式返回的错误信息86

1.错误提示86

2.错误信息及原因86

4.5编辑公式87

1.在编辑栏中编辑公式87

2.在单元格中编辑公式87

4.6复制和移动公式87

1.复制公式的基本方法87

2.复制公式的快捷方法88

3.移动公式的基本方法88

4.移动公式的快捷方法88

4.7如何原封不动复制公式本身或公式的一部分89

1.复制公式本身89

2.复制公式的一部分89

4.8将公式转换为值89

1.将整个公式转换为值89

2.查看公式中某些表达式的计算结果91

4.9使用数组公式92

1.数组公式的特征92

2.数组公式的基本输入方法92

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

4.在单个单元格内输入数组公式94

5.数组公式的编辑95

6.删除数组公式95

7.数组公式的应用95

Chapter5 使用名称简化公式，提高数据管理效率96

5.1使用名称的好处96

5.2定义名称的规则97

5.3在Excel 2003中定义名称的方法97

1.使用“定义”命令：可以任意定义名称97

2.使用“指定”命令：根据表格的行标题或列标题定义名称98

3.使用名称框：定义名称的简捷方法99

5.4在Excel 2007中定义名称的方法99

1.利用“新建名称”对话框100

2.利用“名称管理器”对话框100

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

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

5.6定义动态的数据区域名称103

1.以单元格A1为起点，以A列最后一个单元格为终点的动态列区域名称103

2.以单元格A1为起点，以第1行最后一个单元格为终点的动态行区域名称103

3.以单元格A1为起点，向下偏移M个单元格、高度为H、宽度为1列的动态单元格区域名称104

4.以单元格A1为起点，向右偏移N个单元格、宽度为W、高度为1行的动态单元格区域名称104

5.以单元格A1为起点，以A列最后一个单元格和第1行最后一个单元格为终点的动态单元格区域名称104

6.以单元格A1为起点，向下偏移M个单元格，向右偏移N个单元格，高度为H、宽度为W的动态单元格区域名称105

7.定义指定行或指定列的数据区域名称105

5.7使用名称106

5.8重命名现有的名称106

5.9重新定义名称107

5.10删除名称107

Chapter6 使用数据有效性控制数据输入108

6.1 Excel 2003中数据有效性的使用方法108

6.2 Excel 2007中数据有效性的使用方法111

6.3数据有效性的基本应用111

1.只允许输入规定的整数111

2.只允许输入规定的小数112

3.只允许输入固定期间的日期112

4.只允许输入当天的日期113

5.只允许输入当天以前的日期113

6.只允许输入当天以后的日期114

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

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

9.只允许输入当月的日期115

10.只允许输入规定的时间116

11.只允许输入规定长度的文本数据117

12.只允许输入规定的序列数据117

6.4数据有效性的高级应用119

1.只能输入其他工作表的序列数据119

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

3.只能输入每个月的月底日期121

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

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

6.只能输入数字124

7.只能输入正整数或负整数125

8.只能输入文本125

9.只能输入规定格式的数字126

10.禁止输入重复的数据127

11.当特定单元格输入数据后时才可输入数据128

12.禁止在输入数据时出现空行129

13.必须在上一行输全数据后才能在下一行输入新数据130

14.禁止在几列同时输入数据132

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

1.员工信息管理表格133

2.订单管理表格134

6.6清除数据有效性135

Chapter7 使用条件格式美化表格136

7.1 Excel 2003中条件格式的使用方法136

1.根据单元格数值大小设置格式137

2.根据单元格公式计算值设置格式138

3.删除条件格式139

7.2 Excel 2007中条件格式的使用方法140

1.一般的条件格式设置141

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

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

4.标识重复数据144

5.标识指定时间的数据144

6.利用数据条标识数据的相对大小146

7.利用色阶标识数据的相对大小146

8.利用图标集标识数据的相对大小147

9.根据公式的计算结果设置条件格式148

10.管理条件格式规则优先级149

11.清除条件格式149

7.3 Excel 2003中复杂条件格式应用案例149

1.设置最大的前N项数据格式149

2.设置最小的前N项数据格式150

3.设置高于平均值的数据格式150

4.设置低于平均值的数据格式151

5.标识重复数据151

6.标识指定时间的数据152

7.4条件格式综合应用153

1.对数据区域每隔一行填充不同的颜色153

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

3.只有输入数据后单元格才有边框157

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

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

6.隐藏单元格的特定数据160

7.隐藏单元格的错误值161

Chapte8 自定义数字显示格式，让表格数据整洁美观162

8.1为什么要自定义数字显示格式162

8.2自定义数字显示格式的基本方法163

8.3缩位显示数字164

1.缩小百位显示数字164

2.缩小千位显示数字164

3.缩小万位显示数字165

4.缩小十万位显示数字165

5.缩小百万位显示数字165

6.缩小亿位显示数字166

8.4将数字显示为其他形式167

1.数字的自定义格式代码结构167

2.将负数显示为正数167

3.将正数显示为负数167

4.隐藏单元格的数据168

5.将输入数字的小数表示为上标169

6.将负数用红色字体表示169

8.5自定义日期格式171

1.将日期显示为中文的年月日171

2.将日期显示为中文的星期172

3.将日期显示为中文的年月日和星期172

4.将日期显示为英文的年月日173

5.将日期显示为英文的星期174

6.将日期显示为英文的年月日和星期174

7.将日期显示为纯数字格式175

8.将日期显示为带句点的数字格式175

8.6自定义时间格式175

1.将时间显示为24小时制的标准格式176

2.将时间显示为12小时制的格式176

3.将时间显示为中文格式177

4.将时间显示为计时格式177

5.显示超过24小时的累计时间178

8.7自定义电话号码格式178

Chapter9 使用窗体控件设计更加灵活的表格180

9.1 Excel 2003窗体控件180

9.2 Excel 2007表单控件180

9.3在工作表中插入窗体控件的方法181

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

9.5窗体控件应用之一：使用单选按钮182

1.单选按钮简介182

2.单选按钮的简单应用183

3.如何实现选择多个单选按钮的目的184

9.6窗体控件应用之二：使用复选框185

1.复选框简介185

2.复选框的简单应用186

9.7窗体控件应用之三：使用组合框187

1.组合框简介187

2.组合框的简单应用188

9.8窗体控件应用之四：使用滚动条190

1.滚动条简介190

2.滚动条的简单应用191

9.9窗体控件应用之五：使用微调按钮193

1.微调按钮简介193

2.微调按钮的简单应用194

Chapter10 使用ActiveX控件设计更加灵活的表格197

10.1在Excel 2003中使用 ActiveX控件197

10.2在Excel 2007中使用ActiveX控件198

10.3在工作表中插入ActiveX控件的方法198

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

10.5 ActiveX控件应用之一：使用单选按钮200

1.单选按钮简介200

2.单选按钮的简单应用200

3.ActiveX单选按钮的缺点201

10.6 ActiveX控件应用之二：使用复选框202

1.复选框简介202

2.复选框的简单应用202

10.7 ActiveX控件应用之三：使用组合框203

1.组合框简介203

2.组合框的简单应用204

10.8 ActiveX控件应用之四：使用图像控件206

1.图像控件简介206

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

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

Chapter11 表格与图表图片及Flash的结合：信息更加丰富、更加实用的完美管理表格209

11.1表格与图片结合209

11.2表格与图表结合214

11.3在工作表中插入Flash动画214

Chapter12 限制操作区域，防止输入垃圾数据216

12.1隐藏行和列216

12.2限制滚动区域216

12.3保护工作表219

Chapter13 使用筛选工具辅助制作表格220

13.1 Excel 2003中筛选工具的使用方法220

13.2 Excel 2007中筛选工具的使用方法220

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

13.4如何对含有很多空行的表格进行筛选223

13.5复制筛选结果224

Chapter14 使用数据透视表工具辅助制作表格225

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

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

1.制作数据透视表的基本方法231

2.设置数据透视表选项233

3.对数据透视表字段进行基本设置233

4.改变报表的布局234

5.设置数据透视表样式选项234

6.使用数据透视表样式235

7.数据筛选236

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

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

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

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

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

Chapter15 使用宏和VBA提高表格管理的效率和安全性243

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

1.设计自定义函数243

2.保存自定义函数245

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

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

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

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

15.4隐藏重要的工作表249

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

Chapter16 保护工作表的公式和重要数据252

16.1保护工作表的公式252

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

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

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

16.5禁止他人复制数据256

Chapter17 表格重复使用和共享257

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

1.在Excel 2003中制作及使用模板257

2.在Excel 2007中制作及使用模板258

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

1.在Excel 2003中创建共享工作簿259

2.在Excel 2007中创建共享工作簿260

第三部分 实践篇262

Chapter18 常用人力资源高效管理表格设计与应用262

18.1员工招聘面试评估表262

1.面试评估表结构设计263

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

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

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

5.计算评价结果270

6.保护工作表270

7.打印面试评估表271

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

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

18.2员工培训信息管理表275

1.设计员工信息管理表格276

2.设计课程信息管理表格277

3.设计培训信息管理表格277

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

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

18.3员工基本信息管理表286

1.设计员工基本信息管理表格结构286

2.设置有效性，输入规定的工号287

3.设置有效性，快速输入部门名称288

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

5.设置有效性，正确输入员工的入职日期数据289

6.设置有效性，快速输入员工学历289

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

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

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

10.多行输入教育经历和工作经历291

11.插入员工照片，限制输入任何数据291

12.设置条件格式，美化表格，便于查看数据292

13.保护公式，限制编辑区域292

14.设计员工信息查询系统293

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

16.员工基本信息的多维度分析报表297

17.设计一个系统管理界面，便于操作管理299

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

1.完整月的日常考勤表299

2.制作完整月日常考勤的月汇总表305

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

4.复制工作表，得到全年12个月的日常考勤表和月汇总表308

5.制作各种考勤汇总统计报表308

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

7.系统完善317

18.5员工薪酬管理表317

1.设计员工薪酬清单317

2.制作基本的汇总表318

3.制作个税代扣代缴表319

4.制作社会保险汇总表321

5.制作住房公积金汇总表322

6.制作每个员工的全年薪酬明细表323

7.制作企业薪酬统计分析报表和图表325

Chapter19 常用销售管理表格设计制作与应用329

19.1市场问卷调查表329

1.市场问卷调查表结构与效果329

2.市场问卷调查表结构设计330

3.利用有效性控制数据输入331

4.利用单选按钮选择回答一个问题331

5.利用复选框选择回答多个问题333

6.利用微调按钮输入日期334

7.表格的修饰与美化335

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

9.保存调查表的结果336

10.制作模板，通过邮件发布给客户337

11.对收到的调查表进行汇总分析337

19.2销售日常管理台账337

1.设计产品资料管理表格337

2.设计客户资料管理表格339

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

4.设计日常销售管理台账：输入日期340

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.填列出库单的标头项目348

4.填列出库单表格内容项目348

5.处理辅助查询单与单元格区域350

6.保护工作表350

7.如何编制并打印出库单350

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

1.设计标题351

2.导入销售台账表单的数据351

3.筛选需要的数据，准备制作出库单353

4.制作出库单354

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

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

1.设计出库单表格355

2.引用Microsoft Active Data Objects 2.x Library对象库355

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

4.插入ActiveX组合框控件357

5.编写程序代码357

6.为出库单的订单做已发货标记359

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

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

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

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

19.9销售统计分析报表362

1.制作销售月汇总表及变化趋势图表363

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

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

4.制作销售月累计报表373

5.制作月销售同比分析报表375

6.制作月销售环比分析报表378

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

8.制作考察某产品在各个城市的市场占有率的统计分析表384

9.制作考察某城市中各个产品的销售分布的统计分析表385

Chapter20 常用财务管理表格设计制作与应用387

20.1日常费用管理表387

1.日常费用管理台账的结构387

2.日常费用管理台账的设计388

3.日常费用统计分析表393

20.2会计凭证395

1.通用记账凭证的格式395

2.基于Excel的记账凭证的结构样式396

3.设计会计科目表396

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

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

6.将金额数字转换为大写401

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

8.记账凭证的使用402

20.3记账凭证清单403

1.设计记账凭证清单403

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

20.4查询并打印会计凭证405

1.查询会计凭证405

2.打印会计凭证406

20.5会计核算表格407

1.制作总分类账试算平衡表407

2.制作科目汇总表408

3.制作总分类账表409

4.制作明细分类账表411

20.6预算与实际对比分析报表和图表412

1.报表和图表效果412

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

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

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