2.5 数组运算的规律与应用

什么是数组运算？可以理解成多对一、多对多的运算。这里的“多”指的就是数组或一个以上的单元格区域。通过2.3.1节的学习，我们了解了单元格区域与数组的对应关系。那么，可以把单行单列方向的区域看作一维常规数组，多行多列的区域看作二维常规数组，便于后面理解应用数组。

2.5.1 一维数组与单值的运算

一维数组与单值的运算比较简单。就是数组中的每个元素与单值一一对应计算一次。比如公式={1,2,3}*10，运算规律={1*10,2*10,3*10}，结果={10,20,30}，如图2-10所示。

实例12 计算采购离合器商品的总金额

问题描述： 图2-11所示的表格B列中，列出了商品对应的单价，现在每种商品的采购数量为20个，需要计算出所有商品的总金额。

解决思路： 将所有商品的单价同时乘以数量20，再用SUM函数求所有产品的总金额。

操作步骤： 如图2-12所示，在B7单元格中输入公式=SUM(B3:B6*20)，按Ctrl+Shift+Enter组合键，完成计算。

步骤1： B3:B6*20的本质是{1300;1550;1172;4800}*20，相乘结果是{26000;31000;23440;96000}。

步骤2： 使用SUM函数对步骤1的数组结果进行求和，公式为=SUM({26000;31000;23440;96000})，计算结果是176440。每一步运算都可以选中后按F9键查看运算过程。

2.5.2 一维数组与一维数组的运算（方向相同）

在进行方向相同的一维数组运算时，首先要保证它们都是方向相同的，每个数组中的元素个数必须相同，并一一对应计算，如图2-13所示。

● 一维横向数组运算：={1，2，3}*{10，20，30}；运算规律是={1*10，2*20，3*30}；结果是={10，40，90}。

● 一维纵向数组运算：={1；2；3}*{10；20；30}；运算规律是={1*10；2*20；3*30}；结果是={10；40；90}。

实例13 根据各离合器商品的单价和数量求总金额

问题描述： 图2-14所示的表中列出了每种商品的单价和数量，现需要求出所有商品的总金额。

解决思路： 先将B列的单价区域与C列的数量区域对应相乘，再将求出的金额数组进行求和运算。

操作步骤： 如图2-15所示，首先在C7单元格中输入数组公式=SUM(B3:B6*C3:C6)，然后按Ctrl+Shift+Enter组合键，完成计算。

步骤1： 计算B3:B6*C3:C6部分，将单价区域乘以数量区域，两个区域按F9键进行展开后是{1300;1550;1172;4800}*{20;25;10;5}，相当于{1300*20;1550*25;1172*10;4800*5}，计算结果是{26000;38750;11720;24000}。

步骤2： 使用外层的SUM函数对步骤1的结果进行求和，公式为=SUM({26000;38750;11720;24000})，结果等于100470。

实例14 计算2015—2017年业绩等级为全优的人数

问题描述： 如图2-16所示，计算在2015年、2016年、2017年三年中，业绩等级同时全部为“优”的人数。

解决思路： 由于三年的共同条件都等于“优”，即条件是相同的。那么我们可以将对应的三列数据合并起来，只做一次判断即可。

操作步骤： 如图 2-17 所示，首先将鼠标光标定位在 D11 单元格中，输入数组公式=COUNT（0/（B3：B10&C3：C10&D3：D10=〝优优优〝）），然后按Ctrl+Shift+Enter组合键，完成计算。

步骤 1： 计算 B3：B10&C3：C10&D3：D10，三列值对应的一维纵向连接运算，运算结果为{〝优良优〝；〝中优优〝；〝优优优〝；〝优优优〝；〝优优良〝；〝优优优〝；〝中良优〝；〝优优差〝}。

步骤2： 将步骤1中运算得到的数组结果与〝优优优〝进行等于比较。条件成立则返回TREU，不成立则返回FALSE。运算结果为{FALSE；FALSE；TRUE；TRUE；FALSE；TRUE；FALSE；FALSE}。

步骤3： 用0除以步骤2的结果，目的是对数组中的逻辑值进行转化。0/FALSE的结果值是#DIV/0!（错误值），0/TRUE的结果值是0。所以这一步的运算结果是{#DIV/0!;#DIV/0!;0;0;#DIV/0!;0;#DIV/0!;#DIV/0!}。

步骤4： 用COUNT函数计算步骤3的结果，也就是计算数组里面的数字个数，结果为3。这里要注意COUNT函数的特性：只计算单元格区域或者数组中数字的个数，其他的字符、错误值等将忽略不计。

2.5.3 一维数组与一维数组的运算（方向不同）

横向一维数组与纵向一维数组的方向不同。运算法则并不像方向相同时一一对应。方向不同的一维数组其实是笛卡儿积的运算法则，这种运算法则是数组运算中最难理解的，但也很具有实用价值。

图2-18所示为将{1，2，3}*{10；20；30}进行相乘运算，它们的运算规则是{1*10，2*10，3*10；1*20，2*20，3*20；3*10，3*20，3*30}，运算结果为{10，20，30；20，40，60；30，60，90}。

由上面这个示例可以看出，方向不同的一维数组进行运算得到的是二维数组。二维数组的元素个数等于两个一维数组的元素个数相乘。

实例15 利用数组制作九九乘法表

问题描述： 图2-19所示的表格横向提供了1到9的数字区域B2:J2，纵向也提供了1到 9的数字区域A3:A11，现在需要利用这两个区域的数字来完成一个九九乘法表。

解决思路： B2:J2是在行方向，可以看作水平数组。A3:A11是在列方向，可以看作垂直数组。制作九九乘法表，实质上就是将这两个方向不同的一维数组做连接和算术相乘运算。只要理解了前面讲解的方向不同一维数组的运算原理，就非常容易了。

操作步骤： 如图 2-20 所示，首先选择单元格区域 B3：J11，在编辑栏里输入数组公式=B2：J2&〝×〝&A3：A11&〝=〝&B2：J2*A3：A11+1，然后按Ctrl+Shift+Enter组合键，完成计算。

步骤1： B2：J2&〝×〝&A3：A11是将单元格区域B2：J2与A3：A11进行乘号“×”连接运算，相当于{1，2，3，4，5，6，7，8，9}&〝×〝&{1；2；3；4；5；6；7；8；9}，这是一个典型的方向不同的一维数组之间的运算，{〝1×1〝，〝2×1〝……〝9×1〝；〝1×2〝，〝2×2〝……〝9×2〝；〝1×3〝，〝2×3〝……〝9×3〝；……；〝1×9〝，〝2×9〝……，〝9×9〝}。

步骤2： &B2：J2*A3：A11相当于 {1，2，3，4，5，6，7，8，9}*{1；2；3；4；5；6；7；8；9}，中间的*是真正的相乘。而步骤1中的〝×〝是用于做乘法表效果的字符，不会相乘。运算结果是{1，2，3……9；2，4，6……18；3，6，9……27；……；9，18，27……81}。

步骤3： 将步骤1的乘法样式和步骤2的乘法结果，用“=”进行连接，完成九九乘法表的制作。

注意 为什么要在选择单元格区域B3：J11后再输入公式？这个区域有81个单元格，因为我们推算出九九乘法表是9行9列81个元素，刚好能容纳。如果公式需要修改，则将鼠标光标定位到某个单元格，修改公式后，再按Ctrl+Shift+Enter组合键完成。

2.5.4 一维数组与二维数组的运算

一维数组与二维数组之间的运算，可以分为横向一维数组与二维数组、纵向一维数组与二维数组的运算。

横向一维数组与二维数组的运算原理如图2-21所示，将{1,2,3}*{10,20,30;30,40,60}相乘，运算规则是{1*10,2*20,3*30;1*30,2*40,3*60}，运算结果为={10,40,90;30,80,180}。

纵向一维数组与二维数组的运算原理如图2-22所示，将{1;10;100}*{1,2;3,4;5,6}相乘，运算规则是{1*1,1*2;10*3,10*4,100*5,100*6}，运算结果为={1,2;30,40;500,600}。

实例16 计算各科成绩低于平均分的学生人数

问题描述： 图2-23所示的表中列出每个学生的语文、数学、英语三科成绩，并且在最后一行计算出了每科成绩的平均分，要求计算各科成绩低于平均分的学生人数。

解决思路： 这是一个典型的一维数组与二维数组的计算。先将所有成绩与对应的平均分进行比较，然后将逻辑值结果转为数字，之后求和。如果读者已经理解了前面关于一维和二维数组的运算法则，这题就显得非常简单了。

操作步骤： 如图2-24所示，首先在F2单元格中输入数组公式=SUM(-(B7:D7>=B2:D6))，然后按Ctrl+Shift+Enter组合键，完成计算。

步骤1： 计算B7:D7>=B2:D6，判断B7:D7单元格区域中的平均成绩是否大于或等于B2:D6单元格区域中对应的分数。条件成立则为TRUE，不成立则为FALSE。比较的逻辑值形成的数组为{TRUE,FALSE,TRUE;FALSE,TRUE,FALSE;FALSE,TRUE,TRUE;FALSE,FALSE,TRUE;TRUE,TRUE,FALSE}。

步骤2： 先将步骤1中的逻辑值形成的数组做-（负负）的算术运算，转换成对应的数字{1,0,1;0,1,0;0,1,1;0,0,1;1,1,0}。再用SUM函数对数组中的数字做求和运算，结果为8。

2.5.5 二维数组与单值的运算

二维数组与单值的运算比较简单，和一维数组与单值的运算一样。将二维数组中的每个元素与单值做运算，其运算结果也为二维数组。

图2-25中将{1,2,3;3,4,6}*10，运算规则是{1*10,2*10,3*10;3*10,4*10,6*10}，运算结果为={10,20,30;30,40,60}。

实例17 计算业绩大于或等于450万元的销售员的补助总金额

问题描述： 图2-26所示的表中列出了销售员在4个季度的销售业绩。每个业绩大于或等于450万元的销售员补助2000元，请计算出总的补助金额。

解决思路： 只要判断所有业绩区域B2:E7是否有大于或等于450的单元格，然后将比较结果乘以2000，最后求和即可。关键知识点还是理解二维数组与单值之间的运算规律。

操作步骤： 如图2-27所示，首先在G3单元格输入数组公式=SUM((B3:E7>=450)*2000)，然后按Ctrl+Shift+Enter组合键，完成计算。

步骤1： B3:E7>=450判断所有业绩区域是否有大于或等于450的单元格，判断结果为{FALSE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,TRUE;FALSE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE;TRUE,FALSE,FALSE,FALSE}。

步骤2： 先将步骤1中的数组与2000进行相乘运算，在这个运算过程中，由于使用了乘法运算，所以不用单独对步骤1数组中的逻辑值元素做转化运算，计算出的结果为{0,0,0,0;2000,0,0,2000;0,0,0,0;2000,0,0,0;2000,0,0,0}。再用SUM函数进行求和运算，结果为8000。

2.5.6 二维数组与二维数组的运算

二维数组与二维数组的运算与两个一维数组之间的运算一样，是一一对应的，只不过数据尺寸不一样而已。只要保证两个区域或数组的尺寸是一样的就可以。

图2-28中，将{1,2,3;3,4,6}*{10,20,30;30,40,60}，运算规则是{1*10,2*20,3*30;3*30,4*40,6*60}，运算结果为{10,40,90;90,160,360}。

实例18 统计两年内各项商品的销售情况同时合格的人数

问题描述： 图2-29所示的表中列出了2015年和2016年每个人、每种商品的销售情况是否合格的调查表，合格为√，不合格为×。现在要求统计出每种商品的销售情况在两年中同时都为√（也就是合格）的人数。此实例我们用两种思路来解决。

解决思路1： 分别将2015年、2016年的销售情况与“√”做等于比较，从而形成两个二维数组，再将两个二维数组进行*（乘）的算术运算，然后将计算出来的结果求和即可。

操作步骤： 如图2-30所示，首先在B9单元格输入数组公式=SUM（（B3：D6=〝√〝）*（G3：I6=〝√〝）），然后按Ctrl+Shift+Enter组合键，完成计算。

步骤 1： （B3：D6=〝√〝）判断 2015 年的销售情况是否合格，结果为{FALSE，TRUE，TRUE；FALSE，FALSE，TRUE；FALSE，TRUE，TRUE；TRUE，TRUE，FALSE}。

步骤 2： （G3：I6=〝√〝）判断 2016 年的销售情况是否合格，结果为{FALSE，FALSE，TRUE；TRUE，FALSE，FALSE；FALSE，FALSE，TRUE；FALSE，TRUE，TRUE}。

步骤3： 将步骤1和步骤2形成的两个二维数组的逻辑值对应做相乘运算。逻辑值运算有以下几种情况：TRUE*TRUE结果为1，TRUE*FALSE结果为0，FALSE*FALSE结果为0。所以，最终的比较结果为{0,0,1;0,0,0;0,0,1;0,1,0}。

步骤4： 将步骤3的结果进行求和，=SUM({0,0,1;0,0,0;0,0,1;0,1,0})，结果为3。

解决思路2： 因为两个表的姓名顺序、产品顺序都是一样的，所以可以先将两年的数据区域合并成一个二维数组，相当于合成了一个区域。再将合成的二维数组与“√√”做等于比较，然后将结果转化为数字，之后求和的结果便是两年同时合格的人数。这种思路相当于是将两个条件合并为一个条件，是一种不错的思路。这样既可以简化思路，又可以简化代码。

操作步骤： 如图2-31所示，首先在B10单元格输入数组公式=SUM（--（B3：D6&G3：I6=〝√√〝）），然后按Ctrl+Shift+Enter组合键，完成计算。

步骤1： 将B3：D6&G3：I6中两个表的值进行连接合并运算，结果为{〝××〝，〝√×〝，〝√√〝；〝×√〝，〝××〝，〝√×〝；〝××〝，〝√×〝，〝√√〝；〝√×〝，〝√√〝，〝×√〝}。

步骤2： 将步骤1的数组结果与〝√√〝进行等于的比较运算。运算结果为{FALSE，FALSE，TRUE；FALSE，FALSE，FALSE；FALSE，FALSE，TRUE；FALSE，TRUE，FALSE}。

步骤3： 将步骤2的逻辑值数组进行-（负负）转换运算，结果为{0,0,1;0,0,0;0,0,1;0,1,0}，最后SUM函数进行求和运算，求和结果为3。

实例19 综合应用：根据各农产品不同等级对应的单价和数量计算出金额

问题描述： 图2-32所示的表中列出了“农产品收购清单”，表中包括产品名、等级、数量字段。现在需要计算出每种产品的金额，计算产品金额的公式为数量×单价=金额。现在图2-32左边的“农产品收购清单”中有数量、等级，图2-32右边的“产品单价表”中有各产品、各等级对应的单价，所以本实例的关键是怎么查询单价？

解决思路： 先将图2-32左边“农产品收购清单”中的产品和等级合成一个条件，再与图2-32右边的“产品单价表”横向的所有产品与纵向的所有等级合并，形成二维数组。然后将这个二维数组与前面合成的产品等级进行等于比较，又形成了由逻辑值组成的二维数组，此数组中只会有一个逻辑值是TRUE，其他为FALSE。此数组结构刚好与单价区域G3:J5相同，所以将它们对应相乘，就能找到TRUE对应的单价。外层嵌套SUM或MAX函数就能把这个单价提取出来，最后将提取到的单价乘以数量得出金额。

操作步骤： 如图2-33所示，首先将鼠标光标定位在D3单元格，输入数组公式=SUM((A3&B3=G$2:J$2&F$3:F$5)*G$3:J$5)*C3，然后按Ctrl+Shift+Enter组合键，再向下填充公式，完成计算。

步骤1： 将A3&B3连接，合并成一个条件。结果为〝花生1级〝。

步骤2： 将G$2：J$2&F$3：F$5连接，构造出了所有产品的所有等级，运算结果为二维数组{〝花生1级〝，〝胡豆1级〝，〝玉米1级〝，〝水稻1级〝；〝花生2级〝，〝胡豆2级〝，〝玉米2级〝，〝水稻2级〝；〝花生3级〝，〝胡豆3级〝，〝玉米3级〝，〝水稻3级〝}。

步骤3： 将步骤1和步骤2做等于判断，判断步骤2二维数组中的值是否等于步骤1中的〝花生1 级〝，结果为{TRUE，FALSE，FALSE，FALSE；FALSE，FALSE，FALSE，FALSE；FALSE，FALSE，FALSE，FALSE}，相当于做一个定位。

步骤4： 将步骤3得到的二维数组结果与G$3:J$5对应相乘，结果为{5,0,0,0;0,0,0,0;0,0,0,0}，数组中的非零值实际上就是获取的单价，而且这个非零值一定只有一个。

步骤5： 将步骤4的结果求和，公式为SUM（{5，0，0，0；0，0，0，0；0，0，0，0}），获取当前〝花生1级〝的单价，结果为5。

步骤6： 将步骤5中获得的单价乘以数量，公式为=5*C3，计算出的结果为4250。 9FQkSO5Srr6avtB9CyFb1NwRWdtPCZOQbRUl6/6W5DEs+GPvrJdopy50dmQEiwaO