Excel 2021实战办公一本通（视频教学版）

Excel 2021是Microsoft Office 2021的组件之一，该软件主要用来对表格数据进行管理、运算、分析、统计等处理工作，也是办公人员必学必备的办公软件之一。无论是进入企业的新人，还是企业的行政人员、销售人员、财务人员，或者企业的中高层管理者，都有必要学习Excel 2021来满足自己日常工作的需要。<br />在工作中，用户可以使用Excel 2021创建各种类型的表格，并使用函数、公式、条件格式、数据分析工具、排序、筛选、图表、数据透视表等功能统计分析办公数据。除此之外，还可以将各种功能融合在一起建立系统的管理表格，如销售数据统计分析、财务数据统计分析、员工薪酬数据统计分析、考勤加班数据统计分析、员工培训考核数据统计分析、费用支出数据统计分析、人力资源数据统计分析等，让日常工作变得得心应手、简单高效。<br />本书首先介绍数据输入与编辑、数据格式规范操作、函数与公式操作、数据分析工具的使用，然后通过大量的实操案例来强化Excel 2021的具体操作，让学习者能够循序渐进地学会、用好Excel 2021。<br />本书在策划阶段就一直站在读者的角度思考：到底以什么样的内容结构、什么样的表现形式，才能让读者在快节奏的工作、生活中易学易会、举一反三、拿来就用呢？基于这一思考，我们编写了本书。本书具有以下特色：<br />由浅入深更易学：本书第1～4章详细介绍Excel 2021表格管理的基础知识，通过简洁易懂的语言向读者清晰地展示如何规范表格结构、规范数据输入，以及如何应用函数、公式和数据分析工具提高数据分析能力，第5～12章将这些基础知识应用到实例中，并结合其他实用功能，建立完善的办公管理表格。<br />实操案例更丰富：本书结合具体案例进行讲解，如销售数据统计分析、财务数据统计分析、员工薪酬数据统计分析等。案例的素材都是真实的工作数据，这样读者既可以即学即用，又可以获得宝贵的真实操作经验与注意事项的专业点拨等。<br />图解操作更直观：本书采用图解模式逐一介绍每个表格的创建过程，同时提供知识拓展、高手指引等解释步骤中的操作，清晰直观，简洁明了。<br />本书提供视频文件、素材文件并赠送相关资源，读者可以进入QQ交流群（650326150）获取。如果有技术问题，也可以发送电子邮件到booksaga@126.com。<br />本书由吴祖珍策划，邹县芳老师（阜阳师范大学）和孟龙辉老师（广德市教育体育局）共同编写完成，第1～4章和第12章由孟龙辉老师编写，第5～11章由邹县芳老师编写。尽管编者对书中的内容精雕细琢，但疏漏之处仍然在所难免，敬请广大专家和读者不吝指正。<br />再次感谢您的支持！<br /><br />编者　　<br />2022年3月

本书共分为12章，分别介绍数据输入与编辑、数据格式规范、函数与公式、数据分析工具、销售数据统计分析、财务数据统计分析、员工薪酬数据统计分析、考勤加班数据统计分析、员工培训考核数据统计分析、费用支出数据统计分析、人力资源数据统计分析以及实用办公管理表格等内容。
本书适合不同的读者阅读。对于Excel 2021初学者，通过阅读本书能够快速掌握Excel 2021的功能和操作；对于职场办公人员，通过学习本书能够尽快掌握利用表格提高办公效率的方法；对于具备一定Excel 2021操作基础的读者，通过学习本书中的办公实操案例，能够进一步提高在实际办公中应用Excel 2021的水平。

高级讲师，参与编写《好用，Excel人事管理高手》《Excel在人力资源和行政管理中的应用》《Excel 2019公式与函数应用大全（视频教学版）》《Excel人力资源管理必须掌握的268个文件与248个函数》《Excel财务管理必须掌握的268个文件与248个函数》等书。

目　　录
前言
第1章　数据输入与编辑1
1.1　输入不同类型的数据1
1.1.1　输入数值型数据1
1.1.2　输入文本型数据3
1.1.3　输入日期和时间数据5
1.1.4　输入百分比数据7
1.2　批量输入数据的技巧8
1.2.1　快速填充日期8
1.2.2　快速填充规则数据10
1.2.3　按指定范围填充11
1.3　数据验证在数据输入中的应用12
1.3.1　在下拉列表中输入数据12
1.3.2　限制输入的数据类型15
1.3.3　在数据验证中使用公式16
1.4　实用办公技能：选择性粘贴18
1.4.1　复制和粘贴数据18
1.4.2　保持两张表的数据链接19
1.4.3　在粘贴时进行数据计算20
1.5　数据查找与替换22
1.5.1　查找并替换数据22
1.5.2　替换数据后更改格式23
1.6　导入外部数据24
1.6.1　导入文本数据24
1.6.2　导入网页表格数据26
第2章　数据格式规范28
2.1　遵循表格数据规范28
2.1.1　规范数据格式28
2.1.2　规范表格结构30
2.1.3　保持数据的统一性31
2.1.4　保持数据的连续性31
2.1.5　学会整理二维表格数据32
2.2　不规范表格数据的处理34
2.2.1　快速处理空白行或列34
2.2.2　处理空值技巧37
2.2.3　处理不规范日期38
2.2.4　处理不规范文本41
2.2.5　处理文本数据42
2.2.6　处理重复值和记录43
2.2.7　处理一格多属性数据45
第3章　函数与公式47
3.1　单元格引用方式47
3.1.1　相对引用48
3.1.2　绝对引用49
3.2　编辑函数与公式49
3.2.1　函数结构与种类50
3.2.2　函数输入技巧52
3.2.3　快速复制公式53
3.3　名称的应用55
3.3.1　定义名称56
3.3.2　使用名称56
3.4　常用函数应用实例58
3.4.1　应用IFS函数进行多条件判断58
3.4.2　应用IF函数判断指定条件的真假60
3.4.3　应用WEEKDAY函数计算日期为星期几61
3.4.4　应用DATEDIF函数计算起始日和结束日之间的天数62
3.4.5　应用EDATE函数计算间隔指定月份数后的日期63
3.4.6　应用HOUR函数计算时间值的小时数65
3.4.7　应用AVERAGEIFS函数按指定条件求平均值66
3.4.8　应用SUMIF函数按指定条件求和66
3.4.9　应用COUNTIF函数单条件统计数据个数67
3.4.10　应用MAXIFS/MINIFS函数按条件求大值/小值69
3.4.11　应用VLOOKUP函数查找目标数据70
3.4.12　应用LOOKUP函数查找并返回同一位置的值73
3.4.13　应用INDEX函数从引用或数组中返回指定位置的值75
第4章　数据分析工具79
4.1 模拟运算79
4.1.1　单变量模拟运算79
4.1.2　双变量模拟运算80
4.2　规划求解配置82
4.2.1　安装规划求解加载项82
4.2.2　适合使用规划求解来解决的问题范畴83
4.2.3　利用规划求解功能小化运输成本85
4.3　数据的相关性分析87
4.3.1　方差分析：分析员工学历对综合考评能力的影响87
4.3.2　方差分析：分析哪种因素对生产量有显著性影响89
4.3.3　相关系数：分析产量和施肥量是否有相关性91
4.3.4　相关系数：分析“完成数量”“合格数”“奖金”三者之间的相关性92
4.3.5　协方差：分析数据的相关性92
4.3.6　回归分析：两个因素之间的依赖关系93
4.3.7　回归分析：多个因素之间的依赖关系95
4.4　其他统计工具96
4.4.1　描述统计工具分析员工考核成绩的稳定性97
4.4.2　指数平滑工具预测产品的生产量98
4.4.3　抽样工具抽取样本99
4.4.4　t-检验（双样本等方差假设）工具100
4.4.5　z-检验（双样本平均差检验）工具102
4.4.6　F-检验（双样本方差）工具104
第5章　销售数据统计分析106
5.1　销售记录汇总表106
5.1.1　产品基本信息表106
5.1.2　销售记录汇总表107
5.1.3　建立公式返回基本信息107
5.1.4　计算销售额、折扣、交易金额109
5.2　销售数据分析报表111
5.2.1　分析各系列商品的销售额数据111
5.2.2　分析各系列商品销售额的占比数据113
5.2.3　创建图表分析各系列商品销售额的占比114
5.2.4　分析单日销售额数据116
5.2.5　分析畅销商品数据117
5.3　入库记录表118
5.4　库存汇总、查询、预警120
5.4.1　库存汇总表120
5.4.2　库存量查询表124
5.4.3　应用条件格式查询库存量127
5.5　本期利润分析表128
5.6　计划销量与实际销量对比图131
5.7　创建图表分析产品销售增长率134
第6章　财务数据统计分析137
6.1　应收账款的统计137
6.1.1　应收账款记录表137
6.1.2　筛查已逾期账款140
6.1.3　处理已冲销账款141
6.2　应收账款分析报表142
6.2.1　计算各账款的账龄143
6.2.2　统计各客户应收账款145
6.2.3　统计各账龄应收账款150
6.2.4　创建图表分析应收账款账龄152
6.3　管理应付账款154
6.3.1　应付账款记录表154
6.3.2　分析各项应付账款数据155
6.3.3　汇总统计各供应商的总应付账款156
6.4　专用财务函数实例159
6.4.1　计算贷款的每期偿还额159
6.4.2　计算贷款每期偿还额中包含的本金金额160
6.4.3　计算贷款每期偿还额中包含的利息额161
6.4.4　计算投资的未来值162
6.4.5　计算投资的期数162
第7章　员工薪酬数据统计分析164
7.1　基本工资表164
7.1.1　计算员工工龄164
7.1.2　计算工龄工资166
7.2　个人所得税表168
7.2.1　计算待缴工资额168
7.2.2　计算所得税表明细169
7.3　月度薪资核算表171
7.3.1　应用“基本工资表”数据171
7.3.2　计算实发工资173
7.4　建立工资条176
7.4.1　定义名称176
7.4.2　生成工资条177
7.5　创建薪资分析报表179
7.5.1　按部门汇总工资额180
7.5.2　创建图表比较各部门平均工资182
7.5.3　按工资区间汇总工资184
第8章　考勤加班数据统计分析187
8.1　月度考勤表187
8.1.1　表头日期的填制187
8.1.2　周末日期的特殊显示190
8.1.3　填制考勤表192
8.2　考勤统计表192
8.2.1　本月出勤数据统计193
8.2.2　计算满勤奖与应扣金额195
8.3　考勤数据分析表197
8.3.1　月出勤率分析表197
8.3.2　日出勤率分析表199
8.3.3　各部门缺勤情况分析表201
8.3.4　月满勤率透视分析表203
8.4　加班记录表205
8.4.1　根据加班日期判断加班类型205
8.4.2　统计加班总时数207
8.5　加班分析报表208
8.5.1　加班费核算表209
8.5.2　车间加班费统计分析表213
8.5.3　加班数据季度汇总表214
第9章　员工培训考核数据统计分析217
9.1　员工培训记录汇总表217
9.1.1　按员工统计培训课时数与金额217
9.1.2　按部门统计培训课时数与金额220
9.1.3　统计各项培训课程参加人数221
9.2　员工培训成绩统计分析表222
9.2.1　计算培训总成绩、平均成绩、名次223
9.2.2　突出显示优秀培训者的姓名226
9.2.3　给优秀成绩插红旗227
9.2.4　筛选“不达标”的培训记录229
9.2.5　筛选总成绩前5名的记录231
第10章　费用支出数据统计分析233
10.1　费用支出统计表233
10.1.1　设置数据验证233
10.1.2　筛选指定类别费用235
10.2　创建费用统计报表236
10.2.1　按费用类别统计支出额236
10.2.2　按部门统计支出额239
10.2.3　按部门及类别统计支出额240
10.2.4　按月统计支出额240
10.2.5　按月统计各部门支出额241
10.2.6　按费用类别统计支出次数242
10.3　实际支出与预算比较表243
10.3.1　实际费用与预算费用比较分析表243
10.3.2　计算各分析指标244
10.3.3　筛查超支项目248
第11章　人力资源数据统计分析250
11.1　人事信息数据表250
11.1.1　整表限制输入空格250
11.1.2　快速填充工号252
11.1.3　建立公式返回基本信息253
11.2　员工信息查询表256
11.2.1　创建员工信息查询表256
11.2.2　建立查询公式258
11.2.3　查询员工信息260
11.3　分析员工年龄、学历及稳定性261
11.3.1　分析员工年龄261
11.3.2　分析员工学历264
11.3.3　分析员工的稳定性267
11.4　在职员工结构报表268
11.4.1　定义名称269
11.4.2　统计各部门各性别人数270
11.4.3　统计各部门各学历人数272
11.4.4　统计各部门各年龄段人数273
11.4.5　统计各部门各工龄段人数274
11.4.6　分析人员流动情况276
第12章　实用办公管理表格278
12.1　实用行政表格278
12.1.1　办公用品采购申请表278
12.1.2　办公用品领用管理表284
12.1.3　应聘人员信息登记表288
12.1.4　新员工试用表292
12.2　实用财务表格296
12.2.1　会议费用支出报销单296
12.2.2　业务招待费用报销明细表299
12.2.3　差旅费预支申请表302
12.2.4　公司日常运营费用预算及支出比较表304