☰

在索引匹配排名公式中正确显示平局

tag-icon • tag-icon microsoft-excel worksheet-function
在索引匹配排名公式中正确显示平局

上个月我收到了优秀的配方返回 PTA 成员比例最高的小学、初中或高中（以及第二高的学校，等等）。我刚刚遇到了平局问题。

这是我的公式：

=INDEX('[OK DOE.xlsx]DOE'!$B$2:$B$5000,MATCH(LARGE(IF('[OK DOE.xlsx]DOE'!$M$2:$M$5000 = "PRIMARY", '[OK DOE.xlsx]DOE'!$I$2:$I$5000),1), ('[OK DOE.xlsx]DOE'!$I$2:$I$5000) * ('[OK DOE.xlsx]DOE'!$M$2:$M$5000 = "Primary"),0))

以下是一些示例数据：

-------B-------------------------------I-----------------------M
------School--------------------Percent Members----------School Level
John E. Bryan Elem Sch PTA------------84%------------------Primary
Goldsmith Schiffman Elementary PTA----34%------------------Primary
Huntsville Hi Sch PTSA----------------96%--------------------High 
Bernice Causey Elem   PTSA------------34%------------------Primary
Hampton Cove Elem PTA-----------------34%------------------Primary
Highland Middle School----------------42%-------------------Middle
Elsie Collier Elem PTA----------------24%------------------Primary

应该按如下方式对它们进行排序：

1st - John E Bryan Elem Sch PTA
2nd - Goldsmith Schiffman Elementary PTA
3rd - Bernice Cousey Elem PTSA
4th - Hampton Cove Elem PTA
5th - Elise Collier Elem PTA

但它错误地这样做了：

1st - John E Bryan Elem Sch PTA
2nd - Goldsmith Schiffman Elementary PTA
3rd - Goldsmith Schiffman Elementary PTA
4th - Goldsmith Schiffman Elementary PTA
5th - Elise Collier Elem PTA

帮助？

答案1

添加辅助列，为您的百分比添加决胜值。由于您的示例数据显示M为最后一列，因此我选择列N作为辅助列。只需将其设置为数据后的下一列，设置完成后即可隐藏它。

在单元格中N2输入=0.001-(ROW()/100000000)+I2并将其向下拖动至少与您将拥有的学校一样多的行。

这假设您不需要百分比中的任何小数位，正如您的示例中所示。

ROW()返回公式所在的行号。将其除以100,000,000并从中减去0.001。这将为您提供一个从开始0.099998并向减少的0.000001数字。这样，平局决胜局将有利于它们列出的顺序，这样在您的例子中，“Goldsmith Schiffman”将被列为该系列平局中的第一个，而“Hampton Cove”将被列为最后一个。这些值是任意选择的，不会四舍五入百分点，并为您提供足够大的平局决胜局集，假设您不太可能要99,998跟踪超过学校。

将此决胜数字添加到您的百分比中可确保没有平局。在这种情况下，您最终将使用以下值进行排名：

| School                               | Helper Col |
|--------------------------------------|------------|
| Goldsmith Schiffman   Elementary PTA | 34.099997% |
| Bernice Causey Elem   PTSA           | 34.099995% |
| Hampton Cove Elem PTA                | 34.099994% |

您可以将公式修改为：

=INDEX('[OK DOE.xlsx]DOE'!$B$2:$B$5000,MATCH(LARGE(IF('[OK DOE.xlsx]DOE'!$M$2:$M$5000 = "PRIMARY", '[OK DOE.xlsx]DOE'!$N$2:$N$5000),1), ('[OK DOE.xlsx]DOE'!$N$2:$N$5000) * ('[OK DOE.xlsx]DOE'!$M$2:$M$5000 = "Primary"),0))

将所有引用从一列更改I为另一列N，或者将辅助列放置在任何一列中。当然，您仍然需要将其作为CSE公式输入。

答案2

添加辅助列来计算排名的另一种方法是，对重复项进行调整：

J2: =RANK(I2,$I$2:$I$8, 1)+COUNTIF($I$2:I2,I2)-1

并根据需要向下填充。

该COUNTIF(…)-1函数通过添加“迄今为止”找到的重复项数量来创建唯一排名。还请注意，最高排名将是最高数字。

在此处输入图片描述

假设工作簿已打开，并且公式在同一工作表上，则以下公式将返回与我们在辅助列中生成的唯一排名相对应的学校名称：

=INDEX($B$2:$B$5000,MATCH(AGGREGATE(14,4,($M$2:$M$5000="PRIMARY")*($J$2:$J$5000),Q2),$J$2:$J$5000,0))

在此处输入图片描述

如果我正确编辑了公式，以下内容包括您在原始文件中显示的工作簿/工作表引用：

=INDEX('[OK DOE.xlsx]DOE'!$B$2:$B$5000,MATCH(AGGREGATE(14,4,('[OK DOE.xlsx]DOE'!$M$2:$M$5000="PRIMARY")*('[OK DOE.xlsx]DOE'!$J$2:$J$5000),Q2),'[OK DOE.xlsx]DOE'!$J$2:$J$5000,0))

更改Q2为引用包含您要返回的学校所需排名的单元格。

请注意，使用此公式，占 34% 的小学的顺序与您在示例中显示的顺序相反。如果这是一个问题，可以添加其他逻辑，但由于它们在技术上都是同一等级，因此顺序应该无关紧要。

相关内容