Google Sheets如何将行列双表头二维表转为一维行数据并过滤0值

不用写脚本，单公式就能搞定自动更新、过滤0值的需求，操作很简单：

• 先新建个空白工作表存转换结果，点中这个新表的A1单元格
• 把下面的公式粘进去按回车就行

要是你的源数据没单独放在叫「源数据」的工作表里，把公式里所有源数据!的前缀删掉，确保引用范围能覆盖你所有的源数据就可以

=LET(
  data_range, 源数据!A1:ZZ,
  jobs, FILTER(CHOSECOLS(data_range,1), CHOSECOLS(data_range,1)<>""),
  dates, FILTER(CHOOSEROWS(data_range,1), CHOOSEROWS(data_range,1)<>"Job Name"),
  values, FILTER(CHOSECOLS(data_range, SEQUENCE(COLUMNS(dates),1,2)), CHOSECOLS(data_range,1)<>""),
  total_jobs, ROWS(jobs),
  total_dates, COLUMNS(dates),
  flat_dates, TOCOL(IF(SEQUENCE(total_jobs), dates), 1),
  flat_jobs, TOCOL(IF(SEQUENCE(1, total_dates), jobs), 1),
  flat_usage, TOCOL(values, 1),
  valid_records, FILTER(HSTACK(flat_dates, flat_jobs, flat_usage), flat_usage<>0),
  VSTACK({"Date","Job Name","Usage"}, valid_records)
)

效果说明 #

• 之后你在源表加新的日期列、加新的Job行、改单元格数值，转换后的结果会自动刷新，不用重复操作
• 自动过滤所有用量为0的记录，和你给的示例输出完全匹配
• 会自动跳过源表里的空白行、空白列，不会生成没用的空数据
• 公式里写的源数据!A1:ZZ是留的冗余范围，你可以根据自己的实际数据量调整，只要能覆盖未来要新增的行和列就不用改公式

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