精通Excel数组公式009：提供多个值的数组公式

如下图1所示，我们在单元格区域中使用数组公式生成序号，这样，使用者就不能够随意删除其中一个单元格中的序号，只能选中该区域后全部删除。

图1

下面是创建上面的数组公式的步骤：

1. 因为要生成5个数值项（即从1至5的数字），所以首先选择5个单元格，即单元格区域A2:A6。

2. 在活动单元格（A2）中输入公式：

=ROW(A2:A6)-ROW(A2)+1

3. 公式中使用了ROW函数，能够浏览单元格区域并得到该区域中每个单元格所在行的行号。

4. 公式开始部分的：ROW(A2:A6)，创建了一个函数参数数组运算，得到结果数组{2;3;4;5;6}。

5. 减去ROW(A2)，即数字2，得到数组{0;1;2;3;4}.

6. 再加上1得到结果数组{1;2;3;4;5}。

7. 公式输入完后按Ctrl+Shift+Enter键。

这个数组生成5个值，并分别在5个单元格中输入这些值。这类数组公式有下列特点：

1. 不能对数组公式所在的区域进行部分修改。当你试图删除单元格区域A2:A6中的某个单元格中的内容、删除整行等时，会导致下图2所示的错误。

图2

2. 按Ctrl+/键可以选择当前数组公式所在的区域。

3. 有两种方法删除数组公式区域内容。选择整个数组公式区域，按Delete键；或者选择数组公式区域的任意单元格，按空格键，再按Ctrl+Shift+Enter键。

4. 如果需要编辑数组公式，则可以在该数组公式区域中编辑任一单元格中的公式，然后按Ctrl+Shift+Enter键。

5. 选择数组公式区域的任意单元格，在公式栏中都会看到相同的公式。

6. 数组函数（TRANSPOSE函数除外）都可以放置在其他接受一组值的函数（例如COUNT、MIN、MAX等）中，并且该公式不需要按Ctrl+Shift+Enter键。

上例中的数组公式可以归纳为一个求序号的公式构造：

ROW(单元格区域)-ROW(单元格区域中的第一个单元格)+1

这个公式构造可以作为更高级的数组公式中的元素。

下图3展示了一个随单元格行扩展而逐行增加1的公式。

图3

示例：公式编号增加器

图4：公式数字增加器，得到{1;1;1;2;2;2;3;3;3;4;4;4}

图5：公式INT((ROWS(A$1:A1)-1)/3)+1的工作原理

图6：公式数字增加器，得到{1;2;3;1;2;3}

图7：公式MOD(ROWS(A$1:A1)-1,3)+1的工作原理。

示例：将表转换成列数据

如下图8所示，从表中提取数据并放置在一列中。公式中，INDEX函数的参数row_num是：INT((ROWS(A1:A1)-1)/3)+1，参数column_num是：MOD((ROWS(A1:A1)-1),3)+1。可参照上图4至图7来理解。

图8

示例：求日期所在的季度

如下图9所示，通过数组公式一次求出对应日期所在的季度。

图9

公式运行原理如下图10所示。

图10

《Ctrl+Shift+Enter：MasteringExcel Array Formulas》学习笔记

完美Excel

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。

完美Excel社群2020.9.2动态

#VBA# 增强的CELL函数和INFO函数