Google Sheets非空时自定义拼接单元格与表头值的显示顺序

实现目标 #

• 按表头前缀的数字对内容做自定义排序展示
• 表头无前置数字（或前缀为非数字通配符）时，隐藏对应表头与值对
• 值为空的单元格不生成对应展示行，避免空内容占位

原有公式 #

原有公式仅能按列原有顺序拼接内容，无法实现排序、过滤无编号表头的需求，代码如下：

INDEX(TRIM(SUBSTITUTE(FLATTEN(QUERY(TRANSPOSE(IF(A2:D6="",,"♦"&A1:D1&" "&A2:D6)),,9^9)), "♦", CHAR(10))))

修改后可用公式 #

适配表头范围为A1:D1、数据范围为A2:D6的场景，直接粘贴即可使用：

=INDEX(
  TRIM(
    SUBSTITUTE(
      FLATTEN(
        QUERY(
          TRANSPOSE(
            LET(
              表头区域, A1:D1,
              数据区域, A2:D6,
              排序序号, IFERROR(REGEXEXTRACT(FLATTEN(表头区域), "^\d+")*1, 9^9),
              过滤后列, FILTER(SEQUENCE(COLUMNS(表头区域)), 排序序号<>9^9),
              排序列表, SORT({排序序号, 过滤后列}, 1, 1),
              排序后表头, CHOOSECOLS(表头区域, INDEX(排序列表,,2)),
              排序后数据, CHOOSECOLS(数据区域, INDEX(排序列表,,2)),
              清洗后表头, REGEXREPLACE(FLATTEN(排序后表头), "^\d+_?", ""),
              IF(排序后数据="",, "♦"&清洗后表头&" "&排序后数据)
            )
          ),,9^9
        )
      ),
      "♦", CHAR(10)
    )
  )
)

公式逻辑说明 #

• 自动识别表头开头的数字作为排序权重，无法提取到前置数字的列会被直接过滤，不会出现在最终结果中
• 对保留的列按提取到的数字做升序排列，数字越小对应内容展示位置越靠前
• 自动剔除表头中的数字前缀和分隔用下划线，最终展示纯表头文本
• 自动跳过值为空的单元格，不会生成空行
• 最终拼接结果以换行分隔，每个表头值对单独占一行

使用注意事项 #

• 如果实际表格的表头、数据范围和示例不一致，只需修改公式中表头区域、数据区域两个参数对应的单元格范围即可
• 表头命名规则：需要展示的列使用数字_表头名格式（例如1_Name、4_Age），不需要展示的列不要添加数字前缀（例如_History）即可自动隐藏

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