按月份提取每月最后一条记录结果异常求解

工作表示例截图

你当前用的公式没有加月份范围的匹配规则，只会定位全年最晚日期对应的记录，所以不管输入什么年份、什么日期参数，返回的都是全年最后一个月的数值，拿不到对应月份的结果。

根据你用的Excel版本选就行：

• Excel 365/2021及以上（支持动态数组）
  把公式里的范围替换成你自己表格的实际范围，输入后按回车就能自动返回指定年份1-12月的所有月末值，不用手动拉填充：

  =LET(
    数据源, A:B, // 替换成你存日期、对应数值的列范围
    查询年份, D1, // 替换成你输入要查询年份的单元格
    全年数据, FILTER(数据源, YEAR(INDEX(数据源,,1))=查询年份),
    日期列, INDEX(全年数据,,1),
    数值列, INDEX(全年数据,,2),
    月份序列, SEQUENCE(12),
    BYROW(月份序列, LAMBDA(m,
      当月月末, EOMONTH(DATE(查询年份,m,1),0),
      XLOOKUP(当月月末, 日期列, 数值列, 0, -1)
    ))
  )
  

• Excel 2019及更早版本
  在放1月结果的单元格输入下面的公式，按Ctrl+Shift+Enter三键确认数组公式，之后向右/向下拖动填充就能得到12个月的结果：

  =LOOKUP(1,0/(YEAR($A:$A)=$D$1)*(MONTH($A:$A)=COLUMN(A1))*($A:$A<=EOMONTH(DATE($D$1,COLUMN(A1),1),0)),$B:$B)
  

调整提示：公式里的$A:$A替换成你实际的日期列，$B:$B替换成存目标数值的列，$D$1替换成你填查询年份的单元格；如果你的12个月份是纵向排列的，把公式里的COLUMN(A1)改成ROW(A1)就行，会自动按顺序生成1-12的月份序号。

• 别直接用MAX(日期列)当匹配条件，这个值固定是全年最后一天，永远只会返回12月的数
• 查不同年份的数据必须同时匹配年份、月份两个条件，只匹配月份会把不同年份同月份的数据混在一起
• 要是当月没有正好是月末日期的记录，上面的公式会自动找当月最后一条有记录的数值返回，不用你手动补全所有日期

内容的提问来源于stack exchange，提问作者Mikel L.A.