Excel函数与公式从入门到精通:微课视频版

第 1 章 认识 Excel 公式 视频讲解：40 分钟 1.1 了解公式结构 1.1.1 公式的组成部分 1.1.2 公式中的几种运算符 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.4.4 内存数组是如何调用的 第 2 章 公式中的函数 视频讲解：30 分钟 2.1 认识函数 2.1.1 函数的组成 2.1.2 学习函数的用法 2.2 函数输入 2.2.1 手动输入 2.2.2 使用“插入函数”按钮输入 2.2.3 函数修改与删除 2.3 嵌套函数 2.3.1 当两项成绩都达标时给予合格 2.3.2 只对蓝莓产品进行调价 第 3 章 公式中数据源的引用 视频讲解：43 分钟 3.1 不同的单元格引用方式 3.1.1 相对引用单元格 3.1.2 绝对引用单元格 3.1.3 引用当前工作表之外的单元格 3.1.4 引用其他工作簿的单元格 3.1.5 引用多个工作表中的同一单元格 3.2 名称定义和使用 3.2.1 为什么要定义名称 3.2.2 定义名称的两种方法 3.2.3 修改名称或删除名称 3.2.4 在公式中应用名称 3.2.5 将公式定义为名称 3.2.6 创建动态名称 第 4 章 公式错误修正及常用技巧 视频讲解：58 分钟 4.1 学会看懂公式 4.1.1 查看公式引用的单元格 4.1.2 使用“公式求值”来分步解析公式 4.1.3 使用 F 功能键快速查看公式中部分公式的结果 4.1.4 使用“错误检查”来检查公式 4.2 几种常见错误公式的修正 4.2.1 修正文本数据参与计算的问题 4.2.2 修正公式中文本不使用双引号问题 4.2.3 修正日期计算时差值总为日期问题 4.2.4 空白单元格不为空问题 4.2.5 实际的值与显示的值不同 4.2.6 修正循环引用不能计算的公式 4.2.7 修正小数计算结果出错 4.3 分析与解决公式返回错误值 4.3.1 分析与解决“####”错误值 4.3.2 分析与解决“#DIV/!”错误值 4.3.3 分析与解决“#N/A”错误值 4.3.4 分析与解决“#NAME?”错误值 4.3.5 分析与解决“#NUM!”错误值 4.3.6 分析与解决“#VALUE!”错误值 4.3.7 分析与解决“#REF!”错误值 4.3.8 分析与解决“#NULL!”错误值 4.4 公式使用中的几个实用技巧 4.4.1 将公式结果转换为数值 4.4.2 超大范围公式的复制 4.4.3 跳过非空单元格批量建立公式 4.4.4 在多表的同一单元格建立公式 4.4.5 禁止修改公式 4.4.6 隐藏公式 第 5 章 逻辑函数 视频讲解：33 分钟 5.1 逻辑判断函数 5.1.1 AND：判断指定的多个条件是否全部成立 5.1.2 OR：判断指定的多个条件是否有一个成立 5.1.3 NOT：判断指定的条件不成立 5.2 根据逻辑判断结果返回值 5.2.1 IF：根据逻辑测试值返回指定值 5.2.2 IFERROR：根据错误值返回指定值 第 6 章 文本函数 视频讲解：50 分钟 6.1 文本提取函数 6.1.1 FIND：返回字符串在另一个字符串中的起始位置 6.1.2 SEARCH：查找字符串中指定字符起始位置（不区分大小写） 6.1.3 LEFT：从*左侧开始提取指定个数的字符 6.1.4 RIGHT：从*右侧开始提取指定个数的字符 6.1.5 MID：提取文本字符串中从指定位置开始的特定个数的字符 6.2 文本新旧替换 6.2.1 REPLACE：将一个字符串中的部分字符用另一个字符串替换 6.2.2 SUBSTITUTE：用新字符串替换字符串中的部分字符串 6.3 文本格式的转换 6.3.1 ASC：将全角字符更改为半角字符 6.3.2 DOLLAR：四舍五入数值，并添加千分位符号和$符号 6.3.3 RMB：四舍五入数值，并添加千分位符号和￥符号 6.3.4 VALUE：将文本转换为数值 6.3.5 TEXT：将数值转换为按指定数字格式表示的文本 6.3.6 PROPER：将文本字符串的首字母转换成大写 6.3.7 UPPER：将文本转换为大写形式 6.3.8 LOWER：将文本转换为小写字母 6.4 文本的其他操作 6.4.1 CONCATENATE：将多个文本字符串合并成一个文本字符串 6.4.2 TRIM：删除文本中的多余空格 6.4.3 CLEAN：删除文本中不能打印的字符 6.4.4 EXACT：比较两个文本字符串是否完全相同 6.4.5 REPT：按照给定的次数重复显示文本 第 7 章 数学函数 视频讲解：51 分钟 7.1 数据计算函数 7.1.1 SUM：求和 7.1.2 SUMIF：根据指定条件对若干单元格求和 7.1.3 SUMIFS：对区域中满足多个条件的单元格求和 7.1.4 PRODUCT：求所有参数的乘积 7.1.5 SUMPRODUCT：求数组间对应的元素乘积的和 7.2 舍入函数 7.2.1 INT：返回实数向下取整后的整数值 7.2.2 ROUND：按指定位数对数值四舍五入 7.2.3 ROUNDUP：向上舍入数值 7.2.4 ROUNDDOWN：向下舍入数值 7.2.5 CEILING.PRECISE：向上舍入到*接近指定数字的某个值的倍数值 7.2.6 FLOOR.PRECISE：向下舍入到*接近指定数字的某个值的倍数值 7.2.7 MROUND：按照指定基数的倍数对参数四舍五入 7.2.8 EVEN：将数值向上舍入到*接近的偶数 7.2.9 ODD：将数值向上舍入到*接近的奇数 7.3 随机数函数 7.3.1 RAND：返回大于等于及小于的均匀分布随机数 7.3.2 RANDBETWEEN：产生整数的随机数 第 8 章 统计函数 视频讲解：72 分钟 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 LARGE(SMALL)：返回某数据集的某个*大（小）值 8.1.11 TRIMMEAN：截头尾返回数据集的平均值 8.1.12 GEOMEAN：返回数据集的几何平均值 8.1.13 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 章 日期与时间函数 视频讲解：45 分钟 9.1 构建与提取日期、时间 9.1.1 TODAY：返回当前日期 9.1.2 DATE：构建标准日期 9.1.3 TIME：构建标准时间 9.1.4 YEAR：返回某日期中的年份值 9.1.5 MONTH：返回某日期中的月份值 9.1.6 DAY：返回某日期中的天数 9.1.7 EOMONTH：返回某个月份*后一天的序列号 9.1.8 WEEKDAY：返回日期对应的星期数 9.1.9 HOUR：返回时间值的小时数 9.1.10 MINUTE：返回时间值的分钟数 9.1.11 SECOND：返回时间值的秒数 9.2 期间差 9.2.1 DATEDIF：计算起始日和结束日之间的年数、月数、天数等 9.2.2 DAYS：返回两日期间相差的天数（按照一年360 天的算法） 9.2.3 NETWORKDAYS：计算某时段中的工作日天数 9.2.4 NETWORKDAYS.INTL 函数 9.2.5 WORKDAY：根据起始日期计算出指定工作日之后的日期 9.2.6 WORKDAY.INTL 函数 9.2.7 EDATE：计算出间隔指定月份数后的日期 9.3 文本日期与文本时间的转换 9.3.1 DATEVALUE：将文本日期转换为可计算的日期序列号 9.3.2 TIMEVALUE：将文本时间转换为可计算的小数值 第 10 章 财务函数 视频讲解：16 分钟 10.1 投资计算函数 10.1.1 FV：固定利率及等额分期付款方式返回投资未来值 10.1.2 FVSCHEDULE：计算投资在变动或可调利率下的未来值 10.1.3 IPMT：返回贷款的给定期间内利息偿还额 10.1.4 ISPMT：等额本金还款方式下的利息计算 10.1.5 PMT：返回贷款的每期等额付款额 10.1.6 PPMT：返回贷款的给定期间内本金偿还额 10.1.7 NPV：返回投资的净现值 10.1.8 PV：返回投资的现值 10.1.9 XNPV：返回一组不定期现金流的净现值 10.1.10 EFFECT：计算实际年利率 10.1.11 NOMINAL：计算名义年利率 10.1.12 NPER：返回投资的总期数 10.2 偿还率计算函数 10.2.1 IRR：计算内部收益率 10.2.2 MIRR：计算修正内部收益率 10.2.3 RATE：返回年金的各期利率 10.2.4 XIRR：计算不定期现金流的内部收益率 第 11 章 引用与查找函数 视频讲解：61 分钟 11.1 数据的引用 11.1.1 CHOOSE：根据给定的索引值，返回数值参数清单中的数值 11.1.2 ROW：返回引用的行号 11.1.3 COLUMN：返回引用的列号 11.1.4 OFFSET：以指定引用为参照系，通过给定偏移量得到新的引用 11.2 数据的查找 11.2.1 VLOOKUP：查找目标数据并返回当前行中指定列处的值 11.2.2 LOOKUP：查找目标数据并返回当前行中指定数组中的值 11.2.3 MATCH：查找并返回找到值所在位置 11.2.4 INDEX：从引用或数组中返回指定位置处的值 第 12 章 信息函数 视频讲解：25 分钟 12.1 信息获得函数 12.1.1 CELL：返回单元格的信息 12.1.2 TYPE：返回单元格内的数值类型 12.2 IS 函数 12.2.1 ISBLANK：判断测试对象是否为空单元格 12.2.2 ISERROR：检测一个值是否为错误值 12.2.3 ISNA：检测一个值是否为“#N/A”错误值 12.2.4 ISNUMBER：检测一个值是否为数值 12.2.5 ISEVEN：检测一个值是否为偶数 12.2.6 ISODD：检测一个值是否为奇数 12.2.7 ISTEXT：检测一个值是否为文本 12.2.8 ISNONTEXT：检测一个值是否为非文本 第 13 章 函数辅助人事数据的管理 视频讲解：46 分钟 13.1 函数辅助建立档案表 13.1.1 身份证号码提取有效信息 13.1.2 计算员工工龄 13.2 人员结构分析 13.2.1 统计各部门员工总数、各性别员工人数 13.2.2 统计各部门各学历人数 13.2.3 统计各部门各年龄段人数 13.2.4 统计各部门各工龄段人数 13.3 员工信息的快速查询 13.4 人员入职培训成绩统计 13.4.1 计算总成绩、平均成绩、合格情况、名次 13.4.2 筛选查看任意培训者成绩 13.5 自动化到期提醒设计 13.5.1 员工生日提醒 13.5.2 试用期到期提醒 13.5.3 合同到期提醒 13.5.4 退休到期提醒 第 14 章 加班、考勤数据的核算与统计 视频讲解：39 分钟 14.1 加班统计表 14.1.1 根据加班日期自动返回加班类型 14.1.2 计算加班小时数 14.1.3 计算加班费 14.2 异常考勤的统计 14.2.1 整理考勤机自动产生的异常数据 14.2.2 手动整理异常数据 14.2.3 员工出勤情况统计 14.2.4 员工出勤率分析 第15 章 应收账款及固定资产数据的核算与统计 视频讲解：45 分钟 15.1 应收账款数据的计算 15.1.1 计算未收金额、判断账款目前状态 15.1.2 计算各笔账款逾期未收金额 15.1.3 分客户统计应收账款 15.1.4 统计各个账龄区间的未收款 15.1.5 计算各账龄下的应收账款所占比例 15.2 固定资产折旧计提 15.2.1 直线法计提折旧 15.2.2 年数总和法计提折旧 15.2.3 双倍余额递减法计提折旧 第 16 章 函数辅助建立工资核算系统 视频讲解：22 分钟 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 工资水平分布统计