google-sheets - 列出日期范围之间的每个月。谷歌查询

Question

我正在为一个我一直试图解决几个小时的问题寻求帮助，但徒劳无功。我正在使用谷歌电子表格上的谷歌查询（）。所以我有这种表（比如说A1：E3）

客户	银行单据	开始日期	结束日期	价值
约翰	#1	2021 年 10 月 14 日	2021 年 10 月 31 日	500
约翰	#2	2021 年 10 月 14 日	2021 年 12 月 31 日	500

为了更清楚，银行收据是具有开始日期和结束日期的值。因此，银行收据 #2 涵盖了 10 月、11 月和 12 月的 500。我正在尝试做的是一个按时间顺序排列的表（列中有月份），它向我显示了 john 在一年中的银行收据覆盖率，以便获得这种结果：

		十月	十一月	十二月
约翰	银行收据	1000	500	500

这是更长查询的第一步，但我需要以这种方式返回数据才能继续。

我试图寻找方法来返回每个月的日期范围，但只发现帖子谈论我无法使用的谷歌脚本。我还尝试使用 SEQUENCE() 似乎可以使用一些技巧，直到我从 2021/12/15 到 2022/03/31 并且由于年份的变化它返回了 14。所以我完全没有解决方案。我确切地说我是使用谷歌查询的新手，所以我可能会错过一些明显的事情。

非常感谢谁可以帮助

标签： google-sheetsgoogle-query-language

Answer 1

只是一个相当简单的 2 行 X 3 列演示，您可以在其上构建：

=ArrayFormula(mmult({1,1},if((C2:C3<=eomonth(G2,{0,1,2}))*(D2:D3>=eomonth(G2,{0,1,2})),E2:E3,0)))

稍后会给出更完整的解决方案。

---

这是公式的更动态版本

=ArrayFormula(mmult(sequence(1,counta(A2:A),1,0),
if((C2:index(C:C,counta(C:C))<=eomonth(G2,sequence(1,datedif(G2,H2,"M")+1,0)))*
(D2:index(D:D,counta(D:D))>=eomonth(G2,sequence(1,datedif(G2,H2,"M")+1,0))),E2:index(E:E,counta(E:E)),0)))

但它使用相同的原理

(1) 使用 eomonth 和序列来制作一个单行的月末数组（31/10/21、30/11/21、31/12/21 等）

(2) 将这些日期与数据中的日期范围进行比较，以查看每个范围中包含哪些日期，以及它们匹配的位置，生成一个二维数组，其中包含月份的数量和数据行的下降

(3) 使用 mmult 获取 2d 数组的列总计（这是获取此处显示的列总计的标准方法，用于 Excel 中）

我使用了一个更简单的公式来计算从 I1 开始的月份

=ArrayFormula(edate(G2,sequence(1,datedif(G2,H2,"M"),0)))