Google Sheets数据透视表问题：基于Query函数统计各城市2018-2020年度留存人员数的公式调试求助

你的核心问题在于没有先将跨年份在职的人员展开到每个对应的统计年份，原公式的pivot year(Col2)只会把人员归到入职年份，而无法覆盖他们在职的所有年份；另外WHERE条件的括号嵌套也有语法错误。

解决方案思路 #

要实现“某人员在2018-2020年中每个在职年份都被统计”，需要先把每个员工的记录拆分成对应所有在职年份的多行数据，再对拆分后的数据集做透视统计。

完整可用公式 #

=ARRAYFORMULA(
  LET(
    // 定义原始数据范围
    raw_data, $A$2:$C$10,
    // 提取各列数据
    city_col, INDEX(raw_data,,1),
    start_year, YEAR(INDEX(raw_data,,2)),
    end_year, YEAR(INDEX(raw_data,,3)),
    // 定义需要统计的目标年份（2018-2020）
    target_years, SEQUENCE(3, 1, 2018),
    // 生成城市、入职年、离职年与目标年份的交叉组合
    cross_data, FLATTEN(city_col & "|" & start_year & "|" & end_year & "|" & target_years),
    // 拆分交叉组合为列
    split_data, SPLIT(cross_data, "|"),
    // 筛选出目标年份在入职-离职区间内的有效记录
    valid_records, FILTER(
      split_data,
      INDEX(split_data,,4) >= INDEX(split_data,,2),
      INDEX(split_data,,4) <= INDEX(split_data,,3)
    ),
    // 对有效记录做透视统计并排序
    QUERY(
      valid_records,
      "select Col1, count(Col1) 
       group by Col1 
       pivot Col4 
       order by Col4 desc, Col3 desc, Col2 desc 
       label Col1 'Between'",
      1
    )
  )
)

公式解释 #

1. LET函数：用来定义变量，让公式结构更清晰，避免重复引用数据。
2. 交叉组合生成：通过FLATTEN将每个城市记录与2018-2020年做交叉，得到每条记录对应3个年份的临时数据。
3. 筛选有效记录：只保留目标年份在入职年份和离职年份之间的记录，比如入职2015、离职2020的员工，会保留2018、2019、2020三条有效记录。
4. QUERY透视统计：按城市分组，按目标年份做透视，最后按年份降序排序，并重命名第一列为Between。

原公式问题分析 #

1. WHERE条件语法错误：原公式的括号嵌套缺失，正确的条件应该是(2018>=YEAR(Col2) and 2018<=YEAR(Col3)) or (2019>=YEAR(Col2) and 2019<=YEAR(Col3)) or (2020>=YEAR(Col2) and 2020<=YEAR(Col3))，但即使修正语法，也无法解决“单条记录对应多个年份”的统计问题。
2. Pivot维度错误：使用pivot year(Col2)只能将员工归到入职年份，无法覆盖他们在职的所有年份，必须先展开数据再透视。

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