EXCEL函数常用技巧浅析(四)数组--随心所欲.xls

EXCEL函函数数常常用用技技巧巧浅浅析析 四四 技技巧巧四四 数数组组 随随心心所所欲欲 数组的变化可以说是函数的精髓部分 对于数组的解析太多前辈高人做过很多的讲解过了 我这里只不过把前辈高人讲过的东西再重新解读一下 数组高手可以飘过 本技巧介绍的都是以CTRL SHIFT 回车键结束的内存数组公式 所谓内存数组 简单的讲就是在内存中保存的或者说用F9键在公式栏查看时可以显示全部结果 一一 数数组组的的基基本本原原理理 1 一一个个纵纵向向一一维维数数组组与与单单个个元元素素的的运运算算 单个元素 我这里为什么要把他叫做单个元素 因为无论是一个单元格也好 一个常量数值或文本也好 在数组的运算中 我们即不能把他看成一个纵向数组 也不能把他看 成一个横向数组 因为常常在与一维或二维的运算中 这个元素是具有扩展性的 如果与他运算的数组是一个五行一列的纵向数组 那这个元素也会自动扩展成一个五行 一列的数组与之运算 其结果也必然是五行一列的结果 纵向一维数组单个元素自动扩展结果 1112 2213 3 1 3 14 4415 5516 公公式式 B13 B17 1 2 一一个个横横向向一一维维数数组组与与单单个个元元素素的的运运算算 同理 如果这个与其运算的数组是一个一行五列的横向数组 那这个元素也会自动扩展为一个一行五列的数组与之运算 其结果也必然是一个五行一列的结果 横向一维数组12345 单个元素 1可可以以用用F9键键查查看看结结果果的的公公式式栏栏 12345形形成成的的是是内内存存数数组组 2 3 4 5 6 2 3 4 5 6 自动扩展11111 结果23456 公公式式 C23 G23 1 3 一一个个二二维维数数组组与与单单个个元元素素的的运运算算 同样 一个二维数组与单个元素进行运算 那这个元素也会自动扩展成与之运算的二维数组的行列数 其结果与与其运算的二维数组一致 二行二列数组单个元素自动扩展 161611 27 12711 3838 11 494911 51051011 4 一一个个横横向向数数组组与与纵纵向向数数组组运运算算 一个横向数组与一个纵向数组进行运算 纵向数组扩展成为与其进行运算的横向数组的列数 形成一个行数与原数组相同 列数与纵向数组相同的二维数组 横向 数组扩展成为一个行数与纵向数组相同 列数为原数组列数相同 然后两个新的数组相对应的值进行运算 得到一个行数与纵向数组相同 列数与横向数组相同的结果 横向数组678910 纵向数组 1 2 3 4 5 纵向数组扩 展 11111 22222 33333 44444 55555 横向数组扩 展 678910 678910 678910 678910 678910 结果 7891011 89101112 可可以以用用F9键键查查看看结结果果的的公公式式栏栏 910111213 形形成成的的是是内内存存数数组组 7 8 9 10 11 7 8 9 10 11 1011121314 8 9 10 11 12 9 10 11 12 13 10 11 12 13 14 11 12 13 14 15 8 9 10 11 12 9 10 11 12 13 10 11 12 13 14 11 12 13 14 15 1112131415 5 一一维维同同向向数数组组运运算算 两个一维同向数组运算 其中有一个条件就是这两个数组的行数或列数必须是相等的 其结果行或列数与原数组相同 如果不相等 则不会自动扩展 而会产生错误值 数组一数组二结果 145数组对称 2 57 369 公公式式 B76 B78 D76 D78 数组一数组二结果 145数组不对称 2 57 369 7 N A 8 N A 公公式式 B81 B83 D81 D85 6 一一维维数数组组与与二二维维数数组组运运算算 一维数组与二维数组的运算与一维同向数组原理相同 那就是一维数组的行数或列数必须与二维数组的行数或列数相同 一维数组会自动扩展成二维数组 其大小与二维数组一致 否则会产生错误 一维数组二维数组结果 14758 2 58710 369912 公公式式 B92 B94 D92 E94 一维数组二维数组结果 14758 纵向数组 2 58710 369912 4 N A N A 公公式式 B98 B101 D98 E100 7 二二维维数数组组与与二二维维数数组组的的运运算算 二维数组与二维数组的运算 两个数组的行列数必须一致 其结果行列数与原数组相同 否则会产生错误 数组一数组二结果 159131018 26 10141220 3711151422 4812161624 数组一数组二 159131721 26 10141822 3711151923 4812162024 二二 一一维维转转二二维维 A B C D转换成ABC EDEF FGHI G H I 思思维维一一 LOOKUP函函数数 此此题题出出一一位位前前辈辈高高人人贴贴子子 具具体体贴贴子子没没查查到到 LOOKUP函数一般利用第一参数来驱动数组的结果 我们来想像一下 我们需要构造一个什么样的数组才会得到结果 第一步 首先利用ROW函数构造LOOKUP函数的第二参数与第三参数 向量法 1A 2B 3C 4D 5E 6F 7G 8H 9I LOOKUP 现现在在不不考考虑虑第第一一参参数数 ROW 1 9 A9 A17 第二步 需要得到的结果为三行三列 我们的第一参数也必然是一个三行三列数组来驱动函数的结果 想像一个如果要得到正确的结果 我们的第一参数就必须为以下数组 123 456 789 第三步 在我们要得到正确结果中的第一参数中寻找规律 这个规律是很好找的 我们会发现这个数组直接用行列号就可以构造出来的 列号 123 公公式式 ROW 1 3 1 3 COLUMN A C 行号 1123 2456 3789 公式解析 首先用ROW 1 3 1产生一个一列三行纵向数组 其结果为 0 1 2 再用这个数组乘以3 其结果依然为一列三行的纵向数组 0 3 6 00 1 乘以33其原理为纵向数组的第一个元素乘以单个数字 其结果数组方向不会改变 26 ROW 1 3 1 ROW 1 3 1 3 再用COLUMN A J 产生一个一行三列的横向数组 其结果为 1 2 3 123 COLUMN A C 最后用纵向的一维数组 ROW 1 3 1 3加上横向的一维数组COLUMN A C 其结果为一个三列三行的二维数组 1 2 3 4 5 6 7 8 9 123 0123 此数组运算的原理可以理解为 每一个纵向数组与每一个横向数组的元素相加 其结果摆放在两个数组行列相交的位置 3456 或者也可以这样理解 每一个横向数组与每一个纵向数组的元素相加 其结果摆放在两个数组行列相交的位置 6789 C60 C62 D59 F59 第四步 利用LOOKUP的向量法进行每一个第一参数的查找 得到结果的数组方向与第一参数一致 ABC DEF GHI LOOKUP ROW 1 3 1 3 COLUMN A C ROW 1 9 B9 B17 思思维维二二 INDIRECT函函数数 利用INDIRECT函数对单元格引用的特性也可以实现以上结果 利用INDIRECT函数的第一参数来驱到数组的方向 第一步 我们设想一下我们我们应该如果构造INDIRECT函数的第一参数 现我们的原数据在B9 B17区域 那INDIRECT函数的第一参数就应该如下表 B9B10B11略去B91011 B12B13B14121314 B15B16B17151617 第二步 如何实现以上效果 其实规律同思维一是一样的 只是数字不一样而已 列号 91011 行号 191011 公公式式 ROW 1 3 1 3 COLUMN I K 2121314 3151617 公式解析 同思维一 只是增大了列号 第三步 根椐INDIRECT函数的特性 加上列号 就形成了第一步的第一表 B9B10B11 B12B13B14 B15B16B17 第四步 用INDIRECT函数引用上面得出的单元格地址 但得到的结果为一个三维结果 用F9与单元格无法显示 在本示例中为文本 可以用T函数转换 如果是数字 就需要用N函数来转换 注注意意 T与与N函函数数转转换换三三维维后后得得到到的的结结果果只只是是每每一一个个三三维维平平面面中中第第一一个个单单元元格格的的结结果果 平平面面中中不不是是第第一一个个单单元元格格中中的的内内容容无无法法取取出出 单个元素 我这里为什么要把他叫做单个元素 因为无论是一个单元格也好 一个常量数值或文本也好 在数组的运算中 我们即不能把他看成一个纵向数组 也不能把他看成一个横向数组 因为常常在与一维或二维的运算中 这个元素是具有扩展性的 如果与他运算的数组是一个五行一列的纵向数组 那这个元素也会自动扩展成一个五行一列的数组与之运算 其结果也必然是五行一列的结果 VALUE VALUE 纵向一维数组如果不加T的结果为 VALUE VALUE VALUE VALUE 最最后后公公式式 T INDIRECT b 123 456 789 IF 1 ROW 1 3 1 3 COLUMN A C 第二步 构造VLOOKUP函数第二参数 这里可以用IF函数构造一个9行二列的数组 1A IF 1 0 ROW 1 9 B9 B17 2B 3C 4D 5E 6F 7G 8H 9I 第三步 进行组装 VLOOKUP函数结果的方向由第一参数驱动 ABC DEF GHI 公公式式 VLOOKUP N IF 1 ROW 1 3 1 3 COLUMN A C IF 1 0 ROW 1 9 B9 B17 2 0 思思维维五五 INDEX函函数数 同理 用N IF 1 也可以实现INDEX函数第二参数的数组化 而且INDEX函数结果的方向也是由第二参数来驱动 ABC DEF GHI INDEX B9 B17 N IF 1 ROW 1 3 1 3 COLUMN A C 三三 二二维维转转一一维维 二维转一维的显示原数据内存数组的办法不是很多 而且如果待转区域数值与文本相混合的话会变得很繁琐 A B C ABCD转换成D EFGHE IJKLF G H I J K L 思思维维一一 OFFSET函函数数 基原理利用OFFSET函数第二参数与第三参数相对应产生的偏移来形成一个一维数组 第一步 首先我们应该考虑应该怎样来构造第二参数与第三参数 想像一下 我们是否应该构造成以下对应数组 这里我们以B257单元格为偏移点 第二参数 行偏移 第三参数 列偏移 测试 00A 01B 02C 0 3 现 现在在我我们们来来证证明明一一下下如如此此D 1 0 构 构造造第第二二与与第第三三参参数数是是否否E 1 1 正 正确确F 12G 13H 20I 21J 22K 23L 公公式式 T OFFSET B257 B271 B282 C271 C282 结果证明这种思路是正确的 那剩下的事情就简单了 我们只要找出构造这种数组的办法就行了 第二步 构造第二参数与第三参数 从上面的猜想我们可以看到第二参数与第三参数是有明显的规律的 第二参数的规律是重复我们原数据的列数的数据 可以用INT加除法实现 第二参数重复从0到原数据的列数减1 直接用MOD函数就可以得出了 第二参数 行偏移 第三参数 列偏移 00 01 02 03 10 11 12 13 20 21 22 23 公公式式 INT ROW 4 15 4 1 公公式式 MOD ROW 4 15 4 第三步 组装 再把构造出的参数代入OFFSET函数 因为OFFSET函数取出的数据是三维平面 而这里又是文本 所有要用T函数取出 A B C D E F G H I J K L 公公式式 T OFFSET B257 INT ROW 4 15 4 1 MOD ROW 4 15 4 思思维维二二 INDIRECT函函数数 INDIRECT函数的原理与OFFSET函数差不多 利用INDIRECT函数的R1C1模式也可以构造出同样的数组 所谓的R1C1模式就是引用第几行 R1 第几列 C1 的单元格 第一步 根据INDIRCT特性分别设想R1部分与C1部分 现数据区域在R257C2 R259C5中 R1R1C1C1测测试试 2572A 2573B 2574再来测试一下我们的设想C结果证明我 2575是否正确D们的设想是正确的 2582E 2583F 2584G 2585H 2592I 2593J 2594K 2595L 第二步 构造R1与C1 其实我们只要仔细观察一下 上面的数组与OFFSET函数的第二与第三参数基本上雷同 只是把数字的大小放大了而已 R1R1C1C1 2572 2573 2574 2575 2582 2583 2584 2585 2592 2593 2594 2595 公公式式 INT ROW 4 15 4 256 公公式式 MOD ROW 4 15 4 2 第三步 组装 原理与OFFSET函数第三步一样 A B C D E F G H I J K L 公公式式 T INDIRECT R 3 4 5 6 2 3 4 5 6 结果 27 可可以以用用F9键键查查看看结结果果的的公公式式栏栏 3 8 形 形成成的的是是内内存存数数组组 2 7 3 8 4 9 5 10 6 11 2 7 3 8 4 9 5 10 6 11 49 510 611 EXCEL函函数数常常用用技技巧巧浅浅析析 四四 数组的变化可以说是函数的精髓部分 对于数组的解析太多前辈高人做过很多的讲解过了 我这里只不过把前辈高人讲过的东西再重新解读一下 数组高手可以飘过 本技巧介绍的都是以CTRL SHIFT 回车键结束的内存数组公式 所谓内存数组 简单的讲就是在内存中保存的或者说用F9键在公式栏查看时可以显示全部结果 单个元素 我这里为什么要把他叫做单个元素 因为无论是一个单元格也好 一个常量数值或文本也好 在数组的运算中 我们即不能把他看成一个纵向数组 也不能把他看 成一个横向数组 因为常常在与一维或二维的运算中 这个元素是具有扩展性的 如果与他运算的数组是一个五行一列的纵向数组 那这个元素也会自动扩展成一个五行 同理 如果这个与其运算的数组是一个一行五列的横向数组 那这个元素也会自动扩展为一个一行五列的数组与之运算 其结果也必然是一个五行一列的结果 可可以以用用F9键键查查看看结结果果的的公公式式栏栏 形形成成的的是是内内存存数数组组 2 3 4 5 6 2 3 4 5 6 同样 一个二维数组与单个元素进行运算 那这个元素也会自动扩展成与之运算的二维数组的行列数 其结果与与其运算的二维数组一致 一个横向数组与一个纵向数组进行运算 纵向数组扩展成为与其进行运算的横向数组的列数 形成一个行数与原数组相同 列数与纵向数组相同的二维数组 横向 数组扩展成为一个行数与纵向数组相同 列数为原数组列数相同 然后两个新的数组相对应的值进行运算 得到一个行数与纵向数组相同 列数与横向数组相同的结果 行数相同 行数不同 可可以以用用F9键键查查看看结结果果的的公公式式栏栏 形形成成的的是是内内存存数数组组 7 8 9 10 11 7 8 9 10 11 8 9 10 11 12 9 10 11 12 13 10 11 12 13 14 11 12 13 14 15 8 9 10 11 12 9 10 11 12 13 10 11 12 13 14 11 12 13 14 15 两个一维同向数组运算 其中有一个条件就是这两个数组的行数或列数必须是相等的 其结果行或列数与原数组相同 如果不相等 则不会自动扩展 而会产生错误值 一维数组与二维数组的运算与一维同向数组原理相同 那就是一维数组的行数或列数必须与二维数组的行数或列数相同 一维数组会自动扩展成二维数组 其大小与二维数组一致 行列数一样 结果 1018 N A N A行数相同 1220 N A N A列数不同 1422 N A N A 1624 N A N A LOOKUP函数一般利用第一参数来驱动数组的结果 我们来想像一下 我们需要构造一个什么样的数组才会得到结果 第二步 需要得到的结果为三行三列 我们的第一参数也必然是一个三行三列数组来驱动函数的结果 第三步 在我们要得到正确结果中的第一参数中寻找规律 这个规律是很好找的 我们会发现这个数组直接用行列号就可以构造出来的 公公式式 ROW 1 3 1 3 COLUMN A C 首先用ROW 1 3 1产生一个一列三行纵向数组 其结果为 0 1 2 再用这个数组乘以3 其结果依然为一列三行的纵向数组 0 3 6 其原理为纵向数组的第一个元素乘以单个数字 其结果数组方向不会改变 最后用纵向的一维数组 ROW 1 3 1 3加上横向的一维数组COLUMN A C 其结果为一个三列三行的二维数组 1 2 3 4 5 6 7 8 9 此数组运算的原理可以理解为 每一个纵向数组与每一个横向数组的元素相加 其结果摆放在两个数组行列相交的位置 或者也可以这样理解 每一个横向数组与每一个纵向数组的元素相加 其结果摆放在两个数组行列相交的位置 第四步 利用LOOKUP的向量法进行每一个第一参数的查找 得到结果的数组方向与第一参数一致 利用INDIRECT函数对单元格引用的特性也可以实现以上结果 利用INDIRECT函数的第一参数来驱到数组的方向 公公式式 ROW 1 3 1 3 COLUMN I K VALUE VALUE VALUE 第四步 用INDIRECT函数引用上面得出的单元格地址 但得到的结果为一个三维结果 用F9与单元格无法显示 在本示例中为文本 可以用T函数转换 如果是数字 就需要用N函数来转换 注注意意 T与与N函函数数转转换换三三维维后后得得到到的的结结果果只只是是每每一一个个三三维维平平面面中中第第一一个个单单元元格格的的结结果果 平平面面中中不不是是第第一一个个单单元元格格中中的的内内容容无无法法取取出出 利用OFFSET函数的第二参数对行的偏移 也可以实现以上效果 第二参数的数组构造与思维一一致 因为OFFSET函数产生的结果也是三维的 所以也需要用T函数来转换 因为PINY版主的N IF 1 的发现 用VLOOKUP函数实现上面的内存数组不再是难事情 虽然实现的公式看起来有点繁复 但毕竟我们又多了一把利器 同理 用N IF 1 也可以实现INDEX函数第二参数的数组化 而且INDEX函数结果的方向也是由第二参数来驱动 第一步 首先我们应该考虑应该怎样来构造第二参数与第三参数 想像一下 我们是否应该构造成以下对应数组 这里我们以B257单元格为偏移点 公公式式 T OFFSET B257 B271 B282 C271 C282 第二参数的规律是重复我们原数据的列数的数据 可以用INT加除法实现 第二参数重复从0到原数据的列数减1 直接用MOD函数就可以得出了 再把构造出的参数代入OFFSET函数 因为OFFSET函数取出的数据是三维平面 而这里又是文本 所有要用T函数取出 INDIRECT函数的原理与OFFSET函数差不多 利用INDIRECT函数的R1C1模式也可以构造出同样的数组 所谓的R1C1模式就是引用第几行 R1 第几列 C1 的单元格 其实我们只要仔细观察一下 上面的数组与OFFSET函数的第二与第三参数基本上雷同 只是把数字的大小放大了而已 INDEX函数的原理与OFFSET函数一样 也是通过构造第二参数与第三参数来实现二维转一维 不过如果要形成内存数组 就必须加上N IF 1 结构 否则形成的就是 INDEX B257 E259