如何纵向合并多表同列数QUERY公式结果并按客户分组？

错误原因 #

当使用数组字面量（{'Day 1'!A:C;'Day 2'!A:C}）堆叠多个工作表区域时，QUERY函数无法识别原工作表的列名（A/B/C），必须通过Col1/Col2/Col3这类列索引来指代对应列。你原公式中用A引用列，导致QUERY无法识别，触发NO_COLUMN: A错误。

修正方案 #

方案1：直接修正列引用并处理表头 #

将QUERY语句中的列名替换为Col1/Col2/Col3，同时避免重复表头（仅保留第一个表的表头，后续表从第二行取数据）：

=query({
  'Day 1'!A:C;
  'Day 2'!A2:C;
  'Day 3'!A2:C;
  'Day 4'!A2:C;
  'Day 5'!A2:C;
  'Day 6'!A2:C;
  'Day 7'!A2:C;
  'Day 8'!A2:C;
  'Day 9'!A2:C;
  'Day 10'!A2:C;
  'Day 11'!A2:C;
  'Day 12'!A2:C;
  'Day 13'!A2:C;
  'Day 14'!A2:C;
  'Day 15'!A2:C;
  'Day 16'!A2:C;
  'Day 17'!A2:C;
  'Day 18'!A2:C;
  'Day 19'!A2:C;
  'Day 20'!A2:C;
  'Day 21'!A2:C;
  'Day 22'!A2:C;
  'Day 23'!A2:C;
  'Day 24'!A2:C;
  'Day 25'!A2:C;
  'Day 26'!A2:C;
  'Day 27'!A2:C;
  'Day 28'!A2:C;
  'Day 29'!A2:C;
  'Day 30'!A2:C;
  'Day 31'!A2:C
}, "select Col1, Col2, Col3 where Col1 matches '"&textjoin("|",1,E2:E)&"' group by Col1, Col2, Col3", 1)

• 最后一个参数1表示第一行是表头；
• 后续表用A2:C跳过自身表头，避免数据中混入重复表头行；
• group by Col1, Col2, Col3会按客户+具体数据分组，若需按客户汇总（如求和销售额），可改为select Col1, sum(Col2), sum(Col3) group by Col1。

方案2：先过滤单表再合并（更高效） #

先对每个表用QUERY筛选目标客户，再堆叠合并，最后统一分组：

=query({
  query('Day 1'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 1);
  query('Day 2'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 3'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 4'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 5'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 6'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 7'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 8'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 9'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 10'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 11'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 12'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 13'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 14'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 15'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 16'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 17'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 18'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 19'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 20'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 21'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 22'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 23'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 24'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 25'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 26'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 27'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 28'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 29'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 30'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0);
  query('Day 31'!A:C, "select A,B,C where A matches '"&textjoin("|",1,E2:E)&"'", 0)
}, "select Col1, sum(Col2), sum(Col3) group by Col1 label sum(Col2)'总销售额', sum(Col3)'总数量'", 1)

• 第一个表的QUERY用参数1保留表头，后续表用0跳过表头；
• 外层QUERY直接按客户（Col1）汇总，并用label重命名聚合列，更符合分类账需求。

关键注意事项 #

• textjoin("|",1,E2:E)会将E列的客户名拼接成正则匹配字符串，若客户名包含正则特殊字符（如()、[]、*），需用REGEXREPLACE转义，例如：textjoin("|",1,ARRAYFORMULA(REGEXREPLACE(E2:E,"([\[\]\(\)\*\+\?\.\\])","\\$1")))；
• 若部分日表无数据，公式仍能正常运行，QUERY会自动忽略空结果。

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