本文目录一览：

Excel里面数组函数应用技巧

这是一位网友提给我的问题，说给大家看看；

工作簿内一共有两个工作表Sheet1和Sheet2，先说Sheei1，如下

　AB　C

货号 序号　名称

101　1　车背带

101　2　合前片

101　3　合后片

101　4　车手带

101　5　车边片

101　6　合前袋

101　7　车后手带

202　1　车前片链

202　2　车前袋

202　3　合包

202　4　车后片

202　5　车手垫

202　6　合前袋

表Sheet2如下：

ABC

货号　序号名称

101　5　[此单元格空]

101　2　[此单元格空]

101　3　[此单元格空]

101　7　[此单元格空]

202　3　[此单元格空]

202　1　[此单元格空]

要求根据Sheet2内容，查找Sheet1，在Sheet2的名称列填写入正确的内容。

题目分析：

根据要求，如果要在Sheet2某一行n填入正确的名称，首先需要在Sheet1找到这样的行：该行第一列内容等于Sheet2某行n第一列，该行第二列内容等于Sheet2某行n第二列。

因为涉及到数据的查找，拟采用数组公式实现。

1、首先定义名称：

（定义名称使用菜单：插入-名称）

dataA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

dataB=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

这样做的好处是在于无论Sheet1添加了多少行，dataA始终包括A列有内容的行，这样比起直接使用A:A整列，要节约很多的计算时间。dataB同理。

2、匹配条件

先匹配A列，选中Sheet2！D2:D13（因为原始数据从2~13行），在编辑栏输入

=IF(dataA=A2,1,0)

用Ctrl+Shift+回车，这样可以看到凡是与Sheet2！A2匹配的Sheet1的行，在这里都变成了1，而不匹配的则是0。

同理，在Sheet2！E2:E13输入

=IF(dataB=B2,1,0)

这样我们在D、E两列就得到了分别与Sheet2!A和Sheet2!B列匹配的数组。

3、合并匹配条件

我们需要的是两个条件的与运算，因此使用乘法是最好的，只有两个条件同时成立，1*1=1，否则两个乘数里面至少有1个为0，结果为0。

well，在Sheet2!F2:F13中输入数组公式：

=IF((dataA=A2)*(dataB=B2),1,0)

如此一来，就得到了一个新的由1和0组成的数组，某元素对应Sheet1的行如果能够匹配，该元素为1，否则为0。

4、检索行号

有了这个数组，我们需要得到匹配出来的行号，因为数组是由若干0和一个1组成的，所以我们只需要使用MATCH函数，就可以得到与Sheet2!An匹配的Sheet1的行号。

在Sheet2!G1输入

=MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）

注意此处虽然仅仅是在一个单元格里面输入公式，但仍然是一个数组公式，需要使用Ctrl+Shift+Enter。

结果就是Sheet1中匹配的行号。

5、检索结果

有了行号，检索结果就很容易了，我们使用INDIRECT函数。

在Sheet2!C2输入数组公式：

=INDIRECT("Sheet1!$C$" MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）)

注意依然使用Ctrl+Shift+Enter，正确的结果就出现了。

对于下面的行，只需要拖动填充句柄复制公式就可以了。

另外别忘了删除掉用来演示的D、E、F、G列的已经不再需要的公式。

在Excel中如何使用数组公式？

01

数组公式用于区间计算非常多，经常会配合函数使用，比如说我们要计算多个产品的总金额，按常规计算，得先计算出单个产品金额，再进行汇总求和。

02

如果用数组公式配合公式使用那就能一步到位了，首先输入公式=sum(b2:b16*c2:c16),如果像普通公式一样直接回车，返回的结果是错误的。

03

数组公式结束需要同时按CTRL+SHIFT+ENTER结束，结束后会自动在公式的外边生成一个花括号，返回正确的总金额，是不是很快捷高效呢？

Excel中的数组函数怎么用?什么是数组函数?

1、EXCEL数组函数是用于建立可产生多个结果或可对存放在行和列中的一组参数进行运算的单个公式。

2、Excel数组用法：区域数组和常量数组。区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式；常量数组将一组给定的常量用作某个公式中的参数。

3、举例说明：

数组： ={1,2;3,4}*2扩充后的公式就会变为={1,2;3,4}*{2,2;2,2}，则相应的计算结果为“2,4,6,8”。

数值：=SUM(1,2,3,4)，它不是一个数组，是一个简单的求和公式，结果为数值10。

扩展资料：

1、Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。

2、数组公式的特点就是所引用的参数是数组参数，包括区域数组和常量数组。执行多重计算,它返回的是一组数据结果。

3、输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl+Shift+Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。

参考资料：百度百科_数组公式   百度百科_excel函数

如何在Excel中使用数组公式

在Excel中，公式是以等号或“+”号开头，由常数、函数、单元格引用或运算符组成的式子，并将计算结果显示在相应的单元格中。在公式中可以使用的运算符有算术运算符、比较运算符、字符连接运算符（）。 前面介绍的公式都是只执行一个简单计算且返回一个计算结果的情况，如果需要同时对一组或两组以上的数据进行计算，计算的结果可能是一个，也可能是多个，这种情况只有数组公式才能处理。 数组公式可以对两组或两组以上的数据（两个或两个以上的单元格区域）同时进行计算。在数组公式中使用的数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量（经过特殊组织的常量表）。 3.2.1 数组公式的建立方法 如果需要建立数组公式进行批量数据的处理，其操作步骤如下： 如果希望数组公式返回一个结果，可以先选中保存计算结果的单元格；如果数组公式返回多个结果，则需先选中要保存数组公式计算结果的单元格区域。 输入公式的内容。 公式输入完成后，按下Ctrl+Shift+Enter组合键。 下面介绍数组公式的几个简单应用。 1．用数组公式计算两个数据区域的乘积 当需要计算两个相同矩形区域对应单元格的数据之积时，可以用数组公式一次性计算出所有的乘积值，并保存在另一个大小相同的矩形区域中。 某公司出售各种机械产品，已知各种产品的单价以及销售数量，计算各种产品的销售总额。其操作步骤如下： 输入除销售总额之外的其余数据。 选中单元格区域F2:F11。 输入公式“=D2:D11*E2:E11”。 按下Ctrl+Shift+Enter组合键，得到如图3-5所示的结果。 图 3-5 2．用数组公式计算多列数据之和 如果需要把多个对应列或行的数据相加，并得出对应的和值所组成的一列或一行数据时，可以用一个数组公式完成。某班级期末考试成绩表中有3科成绩，现要计算各科成绩的总分以及各科成绩的综合测评成绩的综合测评分数，总分的计算方式为各科成绩的总和，综合测评分的计算方式为“（高等数学+工商管理）*0.8+体育*0.2”。使用数组公式计算各同学的总分和综合测评成绩的操作步骤如下： 在表中输入原始成绩——各科成绩，然后选中总分一列中的单元格区域F2:F17。 输入数组公式“=C2:C17+D2:D17+E2:E17”，按下Ctrl+Shift+Enter组合键，得到总分。 选择综合测评一列中的单元格区域G2:G17。 输入数组公式“=(C2:C17+D2:D17)*0.8+E2:E17*0.2”，按下Ctrl+Shift+Enter组合键，得到综合测评成绩，如图3-6所示。 图 3-6 3.2.2 使用数组公式的规则 输入数组公式时，首先选择用来保存计算结果的单元格区域，如果计算公式将产生多个计算结果，必须选择一个与计算结果所需大小和形状都相同的单元格区域。 数组公式输入完成后，按下Ctrl+Shift+Enter组合键，这时在公式编辑栏中可以看见公式的两边加上了花括号，表示该公式是一个数组公式。 在数组公式所涉及的区域中，不能编辑、清除或移动单个单元格，也不能插入或删除其中任何一个单元格，也就是说，数组公式所涉及的单元格区域只能作为一个整体进行操作。 也可单击数组公式所包含的任一单元格，这时数组公式会出现在编辑栏中，它的两边有花括号，单击编辑栏中的数组公式，它两边的花括号就会消失。 要编辑或清除数组，需要选择整个数组并激活编辑栏，然后在编辑栏中修改数组公式或删除数组公式，操作完成后，按下Ctrl+Shift+Enter组合键即可。 要把数组公式移到另一个位置，需要先选中整个数组公式所包括的范围，然后把整个区域拖放到目标位置，也可通过【编辑】菜单中的【剪切】和【粘贴】菜单项进行。 3.2.3 数组扩展 在公式中用数组作为参数时，所有的数组必须是同维的。如果数组参数或数组区域的维数不匹配，Excel会自动扩展该参数。 如假设单元格区域A1:A8的数据分别为1，2，3，4，5，6，7，8，现要将这8个数分别乘以100，把结果放在单元格区域B1:B8中，则可以在单元格区域B1:B8中输入数组公式“=A1:A8*10”，如图3-7所示。这个公式并不平衡，乘号“*”的左边有10个参数，而乘号“*”的右边只有一个参数，对于这种情况，Excel将扩展第2个参数，使之与第1个参数的个数相同。经过Excel内部处理之后，上述公式实际上就变成了“=A1:A8*{10，10，10，10，10，10，10，10，10，10}”。 数组公式的这种扩展在某些时候特别有用，如图3-6中的学生综合测评分的计算就是使用这种方法。 图 3-7 3.2.4 二维数组 前面对于数组的讨论基本上都局限于单行或单列，在实际应用中，往往会涉及多行或多列的数据处理，这就是所谓的二维数组。Excel支持二维数组的各种运算，如加、减、乘、除等。合理地应用二维数组运算，能够提高数据处理的能力，特别是在不同工作表之间进行数据汇总时非常有效。 某音像制品店要统计2005年11月份和2005年12月份各类音像制品的销售量，并由此计算出各类图书的总销售额。如图3-8和图3-9所示为该音像制品店11月份和12月份各类音像制品的销量情况表。 图 3-8 图 3-9 现在计算该音像店11月份和12月份各类音像制品的销量汇总，各项数据可以通过一个二维数组公式一次性计算出来。其操作步骤如下： 在汇总表中选择单元格区域B3:D7。 输入公式“='11月份'!B3:D7+'12月份'!B3:D7”。 按下Ctrl+Shift+Enter组合键得到如图3-10所示的结果。 图 3-10

Excel表格中数组公式有哪些使用方法

Excel数组公式可实现对多个数据的计算操作，从而避免了逐个计算所带来的繁琐工具，使计算效果得到大幅度提高。以下是我为您带来的关于Excel数组公式使用方法，希望对您有所帮助。

Excel数组公式使用方法

正确使用数组公式的前提

要想正确使用数组公式，则需要首先选中将要生成的结果单元格区域，且该区域与整个计算结果所得区域相同或等价。

数组公式采用一对花括号做为标记，因此在输入完公式之后，只有在同时按下“Ctrl+Shift+Enter”组合键之后才能正常生成数组公式。

数组公式只有与其它函数相配合才能发挥出其强大的优势，在使用数组公式的过程中，要正确的使用相对单元格引用和绝对单元格引用，这样才能确保计算结果的正确性。

方法/步骤

1.对于如图所示的表格，如果我们想计算A*B，则首先选中“C1”至“C7”单元格，然后在“编辑栏”中输入“=A2：A7*B2：B7”。

2.接着同时按下“Ctrl+Shift+Enter”组合键，就会发现数组公式产生了结果，并且“编辑栏”中的公式被一对花括号所包围。

3.将鼠标放置在编辑栏中，按键盘上的“F9”，就会发现数组公式的结果被计算出来啦。

4.接下来我们实现一些更复杂的操作，进行区域数据的计算。如图所示的两个区域，如果想生成“九九乘法”表，则选择“9*9”的结果生成区域，然后输入公式“=A2:A10*D1:L1”。

5.接着同时按下“Ctrl+Shift+Enter”组合键，使可得结果。

数组公式与函数的配合：

如图，如果我们想要计算产品总金额，就可以在C8单元格中输入公式“”并同时按下“Ctrl+Shift+Enter”组合键来生成结果。

猜你喜欢：

1. excel正弦公式的使用教程

2. excel次方公式使用教程(2种)

3. 18个Excel最常用的公式运算技巧总结

4. 手把手教你用“Excel表格自动求积公式”

5. excel函数公式大全使用教程

应用技巧二十一：数组公式

对于希望精通Excel函数与公式的用户来说，数组运算和数组公式是必须跨越的门槛。通过本文的介绍，让用户能够对数组公式和数组运算有更深刻地理解，并能够利用数组公式来解决实际工作中的一些疑难问题。

一、理解数组

1. Excel中数组的相关定义

在Excel函数与公式应用中，数组是指按一行、一列或多行多列排列的一组数据元素的集合。数据元素可以是数值、文本、日期、逻辑值和错误值。

数组的维度是指数组的行列方向，一行多列的数组为横向数组，一列多行的数组为纵向数组。多行多列的数组则同时拥有纵向和横向两个维度。

数组的维数是指数组中不同维度的个数。只有一行或一列在单一方向上延伸的数组，成为一维数组；多行多列同时拥有两个维度的数组成为二维数组。

数组的尺寸是以数组各行列上的元素个数来表示的。一行N列的一位横向数组，其尺寸表示为1*N；一列N行的一维纵向数组，其尺寸表示为N*1；对于M行N列的二维数组，其各行或各列的元素个数必须相等，呈矩形排列，其尺寸表示为M*N。

2. Excel中数组的存在方式

（1）常量数组

在Excel函数与公式应用中，常量数组是指直接在公式中写入数组元素，并用大括号{}在首尾进行识别的文字串表达式。其不依赖单元格区域，可直接参与公式的计算。

顾名思义，常量数组的组成元素只可为常量元素，决不能是函数、公式或单元格引用。常量元素中不可以包含美元符号、逗号、圆括号和百分号。

一维纵向常量数组（通常称为“行数组”）的各元素用半角分号“;”间隔，如下式，表示尺寸为5行*1列的数值型常量数组：

={1;2;3;4;5}

一维横向量数组（通常称为“列数组”）的各元素用半角逗号“,”间隔，如下式，表示尺寸为1行*3列的文本型常量数组：

={“张三”,”李四”,”王五”}

文本型常量元素必须用半角双引号“””将首尾标识起来。

二维常量数组的每一行上的元素用半角逗号“,”间隔，每一列上的元素用半角分号“;”间隔。如下式，表示尺寸为4行*3列的二维混合数据类型的数组，包含数值、文本、日期、逻辑值和错误值。

={1,2,3:#N/A,5,TRUE;”田径”,”2008-8-8”,”股市”;#VALUE!,FALSE,12}

提示：如果用户在手工输入数组的过程中感觉非常繁琐，可以借助单元格引用来转换为常量数组。

例如当用户在单元格A1:A7中分别输入“A到G”的字符后，再在B1中输入：=A1:A7，并选中公式段中的A1:A7，同时按下键，Excel会自动将单元格引用转换为常量数组。

（2）区域数组

如果在公式或函数参数中引用工作表的摸个单元格区域，且其中函数参数不是单元格引用或区域类型（reference、ref或range），也不是向量（vector）时，Excel会自动将该区域引用转换成由区域中各单元格的值构成的同维数同尺寸的数组，可称之为区域数组。

区域数组的维度和尺寸与常量数组完全一致，而在公式运算中会自动将“区域引用”进行转换，这类区域数组也是用户在利用“公式求值”查看公式运算过程时常看到的。

（3）内存数组

内存数组是指某一公式通过计算，在内存中临时返回多个结果值构成的数组。而该公式的计算结果，不必存储到单元格区域中，便可作为一个整体直接嵌套入其他公式中继续参与计算。该公式本身则称之为内存数组公式。

内存数组与区域数组的主要区别在于，区域数组通过引用而非通过公式计算获得，但其不是通过公式计算在内存中临时获取的，而是作为常量直接输入的。

可以用一句话概括内存数组的特点，即内存数组生于内存，存于内存。

（4）命名数组

命名数组是指，使用命名公式（即名称）定义的一个常量数组、区域数组或内存数组。该名称可在公式中作为数组来调用。在数据有效性（有效性序列除外）和条件格式的自定义公式中，不接受常量数组，但可将其命名后，直接调用名称进行运算。

二、数组公式与数组运算

（1）认识数组公式

简单地说，数组公式是指区别与普通公式，并以按下组合键来完成编辑的特殊公式。作为标识，Excel会自动在编辑栏中给数组公式的首尾加上大括号“{}”。数组公式的实质是单元格公式的一种书写形式，用来显式地通知Excel计算引擎对其执行多项计算。

所谓的多项计算是指，对公式中有对应关系的数组元素同步执行相关计算，或在工作表的相应单元格区域站宏同时返回常量数组、区域数组、内存数组或命名数组中的多个元素。

但是，并非所有执行多项计算的公式，都必须以数组公式的输入方式来完成编辑。一些函数在其array数组类型或vector向量类型的参数中使用数组，并返回单一结果值时，Excel不需要获得通知就可以直接对其执行多项计算。例如，Excel

2010中SUMPRODUCT、LOOKUP、MMULT以及新增的MODE.MULT函数。

（2）多单元格联合数组公式

如果一个函数或公式返回多个结果值，并需要存在单元格区域中，那么额借助多单元格数组公式来实现。

例 1 ： 多单元格数组公式计算销售额

如图1所示，选择G3:G11单元格区域后，输入如下数组公式后，并按下结束编辑：{=E3:E11*F3:F11}（注：输入公式时不包括外层大括号）

此公式将各种商品的销售数量分别乘以各自的单价，获得一个内存数组{44;200;36;112;125;54;28;60;21}，将其写入指定的G3:G11单元格区域中显示出来（在本例中生成的内存数组与写入的单元格区域尺寸完全一致）。

本示例只是为了说明数组公式的用法，本应用其实相当于选择G3:G11单元格区域后，在G3单元格中使用相对引用输入如下普通公式，并按下结束编辑：

=E3*F3

多单元格数组公式在每个单元格中显示相同的公式，并按一个公式执行计算，可提高运算效率。

在某些时候，用户编写公式的运算结果无法生成内存数组，但后续的公式有需要对结果进行再处理，也可以考虑使用多单元格公式来协助处理。

例 2 ： 利用多单元格数组公式计算最小值

如图2所示，列举了3列随机数值，下面的公式将分别从各列中取得最小值，并写入指定的单元格中，可以输入多单元格数组公式如下。

例如在E2:G2单元格中输入多单元格联合数组公式如下：

{=MIN(INDEX($A$3:$C$10,,{1,2,3}))}

由于INDEX函数地2、3个参数都不支持数组元素来生成内存数组，因此该公式的结果只能放置于多单元格中才能显示。

当然，如果该示例需要生成内存数组，可以使用以下两个公式：

公式1：{=CHOOSE({1,2,3},MIN($A$3:$A$10),MIN($B$3:$B$10),MIN($C$3:$C$10))}

公式2：{=SUBTOTAL(5,OFFSET($A$3:$A$10,,{0,1,2}))}

注意：数组公式首尾的大括号{}是由组合键自动生成，千万不要试图手工输入，否则Excel只能识别其为文本字符，而无法被当成公式正确地运算。

（3）单个单元格数组公式

例 3 ：单个单元格数组公式

同样沿用例1的饮品销售数据，下面可以使用一个公式来完成对所有饮品总销售利润进行统计，如图3所示。

本例中G13单元格的数组公式如下：

{=SUM(E3:E11*F3:F11)*G1}

该公式先在内存中执行计算，将各商品的销量和单价分别相乘，然后再将数组中的所有元素用SUM函数汇总，得到总销售额，最后再乘以G1单元格的利润率，即可得出最终结果。

由于SUM函数的参数不能直接支持数组，所以在输入该公式时，必须以数组公式的形式输入，来通知Excel执行多重计算。这样就可以不借助任何辅助单元格，直接完成计算，并返回结果值。

本例中的公式还可用SUMPRODUCT函数来替代：

=SUMPRODUCT(E3:E11*F3:F11)*G1

SUMPRODUCT函数的所有参数都是array数组类型参数，直接支持多项计算，因此该公式不需要以数组公式的形式输入公式，也能够正常返回结果。

（4）数组公式的编辑

与Excel

2003一样，在Excel 2010中同样对多单元格数组公式有如下限制。

a. 不能单独改变公式区域某一部分单元格的内容；

b. 不能单独移动公式区域的某一部分单元格；

c. 不能单独删除公式区域的某一部分单元格；

d. 不能在公式区域插入新的单元格。

如果需要修改多单元格数组公式，操作步骤如下。

步骤 1 选择公共区域，按F2键进入编辑模式。

步骤 2 修改公式内容后，再次按下组合键结束编辑。

如果希望删除原有的多单元格数组公式，操作步骤如下。

步骤 1 选择任意一个多单元格数组公式单元格，按F2进入编辑状态。

步骤 2 删除该单元格公式内容后，再次按下组合键结束编辑。

另外，读者还可以先按下组合键，选择多单元数组公式后，再按下键进行删除。

��8����