Google数组到A列中的工作表:一个间接的Array公式

Question

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");""))

Answer 1

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)")

​

​

一切都是自动化的