Excel高效管理表格设计从入门到精通

第一部分设计原则篇

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设计表格的基本原则 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

第二部分基础与技能篇

chapter 2 快速定位单元格的方法和技巧 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

chapter 3 常用函数及其应用 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.today()函数 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

chapter 4 公式基础知识 76

4.1公式的构成 76

1.公式的元素 76

2.公式的运算符 77

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

4.2单元格的引用 78

1.什么是引用 78

2.引用方式 78

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

4.绝对引用应用实例 81

5.相对引用应用实例 81

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

chapter 5 使用名称简化公式，提高数据管理效率 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

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

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

6.2excel 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

chapter 7 使用条件格式美化表格 136

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

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

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

3.删除条件格式 139

7.2excel 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.3excel 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

chapter 8 自定义数字显示格式，让表格数据整洁美观 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

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

9.1excel 2003窗体控件 180

9.2excel 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

chapter 10 使用activex控件设计更加灵活的表格 197

10.1在excel 2003中使用activex控件 197

10.2在excel 2007中使用activex控件 198

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

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

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

1.单选按钮简介 200

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

3.activex单选按钮的缺点 201

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

1.复选框简介 202

2.复选框的简单应用 202

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

1.组合框简介 203

2.组合框的简单应用 204

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

1.图像控件简介 206

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

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

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

11.1表格与图片结合 209

11.2表格与图表结合 214

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

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

12.1隐藏行和列 216

12.2限制滚动区域 216

12.3保护工作表 219

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

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

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

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

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

13.5复制筛选结果 224

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

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

14.2excel 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

chapter 15 使用宏和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

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

16.1保护工作表的公式 252

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

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

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

16.5禁止他人复制数据 256

chapter 17 表格重复使用和共享 257

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

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

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

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

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

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

第三部分实践篇

chapter 18 常用人力资源高效管理表格设计与应用 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

chapter 19 常用销售管理表格设计制作与应用 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

chapter 20 常用财务管理表格设计制作与应用 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