Excel 2007表格制作与数据处理PDF电子书下载

Part 01 基础学习 1

Chapter 01　工作表与单元格的基本操作 1

1.1　工作表的基本操作 2

1.1.1 重命名工作表 2

1.1.2　设置工作表标签颜色 2

1.1.3　插入工作表 3

1.1.4　删除工作表 4

1.1.5　移动工作表 4

1.1.6　复制工作表 5

1.1.7　隐藏工作表 6

1.1.8　显示工作表 6

1.1.9　调整工作表的显示比例 6

1.1.10　更改一张工作簿默认包含的工作表数量 8

1.2　单元格的基本操作 8

1.2.1　插入单元格 8

1.2.2　插入单行或单列 9

1.2.3　一次性插入多行或多列 10

1.2.4　删除单元格 12

1.2.5　删除行或列 13

1.2.6　合并单元格 14

1.2.7　调整行高和列宽 15

1.2.8　隐藏工作表行或列 17

1.2.9　显示工作表行或列 18

Chapter 02 表格数据的输入与编辑 19

2.1　表格数据的输入 20

2.1.1　输入文本型数据 20

2.1.2　输入包含指定小数位数的数值数据 22

2.1.3　输入货币型数据 23

2.1.4　输入百分比型数据 24

2.1.5　输入分数型数据 24

2.1.6　输入日期型数据 25

2.1.7　输入时间型数据 27

2.1.8　输入特殊符号 28

2.2　表格数据的填充 29

2.2.1 相同数据的快速填充 29

2.2.2　在多个不连续单元格中输入相同数据 31

2.2.3　有规则数据的填充 31

2.2.4　利用“自动填充选项”功能来填充 32

2.2.5 自定义填充序列 33

2.3　表格数据的修改、移动、复制、粘贴与删除 35

2.3.1 数据修改 35

2.3.2　通过“剪贴板”来移动、复制、粘贴与删除数据 35

2.3.3　通过快捷键来移动、复制、粘贴与删除数据 36

2.3.4　通过“选择性粘贴”功能来复制、粘贴数据 37

2.3.5　通过“粘贴选项”功能来复制、粘贴数据 40

2.4　表格数据的查找与替换 40

2.4.1 表格数据的查找 40

2.4.2　表格数据的替换 42

Chapter 03 表格的美化与效果设置 45

3.1　表格字体与对齐方式设置 46

3.1.1 设置表格字体 46

3.1.2　设置表格对齐方式 47

3.2　表格边框与底纹设置 48

3.2.1 设置表格边框效果 48

3.2.2　设置表格底纹效果 51

3.3 套用单元格样式来美化表格 52

3.3.1 套用默认单元格样式 52

3.3.2　新建单元格样式 53

3.3.3　修改单元格样式 54

3.3.4　合并单元格样式 55

3.4　套用表格格式来美化表格 56

3.4.1 套用默认表格格式 56

3.4.2　新建表样式 57

3.5　套用“主题”方案来美化表格 59

3.5.1 套用“主题”方案 59

3.5.2　新建“主题”方案 62

Chapter 04 表格数据的条件设置 65

4.1　表格数据有效性设置 66

4.1.1 设置数据有效性为满足特定的值区域 66

4.1.2　设置数据有效性为可选择序列 68

4.1.3　数据有效性的复制与清除 69

4.2　数据条件格式设置 70

4.2.1 突出显示单元格规则 70

4.2.2　项目选取规则 71

4.2.3　以“数据条”标记形式突显表格数据 73

4.2.4　以“图标集”标记形式突显表格数据 74

4.2.5　新建条件格式规则 76

4.2.6　管理新建的条件格式规则 78

Chapter 05 表格数据的管理 80

5.1　数据排序 81

5.1.1 按单个条件排序 81

5.1.2　按多个条件排序 82

5.2　数据筛选 83

5.2.1 添加自动筛选功能 83

5.2.2　筛选出大于指定值和大于平均值的记录 84

5.2.3　筛选出“或”条件与“与”条件的记录 86

5.2.4　高级筛选的应用 87

5.2.5　取消设置的筛选条件 89

5.3 数据分类汇总 89

5.3.1 创建分类汇总统计数据 89

5.3.2　编辑分类汇总 91

5.4　数据分列 92

5.4.1 以“分隔符号”分列表格数据 92

5.4.2　以“固定宽度”分列表格数据 94

Chapter 06 表格数据的计算 97

6.1 “合并计算”功能的使用 98

6.1.1 利用“合并计算”功能进行求和计算 98

6.1.2　利用“合并计算”功能进行求平均值计算 99

6.2　利用公式进行数据计算 100

6.2.1 公式运算符 101

6.2.2　公式的输入 101

6.2.3　公式的编辑 102

6.3　公式计算中函数的使用 103

6.3.1 函数的构成 103

6.3.2　函数的参数及其说明 103

6.3.3　了解函数类别与函数帮助 104

6.3.4 “插入函数”向导的应用 105

6.4　公式计算中对数据源的引用 108

6.4.1 引用相对数据源 108

6.4.2　引用绝对数据源 109

6.4.3 引用当前工作表之外的数据源 110

6.4.4 引用其他工作簿中的数据源 112

6.5　公式计算中名称的使用 113

6.5.1 将单元格区域定义为名称 113

6.5.2　使用定义的名称 114

6.5.3　将公式定义为名称 117

6.5.4　名称管理 117

Chapter 07 表格数据的分析 120

7.1　使用数据透视表来进行数据分析 121

7.1.1 数据透视表概述 121

7.1.2　数据透视表元素组成 121

7.1.3　数据透视表的创建 123

7.1.4　数据透视表的编辑 126

7.1.5　数据透视表的美化 130

7.1.6　在数据透视表中对数据进行排序 132

7.1.7　在数据透视表中对数据进行筛选 134

7.2　使用图表来进行数据分析 136

7.2.1 图表类型概述 136

7.2.2　图表的组成 143

7.2.3　图表的创建 144

7.2.4　图表的编辑 146

7.2.5　图表的设置 149

Chapter 08 Excel分析工具的应用 163

8.1　安装与卸载分析工具库 164

8.1.1 安装分析工具库 164

8.1.2　卸载分析工具库 165

8.2　单变量求解 166

8.2.1 单变量求解预定销售量 166

8.2.2　单变量求解可贷款金额 168

8.2.3　单变量求解贷款年限 169

8.3　数据表（模拟运算表） 169

8.3.1 单变量数据表运算 169

8.3.2　双变量数据表运算 171

8.3.3　将模拟运算结果转换为常量 173

8.3.4　删除模拟运算结果 174

8.4　方案管理器 174

8.4.1 建立方案 174

8.4.2　显示方案 178

8.4.3　合并方案 178

8.4.4　编辑方案 180

8.4.5　创建方案摘要 181

8.5 规划求解 182

8.5.1 建立“产品利润最大化生产方案”计算模型 183

8.5.2　使用“规划求解”计算出最大利润额 183

Part 02 技巧点拨 187

Chapter 09 单元格编辑技巧 187

9.1　单元格选取技巧 188

1．选取多个不连续的单元格 188

2．选取工作表中的所有单元格 188

3．选择大范围块状单元格区域 188

4．选择活动单元格至工作表开始（结束）的区域 189

5．扩展选取选定区域 189

6．选取公式引用的所有单元格 189

7．在同行（同列）非空单元格间跳转 190

9.2　单元格格式设置与编辑技巧 190

1．当输入内容超过单元格宽度时自动调整列宽 190

2．当输入内容超过单元格宽度时自动换行 191

3．当输入内容超过单元格宽度时自动缩小字体 191

4．让单元格不显示0值 192

5．为特定单元格添加批注信息 193

6．为特定单元格设置超链接 193

7．一次清除表格中所有单元格的格式设置 194

8．将表格转化为图片 195

9．将Excel表格插入到Word中 196

10．将Excel表格插入到Word中且能自动更改 197

Chapter 10 表格数据编辑技巧 198

10.1　数据输入技巧 199

1．自定义输入日期的格式 199

2．在单元格中同时输入日期与时间 199

3．快速输入大写人民币值 200

4．快速输入大量负数 201

5．快速输入有部分重复的数据 202

6．在输入数据前自动添加特定位数小数 202

7．按指定规则进行数据填充输入 203

8．输入网址和电子邮件地址后不建立超链接 205

10.2　外部数据导入技巧 205

1．导入Excel表格数据 205

2．导入文本文件数据 207

3．引用Word表格数据 208

4．以链接形式导入Word表格数据 209

5．导入网站中的内容与数据 210

Chapter 11 常用函数使用技巧 212

11.1　文本函数使用技巧 213

1．利用LEFT、FIND函数从商品全称中提取其产地 213

2．利用多个提取函数配合IF函数从身份证号码中提取完整的出生年月日 213

3．利用IF、LEN、MID、MOD函数从身份证号码中判断性别 214

4．解决一个MID函数返回值错误的问题 215

5．利用REPT函数一次性输入多个相同符号 216

6．分离7位、8位混合显示的电话号码区号与号码 216

7．利用SEARCH、MID、LEN函数将字符串中的数据信息建立为数据表 217

8．利用LEN、SUBSTITUTE函数计算各项课程的实际参加人数 218

11.2　查找和引用函数使用技巧 218

1．使用COLUMN函数建立有规律的三级序列编号 218

2．使用ROW函数建立有规律的三级序列编号 219

3．ROW函数配合其他函数对特定行求和 219

4．使用ROW函数自动控制要显示的行数 220

5．使用LOOKUP函数（向量型语法）实现自动查询 220

6．使用LOOKUP函数（数组型语法）实现自动查询 221

7．使用HLOOKUP函数实现查询 222

8．使用VLOOKUP函数进行反向查询 223

9．配合使用INDEX与MATCH函数实现双条件查询 223

10．配合使用INDEX与MATCH函数实现反向查询 224

11．使用INDEX配合多个函数实现查询满足同一条件的所有记录 224

11.3　数学函数使用技巧 226

1．利用SUM函数统计某一经办人的总销售金额 226

2．利用SUM函数统计某两位（或多位）经办人的总销售金额 226

3．使用SUM函数统计不同时间段不同类别产品的销售金额 227

4．使用SUM函数汇总各个月份的销售总额 228

5．使用SUMIF函数统计两种类别或多种类别总销售金额 229

6．在SUMIF函数中使用通配符 229

7．使用SUMIFS函数实现多条件统计 229

8．使用SUMIFS函数统计某一日期区间的销售金额 230

9．利用SUMPRODUCT函数统计出指定类别产品的总销售额 230

10．使用SUMPRODUCT函数统计出某两位（或多位）经办人的总销售金额 231

11．使用SUMPRODUCT函数统计指定专柜中指定类别产品的销售金额合计值 231

12．解决浮点运算造成ROUND函数计算不准确的问题 232

13．使用INT、ROUND、TEXT函数让返回值保留指定位数 232

14．使用ROUNDUP函数计算物品的快递费用 233

15．使用MOD函数计算加班时长 233

16．使用多个函数将小写金额转换为大写金额 234

11.4　财务函数使用技巧 234

1．使用SLN函数（直线法）计算出固定资产的日折旧额 234

2．使用DB函数（固定余额递减法）计算出固定资产的每月折旧额 235

3．使用SYD函数（年限总和法）计算出固定资产的每年折旧额 235

4．使用DDB函数（双倍余额递减法）计算出固定资产的每年折旧额 235

5．使用PMT函数计算每期应偿还额（当支付次数为按季度（月）支付时） 236

6．使用PPMT函数计算各偿还额中包含的本金额 236

7．使用IPMT函数计算出住房贷款中每月还款利息额 237

8．使用FV函数计算住房公积金的未来值 237

9．使用NPER函数计算出某项投资的投资期数 238

10．使用NPV函数按月统计投资额现值 238

11．使用ISPMT函数计算投资期内指定月份要支付的利息额 239

12．使用IRR函数计算某项投资的内部收益率 239

13．使用MIRR函数计算某项投资的修正内部收益率 240

14．使用RATE函数计算某项保险的收益率 240

11.5　统计函数使用技巧 240

1．使用AVERAGE函数求平均值时忽略计算区域中的0值 240

2．使用AVERAGE函数按指定条件求平均值 241

3．使用AVERAGE函数对同时满足多个条件的数据求平均值 241

4．使用AVERAGE、MOD、COLUMN函数统计指定月份的平均销售金额 242

5．使用AVERAGE、LARGE函数统计数据表中前5名的平均值 242

6．在AVERAGEIF函数中使用通配符计算平均利润 243

7．使用AVERAGEIFS函数计算出满足多重条件的数据平均值 243

8．在AVERAGEIFS函数中使用通配符计算平均利润 244

9．使用LARGE函数按指定条件返回最大值 244

10．使用LARGE、SUM函数求解指定部门中前三名销售额之和 245

11．使用SMALL函数按指定条件返回最小值 245

12．使用SMALL函数求解指定部门中后三名销售额之和 246

13．使用MAX、DATE函数按条件求取最大值 246

14．使用COUNT函数按条件统计 247

15．使用COUNTA函数统计参会人名单 248

16．使用COUNTBLANK函数统计缺考人数 248

17．使用COUNTIF函数返回大于某个单元格中值的记录条数 248

18．使用COUNTIF函数返回大于平均值的记录条数 249

19．在COUNTIF函数中使用通配符计算满足条件的销售记录条数 249

20．使用COUNTIF函数统计出某一数据区间的记录条数 250

21．使用COUNTIF、SUM函数统计出满足两个或多个值的记录条数 250

22．使用COUNTIFS函数统计某时段放映指定影片类型的数目 250

23．使用RANK函数对不连续的单元格排名次 251

24．使用RANK、COUNTIF函数解决当出现相同名次时缺少名次数的问题 251

25．使用RANK、COUNTIF函数将不在同列中的数据统一排名 252

26．使用RANK、IF函数只显示满足条件的排名 253

Chapter 12 数据透视表与图表使用技巧 254

12.1　数据透视表的编辑与设置技巧 255

1．选择数据库中部分数据建立数据透视表 255

2．使用外部数据源建立数据透视表 256

3．更改数据透视表的数据源 257

4．在数据透视表中双击查看明细数据 257

5．禁止他人通过双击单元格查看明细数据 258

6．隐藏特定的记录 258

7．显示或隐藏整个报表的总计项 259

8．让数据透视表打开时自动更新 259

9．解决刷新数据透视表后所有格式都消失的问题 260

10．让数据透视表中的空白单元格显示为0 260

11．自定义公式求解各销售员奖金 261

12．自定义公式比较1、2月支出金额差额 262

13．自定义公式计算1月与2月支出费用的增长比 264

12.2　图表的编辑与设置技巧 265

1．重新更改图表数据源为不连续显示区域 265

2．按实际需要更改系列的名称 266

3．一张图表中使用两种图表类型 267

4．让图表绘制出被隐藏的数据源 268

5．以零值替代数据源的空值 268

6．使用模板新建图表 269

7．固定图表的大小与位置使其不改变 270

8．快速复制图表格式 270

9．切换行列改变图表的表达重点 270

10．保护图表不被随意更改 271

11．只允许对工作表中的指定图表进行编辑 271

12．实现让水平轴从右向左显示 272

13．重新设置刻度值的数字格式 272

14．根据图表需求更改刻度线标签的位置 273

15．重新更改分类轴标签的值 273

16．更改垂直轴与水平轴的交叉位置 274

17．让垂直轴显示在右侧 275

18．解决条形图垂直轴标签显示次序颠倒的问题 275

19．使用对数刻度绘制图表 276

20．重新建立图表数据源与数据标签的链接 277

21．隐藏特定的数据系列 278

22．设置数据系列分离（重叠）显示 278

23．更改数据系列的显示次序 279

24．设置饼图第一扇区的起始角度 279

25．将饼图中的特定扇面分离出来 280

26．重新设置复合饼图中第二绘图区的大小 281

Part 03 行业应用 282

Chapter 13　企业员工档案管理、查询、分析表 282

范例效果 283

范例概述 283

范例制作 283

13.1　创建员工档案管理表 283

13.1.1　规划员工档案表框架 283

13.1.2　表格区域单元格格式及数据有效性设置 286

13.1.3　设置公式自动返回相关信息 290

1．设置返回性别、年龄、出生日期的公式 290

2．设置计算工龄及工龄工资的公式 292

范例效果 293

范例应用 293

13.2　实现从庞大的档案数据表中查询任意员工档案 293

13.2.1 建立员工档案查询表框架 293

13.2.2　设置单元格的公式 294

13.2.3　实现快速查询 296

范例应用 296

13.3　分析企业员工年龄层次 296

13.3.1 建立数据透视表分析员工年龄层次 296

13.3.2　建立数据透视图直观显示各年龄段人数占比情况 299

范例应用 299

13.4　分析企业员工学历层次 299

13.4.1 建立数据透视表分析员工学历层次 299

13.4.2　建立数据透视图直观显示各学历人数占比情况 301

范例扩展 302

1．电话记录管理表 302

2．客户信息管理表 302

3．文件、资料借阅管理表 303

Chapter 14　企业员工考勤记录、统计、分析表 304

范例效果 305

范例概述 305

范例制作 305

14.1　创建员工考勤表 305

14.1.1 创建可选择年、月的考勤表表头 305

范例效果 308

14.1.2　设置根据当前年、月自动显示当月日期及对应星期数 308

1．建立公式返回指定月份中对应的日期与星期数 308

2．设置“星期六”、“星期日”显示为特殊颜色 310

3．验证考勤表的表头 312

14.1.3 员工考勤记录表表体的创建并按实际情况记考勤 313

范例应用 315

14.2　按员工实际考勤情况进行统计和分析 315

14.2.1 统计各员工本月请假天数、迟到次数及出勤率 315

1．建立分析标识 315

2．统计各请假类别的天数、迟到次数及出勤率 316

14.2.2　分析各部门的出勤情况 318

1．建立数据透视表分析各部门出勤情况 318

2．建立数据透视图直观比较各部门出勤情况 321

范例扩展 322

1．员工加班记录表 322

2．员工加班时长统计表 322

Chapter 15　企业产品销售数据统计、分析、查询表 323

范例效果 324

范例概述 324

范例制作 324

15.1　创建产品销售数据统计报表 324

15.1.1 新建销售记录表并设置表格格式 324

15.1.2　建立企业产品档案表 325

15.1.3　输入产品编码自动返回产品信息 325

范例应用 327

15.2　统计各类别产品的销售数据 327

范例效果 327

15.2.1 使用SUMIF函数建立公式返回统计结果 327

15.2.2　用图表直观显示各类别产品的销售占比 328

范例应用 330

15.3 分析各销售人员业绩 330

15.3.1 统计各销售人员的销售数量与销售金额 330

15.3.2　计算员工业绩奖金 331

15.3.3　用图表直观显示员工业绩奖金占比 332

范例应用 333

15.4　统计各销售人员销售各品牌产品的金额 333

范例应用 336

15.5　查询各产品的最高销售价格及对应的销售员 336

范例应用 338

15.6　查询某个品牌产品的所有销售记录 338

15.6.1 建立按类别查询销售记录的工作表 338

15.6.2　建立公式以达到自动查询的目的 339

范例扩展 342

1．采购产品统计表 342

2．产品出入库数据记录表 343

3．指定品种的出入库记录查询表 343

Chapter 16　企业员工工资统计与汇总分析表 344

范例效果 345

范例概述 345

范例制作 345

16.1　创建与工资结算金额相关的各表格并统计数据 345

16.1.1 创建员工基本工资记录表 345

范例效果 347

16.1.2　创建业绩提成、奖金、罚款记录表 347

16.1.3　创建考勤扣款统计表 350

16.1.4　创建加班工资统计表 354

范例制作 357

16.2　创建工资统计表 357

16.2.1 创建工资统计表格 357

16.2.2　计算工资表中应发金额 358

16.2.3　计算工资表中应扣金额并生成工资表 360

范例应用 363

16.3　生成员工工资单 363

16.3.1 建立起始编号员工的工资单 363

16.3.2　快速生成每位员工的工资单 366

范例应用 367

16.4　按部门汇总员工工资金额 367

范例扩展 368

1．员工医疗费用登记表 368

2．年末员工医疗费用报销统计表 369

Chapter 17　企业记账凭证汇总、查询与总分类账管理 370

范例效果 371

范例概述 371

范例制作 371

17.1　填制会计凭证管理表 371

1．填制收款凭证表 371

2．填制付款凭证表 372

3．填制转账凭证表 372

范例效果 373

范例制作 373

17.2　创建会计科目代码表 373

17.2.1 会计科目的分类 373

1．按其反映会计对象具体内容的不同分类 373

2．按其所提供信息的详细程度分类 374

17.2.2　建立会计科目表 375

1．建立明细会计科目表 375

2．建立总账科目表 377

范例制作 379

17.3　创建记账凭证汇总表 379

17.3.1 设计记账凭证汇总表 379

1．建立记账凭证汇总基本表 379

2．记账凭证汇总表的相关属性设置 381

17.3.2 自动生成会计凭证编号 383

17.3.3　根据会计科目名称自动显示科目编号 385

范例应用 388

17.4　查询记账凭证 388

17.4.1 查询收款凭证 388

17.4.2　查询付款凭证 389

17.4.3　查询现金日记账 390

范例应用 391

17.5　总分类账管理 391

17.5.1 设计总分类账表 391

17.5.2　计算本期发生额、期末数 392

17.5.3　进行试算平衡检测 395

范例扩展 397

1．资产负债表 397

2．利润表 397

3．财务比率分析表 398

Chapter 18　企业产品形象市场调查与分析表 399

范例效果 400

范例概述 400

范例制作 400

18.1　设计调查问卷和调查结果接收表 400

18.1.1 建立调查问卷 400

18.1.2　建立记录调查结果的工作表 404

范例应用 406

18.2　调查结果的汇总统计 406

18.2.1　汇总所有调查结果 406

18.2.2　替换编码生成结果数据库 407

范例应用 411

18.3　样本组成分析 411

18.3.1 性别组成分析 411

18.3.2　年龄组成分析 414

范例应用 417

18.4　品牌认知度分析 417

18.4.1 分析品牌知名度 417

18.4.2　不同地域的品牌知名度分析 419

范例应用 421

18.5 品质认知度与地域的相关性分析 421

范例应用 424

18.6　品牌忠诚度与性别的相关性分析 424

范例扩展 427

1．消费者消费行为调查问卷 427

2．消费者消费行为调查问卷结果统计表 428

3．购买时受各因素影响分析图表 428