Chapter 01 学习使用Excel 2010工作簿与工作表
素材路径：随书光盘\素材\Chapter 01\
视频路径：随书光盘\视频教程\Chapter 01\
1.1工作簿的创建与保存2
1.1.1创建新工作簿2
1.1.2保存工作簿4
1.2Excel 2010工作表的基本操作5
1.2.1插入与删除工作表5
1.2.2移动与复制工作表6
1.2.3 重命名工作表7
1.2.4 通过工作表标签颜色体现工作表的不同重要性8
1.2.5 隐藏和显示工作表8
1.2.6 调整工作表的显示比例9
1.3单元格的基本操作10
1.3.1 插入和删除单元格10
1.3.2 插入行或列10
1.3.3 合并单元格12
1.3.4 调整单元格的高度与宽度12
1.3.5 隐藏含有重要数据的行或列13
1.4保护工作表与工作簿数据13
1.4.1 保护工作表13
1.4.2加密保护工作簿14
Chapter 02 电子表格中数据的输入与编辑
素材路径：随书光盘\素材\Chapter 02
视频路径：随书光盘\视频教程\Chapter 02\
2.1不同类型数据的输入17
2.1.1了解常规格式数据与文本格式数据17
2.1.2输入不同的数值（小数、百分比、货币）18
2.1.3输入日期和时间数据19
2.1.4输入特殊符号21
2.1.5用填充功能批量输入21
2.1.6导入外部数据23
2.2表格数据的修改、移动、复制与删除25
2.2.1修改数据25
2.2.2移动数据25
2.2.3复制数据26
2.2.4利用“粘贴预览”功能提前预览粘贴效果26
2.2.5删除数据27
2.3查找与替换数据28
2.3.1表格数据的查找28
2.3.2表格数据的替换29
Chapter 03 公式、函数计算的基础知识
素材路径：随书光盘\素材\Chapter 03\
视频路径：随书光盘\视频教程\Chapter 03\
3.1使用公式进行数据计算32
3.1.1什么是公式32
3.1.2公式运算符32
3.1.3 输入公式33
3.1.4编辑公式33
3.1.5复制公式完成批量计算34
3.2公式计算中函数的使用34
3.2.1函数的构成34
3.2.2函数的参数及其说明35
3.2.3了解函数类型与函数的帮助功能35
3.2.4插入函数时使用“插入函数”向导36
3.3公式计算中对单元格的引用39
3.3.1引用相对数据源计算39
3.3.2 引用**数据源计算40
3.3.3引用其他工作表中的单元格41
3.3.4引用其他工作簿中的单元格41
3.4公式中对名称的使用42
3.4.1将公式中要使用的单元格区域定义为名称42
3.4.2将定义的名称作为函数参数使用43
3.5公式检测与审核44
3.5.1追踪引用单元格44
3.5.2追踪从属单元格44
3.5.3“错误检查”功能45
3.5.4公式分步求值45
3.6公式返回错误值分析与解决办法46
3.6.1“#DIV/0!”错误值46
3.6.2“#N/A”错误值47
3.6.3“#NAME?”错误值47
3.6.4“#NUM!”错误值48
3.6.5“#VALUE!”错误值48
3.6.6“#REF!”错误值49
Chapter 04 使用图表直观分析数据
素材路径：随书光盘\素材\Chapter 04\
视频路径：随书光盘\视频教程\Chapter 04\
4.1初识图表51
4.1.1了解图表结构51
4.1.2各图表类型应用范围解析52
4.2创建新图表56
4.2.1按分析目的创建针对性图表57
4.2.2迷你图的应用59
4.2.3变更图表类型60
4.2.4变更图表数据源60
4.3图表大小及位置调整62
4.3.1调整图表大小62
4.3.2更改图表位置62
4.4图表的复制与删除63
4.4.1以链接方式复制图表63
4.4.2将图表复制为图片64
4.4.3删除图表65
4.5图表元素的添加65
4.5.1添加图表标题65
4.5.2重新设置坐标轴65
4.5.3应用数据标签66
4.6图表的美化68
4.6.1设置图表文字格式68
4.6.2设置图表中对象的填充效果69
4.6.3设置图表中对象的形状特效70
4.6.4套用图表样式快速美化图表72
Chapter 05 数据透视表与数据透视图
素材路径：随书光盘\素材\Chapter 05\
视频路径：随书光盘\视频教程\Chapter 05\
5.1了解数据透视表74
5.1.1数据透视表的作用及使用场合74
5.1.2了解数据透视表的结构74
5.2创建数据透视表分析数据75
5.2.1创建数据透视表75
5.2.2神奇的字段位置77
5.3数据透视表的编辑、排序和筛选79
5.3.1设置字段汇总方式、值显示方式79
5.3.2对数据透视表统计结果进行排序81
5.3.3在数据透视表筛选查看部分数据82
5.3.4数据透视表的移动、刷新、删除、转换84
5.3.5一键美化数据透视表85
5.4用数据透视图直观查看分析结果86
5.4.1创建数据透视图86
5.4.2优化数据透视图86
5.4.3数据透视图筛选查看88
Chapter 06 高级分析工具的应用
素材路径：随书光盘\素材\Chapter 06\
视频路径：随书光盘\视频教程\Chapter 06\
6.1安装与卸载分析工具库90
6.1.1安装分析工具库90
6.1.2卸载分析工具库91
6.2模拟运算表91
6.2.1单变量模拟运算91
6.2.2双变量模拟运算92
6.2.3将模拟运算结果转换为常量93
6.2.4删除模拟运算结果94
6.3单变量求解94
6.3.1单变量求解预定销售计划94
6.3.2单变量求解可贷款金额95
6.3.3单变量求解贷款年限96
6.4方案管理器97
6.4.1建立方案97
6.4.2显示方案99
6.4.3合并方案99
6.4.4编辑方案101
6.4.5创建方案摘要101
6.5规划求解103
6.5.1规划求解确定**生产方案103
6.5.2规划求解确定**运输方案105
Chapter 07 逻辑、文本函数应用技巧
素材路径：随书光盘\素材\Chapter 07\
7.1逻辑、文本函数功能介绍110
1．AND函数110
2．NOT函数110
3．OR函数110
4．IF函数110
5．CONCATENATE函数110
6．FIND函数111
7．LEFT函数111
8．LEN函数111
9．MID函数111
10．REPT函数111
11．RIGHT函数112
12．SEARCH函数112
13．SUBSTITUTE函数112
14．TEXT函数112
7.2逻辑函数范例113
1．判断给定值是否在指定区间113
2．根据多项成绩判断*终考评结果是否合格113
3．根据多项培训成绩判断员工是否参加二次考评113
4．快速比较各商品两个月份的采购价格是否相同114
5．库存量过小时提示补货114
6．根据消费卡类别与消费额派发赠品115
7．根据职工性别和职务判断退休年龄116
8．计算个人所得税117
7.3文本函数范例118
1．合并商品货号、码数及颜色 118
2．从商品全称中提取产地118
3．检验具有固定长度的字符串输入的位数是否正确119
4．一次性输入多个相同符号119
5．分离电话号码的区号与号码119
6．从产品编码中提取产品的货号 120
7．去掉多余的空格120
8．根据人员姓名统计各项课程的实际参加人数121
9．根据商品的名称与颜色进行一次性调价121
10．发票金额的分列填写122
11．从项目编码中提取合同号123
12．合并编码时不忽略前面的0123
13．让合并的日期显示正确格式124
14．让计算得到金额显示为“余款：15,850.00”形式124
15．解决总金额无法计算的问题125
Chapter 08 数学函数应用技巧
素材路径：随书光盘\素材\Chapter 08
8.1日期、时间函数功能介绍127
1．DATE函数127
2．DATEDIF函数127
3．DAY函数127
4．DAYS360函数127
5．EOMONTH函数128
6．MONTH函数128
7．NETWORKDAYS函数128
8．TODAY函数128
9．WEEKDAY函数129
10．WORKDAY函数129
11．YEAR函数129
12．HOUR函数129
13．MINUTE函数129
14．SECOND函数130
8.2日期函数范例130
1．返回当前日期与星期数130
2．已知一年中的第几天，返回其完整日期130
3．创建倒计时牌130
4．将文本日期转换为标准日期131
5．利用TODAY函数进行账龄统计131
6．计算总借款天数132
7．判断借款是否逾期132
8．自动填写报表中的月份132
9．根据出生日期快速得出年龄133
10．计算借款至今日时长（显示为*年*月*日形式）133
11．根据工龄自动追加工龄工资134
12．返回值班日期对应的星期数134
13．判断值班日期是平时加班还是双休日加班135
14．计算请假天数占全年工作日的百分比135
15．计算指定日期到月底的天数136
16．实现查询指定年份中各月的天数136
8.3时间函数范例137
1．计算来访签到时间的区间137
2． 计算停车时间137
3．计算通话秒数137
4． 计算高于或低于标准时间的值138
Chapter 09 日期、时间函数应用技巧
素材路径：随书光盘\素材\Chapter 09\
9.1数学函数功能介绍140
1．SUM函数140
2．SUMIF函数140
3．SUMIFS函数140
4．SUMPRODUCT函数140
5．ABS函数141
6．INT函数141
7．MOD函数141
8．CEILING函数141
9．FLOOR函数141
10．ROUND函数141
11．ROUNDDOWN函数142
12．ROUNDUP函数142
9.2求和函数范例142
1．根据各月份预算费用一步计算总预算费用142
2．得知每个课程的人数与价格，用SUM函数求取总金额142
3．利用SUM函数统计某一经办人的总销售金额143
4．统计每个月的出库总数量143
5．统计各部门工资总额144
6．利用SUMIF函数统计某个时段的销售总金额144
7．在SUMIF函数中使用通配符145
8．计算销售金额前三名合计值145
9．统计指定地点指定时间的销售金额145
10．统计某一日期区间的销售金额146
11．根据销售数量与单价计算总销售额 146
12．统计指定类别产品的总销售额（表格中只统计了销售数量与单价）146
13．统计某两位（或多位）经办人的总销售金额147
14．统计指定部门获取奖金的人数（去除空值）147
15．统计指定班级分数大于指定值的人数148
16．统计指定店铺各品牌产品的销售金额合计值148
17．使用SUMPRODUCT函数统计非工作日销售金额149
9.3其他数学函数范例149
1．ABS函数在数据比较中的应用149
2．平均销售数量时取整数149
3．正数显示总支出金额150
4．计算每位员工的加班时长150
5．以1个百分点为单位计算奖金或扣款151
6．计算物品的快递费用151
7．计算上网费用 152
8．以6秒（不足6秒按6秒计算）为单位计算通话费用153
9．计件工资中的奖金计算153
10．计算商品运送车次153
11．解决浮点运算造成ROUND函数计算不准确的问题154
Chapter 10 统计函数应用技巧
素材路径：随书光盘\素材\Chapter 10\
10.1统计函数功能介绍156
1．AVERAGE函数156
2．AVERAGEA函数156
3．AVERAGEIF函数156
4．AVERAGEIFS函数156
5．MAX函数156
6．LARGE函数157
7．MIN函数157
8．SMALL函数157
9．MODE.MULT函数157
10．COUNT函数157
11．COUNTA函数157
12．COUNTBLANK函数158
13．COUNTIF函数158
14．COUNTIFS函数158
15．MEDIAN函数158
16．PERCENTILE.INC函数158
17．RANK.EQ函数159
10.2求平均值函数范例159
1．忽略0值求平均分数159
2．统计各班级平均分159
3．统计数据表中前5名的平均值160
4．统计各部门的平均工资160
5．计算每月平均出库数量161
6．计算销售利润时使用通配符 161
7．计算一车间女职工平均工资162
8．计算指定班级平均分并且忽略0值162
9．求介于某一区间内的平均值162
10．统计指定店面所有男装品牌的平均利润163
10.3求**/*小值函数范例163
1．利用LARGE函数按指定条件返回**值163
2．利用LARGE函数求解指定部门中前3名销售额之和164
3．利用SMALL函数按指定条件返回*小值165
4．快速返回所有店铺**销售金额与**销售金额165
5．返回上半个月单笔**销售金额166
6．返回企业女员工的**年龄166
7．忽略0值求*小值167
10.4数目统计函数范例167
1．统计成绩表中缺考的人数167
2．根据参加课程人员的姓名统计总人数168
3．统计测试时无故障的机器台数168
4．统计大于某个单元格中值的记录条数168
5．统计成绩大于平均分数的学生人数169
6．使用通配符统计某一类数据的记录条数169
7．统计是三好学生且参加数学竞赛的人数170
8．统计各店面男装的销售记录条数（双条件）170
9．统计指定时间指定类别商品的销售记录数171
10．统计指定类型指定影院指定时间的影片放映数量 171
11．统计指定产品每日的销售记录数172
12．统计一组数据中出现次数*多的数据172
13．一次性统计一组数据中各数据出现的次数 173
10.5数据排位函数范例173
1．为各销售员的全年销售金额排名次173
2．对不连续单元格排名次174
3．将不在同一 列中的数据统一排名174
4．只显示满足条件的排名175
5．返回数值区域的K百分比数值点175
6．统计全年各月利润值中的中位数176
Chapter 11 财务函数应用技巧
素材路径：随书光盘\素材\Chapter 11\
11.1财务函数功能介绍178
1．FV函数178
2．PV函数178
3．NPV函数178
4．XNPV函数178
5．PMT函数179
6．PPMT函数179
7．IPMT函数179
8．ISPMT函数179
9．NPER函数180
10．RATE函数180
11．XIRR函数180
12．IRR函数180
13．MIRR函数180
14．DB函数181
15．DDB函数181
16．SLN函数181
17．SYD函数181
18．VDB函数182
11.2投资计算函数应用182
1．当支付次数为按季度（月）支付时计算每期应偿还额182
2．快速计算各年偿还额中包含的本金182
3．快速计算各期偿还额中包含的利息额183
4．计算住房贷款中每月还款利息额183
5．在等额本金还款方式下计算某项贷款的利息184
6．计算住房公积金的未来值 184
7．计算某项投资的投资期数184
8．判断购买某项保险是否合算185
9．计算某投资的净现值185
10．计算一组不定期盈利额的净现值186
11.3偿还率计算函数应用186
1．计算某项投资的内部收益率186
2．计算一组不定期盈利额的内部收益率186
3．计算某项投资的修正内部收益率187
4．计算购买某项保险的收益率 187
11.4折旧计算函数应用188
1．直线法计算固定资产的每月折旧额188
2．直线法计算固定资产的每天折旧额188
3．固定余额递减法计算每年折旧188
4．固定余额递减法计算固定资产的每月折旧额189
5．年限总和法计算每年折旧189
6．双倍余额递减法计算每年折旧额189
7．利用直线法计算累计折旧额190
8．利用双倍余额递减法计算累计折旧额190
Chapter 12 查找、数据库函数应用技巧
素材路径：随书光盘\素材\Chapter 12\
12.1查找、数据库函数功能介绍193
1．ADDRESS函数193
2．CHOOSE函数193
3．COLUMN函数193
4．COLUMNS函数193
5．HLOOKUP函数194
6．INDEX函数194
7．INDIRECT函数194
8．LOOKUP函数（向量型语法）195
9．LOOKUP函数（数组型语法）195
10．MATCH函数195
11．OFFSET函数195
12．ROW函数196
13．ROWS函数196
14．VLOOKUP函数196
15．DSUM函数196
16．DAVERAGE函数197
17．DCOUNT函数197
18．DCOUNTA函数197
19．DGET函数197
20．DMAX函数198
21．DMIN函数198
12.2查找和引用函数范例198
1．使用LOOKUP函数（向量型语法）实现自动查询198
2．使用LOOKUP函数（数组型语法）实现自动查询199
3．使用HLOOKUP函数查询某一科目的成绩200
4．根据值班日期自动返回工资标准200
5．在销售表中自动返回产品单价201
6．根据多条件计算员工年终奖201
7．使用VLOOKUP函数进行反向查询202
8．查找指定月份指定专柜的销售金额202
9．返回成绩**的学生的姓名203
10．查询总金额**的对应的店铺（反向查询）203
11．求取一组数据的反转数据204
12．根据产品不合格率决定产品处理办法204
13．查找**销售额所在位置 205
14．解决合并单元格引用数据列出现跳跃的问题205
15．使用ROW函数控制年份序列的显示行数206
16．按奇数月与偶数月统计销量206
17．实现数据的动态查询207
18．对每日出库数量累计求和208
12.3数据库函数范例208
1．使用DSUM函数实现满足双条件求和208
2．计算总工资时去除某一个（或多个）部门209
3．使用通配符统计一类数据之和209
4．避免DSUM函数的模糊匹配210
5． 计算指定班级平均分211
6．计算指定车间指定性别员工的平均工资（双条件）211
7．查询指定班级各科目平均成绩 211
8．统计满足条件的记录条数212
9．统计同时满足两个条件时的记录条数212
10．从成绩表中统计某一分数区间的人数213
11．忽略0值统计记录条数213
12．统计满足指定条件的且为“文本”类型的记录条数213
13．统计指定性别测试合格的人数214
14．返回指定车间指定性别员工的**工资214
15．查询指定班级各科目中的**分214
16．从数据库中提取符合条件的单个值215
Chapter 13 图表使用中的常用技巧
素材路径：随书光盘\素材\Chapter 13\
13.1图表设置技巧217
1．按实际需要更改系列名称217
2．重新调整数据系列的显示次序217
3．一张图表使用两种图表类型218
4．让图表绘制出被隐藏的数据源218
5．以0值替代数据源的空值219
6．设置竖排标题219
7．让图表标题与表格标题同步更新220
8．将垂直轴显示在图表正中位置220
9．让水平轴标签从右向左显示221
10．解决条形图垂直轴标签显示次序颠倒的问题221
11．根据图表需求更改刻度线标签的位置222
12．设置数据系列分离（重叠）显示223
13．将看不到的系列绘制于次坐标轴上223
14．解决更改图表数据源数据标签不能同步更改的问题225
15．使用对数刻度绘制图表225
16．使创建的复合饼图达到表达效果226
17．固定图表的大小与位置让其不改变227
18．快速取消图表的所有格式227
13.2图表安全保护及协同使用227
1．隐藏工作表中的图表227
2．只允许对指定图表进行编辑228
3．保护图表不被随意更改228
4．实现在Word中编辑Excel图表228
5．在PowerPoint演示文稿中使用Excel图表229
Chapter 14 公式、函数、图表在考勤管理与分析中的应用
素材路径：随书光盘\素材\Chapter 14\
视频路径：随书光盘\视频教程\Chapter 14\
|范例制作|
14.1创建考勤表记录员工出勤情况231
14.1.1创建可自由选择年、月的考勤表表头231
14.1.2根据当前年、月自动显示当月日期及对应星期数234
14.1.3创建考勤记录表表体并按实际情况记录考勤238
|范例制作|
14.2汇总每位员工本月出勤数据240
14.2.1统计各员工本月出勤数据240
14.2.2计算满勤奖、应扣工资242
|范例应用|
14.3比较分析本月各部门缺勤情况243
14.3.1建立数据透视表分析各部门缺勤情况243
14.3.2建立数据透视图直观比较缺勤情况244
14.4本月出勤率计算及趋势分析246
14.4.1计算每日出勤率246
14.4.2建立全月出勤率走势折线图248
|范例扩展|
14.5员工考勤表249
14.6员工请假流程图250
Chapter 15 公式、函数、图表在加班管理中的应用
素材路径：随书光盘\素材\Chapter 15\
视频路径：随书光盘\视频教程\Chapter 15\
15.1统计分析逐条记录的加班情况252
|范例制作|
15.1.1建立加班记录表252
15.1.2计算加班费254
15.1.3不同加班类型的加班时长比较256
1．创建数据透视表统计加班时长256
2．创建数据透视图直观比较各员工加班情况257
15.1.4每位员工加班总时数比较图表258
15.1.5不同加班类型总时数比较图表258
|范例制作|
15.2统计分析按考勤方式记录的加班情况259
15.2.1建立加班记录表259
15.2.2加班时长统计表及加班费的计算261
|范例应用|
15.2.3查看本月未加班的员工263
15.2.4各部门的加班费比较分析264
|范例扩展|
15.3加班申请单266
15.4值班提醒表266
Chapter 16 公式、函数、图表在档案管理中的应用
素材路径：随书光盘\素材\Chapter 16\
视频路径：随书光盘\视频教程\Chapter 16\
|范例制作|
16.1建立档案管理表格268
16.1.1建立员工档案表268
16.1.2设置公式自动返回相关信息271
|范例应用|
16.2输入任意员工姓名查询其档案274
16.2.1建立员工档案查询表框架274
16.2.2设置单元格的公式并实现查询276
16.3员工学历层次的分析277
16.3.1建立数据透视表分析员工学历层次277
16.3.2用数据透视图直观显示各学历占比情况278
16.4员工年龄层次的分析279
16.4.1建立数据透视表分析员工年龄层次279
16.4.2用数据透视图直观显示各年龄段人数占比281
16.5分析员工稳定性281
16.5.1建立数据透视表统计各工龄段的人数282
16.5.2用数据透视图直观显示各工龄段人数占比283
|范例扩展|
16.6员工个人资料登记表283
16.7管理人才储备表284
Chapter 17 公式、函数、图表在销售管理及业绩考核中的应用
素材路径：随书光盘\素材\Chapter 17\
视频路径：随书光盘\视频教程\Chapter 17\
|范例制作|
17.1建立产品销售数据统计表286
17.1.1建立企业产品备案表286
17.1.2创建销售记录表286
17.1.3设置公式实现只输入产品编码自动返回品牌、产品名称等信息287
|范例应用|
17.2统计各类别产品的销售数据288
17.2.1使用SUMIF函数建立公式288
17.2.2用图表直观显示各类别产品的销售占比290
17.3分析各销售人员业绩291
17.4统计各销售员销售各品牌产品的金额293
17.4.1使用SUMPRODUCT函数建立公式293
17.4.2建立图表直观比较各品牌商品销售金额与各销售员金额295
17.5查询各产品的**售价及对应的销售员296
17.6销售员全年业绩排名、等级、趋势分析298
17.6.1建立变化趋势迷你图299
17.6.2计算平均销售金额、总销售金额、排名次300
|范例扩展|
17.7销售单据302
17.8销售费用计算报表302
Chapter 18 公式、函数、图表在库存管理、分析与查询中的应用
素材路径：随书光盘\素材\Chapter 18\
视频路径：随书光盘\视频教程\Chapter 18\
|范例制作|
18.1建立入库数据管理表与出库数据管理表304
18.1.1建立商品基本信息表304
18.1.2建立入库数据管理表304
18.1.3建立出库数据管理表307
18.2建立库存数据统计表308
18.2.1设置公式返回产品基本信息308
18.2.2设置公式计算本期入库、出库与库存310
|范例应用|
18.3出、入库数据统计分析312
18.3.1从各供应商处采购汇总313
18.3.2采购的各系列产品的汇总314
18.3.3比较各系列商品的出、入库数据315
18.3.4查询期末库存较小的记录317
18.4根据本期出、入库数据计算销售成本、利润等318
|范例扩展|
18.5 进货单据321
18.6 商品进销存明细表321
Chapter 19 动态图表的应用分析——按钮调节式
素材路径：随书光盘\素材\Chapter 19\
视频路径：随书光盘\视频教程\Chapter 19\
|范例制作|
19.1选择公司对各月数据进行比较323
19.1.1建立动态图表数据源323
19.1.2添加控件按钮控制可变单元格的值325
19.1.3 建立动态图表326
19.2选择某月份对各公司数据进行比较328
19.2.1建立动态图表数据源328
19.2.2添加控件按钮控制可变单元格的值330
19.2.3建立动态图表331
|范例扩展|
19.3创建列表框式动态折线图333
Chapter 20 动态图表的应用分析——下拉列表式
素材路径：随书光盘\素材\Chapter 20\
视频路径：随书光盘\视频教程\Chapter 20\
|范例制作|
20.1建立动态图表数据系列并创建图表335
20.1.1 建立辅助序列335
20.1.2将图表数据系列定义为名称336
20.1.3使用定义的名称建立图表337
20.2添加控件控制图表的显示339
20.2.1添加组合框控制“销售金额”、“首付金额”、“销售套数”339
20.2.2添加组合框控制“一居”、“二居”等户型339
20.3图表优化设置340
20.3.1添加文本框到图表中340
20.3.2添加百分比数据标签并显示两位小数341
20.3.3动态图表美化及效果测试342
20.4将图表与图表内各对象组合为一个对象342
|范例扩展|
20.5创建单选项式动态圆环图343
鄂公网安备 42010302001612号 