如何使用Google Sheets公式将数据矩阵转为关系表（后续问题）

报错根因 #

Error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows 报错的核心原因是多数组拼接时，不同数组的输出行列数不匹配。此前适配小数据集的旧公式多采用固定行列计数、REPT+SPLIT的逻辑实现数据重复，大数据量下如果存在空值、行列计数偏移，就会出现数组行数不匹配的问题。

通用解决方案 #

使用谷歌表格新版LET+TOCOL组合公式替换原有逻辑，支持任意大小的数据集，不存在行列计数偏差问题：

=LET(
  // 按需调整下方3个参数即可
  data_range,A1:AF198,          // 你的原始矩阵数据范围
  has_header,TRUE,              // 原始数据是否包含表头行，是填TRUE，否填FALSE
  has_row_key,TRUE,             // 原始数据第一列是否为行主键，是填TRUE，否填FALSE
  // 以下逻辑无需修改
  row_cnt,ROWS(data_range),
  col_cnt,COLUMNS(data_range),
  header,IF(has_header,INDEX(data_range,1,SEQUENCE(col_cnt-(has_row_key*1),,1+has_row_key)),"属性_"&SEQUENCE(col_cnt-(has_row_key*1))),
  row_key,IF(has_row_key,INDEX(data_range,SEQUENCE(row_cnt-(has_header*1),,1+has_header),1),SEQUENCE(row_cnt-(has_header*1))),
  value_area,OFFSET(data_range,has_header*1,has_row_key*1,row_cnt-(has_header*1),col_cnt-(has_row_key*1)),
  flat_value,TOCOL(value_area,0),
  match_row_key,TOCOL(IF(SEQUENCE(ROWS(value_area)),row_key,),,TRUE),
  match_header,TOCOL(IF(SEQUENCE(COLUMNS(value_area)),header,),TRUE),
  output_header,{"行标识","列属性","数值"},
  {output_header;HSTACK(match_row_key,match_header,flat_value)}
)

注意事项 #

• 公式仅需修改data_range、has_header、has_row_key三个参数即可适配不同的数据集场景
• 若不需要保留空值对应的行列数据，可将TOCOL(value_area,0)的第二个参数改为1，同时将TOCOL(IF(SEQUENCE(ROWS(value_area)),row_key,),,TRUE)的第三个参数改为2，TOCOL(IF(SEQUENCE(COLUMNS(value_area)),header,),TRUE)的第二个参数改为2
• 该公式最大支持十万级行的数据集运算，不会触发数组长度不匹配报错

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