【EXCEL：你知道什么是CSE公式吗？】
在本教程中,您将学习Excel数组公式是什么,如何正确地输入数组公式,以及如何在Excel中使用数组常量和数组函数。
Excel中的数组公式是一个非常强大的工具，也是最难掌握的工具之一，一个Excel数组公式可以执行多个计算并替换数千个常用公式。而且，90%的Excel用户从来没有在工作表中使用过数组函数，因为他们害怕开始学习（我也一样，感觉好复杂，难学！）
实际上，数组公式是最令人困惑的Excel特性之一。本教程的目的是让学习的过程尽可能的简单和流畅，循序渐进的学习。
目录：
在Excel里什么是数组公式？
如何输入数组公式(CTRL + SHIFT + enter)
如何评估计算数组公式的部分项(F9键)
Excel中单个单元格和多个单元格的数组公式
Excel数组常量
Excel数组公式中AND 和 OR 运算符
数组公式中的双一元运算符
在Excel里什么是数组公式？
在我们开始使用Excel数组函数和公式之前，我们先来弄清楚“数组”这个词的含义。从本质上讲，Excel中的数组是项的集合。这些项可以是文本或数字，它们可以驻留在单个行或列中，或在多个行和列中。
例如，如果你把每周的购物清单放入Excel数组格式，它看起来是这样的:
{“牛奶”，“鸡蛋”，“黄油”，“玉米片”}
然后，如果您选择A1到D1，输入上面的数组，然后在公式栏中使用等号(=)并按CTRL + SHIFT + enter，您将得到以下结果:
你所做的只是创建一个一维水平数组。到目前为止还没有什么可怕的，对吧?
什么是Excel数组公式?数组公式和普通Excel公式之间的区别是，数组公式处理多个值而不是一个值。换句话说，Excel中的数组公式对数组中的所有单个值进行计算，并根据公式中表达的条件对一个或多个项目执行多个计算。
数组公式不仅可以同时处理多个值，还可以同时返回多个值。因此，Excel数组公式返回的结果也是一个数组。
在Excel 2013,Excel 2010,Excel 2007，以及更低的版本中都可以使用数组公式。
现在，似乎是你创建第一个数组公式的合适时机。
Excel数组公式的简单示例
假设在B列有一些商品的名称，C列是价格，你想计算所有销售的总金额。
当然，你可以先计算每一行的总数用= B2 * C2，然后再把每一行的总数求和:
但是，一个数组公式可以省去这些额外公式的输入，因为它可以在内存中存储中间结果，而不是在额外的列中。所以，它所需要的只是一个单一的数组公式和两个简单快速的操作步骤:
选择一个空单元格并输入以下公式:
=SUM(B2:B6*C2:C6)
按下键盘快捷键CTRL + SHIFT + ENTER完成数组公式。
一旦你这样做了，Excel就会用{大括号}来包围这个公式，大括号是数组公式的标识。
公式所做的是将每一行指定数组中的值相乘(单元格区域B2到C6)，将各行子总数加在一起，然后输出一个大的总数:
这个简单的例子展示了Excel数组公式的威力。当处理成百上千行数据时，只需考虑在单个单元中输入一个数组公式可以节省多少时间。
为什么在Excel中使用数组公式?
Excel数组公式是执行复杂计算和完成复杂任务的最方便工具。一个单一的数组公式可以替换数百个通常的公式。
数组公式非常善于处理诸如:
1. 满足某些条件的数字，例如在一个范围内的最大或最小值。
2.某几行，或者第n行或列的求和，如这个例子所示。
3. 计算指定范围内所有或某些字符的数量（ps:这一条有时间再举例学习）
如何在Excel中输入数组公式(Ctrl + Shift + enter)
正如你已经知道的，CTRL + SHIFT + ENTER是一个神奇的组合键，它将一个常规公式变成一个数组公式。
当在Excel中输入数组公式时，需要记住4件重要的事情:
一旦你完成了这个公式，并同时按下CTRL SHIFT ENTER键，Excel就会自动地将公式包围在{大括号}中。当您选择这样一个单元格时，您可以看到公式栏中的大括号，说明这个单元格有数组公式。
手动地在一个公式两端输入大括号不会把它转换成一个数组公式。你必须按下Ctrl + Shift + Enter键来完成一个数组公式。
每次编辑数组公式时，括号都会消失，必须重新按下Ctrl + Shift + Enter，以保存公式中的更改。
如果忘记按Ctrl + Shift + Enter，那么您的公式就会像通常的Excel公式一样，只处理指定数组中的第一个值。
因为所有的Excel数组公式都需要按Ctrl + Shift + Enter，所以它们有时被称为CSE公式（和标题对上了）。
如何评估计算数组公式的部分项(F9键)
在Excel中使用数组公式时，您可以观察它们如何计算和存储它们的项(内部数组)以显示在单元格中看到的最终结果。要做到这一点，在函数的括号内选择一个或多个参数，然后按F9键。按Esc键退出评估模式。
在上面的例子中，为了查看所有产品的子总数，你选择B2:B6 * C2:C6，按F9，得到以下结果。
请注意，您必须在按F9之前选择公式的一部分，否则F9键将简单地将Excel数组公式替换为计算值。
Excel中的单个单元格和多单元格数组公式
Excel数组公式可以在单个单元格或多个单元格中返回结果。 在单元格范围内输入的数组公式称为多单元公式。 驻留在单个单元格中的数组公式称为单个单元格公式。
这里存在几个Excel数组函数，用于返回多单元数组，例如TRANSPOSE，TREND，FREQUENCY，LINEST等。
其他功能函数，如SUM，AVERAGE，AGGREGATE，MAX，MIN，可以使用Ctrl + Shift + Enter输入单个单元格时计算数组表达式。
以下示例演示如何在Excel中使用单个单元格和多单元格数组公式。
示例1.单个单元格数组公式
假设您有两列列出了两个不同月份销售的商品数量，比如列B和C，并希望找到最大的销售增额是多少。
通常，您可以添加一个额外的列，例如列D，使用公式如= C2-B2计算每个产品的销售额变化，然后在该附加列= MAX（D：D）中找到最大值。
Excel数组公式不需要额外的列，因为它完美地将中间结果存储在内存中。 所以，您只需输入以下公式，然后按Ctrl + Shift + Enter：
示例2. Excel中的多单元格数组公式
在以前的SUM示例中，假设您必须从每次销售中支付10％的税款，并且您想要使用一个公式计算每个产品的税额。
选择一些空白列中的单元格范围，例如D2：D6，并在公式栏中输入以下公式：
= B2：B6 * C2：C6 * 0.1
一旦按下Ctrl + Shift + Enter，Excel会将您的数组公式的实例放在所选范围的每个单元格中，您将得到以下结果：
示例3.使用Excel数组函数返回多单元格数组
如前所述，Microsoft Excel提供了一些所谓的“数组函数”，它们是专为多单元格数组而设计的。 Excel TRANSPOSE是其中一个功能，我们将利用它来转置上表，即将行转换为列。
选择要输出转置表的单元格的空白范围。 由于我们将行转换为列，因此请务必分别选择与源表具有列和行相同数量的行和列。 在这个例子中，我们选择6列和4行。
按F2进入编辑模式。
输入数组函数= TRANSPOSE（数组），然后按Ctrl + Shift + Enter。 在我们的例子中，公式为= TRANSPOSE（$ A $ 1：$ D $ 6）。
结果看起来与此类似：
这是在Excel中如何使用数组函数。
如何使用多单元格数组公式
在Excel中使用多单元格数组公式时，请务必遵循以下规则获取正确的结果：
在输入公式之前，选择要输出结果的单元格范围。
要删除多单元格数组公式，请选择所有包含它的单元格，然后按DELETE，或者选择公式栏中的整个公式，按DELETE，然后按Ctrl + Shift + Enter。
您不能编辑或移动Excel数组公式中的单个单元格的内容，也不能在多单元格数组公式中插入新单元格或从其删除现有单元格。无论何时尝试执行此操作，Microsoft Excel将抛出警告“您不能更改数组的一部分”。
要缩小阵列公式，即将其应用于更少的单元格，您需要首先删除现有公式，然后输入新的公式。
要扩展数组公式，即将其应用于更多的单元格，请选择所有包含当前公式的单元格加上要存储的单元格，然后按F2切换到编辑模式，调整公式中的引用，然后按Ctrl + Shift + Enter进行更新。
您应该在与公式返回的结果数组相同大小的单元格范围内输入多单元格数组公式。如果您的Excel数组公式产生的数组大于所选范围，则超出值将不会显示在工作表上。如果公式返回的数组小于所选范围，＃N / A错误将显示在额外的单元格中。
如果您的公式可能返回具有可变数目元素的数组，请在等于或大于公式返回的最大数组的范围内输入，并将公式包装在IFERROR函数中。
Excel数组常量
在Microsoft Excel中，数组常量只是一组静态值。 将公式复制到其他单元格或值时，这些值不会更改。
您已经看到了本教程一开始就从杂货列表创建的数组常量的例子。 现在，我们来看看其他数组类型的存在以及如何创建它们。
Excel中存在3种数组常量：
1.水平数组常量
一个水平数组常量驻留在一行中， 要创建一行数组常量，请键入逗号分隔的值，然后用大括号括起来，例如{1,2,3,4}。
注意。 创建数组常量时，您应该手动输入开关和大括号。
要在电子表格中输入水平数组，请选择一行中相应数目的空白单元格，在公式栏中键入公式= {1,2,3,4}，然后按Ctrl + Shift + Enter。 结果将类似于：
如截图所示，Microsoft Excel将数组常量包裹在另一组大括号中，与在Excel中输入数组公式时一样。
2.垂直数组常量
垂直数组常量驻留在列中， 与水平数组相同的方式创建它，唯一的区别是常量之间是以分号分割的，例如：
3.二维数组常量
要在Excel中创建一个二维数组，您可以用逗号分隔每行中的项目，并用分号分隔每列。
使用Excel数组常量
数组常量是Excel数组公式的基石之一。以下信息和提示可能有助于您以最有效的方式使用它们。
1.数组常量的元素
Excel数组常量可以包含数字，文本值，布尔值（TRUE和FALSE）以及以逗号或分号分隔的错误值。
您可以输入数值为整数，十进制或科学记数法。如果使用文本值，它们应该以双引号（“）包围，就像在任何Excel公式中一样。
数组常量不能包括其他数组，单元格引用，范围，日期，定义的名称，公式或Excel函数。
2.命名数组常量
为了使数组常量更容易使用，请给它一个名称：
切换到公式选项卡>定义名称组，然后单击定义名称。或者，按Ctrl + F3，然后单击新建。
在名称中键入名称
在“引用”框中，输入包含大括号的数组常量项与前面的等号（=）。例如：
= {“Su”，“Mo”，“Tu”，“We”，“Th”，“Fr”，“Sa”}
单击“确定”保存命名的数组并关闭窗口。
要在工作表中输入命名的数组常量，请选择与数组中的项目一致的行或列中的单元格，在公式栏中键入数组的名称，然后按Ctrl + Shift + Enter。
结果应该类似于：
3.防止错误
如果您的数组常量无法正常工作，请检查以下问题：
使用正确的字符定义数组常量的元素 – 水平数组常量中的逗号和垂直数组常量中的分号。
选择与数组常量中的项目数量完全匹配的单元格范围。 如果您选择更多的单元格，每个额外的单元格将具有＃N / A错误。 如果选择较少的单元格，则只会插入阵列的一部分。
在Excel公式中使用数组常量
既然您已经熟悉了数组常量的概念，我们来看看如何在Excel公式中使用数组来解决实际的任务。
示例1.在范围内求和N个最大/最小的数字
您首先创建一个垂直数组常量，其中包含您想要求和的数量。 例如，如果要在范围内加起3个最小或最大的数字，则数组常量为{1,2,3}。
然后，您将使用LARGE或SMALL函数，在第一个参数中指定单元格的整个范围，并在第二个参数中包含数组常量。 最后，将其嵌入到SUM函数中，如下所示：
总和最大的3个数字：= SUM（LARGE（range，{1,2,3}））
求最小3个数字：= SUM（SMALL（range，{1,2,3}））
输入Excel数组公式后，不要忘记按Ctrl + Shift + Enter，您将得到以下结果：
以类似的方式，您可以计算一个范围内N个最小或最大值的平均值：
三个数值靠前的平均值：= AVERAGE（LARGE（range，{1,2,3}））
三个数值靠后的平均值：= AVERAGE（SMALL（range，{1,2,3}））
示例2.用多个条件对单元格进行计数的数组公式
假设你有一个订单列表，你想知道一个送货商卖给指定产品的次数。
最简单的方法是使用具有多个条件的COUNTIFS公式。 但是，如果要包含许多产品，您的COUNTIFS公式可能会增长太大。 为了使其更紧凑，您可以与SUM一起使用COUNTIFS，并在一个或多个参数中包含数组常量，例如：
= SUM（COUNTIFS（range1，“criteria1”，range2，{“criteria1”，“criteria2”}））
真正的公式可能如下：
= SUM（COUNTIFS（B2：B9，“sally”，C2：C9，{“apples”，“lemons”}））
我们的示例数组只包含两个元素，因为目标是演示该方法。 在您的真正的数组公式中，您可能会包含与业务逻辑需求相同的元素，前提是在Excel 2013，2010和2007中，公式的总长度不超过8,192个字符（Excel 2003及更低版本中为1,024个字符） 强大到足以处理大型阵列。 有关详细信息，请参阅Excel中数组公式的限制。
AND和OR运算符在Excel数组公式的用法
数组运算符告诉公式如何处理数组 – 使用AND或OR逻辑。
AND运算符是星号（*）。 如果所有条件都为TRUE，它将指示Excel返回TRUE。
OR运算符是加号（+）。 如果给定表达式中的任何条件求值为TRUE，则返回TRUE。
1.使用AND运算符的数组公式
在这个例子中，我们找到销售人员是Mike的销售总额，产品是苹果：
= SUM（（A2：A9 =“Mike”）*（B2：B9 =“苹果”）*（C2：C9））
要么
= SUM（IF（（（A2：A9 =“Mike”）*（B2：B9 =“Apples”）），（C2：C9）））
2.Excel数组公式与OR运算符
OR运算符（+）的以下数组公式将销售人员的所有销售额加起来，或者产品是苹果：
= SUM（IF（（（A2：A9 =“Mike”）+（B2：B9 =“Apples”）），（C2：C9）））
在现代版本的Excel 2013,2010或2007中，在这种情况下使用数组公式实际上是多余的，而简单的SUMIFS公式可以产生完全相同的结果。 然而，数组公式中的AND和OR运算符可能在其他更复杂的场景中证明是有用的。
Excel数组公式中的双一元运算符
如果您曾经在Excel中使用过数组公式，那么您可能会碰到一些包含两个 “- -“符号的数组，您可能会想知道它是用于什么。
技术上称为双一元运算符的双破折号用于将某些表达式返回的非数字布尔值（TRUE / FALSE）转换为Excel数组函数可以理解的1和0。
以下示例将有助于使事情更容易理解。 假设您在列A中列出了日期列表，并且希望知道1月份的日期多少个，而不考虑年份。
以下公式将会奏效：
=SUM(–(MONTH(A2:A10)=1))
因为这是一个Excel数组公式，记得按Ctrl + Shift + Enter来完成它。
如果您对其他月份感兴趣，请使用相应的数字替换1。 例如，2代表2月，3代表March，等等。 要使公式更灵活，您可以在某些单元格中指定月份号，如屏幕截图所示：
现在，我们分析一下这个Excel数组公式的工作原理。 MONTH函数返回由序列号表示的单元格A2到A10中每个日期的月份，产生阵列{2; 1; 4; 2; 12; 1; 2; 12; 1}。
之后，将数组的每个元素与单元格D1中的值进行比较，该值在本示例中为1。 此比较的结果是布尔值为TRUE和FALSE的数组。 记住，您可以选择数组公式的某一部分，然后按F9查看该部分等同于：
最后，您必须将这些布尔值转换为1和0，SUM函数可以理解。 这就是双重一元运算符所需要的。 第一个一元运算符分别强制为TRUE / FALSE到-1/0。 第二个一元运算符再次把值否定，即反转，将其转换为+1和0，这大部分Excel函数可以理解和使用。 如果从上述公式中删除双一元，则不起作用。
我希望这个简短的教程在您掌握Excel数组公式的道路上已被证明是有帮助的。
转载请注明：徐自远的乱七八糟小站 » 【EXCEL：你知道什么是CSE公式吗？】
苏公网安备 32021402001397号