如何在Google Sheets中生成无空行的动态客户发票汇总？

问题描述 #

我有一个发票汇总与报告电子表格，需要为每个客户生成动态汇总（客户数量可变），要求每个客户的发票列表后紧跟其总计，而非将所有总计集中在末尾。

目前我用=sort(UNIQUE(E6:E))提取客户列表，并用以下数组QUERY公式实现汇总：

={query(Data!A5:I5,"select A,B,C,D,' ',F,G,H,I label ' ' ''");

IF(Data!N7<>"",
{{Data!N7,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N7&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N7&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});

IF(Data!N8<>"",
{{Data!N8,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N8&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N8&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});

IF(Data!N9<>"",
{{Data!N9,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N9&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N9&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});

IF(Data!N10<>"",
{{Data!N10,"","","","","","","",""};query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&Data!N10&"' label ' ' ''");query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&Data!N10&"'");{"___________________________________________________________________________________________________________________________________________","","","","","","","",""}},
{"","","","","","","","",""});

{"","","","",Data!O6,Data!J6,Data!K6,Data!L6,Data!M6}}

但当前实现会因客户不存在出现空行，求优化方案消除这些空行。

优化方案 #

核心思路是直接基于提取的客户列表动态生成每个客户的数据块，再合并所有块并过滤空行，彻底避免手动指定客户单元格带来的空行问题。

替换原有公式为以下内容：

=LET(
  headers, query(Data!A5:I5,"select A,B,C,D,' ',F,G,H,I label ' ' ''"),
  clients, SORT(UNIQUE(Data!E6:E)),
  client_blocks, MAP(clients, LAMBDA(client,
    IF(client="",,
      VSTACK(
        {client,"","","","","","","",""},
        query(Data!A6:Y,"select A,B,C,D,' ',F,G,H,I where A is not null and E='"&client&"' label ' ' ''"),
        query(Data!J6:Y,"select V,W,X,Y,O,J,K,L,M where N='"&client&"'"),
        {"___________________________________________________________________________________________________________________________________________","","","","","","","",""}
      )
    )
  )),
  final_total, {"","","","",Data!O6,Data!J6,Data!K6,Data!L6,Data!M6},
  FILTER(VSTACK(headers, TOCOL(client_blocks,1), final_total), BYROW(VSTACK(headers, TOCOL(client_blocks,1), final_total), LAMBDA(row, NOT(AND(row="")))))
)

关键优化点 #

• 动态适配客户数量：用MAP遍历SORT(UNIQUE(Data!E6:E))返回的所有有效客户，自动跳过空客户，无需手动维护N7-N10这类固定单元格。
• 自动过滤空块：TOCOL(client_blocks,1)会自动忽略MAP生成的空数组，再通过FILTER+BYROW移除所有全空行。
• 保留原有格式逻辑：完全继承了原有的表头、客户标题、发票列表、客户总计、分隔线以及最终总计的展示样式。

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