Google 表格數組到 A 列中的表格：A 通過 INDIRECT，Arrayformula

科斯佳·科羅斯捷列夫

https://docs.google.com/spreadsheets/d/1TBi3XFlvIWCyht8kO8CngAxOHEe-wLYmuE361BQodHo/edit#gid=613086152表“Сбор данных”，公式在單元格C1中

我使用一個公式，我手動添加每一行，我可以以某種方式更改它，以便數組由非空行自行處理

        =query(UNIQUE({IF(COUNTA(INDIRECT($A1&"!A7:A"))>5;INDIRECT($A1&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A2&"!A7:A"))>5;INDIRECT($A2&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A3&"!A7:A"))>5;INDIRECT($A3&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A4&"!A7:A"))>5;INDIRECT($A4&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A5&"!A7:A"))>5;INDIRECT($A5&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A6&"!A7:A"))>5;INDIRECT($A6&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A7&"!A7:A"))>5;INDIRECT($A7&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A8&"!A7:A"))>5;INDIRECT($A8&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A9&"!A7:A"))>5;INDIRECT($A9&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A10&"!A7:A"))>5;INDIRECT($A10&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A11&"!A7:A"))>5;INDIRECT($A11&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A12&"!A7:A"))>5;INDIRECT($A12&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A13&"!A7:A"))>5;INDIRECT($A13&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A14&"!A7:A"))>5;INDIRECT($A14&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A15&"!A7:A"))>5;INDIRECT($A15&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A16&"!A7:A"))>5;INDIRECT($A16&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A17&"!A7:A"))>5;INDIRECT($A17&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A18&"!A7:A"))>5;INDIRECT($A18&"!A7:N");INDIRECT($A1&"!A7:N"))
    ;IF(COUNTA(INDIRECT($A19&"!A7:A"))>5;INDIRECT($A19&"!A7:N");INDIRECT($A1&"!A7:N"))

}); "Select Col1,Col3,Col5,Col4,Col6,Col7,Col13,Col14 Where Col1 is not null and not Col1 like 'Методист'";0)

所以只取第一個值並且不創建數組：

=ARRAYFORMULA(if(len(A:A)>0;INDIRECT(index($A:A)&"!A7:N");""))

玩家0

INDIRECT 不支持數組所以...

添加這個腳本：

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Сбор данных');  // sheet name
var src = sheet.getRange("A1");                                        // cell which holds the formula
var str = src.getValue();  
var cell = sheet.getRange("C2");                                       // cell where I want the results
cell.setFormula(str);              
}

並將此公式粘貼到 A1 中：

=ARRAYFORMULA("=QUERY(UNIQUE({"&TEXTJOIN("; "; 1; "IF(COUNTA("&QUERY(SHEETSNAME(); 
 "where not Col1 matches 'Сводная|Шаблон|Тех Лист|UTM- метки|Сбор данных'"; 0)&"!A7:N)>5; "&QUERY(SHEETSNAME(); 
 "where not Col1 matches 'Сводная|Шаблон|Тех Лист|UTM- метки|Сбор данных'"; 0)&"!A7:N; "&QUERY(SHEETSNAME(); 
 "where not Col1 matches 'Сводная|Шаблон|Тех Лист|UTM- метки|Сбор данных' limit 1"; 0)&"!A7:N)")&
 "}); ""select Col1,Col3,Col5,Col4,Col6,Col7,Col13,Col14 where Col1 is not null and not Col1 like 'Методист'""; 0)")