EXCEL在日常工作中的应用.ppt

EXCEL在日常工作中的应用 第一章EXCEL文件管理 新建文件工作表的隐藏文件的安全与保护快速打印指定表格 第二章单元格编辑 录入相同的内容下拉列表录入录入内容的限制限制数字格式或大小文本长度单 多条件限定限制重复输入录入区域的限制 第二章单元格编辑 选取使用定位选取选择性粘贴粘贴数值粘贴运算隐藏查找 第三章条件格式 3 1条件格式的设立 添加设立条件格式添加条件3 2定义条件单元格数值条件公式条件3 3条件格式实例应用3 4小结 第三章条件格式 3 1条件格式的设立 添加设立条件格式添加条件3 2定义条件单元格数值条件公式条件3 3条件格式实例应用3 4小结 第三章条件格式 3 1条件格式的设立 添加设立条件格式操作步骤 选中区域 格式 条件格式 输入条件 选择格式添加条件在 条件设置 对话框中 单击添加按钮注 条件格式最多可以设置三个 第三章条件格式 3 2定义条件 单元格数值 用于简单的数值对比 公式 用于设置较为复杂的单元格内容3 2 1单元格数值条件3 2 2公式条件 第三章条件格式 3 2定义条件 单元格数值 用于简单的数值对比 公式 用于设置较为复杂的单元格内容3 2 1单元格数值条件3 2 2公式条件 第三章条件格式 3 3条件格式实例应用3 3 1工龄分析的颜色提示3 2 2应收账款催款提醒3 2 3合同到期提醒监视重复录入格式化账簿代码录入的错误显示动态显示销售额排行隐藏公式中的错误值 第三章条件格式 3 3条件格式实例应用3 3 5代码录入的错误显示条件 1 代码位数不等于五位2 代码位数不等于八位公式 AND LEN B2 5 LEN B2 8 B20 第三章条件格式 3 3条件格式实例应用3 3 6动态显示销售额排行条件 突出显示前N名商品的销售额公式 D2 LARGE D 2 D 10 5 最大值函数 MAX求出一个最大值 LARGE可以求第N个最大值 第三章条件格式 3 3条件格式实例应用3 3 7隐藏公式中错误值条件 把所有错误值隐藏公式 ISERROR D2 判断值是否为任意错误值 N A VALUE 第三章条件格式 3 4小结本章对条件的创建 条件的设置作了详细介绍 同时也列举了大量应用实例 读者从实例中不难看出 如果想用好条件格式 掌握公式及函数的使用是非常重要的 习题 1 如何设置公式条件2 如何突出显示重复录入内容 3 如何突出显示一列数据中最大前三个数字 4 如何添加和删除条件格式 第四章数据表和图表 4 1排序4 1 1数据表排序4 1 2隔行插入空行4 2分列4 2 1拆分整列为多列4 2 2长文本型数字的导入4 2 3转化字符为日期格式4 3自动筛选4 3 1自动筛选的实现4 3 2一次删除所有重复记录 第四章数据表和图表 4 3自动筛选4 3 1自动筛选的实现4 3 2一次删除所有重复记录添加一辅助列 输入公式 IF COUNTIF D2 D2 D2 1 1 2 这儿一定要注意理解绝对引用和相对引用的用法 第一个 IF COUNTIF D 2 D2 D2 1 1 2 第二个 IF COUNTIF D 2 D5 D5 1 1 2 第四章数据表和图表 4 4高级筛选高级筛选功能灵活性强 和自动筛选相比有如下特点 可以把筛选结果复制到其他位置 需要设置条件区域 而且可以使用更多条件 可筛选不重复记录 4 4 1筛选符合条件的记录1 输入条件区域规则 1 标题行和源区域一样 2 同行不同列的条件是并列关系 3 同列不同行的条件是或者关系 第四章数据表和图表 2 设置筛选项目 复制标题行到要显示筛选结果的第一行 复制和手工输入有什么区别 3 数据 筛选 高级筛选 数据源区域 设置条件的区域 第四章数据表和图表 4 4 2筛选本列不重复记录4 4 3筛选两区域重复记录4 4 4筛选两表中不重复记录 COUNTIF D 16 D 24 D3 0 第四章数据表和图表 4 5数据透视表数据透视表是一种对数据清单快速建立汇总的动态总结报告 它可以随时调换行列的位置而进行不同形式的汇总 是Excel提供的一个极为有效的汇总工具 数据透视表在销售数据汇总 出入库汇总及明细账汇总等方面有着广泛应用 一个普通的数据表 你的工作表含有大量数据 但是你知道这些数字的含义吗 这些数据能够解答您的问题吗 不普通的数据透视表 数据透视表提供了一种快速且强大的方式来分析数值数据 以不同的方式查看相同的数据以及回答有关这些数据的问题 第四章数据表和图表 4 5数据透视表4 5 1创建数据透视表三步曲之一 确定报表类型 第四章数据表和图表 4 5数据透视表4 5 1创建数据透视表三步曲之二 确定数据源 第四章数据表和图表 4 5数据透视表4 5 1创建数据透视表三步曲之三 布局 第四章数据表和图表 4 5数据透视表4 5 1创建数据透视表三步曲之三 确定显示位置 第四章数据表和图表 4 5 5固定数据透视表格式4 5 1创建数据透视表4 5 2调整数据透视表格式4 5 3在数据透视表中设置公式4 5 4其他编辑调整汇总方式显示或隐藏汇总行数据透视表的更新和自动更新 第四章数据表和图表 4 5 7多个数据透视表合并 第四章数据表和图表 4 5 7多个数据透视表合并 第四章数据表和图表 4 6图表4 6 1创建图表插入 图表四步曲设置图表类型设置数据源设置图表选项设置图表位置 第四章数据表和图表 四步曲 第四章数据表和图表 4 6 2双坐标图表 单坐标图表 常规设置的双坐标图表 双坐标图表 第五章公式与函数 公式与函数是Excel的精华所在 它为分析和处理数据提供了方便 特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能 本章在介绍函数的用法时 着重介绍它们在财务工作中的应用 本章要点 IF SUM COUNTIF SUMPRODUCT和VLOOKUP等常用函数的用法IF SUM COUNTIF SUMPRODUCT和VLOOKUP等常用函数的实例应用 第五章公式与函数 公式与函数是Excel的精华所在 它为分析和处理数据提供了方便 特别是在处理大批量数据和进行复杂数据分析等方面更能发挥它的强大功能 本章在介绍函数的用法时 着重介绍它们在财务工作中的应用 本章要点 IF SUM COUNTIF SUMPRODUCT和VLOOKUP等常用函数的用法IF SUM COUNTIF SUMPRODUCT和VLOOKUP等常用函数的实例应用 第五章公式与函数 5 1IF函数IF函数是工作中最常用函数之一 它可以根据设置的条件进行运算或返回值 语法 IF 逻辑表达式 TRUE FALSE 逻辑表达式不成立返回的值 逻辑表达式成立返回的值 返回值为TRUE或FALSE的逻辑表达式 例 IF 5 3 对 不对 例 IF 5 3 不对 对 第五章公式与函数 5 1IF函数 逻辑表达式不成立返回的值 逻辑表达式成立返回的值 返回值为TRUE或FALSE的逻辑表达式 打开EXCEL 第五章公式与函数 5 1 1单条件和多条件判断1 单条件返回文本IF函数实例 P113 逻辑表达式不成立返回的值 逻辑表达式成立返回的值 逻辑表达条件 比较实际数和计划数的大小 D2 IF C2 B2 节约 超支 第五章公式与函数 5 1 1单条件和多条件判断2 单条件判断并运算IF函数实例 P113 逻辑表达式不成立进行运算的表达式 逻辑表达式成立时进行运算的表达式 逻辑表达条件 销售额是否超过3万元 C2 IF B2 30000 B2 0 015 B2 0 01 第五章公式与函数 5 1 1单条件和多条件判断3 单条件判断返回引用区域IF函数实例 P113 逻辑表达式不成立返回的区域 逻辑表达式成立时返回的区域 逻辑表达条件 A2是否等于销售一部 SUM IF A2 销售一部 B5 B9 E5 E9 第五章公式与函数 5 1 1单条件和多条件判断4 多条件判断IF函数实例 逻辑表达式不成立返回的表达式 逻辑表达式成立时返回的值 逻辑表达条件 B2或C2任一为0是否成立 IF OR B2 0 C2 0 C2 B2 C2 第五章公式与函数 5 1 1单条件和多条件判断4 多条件判断IF函数实例 IF B2 20000 B2 1 IF B2 25000 B2 2 IF B2 35000 B2 3 B2 4 IF B2 20000 B2 1 IF 20000 B2 25000 B2 2 IF 25000 B2 35000 B2 3 B2 4 常见的错误 常见的错误 第五章公式与函数 5 1 1单条件和多条件判断4 多条件判断IF函数实例 P113 IF B2 10000 B2 2 0 IF AND B2 20000 B2 3 0 IF AND B2 30000 B2 4 0 IF AND B2 40000 B2 5 0 IF AND B2 50000 B2 6 0 IF AND B2 60000 B2 7 0 IF AND B2 70000 B2 8 0 IF B2 80000 B2 9 0 第五章公式与函数 课堂练习 成绩表小结 这节课讲了IF函数的使用 有以下四种情况 单条件返回文本单条件进行运算单条件返回区域多条件判断IF函数在实际工作中应用很广 要注意不同函数中参数的含义 第五章公式与函数 5 2SUM函数SUM函数是工作中最常用函数之一 几乎所有的表格中都有合并的运算 语法 SUM 参数1 参数2 参数30 参数最多为30个 参数可以为引用 数值 文本 表达式和数组 例 SUM 5 3 2 1 例 a1 b1 第五章公式与函数 5 2 1连续 不连续及交叉区域求和例1 连续区域的求和 SUM A1 C5 例1 不连续区域的求和 SUM A1 B3 D22 例1 交叉区域的求和 SUM 1 3C C 注意 这儿有空格 第五章公式与函数 5 2 2多工作表自动汇总是SUM函数的三维应用例 SUM 1日 空白 C5 单引号的作用是去掉工作表名的空格 工作表的名称必须加感叹号 第五章公式与函数 5 1SUMIF函数SUMIF函数是根据指定条件对若干单元格求和 语法 SUMIF 条件范围 条件 求和范围 需要求和的实际范围 省略则对条件范围求和 只能用单条件而不能用复合条件 可以使用通配符 用于条件判断的单元格区域 例 SUMIF B2 B9 副教授 D2 D9 例 SUMIF D2 D9 2000 第五章公式与函数 5 1SUMIF函数 求和范围 条件 条件范围 打开EXCEL 第五章公式与函数 5 3 1单条件求和问题1 根据B列销售金额求和 要求对销售金额大于2000的数值求和SUMIF函数实例 P121 省略求和范围 对条件范围进行求和 条件 条件范围 SUMIF B2 B9 2000 第五章公式与函数 5 3 1单条件求和问题2 根据商品名称求和 要求对商品名称为A1的销售金额求和SUMIF函数实例 求和范围 条件 条件范围 SUMIF A2 A9 A1 B2 B9 第五章公式与函数 5 3 1单条件求和问题3 根据B列销售金额求和 要求对销售金额大于D2的数值求和SUMIF函数实例 P121 省略求和范围 对条件范围进行求和 条件 条件范围 SUMIF B2 B9 D2 第五章公式与函数 5 3 1单条件求和问题4 对B列中大于平均数的销售金额求和SUMIF函数实例 P121 省略求和范围 对条件范围进行求和 条件 条件范围 SUMIF B2 B9 AVERAGE B2 B9 第五章公式与函数 5 3 1单条件求和问题5 求商品名称包含 A 的销售金额之和SUMIF函数实例 P121 求和范围 条件 条件范围 SUMIF A2 A9 A B2 B9 第五章公式与函数 5 3 1单条件求和问题2 根据商品名称求和 要求对商品名称为A1的销售金额求和SUMIF函数实例 P113 求和范围 条件 条件范围 SUMIF A2 A9 A1 B2 B9 第五章公式与函数 5 3 1单条件求和问题6 根据商品名称求第四五个字符为 A2 且字符总长度为6个字符的销售金额求和SUMIF函数实例 P121 求和范围 条件 条件范围 SUMIF A2 A9 A2 B2 B9 第五章公式与函数 5 3 2多条件及区间求和问题1 符合入库数量大于4小于10的商品 对其入库数量求和SUMIF函数实例 P122 SUMIF C2 C9 4 SUMIF C2 C9 10 4 10 第五章公式与函数 5 3 2多条件及区间求和问题2 B列品名分别为 AA BB CC 的销售数量之和SUMIF函数实例 SUM SUMIF B2 B9 AA BB CC C2 C9 第五章公式与函数 5 3 2多条件及区间求和问题3 对品名分别为 AA 的手机入库数量进行求和SUMIF函数实例 SUMIF A2 A9 AA手机 D2 D9 注意要先添加一辅助列 第五章公式与函数 5 3 3不相邻区域的求和SUMIF函数实例 SUMIF A3 D11 1 B3 E11 注意两个区域的大小要一致 第五章公式与函数 5 4COUNTIF函数COUNTIF函数是根据指计算给定区域内满足特定条件单元格数目 语法 COUNTIF 条件范围 条件 可以为数字 表达式或文本 用于条件判断的单元格区域 例 COUNTIF B2 B9 副教授 例 COUNTIF D2 D9 2000 第五章公式与函数 5 4COUNTIF函数 条件 条件范围 打开EXCEL 第五章公式与函数 5 4 1按条件计数问题1 统计实发工资大于2500的人数COUNTIF函数实例 P124 条件 条件范围 COUNTIF E2 E7 2500 第五章公式与函数 5 4 1按条件计数问题2 统计财务部的人数COUNTIF函数实例 条件 条件范围 COUNTIF A2 A7 财务部 第五章公式与函数 5 4 2COUNTIF计数常见的错误1 区域选取的影响COUNTIF函数实例 P124 COUNTIF B3 B8 C3 C8 6 COUNTIF B3 C8 6 第五章公式与函数 5 4 2COUNTIF计数常见的错误2 数字格式的影响COUNTIF函数实例 P124 解决办法 把文本数字转换成数值型 第五章公式与函数 5 4 2COUNTIF计数常见的错误3 长数字的影响COUNTIF函数实例 解决办法 在长数字中添加 号 第五章公式与函数 5 5SUMPRODUCT函数SUMPRODUCT函数是在给定的几组数组中 将数组间对应的元素相乘 并返回乘积之和 语法 SUMPRODUCT 数组1 数组2 数组3 数组参数必须具有相同的维数 否则函数SUMPRODUCT将返回错误值 VALUE 例 SUMPRODUCT 1 2 3 4 5 1 2 3 4 5 120 第五章公式与函数 5 5SUMPRODUCT函数 数组2 数组1 打开EXCEL 数组3 第五章公式与函数 5 5 1库存金额的简便运算不用设置金额列 直接计算出总入库金额SUMPRODUCT函数实例 数组2 数组1 SUMPRODUCT B2 B9 C2 C9 数组参数必须具有相同的维数 第五章公式与函数 5 5 2多条件计数和求和1 多条件同时成立计数 SUMPRODUCT 条件1 条件2 条件3 条件n 求和 SUMPRODUCT 条件1 条件2 条件3 条件n 要统计的数据区域 2 任一条件成立计数 SUMPRODUCT 条件1 条件2 条件3 条件n 求和 SUMPRODUCT 条件1 条件2 条件3 条件n 要统计的数据区域 SUMPRODUCT函数实例 第五章公式与函数 多条件计数和求和例5 17在入库明细汇总表中 根据要求计算问题1 计算供应商A1的冰箱入库类型的品种数 SUMPRODUCT函数实例 条件2 类别为冰箱 条件1 供应商的名字为A1 SUMPRODUCT B3 B11 A1 C3 C11 冰箱 第五章公式与函数 多条件计数和求和例5 17在入库明细汇总表中 根据要求计算问题2 计算供应商A3的洗衣机入库数量 SUMPRODUCT函数实例 P126 条件3 类别为洗衣机 条件1 供应商的名字为A3 SUMPRODUCT B3 B11 A3 C3 C11 洗衣机 E3 E11 统计数据 入库数量 第五章公式与函数 多条件计数和求和例5 17在入库明细汇总表中 根据要求计算举一反三 1 计算供应商A1或A2的冰箱入库数量 2 计算供应商A1的冰箱或彩电的品种数 SUMPRODUCT函数实例 第五章公式与函数 5 6VLOOKUP函数VLOOKUP函数是在表格或数值组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 它是最常用的函数之一功能 1 指定位置查找和引用数据2 表与表的核对3 利用模糊运算进行区间查询 第五章公式与函数 5 6VLOOKUP函数语法 VLOOKUP 查找目标 查找区域 相对列数 TRUE或FALSE 要找的内容在查找区域中的哪一列 在哪儿查找 注意 查找内容必须在查找区域的第一列 要查找的内容 例 VLOOKUP B2 D 2 H 9 3 0 TRUE 模糊查找 FALSE 精确查找 可以用其1和0代替 第五章公式与函数 5 6VLOOKUP函数 相对列数 查找区域 查找目标 打开EXCEL 精确查找或模糊查找 第五章公式与函数 5 6 1单个区域查找问题1 要求在C列 从员工信息表中根据姓名查找其级别 VLOOKUP函数实例 VLOOKUP B2 G 9 H 14 2 0 要找的内容在查找区域中的第2列 在哪儿查找 员工信息表 注意绝对引用的使用 要查找的内容 张三 精确查找 可以用0代替 第五章公式与函数 5 6 1单个区域查找问题2 要求在D E列 分别根据工资级别和姓名 从基本工资表和提成表查找相应的数值 VLOOKUP函数实例 P130 VLOOKUP C2 G 2 H 7 2 0 要找的内容在查找区域中的第2列 在哪儿查找 基本工资表 注意绝对引用的使用 要查找的内容 工资级别 精确查找 可以用0代替 第五章公式与函数 5 6 2多个区域查找利用以前所学的函数VLOOKUP函数实例 P130 VLOOKUP A2 IF C2 公司1 F 3 G 6 F 10 G 13 2 0 要找的内容在查找区域中的第2列 在哪儿查找 现在有两个表 需要判断的时候就要想到IF函数 要查找的内容 姓名 精确查找 可以用0代替 第五章公式与函数 5 6 3模糊查找计算个人所得税以前学过IF函数条件判断后再求值 但嵌套太多 容易出错 这里运用VLOOKUP函数来解决这个问题VLOOKUP函数实例 P131 C2 要找的内容在查找区域中的第3列 在哪儿查找 要查找的内容 应税所得 模糊查找 可以省略 应税所得 VLOOKUP C2 G 2 I 10 3 VLOOKUP C2 G 2 J 10 4 第五章公式与函数 5 6 4处理查找出现的错误在利用VLOOKUP函数查找时 常遇到下列几种查询错误 参数设置错误空格及不可见字符引起的错误格式不一致引起的错误 第五章公式与函数 1 参数设置错误VLOOKUP函数实例 P131 错误原因 选取查询区域错误 错误原因 省略参数是模糊查找 公式1 VLOOKUP B10 A1 E5 3 0 公式2 VLOOKUP B11 B2 C5 3 0 错误原因 选取查询区域错误 公式3 VLOOKUP B12 B2 E5 3 第五章公式与函数 2 空格及不可见字符引起的错误VLOOKUP函数实例 P132 解决方法 替换不可见字符 解决方法 转换格式 1 空格引起的错误 解决方法 替换空格 2 不可见字符引起的错误 3 数字格式不一致引起的错误 第五章公式与函数 5 7INDIRECT函数INDIRECT是一个非常重要的函数 它可以把随意组合或者插入变量的字符串转换成可以使用的引用 功能 返回由文字串指定的引用 并对引用进行计算 显示其内容 第五章公式与函数 5 7INDIRECT函数语法 INDIRECT 文本字符串 引用类型 TRUE A1类型FALSE R1C1类型省略为A1类型 对单元格的引用或字符串 此单元格可以包含A1样式的引用 定义为引用的名称或对文字串单元格的引用 例 INDIRECT R4C4 0 例 INDIRECT A1 第五章公式与函数 5 6INDIRECT函数 引用类型 文本字符串 打开EXCEL 第五章公式与函数 5 7 1行列转置以前我们学过用选择性粘贴 现在我们来学习用公式进行行列的转置 INDIRECT函数实例 C1 INDIRECT A COLUMN A1 A结合后面的数字组合成一个新的引用 注意相对引用的使用 利用相对绝对的原理 把列数取出和前面的 A 组合成一个新的引用 注意相对引用的使用 第五章公式与函数 5 7 2日报表的自动累计日报表是每天必做的工作 累计工作则是日报表中重要的一项 如果是比较复杂的日报表 手工输入累计值或每张逐一设置公式 是一件很麻烦的事 这时就要用到INDIRECT函数INDIRECT函数实例 INDIRECT DAY C2 1 日 D13 D12 利用取日期中的天数 减去1再加上 日 D13 就得到了上前一天报表的本月累计的引用 前一天报表的D13是前一天的本月累计 再加上今天的本日累计就得出今天的本月累计 第五章公式与函数 5 7 3二级下拉列表设置二级下拉列表是指在选取一级下拉列表内容后 在后面二级下拉列表中可以显示相对应的子列表 光用我们以前学到的数据有效性已经不够了 这儿要用到INDIRECT函数INDIRECT函数实例 INDIRECT A2 这里的A2的内容是 河南省 但用了INDIRECT函数后 返回是的 河南省 所对应的名称代表的区域 举一反三 能不能做三级下拉列表 第五章公式与函数 5 8其他数学函数本节介绍的ROUND和MOD函数用法简单 但用途却极为广泛 5 8 1用ROUND函数处理工资表的计算误差语法 ROUND 数字 指定的位数 如果指定的位数大于0 则舍入到指定的小数位 如果指定的位数等于0 则舍入到最接近的整数 如果指定的位数小于0 则在小数舍入 例 ROUND 25 265 2 25 27 例 ROUND 25 265 0 25 例 ROUND 25 265 1 30 第五章公式与函数 5 8 1ROUND函数 指定的位数 数字 打开EXCEL 第五章公式与函数 5 8 1用ROUND函数处理工资表的计算误差ROUND函数实例 P134 ROUND G4 2 通过设置小数点位数 只是显示上保留两位小数 实质上单元内部的小数位数并没有改变 而用ROUND函数是实质上把多余的位数舍掉了 而不仅仅是在显示上 第五章公式与函数 5 8 2用MOD函数隔行填充颜色功能 返回两数相除的余数 结果的正负号与被除数相同 语法 MOD 被除数 除数 例 MOD 4 2 0例 MOD 5 2 1例 MOD 10 4 2例 MOD 10 4 2 第五章公式与函数 5 8 2MOD函数 除数 被除数 打开EXCEL 第五章公式与函数 5 8 2用MOD隔行填充颜色MOD函数实例 P138 MOD ROW 2 0 返回当前行的行数 第五章公式与函数 5 9其他统计函数统计函数是工作中常用的函数 以前我们学过SUM COUNT等函数 本节将介绍其他几个统计函数 5 9 1用COUNTA函数自动统计工资表人数功能 返回参数组中非空值的数目 语法 COUNTA 参数1 参数2 参数3 参数N N最大值为30 可以进行多工作表的三维引用注意和COUNT函数的对比 COUNT函数只统计数值型数据 例 COUNTA A1 B67 第五章公式与函数 5 9 1COUNTA函数 参数2 参数1 打开EXCEL 第五章公式与函数 5 9 1用COUNTA函数自动统计工资表人数COUNTA函数实例 COUNTA B2 B5 不便于插入行 COUNTA INDIRECT B2 B ROW 1 利用INDIRECT函数把字符串转换为引用 利用ROW函数把当前行数减去1 得出上一行的行数 第五章公式与函数 5 9 2用MAX函数设置变动序号MAX和MIN函数是分别求最大值和最小值的函数 它们常在复杂的数组公式中出现 功能 MAX求一组数中的最大值 MIN求一组数中的最小值语法 MAX 数值1 数值2 MIN 数值1 数值2 MAX和MIN函数实例 P141 第五章公式与函数 MAX和MIN函数实例 P141 设置序号 要求 序号随行的删除可自动调整为新的连续序号 在小计行 合计行和空行前不加序号 IF OR B6 B6 小计 B6 合计 MAX A 1 A5 1 注意绝对引用和相对引用的使用 第五章公式与函数 5 9 3用LARGE和SMALL实现销售数量自动排名功能 LARGE求一组数中的第N个最大值 SMALL求一组数中的第N个最小值语法 LARGE 一组数值或单元格区域 第N个最大值 SMALL 一组数值或单元格区域 第N个最小值 LARGE和SMALL函数实例 LARGE B2 B17 1 SMALL B2 B17 1 当有两个第二大值相等的时候 一个会作为第二大 另一个作为第三大 第五章公式与函数 5 10其他查找引用函数查找引用函数在单元格查询 数据表之间的取数 核对方面有着极其广泛的用途 前面我们学过VLOOKUP函数 以下介绍其他查找引用函数5 10 1ROW和COLUMN生成公式变动函数语法 ROW 引用的单元格或单元格区域 COLUMN 引用的单元格或单元格区域 如果引用的单元格或单元格区域省略 返回的为当前行号 如果引用的是一个单元格 返回的是引用单元格所在的行号或列号 如果引用的是一个单元格区域 返回的是单元格区域左上角所在的行号或列号 第五章公式与函数 5 10其他查找引用函数如果公式在A10单元格 例 ROW 例 ROW C25 例 ROW D2 E10 例 COLUMN 例 COLUMN C25 例 COLUMN D2 E10 10 25 2 1 3 4 第五章公式与函数 5 10其他查找引用函数ROW和COLUMN函数实例 C14 VLOOKUP B14 B 2 F 9 2 0 C15 VLOOKUP B15 B 2 F 9 COLUMN B1 0 这两个公式的区别在于相对列数的不同 第一个公式直接用数字2 后面的公式必须要手工来改相对列数 这样不便于公式的复制 第二个公式用的是COLUMN B1 这样随着公式向右的拖动 列发生改变 函数返回的值也随之改变 就可以实现公式的轻松复制 第五章公式与函数 5 10其他查找引用函数ROW和COLUMN函数实例 I3 LARGE F 2 F 9 1 J3 LARGE F 2 F 9 ROW A1 这两个公式的区别在于第N大值的不同 第一个公式直接用数字1 后面的公式必须要手工来改第N大值 这样不便于公式的复制 第二个公式用的是ROW A1 这样随着公式向下的拖动 行发生改变 函数返回的值也随之改变 就可以实现公式的轻松复制 第五章公式与函数 5 10其他查找引用函数5 10 2MATCH和INDEX实现双向查找MATCH是查询函数 INDEX是引用函数 在实际查找并返回值过程中 MATCH和INDEX总是结对出现在公式中 功能 MATCH返回在指定方式下与指定数值匹配的数组中元素的相应位置INDEX返回表格 区域中的数值或数值的引用 第五章公式与函数 5 10其他查找引用函数5 10 2MATCH和INDEX实现双向查找语法 MATCH 查找的值 查找区域 查找类型 查找的类型为三种 1 0 1如果为1 查找小于或等于查找值的最大数值 如果为0 查找等于查找值的第一个数值 如果为 1 查找大于或等于查找值的最小数值 如果省略 则默认为1 在哪儿查找 要查找的内容 第五章公式与函数 5 10其他查找引用函数5 10 2MATCH和INDEX实现双向查找语法 INDEX 区域 行数 列数 行数和列数 是指相对于该区域的行数和列数 而并非相对整个工作表的行数和列数 为单元格区域或数组常数 MATCH和INDEX函数实例 P144 INDEX A 1 H 6 MATCH A11 A 1 A 6 0 MATCH B11 A 1 H 1 0 第五章公式与函数 行数 为单元格区域或数组常数 INDEX A 1 H 6 MATCH A11 A 1 A 6 0 MATCH B11 A 1 H 1 0 列数 查找的类型为0 查找等于查找值的第一个数值 第一个结果是2 表示行数为2 第二个结果为4 表示列数为4 在哪儿查找 要查找的内容 部门 3月费用计划 第五章公式与函数 5 10文本函数5 10 1字符串的查找和截取功能 LEFT 取左边的N个字符RIGHT 取右边的N个字符MID 根据指定位置取指定位数字符LEN 字符串的字符数FIND 在指定字符串中查找指定字符的位置SEARCH 查找特定字符或文本串的位置 第五章公式与函数 5 11时间函数5 11 1用TODAY和NOW计算和更新库龄本小节介绍的是能分别显示当前日期函数TODAY和当前时间函数NOW 它们的特点可以随着日期和时间的变化而变化 但这种更新是在工作簿打开或工作表重新计算的前提下 在下面几中常见情况下 工作表会重新计算 1 双击工作表中的某个单元格 2 编辑任一个单元格内容 3 按F9键 第五章公式与函数 5 11时间函数5 11 1用TODAY和NOW计算和更新库龄功能 TODAY 显示当前日期NOW 显示当前时间语法 TODAY NOW 二者均没有参数 但括号不能省略 第五章公式与函数 5 11时间函数5 11 1日期格式的拆分与组合在对日期进行处理时 常根据实际需要对日期进行拆分和组合功能 YEAR 返回具体日期的年份MONTH 返回具体日期的月份DAY 返回具体日期的天数DATE 根据已知年 月 日数值 返回具体日期WEEKDAY 转换日期为星期中的一天 一般用于判断是一周的第几天 第五章公式与函数 5 11 1日期格式的拆分与组合语法 YEAR 日期 MONTH 日期 DAY 日期 DATE 年 月 日 WEEKDAY 日期 返回值的类型 1989 07 08 1989 07 08 第五章公式与函数 5 11 1日期格式的拆分与组合语法 WEEKDAY 日期 返回值的类型 第五章公式与函数 5 11时间函数5 11 2用DATEDIF计算日期间隔DATEIF是EXCEL的一个隐藏函数 且在EXCEL2002以前版本中 帮助文件没有它的介绍 但事实上它是一个十分有用的函数 功能 可以计算两日期之间的天数 月数 年数语法 DATEDIF 开始日期 结束日期 单位代码 开始日期要比结束日期要早 否则返回错误值 Y 返回整年数 M 返回整月数 D 返回整天数 MD 返回天数差 忽略日期的年和月 YM 返回月份差 忽略日期的年和天数 YD 返回天数差 忽略日期的年 第五章公式与函数 5 11 3设置A1的值2010 12 22 B1的值为2011 12 31 例 DATEDIF A1 B1 Y 例 DATEDIF A1 B1 M 例 DATEDIF A1 B1 D 例 DATEDIF A1 B1 MD 例 DATEDIF A1 B1 YM 例 DATEDIF A1 B1 YD 1 12 小结 本节介绍了在工作中常用到的函数 其应用实例也是根据在工作生活中常遇到的问题而设置的 函数功能非常强大 实际工作中不要仅局限于函数的基本用法 而是要结合函数的特点去灵活运用 第六章宏 6 1宏 指一系列EXCEL能够执行的VBA语句 6 2宏的运用6 2 1宏的录制6 2 2宏的简单编辑 第六章宏 6 3宏的执行6 3 1宏的一般执行6 3 2宏的执行按扭 第七章综合实例 6 1综合实例车间排班表祝你天天快乐乘法口决办公室醒脑小游戏 第七章综合实例 6 2综合实例考勤汇总人事管理10000以内的加减法小学生字拼音练习 常用的学习方式 Excel宝典腾讯课堂 Excel的日常运用中遇到问题的处理方法 谢谢