google-sheets - 在 Google 表格中，如何检查单元格 A（日期）是否在单元格 B 和 C 的日期范围内

Question

我有一张带有时间线的工作表，在 A 列中每行显示一个月，在 B 列中显示该月旁边的美元金额。

我希望能够在 G 列中指定金额，并在 E 和 F 列中指定该金额的开始和结束日期。

我想要实现的是，B列中的值是通过查看E列和F列中指定的开始日期和结束日期自动计算的，然后如果A列中的日期在日期之间，则从G列中获取相应的值在 E 和 F 中指定的范围。

这有点难以解释，所以我创建了一个演示，您可以在这里查看：https ://docs.google.com/spreadsheets/d/1vJkkEkNdLBIH6ifx_B7FJLiL9azJHyIuSRy90IRfPp4/edit?usp=sharing

我在网上找到了许多针对类似问题的建议，但无法让其中任何一个适用于我的具体案例。非常欢迎任何帮助

标签： google-sheetsgoogle-sheets-formula

Answer 1

您可以将其作为数组公式来执行，如下所示：

=ArrayFormula(mmult((text(indirect("A2:A"&count(A2:A)+1),"YYMM")>=text(TRANSPOSE(indirect("`E3:E"&count(E3:E)+2)),"YYMM"))*(text(indirect("A2:A"&count(A2:A)+1),"YYMM")<=text(transpose(indirect("F3:F"&count(F3:F)+2)),"YYMM"))*transpose(indirect("G3:G"&count(G3:G)+2)),(INDIRECT("G3:G"&count(G3:G)+2)+2)^0))

这个想法是开发一个二维数组，其中行是月份，列是匹配时间段的金额。然后使用标准的 Mmult 方法来获取数组的行总数。

对范围使用间接会使公式更长，但使用全列引用会很慢，因为它会导致默认大小的工作表接近 1000 X 1000 数组。

编辑 1

或者更短

=ArrayFormula(mmult((text(indirect("A2:A"&count(A2:A)+1),"YYMM")>=text(TRANSPOSE(indirect("E3:E"&count(E3:E)+2)),"YYMM"))
*(text(indirect("A2:A"&count(A2:A)+1),"YYMM")<=text(transpose(indirect("F3:F"&count(F3:F)+2)),"YYMM"))
,INDIRECT("G3:G"&count(G3:G)+2)))

因为您可以将行总计步骤与 G 列相乘。

编辑 2

或者，您可以使用 SUMIFS 使用更简单的下拉公式：

=ArrayFormula(sumifs(G$3:G,eomonth(E$3:E,-1)+1,"<="&A2,F$3:F,">="&A2))

这使用 Eomonth 将所有开始日期更改为当月的第一天，以便可以正确地将它们与 A 列中的日期进行比较。由于 Eomonth 计算，公式仍然必须作为数组公式输入。

笔记

与上面的原始数组公式等效的下拉公式将是

=ArrayFormula(sumifs(G$3:G,text(E$3:E,"YYMM"),"<="&text(A2,"YYMM"),text(F$3:F,"YYMM"),">="&text(A2,"YYMM")))

但这对所有行都为零 - 在撰写本文时，原因对我来说并不明显。