好吧,我觉得有点傻,因为两年前我问过是否要增加第二个标准在这个公式中,现在我添加第三个,我又被难住了。
我希望满足的三个标准是:
- 我想匹配数据表中 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,并填充。
根据需要调整公式中的单元格引用。