数组的概念(excel)

第一部分 了解数组公式第一部分 了解数组公式 在开始讲数组公式之前 我们先来认识几个必要的概念 1 数组 数组 什么是数组 仁者见仁 智者见智 我个人的感觉是 数组是具有某种联系的多个元素的组合 某班级里有 50 个学生 这里 如果 班级是数组 50 个学生就是数组里的 50 个元素 当然 班级里的元素是可变的 可以是 20 个 可以是 30 个 也可以是 60 个 放到 Excel 里 班级就相当于工作表 而学生就相当于工作表 里的单元格数值 所以 Excel 里的数组 我还把它理解是为多个单元格数值的组合 2 公式 公式 如果你在使用 Excel 如果你说你还没听过 公式 这个名词 我只能说 你太 OUT 了 什么是公式 我的理解是 在 Excel 里 凡是以半角符号 开始的 具有计算功能的单元格 内容就是所谓的 Excel 公式 如 SUM B2 D2 B2 C2 D2 这些都是公式 3 数组公式 数组公式 数组公式是相对于普通公式而言的 普通公式 如上面的 SUM B2 D2 B2 C2 D2 等 只占用一个单元格 只返回一个结果 而数组公式可以占用一个单元格 也可以占用多个单元格 它对一组数或多组数进行多重计算 并返回一个或多个结果 集合在教室外面的学生 老师把他们叫进教室 老师说 第一组第一桌的同学进教室 于 是第一组第一桌的同学走进教室 老师接着叫 第一组第二桌的同学进教室 然后是第二 桌的同学进教室 老师再叫 第一组第三桌的同学进教室 然后第三桌的同学走进教室 接着是第四桌 第五桌 就这样一个学生一个学生的叫 这就是普通公式的做法 学生回 到座位 就像数值回到工作表的单元格里 一个座位叫一次 就像一个单元格输入一个公式 如果老师说 第一组的全部进教室 学生听到命令后 第一桌的同学走进去 然后是第二 桌 第三桌 老师不用再下第二个命令 这是数组公式的处理方法 4 数组公式的标志 数组公式的标志 在 Excel 中数组公式的显示是用大括号对 来括住以区分普通 Excel 公式 如图 1 数组公式 2 普通公式 2 输入数组公式 用 Ctrl Shift Enter 结束公式的输入 特别提醒 这是最关键的 这相当于用户告诉 Excel 我不是一般人 爷我是数组公式 你得 对我特别关照 于是 Excel 明白了 不能用常规的逻辑来对待这位大爷 当你按下三键后 Excel 会自动给公式加上 以和普通公式区别开来 不用用户输入 但如是是想在公 式里直接表示一个数组 就需要输入 来把数组的元素括起来 如 IF 1 0 D2 D8 C2 C8 这个公式里的数组 1 0 的括号就是用户自己输入的 5 数组的维数 数组的维数 维数 是数组里的又一个重要概念 数组有一维数组 二维数组 三维数组 四维数组 在公式里 我们更多接触到的只是一维数组和二维数组 一维数组我们可以简单地看成是一行的单元格数据集合 比如 A1 F1 一维数组的各个元素间 用英文的逗号 隔开 如果是单独的一列时 用英文分号 隔开 3 1 2 3 4 5 6 这就是一个有 6 个元素的一维数组 或者说 只有一行的数组 数组的各个元素 间用逗号 分隔 如果想把这个数组输入到工作表的单元格里 同时选中同一行里相领的六 个单元格 输入 1 2 3 4 5 6 后 三键结束公式 你就可以看到这个一维数组被输入到工作 表的单元格里了 自己动手试一试 二维数组可以看成是一个多行多列的单元各数据集合 也可以看成是多个一维数组的组合 如 单元格 A1 D3 就是一个三行四列的二维数组 我们可以把它看成是 A1 D1 A2 D2 与 A3 D3 这三个一维数组的组合 二维数组里同行的元素间用逗号 分隔 不同的行用分号 分隔 我们可以用上面的方法 在 A1 D3 区域输入数据 并引用地址 按 F9 来查看 4 可以看到在数组里 换行的时候 元素间的分隔符是 所以 要判断一个数组是几行几列 的数组 只需要看里面的逗号和分号就知道了 如果需要把数把数组返回到单元格区域里 首先得看数组是几行几列 然后再选择相应的单元 格区域 输入数组 三键结束 对了 是哪三键你还不要忘记了 Ctrl Shift Enter 记住 1 一维数组是单独的一行或一列 二维数组是多行多列 2 数组里的元素 同一行内的各元素用英文逗号 分开 用英文分号 将各行分开 3 二维数组的元素按先行后列的顺序排列 总是这样 第一行的第一个 第一行的第二个 第 一行的第三个 第二行的第一个 第二行的第二个 第二行的第三个 第三行的第一 个 5 第二部分 数组公式的初步认识第二部分 数组公式的初步认识 在对数组公式有了一个简单的了解之后 这贴我们将通过一些简单的例子来进一步认识数 组公式 问题问题 1 1 在 在 D2 D4D2 D4 求出商品的销售金额 求出商品的销售金额 现在你解决这个问题会用什么办法呢 我知道很小儿科 千万不要在心里骂我拿这种简单的问题来考你 是的 很简单 在 D2 单元格输入公式 B2 C2 下拉公式即可 在这里 D2 D4 三个单元格输入了三个普通公式 分别返回了三个值在三个单元格里 这 就是老师在点学生进教室 第一组第一桌的同学进教室入座 第一组第二桌的同学进教室 入座 我们试着用数组公式来解决这个问题 老师嗓子不好 让他叫一次我们就乖乖进教室去得 了 6 选中 D2 D4 输入公式 B2 B4 C2 C4 三键结束输入数组公式 即可得到同样的结果 这就是一个多单元格的数组公式 多单元格数组公式是进行批量计算 可节省计算的时间 同时 它还有一个特点 当你输入完数组公式后 请你尝试修改公式区域里其中一个单元 格的公式 看看会有什么结果 是的 你已经发现了 会弹出一个对话框 提醒你 不能修改数组的某一部分 这就是多单元格数组公式的一个重要的特点 保证公式集合的完整性不被修改 这可以防 止用户在操作时无意间修改到表格的公式 这是不是会安全得多 当然 如果你要修改公式的话 必须得选中公式所在的所有单元格 问题问题 2 2 在 在 F1F1 求出商品的销售总金额求出商品的销售总金额 7 这一题如果你用普通公式又怎么解决呢 我想象中可能有两种方法 A 插入辅助列 先求出各商品的销售额 然后再求总和 B 直接在 F1 输入公式 SUM B2 C2 B3 C3 B4 C4 这样看上去不错 可是 如果有 100 行数据 一千行号数据呢 先不考虑单元格能容纳多少字符的问题 就光输入公式 累也得把你累趴下 显然是行不通的 这时候就需要用数组公式来完成了 选中 F1 单元格 输入公式 SUM B2 B4 C2 C4 三键确认输入即可 这是一个单个单元格的数组公式 B2 B4 C2 C4 是两个一维数组相乘 返回一个新的一维 数组 最后用 SUM 函数对返回的数组进行了求和 这里 用一个数组公式代替了多个公式 的方式来完成了数据的计算 做了这个问题 总结一下 什么时候会用到数组公式 8 是的 当运算中存在着一些只有通过复杂的中间运算过程才会等到结果的时候 就需要使 用数组公式了 这一贴的内容非常简单 记住几点 1 三键输入数组公式 2 数组公式同时进行多个计算 可返回一个或多个结果 3 多单元格数组公式需选区多个单元格进行输入 多单元格数组公式具有保护公式的作 用 4 数组公式可以完成复杂的中间运算得到最终想要的运算结果 9 第三部分 数组公式的计算第三部分 数组公式的计算 学习继续 在对数组有了基本的认识后 这贴我们将通过一些例子来讲一讲数组公式是怎 么计算的 1 1 行列数相同数组的运算 行列数相同数组的运算 数组 1 数组 2 这是一个多单元格的数组公式 第一个数组的第一个元素与第二个数组的 第一个元素相加 结果作为数组公式结果的第一个元素 然后第一个数组的第二个元素与 第二个数组的第二个元素相加 结果作为数组公式结果的第二个元素 接着是第三个元 素 直到第 N 个 这是横向的一维数组的计算 原理同上 10 这是二维数组与二维数组进行计算 生成一个新的二维数组的多单元格数组公式 同样的 计算过程 第一个数组的第一行的第一个元素与第二个数组的第一行的第一个元素相乘 结果为数组公式的结果的数组的第一行的第一个元素 接着是第二个 第三个 直到第 N 个 规律很简单 两个同行同列的数组计算是对应元素间进行运算 并返回同样大小的数组 正如穿鞋要穿合脚的才走得了路一样 在公式或函数中使用数组时 运算对象或参数的数 组维数要匹配 否则计算会出错 教室里 第一排的有 8 个同学 第二排有 9 个同学 老 师说 第一排和第二排的同学交换作业 互相检查 第二排的第 9 个同学和谁交换 这就是数组的不匹配 数组不匹配时 工作就不能完成了 你可以试着改一改数组的参数试试 2 2 数组与单一的数据的运算 数组与单一的数据的运算 11 这相当于在 E42 单元格输入公式 A42 C 42 然后下拉复制公式实现 等同于在 B56 输入公式 B52 B 54 然后右拉复制公式实现 等同于在 C67 单元格输入公式 A60 E 60 然后右拉下拉复制公式实现 不难看出 一个数组与一个单一的数据进行运算 是将数组的每一元素均与那个单一数据 进行计算 并返回同样大小的数组 3 3 单列数组与单行数组的计算 单列数组与单行数组的计算 12 两个数组相加 查看结果是几行几列 在任意单元格输入公式 A80 A83 B87 E87 抹 黑公式 按 F9 键 可看到公式的计算结果为数组 110 210 310 410 120 220 320 420 130 230 330 430 140 240 340 440 通看看分号 与逗号 我们知道这是一个四行四列的数组 选择一个四行四列的单元格 输入公式 A80 A83 B87 E87 三键结束 可看到返回的结果为 13 相当于在 E80 输入公式 A80 B 87 右拉下拉复制公式的结果 单列数组与单行数组的计算 A 计算结果返回一个多行列的数组 B 返回数组的行数同单列数组的行数相同 列数同单行数组的列数相同 C 返回数组中第 R 行第 C 列的元素是单列数组的第 R 个元素和单行数组的第 C 个元素运算 的结果 4 4 行数 或列数 相同的单列 或单行 数组与多行多列数组的计算 行数 或列数 相同的单列 或单行 数组与多行多列数组的计算 1 单列数组的行数与多行多列数组的行数相同时 2 单行数组的列数与多行多列数组的列数相同时 14 计算规律同单行单列的数组计算的规律大同小异 A 计算结果返回一个多行列的数组 B 返回数组的行 列数与多行多列数组的行列数相同 C 单列数组与多行多列数组计算时 返回的数组的第 R 行第 C 列的数据等于单列数组的第 R 行的数据与多行多列数组的第 R 行第 C 列的数据的计算结果 D 单行数组与多行多列数组计算时 返回的数组的第 R 行第 C 列的数据等于单行数组的第 C 列的数据与多行多列数组的第 R 行第 C 列的数据的计算结果 留给你的思考题留给你的思考题 讲到这里 我们可以暂停一下进度 课间休息 插播一段广告 你可以喝杯水 听听音乐 然后我们来看几个例子 图 1 图 2 15 图 3 上面的三张图 第一个公式是我们前面讲的例子 第二个公式是在第一个公式的基础上对 参与计算的数组区域进行了修改 但是 两个不同参数的公式 返回的结果却都是一样的 这里我只是举了三个例子 你可以把前面我们讲过的公式里的数组参数都修改修改 什么 情况下 会返回相同的结果呢 它们又有什么共同的地方 知识总是光顾那些善于总结和 发现的人 否则 踩着别人的脚印走 想要看到别人没看到的风景 你要等到猴年马月 好了 我也仿小学老师的口气问问大家 为什么两个不同的公式 返回的结果都是一样 的呢 从上面的图 你发现了什么 把你的发现说给你的伙伴听一听 这就是你今天的作业 如果你是真心想想学数组公式的 记得跟贴回复 5 5 行 列数不相等的数组计算 行 列数不相等的数组计算 1 行数不相等的单列数组与与多行列数组的计算 16 2 列数不相等的单行数组与多行多列数组的计算 3 行 列数不相同的两个多行多列数组的计算 有了对前面例子的分析 再来看这三个例子就相对简单了 它们的计算规则和前面都是一 样的 不难看出 A 公式返回一个多行多列数组 B 返回数组的行数与参与计算的两个数组中行数较大的数组的行数相同 列数与较大的列 数的数组相同 C 返回数组的大于较小行数数组行数 大于较大列数数组列数的区域的元素均为 N A 有 效元素为两个数组中对应数组的计算结果 需要提醒一点的是 对会返回 N A 的数组 在进行再计算和处理时 考虑对 N A 值作相应 的处理 比如我们想对上面数组与数组 2 相加后的结果进行求和 正确的公式 数组 SUM IF ISNA A213 B216 D213 F215 0 A213 B216 D213 F215 17 通过 ISNA 函数对返回的数组里的各个元素进行了判断和处理 把把有的 N A 值替换成数 值 0 最后再用 SUM 函数对所有数值进行求和 我们说 数组计算时 得注意行列数的匹配 其实如果了解了数组的计算原理后 能正确 处理那些返回的 N A 值的话 很多时候 并不会出错的 18 第四部分 数组扩充第四部分 数组扩充 这一贴的内容相对比较简单 主要是对第三部分 数组的计算里提出的思考问题作出回复 昔日关云长温酒斩华雄的故事听过吧 如果你已认真读了前面的贴子 且用心总结了下 再来看此贴 相信你也会有 云长提华雄之头 掷于地上 其酒尚温 的豪气 呵呵 嫌我唐僧了吧 那端上一杯热茶 快快进入主题 当读完贴后 你的茶是否喝完 读完上一贴 了解了数组公式的计算规律后 我们知道 数组与数组计算 返回一个新的 数组 返回的数组的行数与参与计算的数组中行数较大的数组的行数相同 列数与列烽较 大的数组的列数相同 但 为什么两个不同的公式 返回的结果却相同呢 这就是我们今天要讲的一个新概 念 数组扩充 数组计算时 参与计算的两个数组得具有相同的维数 也就是得注意行列数的匹配 对于行列数不匹配的数组 在计算时 Excel 会将数组对象进行扩展 以符合计算需要的维 数 每一个参与计算的数组的行数必须与行数最大的数组的行数相同 列数必须与列数最 大的数组的列数相同 例例 1 1 公式 SUM 10 20 30 40 10 里 第一个参数 10 20 30 40 是一行四列的数组 第二个 参数不是数组 只是一个数值 为了让第二个数值能与第一个数组进行专题片 这时 Excel 会自动将第二参数的 10 扩充成一个一行四列的数组 10 10 10 10 与第一参数匹 配 所以 SUM 10 20 30 40 10 最后是使用 SUM 10 20 30 40 10 10 10 10 进 行计算 得到的结果是 10 10 20 10 30 10 40 10 的和 例例 2 2 公式 10 20 30 40 100 200 的第一个参数 10 20 30 40 是一个四行一列的数组 100 200 是一个一行二列的数组 在计算时 Excel 会将第一个数组自动扩充为一个四行 二列的数组 10 10 20 20 30 30 40 40 也会将第二个数组扩充为一个四行二列的数组 100 200 100 200 100 200 100 200 所以 10 20 30 40 100 200 这个公式最后是使 用公式 10 10 20 20 30 30 40 40 100 200 100 200 100 200 100 200 进行计算 公 式最后返回的数组也是一个四行二列的数组 数组的第 R 行第 C 列的元素等于扩充后的两 个数组的第 R 行第 C 列的元素的计算的结果 19 好了 在这一贴要讲的已经讲完了 数组扩充 这个华雄是否已被你斩于马下 也不知 道你手里的茶喝完了没 我希望听到你回答的是 华雄已斩 茶没喝完 还温着呢 有兴趣 记得跟贴告诉我一声 呵呵 继续喝茶 休息 顺便听我再给你唠叨几句 班里有 50 个学生 为了让每个学生都有座位 需要预备 50 套课桌椅 如果只有 30 套课桌 椅 那最后进教室的 20 个同学将没有座位 如果有 60 套课桌椅 将会有 10 套课桌椅空在 教室里而别的班级需要课桌椅的同学又不能使用 浪费啊 学生就像数组里的元素 输入数组公式返回数组的元素就像叫学生进教室 我们得给他们 准备好合适的座位 所以输入多单元格数组公式时 应先选中需要返回数据的单元格区域 输入多单元格数组公式时 应先选中需要返回数据的单元格区域 选中的单元格区域的行 列数应与返回数组的行 列数相同 选中的单元格区