使用QUERY函数将含重复值的有序数字按5行分组返回

要实现将含重复值的有序数字列表按5行一组展示（不足5的倍数时补充空行凑整），可以通过Google Sheets的函数组合完成，无需使用Apps Script。假设你的原始数据位于A2:A区域，可直接使用以下公式：

=ARRAYFORMULA(
  FLATTEN(
    BYROW(
      UNIQUE(FILTER(A2:A, A2:A<>"")),
      LAMBDA(current_val,
        LET(
          total_instances, COUNTIF(A2:A, current_val),
          empty_rows_needed, IF(MOD(total_instances,5)=0, 0, 5-MOD(total_instances,5)),
          total_rows, total_instances + empty_rows_needed,
          IF(SEQUENCE(total_rows) <= total_instances, current_val, "")
        )
      )
    )
  )
)

公式说明： #

• UNIQUE(FILTER(A2:A, A2:A<>""))：提取原始数据中的非空唯一值，确保每个数字只处理一次
• BYROW(..., LAMBDA(current_val, ...))：遍历每个唯一值，单独执行后续逻辑
• LET(...)：定义变量简化计算，分别得出当前数字的出现次数、需要补充的空行数、最终要生成的总行数
• IF(SEQUENCE(total_rows) <= total_instances, current_val, "")：生成对应行数的内容，前total_instances行显示原数字，剩余行显示空值
• FLATTEN：将分组处理后的多行结果平铺为单列

如果需要结合QUERY函数对原始数据先做筛选（比如过滤特定条件），可以将原始数据替换为QUERY的输出结果，示例如下：

=ARRAYFORMULA(
  FLATTEN(
    BYROW(
      UNIQUE(QUERY(A2:A, "WHERE A IS NOT NULL AND A > 0")),
      LAMBDA(current_val,
        LET(
          filtered_data, QUERY(A2:A, "WHERE A IS NOT NULL AND A > 0"),
          total_instances, COUNTIF(filtered_data, current_val),
          empty_rows_needed, IF(MOD(total_instances,5)=0, 0, 5-MOD(total_instances,5)),
          total_rows, total_instances + empty_rows_needed,
          IF(SEQUENCE(total_rows) <= total_instances, current_val, "")
        )
      )
    )
  )
)

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