☰

获取单元格值大于的前 3 行

tag-icon • tag-icon microsoft-excel
获取单元格值大于的前 3 行

努力根据行内的值获取一行数据。我的数据结构如下：

ID  Name   Score  Owner
1   Item1  94%    MD
2   Item2  24%    OM
3   Item3  55%    TM
4   Item4  76%    MD
5   Item5  12%    OM

我想要实现的是能够提取得分超过 65%（或任何其他数字）的姓名和所有者。

我目前使用列引用，我的表被称为“table2”，例如 Table2[Name]，等等。

尝试使用 INDEX 和 MATCH，但似乎对我不起作用，也尝试了 VLOOKUP，但似乎仍然不起作用。

我一直在尝试和调整一些公式来实现我想要的效果：

=INDEX(Table2,MATCH(0.32,Table2[Score],-1),0)

=IF(K5="","",INDEX(Table2[Name],Table2[Score]=L5))
$K = 1 to 3 (5=1 in the above), L5 = the score I am looking for.

任何指导都会非常有用，我在互联网上找到的主要问题是它们使用范围“$A$1:$A$19”等，而当我用这些范围替换命名范围时，公式不起作用。

答案1

在此处输入图片描述

怎么运行的：

  • 在单元格中写入此数组公式A10，最后Ctrl+Shift+Enter填写正确的然后向下。

      {=IFERROR(INDEX($B$2:$D$6, SMALL(IF((INDEX($B$2:$D$6, , $B$8)>$A$8), MATCH(ROW($B$2:$D$6), ROW($B$2:$D$6)), ""), ROWS(B2:$B$2)), COLUMNS($A$1:A1)),"")}

笔记：

  • 由于得分大于 65%，因此将仅提取 2 条记录。

  • 单元格A8具有标准 65% 并B8具有列位置2（分数列）。

  • 根据需要调整公式中的单元格引用。

編輯：

INDEX($B$2:$D$6, , $B$8)，变成

INDEX($B$2:$D$6, , 2)

并返回C2:C6。

C2:C6与 $A$8（本例中为 65%）进行比较。

MATCH(ROW($B$2:$D$6), ROW($B$2:$D$6)), "")

变成，，{1;2;3;4;5}, "")

与 上的 IF 测试结合使用C2:C6，>$A$8返回行，

{1;"";"";"";4;""}从Name Column。

最后将公式向右然后向下拖动，完成数据提取。

答案2

我似乎已经回答了自己的问题，在我写出我的问题之后，我回顾了我从https://people.highline.edu/mgirvin/excelisfun.htm特别是 EMT967.xlsx，此处有解释https://www.youtube.com/watch?v=rKDI-kdBsjY另一个来自http://www.get-digital-help.com/2009/09/28/extract-all-rows-from-a-range-that-meet-criteria-in-one-column-in-excel/。使用这两个示例，我设法使用 INDEX 和 MATCH 的组合创建了所需的公式。

为了清楚起见，这里是我目前在某些单元格中所拥有的内容：

W4

=LARGE(Table2[Score],3)

W5

3

K5（向下复制 3 个单元格至 K8）

=IF(ROWS(K$5:K5)>$W$5,"",ROWS(K$5:K5))

L5（向下复制 3 个单元格至 L8）

=IF(K5="","",LARGE(Table2[Score],K5))

M5（向下复制 3 个单元格至 M8）

=IF(K5="","",INDEX(Table2[Name],MATCH(L5,Table2[Score],0)))

如果我将单元格 W5 更改为其他内容，例如 1，工作表将仅显示我的数据集中得分最高的项目，2 将显示 2，依此类推。如果您想要超过 3 个，请将 K5、L5 和 M5 复制到更多单元格下方，然后更改 W5 中的数字以填充公式所在的行数。

我希望这能帮助别人，我花了大约 6 个小时来学习它并让它工作，真是令人尴尬！但是，我让它工作了，所以这是一件好事。

更新：这“有效”，但不能处理唯一值，因为它只返回与给定分数匹配的第一个名字。

更新2：使用之前提供的示例之一进行了修复。Cell M5 现在包含：

=IF(K5="","",INDEX(Table2[Name],AGGREGATE(15,6,(ROW(Table2[Score])

-CELL("row",Table2[Score])+1)/(Table2[Score]=L5),COUNTIF($L$5:L5,L5))))

现在似乎能够处理重复值。

相关内容