公开课-excel(函数)初级数组公式入门(轩辕轼轲).xls

一 数组公式概述 1 什么是数组公式 2 数组公式的计算原理 3 数组的分类 4 简单的数组公式实例 5 数组公式应用的注意事项 二 数组公式应用实例 1 实例1 条件求和 1 求各班优秀的人数 2 求产品销量 3 注意事项 2 实例2 数组重构 1 利用 1 0 重构数组进行多条件查找 2 利用 1 0 重构数组进行逆向查找 3 HLOOKUP查找函数与 1 0 的组合运用 4 利用CHOOSE函数对数组重新排列 5 知识共享 自定义一个比VLOOKUP更强大查找函数LOOK 3 实例3 数组函数解方程 1 鸡兔同笼问题 2 计算客人数量 三 课前自学 矩阵的运算规则 数数组组公公式式概概述述 1 什么是数组公式 数组就是数据单元的集合或是一组数值的集合 数组公式就是用可以产生多个结果的集合或对可以存放在行和列中的一组参数的集 合进行运算的单个公式或者一组公式 数组公式的特点 可以执行多重计算 公式中的函数或者表达式的返返回回值值是一组数据结果 数组公式的马甲 数组公式的快捷键 Ctrl Shift Enter 2 数组公式的计算原理 1414 SUM 2 3 4 2 SUM 8 6 14 1111 SUM 2 3 4 2 SUM 2 4 3 2 SUM 6 5 11 在数组运算中 就是乘法运算 就是加法运算 我们也可以从 公式求值 中看出运算过程 第一个公式 Sum 2 4 3 2 Sum 8 6 14 第二个公式 Sum 2 4 3 2 Sum 6 5 11 数组公式的计算本质上是矩阵的加法和乘法 A 矩阵的与运算 即数组乘法运算 如果参与运算的是逻辑值就相当于AND函数 B 矩阵的或运算 即数组的加法运算 如果参与运算的是逻辑值就相当于OR函数 注意 逗号分隔符是列分隔符 分号分隔符是行分隔符 比比如如数数组组 1 0 0 0 1 0 0 0 1 1 0 0 0 1 0 0 0 1 和和 1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7 8 9 这这两两个个数数组组都都是是三三行行三三列列的的数数组组 Sum 1 0 0 0 1 0 0 0 1 1 2 3 4 5 6 7 8 9 1 15 51 15 5 Sum 1 0 0 0 1 0 0 0 1 1 2 3 4 5 6 7 8 9 4 48 84 48 8 课课后后思思考考题题 数数组组的的减减法法和和除除法法是是怎怎么么计计算算的的 C 不同大小矩阵计算原理 未未扩扩充充扩扩充充后后未未扩扩充充 2 2 3 3 2 2 3 3 4 4 2 2 4 4 2 28 8 6 6 2 2 3 3 4 4 2 2 1 0 0 0 1 0 0 0 1 1 2 3 4 5 6 7 8 9 1 0 0 0 5 0 0 0 9 1 0 0 0 1 0 0 0 1 1 2 3 4 5 6 7 8 9 2 2 3 4 6 6 7 8 10 242426 6868612 1 2 3 4 2 1 2 3 4 2 2 2 2 1 2 3 4 2 3 结果 2 4 6 8 3 数组的分类 按照数组的性质分 常数数组 区域数组 内存数组 按照数组的维度分 一维数组 二维数组 三维数组 多维数组 常常数数数数组组可以是一维的也可以是二维的 一维数组可以是垂直的也可以是水平 区域数组即一个单元格区域 数组中的元素用逗号 分开 数组中的元素用分号 分开 一维水平数组 也简称为水平数组 一维垂直数组 也简称为垂直数组 10 20 30 40 50 100 200 300 400 500 600 常数数组的注意事项 A 常数二维数组 对于二维数组 用逗号将一行内的元素分开 用分号将各行分开 B 不可以在数组公式中使用列出常数的方法列出单元引用 名称或公式 例如 2 3 3 3 4 3 因为列出了多个公式 是不可用的 A1 B1 C1 因为列出多个引用 也是不可用的 不过可以使用一个区域 例如 A1 C1 C 对于数组常量的内容 可由下列规则构成 数组常量可以是数字 文字 逻辑值或错误值 数组常量中的数字 也可以使用整数 小数或科学记数格式 文字必须以双引号括住 D 同一个数组常量中可以含有不同类型的值 数组常量中的值必须是常量 不可以是公式 常量不能含有货币符号 括号或百分比符号 所输入的数组常量不得含有不同长度的行或列 区区域域数数组组就是数组内容为单元格区域的引用 如 A1 F10 4 8 12 内内存存数数组组就是数组内容为Excel内存常量的数组 如ROW 1 10 COLUMN A F 4 简单的数组公式实例 实例一 姓名班级性别分数 问题1 计算一班男生的个数 A一班男57 SUM C125 C132 一班 D125 D132 男 B一班男0答案 3 C二班男64 D一班女52 问题2 求一班女生的总分 E二班男75 SUM C125 C132 一班 D125 D132 女 E125 E132 F一班女54答案 106 G一班男79 H二班女60 思考 如果求一班女生的平均分怎么办 SUM SUM 一班男 一班男 二班男 一班女 二班男 一班女 一班男 二班女 SUM SUM 一班 一班 男 男 一班 一班 男 男 二班 一班 男 男 一班 一班 女 男 二班 一班 男 男 一班 一班 女 男 一班 一班 男 男 二班 一班 女 男 SUM SUM TRUETRUE TRUETRUE FALSETRUE TRUEFALSE FALSETRUE TRUEFALSE TRUETRUE FALSEFALSE SUM SUM TRUE TRUE SUM SUM 1 TRUE TRUE1 FALSE TRUE0 TRUE FALSE0 FALSE TRUE0 TRUE FALSE0 TRUE TRUE1 一班 FALSE FALSE0 实例二 求销售总金额 一一般般公公式式 数数组组公公式式 品名销售数量销售单价销售金额品名销售数量 BCD 209 30240072000 BCD 209 30 BCD 210 26198751662 BCD 210 26 BCD 219 602475148500 BCD 219 60 BCD 230 801958156640 BCD 230 80 BCD 158 50134567250 BCD 158 50 BCD 168 701479103530 BCD 168 70 BCD 178 10167816780 BCD 178 10 求求总总和和616362一一次次求求出出总总和和 常常规规公公式式一一 数数组组方方法法的的公公式式 第一步 C176 D176 SUM H176 H182 I176 I182 第二部 SUM E176 E182 常常规规公公式式二二 SUMPRODUCT H176 H182 I176 I182 5 数组公式应用的注意事项 A 多使用公式审核工具 要看懂别人复杂的数组公式 就要用到公式审核工具 在自己写公式的过程中 想看看有些复杂的部分其运行结果如何 是否符合自己的想象 也离不开公式审核工具 公式审核工具有三个 第一个 F9 这是用得最多的 第二个工具是菜单 工具 公式审核中的 公式求值 A 第三个是 插入函数 的功能 fxfxB A B 要清楚逻辑值与数值之间的转换关系 D TRUE TRUE 1TRUE TRUE 1 TRUE FALSE 0TRUE FALSE 1 SUM H140 H143 A I140 I143 FALSE FALSE 0FALSE FALSE 0 在加减乘除的运算中TRUE 1 FALSE 0 所以逻辑值能直接参与运算 但是SUM函数中如果引用的数据区域中含有逻辑值的话 是忽略逻辑值的 除非是直接作为参数 所以当你需要在SUM中使数据区域中的TRUE 1 FALSE 0的话 必须要使TRUE或FALSE先参与一次四则运算 转换成数值 TRUE 1 1 从左边的计算来看 好像TRUE就是1 FALSE就是0 但是 如果你这么理解 碰见下面的问题你就没法解释了 FALSE 1 1 1 1 TRUE FALSE 2 3 4 这个公式的运行结果是FALSE 1 FALSE TRUEFALSEFALSE 0 TRUE FALSE 计算机运算的规则是 从左到右顺序计算 并且数字 字 母 逻辑值 所以 TRUE 4 不是真的 执行结果是FALSE 1 SUM TRUE FALSE 0 SUM C41 C42 1 SUM C41 1 C42 1 逻辑值转换数值的方法 1 1 0 0等等 不 怕麻烦 写成SQRT TRUE 2也可以 C 熟悉ROW 函数与COLUMN 函数 2 SUM SUM SUM SUM 7 6 SMALL C 224 C 229 ROW A1 下拉 4 对左侧数据进行升序排序 1 SMALL C 224 C 229 COLUMN A1 右拉 5 D 数组中尽量避免使用易失性函数 OFFSET INDIRECT TODAY NOW RAND CELL INFOOFFSET INDIRECT TODAY NOW RAND CELL INFO E 多用 多单元格数组公式 多单元格数组公式 又称 联合数组公式 最大的优点就是计算速度快 单个单元格数组公式 每个单元格的公式都要从头计算 不管两个单元格公式多么相像 而多单元格数组公式呢 整个区域N个公式只计算一次 然后直接把得到的N个结果分别赋值给这N个单元格 F 不要过于迷信数组公式 喜欢她 但不要迷恋她 COUNTIF SUMIF等函数不支持数组 大量的使用数组公式会让EXCEL疲于计算 拖慢表格 特别是使用了易失性函数后 扩扩充充后后 数数组组公公式式概概述述 数组就是数据单元的集合或是一组数值的集合 数组公式就是用可以产生多个结果的集合或对可以存放在行和列中的一组参数的集 合进行运算的单个公式或者一组公式 SUM 2 4 3 2 SUM 6 5 11 Sum 1 0 0 0 1 0 0 0 1 1 2 3 4 5 6 7 8 9 Sum 1 0 0 0 1 0 0 0 1 1 2 3 4 5 6 7 8 9 26 612 1 2 3 4 2 3 2 3 结果 2 6 6 12 100 200 300 400 500 常常数数数数组组可以是一维的也可以是二维的 一维数组可以是垂直的也可以是水平 区域数组即一个单元格区域 一维垂直数组 也简称为垂直数组 100 200 300 400 500 600 B 不可以在数组公式中使用列出常数的方法列出单元引用 名称或公式 例如 2 3 3 3 4 3 因为列出了多个公式 是不可用的 A1 B1 C1 因为列出多个引用 也是不可用的 不过可以使用一个区域 例如 A1 C1 C 对于数组常量的内容 可由下列规则构成 数组常量可以是数字 文字 逻辑值或错误值 数组常量中的数字 也可以使用整数 小数或科学记数格式 文字必须以双引号括住 D 同一个数组常量中可以含有不同类型的值 数组常量中的值必须是常量 不可以是公式 常量不能含有货币符号 括号或百分比符号 所输入的数组常量不得含有不同长度的行或列 男 3 3 问题1 计算一班男生的个数 SUM C125 C132 一班 D125 D132 男 问题2 求一班女生的总分 SUM C125 C132 一班 D125 D132 女 E125 E132 思考 如果求一班女生的平均分怎么办 销售单价销售金额 2400 1987 2475 1958 1345 1479 1678 616362 1 7 7 3 6 3 一一次次求求出出总总和和 SUM H176 H182 I176 I182 在自己写公式的过程中 想看看有些复杂的部分其运行结果如何 是否符合自己的想象 也离不开公式审核工具 SUM H140 H143 A I140 I143 但是SUM函数中如果引用的数据区域中含有逻辑值的话 是忽略逻辑值的 除非是直接作为参数 所以当你需要在SUM中使数据区域中的TRUE 1 FALSE 0的话 必须要使TRUE或FALSE先参与一次四则运算 转换成数值 从左边的计算来看 好像TRUE就是1 FALSE就是0 但是 如果你这么理解 碰见下面的问题你就没法解释了 2 3 4 这个公式的运行结果是FALSE 计算机运算的规则是 从左到右顺序计算 并且数字 字 母 逻辑值 所以 TRUE 96 I 6 I 14 B20 思考题 求202班同学中有多少人来自雁塔区的数组公式怎么写 2 求产品销量 类型 日期 单价 销售数量 A2005 10 15100099 B2005 8 20100016 B2005 8 1400071 C2005 7 10400062 B2005 7 1590004 B2005 8 20900015 A2005 9 30100083 A2005 10 10100028 C2005 10 15400015 多条件求销量 B产品8月销售量 102 SUM B35 B43 B MONTH C35 C43 8 E35 E43 A产品10月销售额 127000 SUM B35 B43 A MONTH C35 C43 10 E35 E43 D35 D43 计算8月份前不包括 B产品销量和和8月后 不包括C产品销量 公式一 SUM B35 B43 B MONTH C35 C43 8 B35 B43 C MONTH C35 C43 8 E35 E43 272 公式二 SUM NOT B35 B43 B MONTH C35 C43 8 E35 E43 272 3 注意事项 1 SUM函数有1 255个参数 参数可以是区域也可以是表达式 如 SUM A1 10 25 A2 8 2 如果是文本型数字参数 SUM是可以直接求和的 但如果引用单元格内容是文本型数值 则求和时忽略 如 SUM 10 10 计算结果为20 如果A1单元格为文本10 则 SUM 10 A1 的结果为10 3 如果单元格中的数值时文本型的并且需要求和 可以将他们转换成数值 转换方式有多种 A 使用Value函数 B 利用 1 1 0 等等 4 对于SUM型的数组公式 可以用SUMPRODUCT函数转换成普通公式 5 善用加载宏里面的 条件求和向导 6 要注意数组函数中 的使用 以及括号的位置 7 注意SUM函数对逻辑值的处理方式 SUM TRUE 1 结果等于2对于逻辑值可以直接求和 SUM TRUE 1 结果等于1对于包含在数组中的逻辑值则忽略 SUM TRUE 1 1 结果等于2将逻辑值转换成数值后再求和 SUM VALUE TRUE 1 结果错误VALUE函数无法转换逻辑值 只能转换文本型数值 家庭地址 陕西西安碑林区 西安雁塔区王家村 西安市雁塔区雁塔南路 雁塔区朱雀大街 碑林区长乐西路 西安雁塔区长安南路 陕西西安雁塔区雁展路 西安莲湖区西关正街 西安市黄雁村 政治 0 2 1 0 0 条条件件求求和和 求各班级的优秀者人数 96分以上为优秀 这种公式虽然用的是SUM 但是实质是计数性质的 用数组公式示例如下 优秀人数 96分以上 普通公式 SUM B35 B43 B MONTH C35 C43 8 E35 E43 SUM B35 B43 A MONTH C35 C43 10 E35 E43 D35 D43 公式一 SUM B35 B43 B MONTH C35 C43 8 B35 B43 C MONTH C35 C43 8 E35 E43 公式二 SUM NOT B35 B43 B MONTH C35 C43 8 E35 E43 2 如果是文本型数字参数 SUM是可以直接求和的 但如果引用单元格内容是文本型数值 则求和时忽略 3 如果单元格中的数值时文本型的并且需要求和 可以将他们转换成数值 转换方式有多种 VALUE函数无法转换逻辑值 只能转换文本型数值 数数组组重重构构 1 利用 1 0 重构数组进行多条件查找 姓名工号性别籍贯出生年月 张三丰KT001男北京1970年8月 李四光KT002女天津1980年9月 赵六儿KT003男河南1981年12月 王麻子KT004男河北1975年3月 赵六儿KT005女河南1985年12月 查找名叫 赵六儿 的女员工的籍贯 赵六儿女河南公式 VLOOKUP B17 0 的组合运用 姓名张三丰李四光赵六儿王麻子赵六儿 工号KT001KT002KT003KT004KT005 性别男女男男女 籍贯北京天津河南河北河南 赵六儿女河南公式 HLOOKUP B540 B49 G490 B50 G50 B49 G49 2 0 重构后的数组如下 工号KT001KT002KT003KT004KT005 姓名张三丰李四光赵六儿王麻子赵六儿 4 利用CHOOSE函数对数组重新排列 姓名工号性别籍贯出生年月 张三丰KT001男北京1970年8月 李四光KT002女天津1980年9月 赵六儿KT003男河南1981年12月 王麻子KT004男河北1975年3月 赵六儿KT005女河南1985年12月 B82 F87数组的列序进行逆序输出 CHOOSE 5 4 3 2 1 B73 B78 C73 C78 D73 D78 E73 E78 F73 F78 出生年月籍贯性别工号姓名 1970年8月北京男KT001张三丰 1980年9月天津女KT002李四光 1981年12月河南男KT003赵六儿 1975年3月河北男KT004王麻子 1985年12月河南女KT005赵六儿 因而CHOOSE函数可以代替IF 1 0 并且比IF 1 0 更强大 何以见得 请看下面的例子 工号姓名性别出生年月籍贯 KT001 KT002 KT003 KT004 KT005 课后思考题 如何对数据区域的行进行重新排列 知知识识共共享享 自自定定义义一一个个比比VLOOKUPVLOOKUP更更强强大大查查找找函函数数LOOKLOOK 代代码码 vlookup精确查找增强版 强化处 1 可以通过第四参数轻松获取重复对象 即有多个查找目标时一并罗列出来 2 不仅可以从左向右查找 也可以从右向左查找 3 第二参数可以指定一列 也可以多列 函数可以在超出范违的区域中返回值 Function LOOK 查找值 As String 区域 As Range 列 As Integer 索引号 As Integer As String Application Volatile 将函数设置为易失性函数 On Error Resume Next Dim i As Long For i 1 To 区域 Rows Count If 区域 i 1 查找值 Then j j 1 If j 索引号 Then LOOK 区域 1 Offset i 1 列 1 Exit Function Next i End Function 工号姓名性别籍贯出生年月 KT001张三丰男北京1970年8月 KT002李四光女天津1980年9月 KT003赵六儿男河南1981年12月 KT004王麻子男河北1975年3月 KT005赵六儿女河南1985年12月 赵六儿 超出区域查找 公式 LOOK B124 C128 C126 3 1 逆向查找 公式 LOOK B124 C128 C126 0 1 返回第二个符合条件的 公式 LOOK B124 C128 C126 0 2 代代码码 vlookup精确查找增强版 强化处 1 可以通过第四参数轻松获取重复对象 即有多个查找目标时一并罗列出来 2 不仅可以从左向右查找 也可以从右向左查找 3 第二参数可以指定一列 也可以多列 函数可以在超出范违的区域中返回值 Function LOOK 查找值 As String 区域 As Range 列 As Integer 索引号 As Integer As String Application Volatile 将函数设置为易失性函数 On Error Resume Next Dim i As Long For i 1 To 区域 Rows Count If 区域 i 1 查找值 Then j j 1 If j 索引号 Then LOOK 区域 1 Offset i 1 列 1 Exit Function Next i End Function 数数组组重重构构 VLOOKUP B170 B49 G490 B50 G50 B49 G49 2 0 CHOOSE 5 4 3 2 1 B73 B78 C73 C78 D73 D78 E73 E78 F73 F78 代代码码 vlookup精确查找增强版 强化处 1 可以通过第四参数轻松获取重复对象 即有多个查找目标时一并罗列出来 2 不仅可以从左向右查找 也可以从右向左查找 3 第二参数可以指定一列 也可以多列 函数可以在超出范违的区域中返回值 Function LOOK 查找值 As String 区域 As Range 列 As Integer 索引号 As Integer As String Application Volatile 将函数设置为易失性函数 On Error Resume Next Dim i As Long For i 1 To 区域 Rows Count If 区域 i 1 查找值 Then j j 1 If j 索引号 Then LOOK 区域 1 Offset i 1 列 1 Exit Function Next i End Function 代代码码 vlookup精确查找增强版 强化处 1 可以通过第四参数轻松获取重复对象 即有多个查找目标时一并罗列出来 2 不仅可以从左向右查找 也可以从右向左查找 3 第二参数可以指定一列 也可以多列 函数可以在超出范违的区域中返回值 Function LOOK 查找值 As String 区域 As Range 列 As Integer 索引号 As Integer As String Application Volatile 将函数设置为易失性函数 On Error Resume Next Dim i As Long For i 1 To 区域 Rows Count If 区域 i 1 查找值 Then j j 1 If j 索引号 Then LOOK 区域 1 Offset i 1 列 1 Exit Function Next i End Function 数数组组函函数数解解方方程程 数组函数强大之处就在于 只要我们指定了计算范围 它会讲范围内的每一个结果输出出来 因此 数组函 数可以轻松的解答一次方程 二元一次方程至多元一次方程 都可以使用数组枚举出正确答案 1 鸡兔同笼问题 鸡 免共七十九只 二百只脚在笼里走 试问多少只鸡 多少只兔 常规解法 先设鸡X只 兔Y只 X Y 79 2X 4Y 200 然后再求二元一次方程 数组枚举法 公式 鸡 MAX IF ROW 1 79 2 79 ROW 1 79 4 200 ROW 1 79 0 只 兔子 79 MAX IF ROW 1 79 2 79 ROW 1 79 4 200 ROW 1 79 0 只 计算结果 鸡58只 兔子21只 如果头数和脚数是变数 则公式可以扩展如下 头数100 脚数342 公式 鸡 MAX IF ROW INDIRECT 1 C24 2 C24 ROW INDIRECT 1 C24 4 C25 ROW INDIRECT 1 C24 0 只 兔子 C24 MAX IF ROW INDIRECT 1 C24 2 C24 ROW INDIRECT 1 C24 4 C25 ROW INDIRECT 1 C24 0 只 计算结果 鸡29只 兔子71只 2 计算客人数量 一家正在操办喜事 有好事者问 共有多少客人 主人曰 客人每二人共用一只菜碗 每三人共用一只汤碗 每四人共用一只饭碗 共用去碗65只 算算共有多少客人 公式 SUM IF ROW 1 500 2 ROW 1 500 3 ROW 1 500 4 65 ROW 1 500 0 计算结果60 数数组组函函数数解解方方程程 数组函数强大之处就在于 只要我们指定了计算范围 它会讲范围内的每一个结果输出出来 因此 数组函 数可以轻松的解答一次方程 二元一次方程至多元一次方程 都可以使用数组枚举出正确答案 鸡 MAX IF ROW 1 79 2 79 ROW 1 79 4 200 ROW 1 79 0 只 兔子 79 MAX IF ROW 1 79 2 79 ROW 1 79 4 200 ROW 1 79 0 只 鸡 MAX IF ROW INDIRECT 1 C24 2 C24 ROW INDIRECT 1 C24 4 C25 ROW INDIRECT 1 C24 0 只 兔子 C