如何基于IMPORTRANGE结果按动物类型透视、分组与聚合

原始数据结构 #

通过IMPORTRANGE()获取的原始表格结构如下：

目标数据结构 #

需要转换为按动物类别聚合成年/幼年数量的结构，且过滤掉成年、幼年数量均为0的行：

整合公式方案 #

直接使用QUERY()嵌套IMPORTRANGE()，结合数组构造与行转列逻辑实现需求：

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      IMPORTRANGE("你的表格URL", "数据范围") & "|" & 
      {"犬类","猫类","鸟类",...} & "|" & 
      {INDEX(IMPORTRANGE("你的表格URL", "数据范围"),,4),INDEX(IMPORTRANGE("你的表格URL", "数据范围"),,6),INDEX(IMPORTRANGE("你的表格URL", "数据范围"),,8),...} & "|" & 
      {INDEX(IMPORTRANGE("你的表格URL", "数据范围"),,5),INDEX(IMPORTRANGE("你的表格URL", "数据范围"),,7),INDEX(IMPORTRANGE("你的表格URL", "数据范围"),,9),...}
    )
  ),
  "SELECT SPLIT(Col1,'|')[0],SPLIT(Col1,'|')[1],SPLIT(Col1,'|')[2],SPLIT(Col1,'|')[3],SUM(SPLIT(Col1,'|')[4]*1),SUM(SPLIT(Col1,'|')[5]*1) 
   WHERE SPLIT(Col1,'|')[4]*1 + SPLIT(Col1,'|')[5]*1 > 0 
   GROUP BY SPLIT(Col1,'|')[0],SPLIT(Col1,'|')[1],SPLIT(Col1,'|')[2],SPLIT(Col1,'|')[3] 
   LABEL SPLIT(Col1,'|')[0]'领养日期',SPLIT(Col1,'|')[1]'带回家日期',SPLIT(Col1,'|')[2]'姓名',SPLIT(Col1,'|')[3]'动物类别',SUM(SPLIT(Col1,'|')[4]*1)'成年数量',SUM(SPLIT(Col1,'|')[5]*1)'幼年数量'",
  1
)

公式细节说明 #

1. 优化重复调用：可以先把导入的数据存入单元格（比如A1）：=IMPORTRANGE("你的表格URL", "数据范围")，公式里用A1代替所有IMPORTRANGE部分，避免重复拉取数据。
2. 数组配对逻辑：
   • {"犬类","猫类","鸟类",...}要和原始表格的动物类别一一对应；
   • INDEX(...,4)对应「成年犬」列，INDEX(...,5)对应「幼犬」列，以此类推，按动物类别配对成年/幼年数据列；
3. 行转列处理：用FLATTEN把多维数组转成带分隔符|的单行字符串，再通过SPLIT拆分回列数据；
4. 过滤无效行：WHERE条件确保成年+幼年数量不为0的行才被保留；
5. 聚合与命名：通过GROUP BY按指定维度分组，SUM聚合数量，最后用LABEL给列设置自定义名称。

简化版（先存储导入数据） #

如果已将导入数据放在A1:Z（按需调整范围），公式可简化为：

=QUERY(
  FLATTEN(
    ARRAYFORMULA(
      A1:Z & "|" & 
      {"犬类";"猫类";"鸟类";...} & "|" & 
      {D:D,F:F,H:H,...} & "|" & 
      {E:E,G:G,I:I,...}
    )
  ),
  "SELECT SPLIT(Col1,'|')[0],SPLIT(Col1,'|')[1],SPLIT(Col1,'|')[2],SPLIT(Col1,'|')[3],SUM(SPLIT(Col1,'|')[4]*1),SUM(SPLIT(Col1,'|')[5]*1) 
   WHERE SPLIT(Col1,'|')[4]*1 + SPLIT(Col1,'|')[5]*1 > 0 
   GROUP BY SPLIT(Col1,'|')[0],SPLIT(Col1,'|')[1],SPLIT(Col1,'|')[2],SPLIT(Col1,'|')[3] 
   LABEL SPLIT(Col1,'|')[0]'领养日期',SPLIT(Col1,'|')[1]'带回家日期',SPLIT(Col1,'|')[2]'姓名',SPLIT(Col1,'|')[3]'动物类别',SUM(SPLIT(Col1,'|')[4]*1)'成年数量',SUM(SPLIT(Col1,'|')[5]*1)'幼年数量'",
  1
)

内容的提问来源于stack exchange，提问作者Aleister Tanek Javas Mraz