数组公式的分析和研究

ZhouFfett

    本贴将藉着对一系列包含INDIRECT函数的区域数组公式的分析和研究，从而引申出很多同数组公式相关的理论知识。
       indirect数组疑难.rar (165.35 KB, 下载次数: 414)

2018-5-21 22:01 上传

点击文件名下载附件

    先看截图（即附件中的Sheet0表）：

    图中，相同的数据源（A1:E6和A8:B10），5个区域数组公式返回两种的计算结果。
    下面将为大家分析这些数组公式的计算过程，两种不同结果的成因。在分析的过程中，将介绍到很多跟数组公式相关的原理。


    数组公式可以分为两种：单格数组公式和区域数组公式。所谓单格数组公式，就是在一个单元格中输入一个数组公式；所谓区域数组公式，就是在一片连续的单元格区域中输入一个数组公式。

       这样的分类是很有必要的，从后面的分析中，大家将会了解到这两类数组公式的不同之处。

   （待续）

ZhouFfett

    下面开始分析区域数组公式1：{=SUM(INDIRECT($A$8:$B$10))} （见附件中的Sheet1表）：

    在分析区域数组公式之前，先分析单格数组公式。
    为了弄清楚公式中的INDIRECT($A$8:$B$10)返回什么结果，这里利用INDEX函数来进行分析。


    下面首先对INDEX函数（主要分析INDEX函数的引用形式）的使用，进行一些必要的测试和解释。
    比如，普通公式=INDEX(A1:D3,2,1)返回对A1:D3中第2行第1列的引用。
    在这里，我们需要注意两点：
    （1）INDEX函数的第一个参数，现在是一个引用，所以INDEX的返回值也是一个引用。
    在上面公式的外面，再套一上ROW函数，变成=ROW(INDEX(A1:D3,2,1))，结果返回2。
    ——可见，这里的INDEX返回的是一个单元格引用，而不是单元格的值。
    ——INDEX(A1:D3,2,1)返回对单元格A2的引用，然后=A2才返回单元格A2的值，INDEX并不直接返回A2的值。
    （2）INDEX函数的第二、第三个参数分别指定第几行、第几列，如果指定的范围超出第一个参数的，则返回错误值#REF!。例如=INDEX(A1:D3,4,1)会返回#REF!。
    （待续）

ZhouFfett

        下面继续分析INDIRECT($A$8:$B$10)的返回值。
        INDIRECT的参数是一个两列三行的数组，经过数组运算后，INDIRECT返回的也是一个两列三行的数组，但该数组中的每个元素都是一个单元格引用：

      {

        A1:D3, B1:E4;

        C2, C3:E5;

        D6, A2:E4

      }

      但是，我们分析2楼的截图（即附件的Sheet1表）中，C21～C29的单格数组公式，发现其中的INDEX函数，只使用到INDIRECT返回的数组中的第一个元素A1:D3；而C30的单格数组公式，SUM函数也只对A1:D3求和。

      ——C21～C30的单格数组公式，只使用了INDIRECT返回的数组中的第一元素，而其他的元素一律忽略掉。为什么会这样呢？

    （待续）

ZhouFfett

       接下来，要讲解数组缩减和数组扩展的原理。
    看截图（即附件中的Sheet1-1表）：

    数组的缩减，是基于一个单元格只能保存一个单值的原理。
    比如，在一个单元格中输入一个数组，单元格只能保存该数组中第一个元素的值，如A3的公式。
    又如，在一个两列两行的区域中输入一个三行三列的数组，只能保存数组的前两行和前两列的值，如A5:B6的区域数组公式。
    这样就可以理解3楼的情况，C21～C30的单格数组公式只使用INDIRECT返回的数组中的第一元素，而忽略数组其他的元素。


    而数组的扩展，则是基于一个单元格需要获得一个单值的原理。
    比如，对于一个两列两行的区域数组公式（如A11:B12），只提供了一个单值{=1}，Excel自动把该值扩展到该区域中的每一个单元格。
    数组扩展的结果有两种：原值扩展和N/A值扩展。
    在新维度上的扩展，使用原值扩展；在原有维度上的扩展，使用N/A值扩展。
    比如，A14:B16是一个三行三列的区域，而其数组公式{={1,2}}只提供了一个一行两列的水平数组，于是，在垂直方向的扩展为新维度扩展，适用原值扩展，在水平方向的扩展则使用N/A值扩展。
    （待续）

ZhouFfett

       有了数组扩展的理论，我们就可以理解单格数组公式与区域数组公式有什么不同了。
    继续看附件中的Sheet1表（2楼的截图）。
    由于C21～C30的单格数组公式，只需要返回一个单值给单元格，所以，公式只使用INDIRECT返回的数组中的第一元素。
    而C14:F18的区域数组公式则不同，在该区域中只有一个公式，而这个公式需要返回一个四列五行的数组以适应该区域。
    从3楼的分析可知，这时候INDIRECT函数返回的是一个两列三行的数组，而C14:F18是一个四列五行的数组，所以INDIRECT返回的数组需要进行数组扩展。
    两个数组都是二维，所以使用N/A值扩展。

       {

         A1:D3, B1:E4;

         C2, C3:E5;

         D6, A2:E4

       }

    扩展后为：

       {

          A1:D3, B1:E4, #N/A, #N/A;

         C2, C3:E5, #N/A, #N/A;

         D6, A2:E4, #N/A, #N/A;

         #N/A, #N/A, #N/A, #N/A;

         #N/A, #N/A, #N/A, #N/A

        }

    区域数组公式1：{=SUM(INDIRECT($A$8:$B$10))}的计算过程如下：

       ={

            SUM(A1:D3), SUM(B1:E4), SUM(#N/A), SUM(#N/A);

            SUM(C2), SUM(C3:E5), SUM(#N/A), SUM(#N/A);

            SUM(D6), SUM(A2:E4), SUM(#N/A), SUM(#N/A);

            SUM(#N/A), SUM(#N/A), SUM(#N/A), SUM(#N/A);

            SUM(#N/A), SUM(#N/A), SUM(#N/A), SUM(#N/A)

         }

    所以最后返回：

       {

          446, 1760, #N/A, #N/A;

          3, 307, #N/A, #N/A;

          3, 1033, #N/A, #N/A;

          #N/A, #N/A, #N/A, #N/A;

          #N/A, #N/A, #N/A, #N/A

        }

    区域数组公式2：{=SUM(IF(1,INDIRECT($A$8:$B$10)))}的数组扩展同上，其计算过程如下：

       ={

             SUM(IF(1,A1:D3)), SUM(IF(1,B1:E4)), SUM(IF(1,#N/A)), SUM(IF(1,#N/A));

             SUM(IF(1,C2)), SUM(IF(1,C3:E5)), SUM(IF(1,#N/A)), SUM(IF(1,#N/A));

             SUM(IF(1,D6)), SUM(IF(1,A2:E4)), SUM(IF(1,#N/A)), SUM(IF(1,#N/A));

             SUM(IF(1,#N/A)), SUM(IF(1,#N/A)), SUM(IF(1,#N/A)), SUM(IF(1,#N/A));

             SUM(IF(1,#N/A)), SUM(IF(1,#N/A)), SUM(IF(1,#N/A)), SUM(IF(1,#N/A))

        }

    所以最后返回同上，不再累赘。
   （待续）

ZhouFfett

       在分析后面三个区域数组公式之前，先说说降维函数。
    Excel中有不少这样的函数，其参数可以是一维或两维的数组，而函数的返回值是一个单值。我们可以把这类函数称为降维函数。例如SUM、COUNT、AVERAGE、INDEX等等。
    在数组公式中，一个降维函数嵌套另一个降维函数，叫做降维函数嵌套吧，比如区域数组公式3的INDEX(SUM)，区域数组公式4的SUM(SUM)。
    降维函数嵌套将增加数组的维度，改变数组维度的方向，从而影响数组扩展的结果。


    现在分析区域数组公式3：{=INDEX(SUM(INDIRECT($A$8:$B$10)),1,1)}
         INDEX函数需要生成一个四列五行的数组以适合区域，其中的每一个元素都是一个 SUM函数，而每个SUM函数都包含一个两列三行的数组。
    注意了，中间这个SUM函数，在区域数组公式1和区域数组公式3中，是处于不同维度（或者说不同方向）的；而INDIRECT所生成的数组，在这两个区域数组公式也是处于不同的维度（或不同方向）。
    不同的维度方向，必然产生不同的数组扩展。


    区域数组公式3的计算过程，已写在附件中的Sheet3表。

ZhouFfett

更正一下，4楼所说的“数组忽略”，改称为“数组缩减”会准确些。

因为忽略的并不是整个数组，而是数组中的一部分元素；忽略了一部分元素，原来的数组就缩减了，因此应该改称“数组缩减”

r_zxf

ZhouFfett 发表于 2018-5-21 22:29
在分析后面三个区域数组公式之前，先说说降维函数。
    Excel中有不少这样的函数，其参数可以是一 ...

对于函数SUM，你描述的计算规则哪来的？你经常谈理论，此处有何理论支撑？

ZhouFfett

r_zxf 发表于 2018-5-22 22:18
对于函数SUM，你描述的计算规则哪来的？你经常谈理论，此处有何理论支撑？

测试结果支持我这个说法

r_zxf

ZhouFfett 发表于 2018-5-22 22:22
测试结果支持我这个说法

你的这些测试在我的总结中可以找到。