☰

测试多个 INDEX/MATCH 结果而不查看它们

tag-icon • tag-icon microsoft-excel worksheet-function
测试多个 INDEX/MATCH 结果而不查看它们

我需要识别每个列标题，然后根据另一组标准对其进行测试，而无需实际显示结果。

示例：
每个人都被要求组建一支保龄球队（由四人组成）。每支球队必须至少有一名女性。每个人都计算自己的人数，然后为球队的其他三名成员填写“Y”。

我需要一个公式来识别团队的四名成员，然后根据主性别列表进行检查，如果有女性，则返回“True”。

数据表

答案1

我会使用辅助行来完成此操作。
您可以将其添加到范围上方/下方，使用公式使其保持最新状态，也可以隐藏它。辅助行中的公式：
=IF(INDEX($N$3:$N$11,MATCH(B2,$M$3:$M$11,0))="f",1,0)

检查团队是否至少包含一名女性的公式：
=SUM((B3:J3="y")*($B$1:$J$1))>0
这是一个数组公式，因此输入后按 CTRL+SHIFT+ENTER。

在此处输入图片描述

答案2

感谢所有查看并考虑回复的人。昨天，当我继续研究这个问题时，我得出结论，我的方法不正确。我最终修改了表格布局 - 基本上将“y”放在 X 轴上。最终公式包含的内容比我最初要求的要多得多，但最终我发现 SMALL 函数是关键；在这个版本中，我将其设置为返回每次出现的值（我只期望三次），并为每个出现创建一个公式。之后，我将其与 HLOOKUP 配对，如果返回错误，则大功告成！

=IF($L8="+",IF(AND(ISERROR(HLOOKUP(INDEX(INDIRECT("表格！"&ADDRESS(79,55+MATCH(E$6,表格!$BD$78:$CL$78,0))&":"&ADDRESS(130,55+MATCH(E$6,表格!$BD$78:$CL$78,0))),SMALL(IF(表格!$BC$79:$BC$117=$K8,ROW(表格!$BC$79:$BC$117)-ROW(INDEX(表格!$BC$79:$BC$117,1,1))+1),1)),$E$2:$K$3,1,FALSE)),ISERROR(HLOOKUP(INDEX(INDIRECT("表！"&ADDRESS(79,55+MATCH(E$6,表!$BD$78:$CL$78,0))&":"&ADDRESS(130,55+MATCH(E$6,表!$BD$78:$CL$78,0))),SMALL(IF(表!$BC$79:$BC$117=$K8,ROW(表!$BC$79:$BC$117)-ROW(INDEX(表!$BC$79:$BC$117,1,1))+1),2)),$E$2:$K$3,1,FALSE)),ISERROR(HLOOKUP(INDEX(INDIRECT("表！"&ADDRESS(79,55+MATCH(E$6,表!$BD$78:$CL$78,0))&":"&ADDRESS(130,55+MATCH(E$6,表!$BD$78:$CL$78,0))),SMALL(IF(表!$BC$79:$BC$117=$K8,ROW(表!$BC$79:$BC$117)-ROW(INDEX(表!$BC$79:$BC$117,1,1))+1),3)),$E$2:$K$3,1,FALSE))),"","X"),"") 作为 CSE。

答案3

我有一点变化Máté 的回答。

  • 创建一个辅助行，简单地复制列 M，即“性别”列，但转置（将每个人的性别放在他们的列中）。例如，使用第 24 行，放入

    =INDEX($M$1:$M$10, COLUMN())

    进入单元格 B24并拖动/填充到右侧的列 J。我们不需要使用MATCH()，因为如果（例如）我们在列 F（即第 6 列），这是 Bob 的列，那么 Bob 的性别在第 6 行。我们不需要做，IF() 因为我们可以在下一步中测试该值。

    正如 Máté 的回答一样，如果您愿意，可以隐藏此辅助行。

    或者，您可以M完全消除列并仅将性别存储在一行中。

  • 在第 2 行的某个位置（例如，单元格 N2），输入公式

    =SUMPRODUCT(--(B2:J2="y"), --(B$24:J$24="f"))

    SUMPRODUCT()，顾名思义，就是将一些数字相乘，然后相加。它本质上与 Máté 的 相同 ，但不需要以数组公式的形式输入。由于 Máté 没有解释他的答案：这将查看范围-中的每一列（成员的列）。它查看当前行（显示为 ）以查看成员（对应此列）是否是与此行对应的团队的成员。并且它查看第 24 行以查看成员是否为女性。使用转换为并将转换为。将它们相乘可得到该团队中每个女性成员的 ；然后将这些一和零相加即可得到团队中的女性人数。SUM(range1*range2)BJ2--TRUE1FALSE01

    当然，拖动/填充至第 10 行。

电子表格

当然，您可以测试计数是否为 0，可能使用条件格式。

相关内容