☰

修改重复值

tag-icon • tag-icon microsoft-excel
修改重复值

我有一组单元格，每个单元格都有一个值。这些值与学生姓名相关联。我试图根据这个值找出得分最低的 5 名学生。我有这个公式来找到最低值 -

=SMALL(FilteredData[DARTOPIMaths],COUNTIF(FilteredData[DARTOPIMaths],"<="&K2)+1)

然后使用这个公式查找与这个值相关的名称 -

=INDEX(SurnameForenameFiltered,MATCH(K3,FilteredData[DARTOPIMaths],0))

这很好用，但我的问题是当我有两个学生的值相同时。然后它会报告两次名字。我尝试在不改变原始值的情况下使值唯一，但我无法让它工作。

我确实需要能够找到倒数 5 名学生，然后列出学生姓名。如果两名学生的数值相同，则选择两名学生的姓名。

任何帮助都将非常感激。

问候

答案1

我们可以消除重复项的冲突。假设我们有如下数据：

在此处输入图片描述

很多分数是重复的，因此C1我们进入：

 =B1

以及C2进入：

=(COUNTIF($B$1:B2,B2)-1)*0.0001+B2

并抄下来：

在此处输入图片描述

辅助列中的值都是唯一的。在E1进入：

=INDEX(A:A,MATCH(SMALL(C:C,ROW()),C:C,0))

并抄下来。在F1进入：

=INDEX(B:B,MATCH(SMALL(C:C,ROW()),C:C,0))

并抄下来：

在此处输入图片描述

这种重复值方法的修改避免了对数组公式的需要。

答案2

这是实现此目的的一种方法（我复制了@GaryStudent 使用的表格）。

  • 确定您想要的“最低”值并将该值放入 D1 中（5在此示例中）
  • 创建一个结果数组，TRUE/FALSE根据分数是否小于或等于d1最低分数
  • 将该数组乘以行号数组，以获得适当的行
  • 添加将1/1/(array)其更改为错误数组DIV/0和行号。
  • 使用聚合函数的 SMALL 参数仅返回那些不是错误的值。
  • 使用该结果对表进行索引并返回名称和分数。
  • 我像您一样使用了结构化引用。

请注意，考虑到可能出现平局，这可能会返回超过五名学生

F1: =IFERROR(INDEX(StudentScores[#All],AGGREGATE(15,6,1/1/(StudentScores[Score] <= SMALL(StudentScores[Score],$D$1))*ROW(StudentScores),ROWS($1:1)),1),"")

G1: =IFERROR(INDEX(StudentScores[#All],AGGREGATE(15,6,1/1/(StudentScores[Score] <= SMALL(StudentScores[Score],$D$1))*ROW(StudentScores),ROWS($1:1)),2),"")

在此处输入图片描述

当然，您也可以使用Advanced Filter，以公式为标准，并将结果写入“新”位置。但它可能不如您所需的那么动态。

相关内容