如何向索引匹配大公式添加第 3 个标准?

如何向索引匹配大公式添加第 3 个标准?

好吧,我觉得有点傻,因为两年前我问过是否要增加第二个标准在这个公式中,现在我添加第三个,我又被难住了。

我希望满足的三个标准是:

  • 我想匹配数据表中 G 列中的州(在本地表的 $A$2 中列出)。
  • 我想匹配数据表 Q 列中的学校类型(硬编码,在本例中为“小学”)。
  • 我想匹配数据表中 BF 列中的最大数字。

以下是我提取的数据: 在此处输入图片描述

排名辅助列打破了数据中的任何联系。

以下是包含信息的报告表(位于单独的电子表格中): 在此处输入图片描述

因此,报告表中的 D46 将显示会员比例最高的亚利桑那州小学的名称(BF)。D47 将显示会员比例第二高的亚利桑那州小学的名称,等等。

这是我的工作公式,显示最大数字和状态:

=INDEX('[DOE Data.xlsx]Data'!$A$2:$A$40000,MATCH(LARGE(IF('[DOE Data.xlsx]Data'!$G$2:$G$40000=$A$2,'[DOE Data.xlsx]Data'!$BF$2:$BF$40000),4),IF('[DOE Data.xlsx]Data'!$G$2:$G$40000=$A$2,'[DOE Data.xlsx]Data'!$BF$2:$BF$40000),0))

现在我想让它也检查学校类型(即“小学”),我尝试了两种方法,但都没有用。

一开始我想把 IF 函数改成 IFS 函数,然后添加另一个条件。但没成功:

=INDEX('[DOE Data.xlsx]Data'!$A$2:$A$40000,MATCH(LARGE(IFS('[DOE Data.xlsx]Data'!$G$2:$G$4000=$A$2,'[DOE Data.xlsx]Data'!$BF$2:$BF$4000,'[DOE Data.xlsx]Data'!$Q$2:$Q$4000="Elementary",'[DOE Data.xlsx]Data'!$BF$2:$BF$4000),2),IFS('[DOE Data.xlsx]Data'!$G$2:$G$4000=$A$2,'[DOE Data.xlsx]Data'!$BF$2:$BF$4000,'[DOE Data.xlsx]Data'!$Q$2:$Q$4000="Elementary",'[DOE Data.xlsx]Data'!$BF$2:$BF$4000),0)

然后我尝试使用两个 IF 函数:

=INDEX('[DOE Data.xlsx]Data'!$A$2:$A$4000,MATCH(LARGE(IF('[DOE Data.xlsx]Data'!$G$2:$G$4000=$A$2,'[DOE Data.xlsx]Data'!$BG$2:$BG$4000) * IF('[DOE Data.xlsx]Data'!$Q$2:$Q$4000="Elementary",'[DOE Data.xlsx]Data'!$BG$2:$BG$4000),2),('[DOE Data.xlsx]Data'!$BG$2:$BG$4000),0))

还是没用。显然我做错了。两个公式都只给了我 #N/A。帮忙吗?

答案1

我想建议一个数组(CSE)公式,根据三个标准提取多行。

{=IFERROR(INDEX($A$2:$C$25, LARGE(IF(COUNTIF($A$2:$A$25,$A$1)*COUNTIF($B$2:$B$25,$B$1)*COUNTIF($C$2:$C$25,$C$1), ROW($A$2:$C$25)-MIN(ROW($A$2:$C$25))+1), ROW(A1)), COLUMN(A1)),"")}

注意:

  • 不要使用像这样的“基本”标准(这会使公式成为硬核),最好使用单元格引用作为标准,这会使公式动态化。

  • 并给予您自由,如果您需要更改条件,则编辑包含条件的单元格,而不是公式。

  • 单元格 A1、B1 和 C1 包含标准。

  • 完成配方Ctrl+Shift+Enter,并填充。

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


:编辑:

  • 这部分是根据 OP 最近发布的屏幕截图得出的,其中 OP 想要获得亚利桑那州小学中 5 个最大百分比。
  • 为了解决这个问题,OP 的原始公式需要进行一些修正。

在此处输入图片描述

怎么运行的:

  • 输入值 (1 至 5) 在辅助列 G 中,如所附屏幕截图所示。

  • 值 1 到 5 支持公式以获取 5 个最大值,作为 LARGE 函数的数组使用。

  • 单元格 A174 中的数组 (CSE) 公式:

    {=IFERROR(INDEX(A$165:A$170,MATCH(LARGE(IF($C$165:$C$170=$A$172,IF($F$165:$F$170="Elementary",$E$165:$E$170)),$G181),IF($C$165:$C$170=$A$172,$E$165:$E$170),0)),"")}
    
  • 完成配方Ctrl+Shift+Enter,并填充。

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

相关内容