数组公式经典教程r 2003.xls

数数组组公公式式 Part I简简述述 数数组组公公式式 是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式 数组公式的特点就是可以执行多重计算 它返回的是一组数据结果 由于一个单元格内只能储存一个数值 所以当结果是一组数据时 单元格只返回第一个值 如 23 23 24 25 22 如果你需要用到所有的运算结果时 要么用多个单元格去分别返回 如 23 INDEX 23 24 25 22 1 24 INDEX 23 24 25 22 2 25 INDEX 23 24 25 22 3 22 INDEX 23 24 25 22 4 要么用某些函数来取其共性 如SUM MAX MIN 等 94 SUM 23 24 25 22 25 MAX 23 24 25 22 参参数数 数组公式最大的特征就是所引用的参数是数组参数 包括区域数组和常量数组 区域数组 是一个矩形的单元格区域 如 A 1 D 5 常量数组 是一组给定的常量 如 1 2 3 或 1 2 3 或 1 2 3 1 2 3 数组公式中的参数必须为 矩形 如 1 2 3 1 2 就无法引用了 输输入入 同时按下CTRL SHIFT ENTER 数组公式的外面会自动加上大括号 予以区分 55 SUM I 23 I 25 10 J 23 J 25 1011 2022 3033 有的时候 看上去是一般应用的公式也应该是属于数组公式 只是它所引用的是数组常量 对于参数为常量数组的公式 则在参数外有大括号 公式外则没有 输入时也不必按CTRL SHIFT ENTER 55 SUM 10 20 30 10 11 22 33 可以看到 这两个公式的结果是一样的 接下来让我们看看数组公式是如何进行工作的 I m from Eraser说 说 数组公式可以用几个一般公式和一些过渡数据 来代替 从而达到同样的目的 不用数组公式 地球同样转 所以千万不要迷信 数组公式 以免走火入魔 呵呵 21 AA 31 BB 42 CC 52 DD 83 EE 43 DD 94 CC 14 EE 05 AA 35 AA 76 CC 21 CC cc88 Part I End 对于参数为常量数组的公式 则在参数外有大括号 公式外则没有 输入时也不必按CTRL SHIFT ENTER Eraser说 说 数组公式可以用几个一般公式和一些过渡数据 来代替 从而达到同样的目的 不用数组公式 地球同样转 所以千万不要迷信 数组公式 以免走火入魔 呵呵 数数组组公公式式 Part II数数组组公公式式的的计计算算过过程程 产产品品编编号号产产品品单单价价产产品品数数量量 AA1100 BB2150 CC3200 AA1250 BB2300 问题 求产品AA的总价 350 SUM IF B 4 B 8 AA C 4 C 8 D 4 D 8 0 它它的的运运算算过过程程是是这这样样的的 1 IF函数先判断 B 4 B 8里 AA的参数 返回的结果为 AATRUE1100 BBFALSE2150 CCFALSE3200 AATRUE1250 BBFALSE2300 用公式表述为 SUM IF TRUE FALSE FALSE TRUE FALSE C 4 C 8 D 4 D 8 0 2 然后对返回TRUE的行项执行下一个运算 C 4 C 8 D 4 D 8 对于返回FALSE的 则直接返回0 TRUE1100100 1 100 FALSE21500 0 FALSE32000 0 TRUE1250250 1 250 FALSE23000 0 3 到了这一步 公式返回出的是一组符合要求的数字了 该组数字用数组常量表示为 100 0 0 250 0 4 最后用SUM函数把这一组数组常量相加 得到最后结果 350 SUM 100 0 0 250 0 该公式可简化为 350 SUM B 4 B 8 AA C 4 C 8 D 4 D 8 在在加加减减乘乘除除的的运运算算中中TRUE 1 FALSE 0 所所以以逻逻辑辑值值能能直直接接参参与与运运算算 但但是是SUM函函数数中中如如果果引引用用的的数数据据区区域域中中含含有有逻逻辑辑值值的的话话 是是忽忽略略逻逻辑辑值值的的 除除非非是是直直接接作作为为参参数数 所以当你需要在SUM中使数据区域中的TRUE 1 FALSE 0的话 必须要使TRUE FALSE先参与一次四则运算 转换成数值 看看下面的例子能帮助你理解逻辑值是如何出来的 并能更好地理解上面这段话 TRUE 1 1 FALSE 1 1 1 1 TRUE FALSE 1 FALSE TRUE 0 TRUE FALSE 1 SUM TRUE FALSE 0 SUM C41 C42 1 SUM C41 1 C42 1 IF 的判断可以被简化 也就是利用了这一点 在Part IV里 还有更详细的例子 相信你现在已经对数组公式有了个大概的了解了 下面让我们来看一些数组公式的应用 I m from Eraser说 说 TRUE和FALSE的问题 在非数组公式中也是着很 广泛的运用的 了解这些是非常有帮助的 所以我在这里也说了许多 希望你们不要说我罗 嗦哦 Part II End 但但是是SUM函函数数中中如如果果引引用用的的数数据据区区域域中中含含有有逻逻辑辑值值的的话话 是是忽忽略略逻逻辑辑值值的的 除除非非是是直直接接作作为为参参数数 所以当你需要在SUM中使数据区域中的TRUE 1 FALSE 0的话 必须要使TRUE FALSE先参与一次四则运算 转换成数值 Eraser说 说 TRUE和FALSE的问题 在非数组公式中也是着很 广泛的运用的 了解这些是非常有帮助的 所以我在这里也说了许多 希望你们不要说我罗 嗦哦 数数组组公公式式 Part IIIRow 函函数数在在数数组组公公式式中中的的运运用用 谈到数组公式 我们不得不说一下ROW 这个函数 它在数组公式中起到了很大的作用 许多公式中都需要用到它来作为参数 我们先来做一个题目 求正整数列1 2 3 4 100这100个数字之和 首先假设你不知道等差数列求和公式 呵呵 5886 SUM ROW A 1 A 100 我们都知道 ROW 是用于返回单元格行号的函数 通常它只能引用一个参数 但是在数组公式中 该函数就能引用多个单元格作为参数 对于整个引用区域进行分别运算 从而就能返回一组数据 ROW A1 1 ROW A2 2 ROW A100 100 从而ROW A 1 A 100 1 2 3 100 知道了这一点以后 我们就能在数组公式中利用这一个功能来得到一组连续的正整数 当然COLUMN 的作用和ROW 是相同的 上面的计算也能用以下公式 5050 SUM COLUMN A 1 CV 1 但是相对于ROW 的引用方式来说 A1 A100要比A1 CV1更直观地体现出所引用的是100行还是100列 所以ROW 一般来说使用得更普遍些 当然也不排除有时候需要用到COLUMN 这就要看具体情况了 举个例子吧 我们在PartI里好象说了个把一组4个数据分别列出 23 24 25 22 当是我是用了 INDEX 23 24 25 22 1 每行的参数都是手工改的 但是现在用了ROW 的话 那么只需要输入第一个公式 其他的拖拽就行了 如下 23 INDEX 23 24 25 22 ROW A1 24 INDEX 23 24 25 22 ROW A2 25 INDEX 23 24 25 22 ROW A3 22 INDEX 23 24 25 22 ROW A4 如果是需要分列来显示 那么就用COLUMN 来的方便了 23242522 I m from 1531 67 2316 34112 4462 67 5656 6141 5 31829 84 Eraser说 说 我偏不用ROW 函数来返回数列 我偏要自 己一个个输入进去 这个嘛 也是可以的哦 Part III End 谈到数组公式 我们不得不说一下ROW 这个函数 它在数组公式中起到了很大的作用 许多公式中都需要用到它来作为参数 但是在数组公式中 该函数就能引用多个单元格作为参数 对于整个引用区域进行分别运算 从而就能返回一组数据 但是相对于ROW 的引用方式来说 A1 A100要比A1 CV1更直观地体现出所引用的是100行还是100列 所以ROW 一般来说使用得更普遍些 当然也不排除有时候需要用到COLUMN 这就要看具体情况了 当是我是用了 INDEX 23 24 25 22 1 每行的参数都是手工改的 但是现在用了ROW 的话 那么只需要输入第一个公式 其他的拖拽就行了 如下 Eraser说 说 我偏不用ROW 函数来返回数列 我偏要自 己一个个输入进去 这个嘛 也是可以的哦 数数组组公公式式 Part IV利利用用数数组组公公式式进进行行条条件 件求求和和 条件求和的公式 我们一般都会使用SUMIF 但是SUMIF只能进行单个条件求和 而数组公式能帮助你进行多条件求和 产产品品编编号号生生产产日日期期产产品品单单价价产产品品数数量量1 求产品BB的8月份产量 AA2000 6 151100625 SUM IF B 7 B 20 BB MONTH C 7 C 20 8 E 7 E 20 0 AA2000 6 201125我们是这样来考虑的 BB2000 6 302150用IF 来判断 如果 B 7 B 20 BB 而且MONTH C 7 C 20 8的话 BB2000 7 102175那么我们就取 E 7 E 20中对应的值 否则就让它等于0 CC2000 7 153200 公公式式中中的的 相相当当于于AND 即即同同时时满满足足条条件件 CC2000 7 203225该公式可以作如下简化 AA2000 7 301250625 SUM B 7 B 20 BB MONTH C 7 C 20 8 E 7 E 20 AA2000 8 101275 BB2000 8 1523002 求产品BB和CC的总产值 BB2000 8 2023255350 SUM IF B 7 B 20 BB B 7 B 20 CC E 7 E 20 D 7 D 20 CC2000 8 303350 公公式式中中的的 相相当当于于OR 即即满满足足条条件件之之一一即即可可 CC2000 10 103375该公式可以作如下简化 DD2000 10 1544005350 SUM B 7 B 20 BB B 7 B 20 CC E 7 E 20 D 7 D 20 DD2000 10 304425 3 求8月份之前AA的产量和8月份之后不包括AA的产品产量之和 26502650 SUM IF MONTH C 7 C 20 8 B 7 B 20 AA E 7 E 20 公公式式中中的的 相相当当于于不不同同时时满满足足 即即要要么么满满足足条条件件1 要要么么满满足足条条件件2 同同时时满满足足的的不不算算 以下公式和上面的公式等价 2650 SUM IF MONTH C 7 C 20 8 B 7 B 20 AA E 7 E 20 还还可可以以用用 来来代代替替 2650 SUM IF B 7 B 20 AA MONTH C 7 C 20 8 E 7 E 20 该公式可以作如下简化 2650 SUM B 7 B 20 AA MONTH C 7 C 20 8 E 7 E 20 简简化化时时 注注意意只只能能用用 而而不不能能用用 有兴趣的朋友可以试试 如果一定要用 来进行简化 该怎么做呢 看完了这些后 Eraser2000再给你布置个家庭作业 求求产产品品AA和和BB在在8月月份份的的总总产产值值 答案在N34单元格 不过先自己做 别先看哦 I m from Eraser说 说 对于公式的简化问题 我个人认为如果不是很熟 练的情况下 最好还是保留IF 语句 因为有IF在的话你对公式将很容易理解 否则时 间一长 不熟练的人可能自己都想不起来当时的 公式是怎么回事了 VALUE Part IV End 条件求和的公式 我们一般都会使用SUMIF 但是SUMIF只能进行单个条件求和 而数组公式能帮助你进行多条件求和 SUM IF B 7 B 20 BB MONTH C 7 C 20 8 E 7 E 20 0 用IF 来判断 如果 B 7 B 20 BB 而且MONTH C 7 C 20 8的话 那么我们就取 E 7 E 20中对应的值 否则就让它等于0 公公式式中中的的 相相当当于于AND 即即同同时时满满足足条条件件 SUM B 7 B 20 BB MONTH C 7 C 20 8 E 7 E 20 SUM IF B 7 B 20 BB B 7 B 20 CC E 7 E 20 D 7 D 20 公公式式中中的的 相相当当于于OR 即即满满足足条条件件之之一一即即可可 SUM B 7 B 20 BB B 7 B 20 CC E 7 E 20 D 7 D 20 3 求8月份之前AA的产量和8月份之后不包括AA的产品产量之和 SUM IF MONTH C 7 C 20 8 B 7 B 20 AA E 7 E 20 公公式式中中的的 相相当当于于不不同同时时满满足足 即即要要么么满满足足条条件件1 要要么么满满足足条条件件2 同同时时满满足足的的不不算算 SUM IF MONTH C 7 C 20 8 B 7 B 20 AA E 7 E 20 SUM IF B 7 B 20 AA MONTH C 7 C 20 8 E 7 E 20 SUM B 7 B 20 AA MONTH C 7 C 20 8 E 7 E 20 简简化化时时 注注意意只只能能用用 而而不不能能用用 有兴趣的朋友可以试试 如果一定要用 来进行简化 该怎么做呢 看完了这些后 Eraser2000再给你布置个家庭作业 答案在N34单元格 不过先自己做 别先看哦 数数组组公公式式 Part V利利用用数数组组公公式式进进行行条条件件记记数数 看完了如何使用数组公式求和后 我还要顺便说一下数数的问题 一般用COUNTIF可以进行条件计数 可是两个或以上条件时 COUNTIF就不行了 在这里我们可以用数组公式来解决 记数的问题 大多数情况下是用SUM来解决 是因为数数其实也是求和的一种 只不过是若干个1相加而已 但是SUM中参数的变化可就真谓千变万化了 产产品品编编号号生生产产日日期期产产品品单单价价产产品品数数量量1 求产品AA有几次是产量大于200的 AA2000 6 1511002 SUM IF B 8 B 21 AA E 8 E 21 200 1 0 AA2000 6 201125我们是这样来考虑的 BB2000 6 302150用IF 来判断 如果 B 8 B 21 AA 而且 E 8 E 21 200的话 BB2000 7 102175那么我们就赋予它值为1 不满足条件则为0 CC2000 7 153200把所有的1相加就代表有几个记录是符合条件的了 CC2000 7 203225 该公式可以作如下简化 AA2000 7 3012502 SUM B 8 B 21 AA E 8 E 21 200 AA2000 8 101275 BB2000 8 1523002 求记录中共有几种产品 即产品编号中不重复的编号总数 BB2000 8 2023254 SUM 1 COUNTIF B 8 B 21 B 8 B 21 CC2000 8 303350我们是这样来考虑的 CC2000 10 103375求共有几种产品 最好是把产品出现的次数加起来 但是产品都是重复出现的 如果每个产品都按1来计算的话 DD2000 10 154400出来的结果就肯定不对 最好是凡是一种产品的加起来就等于1 那么就最好了 DD2000 10 304425有了这种想法 便有了1 COUNTIF B 8 B 21 B 8 B 21 去查找每个产品编号共出现了几次 1出现N次 就让它等于1 N 如 AA 共出现了4次 那么让每个 AA 都对应一个值 1 4 1把4个1 4相加就得出了1 也就是说只要有不同的编号存在 每个编号都会被分成N份 并加上N次 这一条可能记住公式的人不少 真正理解的不多 我也口齿不大清楚 听的懂听不懂的请多原谅 3 求记录中共记录了几个月的数据 是不是有人会用第2题里的公式去套用呢 SUM 1 COUNTIF MONTH C8 C21 MONTH C8 C21 试过的人肯定知道 这个公式是无法输入的 所以我们必须换个思路来考虑这个问题 4 SUM IF MATCH MONTH C 8 C 21 MONTH C 8 C 21 0 ROW C 8 C 21 7 1 详细的就不多说了 给个提示吧 自己多琢磨哦 利用MATCH 函数的match type参数为0时 它只返回第一个符合条件的值的位置 那么第一个符合的就能得到一个参数1 排在后面的就得不到了哦 看完了这些后 Eraser2000再给你布置个家庭作业 还还是是第第2个个问问题题 但但是是用用第第3题题的的思思路路解解 可可以以吗吗 I m from Eraser说 说 本小节的第3道题目说明了什么呢 我想说的是数组公式并非是一成不变的 有许多 可以 和许多 不可以 这是要靠积累的 当碰到 不可以 时 就要想想看是不是能从另外一个角 度来看待问题 关键还是在解题的思路上面 至于到底那个公式为什么不能输入 我所能告诉你的是 因为COUNTIF range criteria 函数的range参数不支持 常量型数组 而且这也只是我个人通过实验得出的想法 而已 欢迎大家和我一起探讨 Part V End 一般用COUNTIF可以进行条件计数 可是两个或以上条件时 COUNTIF就不行了 在这里我们可以用数组公式来解决 记数的问题 大多数情况下是用SUM来解决 是因为数数其实也是求和的一种 只不过是若干个1相加而已 但是SUM中参数的变化可就真谓千变万化了 SUM IF B 8 B 21 AA E 8 E 21 200 1 0 用IF 来判断 如果 B 8 B 21 AA 而且 E 8 E 21 200的话 那么我们就赋予它值为1 不满足条件则为0 把所有的1相加就代表有几个记录是符合条件的了 SUM B 8 B 21 AA E 8 E 21 200 2 求记录中共有几种产品 即产品编号中不重复的编号总数 SUM 1 COUNTIF B 8 B 21 B 8 B 21 求共有几种产品 最好是把产品出现的次数加起来 但是产品都是重复出现的 如果每个产品都按1来计算的话 出来的结果就肯定不对 最好是凡是一种产品的加起来就等于1 那么就最好了 有了这种想法 便有了1 COUNTIF B 8 B 21 B 8 B 21 去查找每个产品编号共出现了几次 出现N次 就让它等于1 N 如 AA 共出现了4次 那么让每个 AA 都对应一个值 1 4 把4个1 4相加就得出了1 也就是说只要有