我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
数组公式是可对数组中的一个或多个项目执行多个计算的公式。你可以将数组视为值的一行或一列,或者视为值的行和列的组合。数组公式可以返回多个结果,也可以返回单个结果。
从Office365的2018年9月更新开始,任何可返回多个结果的公式将自动溢出它们,或跨越相邻单元格。此行为更改还附带几个新的动态数组函数。动态数组公式,无论是使用现有函数还是动态数组函数,只需输入单个单元格,然后按enter确认。以前,旧数组公式要求首先选择整个输出区域,然后用Ctrl+Shift+Enter确认公式。它们通常称为CSE公式。
可以使用数组公式执行复杂任务,例如:
以下示例演示了如何创建多单元格数组公式和单单元格数组公式。在可能的情况下,我们提供了一些包含一些动态数组函数的示例,以及输入为动态和旧数组的现有数组公式。
下载我们的示例下载包含本文中所有数组公式示例的示例工作簿。
Office365Office2010-Office2019
按enter时,你会看到结果溢出到单元格H10:H19。请注意,当你选择溢出区域中的任意单元格时,溢出范围将以边框突出显示。你可能还会注意到单元格H10中的公式是灰显的。它们只是为了引用,因此,如果你想要调整公式,则需要选择单元格H10,其中主公式存在。
在示例工作簿的单元格H20中,键入或复制并粘贴=SUM(F10:F19*G10:G19),然后按Enter。
在这种情况下,Excel会将数组中的值(单元格区域F10到G19)相乘,然后使用SUM函数将总计相加。结果等于$1,590,000的总销售额。
本示例演示了此类公式的强大功能。例如,假定您有1,000行数据。您可以通过在单个单元格中创建数组公式来对部分或全部数据求和,而不是将公式向下拖动以包括所有1,000行来求和。此外,请注意单元格H20中的单单元格公式完全独立于多单元格公式(单元格H10到H19中的公式)。这是使用数组公式的另一个优点 -灵活性。你可以更改列H中的其他公式,而不影响H20中的公式。您也可以采用如下方式具有独立的汇总,因为它有助于验证结果的准确性。
={1,2,3,4,5}或={“一月份”,”二月”,”三月”}
如果使用逗号分隔各个项,将创建水平数组(一行)。如果使用分号分隔项,将创建垂直数组(一列)。若要创建二维数组,请使用逗号分隔每行中的项目,并用分号分隔每一行。
下面将为你提供创建水平、垂直和二维常量的练习。我们将使用SEQUENCE函数显示示例,以自动生成数组常量以及手动输入的数组常量。
使用上述示例中所用工作簿,或创建新的工作簿。选择任何空单元格,然后按enter=SEQUENCE(1,5)。SEQUENCE函数通过与={1,2,3,4,5}生成1行x5列数组。将显示以下结果:
选择下方有聊天室的任何空白单元格,然后按enter=SEQUENCE(5)或={1;2;3;4;5}。将显示以下结果:
选择右侧和下方带有空间的任何空白单元格,然后按enter=SEQUENCE(3,4)。将得到以下结果:
您也可以输入:or={1,2,3,4;5,6,7,8;9,10,11,12},但您希望在放置分号和逗号的位置上格外关注。
正如你所看到的,序列选项比手动输入数组常量值具有显著的优势。它主要为您节省时间,但也有助于减少手动输入的错误。它也更易于阅读,尤其是在半冒号很难区分逗号分隔符。
在单元格D9中,我们输入了=SEQUENCE(1,5,3,1),但也可以在单元格A9:H9中输入3、4、5、6和7。对于特定的数字选择,我们只需选择1-5以外的其他内容。
在单元格E11中,输入=sum(D9:h9*SEQUENCE(1,5))或=SUM(d9:H9*{1,2,3,4,5})。公式返回85。
SEQUENCE函数生成数组常量的等效项{1,2,3,4,5}。由于Excel先对括在括号中的表达式执行运算,接下来,接下来的两个元素是D9:H9中的单元格值和乘法运算符(*)。此时,公式将存储数组中的值与常量中对应的值相乘。它等价于:
=Sum(D9*1,E9*2,F9*3,G9*4,H9*5)或=SUM(3*1,4*2,5*3,6*4,7*5)
最后,SUM函数将添加值,并返回85。
若要避免使用存储的数组并将操作完全保留在内存中,可以将其替换为另一个数组常量:
=SUM(SEQUENCE(1,5,3,1)*序列(1,5)或=SUM({3,4,5,6,7}*{1,2,3,4,5})
可在数组常量中使用的元素
转到”公式”>定义的名称>”定义名称”。在”名称”框中,键入”第1季度”。在“引用位置”框中,输入下面的常量(记住要手动键入大括号):
={“一月”,”二月”,”三月”}
该对话框现在应如下所示:
单击”确定”,然后选择带有三个空白单元格的任何行,然后按Enter=第1季度。
将显示以下结果:
如果希望结果在垂直方向(而不是水平)溢出,可以使用=换位(第1季度)。
如果想要显示12个月的列表,例如在构建财务报表时可能使用的列表,则可以使用SEQUENCE函数将其与当前年份基本。此函数的整洁之处在于,即使只显示月份,也有一个有效的日期,您可以在其他计算中使用它。您将在示例工作簿中的命名数组常量和快速示例数据集工作表中找到这些示例。
=TEXT(日期(YEAR(TODAY()),SEQUENCE(1,12),1),”mmm”)
这将使用DATE函数基于当前年份创建日期,序列将在1月到12月之间创建一个从1到12的数组常量,然后文本函数将显示格式转换为”mmm”(Jan、二月、三月等)。如果想要显示完整月份名称(如一月),请使用”mmmm”。
将命名常量用作数组公式时,请记住输入等号,如在=第1季度中,而不仅仅是第1季度。如果没有输入等号,Excel将数组解释为文本字符串,并且公式不会按预期工作。最后,请记住,你可以使用函数、文本和数字的组合。这完全取决于您希望获得的创意。
Enter=SEQUENCE(1,12)*2,或={1,2,3,4;5,6,7,8;9,10,11,12}*2
您也可以用(/)进行除法运算,加上(+),然后用(-)进行减法运算。
Enter=SEQUENCE(1,12)^2,或={1,2,3,4;5,6,7,8;9,10,11,12}^2
Enter=sqrt(SEQUENCE(1,12)^2),或者=SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)
Enter=换位(SEQUENCE(1,5)),或=换位({1,2,3,4,5})
即使输入的是水平数组常量,TRANSPOSE函数也会将该数组常量转换为列。
Enter=换位(SEQUENCE(5,1)),或=换位({1;2;3;4;5})
即使输入的是垂直数组常量,TRANSPOSE函数也会将该常量转换为行。
Enter=换位(SEQUENCE(3,4))或=换位({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE函数将各行转换为一系列的列。
以下示例说明了如何使用数组公式从现有数组创建新数组。
Enter=SEQUENCE(3,6,10,10),或={10,20,30,40,50,60;70,80,90100110120;130140150160170180}
请确保在键入10之前键入{(左大括号),然后在键入180后键入”}”(右大括号),因为您要创建一个数字数组。
接下来,在空白单元格中输入=d9#,或=d9:I11。3×6单元格的单元格显示,其值与D9:D11中显示的值相同。#符号称为溢出的范围运算符,它是引用整个数组范围的Excel’s方式,而无需键入它。
你可以获取溢出的数组公式的结果,并将其转换为其组件部分。选择单元格D9,然后按F2切换到编辑模式。接下来,按F9将单元格引用转换为值,然后Excel将其转换为数组常量。按enter时,公式=D9#现在应为={10,20,30;40,50,60;70,80,90}。
下面的示例演示了如何计算单元格区域中的字符数。其中包括空格。
=SUM(LEN(C9:C13))
在这种情况下,LEN函数返回区域中每个单元格的每个文本字符串的长度。然后,SUM函数将这些值相加并显示结果(66)。如果想要获取平均字符数,可以使用:
=AVERAGE(LEN(C9:C13))
=INDEX(C9:C13,MATCH(MAX(C9:C13)),LEN(C9:C13),0),1)
本公式仅在数据区域包含单列单元格时适用。
让我们从内部元素开始,由内而外深入了解此公式。LEN函数返回单元格区域D2:D6中每个项目的长度。MAX函数计算这些项目中的最大值,它们对应于单元格D3中最长的文本字符串。
下面的计算稍微有点复杂。MATCH函数计算包含最长文本字符串的单元格的偏移量(相对位置)。为此,需要三个参数:分别是查阅值、查阅数组和匹配类型。MATCH函数在查阅数组中搜索指定的查阅值。在这种情况下,查阅值为最长的文本字符串:
MAX(LEN:C13)
并且该字符串位于此数组中:
LEN(C9:C13)
此例中的”匹配类型”参数为0。Match类型可以是1、0或-1值。
最后,INDEX函数采用以下参数:数组,以及该数组中的行号和列号。C9的单元格区域:C13提供数组,MATCH函数提供单元格地址,最后一个参数(1)指定值来自数组中的第一列。
如果想要获取最小文本字符串的内容,请将上述示例中的最大值替换为MIN。
此示例显示了如何查找单元格区域中的三个最小值,其中数组是单元格B9中的示例数据数组:=INT(RANDARRAY(10,1)*100)。B18has已创建。请注意,RANDARRAY是可变函数,因此每次Excel计算时你将收到一组新的随机数字。
Enter=small(b9#,序列(D9),=SMALL(b9:B18,{1;2;3})
此公式使用数组常量计算小函数三次,并返回单元格B9中包含的数组中的最小3个成员:B18,其中3是单元格D9中的可变值。若要查找更多值,可以增大SEQUENCE函数中的值,或向该常量添加更多参数。还可以对此公式使用其他函数,例如SUM或AVERAGE。例如:
=SUM(小号(B9#,序列(D9))
=AVERAGE(SMALL(B9#,序列(D9))
若要查找区域中的最大值,可以将SMALL函数替换为大型函数。此外,下面的示例使用ROW和INDIRECT函数。
Enter=大型(b9#、ROW(间接(”1:3″)))或=大型(b9:B18、row(”1:3″))
现在,了解一点ROW和INDIRECT函数可能会有所帮助。可以使用ROW函数创建连续的整数数组。例如,选择一个空输入,然后输入:
=ROW(1:10)
此公式创建由10个连续整数组成的一列。为了查看可能的问题,请在包含数组公式的区域上面插入一行(即第1行上)。Excel将调整行引用,公式现在将生成从2到11的整数。要修正该问题,可以向该公式添加INDIRECT函数:
=ROW(INDIRECT(“1:10”))
间接函数将文本字符串用作其参数(这就是区域1:10围绕引号括起的原因)。当插入行或移动数组公式时,Excel不会调整文本值。因此,此ROW函数总是生成所需的整数数组。您可以同样轻松地使用序列:
=SEQUENCE(10)
我们来看看以前使用的公式-=大型(B9#,ROW(”1:3″))-从内部括号开始,并向外工作:间接函数返回一组文本值,在此情况下,值为1到3。ROW函数反过来生成一个三个单元格的列数组。大型函数使用单元格区域B9:B18中的值,并对ROW函数返回的每个引用进行三次计算。如果要查找更多值,请向间接函数添加一个更大的单元格区域。最后,与小型示例一样,你可以将此公式与其他函数(如SUM和AVERAGE)一起使用。
当您尝试对包含错误值的区域求和时,Excel中的SUM函数不起作用,例如#VALUE!或#N/A。此示例演示如何对包含错误的名为数据的区域中的值求和:
该公式创建一个新数组,包含除错误值以外的原始值。从内层函数开始向外运算,ISERROR函数在单元格区域(数据)中搜索错误。IF函数在指定的条件计算结果为TRUE时返回指定值,在计算结果为FALSE时返回另一个值。在此处,它为所有错误值返回空字符串(“”),因为它们的计算结果为TRUE,并且返回该区域(数据)中的其他值(因为这些值计算结果为FALSE,表示它们不包含错误值)。接着SUM函数计算筛选出的数组的总和。
此示例与上一个公式类似,但它将返回一个名为数据的区域中的错误值的数目,而不是将它们筛选掉:
=SUM(IF(ISERROR(数据),1,0))
该公式创建一个数组,它为包含错误的单元格包含值1,为不包含错误的单元格包含值0。可以简化该公式,并达到相同的结果,方法是移除IF函数的第三个参数,如下所示:
=SUM(IF(ISERROR(数据),1))
如果未指定该参数,IF函数在单元格不包含错误值时返回FALSE。可以进一步简化该公式:
=SUM(IF(ISERROR(数据)*1))
此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。
例如,此数组公式仅对名为Sales的区域中的正整数进行求和,这表示上例中的单元格E9:E24:
=SUM(IF(Sales>0,Sales))
IF函数创建正值和false值的数组。SUM函数实际上将忽略false值,因为0+0=0。在此公式中使用的单元格区域可以由任意数量的行和列组成。
还可以对满足多个条件的值求和。例如,此数组公式计算大于0且小于2500的值:
=SUM((销售额>0)*(销售额<2500)*(销售))
请注意,如果区域中包含一个或多个非数字单元格,此公式将返回错误。
还可以创建使用OR条件的数组公式。例如,你可以将大于0或小于2500的值相加:
=SUM((销售额>0)+(销售额<2500),销售额)
不能在数组公式中直接使用AND和OR函数,因为这些函数返回单一结果,TRUE或FALSE,而数组函数需要结果数组。可以通过使用上一公式中显示的逻辑来解决这一问题。换句话说,对满足OR或AND条件的值执行数学运算,如加法或乘法运算。
本示例演示当您需要对区域中的值求平均值时,如何从该区域中移除零。下面的公式使用名为“销售量”的数据区域:
=AVERAGE(IF(Sales<>0,Sales))
IF函数创建不等于0的值数组,然后将这些值传递给AVERAGE函数。
=SUM(IF(我的数据=你的数据,0,1))
此公式创建与正比较的区域大小相同的新数组。IF函数使用值0和值1填充数组(0表示单元格不匹配,1表示单元格匹配)。然后SUM函数返回该数组中的值的和。
可以如下所示简化该公式:
=SUM(1*(我<>数据))
与计算区域中的错误值的公式相似,此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。
此数组公式返回名为“数据”的单列区域中的最大值所在的行号:
=MIN(IF(数据=MAX(数据),ROW(数据),””))
IF函数创建与名为“数据”的区域对应的新数组。如果对应的单元格包含区域中的最大值,则此数组包含该行号。否则,此数组包含空字符串(“”)。MIN函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。如果名为“数据”的区域包含完全相同的最大值,则该公式返回第一个值的行号。
如果要返回最大值的实际单元格地址,请使用下面的公式:
=ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),””)),COLUMN(数据))
您将在示例工作簿中的”数据集之间的差异”工作表中找到类似的示例。
复制下面的整个表格,并将其粘贴到空白工作表中的单元格A1中。
刘鹏
四门轿车
5
33000
双门轿车
4
37000
尹歌
四门轿车
6
24000
双门轿车
8
21000
林彩瑜
四门轿车
3
29000
双门轿车
1
31000
潘杰
四门轿车
9
24000
双门轿车
5
37000
施德福
四门轿车
6
33000
双门轿车
8
31000
公式(总计)
总计
‘=SUM(C2:C11*D2:D11)
=SUM(C2:C11*D2:D11)
按Ctrl+Shift+enter时,Excel会用大括号({})将公式括起来,并在所选区域的每个单元格中插入公式的一个实例。因为执行速度很快,所以你在E列中看到的是每位销售人员每种轿车类型的总销售额。如果你选择E2,然后选择E3、E4等,你将看到相同的公式{=C2:C11*D2:D11}。
在工作簿的单元格D13中,键入以下公式,然后按Ctrl+Shift+Enter:
=SUM(C2:C11*D2:D11)
在这种情况下,Excel会将数组中的值(单元格区域C2到D11)相乘,然后使用SUM函数将总计相加。结果等于$1,590,000的总销售额。本示例演示了此类公式的强大功能。例如,假定您有1,000行数据。您可以通过在单个单元格中创建数组公式来对部分或全部数据求和,而不是将公式向下拖动以包括所有1,000行来求和。
此外,请注意单元格D13中的单单元格公式完全独立于多单元格公式(单元格E2到E11中的公式)。这是使用数组公式的另一个优点 -灵活性。你可以更改列E中的公式或删除该列,而不会影响D13中的公式。
数组公式还具有以下优点:
数组函数可以是构建复杂公式的有效方式。数组公式=SUM(C2:C11*D2:D11)与=SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11)相同。
使用多单元格公式时,还需记住以下原则:
={1,2,3,4,5}
现在,你知道创建数组公式时需要按Ctrl+Shift+Enter。因为数组常量是数组公式的组成部分,可以通过手动输入一对大括号将常量括起来。然后使用Ctrl+Shift+enter输入整个公式。
如果使用逗号分隔各个项,将创建水平数组(一行)。如果使用分号分隔项,将创建垂直数组(一列)。若要创建二维数组,应在每行中使用逗号分隔项,并使用分号分隔每行。
下面是一行中的数组:{1,2,3,4}。下面是单列中的数组:{1;2;3;4}。还有一个两行四列的数组:{1,2,3,4;5,6,7,8}。在两个行数组中,第一行是1、2、3和4,第二行是5、6、7和8。单个分号在4和5之间分隔两行。
使用数组公式时,可以将数组常量用于Excel提供的大部分内置函数中。下面几节将解释如何创建各种类型的常量以及如何将这些常量用于Excel中的函数。
创建水平常量
={1,2,3,4,5}
在这种情况下,应键入左括号和右大括号({}),Excel将为你添加第二个集。
将显示以下结果。
创建垂直常量
={1;2;3;4;5}
将显示以下结果。
创建二维常量
={1,2,3,4;5,6,7,8;9,10,11,12}
将得到以下结果:
在公式中使用常量
下面是一个使用常量的简单示例:
=SUM(A1:E1*{1,2,3,4,5})
请注意,Excel用另一对大括号将常量括起来,这是因为您是以数组公式的形式输入该常量。
单元格A3中显示值85。
下节将讨论此公式的计算方法。
1.函数
2.存储数组
3.运算符
4.数组常量
括号内的最后一个元素是数组常量:{1,2,3,4,5}。请注意,Excel不会用大括号将数组常量括起来,您必须自己添加大括号。另请注意,在将常量添加到数组公式后,按Ctrl+Shift+enter可输入公式。
因为Excel首先对括号括起来的表达式执行运算,接下来参与运算的两个元素是存储在工作簿(A1:E1)中的值以及运算符。此时,公式将存储数组中的值与常量中对应的值相乘。它等价于:
=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)
最后,SUM函数将这些值相加,总和85显示在单元格A3中:
要避免使用存储数组并让运算完全位于内存中,可用另一个数组常量来替换存储数组:
=SUM({3,4,5,6,7}*{1,2,3,4,5})
若要尝试此操作,请复制函数,选择工作簿中的空白单元格,将公式粘贴到编辑栏中,然后按Ctrl+Shift+Enter。将得到与上述练习中使用以下数组公式相同的结果:
=SUM(A1:E1*{1,2,3,4,5})
数组常量不能包含其他数组、公式或函数。换言之,它们只能包含以逗号或分号分隔的文本或数字。当您输入如下所示的公式时,Excel将显示警告消息:{1,2,A1:D4}或{1,2,SUM(Q2:Z8)}。另外,数值不能包含百分号、货币符号、逗号或圆括号。
={“一月”,”二月”,”三月”}
对话框中的内容现在类似于以下内容:
=第1季度
将显示以下结果。
将命名常量用作数组公式时,切记要输入等号。如果没有输入等号,Excel将数组解释为文本字符串,并且公式不会按预期工作。最后,请记住可以使用文本和数字的组合。
乘以数组中的各项
={1,2,3,4;5,6,7,8;9,10,11,12}*2
对数组中的各项求平方
={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}
或者,输入下面的数组公式,它使用脱字符号(^):
={1,2,3,4;5,6,7,8;9,10,11,12}^2
转置一维行
=TRANSPOSE({1,2,3,4,5})
即使输入的是水平数组常量,TRANSPOSE函数也会将该数组常量转换为列。
转置一维列
=TRANSPOSE({1;2;3;4;5})
即使输入的是垂直数组常量,TRANSPOSE函数也会将该常量转换为行。
转置二维常量
=TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})
TRANSPOSE函数将各行转换为一系列的列。
从现有值创建数组和数组常量
下面的示例介绍如何使用数组公式在不同工作表的单元格区域之间创建链接。还演示如何使用同一组值创建数组常量。
从现有值创建数组
={10,20,30;40,50,60;70,80,90}
确保在输入10之前输入{(左大括号),在输入90之后输入}(右大括号),因为你要创建数字数组。
40
50
60
70
80
90
=C8:E10
通过3×3单元格数组,单元格C1到E3中显示的值与在C8到E10中看到的值相同。
从现有值创建数组常量
在单元格区域中对字符计数
下面的示例演示如何计算单元格区域中的字符数(包括空格)。
这是
很多单元格
组合
构成了
一个句子。
A2:A6中的总字符
=SUM(LEN(A2:A6))
最长的单元格内容(A3)
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)
在单元格A8中使用以下公式计算单元格区域A2到A6中的字符总数(66)。
=SUM(LEN(A2:A6))
这样,LEN函数返回该区域的每个单元格中的每个文本字符串的长度。然后,SUM函数将这些值相加并显示结果(66)。
查找出区域内的n个最小值
本示例演示如何查找单元格区域内的三个最小值。
=SMALL(A1:A11,{1;2;3})
此公式使用数组常量来计算小函数三次,并返回单元格A1:A10中包含的数组中的最小值(1)、第2位和第三个最小值(3)的成员。要查找更多值,请将更多参数添加到持续.还可以对此公式使用其他函数,例如SUM或AVERAGE。例如:
=SUM(SMALL(A1:A10,{1,2,3})
=AVERAGE(SMALL(A1:A10,{1,2,3})
查找出区域中的n个最大值
要找出区域中的多个最大值,可以使用LARGE函数替代SMALL函数。此外,下面的示例使用ROW和INDIRECT函数。
=大(A1:A10,ROW(”1:3″))
现在,了解一点ROW和INDIRECT函数可能会有所帮助。可以使用ROW函数创建连续的整数数组。例如,在练习工作簿中选择一个空列10个单元格,输入此数组公式,然后按Ctrl+Shift+enter:
=ROW(1:10)
此公式创建由10个连续整数组成的一列。为了查看可能的问题,请在包含数组公式的区域上面插入一行(即第1行上)。Excel调整行引用,并且此公式生成从2到11的整数。要修正该问题,可以向该公式添加INDIRECT函数:
=ROW(INDIRECT(“1:10”))
INDIRECT函数使用文本字符串作为参数(这是区域1:10由双引号括起的原因)。当插入行或移动数组公式时,Excel不会调整文本值。因此,此ROW函数总是生成所需的整数数组。
让我们看看以前使用过的公式-=大(A5:A14,ROW(”1:3″))-从内部括号开始,并向外工作:间接函数返回一组文本值,在这种情况下,值为1到3。ROW函数反过来生成一个包含三个单元格的纵栏数组。大型函数使用单元格区域A5:A14中的值,并对ROW函数返回的每个引用进行三次计算。值3200、2700和2000将返回到3个单元格的纵栏数组。如果要查找更多值,请向间接函数添加一个更大的单元格区域。
与前面的示例一样,你可以将此公式与其他函数(如SUM和AVERAGE)一起使用。
查找单元格区域中的最长文本字符串
返回到前面的文本字符串示例,在空单元格中输入以下公式,然后按Ctrl+Shift+enter:
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)
显示的文本”单元格组”。
让我们从内部元素开始,由内而外深入了解此公式。LEN函数返回单元格区域A2:A6中每个项目的长度。MAX函数计算这些项目中的最大值,这两个值对应于单元格A3中最长的文本字符串。
下面的计算稍微有点复杂。MATCH函数计算包含最长文本字符串的单元格的偏移量(相对位置)。为此,需要三个参数:分别是查阅值、查阅数组和匹配类型。MATCH函数在查阅数组中搜索指定的查阅值。在这种情况下,查阅值为最长的文本字符串:
(MAX(A2:A6))
并且该字符串位于此数组中:
LEN(A2:A6)
匹配类型参数为0。匹配类型可以包含值1、0或-1。如果指定1,MATCH返回小于或等于查阅值的最大值。如果指定0,MATCH返回正好等于查阅值的第一个值。如果指定-1,MATCH查找出大于或等于指定查阅值的最小值。如果未指定匹配类型,Excel会采用值1。
最后,INDEX函数采用这些参数:数组以及该数组内的行号和列号。单元格区域A2:A6提供数组,MATCH函数提供单元格地址,最后一个参数(1)指定值来自数组中的第一列。
对包含错误值的区域求和
当您尝试对包含错误值(例如#N/A)的区域求和时,Excel中的SUM函数不起作用。本示例显示如何对包含错误的命名为“数据”的区域中的值求和。
=SUM(IF(ISERROR(数据),””,数据))
该公式创建一个新数组,包含除错误值以外的原始值。从内层函数开始向外运算,ISERROR函数在单元格区域(数据)中搜索错误。IF函数在指定的条件计算结果为TRUE时返回指定值,在计算结果为FALSE时返回另一个值。在此处,它为所有错误值返回空字符串(“”),因为它们的计算结果为TRUE,并且返回该区域(数据)中的其他值(因为这些值计算结果为FALSE,表示它们不包含错误值)。接着SUM函数计算筛选出的数组的总和。
计算区域中错误值个数
本示例与上面的公式相似,但它返回名为“数据”的区域中的错误值个数,而不是将错误值筛选掉:
=SUM(IF(ISERROR(数据),1,0))
该公式创建一个数组,它为包含错误的单元格包含值1,为不包含错误的单元格包含值0。可以简化该公式,并达到相同的结果,方法是移除IF函数的第三个参数,如下所示:
=SUM(IF(ISERROR(数据),1))
如果未指定该参数,IF函数在单元格不包含错误值时返回FALSE。可以进一步简化该公式:
=SUM(IF(ISERROR(数据)*1))
此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。
条件求和
可能需要根据条件对值求和。例如,此数组公式仅对名为“销售量”的区域中的正值求和:
=SUM(IF(Sales>0,Sales))
IF函数创建正值和false值数组。SUM函数实际上将忽略false值,因为0+0=0。在此公式中使用的单元格区域可以由任意数量的行和列组成。
还可以对满足多个条件的值求和。例如,下面的数组公式计算大于0并且小于等于5的值:
=SUM((Sales>0)*(Sales<=5)*(Sales))
请注意,如果区域中包含一个或多个非数字单元格,此公式将返回错误。
还可以创建使用OR条件的数组公式。例如,可以对小于5和大于15的值求和:
=SUM(IF((Sales<5)+(Sales>15),Sales))
IF函数查找所有小于5和大于15的值,然后将这些值传递给SUM函数。
不能在数组公式中直接使用AND和OR函数,因为这些函数返回单一结果,TRUE或FALSE,而数组函数需要结果数组。可以通过使用上一公式中显示的逻辑来解决这一问题。也就是,对满足OR或AND条件的值执行加法或乘法等算术运算。
计算零以外的平均值
本示例演示当您需要对区域中的值求平均值时,如何从该区域中移除零。下面的公式使用名为“销售量”的数据区域:
=AVERAGE(IF(Sales<>0,Sales))
IF函数创建不等于0的值数组,然后将这些值传递给AVERAGE函数。
计算两个单元格区域中的不同值个数
此数组公式对名为“我的数据”和“您的数据”的两个单元格区域中的值进行比较并返回它们之间不同值的个数。如果这两个区域中的内容完全相同,此公式将返回0。要使用此公式,单元格区域的大小必须相同,其维度也必须相同(例如,如果MyData是一个3行5列区域,YourData也必须是3行5列):
=SUM(IF(我的数据=你的数据,0,1))
此公式创建与正比较的区域大小相同的新数组。IF函数使用值0和值1填充数组(0表示单元格不匹配,1表示单元格匹配)。然后SUM函数返回该数组中的值的和。
可以如下所示简化该公式:
=SUM(1*(我<>数据))
与计算区域中的错误值的公式相似,此公式版本可以执行计算是因为TRUE*1=1并且FALSE*1=0。
查找区域中最大值的位置
此数组公式返回名为“数据”的单列区域中的最大值所在的行号:
=MIN(IF(数据=MAX(数据),ROW(数据),””))
IF函数创建与名为“数据”的区域对应的新数组。如果对应的单元格包含区域中的最大值,则此数组包含该行号。否则,此数组包含空字符串(“”)。MIN函数使用此新数组作为它的第二个参数并且返回与“数据”区域中最大值的行号相对应的最小值。如果名为“数据”的区域包含完全相同的最大值,则该公式返回第一个值的行号。
如果要返回最大值的实际单元格地址,请使用下面的公式:
=ADDRESS(MIN(IF(数据=MAX(数据),ROW(数据),””)),COLUMN(数据))
声明本文的部分内容基于由ColinWilcox编写的一系列ExcelPowerUser列,并与Excel2002公式的第14和第15章(由JohnWalkenbach编写)是一个以前的ExcelMVP。
需要更多帮助吗?可随时在Excel技术社区中咨询专家,在解答社区获得支持,或在ExcelUserVoice上建议新功能或功能改进。
另请参阅动态数组和溢出数组行为
动态数组公式与旧CSE数组公式对比
FILTER函数
RANDARRAY函数
SEQUENCE函数
SORT函数
SORTBY函数
UNIQUE函数
Excel中的#SPILL!错误
绝对交集运算符:@
公式概述
注意: 本页面是自动翻译的,可能包含语法错误或不准确之处。我们的目的是使此内容能对你有所帮助。能否告知我们此信息是否有所帮助?下面是该参考内容的英文版。
相关文章
我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
在工作中,可能许多朋友都会碰到一个情况,那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中,许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
今天在这里为你分享5个Excel文本函数,这些拆分和组合函数,你一定会用上的。①LEFT函数公式:=LEFT(A2,1)在Excel表格中,需要想要拆分汉字,想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
相信大家也和我一样,才开始看到Excel可以当做翻译软件的时候会很好奇,这究竟是怎样做到的?其实,这个方法并不是很难,它是由一个函数公式而制作出来的,好了,首先我们一起来看看成......
2023-01-08
函数可以说是所用快捷方法中最为简单的一种方法,为什么很多人认为函数用起来很难了?主要是因为它拥有很长的函数公式,记不住。其实不管是学Excel函数,还是学习其他的一些快捷方法......
2023-01-08