Google Sheets交叉制表转明细列表并筛选招聘需求>0的方法

方案1：QUERY+FLATTEN+SPLIT组合公式 #

这是最简洁的实现方式，通过拼接+拆分快速生成明细：

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      IF(
        B2:D>0,
        A2:A&"|"&B1:D1&"|"&B2:D,
        ""
      )
    )
  ),
  "SELECT SPLIT(Col1, '|') WHERE Col1 <> ''",
  0
)

逻辑说明： #

1. ARRAYFORMULA遍历交叉表区域，仅对招聘需求>0的单元格，将「部门|岗位|需求」拼接成字符串
2. FLATTEN把二维拼接结果转成一维列表
3. QUERY拆分字符串为三列，并过滤掉空条目

方案2：LAMBDA+MAKEARRAY自定义生成明细 #

适合需要更灵活控制的场景，通过数组函数生成所有组合后再筛选：

=LET(
  depts, A2:A,
  roles, B1:D1,
  needs, B2:D,
  totalCombos, COUNTA(depts)*COUNTA(roles),
  allRows, MAKEARRAY(
    totalCombos,
    3,
    LAMBDA(rowNum, colNum,
      LET(
        deptPos, CEILING(rowNum/COUNTA(roles), 1),
        rolePos, MOD(rowNum-1, COUNTA(roles))+1,
        currentNeed, INDEX(needs, deptPos, rolePos),
        IF(
          currentNeed>0,
          SWITCH(
            colNum,
            1, INDEX(depts, deptPos),
            2, INDEX(roles, rolePos),
            3, currentNeed
          ),
          ""
        )
      )
    )
  ),
  FILTER(allRows, INDEX(allRows, 0, 3)>0)
)

逻辑说明： #

1. LET定义变量，简化后续引用
2. MAKEARRAY生成所有部门-岗位的组合行
3. 对每一行判断需求是否>0，符合条件则填充对应值
4. FILTER最终筛选出需求>0的有效条目

操作步骤： #

1. 在表格空白区域（比如F1单元格）输入上述任一公式
2. 公式会自动生成包含「部门、岗位、招聘需求」的明细列表，仅保留需求大于0的条目

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