如何在Google Sheets中筛选日期列提取每月最后可用日期？

问题分析 #

你之前用EOMONTH函数的核心问题是：这个函数返回的是自然月的最后一天（比如12月固定返回31日），但你需要的是数据中实际存在的该月最晚日期（比如你的12月数据里最晚是29日），所以函数逻辑不符合需求。另外你的原始日期是日/月/年的文本格式，需要先转换成Google Sheets可识别的日期值，才能进行分组和最大值计算。

解决方案 #

假设你的原始日期数据在A4:A列（和你之前公式的范围一致），以下两种方法都能实现需求：

方法1：使用QUERY函数（推荐，简洁直观） #

这个公式会自动按年月分组，提取每组的最大日期，再转换成你需要的日/月/年格式：

=ARRAYFORMULA(TEXT(QUERY(DATEVALUE(SUBSTITUTE(A4:A,"/","-")), "SELECT MAX(Col1) WHERE Col1 IS NOT NULL GROUP BY YEAR(Col1), MONTH(Col1) ORDER BY MAX(Col1) DESC"), "dd/mm/yyyy"))

公式拆解：

1. SUBSTITUTE(A4:A,"/","-")：把日期中的/替换成-，变成dd-mm-yyyy格式，方便DATEVALUE识别。
2. DATEVALUE(...)：将文本格式的日期转换成Google Sheets可计算的日期值。
3. QUERY(...)：按年、月分组，筛选每组的最大日期值，并按日期从新到旧排序。
4. TEXT(..., "dd/mm/yyyy")：把计算出的日期值转换成你需要的日/月/年显示格式。

方法2：使用MAXIFS+UNIQUE函数 #

如果更习惯用函数组合，也可以用以下公式：

=ARRAYFORMULA(TEXT(SORT(MAXIFS(DATEVALUE(SUBSTITUTE(A4:A,"/","-")), EOMONTH(DATEVALUE(SUBSTITUTE(A4:A,"/","-")),0), UNIQUE(EOMONTH(DATEVALUE(SUBSTITUTE(A4:A,"/","-")),0))),1,FALSE), "dd/mm/yyyy"))

公式拆解：

1. UNIQUE(EOMONTH(...),0)：提取所有唯一的年月（用当月最后一天代表整个月）。
2. MAXIFS(...)：针对每个唯一年月，筛选出该月的最大日期值。
3. SORT(...,1,FALSE)：将结果按日期从新到旧排序。
4. TEXT(...)：转换成目标显示格式。

验证结果 #

使用上述任意公式，都会得到你预期的输出：

3/01/2024
29/12/2023
24/11/2023
27/10/2023

内容的提问来源于stack exchange，提问作者Gonzalo García