☰

Excel 索引匹配和日期数组公式返回相同的值

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 索引匹配和日期数组公式返回相同的值

我正在尝试使用 INDEX MATCH 和数组公式从第一列获取下一列显示数字 1 的所有日期。

我似乎找不到我做错的地方，返回的日期重复多次。我只想要每个日期一次。

公式：

{=INDEX(D1:E7, MATCH(1,E1:E7,0), 1)}

D1:E7 contain from 01.01.2022 to 01.07.2022

E1:E7 contain 0,1,0,1,1,0,1

结果 ：

02.02.2022
02.02.2022
04.02.2022
04.02.2022
05.02.2022
07.02.2022
07.02.2022

预期结果 ：

02.02.2022
04.02.2022
05.02.2022
07.02.2022

谢谢

答案1

此答案与 2021 年之前的 Excel 版本有关。如果您使用的是最新版 Excel，请参阅其他答案，其中提供了更简单的公式。

您得到这个结果是因为您没有锁定范围D1:D7和E1:E7。

在这里您可以看到结果和产生它们的公式

02.01.2022  {=INDEX(D1:D7,MATCH(1,E1:E7,0),1)}
02.01.2022  {=INDEX(D2:D8,MATCH(1,E2:E8,0),1)}
04.01.2022  {=INDEX(D3:D9,MATCH(1,E3:E9,0),1)}
04.01.2022  {=INDEX(D4:D10,MATCH(1,E4:E10,0),1)}
05.01.2022  {=INDEX(D5:D11,MATCH(1,E5:E11,0),1)}
07.01.2022  {=INDEX(D6:D12,MATCH(1,E6:E12,0),1)}
07.01.2022  {=INDEX(D7:D13,MATCH(1,E7:E13,0),1)}

使用评估公式工具查看每个计算步骤

我猜你使用的是旧版本的 Excel（因为你仍在使用数组公式）。所以我们必须省略新的有用公式，如SORT或UNIQUE。

使用此数组公式并将其向下拉伸

{=IFERROR(INDEX($D$1:$D$7,SMALL($E$1:$E$7*ROW($E$1:$E$7),ROWS($D$1:$D$7)-SUM($E$1:$E$7)+ROW(D1)-ROW($D$1)+1)),"")}

怎么运行的：

  1. $E$1:$E$7*ROW($E$1:$E$7)返回一个数组，其中行号在范围内为 1 E1:E7，其他为 0。在我们的例子中：0,2,0,4,5,0,7
  2. 然后我们使用SMALL 数组函数来1.获取大于零的值。你可以想象对SMALL输入数组进行排序并返回给定位置的值。在我们的例子中是排序数组0,0,0,2,4,5,7
  3. 现在我们要跳过前导零。ROWS($D$1:$D$7)-SUM($E$1:$E$7)在我们的例子中，这是通过返回来完成的7-4=3
  4. 我们想要从 中逐项获取SMALL，因此我们需要对下一行进行一些序列 (+1)。它由 完成ROW(D1)-ROW($D$1)。如果你将它向下拉伸，它会给出序列0,1,2...
  5. 综合2-4起来，我们得到ROWS($D$1:$D$7)-SUM($E$1:$E$7)+ROW(D1)-ROW($D$1)+1)代表所需项目在SMALL排序数组中的位置（参见1）
  6. 现在我们有公式，向下延伸返回行号，其中 1 在E:E7 SMALL($E$1:$E$7*ROW($E$1:$E$7),ROWS($D$1:$D$7)-SUM($E$1:$E$7)+ROW(D1)-ROW($D$1)+1)
  7. 所以我们可以在函数6中使用这个公式。INDEXINDEX($D$1:$D$7,SMALL($E$1:$E$7*ROW($E$1:$E$7),ROWS($D$1:$D$7)-SUM($E$1:$E$7)+ROW(D1)-ROW($D$1)+1))
  8. 如果您尝试将公式从 延伸到比中7的值更多的行，则会出现错误。如果出现错误，函数会返回空文本，从而解决此问题。1E1:E7IFERROR""

附言：如果 D 列和 E 列中的数据不是从第一行开始的，则需要调整公式。但我确信，您现在知道如何做了。

答案2

如果您有最新版本的 Excel，则可以使用FILTER，如下所示：

在此处输入图片描述

G2 中的公式：

=FILTER($D$1:$D$7,$E$1:$E$7=1)

相关内容