☰

具有重复值和空白单元格的索引匹配

tag-icon • tag-icon microsoft-excel
具有重复值和空白单元格的索引匹配

我试图引用一行、不同的状态及其对应的日期。这些值中的每一个都存储在具有关联（=重复）引用的列​​中，并且每个状态下都有空白单元格。唯一的好处是，每个对应状态下的值都是从大到小排列的。因此，我需要匹配特定条件来检索第 N 个（最小或最大）值，而不是第一个匹配项。

图像问题

答案1

这是使用数组公式的一种方法。我没有使用标题值（已启动、已撤回等），而是为每列创建了单独的公式。这简化了方法，除非您有很多状态列，否则不会成为负担。我还假设您希望这些公式从第 3 行开始。

样本

注意：这两个都是数组公式，您需要输入Ctrl+ Shft+ Enter(CSE)。

把它们放入指定的单元格CSE，然后拖下来。

K3：

{=IFERROR(INDEX($E$2:$E$14,MATCH(1,(I3=$C$2:$C$14)*(MIN((IF($E$2:$E$14="",1000000,$E$2:$E$14))*(IF(I3=$C$2:$C$14,1,1000000)))=$E$2:$E$14),0),0),"")}

为了使MIN在数组公式中正常工作，我不得不使用当它与您的引用不匹配或为空时IF返回的语句。该值只是一个任意数字，只需大于任何日期值即可。（请记住，日期以数字形式存储，并且可能是 4637 年的某个日期！）如果没有这个，函数会找到数组中的最低值，即。1,000,0001,000,000MIN0

L3：

{=INDEX($F$2:$F$14,MATCH(1,(I3=$C$2:$C$14)*(MAX(($F$2:$F$14)*(I3=$C$2:$C$14))=$F$2:$F$14),0),0)}

请注意，如果未找到任何内容，则 L 列中的公式将返回0。您无需使公式复杂化，只需将格式设置为dd.mm.yy;;;@，这将隐藏零值。如果未找到任何内容，则 K 列中的公式将返回错误，因此我将其包装在语句中IFERROR。

答案2

在此处输入图片描述

怎么运行的：

  1. 在单元格中写入此公式G2并向下填充。

    {=IFERROR(INDEX($A$2:$F$11, MATCH(0,COUNTIF($G$1:G1, $A$2:$A$11), 0)),"")}

注意： 这是一个数组公式，必须用 进行确认 Ctrl+Shift+Enter。

  1. 在单元格中H2写入此数组公式，然后确认Ctrl+Shift+Enter并向下填充。

     {=Text(MIN(IF(($A$2:$A$11=G2)*($C$2:$C$11>0),$C$2:$C$11," ")),"dd.mm.yy;;;@")}

  2. 在单元格中写入此数组公式，I2 最后使用Ctrl+Shift+Enter& 向下填充。

     {=Text(MAX(IF(($A$2:$A$11=G2)*($D$2:$D$11>0),$D$2:$D$11,"")),"dd.mm.yy;;;@")}

  3. 单元格中的数组公式J2必须以Ctrl+Shift+Enter& 向下填充。

    {=IFERROR(INDEX($E$2:$E$11, MATCH(0,COUNTIF($J$1:J1, $E$2:$E$11), 0)),"")}

笔记：

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

答案3

亲爱的，谢谢你的回答，我已经找到了一些更简单的方法来匹配和摘录这些数据。

检索最小值的公式：=IFERROR(LOOKUP(1000000;1/(($A$2:$A$3169=G2)*($B$2:$B$3169<>""));$B$2:$B$3169);"")

检索最大值的公式 =IFERROR(INDEX(D$2:D$3149;MATCH(1;--($A$2:$A$3149=$G2)*--(D$2:D$3149<>"");0);1);"")

问候，解决方案

相关内容