Excel索引功能|公式&免费视频

Excel索引函数(示例+视频)

Excel索引功能

何时使用Excel索引函数

当您想从表格数据中获取值,并且您有数据点的行号和列号时,可以使用Excel INDEX函数。例如,在下面的例子中,当您知道数据集中的行号和列号时,您可以使用INDEX函数来获得Physics中的' Tom '标记。

Excel Index Funtion  - 示例1

它回来了什么

它返回指定行号和列号的表中的值。

语法

= index(array,row_num,[col_num])
= index(array,row_num,[col_num],[area_num])

索引函数有2个语法。然而,在大多数情况下使用第一个,但是,在三向查找的情况下,使用第二个(实施例5)。

输入参数

  • 数组,一个细胞范围或阵列常数。
  • row_num -要获取值的行号。
  • (col_num) -要获取值的列号。虽然这是一个可选的参数,但如果未提供row_num,则需要提供。
  • (area_num) -(可选)如果数组参数由多个范围组成,则此号码将用于从所有范围中选择引用。

额外的笔记(无聊的东西..但重要的是要知道)

  • 如果行号或列编号为0,则会分别返回整行或列的值。
  • 如果在单元格引用的前面使用索引函数(例如A1 :),则返回单元格引用而不是值(参见下面的示例)。
  • 最广泛使用匹配功能
  • 不像vlookup.,索引函数可以从查找值的左侧返回值。
  • INDEX函数有两种形式——数组形式和引用形式
    • “阵列表单”是您根据给定表格的行和列号获取值的位置。
    • “引用表单”是指有多个表的情况,您可以使用area_num参数来选择表,然后使用行号和列号在其中获取一个值(参见下面的实际示例)。

Excel索引函数 - 示例

以下是使用Excel索引函数的六个例子。

示例1 - 在物理学中找到汤姆的标记(双向查找)

假设您有一个数据集,如下所示:

Excel Index Funtion  - 示例1

要查找汤姆的物理标记,请使用以下公式:

=指数(B美元3:E 10美元,3、2)

这个INDEX公式将数组指定为$B$3:$E$10,其中包含所有主题的标记。然后它使用行号(3)和列号(2)来获取Tom的物理成绩。

示例2 - 使用匹配功能使查找值动态

可能并不总是可以手动指定行号和列号。您可能有一个巨大的数据集,或者您可能希望使其动态,以便它自动标识单元格中指定的名称和/或主题并提供正确的结果。

如下图所示:

Excel索引功能 - 示例2  - 动态

这可以使用索引和索引的组合来完成比赛功能。

下面是使查找值动态的公式:

=指数($ B $ 3:$ 10 $ 10,匹配($ 5 $ 5,3:$ 10,0),比赛($ H $ 4,$ 2 $ 2:$ 2,0))

在上述公式中,而不是硬编码行号和列数,匹配函数用于使其动态。

  • 动态行号由下面公式的一部分给出- MATCH($G$5,$A$3:$A$10,0)。它扫描学生的名字并识别查找值(在本例中为$G$5)。然后返回数据集中查找值的行号。例如,如果查找值是Matt,它将返回1,如果是Bob,它将返回2,以此类推。
  • 动态列数由以下部分提供 - 匹配($ H $ 4,$ 2:$ 2,0)。它扫描主题名称并识别查找值(在本例中为$ H $ 4)。然后它返回数据集中查找值的列数。例如,如果查找值是数学,则它将返回1,如果它是物理,则它将返回2等。

示例3 -使用下拉列表作为查找值

在上面的例子中,我们必须手动输入数据。这可能是耗时和错误的,特别是如果您有一个巨大的查找值列表。

在这种情况下,一个好方法是创建查找值的下拉列表(在本例中,可以是学生名和主题),然后从列表中进行选择。根据选择,公式将自动更新结果。

如下图所示:

Excel索引函数-示例3 -动态下拉列表

这是一个很好的仪表板组件,因为您可以有一个后台有数百名学生的庞大数据集,但是最终用户(假设是教师)可以通过从下拉菜单中进行选择,快速获得一个学生在某一科目中的分数。

如何制作:

在这种情况下使用的公式在实施例2中使用。

=指数($ B $ 3:$ 10 $ 10,匹配($ 5 $ 5,3:$ 10,0),比赛($ H $ 4,$ 2 $ 2:$ 2,0))

查找值已被转换为下拉列表。

以下是创建的步骤118bet网址多少 :

  • 选择要在下拉列表中使用的单元格。在这个例子中,在G4中,我们想要学生名。
  • 转到数据 - >数据工具 - >数据验证。
  • 在“数据验证”对话框中,在“设置”选项卡中,从“允许”下拉列表中选择列表。
  • 在源文件中选择$A$3:$A$10
  • 单击确定。

现在在单元格G5中有下拉列表。同样,您可以在H4中为主题创建一个。

示例4 - 来自整个行/列的返回值

在上面的例子中,我们使用了Excel INDEX函数来进行双向查找并获得单个值。

现在,如果你想获得学生的所有标记,怎么办。这可以使您找到该学生的最大/最小分数,或者在所有科目中得分的总标记。

在简单的英语中,您希望首先获得学生的整个分数(让我们说Bob),然后在这些值中识别最高分或所有分数的总数。

这是诀窍。

在Excel索引函数中,当您输入时列数为0,它将返回整行的值。

所以这将是:

=指数($ B $ 3:$ 10 $ 10,匹配($ 5 $ 5,$ 3,$ 3:$ 10,0),0)

现在这个公式。如果按原样使用,将返回#VALUE!错误。当它显示错误时,在后端,它返回一个包含Tom -{57,77,91,91}的所有分数的数组。

如果在编辑模式下选择公式并按F9,您将能够看到其返回的数组(如下所示):Excel索引函数 - 示例4整行

类似地,根据查找值是什么,当列号指定为0(或保留为空)时,它将返回行中查找值的所有值

现在要计算汤姆获得的总分,我们只需在总和函数中使用上述公式。

=总和(指数(B美元3:E 10美元,匹配(G 5美元,美元美元3:$ 10美元,0),0))

在类似的线条上,计算最高分,我们可以使用最大/大而且计算最小,我们可以使用min /小。

Excel索引功能-示例4整行最高和

例5 -使用索引/匹配的三种方式查找

Excel索引函数是为处理三方查找而构建的。

三方查找是什么?

在上面的例子中,我们已经使用了一个表格,为不同科目的学生提供了分数。这是双向查找的示例,因为我们使用两个变量来获取分数(学生的名称和主题)。

现在,假设在一年内,一名学生有三种不同的考试,单位测试,中期和最终检查(这就是我是学生的时候)。

三种查找将是从指定的考试水平获得指定主题的学生标记的能力。这将使它成为一个三种查找,因为有三个变量(学生名称,主题名称和考试水平)。

以下是三方查找的示例:

Excel索引函数-例5 -三向查找

在上面的示例中,除了选择学生的姓名和科目名称外,还可以选择考试级别。根据考试的级别,它从三个表中的一个返回匹配的值。

下面是单元格H4中使用的公式:

=指数(($ B $ 3:$ 7,$ B $ 11:$ 11 $ 15,$ B $ 19:$ E $ 23),比赛($ 4,$ 3,$ 3:$ 7,0),比赛($ H$ 3,$ B $ 2:$ 2,0),如果($ h $ 2 =“单位测试”,1,if($ h $ 2 =“midterm”,2,3)))))

让我们打破这个公式来了解它是如何工作的。

此公式需要四个论点。索引是具有多个语法中的Excel中的函数之一。

= index(array,row_num,[col_num])
= index(array,row_num,[col_num],[area_num])

到目前为止,在上面的所有例子中,我们使用了第一个语法,但要做三方查找,我们需要使用第二个语法。

现在让我们基于第二个语法查看公式的每个部分。

  • 阵列 - ($ b $ 3:$ e $ 7,$ b $ 11:$ e $ 15,$ b $ 19:$ e $ 23):在这种情况下,我们在括号中使用了三个数组。
  • row_num - 匹配($ 4,$ 3 $ 3:$ 7,0):匹配函数用于在学生名称列表中查找学生名称中的学生名称的位置。
  • col_num - MATCH($H$3,$B$2:$E$2,0): MATCH函数用于在主题名称列表中查找主题名称在单元格$H$3中的位置。
  • [Area_Num] - 如果($ H $ 2 =“单位测试”,1,如果($ H $ 2 =“Midterm”,2,3)):区域号值告诉INDEX函数要选择哪个数组。在本例中,第一个参数中有三个数组。如果从下拉列表中选择Unit Test, If函数返回1,INDEX函数选择三个数组中的第一个数组(即$B$3:$E$7)。

示例6 - 使用索引函数创建引用(动态名为范围)

这是Excel INDEX函数的一个疯狂用法。

让我们拍一个简单的例子。

我有一个名字列表如下所示:

Excel索引功能 - 示例6  - 参考

现在我可以使用一个简单的INDEX函数来获取列表中的最后一个名字。

这是公式:

=指数($ $ 2:$ 9美元,COUNTA($ $ 2: $ 9美元))

该函数只计算不为空的单元格数,并返回列表中的最后一项(仅当列表中没有空格时才有效)。Excel索引功能-参考2

现在,这里有什么魔法。

如果将公式放在单元格引用前面,则公式将返回匹配值的小区引用(而不是值本身)。

= A2:指数($ 2:$ 9,counta.($ 2:$ 9美元))

您希望上述公式返回= A2:“Josh”(其中Josh是列表中的最后一个值)。但是,它返回= A2:A9,因此您获得了一系列名称,如下所示:Excel索引函数 - 参考文献3

一个实用的例子,这种技术可能有用的是创造动态名为范围

这就是本教程的内容。我已经介绍了使用Excel INDEX函数的主要示例。如果你想看到更多的例子被添加到这个列表中,请在评论部分告诉我。

注意:我已经尝试过我最好的证明阅读本教程,但如果您发现任何错误或拼写错误,请告诉我

Excel索引函数 - 视频教程

相关Excel功188亚洲金博宝app能:

您也可能喜欢以下Excel教程:

Excel电子书订阅

免费Excel书籍

获得51个Excel提示电子书来提高你的生产力,更快地完成工作

9关于“Excel指数函数的思考公式&免费视频“

  1. 示例5不起作用...如果您有一个使用索引/匹配的三种方式查找的视频将是大的..
    多次尝试,但它正在显示错误......

  2. 谢谢你的帮助资源。请在这方面下载练习表的选择。这将有助于更容易提及示例的粒子。

  3. 你好
    你能帮我把不同列的所有非空值放到一列吗?
    我有30列的值(来自另一个公式-并改变每次)我想把所有非空白值放在一列(如果它忽略重复更好)

  4. 谢谢你的有用资源。我遇到了excel索引()函数中的一个错误,如果其他人遇到这个,我很好奇。

    在附加图像中,请注意,Excel对行或列的值为0的处理如何取决于该功能的位置。在呼叫B3中,对行1,COL 0的引用导致2的意外/不需要的2(第1行中的内容,第2列)。在单元C1中,对行0的引用,COL 1导致1的意外/不需要的结果1(行1的内容,列1)。

    但是,如果相同的函数调用位于阵列的行和列范围之外,则该函数返回#Value!,如预期,用于超出范围引用。请参阅单元格D5和E3,它们分别与B3和C1完全相同。

    是否有任何已知原因,为什么函数的行为不同,具体取决于它相对于目标阵列的位置?这种行为是否记录过?

    谢谢你的任何洞察力。

    https://uploads.disquscdn.com/images/2b5614ecfaed959af45c64b066ab9d8e7ea330f1c6f4aaeche611d6c9598005ab.jpg.

  5. = SUM(索引($ B $ 3:$ 10,匹配($ 5 $ 5,$ 3,$ 3:$ 10,0),0),1),在此公式中为什么在最后使用“,1”。

评论被关闭。