一、数组的概念及分类在Excel中，数组是由（Array）是由一个或者多个元素按照多行或者多列排列方式进行组合成的集合，这些元素可以是文本、数值、逻辑值、日期、错误值等。根据数组的存在形式，可为常量数组、区域数组和内存数组。 1、常量数组 常量数组的组成元素均为常量数据，其文本必须由一对半角双引号包括起来。常量数组的表示方法为一对大括号（{}）将构成数组的常量包括起来，各常量数组之间是用分隔符间隔。可以使用的分隔符包括半角分号（;）和半角逗号（,），其中分号用于间隔的元素是按行排列的元素，逗号用于间隔按列排列的元素。 例如：{0,'不及格';60,'及格'}，这是一个2行2列的数组。 2、区域数组 区域数组实际上就是公式是对单元格区域直接引用。例如： =SUMPRODUCT(J3:J10*K3:K10)，公式中的J3:J10，K3:K10都是区域的数组。 3、内存数组 内存数组是指通过公式计算返回的结果在内存是临时构成，并且还可以作为一个整体直接嵌套到其他公式中继续参与计算的数组。常量数组是不依赖于单元格而存在的，但与内存数据不同的是常量数组不是通过公式计算获取，而是在公式中直接输入的。 例如：{=SMALL(J5:K14,{1,2,3})}，这个公式中{1,2,3}是常量数组，而整个公式得到的计算结果为J5:K14数据区域中的最小的3个数组成的1列3行的内存数组。 二、数组公式的维度与尺寸数组具有行、列及尺寸的特征，常量数组中用分号或者逗号隔开来辨别行列，而区域数组的行列结构则与某引用的单元格区域保持一致。数组的尺寸同时由两个元素来确定，M行与N列的二维数组是由M*N个元素来组成。 例如常量数组{0,'不及格';60,'及格'; 70,'中午';80,'良好';90,'优秀'}，它包含了5洗2列，一共由5*2=10个元素构成。如图所示： 数组中的各行与各列的元素数必须保持一致，也就是其尺寸必须保持一致，不然的话将会返回错误值。 同时包含两个方向的无数的数组称之为“二维数组”，与其区分的是：如果数组的元素在同一行或者同一列中，那么就称之为“一维数组”。例如{1,2,3,4}都在同一行中，它的所有的元素都是同一行 ，那么我们就称之为“水平数组”，而与之相反的如{1;2;3;4}是一个单列数组，所有的元素都在同一列，那么称之为“垂直数组”。 如果数组中只包含一个元素，那第称之为“，单元素数组”，如{1}，ROW(1:1)等等，与单个数据不同，单元素数组也包含“维”的特征，可以看作是1行1列的一维水平数组或者垂直数组。 三、多项计算与数组公式公式中使用数组进行运算，根据公式或者函数的用法以及目的不同，通常有以下两种不同的计算方式。 一种是以将数组作为一个整体进行运算，运算的结果也通常只有单个数据。例如公式：=SUM(A1:A10),公式是是对它们的整体进行运算，求取他们的合值。 另一种就是将数组中的每个元素同时分别运算，数组的直接运算结果或者公式的最终结果会返回一组数据，例如公式：=SUM(A1:A6*(A1:A6>0))，这个公式中的A1:A6>0是对区域数组中每个元素进行了比较运算符的运算，判断是否大于0，得到了一组逻辑值结果，然后再用逻辑值与这个截获数组中的每个元素进行相乘。相乘的结果是又将两个数组中的每个元素分别对应相乘，得到一个新的数组。这个新的数组中包含大于0的，小于0与将小于0的转换为0 的元素。最后才由SUM函数将其相加，经其结果也就是A1:A10区域中的所有的正数的相加。此类将数组参数的各项元素进行计算的过程称之为“多项计算”。 对于上面的例子中的运算的过程如下: 数组公式就是对一组或多组值执行多项计算，并返回一个或者多个结果，其公式括于大括号中，按<Ctrl Shift Enter>一组合键结束的就称为数组公式。 数组公式可以执行多项计算，但是执行了多项计算的公式并不都是数组公式，如下面的公式：=SUM({1;2;3;4;5}*{-1;-2;3;6;7}>0),这个公式可以执行多项计算并且得到正确的结果，但是灾个公式暗藏不是数组公式。 除此之外 有些函数也不需要使用数组公式就以进行多项计算，例如：SUMPRODUCT、LOOKUP等函数。因此，数组公式并不能与多项计算划上等号。 四、多单元格数组公式在单个单元格中使用数组公式进行多项计算后，有时可以返回一组运算结果，但单元格中只能显示单个值（通常是结果中的首个元素），而无法显示整组运算结果。而使用多单元格数组公式，则可以将结果数组中的每一个元素分别显示在不同的单元格中。 例如：{=SUM(A1:A6*(A1:A6>0))}，选定要输入的区域B1:B10，按组键完成输入后，就可以得到一组结果。称为“多单元格数组”。 使用多单元格数组公式能够保证在同一个范围内的公共具有同一性，并且在选定的范围内分别显示数组公式的各个运算结果。创建此类公式后， 公式所在单元格都不能被独立地编辑，否则将会出现警告对话框。 使用多单元格数组公式，也只是输入方式上的一种特殊，根据公式不同，这返回的结果也有可能是单值。 五、数组的直接运算1、数组与单值的直接运算 数组与单值（或单元元素数组）可以直接运算（所谓“直接运算”，指的是不使用函数，直接使运算符对数组进行运算），返回一个结果，并与原数组相同尺寸，如表所示：
2、同方向一维数组之间的直接运算 两个方向的一维数组直接进行运算，会根据元素的位置进行一一对应运算，生成一个新的数组结果，例如公式：={1;2;3;4}>{2;1;4;3}，返回的结果为：{FALSE; TRUE；FALSE; TRUE}，公式的运算结果如下： 参与运算的两个一维数据通常需要有相同的尺寸，否则结果会出现错误值，如：={1;2;3;4}>{1;2},则返回的结果为{FALSE;TRUE;#N/A;#N/A}，超出较小的那个数组的尺寸的部分会出现错误。 3、不同方向一维数组之间的直接运算 两个不同方向的一维数组即M行垂直数组与N列水平数组进行运算，其运算的方式是：数组中的每一个元素分别与另一数组中的每一个元素进行运算，返回M*N的二维数组。 如：={1;2;3;4}*{1,2,3},返回的结果为{1,2,3;2,4,6;3,6,9;4,8,12},具体的运算如图： 4、一维数组与二维数组之间的直接运算 如果一个一维数组与另一个二维数组的同一方向上的尺寸一致时，可以在这个方向上与数组中的每一个元素进行一一对应的运算。即M行N列的二维数组可以与M行N列的另外一个数组进行运算，即返回一个M*N的二维数组。 如：={1;2;3;4}*{1,2;4,5;6,7;8,9},返回的结果为{1,2;8,10;18,21;32,36}。运算过程如下图 如果两个数组之间的尺寸不一致的时候 ，就会出现错误。 5、下维数组之间的直接运算 两个二维数组如果具有完全相同的尺寸，也可以直接进行运算，运算中将每个相同位置的元素两两对应进行运算，返回一个与他们尺寸一致的二维数组结果。 如：={1，2，3；2，3，4；3，5，6}*{1，4，5；2，5，6；3，5，4}，返回的结果为：{1,8,15;4,15,24;9,25,24}. 如果参与运算的两个二维数组尺寸不一致，那么生成的结果以两个数组中的最大行列尺寸为新的数组尺寸，但超出小尺寸的数组部分会产生错误值。 除了上述所说的数组的直接运算外， 数组之间的运算还包括使用函数。部分函数对参与运算的数组尺寸有特定的要求，比如MMULT函数要求Array1的列数与Array2的行数相同，而不一定遵循直接运算的规则。 -US>={1，2，3；2，3，4；3，5，6}*{1，4，5；2，5，6；3，5，4}，返回的结果为：{1,8,15;4,15,24;9,25,24}. 六、数据公式中的逻辑运算AND与OR函数分别可以进行“逻辑与”、“逻辑或”计算，但是在需要进行多项计算的数组公式中，这两个函数只能返回单个TRUE或FALSE，无法返回数组结果。 如：假定A1:A9单元格包含了一组数据，要统计其中大于60小于70的个数。 如果单纯地从And角度出发，可能写成的公式为：{=SUM(AND(A1:A9>60,A1:A9<70)*1)}。但事实上，这个公式不能有效地返回正确的结果，原因就在于AND不能执行多项计算，不会将两个逻辑值进行每一项元素的计算，而只会将两个数组中的元素看作是一个整体，只能返回单值。上这个公式的返回的结果为： {=SUM(AND({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*1))正确的做法应该是使用简洁运算符代替AND函数，故可以将上述的公式代替为： {=SUM((A1:A9>60)*(A1:A9<70))}，按组合键结束，其运算的过程为： 这种数组的多项逻辑计算在数组公式中是非常地常见的，对于上面的公式也可以拓展到求和与求平均值的用法。
该方法就是利用将逻辑值转化为0与1的数值来参与运算从而来构建公式。 |
|
|
