Excel 2019公式与函数应用大全:视频教学版电子书

前言

第1章 认识Excel公式

1.1 认识公式

1.1.1 公式的组成

1.1.2 公式中运算符的使用

1.1.3 公式输入与修改

1.1.4 公式的复制

1.2 认识函数

1.2.1 函数的巨大作用

1.2.2 参数设置要符合规则

1.2.3 初学者如何学习函数

1.2.4 正确输入函数的方法

1.2.5 修改与保存函数公式

1.3 嵌套函数

1.4 了解数组公式

1.4.1 普通公式与数组公式的区别

1.4.2 多个单元格数组公式

1.4.3 单个单元格数组公式

1.4.4 修改或删除数组公式

1.5 公式的保护与设置

第2章 数据源的引用方式

2.1 不同的单元格引用方式

2.1.1 相对引用

2.1.2 绝对引用

2.1.3 混合引用

2.1.4 引用当前工作表之外的单元格

2.1.5 引用多个工作表中的同一单元格

2.1.6 引用其他工作簿中的单元格

2.2 了解名称定义功能

2.2.1 定义名称的作用

2.2.2 定义名称的方法

2.2.3 公式中应用名称

2.2.4 定义公式为名称

2.2.5 创建动态名称

2.2.6 管理定义的名称

第3章 常见错误公式修正及错误值分析

3.1 公式检测与审核

3.1.1 利用“公式求值”按钮解析公式

3.1.2 使用【F9】键快速查看公式指定部分计算结果

3.1.3 查看从属与引用单元格

3.1.4 使用“错误检查”功能

3.1.5 利用“监视窗口”监视数据

3.1.6 利用“显示公式”功能查看公式

3.2 常见错误公式的修正

3.2.1 修正文本数据参与计算的问题

3.2.2 修正日期计算时差值总为日期问题

3.2.3 空白单元格不为空问题

3.2.4 实际的值与显示的值不同

3.2.5 修正循环引用不能计算的公式

3.3 分析与解决公式返回的错误值

3.3.1 分析与解决“####”错误值

3.3.2 分析与解决“#DIV/0！”错误值

3.3.3 分析与解决“#N/A”错误值

3.3.4 分析与解决“#NAME?”错误值

3.3.5 分析与解决“#NUM!”错误值

3.3.6 分析与解决“#VALUE!”错误值

3.3.7 分析与解决“#REF!”错误值

3.3.8 分析与解决“#NULL!”错误值

第4章 逻辑函数

4.1 逻辑判断函数

4.1.1 AND：判断指定的多个条件是否全部成立

4.1.2 OR：判断指定的多个条件是否有一个成立

4.1.3 NOT：判断指定的条件不成立

4.2 根据逻辑判断结果返回值

4.2.1 IF：根据逻辑测试值返回指定值

4.2.2 IFS：多条件判断

4.2.3 SWITCH：返回匹配的结果

4.2.4 IFERROR：根据错误值返回指定值

第5章 日期与时间函数

5.1 构建与提取日期、时间

5.1.1 TODAY：返回当前日期

5.1.2 DATE：构建标准日期

5.1.3 TIME：构建标准时间

5.1.4 YEAR：返回某日期中的年份值

5.1.5 MONTH：返回某日期中的月份值

5.1.6 DAY：返回以序列号表示的某日期中的天数

5.1.7 EOMONTH：返回某个月份最后一天的序列号

5.1.8 WEEKDAY：返回日期对应的星期数

5.1.9 HOUR：返回时间值的小时数

5.1.10 MINUTE：返回时间值的分钟数

5.1.11 SECOND：返回时间值的秒数

5.2 期间差

5.2.1 DAYS360：返回两日期间相差的天数（按照一年360天的算法）

5.2.2 NETWORKDAYS：计算某时段中的工作日天数

5.2.3 NETWORKDAYS.INTL函数

5.2.4 WORKDAY：根据起始日期计算出指定工作日之后的日期

5.2.5 WORKDAY.INTL函数

5.2.6 ISOWEEKNUM：返回日期的ISO周数

5.2.7 EDATE：计算出间隔指定月份数后的日期

5.3 文本日期与文本时间的转换

5.3.1 DATEVALUE：将文本日期转换为可计算的日期序列号

5.3.2 TIMEVALUE：将文本时间转换为可计算的小数值

第6章 数学函数

6.1 数据计算函数

6.1.1 SUM：求和

6.1.2 SUMIF：根据指定条件对若干单元格求和

6.1.3 SUMIFS：对区域中满足多个条件的单元格求和

6.1.4 PRODUCT：求所有参数的乘积

6.1.5 SUMPRODUCT：求数组间对应的元素乘积的和

6.2 舍入函数

6.2.1 INT：返回实数向下取整后的整数值

6.2.2 ROUND：按指定位数对数值四舍五入

6.2.3 ROUNDUP：向上舍入数值

6.2.4 ROUNDDOWN：向下舍入数值

6.2.5 CEILING.PRECISE：向上舍入到最接近指定数字的某个值的倍数值

6.2.6 MROUND：按照指定基数的倍数对参数四舍五入

6.2.7 EVEN：将数值向上舍入到最接近的偶数

6.2.8 ODD：将数值向上舍入到最接近的奇数

6.3 随机数函数

6.3.1 RAND：返回大于等于0及小于1的均匀分布随机数

6.3.2 RANDBETWEEN：产生整数的随机数

第7章 文本函数

7.1 文本提取函数

7.1.1 FIND：返回字符串在另一个字符串中的起始位置

7.1.2 SEARCH：查找字符串中指定字符起始位置（不区分大小写）

7.1.3 LEFT：从最左侧开始提取指定个数的字符

7.1.4 RIGHT：从最右侧开始提取指定个数的字符

7.1.5 MID：提取文本字符串中从指定位置开始的特定个数的字符

7.2 文本新旧替换函数

7.2.1 REPLACE：将一个字符串中的部分字符用另一个字符串替换

7.2.2 SUBSTITUTE：用新字符串替换字符串中的部分字符串

7.3 文本格式转换函数

7.3.1 ASC：将全角字符更改为半角字符

7.3.2 DOLLAR：四舍五入数值并添加千分位符号和$符号

7.3.3 LOWER：将文本字符串中所有大写字母转换为小写字母

7.3.4 PROPER：将英文文本字符串中的首字母转换成大写

7.3.5 RMB：四舍五入数值并添加千分位符号和¥符号

7.3.6 TEXT：将数值转换为按指定数字格式表示的文本

7.3.7 UPPER：将小写英文文本转换为大写形式

7.3.8 VALUE：将代表数字的文本字符串转换为数值

7.4 文本的其他操作

7.4.1 CONCAT：将多个区域和/或字符串的文本组合起来

7.4.2 CONCATENATE：将多个文本字符串合并成一个文本字符串

7.4.3 TRIM：删除文本中的多余空格

7.4.4 CLEAN：删除文本中不能打印的字符

7.4.5 EXACT：比较两个文本字符串是否完全相同

7.4.6 TEXTJOIN：将多个区域的文本组合起来并指定分隔符

7.4.7 REPT：按照给定的次数重复显示文本

第8章 统计函数

8.1 基础统计函数

8.1.1 AVERAGE：计算平均值

8.1.2 AVERAGEA：计算平均值（包括文本、逻辑值）

8.1.3 AVERAGEIF：对区域中满足条件的单元格求平均值

8.1.4 AVERAGEIFS：对区域中满足多个条件的单元格求平均值

8.1.5 COUNT：统计单元格区域中含有数值数据的单元格个数

8.1.6 COUNTA：统计单元格区域中含有数据的单元格个数

8.1.7 COUNTIF：统计满足给定条件的数据个数

8.1.8 COUNTIFS：统计满足多重条件的单元格数目

8.1.9 MAX（MIN）：返回一组值中的最大值、最小值

8.1.10 MAXIFS（MINIFS）：返回一组给定条件或标准指定的单元格中的最大值、最小值

8.1.11 LARGE（SMALL）：返回某数据集的某个最大值、最小值

8.1.12 TRIMMEAN：求一组数据集的内部平均值

8.1.13 GEOMEAN：返回数据集的几何平均值

8.1.14 RANK.EQ：返回数组的最高排位

8.2 方差、协方差与偏差

8.2.1 VAR.S：计算基于样本的方差

8.2.2 VAR.P：计算基于样本总体的方差

8.2.3 STDEV.S：计算基于样本估算标准偏差

8.2.4 STDEV.P：计算样本总体的标准偏差

8.2.5 COVARIANCE.S：返回样本协方差

8.2.6 COVARIANCE.P：返回总体协方差

8.2.7 AVEDEV：计算数值的平均绝对偏差

8.3 数据预测

8.3.1 LINEST：对已知数据进行最佳直线拟合

8.3.2 TREND：构造线性回归直线方程

8.3.3 LOGEST：回归拟合曲线返回该曲线的数值

8.3.4 GROWTH：根据现有的数据预测指数增长值

8.3.5 FORECAST：根据已有的数值计算或预测未来值

8.3.6 CORREL：返回两变量相关系数

8.4 其他常用指标统计函数

8.4.1 MODE.SNGL：返回数组中的众数

8.4.2 MEDIAN：求一组数的中值

8.4.3 MODE.MULT：返回一组数据集中出现频率最高的数值

8.4.4 FREQUENCY：频数分布统计

8.4.5 PROB：返回数值落在指定区间内的概率

8.4.6 KURT：返回数据集的峰值

第9章 财务函数

9.1 投资计算函数

9.1.1 FV：固定利率及等额分期付款方式返回投资未来值

9.1.2 FVSCHEDULE：计算投资在变动或可调利率下的未来值

9.1.3 IPMT：返回贷款的给定期间内利息偿还额

9.1.4 ISPMT：等额本金还款方式的利息计算

9.1.5 PMT：基于固定利率返回贷款的每期等额付款额

9.1.6 PPMT：计算指定期间内本金偿还额

9.1.7 NPV：返回投资的净现值

9.1.8 PV：返回投资的现值

9.1.9 XNPV：返回一组不定期现金流的净现值

9.1.10 EFFECT：计算实际年利率

9.1.11 NOMINAL：计算名义利率

9.1.12 NPER：返回某项投资的总期数

9.2 折旧计算函数

9.2.1 DB：使用固定余额递减法计算折旧值

9.2.2 DDB：使用双倍余额递减法计算折旧值

9.2.3 SLN：返回某项资产在一个期间中的线性折旧值

9.2.4 SYD：使用年数总和折旧法计算折旧值

9.3 偿还率计算函数

9.3.1 IRR：计算内部收益率

9.3.2 MIRR：返回某一连续期间内现金流的修正内部收益率

9.3.3 RATE：返回年金的各期利率

9.3.4 XIRR：计算不定期现金流的内部收益率

第10章 查找与引用函数

10.1 数据的引用

10.1.1 CHOOSE：根据给定的索引值返回数值参数清单中的数值

10.1.2 ROW：返回引用的行号函数

10.1.3 COLUMN：返回引用的列号函数

10.1.4 OFFSET：以指定引用为参照系，通过给定偏移量得到新引用

10.2 数据的查找

10.2.1 VLOOKUP：查找目标数据并返回当前行中指定列处的值

10.2.2 LOOKUP：查找目标数据并返回当前行中指定数组中的值

10.2.3 HLOOKUP：在首行查找指定的数值并返回当前行中指定列处的数值

10.2.4 MATCH：查找并返回值所在位置

10.2.5 INDEX：返回指定行列交叉处引用的值

第11章 信息函数

11.1 信息获得函数

11.1.1 CELL：返回单元格的信息

11.1.2 TYPE：返回单元格内的数值类型

11.2 IS函数

11.2.1 ISBLANK：判断测试对象是否为空单元格

11.2.2 ISERROR：检测一个值是否为错误值

11.2.3 ISNA：检测一个值是否为#N/A错误值

11.2.4 ISNUMBER：检测一个值是否为数值

11.2.5 ISEVEN：检测一个值是否为偶数

11.2.6 ISODD：检测一个值是否为奇数

11.2.7 ISTEXT：检测一个值是否为文本

11.2.8 ISNONTEXT：检测一个值是否为非文本

第12章 员工档案管理

12.1 员工档案管理表

12.1.1 创建员工档案管理表

12.1.2 利用数据有效性防止工号重复输入

12.1.3 从身份证号码中提取有效信息

12.1.4 计算员工年龄和工龄

12.2 员工档案查询表

12.2.1 创建员工档案查询表

12.2.2 使用VLOOKUP函数查询人员信息

12.3 使用函数制作到期提醒

12.3.1 生日到期提醒

12.3.2 试用期到期提醒

12.4 员工学历层次分析

12.4.1 编制员工学历数据透视表

12.4.2 制作员工学历透视图

12.5 员工年龄段分析

12.5.1 编制员工年龄段数据透视表

12.5.2 制作员工学历透视图

第13章 员工考勤管理

13.1 员工考勤登记表

13.1.1 绘制员工基本考勤登记表

13.1.2 设置考勤日期和星期格式

13.1.3 冻结窗格显示固定数据

13.1.4 添加考勤符号

13.2 员工考勤统计表

13.2.1 创建员工考勤统计表

13.2.2 员工考勤扣款统计

13.2.3 员工满勤奖统计

13.3 员工出勤情况分析

13.3.1 员工出勤率统计

13.3.2 使用数据透视表统计各部门出勤情况

第14章 员工薪资管理

14.1 员工销售业绩奖金

14.1.1 创建销售记录表

14.1.2 计算本月业绩奖金

14.2 员工基本工资表

14.2.1 创建员工基本工资表

14.2.2 计算员工工龄及工龄工资

14.3 复制员工考勤扣款及满勤奖表格

14.4 员工福利补贴表

14.4.1 创建员工福利表

14.4.2 计算员工福利补贴

14.5 员工社保缴费表

14.5.1 创建员工社保缴费表

14.5.2 计算各项社保缴费明细

14.6 工资统计表

14.6.1 创建员工月度工资表

14.6.2 计算工资表中应发金额

14.6.3 计算工资表中应扣金额

14.7 员工工资单

14.7.1 创建员工的工资单

14.7.2 快速生成每位员工的工资单

第15章 公司销售管理

15.1 销售记录表

15.1.1 创建销售记录表

15.1.2 标记出排名前三的销售金额

15.1.3 筛选出指定的销售记录

15.2 按产品系列统计销售收入

15.2.1 创建基本表格

15.2.2 创建饼图显示各产品销售金额

15.3 销售收入变动趋势分析

15.3.1 创建基本表格

15.3.2 创建图表显示销售变动趋势

15.4 销售员业绩分析

15.4.1 创建数据透视表

15.4.2 创建数据透视图

第16章 固定资产管理

16.1 固定资产清单

16.1.1 创建固定资产清单

16.1.2 使用公式计算固定资产状态

16.1.3 查询部门固定资产清单

16.2 固定资产查询表

16.2.1 返回固定资产各项信息

16.2.2 固定资产折旧的计算

16.3 企业固定资产折旧计算

16.3.1 使用固定余额法进行折旧计算

16.3.2 使用双倍余额递减法进行折旧计算

16.3.3 使用年限总和法进行折旧计算