查询引用中的黄金搭档，就是Index+Match，还不了解的速度围观

内容导航：

查询引用中的黄金搭档，就是Index+Match，还不了解的速度围观

excel函数中vlookup与index+match有何不同？

Excel关于INDEX和MATCH配合取数的问题和Excel表的更新-Excel学习网

excel函数中vlookup与index+match有何不同？

一、查询引用中的黄金搭档，就是Index+Match，还不了解的速度围观

查询引用，不是Lookup或vlookup的专职工作吗？是的，确实如此，除了Lookup、Vlookup外，还有Hlookup以及Xlookup等；但除此之外，还有一组黄金搭档，那就是Index+Match；要掌握这对黄金搭档，首先要了解Index函数和Match函数本身的作用。

一、Index。

功能：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。

从功能中就可以看出（返回特定行列交叉处单元格的值或引用），此函数具有两种引用形式：

（一）数组形式：=Index(单元格区域或数组常量,行,[列])。

参数解读：

1、单元格区域或数组常量：必需。

如果数组仅包含一行或一列，则对应的参数“行”或“列”是可选的。

2、行：必须。

选择数组中的某行，函数从该行返回值。

3、列：可选。

选择数组中的某列，函数从该列返回值。

备注：

参数“行”和“列”必须指向数组中的单元格，否则Index将返回#REF！错误。

案例：

目的：返回数据表中“司马懿”的“月薪”。

方法：

在目标单元格中输入公式：=INDEX(C3:H12,5,6)或=INDEX(H3:H12,5)。

解读：

1、公式：=INDEX(C3:H12,5,6)中，数组不是单行或单列，所以需要同时指定参数“行”和参数“列”。

2、公式：=INDEX(H3:H12,5)中，只有一列，所以只需指定“行”即可。

（二）引用形式：=Index(单元格区域,行,[列],[区域顺序])。

参数解读：

1、单元格区域：必需。

（1）此处的单元格区域可以是1个，也可以是多个。

（2）如果引用的区域是非邻的，必需用括号()括住。

（3）如果引用的每个区域仅包含1行或1列，则对应的参数“行”、“列”是可选的。

2、行：必需。

引用中某行的行号，函数从该行返回引用。

3、列：可选。

引用中某列的列标，函数从该列返回一个引用。

4、区域顺序：可选。

（1）指定“单元格区域”中被引用的区域，从该范围中按参数“行”、“列”的值返回指定的引用。

（2）选定或输入的第一个区域编号为1，第二个为2，以此类推。

（3）缺省该参数的情况下，Index默认该值为1。

案例：

目的：返回数据表中“财务部”第2行第6列的值。

方法：

在目标单元格中输入公式：=INDEX((C3:H5,C6:H12),2,6,2)。

解读：

公式中的最后一个参数2指定的是第2个数据区域，即C6:H12；从C6:H12中提取第2行第6列较差处的值。

二、Match。

功能：返回符合特定值顺序的项在数值中的相对位置。

语法结构：=Match(定位值,数据范围,[匹配模式])；“匹配模式”分为-1、0、1三种，分别为：“小于”、“精准匹配”、“大于”。

1或省略：Match查找小于或等于“定位置”的最大值。“数据范围”中的值必须以升序排序。

0：Match查找完全等于“定位值”的第一个值。

-1：Match查找大于或等于“定位置”的最小值。“数据范围”中的值必须以降序排序。

备注：

1、Match函数返回的是“定位值”在“数据范围”中的相对位置，而非其值本身。

2、匹配文本时，Match函数不区分大小写字母。

3、如果查询不到“定位值”，Match函数将返回：#N/A!。

4、如果“匹配模式”为0且“定位值”为文本字符串，则可以在“定位值”中使用通配符？（问号，匹配任意单个字符）和*（星号：匹配任意一串字符）；如果要查找实际的？或*，则在字符前输入~（波形符）。

案例：

目的：返回最低“月薪”在月薪列的相对位置。

方法：

在目标单元格中输入公式：=MATCH(SMALL(H3:H12,1),H3:H12,0)。

解读：

利用Small函数获取最低“月薪”，然后利用Match函数定位其位置。

三、Index+Match应用案例。

1、常规查询。

目的：根据“员工姓名”查询对应的“月薪”。

方法：

在目标单元格中输入公式：=INDEX(H3:H12,MATCH(L3,C3:C12,0))。

解读：

典型的数组形式应用案例，首先用Match函数定位出当前值（L3）在C3:C12中的相对位置，然后返回给Index当做“行”参数使用，最后返回H3:H12中指定行的值。

2、逆向查询。

目的：根据“员工姓名”查询对应的“员工编号”。

方法：

在目标单元格中输入公式：=INDEX(B3:B12,MATCH(L3,C3:C12,0))。

解读：

用Match函数定位当前值（L3）在相对范围（C3:C12）中的相对位置，并返回给Index函数的“行”参数；用Index提取B3:B12范围中指定行的值。

3、多条件查询。

目的：查询符合指定“性别”、“婚姻”、和“部门”的“人员姓名”。

方法：

在目标单元格中输入公式：=INDEX(C3:C12,MATCH(L3&M3&N3,E3:E12&F3:F12&I3:I12,0))。

解读：

1、“定位值”和“数据范围”之间用连接符“&”对应连接即可；由于有多个数据范围，所以在填充时用Ctrl+Shift+Enter填充。

2、如果有多个符合条件的值，返回第1条记录。

结束语：

查询引用中，无外呼常规查询（正向查询）、逆向查询以及多条件查询外；用Index+Match函数都可以轻松应对，相对于Lookup和Vlookup来说，更容易理解和上手应用。

如果亲有更多的关于Index和Match函数的应用技巧，欢迎在留言区留言讨论哦！

一、excel函数中vlookup与index+match有何不同？

excel函数中vlookup与index+match准确性方面两个是一样的，速度上vlookup比index+match快。具体有预算速度，取数方向，查找范围的三个方面的区别。

1、运算速度不同：

vlookup比index+match快，因为vlookup只调用一个函数，而index+match使用match的结果作为index的参数，调用两个函数。但是，在计算量较小的情况下，速度可以忽略不计。

2、取数方向不同：

vlookup函数只可正向取数，而index+match函数即可正向取数，也可反向取数。

3、查找范围不同：

vlookup正在查找的数据必须位于所选区域的第一列中，而index+match没有这样的约束。

二、Excel关于INDEX和MATCH配合取数的问题和Excel表的更新-Excel学习网

借助一些Excel公式，您可以在Excel的两种最广泛使用的表设计之间移动数据...并自动将数据从源文件转换为更有用的形式。

越来越多的企业Excel用户将其报告和分析链接到Excel表。这样，他们可以在几秒钟而不是几小时内更新它们。

用户通常依赖两种类型的表来获取数据：垂直表和水平表。尽管每种表都有其优点，但是当您需要一种格式的数据但使用另一种格式的数据时，使用两种类型的表可能会带来挑战。

但是，使用三个Excel工作表功能将使您可以轻松地在这些表格式之间移动数据。

垂直Excel表垂直Excel表格

如果您拥有Excel 2007或更高版本，并且在一家拥有大量数据的公司中工作，您可能已经看到过类似此类的Excel表。

（我已在此页面上的大多数表格中间隐藏了大多数行和/或列，这使您可以看到表格的所有四个角。）

我将其称为“垂直表”，因为日期当然在垂直的列中。

该表的一般格式是从关系数据库获得的数据的典型格式。它有日期字段，键码和金额字段，每行都有很长的列。

请注意，该表没有代码说明，也没有有关每个代码的其他信息。这是因为如果关系数据库的表又高又瘦，则关系数据库以及Power Pivot for Excel的效率会大大提高。

Excel中关键代码的尺寸表

垂直尺寸查询表

要获取有关代码的其他信息，您必须从维查询表中提取数据，有点像这样。

下表显示了有关每个代码的两种信息。首先，它显示了描述。（当然，对您自己的数据的描述将提供更多信息。）

其次，此表具有一个乘法（“多”）列，通常以两种方式使用。

首先，一些外部数据（例如经济和股市数据）以数千或数百万为单位。因此，如果打算在内部使用此数据，将其转换为报表中的数据始终是一个好主意。在这里，代码C009的源数据总是成千上万，因此在报告中将其乘以1,000会将数据转换成一个。

其次，如果数据包含借方和贷方（借方为正数，贷方为负数），则将每个总帐帐户的值乘以其自然符号通常很有用。也就是说，您将应借记的帐户乘以1，将应贷记的帐户乘以-1。这样做时，每个带有自然符号的帐户都将变为正数，这通常使这些帐户更易于在报表中使用。

当然，从关系文件导出的数据通常包括描述和其他维度数据。但这并不总是一件好事。首先，由于许多描述可以重复很多次，因此您的工作簿变得比原来大得多。

其次，这些数据通常可能是不正确的，包括拼写错误的描述，奇怪的缩写，不寻常的大小写文本以及过时的信息。第三，可能不会包含您需要的某些尺寸数据，例如上面显示的“乘法”列。

因此，即使您从关系数据库下载的数据中获得了有关代码和其他关键项的信息，通常也最好维护自己的维查找表，就像上面的表一样。

水平“灰色单元”表

我的 Kyd作战室仪表板模板使用此水平表。由于行和列的边框为灰色，因此我将其称为灰色单元格表。

水平Excel表格

该表包含与前两个表相同的数据。您可以通过将其左上角和右下角数字与第一个表中的顶部和底部数字进行比较来确认这一点。

与竖直桌子相比，此桌子的设计具有多个优点。首先，设计将上述两个垂直表中的数据合并为一个水平表。因此，通常更容易设置和维护。

其次，如果您需要手动输入数据，或从其他来源复制并粘贴数据，则这种布局通常比关系样式表（如顶部Excel表）更易于管理。

但是，如果您的源数据来自关系样式的CSV文件或数据导入，则更新此表可能需要额外的工作。

您的桌面应该放在哪里？

如果您有 Kyd War Room，我建议您复制Dash_Data_Act.xlsx工作簿，以用于以下讨论。如果您喜欢结果，可以将新版本换成旧版本。

通过这种方法，您的仪表板和其他报表仍可以从灰色单元数据库返回数据，该数据库可以使用链接到Excel Table的公式来获取其 数据。该表每个月都会通过快速复制和粘贴进行更新。

如何设置公式以从Excel表填充灰单元数据库

假设每个月您下载的数据看起来像下面左侧的蓝色表格，并且您想使用此数据填充右侧的灰色表格。更具体地说，假设您要设置2012年12月的数据，如下表所示。

从垂直Excel表更新水平表

步骤如下：

1.在灰色单元格表中插入新列以包含新月的数据。例如，在这里，我插入了列BA来包含2012年12月的数据。

2.将日期单元格从前一列复制到新列，然后根据需要更新日期。例如，在这里，我将单元格AZ2复制到单元格BA2，然后将日期更改为12/1/2012。

3.将新月的数据添加到蓝色源表的底部，该表位于不同的工作表中，但与灰色数据表位于同一工作簿中。通常，您只需要复制数据导入或Excel中打开的CSV文件中的数据，然后粘贴到蓝色表格底部下方的第一行即可。

粘贴数据时，表格应自动展开以包括新数据。如果不是，请选择表中的任何单元格，然后选择“数据工具”，“设计”，“属性”，“调整大小表”，然后在“调整大小表”对话框中为表指定新的底部行。

4.您将使用 SUMIFS函数从表中检索数字数据。如果您不熟悉此功能，则可能需要花费一分钟时间来浏览链接并继续阅读。这样做时，您会发现这是SUMIFS函数的语法：

SUMIFS（总和范围，标准范围，标准...）

sum_range 必需。一个或多个要在行或列中求和的单元格，包括数字，范围名称或包含数字的单元格引用。空白和文本值将被忽略。

条件范围 。评估关联标准的第一个范围。条件范围中的错误值将被忽略。

要求的标准 。以数字，表达式，单元格引用或文本形式的条件，这些条件定义了将在Criteria_range1参数中添加哪些单元格。例如，标准可以表示为99，“> 99”，B4，“ sales”或“ 99”。

... 可选。重复对criterias_range和criterias参数对，总共为127对。

5.在为以下公式显示的单元格中输入公式：

BA4：= SUMIFS（TableAct [Amount]，TableAct [Codes]，$ B4，TableAct [Date]，BA $ 2）

这里...

sum_range是TableAct [Amount]。 TableAct是我命名为蓝色Excel表格的名称，而 Amount是该表格中我将在其中找到所需编号的列的名称。也就是说，我希望SUMIFS函数从表的Amount列返回数据。

·条件范围1是 TableAct [Codes]。这是我决定使用的表格中的第一个条件列。

·条件1是$ B4，其值为C001。也就是说，在此行中，我希望SUMIFS函数仅在TableAct [Codes]中找到“ Code C001”的情况下返回数据。

·条件范围2是 TableAct [Date]。

·条件2为BA $ 2，价值为12/1/2012。也就是说，在本专栏中，我希望SUMIFS函数仅在TableAct [Date]中找到2018/12/1的情况下才返回数据。

5.将该公式复制到上图所示的列中。

当然，如果要更新现有表，则可能只是从相邻列中复制公式列。

如何设置公式以使用维数据填充灰色单元数据库

上一组公式更新了灰单元数据库中的数字。但是我们显然不能使用和公式在此处的灰色单元格表格中填充描述字段。

相反，我们使用 INDEX 和MATCH 函数。

（在这个特定示例中，我们也可以使用 VLOOKUP函数。但是由于INDEX-MATCH比VLOOKUP灵活得多，而且速度通常更快，因此我从未在实际工作中使用VLOOKUP。）

假设您具有上面显示的灰色单元格表，并且想要在单元格C4中输入公式以从左侧的蓝色尺寸表返回正确的描述。为显示的单元格输入以下公式：

C4：= INDEX（TableDim [Desc]，MATCH（$ B4，TableDim [Code]，0））

此处，INDEX函数从上图所示的蓝色TableDim表的Desc列返回数据。MATCH函数指定INDEX函数应返回哪个行索引号。

为了计算正确的行索引号，MATCH在TableDim表的“代码”字段中查找在单元格B4中输入的值（即“ C001”）。由于第三个参数为零，因此不需要对数据进行排序，如果未找到“ C001”，则MATCH将返回＃N / A。但事实证明，C001是找到的第一项，因此MATCH返回值1。

因此，INDEX返回在蓝色表的Desc列中找到的第一个描述：“ C001 Act Desc”。

一个类似的公式返回Mult值：

D4：= IF（INDEX（TableDim [Mult]，MATCH（$ B4，TableDim [Code]，0））= 0，“”，

INDEX（TableDim [Mult]，MATCH（$ B4，TableDim [Code]，0） ））

（尽管此公式显示在两行中，但是您当然要在一行中输入它。）

在此公式中，第一个INDEX-MATCH节返回第4行的Mult值。如果该值通常为零，则该公式返回一个空字符串；否则，该公式将返回非零的Mult值。

如何设置公式以从灰色单元格表填充Excel表

我们还可以使用SUMIFS朝另一个方向发展。也就是说，我们可以使用它从灰色单元数据库填充Excel表。

要首次设置蓝色表格，我们首先需要设置“日期”和“代码”列的值。与在C列中设置公式相比，这样做所需的时间更长。

设置日期和代码值...

1.在新工作表中，输入右上方蓝色表格第2行中显示的三个列标题。

2.从灰色单元格表中复制带有29个代码的区域，并将该区域粘贴到蓝色表中的单元格B3中。

3.在单元格A3中输入日期1/1/2009，并将其向下复制到该列中，直到29个Codes为止。

4.因为灰色单元数据库有48个月的数据，每个月有29个代码，所以蓝色表将具有1392（48 x 29）行数据。因此，请在C列中设置一个临时数字列作为参考，该列从1到1392。

为此，请在单元格C3中输入值1，然后按Ctrl + Shift +向下键以选择从C3到电子表格底部的所有单元格。现在选择“主页”，“编辑”，“填充”，“系列”。在“系列”对话框中，将“停止值”指定为1392，然后按OK ...，这将为您提供数字列用作参考。

5.将B列中的29个代码复制并粘贴到最后一个代码下面的第一个单元格中。这应该填充范围B32：B60。

6.在表格中最后一个2009年1月下方的第一个单元格中，输入显示的单元格的公式...

A32：= DATE（YEAR（A3），MONTH（A3）+1,1）

该公式返回2009年2月。将公式向下复制到范围A33：A60 ...，与您刚刚粘贴的代码相邻。

7.复制范围A32：B60，然后将其与您设置的计数器列平行粘贴。这是一种简单的方法：

·按Ctrl + C复制范围后，选择C列中的一个计数器单元格。

·按Ctrl +向下键可“滑动”至该数字列的底部。

·按两次向左箭头键，移至该底行的A列。

·按Ctrl + Shift +向上键将活动单元上方的所有单元格选择为A列中公式的最后一行。

·仅按住Shift键，然后按一次向下箭头键，以便仅选择空白单元格。

·按Ctrl + V粘贴到区域A61：B1394。

8.将日期值和日期公式列更改为日期值。为此，请选择整个日期列。按Ctrl + C复制它们；按Ctrl + Alt + V启动“选择性粘贴”对话框；选择值，然后按确定。

现在，您可以输入从灰细胞数据库返回适当值的公式。在这里，我们为显示的单元格使用INDEX-MATCH-MATCH公式：

A3：= INDEX（例如ActData，MATCH（$ B3，例如ActCodes，0），MATCH（$ A3，例如ActDates，0））**

该公式从为Kyd War Room仪表板模板设置的数据库中返回数据。该数据库使用范围名称（例如ActCodes，ActCodes和ActDates）指定可以找到数据，代码和日期的区域。

INDEX公式返回由两个MATCH函数指定的eg.ActData范围内的值。第一个MATCH函数返回指定的Code值的行索引号，第二个MATCH函数返回指定的Date值的列索引号。

最后，既然普通表已经完成，您可以将其更改为Excel表。为此，选择表中的任何单元格，然后选择“插入”，“表”，“表”；在“创建表”对话框中，确保选中“ 我的表具有标题”；然后选择确定。

如何检查工作

我第一次设置这些表格和公式时，会“四舍五入”它们。也就是说，我从灰色单元格表开始，用链接到灰色单元格表的公式设置蓝色的Excel表，然后设置第二个带有链接到蓝色Excel表的公式的灰色单元格表。

最后，我建立了一个对帐表，该表对开始和结束的灰色单元格表进行了比较。这样，我确保所有公式都可以双向使用。

三、excel函数中vlookup与index+match有何不同？

excel中vlookup与index+match不同点：
1、速度方面vlookup应该比index+match快，因为vlookup只调用一个函数，而index+match则用match的结果作为index的参数，调用了两个函数。不过两者在计算量不大的情况下，速度基本可忽略不计。
2、准确性方面两个是一样的。此外，在不做特殊处理的情况下，vlookup只可正向取数，而index+match即可正向取数，也可反向取数。