在Excel中使用INDEX和MATCH进行简单数据库查询

2024

最初，Excel的设计并非真正的数据库。其早期的数据库功能在数量和质量上都有限制。而且，由于Excel数据库中的每条记录都可以一次显示在屏幕上 - 这意味着所有内存一次都在内存中 - Excel数据库必须非常小：包含少量记录的多个字段或包含大量记录的少数字段;和最少的计算

VLOOKUP（垂直）和HLOOKUP（水平）是可用于查询数据库以获取特定信息的唯一函数。例如，您可以查询查找并提取所有包含销售额高于1000美元但低于5000美元的记录 - 但仅限于平面文件（仅限一个数据库矩阵）。

数据透视表是为了创建关系数据库而开发的，因此用户可以创建关系数据库更容易查询，使用更少的内存，并提供更准确的结果。但是，如果您没有或不需要关系数据库，但需要更强大和更可靠的数据库功能，请尝试以上这些开始。

[更多阅读：您的新PC需要这15个免费的优秀程序]

索引，匹配和索引匹配

在Excel中，INDEX函数返回特定位置（在列表，表格，数据库中）的项目。

MATCH函数返回值的位置（在列表中，表，数据库）。而且，INDEX-MATCH函数一起使用，可以轻松从表中提取数据。

INDEX函数的语法是：INDEX（array，row_num，[column_num]）。该数组是您正在使用的单元格的范围。显然，Row_num是包含要查找的数据的范围中的行号。 Column_num是包含要查找的数据的范围中的列号。 INDEX公式不识别列字母，因此您必须使用数字（从左数起）。

MATCH函数的语法是：MATCH（lookup_value，lookup_array，[match_type]）。 lookup_value是要查找的数字或文本，可以是值，逻辑值或单元格引用。 lookup_array是您正在使用的单元格的范围。 match_type确定MATCH函数 - 即完全匹配或最接近的匹配。 INDEX函数

在我们的例子中，着名的Commodore James Norrington有一张电子表格，用于跟踪加勒比地区所有的海盗船。 Norrington的名单由船舶的战斗编队安排，与他所在地区的海图相匹配。当他看到一艘船正在前进时，他将索引公式输入他的电子表格中，以便识别船舶及其能力。在第一个查询中，Norrington想知道正在前进的船舶的类型。

1。为查询选择一个位置（单元格或单元格区域）（即函数和结果），然后将光标移动到该单元格。例如：第18行的任何单元格。

2。输入INDEX函数（在等号前面），再加上一个左括号，然后突出显示（或键入）数据库/表格范围，如下所示：= INDEX（A2：I16

注意：如果您想要绝对参考，在这种情况下，这意味着对公式进行硬编码，因此当/如果它被复制时，范围不会改变），在每个单元格引用之后按F4一次，也可以突出显示范围：只需在选择完整范围后按F4 ，然后添加完整的绝对参考符号

3.接下来，输入一个逗号来分隔参数（即单独的公式片断）;然后输入行号和逗号，然后输入列号（是，它必须是一个数字，而不是通常的列字母）和右括号（或者只是按Enter键，让Excel为你添加最后的圆括号）完整的公式如下所示：= INDEX（$ A $ 2：$ I $ 16 ，15,2）。

注意：行编号从范围中的第一个数字开始，而不是电子表格中的第一个数字，例如，eve n虽然Cavalleria海盗船在Excel 16行上，但实际上我们公式中的第15行是因为我们的范围是从A2开始并经过I16。如果A2是第1行，则A16是第15行）

4.请注意，Norrington所寻找的船只的类型是一个战争纵队。

JD Sartain

使用索引函数来查找数据库中的特定信息。 INDEX范围

现在，我们可以用这个数据库表做更多的事情。每次你想知道某件事时，你都不需要重新定义范围。为了简单起见，我们将定义一次范围并命名。然后，我们可以将范围的名称放入我们的公式中。

1。转到A2并突出显示范围A2到I16。

2。在公式选项卡中，从定义的名称组中选择

定义名称

。在弹出的对话框中，在名称字段框中输入您的范围的名称。 4。接下来，输入范围（范围所在的范围），它是工作簿或工作簿中的一个工作表。 5。如有必要，请输入评论。

6。最后，验证Refers To字段显示正确的名称和范围，然后单击

。

。如果您想验证您的范围确实已保存在Excel中，请尝试以下小测试：按Ctrl + G（GoTo命令）。在GoTo对话框中选择发货，然后单击

确定，然后Excel再次突出显示范围A2：I16。如何定义和保存范围 C.具有SUM和AVERAGE公式的索引 Norrington正在评估舰队的战斗能力。首先他想知道海盗有多少炮，每艘船的平均炮数以及所有这些海盗船的总人数。他输入以下公式：

1。 = SUM（INDEX（Ships，，8））等于334，大炮总数和

2。 =平均（INDEX（船舶，8））等于22.27，或每船大约22.27个炮。 = SUM（INDEX（Ships，，7））等于2350，是所有船上所有船员的总数。

为什么船和8号之间有逗号，空格，逗号以及这些数字是什么意思？ Ships是Range（后跟逗号），Row参数是空白的（或空格），因为Norrington需要所有的行，而8代表第8列（这是H列，Cannons）。

有些人可能会问为什么不直接在这些列的底部输入SUM和/或AVERAGE公式呢？在这个微小的电子表格中，是的，它会一样简单。但是如果电子表格有5000行和300列，那么您需要使用SUM和AVERAGE来使用INDEX

JD Sartain

03 INDEX公式。

一旦范围被命名，Norrington可以打开一个空白电子表格这个相同的工作簿，并在列B中写入他的查询（公式）（其中显示了结果而不是公式），并在列A中定义了这些查询的描述。（注意：列C显示列B中的实际公式）。

他不必在视觉上看到他的5000条记录的巨大数据库，或者在公式计算时等待几秒钟。他可以从他的查询表中获得他需要的所有信息。请记住，电子表格越大，功能就越慢，特别是如果有很多公式。

JD Sartain

04 Commodore James Norrington的海盗船信息/查询表。

d。 INDEX MATCH与MAX

现在，Norrington想知道人口最多的船上有多少海盗，以及哪艘船？他使用带MAX公式的INDEX获得最高数量的海盗，但他还需要知道哪艘船正在运送它们。因此，他使用带MAX公式的INDEX / MATCH来找出哪艘船上的海盗人数最多。

1。 = MAX（INDEX（Ships，，7））等于300，是其中一艘船舶的最高数量

2。 = INDEX（$ A $ 2：$ A $ 16，MATCH（MAX（船舶），$ G $ 2：G $ 16，0））等于皇家詹姆斯，这艘船上的海盗人数最多。 = INDEX（$ F $ 2：$ F $ 16，MATCH（MAX（船舶），$ G $ 2：G $ 16，0））等于Stede Bonnet，皇家詹姆斯船长，海盗船员为300