☰

如何使用 LARGE() 函数处理重复值？

tag-icon • tag-icon microsoft-excel
如何使用 LARGE() 函数处理重复值？

我想计算具有重复值但名称不同的前 3 个结果。

示例数据：

Name           score
A              40
B              55
C              37
D              55

预期结果：

order        score     name
1            55        B
1            55        D
3            40        A

答案1

您可以使用数据透视表以及RANK()函数来实现这一点。

步骤 1 - 准备数据透视表

  1. 向您的数据添加一列（例如在 C 中），使用以下公式：=RANK(B2,$B$2:$B$5)-> 这将显示B2所有值之间的排名$B$2:$B$5

在此处输入图片描述

  1. 选择您的数据（包括排名列），然后单击“插入”>“数据透视表”
  2. 将字段“分数”和“名称”作为行，将字段“分数”作为值，如下所示

在此处输入图片描述

  1. 通过单击以下菜单项来格式化数据透视表：

在此处输入图片描述

你现在应该得到类似这样的内容：

在此处输入图片描述

步骤 2 - 显示前 3 个值（方法 A）

  1. 单击数据透视表第一个字段（排名）附近的箭头
  2. 选择“值过滤器”
  3. 选择“前 10 名”

在此处输入图片描述

  1. 输入3而不是10获取前 3 个值

在此处输入图片描述

你现在应该有类似这样的内容：

在此处输入图片描述

笔记：使用此方法，如果多个值具有相同的排名（如示例数据中所示），则它们将显示多次。这是数据透视表和“Top X”功能的默认行为。

如果你不想要 4 行，而只想要 3 行，请参见方法 B

第 2 步 - 显示前 3 个值（方法 B）

  1. 单击数据透视表第一个字段（排名）附近的箭头
  2. 选择“标签过滤器”
  3. 选择“小于或等于”，并3在提示时输入

在此处输入图片描述

笔记：使用此方法时要小心：如果有 5 个值的排名为 1，则将有 5 行。第二高的值的排名为 6，并且不会显示（它大于 3）。因此它不再是前 3 名，但我认为这种方法值得一提，因为在某些情况下它可能会起作用。

当数据发生变化时，不要忘记刷新数据透视表！

答案2

在此处输入图片描述

为了获得分数，单元格 F3写下这个公式并填写第 3 行：

=LARGE(B$3:B$6,ROWS(F$3:F3))。

要获取名称，细胞G3写下这个数组公式并填写：

注意：使用 Ctrl+Shift+Enter 完成此数组公式。

{=IFERROR(INDEX(A$3:A$6,SMALL(IF(B$3:B$6=F3,ROW(B$3:B$6)-ROW(F$3)+1),COUNTIF(F$3:F3,F3))),"")}

为了获得秩序，细胞E3写下这个公式并填写：

=IFERROR(RANK(LARGE(B$3:B$6,ROWS(E$3:E3)),$F$3:$F$6),"")

配方如何发挥作用：

  • F3 中的公式找出前 3 名分数。
  • G3 中的公式查找得分最高的 3 名姓名，将 F 列中的得分与 B 列中的得分进行比较，并从 A 列中提取姓名
  • E3 中的公式找出前 3 名得分的排名。

答案3

order只需为和score列添加两个简单公式，再加上为 列添加第三个稍微复杂一点的公式即可完成此操作name。

设置电子表格，如下所示：

电子表格的屏幕截图

然后将这些公式多单元格数组输入到指定的范围内（记住排除周围的{和}）：

公式1（D2:D4）：

{=RANK(E2:E4,E2:E4)}

二级方程式（E2:E4）：

{=LARGE(B2:B5,{1;2;3})}

公式 3（F2:F4）：

{=INDEX(A:A,10^5-MOD(LARGE(10^5*B2:B5+10^5-ROW(B2:B5),{1;2;3}),10^5))}

例如，要将前 3 个结果更改为前 N 个结果（其中 N 是一个较大的数字），10而不是使用硬编码数组进行排名，{1;2;3;4;5;6;7;8;9;10}请使用以下命令：

ROW(INDEX(X:X,1):INDEX(X:X,10))

虽然这可以按原样工作，但最好用X:X输入公式的列来替换。这样，如果X删除列，公式就不会中断。

这是一个比普遍存在的解决方案更好的解决方案，而且ROW(INDIRECT("1:10"))由于它是非易失性的，所以更短。

公式解释：

前两个公式很简单。

第三个公式的美化版本如下：

=
INDEX(
  (A:A),
  10^5-
  MOD(
    LARGE(
      10^5*B2:B5+10^5-ROW(B2:B5),
      {1;2;3}
    ),
    10^5
  )
)

该公式的工作原理是修改分数，使其也包含行索引的十进制补码。然后，在LARGE选择适当的修改分数后，提取索引并将其用于INDEX()获取与所选分数相对应的名称。

逐步执行公式F3应该会使上述内容更加清晰：

  • 10^5*B2:B5
    → 10^5*{40;55;37;55}
    → {4000000;5500000;3700000;5500000}，量表分数
  • 10^5-ROW(B2:B5)
    → 10^5-{2;3;4;5}
    → {99998;99997;99996;99995}，分数行号的十进制补码
  • {4000000;5500000;3700000;5500000}+{99998;99997;99996;99995}
    → {4099998;5599997;3799996;5599995}，“修正”分数
  • LARGE({4099998;5599997;3799996;5599995},{1;2;3})
    → {5599997;5599995;4099998}，前三名修改后的分数
  • 10^5-MOD({5599997;5599995;4099998},10^5)
    → 10^5-{99997;99995;99998}
    → {3;5;2}，前三名成绩的行号
  • INDEX(A:A,{3;5;2})
    → {B;D;A}
    →D因为我们在多单元格数组输入公式的第二个单元格中

笔记：

  • 美化的公式确实可以起作用。
  • (A:A)需要使用周围的括号来强制A:A将其保持在自己的一行上。
  • 对于相同的分数，公式将按行的升序返回姓名。可以修改公式，在修改分数时使用普通行号而不是行号的十进制补码，以按降序返回姓名：
{=INDEX(A:A,MOD(LARGE(10^5*B2:B5+ROW(B2:B5),{1;2;3}),10^5))}
  • 甚至可以通过在分数和行号之间生成一个随机数来随机化相同分数的顺序。

相关内容