数组常用操作

Excel 引用数组中的大小制约

引用大小制约指的是数组公式中各相关引用之间的大小制约或引用大小对结果集大小的制约。

主关键区域决定数组函数返回值的大小（这里说的关键区域指的是决定数组公式返回结果集大小的区域）。

有相互依赖关系的引用之间大小一定要一致。相互依赖指的就是共同决定某个结果，如果不一致，则会返回一个错误值。

Excel 利用数组模拟IF()

前面讲了利用数组模拟AND和OR，同样利用数组也可以模拟IF()。还是以图10-30所示的工作表数据为例。

前一节讲过，在A8单元格中输入公式“=SUM(AND(C3:C7>2000,C3:C7<2500)*1)”，按“Ctrl+Shift+Enter”组合键返回后，得到的结果是0。在B8单元格中输入公式“=SUM((C3:C7>2000)*(C3:C7<2500)*1)”，按“Ctrl+Shift+Enter”组合键返回后，得到的结果是2。

现在把单元格A8中的公式更改为“=SUM(IF(C3:C7>2000,C3:C7<2500)*1)”，按“Ctrl+Shift+Enter”组合键返回后，得到的结果是2，如图10-33所示。

如果把IF去掉，公式又会变成什么样子呢？

在B8单元格中输入公式“=SUM((C3:C7>2000)*(C3:C7<2500))”，按“Ctrl+Shift+Enter”组合键返回后，得到的结果还是2，如图10-34所示。

图10-33　返回结果

图10-34　返回相同结果

由此可以看出，通常情况下“*”可以模拟IF()。需要注意的是，并不是所有的IF()都可以用“*”代替，用户可根据实际情况灵活运用。

Excel 利用数组模拟AND和OR

1）AND（与关系）：当两个或多个条件必须同时成立时才判定为真是，则称判定与条件的关系为逻辑与关系，就是平常所说的“且”。

2）OR（或关系）：当两个或多个条件只要有一个成立时就判定为真时，则称判定与条件的关系为逻辑或关系。

在Excel中，*和+可以与逻辑判断函数AND和OR互换，但在数组公式中，*和+号能够替换AND和OR函数，反之则行不通。这是因为AND函数和OR函数返回的是一个单值TRUE或FALSE，如果数据公式要执行多重计算，单值不能形成数组公式各参数间的一一对应关系。

打开“工资表.xlsx”工作簿，例如要统计如图10-30所示的表格中基本工资为2000～2500的员工人数，就是说统计工资高于2000且工资低于2500的人数，由此可以判定该条件是一个“逻辑与”关系。

STEP01：如果在单元格A8中输入公式“=SUM(AND(C3:C7>2000,C3:C7<2500)*1)”，按“Ctrl+Shift+Enter”组合键后，返回的结果是0，如图10-31所示。

图10-30　目标数据

图10-31　返回结果

因为公式中“C3:C7>2000”返回的值是{TRUE;FALSE;TRUE;TRUE;TRUE}。而公式“C3:C7<2500)”返回的值是{TRUE;TRUE;FALSE;TRUE;FALSE}。

这两个公式返回的值再逻辑与，则返回的值是FALSE。所以计算结果“=SUM(FALSE*1)=SUM(0*1)=0”。因此返回的结果为0。

STEP02：在单元格B8中输入公式“=SUM((C3:C7>2000)*(C3:C7<2500))”，按“Ctrl+Shift+Enter”组合键后，返回的结果是2，如图10-32所示。

图10-32　返回结果

这是因为在公式中“(C3:C7>2000)*(C3:C7<2500)”

={TRUE;FALSE;TRUE;TRUE;TRUE}*{TRUE;TRUE;FALSE;TRUE;FALSE}

={1;0;1;1;1}*{1;1;0;1;0}

={1;0;0;1;0}

所以公式的计算结果为“=SUM（{1;0;0;1;0}）”=2。

Excel 使用相关公式完整性

什么是相关公式完整性？还是以上一节的案例为例。在选中的单元格区域A1:D4中选择任意单元格，例如这里选择B3单元格，然后对单元格B3中的公式进行任意修改（即使和原公式一致），按“Enter”键返回工作表，会弹出如下图所示的警告对话框。

这是什么原因呢？

因为用户正在企图破坏公式的完整性，A1:D4单元格区域中的数据源都是“={“编号”,”姓名”,”性别”,”年龄”;”001″,”张三”,”男”,”22″;”002″,”张五”,”男”,”24″;”004″,”丁一”,”女”,”23″}”，它们运用的是同一个公式。如果用户想单独更改某一个单元格的公式时，系统会认为用户正在更改部分单元格的数据源，这样一来会导致发生数据源不一致的情况，从而导致与其他相关单元格脱离关系，这样数据公式就失去了意义。所以系统不允许更改数组公式中的部分内容。这样就可以保持数据的完整性，与数据源完全相对应。

Excel 返回数组集合

在使用数组公式时，有可能返回的是一个结果，也有可能返回的是一个集合。

STEP01：打开“人员统计.xlsx”工作簿，选中A1:D4单元格区域，输入公式“={“编号”,”姓名”,”性别”,”年龄”;”001″,”张三”,”男”,”22″;”002″,”张五”,”男”,”24″;”004″,”丁一”,”女”,”23″}”，如果按“Ctrl+Enter”组合键返回，会显示如图10-27所示的结果。

STEP02：如果按“Ctrl+Shift+Enter”组合键则会返回一组集合，结果如图10-28所示。

图10-27　返回结果

图10-28　返回一组集合