Excel 2016公式与函数应用大全电子书

前言

第1章 公式基础

1.1 认识公式

1.1.1 公式的组成

1.1.2 公式中运算符的使用

1.1.3 公式中“?”“*”通配符的使用

1.2 公式输入与修改

1.2.1 公式输入

1.2.2 公式修改

1.3 公式复制与填充

1.3.1 公式复制

1.3.2 公式填充

1.4 公式保护、隐藏与显示

1.4.1 公式保护

1.4.2 公式隐藏

1.4.3 公式显示

1.5 改变公式的计算方式

1.5.1 将公式结果转化为数值

1.5.2 将公式转换为文本

1.6 为什么公式正确却得到不正确的结果

第2章函数基础

2.1 认识函数

2.1.1函数的组成

2.1.2 Excel函数类别

2.2函数输入

2.2.1 手动输入

2.2.2 使用“fx”按钮输入

2.2.3 使用“函数自动完成”功能输入

2.2.4 使用“插入函数”功能输入

2.3函数修改与删除

2.3.1 双击公式单元格修改函数

2.3.2 在“编辑栏”中修改函数

2.3.3 使用单元格引用颜色标识来修改引用单元格

2.3.4 删除公式中不要的函数

2.4 嵌套函数

第3章 单元格的引用

3.1 名称定义和使用

3.1.1 快速定义名称

3.1.2 修改名称

3.1.3 编辑名称

3.1.4 删除名称

3.1.5 定义公式名称

3.1.6 创建动态名称

3.1.7 应用名称

3.2 单元格引用

3.2.1 相对引用

3.2.2 绝对引用

3.2.3 混合引用

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

3.2.5 跨工作簿引用

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

3.2.7 使用【F4】键互换引用类型

第4章 数组的应用

4.1 数组类型

4.1.1 一维水平数组

4.1.2 一维垂直数组

4.1.3 二维数组

4.1.4 常量数组在公式中的应用

4.1.5 内存数组

4.2 使用数组公式

4.2.1 普通公式与数组公式

4.2.2 单个单元格数组公式与多个单元格数组公式

4.3 多项计算与数组公式的区别

4.4 修改或删除数组公式

第5章 公式审核与修正

5.1 公式检测与审核

5.1.1 使用“监视窗口”监视数据

5.1.2 使用“错误检查”来检查公式

5.1.3 使用“追踪错误”来追踪公式错误

5.1.4 使用“追踪引用单元格”

5.1.5 使用“追踪从属单元格”

5.1.6 移去追踪箭头

5.2 错误公式的修正

5.2.1 使用“显示公式”来修正

5.2.2 使用“公式求值”来查看公式结果

5.2.3 使用【F9】键查看公式中部分公式的结果

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

5.2.5 修正公式输入的参数不对

5.2.6 修正输入的公式中包含错误

5.2.7 修正公式复制后得不到正确结果

5.2.8 修正按顺序书写的公式不按顺序计算

5.2.9 修正四舍五入后计算结果不正确

5.2.10 修正小数计算结果出错

第6章 分析与解决公式返回错误值

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

6.2 分析与解决“#DIV/O！”错误值

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

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

6.5 分析与解决“#NUM！”错误值

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

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

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

第7章 逻辑函数

7.1 逻辑判断函数

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

7.1.2 OR：判断参数值是否有一个为TRUE

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

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

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

7.2 逻辑值函数

7.2.1TRUE：返回逻辑值TRUE

7.2.2FALSE：返回逻辑值FLASE

第8章 日期与时间函数

8.1 返回当前日期和时间

8.1.1 TODAY：返回当前日期

8.1.2 NOW：返回当前的日期和时间

8.2 用序列号表示或计算时间和日期

8.2.1 YEAR：返回某日对应的年份

8.2.2 DATE：返回特定的日期和序列号

8.2.3 DATEVALUE：将日期值从字符串转换为序列号

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

8.2.5 TIME：返回某一时间的特定小数值

8.2.6 TIMEVALUE：返回由文本字符串所代表的小数值

8.2.7 EOMONTH：从序列号或文本中算出指定月最后一天的序列号

8.2.8 MONTH：返回以序列号表示的日期中的月份

8.2.9 WEEKDAY：返回指定日期对应的星期数

8.2.10 WORKDAY：从序列号或文本中计算出指定工作日之后的日期

8.2.11 EDATE：计算出所制定月数之前或之后的日期

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

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

8.2.14 SECOND：返回时间值的秒数

8.3 期间差

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

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

8.3.3 YEARFRAC：从开始到结束日所经过的天数占全年天数的比例

8.3.4DATEDIF：用指定的单位计算起始日和结束日之间的间隔数

8.4 其他日期与时间函数

8.4.1 WEEKNUM：返回序列号对应的一年中的第几周

8.4.2 DATESTRING：将指定日期的序列号转换为文本日期

第9章 数学与三角函数

9.1 计算函数

9.1.1 SUM：求和

9.1.2 MOD：求余

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

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

9.1.5 GCD：求最大公约数

9.1.6 LCM：求最小公倍数

9.1.7 PRODUCT：求所有参数的乘积

9.1.8 SUMSQ：求参数的平方和

9.1.9 QUOTIENT：求除法的整除数

9.1.10 SUMX2MY2：求两数组中对应数值的平方差之和

9.1.11 SUMX2PY2：求两数组中对应数值的平方和之和

9.1.12 SUMXMY2：求两数组中对应数值差的平均和

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

9.1.14 SUBTOTAL：求数据列表或数据库中的分类汇总

9.1.15 ABS：求出相应数值或引用单元格中数值的绝对值

9.1.16 SIGN：求数值的符号

9.2 零数处理函数

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

9.2.2 TRUNC：将数字的小数部分截去，返回整数

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

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

9.2.5 ROUNDUP：按指定的位数向上舍入数值

9.2.6 ROUNDDOWN：按照指定的位数向下舍入数值

9.2.7 CEILING：将参数向上舍入为最接近的基数的倍数

9.2.8 CEILING.PRECISE：将数字舍入为最接近的整数或最接近的指定基数的倍数

9.2.9 FLOOR：将参数向下舍入到最接近的基数的倍数

9.2.10 FLOOR.PRECISE：对数据进行向下舍取求值

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

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

9.3 圆周率与平方根函数

9.3.1 PI：求圆周率的近似值

9.3.2 SQRTPI：求圆周率π的倍数的平方根值

9.3.3 SQRT：求数值的平方根

9.4 其他数学与三角函数

9.4.1 AGGREGATE：返回一个数据列表或数据库的合计

9.4.2 COMBIN：求组合数

9.4.3 MDETERM：求数组的矩阵行列式的值

9.4.4 MINVERSE：求数组阵列的逆阵列

9.4.5 MMULT：求数组的矩阵乘积

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

9.4.7 RANDBETWEEN：产生整数的随机数

9.4.8 ROMAN：将阿拉伯数字转换为罗马数字

第10章 文本函数

10.1 字符串的查找与位置返回

10.1.1 LEFT：从最左侧提取指定个数字符

10.1.2 LEFTB：从最左侧提取指定个数字符（以字节为单位）

10.1.3 LEN：返回文本字符串的字符数

10.1.4 LENB：返回文本字符串的字节数

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

10.1.6 MIDB：提取文本字符串中从指定位置开始的特定个数的字符（以字节数为单位）

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

10.1.8 REPLACEB：将部分字符根据所指定的字节数用另一个字符串替换

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

10.1.10 RIGHTB：从最右侧开始提取指定字节数的字符

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

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

10.1.13 FINDB：返回字符串在另一个字符串中的起始位置（以字节为单位）

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

10.1.15 SEARCHB：查找字符串中指定字符起始位置（以字节为单位）

10.2 文本格式的转换

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

10.2.2 UPPER：将文本转换为大写形式

10.2.3 LOWER：将文本转换为小写函数

10.2.4 VALUE：将文本转换为数值

10.2.5 BAHTTEXT：将数字转换为泰语文本

10.2.6 NUMBERSTRING：将数字转换为大写汉字

10.2.7 NUMBERVALUE：将文本转化为数字

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

10.2.9 TEXT：设置数字格式，并将其转换为大写汉字

10.2.10 WIDECHAR：将半角字符转化成全角字符

10.2.11 DOLLAR：四舍五入数值，并转换为带$和位符号的格式

10.2.12 RMB：四舍五入数值，并转换为带￥和位符号的格式

10.2.13 FIXED：将数字按指定的位数取整并以文本形式返回

10.2.14 CODE：返回文本字符串中有一个字符的数字代码

10.2.15 UNICODE：返回对应于文本的第一个字符的数字（代码点）

10.2.16 CHAR：返回对应于数字代码的字符

10.2.17 UNICHAR：返回给定数值引用的Unicode字符

10.2.18 T：返回value引用的文本

10.3 文本的其他操作

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

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

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

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

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

第11章 查找与引用函数

11.1 数据的查找

11.1.1 VLOOKUP：查找指定的数值并返回当前行中指定列处的数值

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

11.1.3 LOOKUP：从向量（数组）中查找一个数值

11.1.4 INDEX：返回指定行列交叉处引用的单元格

11.1.5 MATCH：返回指定方式下与制定数值匹配的元素的相应位置

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

11.2 数据的引用

11.2.1 ADDRESS：按给定的行号和列标，建立文本类型的单元格地址

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

11.2.3 INDIRECT：返回由文本字符串指定的引用

11.2.4 AREAS：返回引用中包含的区域个数

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

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

11.2.7 ROWS：返回引用或数组的行数

11.2.8 COLUMNS：返回数组或引用的列数

11.3 其他函数

11.3.1 GETPIVOTDATA：返回存储在数据透视表中的数据

11.3.2 HYPERLINK：创建一个快捷方式以打开存储在网络服务器中的文件

11.3.3 TRANSPOSE：转置单元格区域

11.3.4 FORMULATEXT：以文本形式返回给定引用处的公式

第12章 信息函数

12.1 信息获得函数

12.1.1 CELL：返回单元格的信息

12.1.2 ERROR.TYPE：返回与错误值对应的数字

12.1.3 INFO：返回当前操作环境的信息

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

12.2 IS函数

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

12.2.2 ISERR：检测一个值是否为

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

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

12.2.5 ISLOGICAL：检测一个值是否为逻辑值

12.2.6 ISNA：检测一个值是否为

12.2.7 ISNONTEXT：检测一个值是否不是文本

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

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

12.2.10 ISREF：检测一个值是否为引用

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

12.2.12 ISFORMULA：检测是否包含公式的单元格引用

12.3 其他函数

12.3.1 N：将参数转换为数值形式

12.3.2 PHONETIC：提取文本字符串中的拼音字符

12.3.3 NA：返回错误值函数

12.3.4 SHEET：返回引用工作表的工作表编号

12.3.5 SHEETS：返回引用中的工作表数

第13章 统计函数

13.1 基础统计函数

13.1.1 AVERAGE：返回参数的平均值

13.1.2 AVERAGEA：计算参数列表中非空单元格中数值的平均值

13.1.3 AVEDEV：返回一组数据点到其算术平均数的绝对偏差的平均值

13.1.4 AVERAGEIF：查找给定条件指定的单元格的平均值

13.1.5 AVERAGEIFS：查找一组给定条件指定的单元格的平均值

13.1.6 COUNT：统计参数列表中含有数值数据的单元格个数

13.1.7 COUNTA：计算指定单元格区域中非空单元格的个数

13.1.8 COUNTBLANK：计算空白单元格的个数

13.1.9 COUNTIF：求满足给定条件的数据个数

13.1.10 COUNTIFS：统计一组给定条件所指定的单元格数

13.1.11 DEVSQ：返回数据点与各自样本平均值偏差的平方和

13.1.12 FREQUENCY：以一列垂直数组返回某个区域中数据的频率分布

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

13.1.14 GEOMEAN：求数值数据的几何平均数

13.1.15 MEDIAN：求一组数的中值

13.1.16 MODE.SNGL：求数值数据的众数

13.1.17 HARMEAN：求数据集合的调和平均值

13.1.18 MAX：返回一组值中的最大值

13.1.19 MAXA：返回参数列表中的最大值

13.1.20 MIN：返回一组值中的最小值

13.1.21 MINA：返回参数列表中的最小值

13.1.22 QUARTILE：返回数据集的四分位数

13.1.23 PERCENTILE.INC：返回数组的K百分点值

13.1.24 PERCENTILE.EXC：返回区域中数值的第K个百分点的值

13.1.25 PERCENTRANK.INC：返回特定数值在一组数中的百分比排名

13.1.26 PERCENTRANK.EXC：返回某个数值在一个数据集中的百分比

13.1.27 VAR.S：估算基于给定样本的方差

13.1.28 VAR.P：估算基于整个样本总体的方差

13.1.29 VARA：求空白单元格以外给定样本的方差

13.1.30 VARPA：计算空白单元格以外基于整个样本总体的方差

13.1.31 STDEV.P：计算基于给定的样本总体的标准偏差

13.1.32 STDEV.S：估算基于给定的样本总体的标准偏差

13.1.33 STDEVA：求空白单元格以外给定样本的标准偏差

13.1.34 STDEVPA：计算空白单元格以外的样本总体的标准偏差

13.1.35 SKEW：返回分布的偏斜度

13.1.36 SKEW.P：返回基于样本总体的分布不对称度

13.1.37 KURT：返回数据集的峰值

13.2 协方差、相关与回归函数

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

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

13.2.3 CORREL：返回两变量的相关系数

13.2.4 PEARSON：返回皮尔生乘积矩相关系数

13.2.5 FISHER：返回点x的Fisher变换值

13.2.6 FISHERINV：求Fisher变换的反函数值

13.2.7 SLOPE：返回线性回归直线的斜率

13.2.8 INTERCEPT：求回归直线的截距

13.2.9 FORECAST：求两变量间的回归直线的预测值

13.2.10 TREND：求回归直线的预测值

13.2.11 STEYX：求回归直线的标准误差

13.2.12 RSQ：求回归直线的判定系数

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

13.2.14 LOGEST：求指数回归曲线的系数和底数

13.2.15 LINEST：求回归直线的系数和常数项

第14章 财务函数

14.1 投资计算函数

14.1.1 FV：基于固定利率及等额分期付款方式，返回期指

14.1.2 FVSCHEDULE：基于一系列复利返回本金的期指

14.1.3 IPMT：返回给定期数内对投资的利息偿还率

14.1.4 ISPMT：计算特定投资期内要支付的利息

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

14.1.6 PPMT：求偿还额的本金部分

14.1.7 NPV：基于一系列现金流和固定贴现率，返回净现值

14.1.8 PV：返回投资的现值

14.1.9 XNPV：基于不定期发生的现金流，返回它的净现值

14.1.10 EFFECT：求实际的年利率

14.1.11 NOMINAL：求名义利率

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

14.1.13 PDURATION：返回投资到达指定值所需的期数

14.1.14 RRI：返回投资增长的等效利率

14.2 折旧计算函数

14.2.1 AMORDEGRC：返回每个结算期间的折旧值

14.2.2 AMORLINC：返回每个结算期间的折旧值

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

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

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

14.2.6 SYD：按年限总和折旧法计算折旧值

14.2.7 VDB：使用双倍余额递减法或其他指定方法，返回折旧值

14.3 偿还计算函数

14.3.1 IRR：返回一组现金流的内部收益率

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

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

14.3.4 XIRR：返回不定期内产生的现金流量的内部收益率

第15章 数据库函数

15.1 数据库计算函数

15.1.1 DSUM：返回数据库的列中满足指定条件的数字之和

15.1.2 DPRODUCT：返回数据库的列中满足指定条件的数值的乘积

15.2 数据库统计函数

15.2.1 DAVERAGE：返回数据库的列中满足指定条件的数值的平均值

15.2.2 DCOUNT：返回数据库的列中满足指定条件的单元格的个数

15.2.3 DCOUNTA：返回数据库的列中满足指定条件的非空单元格的个数

15.2.4 DGET：求满足条件的唯一记录

15.2.5 DMAX：返回数据库的列中满足指定条件的数值的最大值

15.2.6 DMIN：返回数据库的列中满足指定条件的数值的最小值

15.2.7 DSTDEV：返回数据库的列中满足指定条件数值的样本标准偏差

15.2.8 DSTDEVP：将满足指定条件的数字作为样本总体，计算标准偏差

15.2.9 DVAR：将满足指定条件的数字作为一个样本，估算样本总体的方差

15.2.10 DVARP：将满足指定条件的数字作为样本总体，计算总体方差

第16章 员工档案管理

16.1 员工档案管理表

16.1.1 创建员工档案管理表

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

16.1.3 身份证号中提取有效信息

16.1.4 计算员工年龄和工龄

16.2 员工档案查询表

16.2.1 创建员工档案查询表

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

16.3 使用函数制作到期提醒

16.3.1 生日到期提醒

16.3.2 试用期到期提醒

16.4 员工学历层次分析

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

16.4.2 制作员工学历透视图

16.5 员工年龄段分析

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

16.5.2 制作员工学历透视图

第17章 员工考勤管理

17.1 员工考勤登记表

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

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

17.1.3 冻结窗格显示固定数据

17.1.4 添加考勤符号

17.2 员工考勤统计表

17.2.1 创建员工考勤统计表

17.2.2 用特殊颜色标记迟到早退人员

17.3 员工出勤情况分析

17.3.1 员工出勤率统计

17.3.2 筛选出勤情况最差的员工

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

17.4 员工考勤扣款统计

17.4.1 员工出勤率统计

17.4.2 筛选出勤扣款最多的5位员工

第18章 员工薪资管理

18.1 员工销售业绩奖金

18.1.1 创建销售记录表

18.1.2 计算本月基本业绩奖金

18.2 员工基本工资表

18.2.1 创建基本表格

18.2.2 计算员工工龄及工龄工资

18.3 员工考勤扣款及满勤奖

18.3.1 复制员工考勤扣款统计工作表

18.3.2 计算员工满勤奖

18.4 员工福利补贴表

18.4.1 创建员工福利表

18.4.2 计算员工福利补贴

18.5 员工社保缴费表

18.5.1 创建员工社保缴费表

18.5.2 计算各项社保缴费情况

18.6 工资统计表

18.6.1 创建员工月度工资表

18.6.2 计算工资表中应发金额

18.6.3 计算工资表中应扣金额

18.7 员工工资单

18.7.1 建立员工的工资单

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

第19章 销售管理

19.1 销售记录表

19.1.1 创建销售记录表

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

19.1.3 筛选出指定的销售记录

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

19.2.1 创建基本表格

19.2.2 创建饼图显示各产品销售情况

19.3 销售收入变动趋势分析

19.3.1 创建基本表格

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

19.4 销售员业绩分析

19.4.1 创建数据透视表

19.4.2 创建数据透视图

第20章 固定资产管理

20.1 固定资产清单

20.1.1 创建固定资产清单

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

20.1.3 查询部门固定资产清单

20.2 固定资产查询表

20.2.1 返回固定资产各项信息

20.2.2 计算固定资产折旧情况

20.3 企业固定资产折旧计算

20.3.1 余额法折旧计算

20.3.2 双倍余额递减法折旧计算

20.3.3 年限总和法折旧计算