Google Sheets：如何在QUERY()函数的SELECT结果中显示工作表名称

核心需求 #

让公式结果的第一列显示对应工作表名称，支持两种形式：

• 作为独立列，例如查询结构为 SELECT SheetName,Col1,Col2,Col3,Col4,Col5
• 与原Col1内容拼接显示

已尝试的无效方法 #

• 执行SELECT CONCATENATE("Jan",Col1)报错
• 使用SELECT "Jan" & Col1无效果
• 用QUERY({ArrayFormula("Jan "&Jan!A3:E);Feb!A3:E}无报错，但未显示"Jan"标识

现有待修改公式 #

=SORT(
    ARRAYFORMULA(IF(A2="[All]",
                    QUERY({Jan!A3:E;Feb!A3:E;Mar!A3:E;Apr!A3:E;May!A3:E;Jun!A3:E;Jul!A3:E;Aug!A3:E;Sep!A3:E;Oct!A3:E;Nov!A3:E;Dec!A3:E}, 
                            "SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")),
                    QUERY(INDIRECT("'"&A2&"'!A3:E"), 
                            "SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(D)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(E)=LOWER('" & C2 & "')")
                            )
                    )
    ),IF(LEN(C4)>0,3,IF(LEN(B5)>0,2,1)),IF(C4="z",FALSE,TRUE)

方案1：添加独立的工作表名列 #

修改思路：给每个工作表的数据前插入一列固定的工作表名称，再调整QUERY语句的列序号和筛选条件。

修改后的公式：

=SORT(
    ARRAYFORMULA(IF(A2="[All]",
                    QUERY({
                            ARRAYFORMULA({"Jan", Jan!A3:E});
                            ARRAYFORMULA({"Feb", Feb!A3:E});
                            ARRAYFORMULA({"Mar", Mar!A3:E});
                            ARRAYFORMULA({"Apr", Apr!A3:E});
                            ARRAYFORMULA({"May", May!A3:E});
                            ARRAYFORMULA({"Jun", Jun!A3:E});
                            ARRAYFORMULA({"Jul", Jul!A3:E});
                            ARRAYFORMULA({"Aug", Aug!A3:E});
                            ARRAYFORMULA({"Sep", Sep!A3:E});
                            ARRAYFORMULA({"Oct", Oct!A3:E});
                            ARRAYFORMULA({"Nov", Nov!A3:E});
                            ARRAYFORMULA({"Dec", Dec!A3:E})
                        }, 
                            "SELECT Col1,Col2,Col3,Col4,Col5,Col6 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(Col5)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(Col6)=LOWER('" & C2 & "')")),
                    QUERY(
                            ARRAYFORMULA({A2, INDIRECT("'"&A2&"'!A3:E")}), 
                            "SELECT Col1,Col2,Col3,Col4,Col5,Col6 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(Col5)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(Col6)=LOWER('" & C2 & "')")
                            )
                    )
    ),IF(LEN(C4)>0,4,IF(LEN(B5)>0,3,2)),IF(C4="z",FALSE,TRUE)

关键调整点：

• 每个工作表数据通过ARRAYFORMULA({"工作表名", 数据范围})添加第一列的工作表名称，原Col1_{Col5对应变为Col2}Col6
• 筛选条件里的Col4/Col5对应改为Col5/Col6
• SORT排序列序号同步调整：原3→4，原2→3，原1→2

方案2：将工作表名与Col1拼接显示 #

修改思路：把工作表名和原Col1内容合并成新的第一列，后续列结构保持和原公式一致。

修改后的公式：

=SORT(
    ARRAYFORMULA(IF(A2="[All]",
                    QUERY({
                            ARRAYFORMULA("Jan "&Jan!A3:A&", "&Jan!B3:E);
                            ARRAYFORMULA("Feb "&Feb!A3:A&", "&Feb!B3:E);
                            ARRAYFORMULA("Mar "&Mar!A3:A&", "&Mar!B3:E);
                            ARRAYFORMULA("Apr "&Apr!A3:A&", "&Apr!B3:E);
                            ARRAYFORMULA("May "&May!A3:A&", "&May!B3:E);
                            ARRAYFORMULA("Jun "&Jun!A3:A&", "&Jun!B3:E);
                            ARRAYFORMULA("Jul "&Jul!A3:A&", "&Jul!B3:E);
                            ARRAYFORMULA("Aug "&Aug!A3:A&", "&Aug!B3:E);
                            ARRAYFORMULA("Sep "&Sep!A3:A&", "&Sep!B3:E);
                            ARRAYFORMULA("Oct "&Oct!A3:A&", "&Oct!B3:E);
                            ARRAYFORMULA("Nov "&Nov!A3:A&", "&Nov!B3:E);
                            ARRAYFORMULA("Dec "&Dec!A3:A&", "&Dec!B3:E)
                        }, 
                            "SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")),
                    QUERY(
                            ARRAYFORMULA(A2&" "&INDIRECT("'"&A2&"'!A3:A")&", "&INDIRECT("'"&A2&"'!B3:E")), 
                            "SELECT Col1,Col2,Col3,Col4,Col5 WHERE 1=1 " 
                                    & IF(B2="[All]",""," AND LOWER(Col4)=LOWER('" & B2 & "')") 
                                    & IF(C2="[All]",""," AND LOWER(Col5)=LOWER('" & C2 & "')")
                            )
                    )
    ),IF(LEN(C4)>0,3,IF(LEN(B5)>0,2,1)),IF(C4="z",FALSE,TRUE)

关键调整点：

• 用"工作表名 "&原Col1&", "&原B:E的格式拼接内容，生成新的第一列
• 单个月份的情况直接引用A2的工作表名进行拼接，筛选和排序逻辑无需调整

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