时间戳

首页 > 解决方案 > 使用 Google 表格 ARRAYFORMULA 创建 QUERY 输入

google-sheets - 使用 Google 表格 ARRAYFORMULA 创建 QUERY 输入

问题描述

我有一个QUERY从各种单独的工作表中查找值的方法，这是它的输入：

=QUERY( {
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col2,  Col4,  Col5  where Col1 IS NOT NULL and Col4  IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col6,  Col8,  Col9  where Col1 IS NOT NULL and Col8  IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col10, Col12, Col13 where Col1 IS NOT NULL and Col12 IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col14, Col16, Col17 where Col1 IS NOT NULL and Col16 IS NOT NULL", 0), { "","","","","" } );
        IFNA( QUERY({'Week 1'!A2:Z133;'Week 2'!A2:Z133;'Week 3'!A2:Z133;'Week 4'!A2:Z133;'Week 5'!A2:Z133},"select Col26, Col1, Col18, Col20, Col21 where Col1 IS NOT NULL and Col20 IS NOT NULL", 0), { "","","","","" } )
}, "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col1")

变得重复且冗长，并且{}每次我再添加一周时都需要手动更新。

最近我发现我可以使用以下公式生成周工作表名称列表：

=ARRAYFORMULA(
    "Week " & 
    {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}
    & "!A2:Z133"
)

然后在另一列中仅列出实际存在的那些工作表：

=IF(
        ISERROR(CELL("address",INDIRECT($U2))),
        "",
        $U2
)

现在我有一列包含 , 等值Week1!A2:Z133。Week2!A2:Z133如何使用此列QUERY自动创建公式源？

使用这个公式可以让我得到第一个引用的范围，但没有列中的后续范围：

={ARRAYFORMULA(INDIRECT(AA:AA) )}

标签： google-sheetsgoogle-sheets-formula

解决方案

此表中的此处是一个基本脚本，用于组合以单词“Week”开头的选项卡。

function tabCombo(){
    var ss = SpreadsheetApp.getActive();

    //Filters sheets to just the ones that start with "Week"
    var sheets = ss.getSheets().filter(function (e){return e.getName().slice(0,4)=='Week'});

    //combines all tab values into one array and filters out the rows with a certain value in the first column
    var combo = sheets.map(e=>e.getDataRange().getValues()).flat().filter(e=>e[0]!='Header1');

    //writes that new value to a 'Master' tab.
    ss.getRange('Master!A2').offset(0,0,combo.length,combo[0].length).setValues(combo);
}

注意“周”这个词，它决定了要抓取哪些标签。

记下数字 4（这是“周”有多少个字母）

注意范围“Master！A2”，它是组合数据应该去的左上角。

请注意术语“Header1”，它是组合数组如何从所有选项卡中过滤掉标题行。

推荐阅读