☰

如何查找某个范围内所有匹配的数字/数据

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
如何查找某个范围内所有匹配的数字/数据

我正在尝试寻找 MAX 数字以及与之相关的年份。

  • 2000-2010 年 (A1:J1)
  • 数据 (A2:J2)
  • 数据的最大值放在 A3 中

我可以使用 INDEX/MATCH 来提供 FIRST MAX YEAR，它与 DATA 的 MAX 匹配，但这个 MAX 数字可能出现在多个年份（例如 2002、2005、2009）。

B3 的第一年

我在 C3 中有一个公式，现在尝试使用 (OFFSET, 0, B3) 开始另一个索引匹配，以使搜索在 FIRST MAX YEAR 之后开始，但由于某种原因，它给了我遇到的第一年份。

=INDEX(A1:J1,MATCH(A3,OFFSET(A2:J2,0,MATCH(B3, A1:J1,0))))

我已将数字四舍五入，使它们有一个小数点，并且我知道在范围 A2:J2 中有两个匹配的 MAX DATA 数字。

更新：希望图片能够解释得更清楚。

查找具有最大值的年份 有任何想法吗？

谢谢迈克尔

回答：

我按照下面评论中 Abdul 的链接找到了一个非常符合我要求的公式。

=IFERROR(INDEX(A1:K1,SMALL(IF(A2:K2=$A$3,COLUMN(A2:K2)-COLUMN(INDEX(A2:K2,1,1))+1),nth)),"")

第 n 个单元格是数字 2。因此，它正在寻找我的 A3 单元格的第 2 次出现。

希望这对大家有所帮助。捷捷是个很棒的网站。强烈推荐。

获取第 n 个与索引匹配的匹配项

答案1

您可以仅在 B3、C3 等中输入公式来执行此操作。但我建议添加一个辅助行（第 4 行）以使公式更具可读性。

在单元格 B4 中输入：

=MATCH($A3,OFFSET($A2:$K2,0,A4),0)+A4

这将返回6第 2 行中第一次出现 19.0 的列号。将此公式复制到右侧，您将在剩余的单元格中得到和。现在在单元格 B3 中输入8：11#N/A

=INDEX($A1:$K1,B4)

并将公式复制到右侧。您将得到找到最大值的年份：2005，2007然后在剩余的单元格中输入2010。#N/A

如果您不想看到这些#N/A值，您可以使用该IFERROR函数将其更改为空白。

答案2

我喜欢 Blackwood 的答案，因为它不需要数组公式。但是，如果您不能忍受辅助行，或者只需要一个公式即可完成此操作，则可以在 A4 中使用此公式并填充到右侧：

 =IFERROR(SMALL(IF($A$2:$K$2=$A$3,$A$1:$K$1),COLUMN()),"")

它是一个数组公式，因此必须用 来输入CTRL Shift Enter，而不是仅仅Enter。

它的工作原理如下。IF() 生成一个年份数组，其中数据与 A3（数据的最大值）匹配，其他位置为 FALSE：

{False;False;False;False;False;2005;False;2007;False;False;2010}

然后 SMALL() 使用列号作为索引从该数组中选择第 1、第 2、第 3 等最小值。它仅检查数字并忽略数组中的“False”值。

最后，IFERROR() 将任何 #N/A 错误替换为空白。

相关内容