Google Sheets创建动态搜索时出现NO_COLUMN: A错误求助

错误原因 #

你遇到的NO_COLUMN: A错误，是因为QUERY函数处理联合数组（如{'Bin 1'!A1:C;'Bin 2'!A1:C;...}）时，无法识别原工作表的列标（A/B/C），必须用Col1、Col2这类索引式列名来引用。

解决方案：先构建带工作表标识的主列表，再搜索 #

1. 更新主列表公式（新增Bin工作表列） #

修改主列表的生成公式，给每个Bin的条目添加对应的工作表名称列，这样后续搜索结果能直接显示来源：

=QUERY(
  {'Bin 1'!A1:C, "Bin 1";
   'Bin 2'!A1:C, "Bin 2";
   'Bin 3'!A1:C, "Bin 3";
   'Bin 4'!A1:C, "Bin 4";
   'Bin 5'!A1:C, "Bin 5";
   'Bin 6'!A1:C, "Bin 6";
   'Bin 7'!A1:C, "Bin 7";
   'Bin 8'!A1:C, "Bin 8";
   'Bin 9'!A1:C, "Bin 9";
   'Bin 10'!A1:C, "Bin 10";
   'Bin 11'!A1:C, "Bin 11";
   'Bin 12'!A1:C, "Bin 12";
   'Bin 13'!A1:C, "Bin 13";
   'Bin 14'!A1:C, "Bin 14";
   'Bin 15'!A1:C, "Bin 15";
   'Bin 16'!A1:C, "Bin 16";
   'Bin 17'!A1:C, "Bin 17";
   'Bin 18'!A1:C, "Bin 18";
   'Bin 19'!A1:C, "Bin 19";
   'Bin 20'!A1:C, "Bin 20";
   'Bin 21'!A1:C, "Bin 21";
   'Bin 22'!A1:C, "Bin 22";
   'Bin 23'!A1:C, "Bin 23";
   'Bin 24'!A1:C, "Bin 24";
   'Bin 25'!A1:C, "Bin 25";
   'Bin 26'!A1:C, "Bin 26";
   'Bin 27'!A1:C, "Bin 27";
   'Bin 28'!A1:C, "Bin 28";
   'Bin 29'!A1:C, "Bin 29";
   'Bin 30'!A1:C, "Bin 30";
   'Bin 31'!A1:C, "Bin 31";
   'Bin 32'!A1:C, "Bin 32"},
  "select * where Col1 is not null", 1)

• 新增的第四列（"Bin X"）会自动标记每个条目的来源工作表
• 公式执行后，主列表会包含原A-C列数据+来源Bin名称列

2. 基于主列表的搜索公式 #

假设主列表在首页（比如Sheet1）的A:D列，搜索框在A2单元格，用以下公式实现全列搜索并显示来源：

=IFERROR(QUERY(Sheet1!A:D, 
  "select * where LOWER(Col1) CONTAINS '"&LOWER(A2)&"' OR LOWER(Col2) CONTAINS '"&LOWER(A2)&"' OR LOWER(Col3) CONTAINS '"&LOWER(A2)&"'", 1), 
  "无匹配结果")

• LOWER()确保搜索不区分大小写
• 可根据需求调整搜索的列（比如只搜Col1就保留第一个条件）
• IFERROR()用于无匹配时显示友好提示

原公式的快速修复（若不想修改主列表） #

如果暂时不想调整主列表，只需把原搜索公式里的A改成Col1即可解决错误，但无法显示来源工作表：

=QUERY({'Bin 1'!A1:C;'Bin 2'!A1:C;'Bin 3'!A1:C;'Bin 4'!A1:C;'Bin 5'!A1:C;'Bin 6'!A1:C;'Bin 7'!A1:C;'Bin 8'!A1:C;'Bin 9'!A1:C;'Bin 10'!A1:C;'Bin 11'!A1:C;'Bin 12'!A1:C;'Bin 13'!A1:C;'Bin 14'!A1:C;'Bin 15'!A1:C;'Bin 16'!A1:C;'Bin 17'!A1:C;'Bin 18'!A1:C;'Bin 19'!A1:C;'Bin 20'!A1:C;'Bin 21'!A1:C;'Bin 22'!A1:C;'Bin 23'!A1:C;'Bin 24'!A1:C;'Bin 25'!A1:C;'Bin 26'!A1:C;'Bin 27'!A1:C;'Bin 28'!A1:C;'Bin 29'!A1:C;'Bin 30'!A1:C;'Bin 31'!A1:C;'Bin 32'!A1:C}, "SELECT * WHERE LOWER(Col1) CONTAINS '"&LOWER(A2)&"'", 1)

（注：原公式里重复了两次相同的条件，已删除冗余部分）

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