Google Sheets含缺失日期的VLOOKUP公式结果为0的修复方案

场景说明 #

• A列：通过Supermetrics拉取的日期（存在大量缺失）
• B列：对应A列日期所属月份的月度预算
• D列：完整的日期序列
• E列：需要计算对应日期的每日预算（逻辑为「月度预算 ÷ 当月天数」）

原公式因A列日期缺失，无法正确关联对应月份的预算与天数，导致计算结果出现0值。以下是无需新增列的修改方案：

修改后的公式 #

=ARRAYFORMULA(
  IF(
    ISBLANK(D3:D),,
    (XLOOKUP(
      TEXT(D3:D, "YYYYMM"),
      QUERY(A3:B, "SELECT TEXT(A, 'YYYYMM'), B WHERE A IS NOT NULL GROUP BY TEXT(A, 'YYYYMM'), B", 0),
      2,
      0
    )) / DAY(EOMONTH(D3:D, 0))
  )
)

公式逻辑拆解 #

1. 过滤空行：外层IF(ISBLANK(D3:D),, ...)确保D列无日期的行返回空值，避免无效计算
2. 统一月份格式：TEXT(D3:D, "YYYYMM")将完整日期序列转换为「年月」格式（如202405），用于精准匹配月份
3. 提取有效预算映射：QUERY(A3:B, "SELECT TEXT(A, 'YYYYMM'), B WHERE A IS NOT NULL GROUP BY TEXT(A, 'YYYYMM'), B", 0)从A/B列中提取去重的「年月-月度预算」对应关系，自动过滤A列空值的行
4. 匹配月度预算：XLOOKUP根据D列的年月，从QUERY结果中匹配对应的月度预算
5. 计算每日预算：用DAY(EOMONTH(D3:D, 0))获取当前日期所在月份的总天数，将匹配到的月度预算除以天数得到每日预算

替代方案（使用VLOOKUP） #

如果更习惯用VLOOKUP，可使用以下公式：

=ARRAYFORMULA(
  IF(
    ISBLANK(D3:D),,
    VLOOKUP(
      TEXT(D3:D, "YYYYMM"),
      UNIQUE(FILTER({TEXT(A3:A, "YYYYMM"), B3:B}, A3:A<>"")),
      2,
      FALSE
    ) / DAY(EOMONTH(D3:D, 0))
  )
)

内容的提问来源于stack exchange，提问作者Muhammad Rafif