Здравствуйте, Возник вопрос на который я не могу найти ответ никак. Возможно ли использовать функции поиска по всех листах книги. Я понимаю что через формулу query и массивы можно объединить все листы и в получившемся масиве уже производить поиск, но очень неудобно каждый раз добавлять в формулу новый лист. Возможно вы знаете как это провернуть, возможно скриптом?
Здравствуйте, Возник вопрос на который я не могу найти ответ никак. Возможно ли использовать функции поиска по всех листах книги. Я понимаю что через формулу query и массивы можно объединить все листы и в получившемся масиве уже производить поиск, но очень неудобно каждый раз добавлять в формулу новый лист. Возможно вы знаете как это провернуть, возможно скриптом?romanmiroshnik7
Можно объединить данные со всех нужных рабочих листов на одном листе (назовем его "Слияние") при помощи формулы, помещаемой в единственную ячейку этого листа (допустим, A1). Формулу же в этой ячейке будет строить/перестраивать специальный скрипт, запускаемый вручную, по мере появления новых рабочих листов в файле: [vba]
Код
// @OnlyCurrentDoc // <- аннотация для предотвращения сообщения "Приложение заблокировано" при авторизации скрипта
function buildFormula() {
// ПАРАМЕТРЫ ДЛЯ ПОСТРОЕНИЯ ФОРМУЛЫ // имя объединяющего листа var unionSheet = 'Слияние'; // листы, исключаемые при построении формулы var excludedSheets = [unionSheet,'Лист5']; // диапазон для объединения (на каждом листе) var dataRangeAddr = 'A:J'; // условие отбора строк для объединения (на каждом листе) var filterCondition = 'A:A<>""';
var oneSheet = function(sheetName) { return 'FILTER({\''+sheetName+'\'!'+dataRangeAddr+ '\\IF(\''+sheetName+'\'!'+filterCondition+ ';"'+sheetName+'")\\ROW(\''+sheetName+'\'!A:A)};\''+sheetName+'\'!'+filterCondition+')'; };
var ss = SpreadsheetApp.getActive(); var strFormula = ''; ss.getSheets().forEach(function(v) { if (!~excludedSheets.indexOf(v.getName())) { strFormula += oneSheet(v.getName())+';\n'; } }); strFormula = '={'+strFormula.slice(0, -2)+'}'; ss.getSheetByName(unionSheet).getRange('A1').setFormula(strFormula); }
[/vba] Обратите внимание на массив excludedSheets, который содержит список листов, которые НЕ НАДО включать в формулу объединения. Само собой, первым туда входит лист, на который нужно поместить формулу. Помимо него при необходимости можно указать и некоторые другие.
Также отмечу, что к исходным объединяемым диапазонам с рабочих листов (единый адрес указывается в переменной dataRangeAddr) на объединяющем листе добавляются справа две колонки: имя листа-источника и номер строки на листе-источнике.
По текущему состоянию файла по ссылке при помощи скрипта строится такая формула: [vba]
Можно объединить данные со всех нужных рабочих листов на одном листе (назовем его "Слияние") при помощи формулы, помещаемой в единственную ячейку этого листа (допустим, A1). Формулу же в этой ячейке будет строить/перестраивать специальный скрипт, запускаемый вручную, по мере появления новых рабочих листов в файле: [vba]
Код
// @OnlyCurrentDoc // <- аннотация для предотвращения сообщения "Приложение заблокировано" при авторизации скрипта
function buildFormula() {
// ПАРАМЕТРЫ ДЛЯ ПОСТРОЕНИЯ ФОРМУЛЫ // имя объединяющего листа var unionSheet = 'Слияние'; // листы, исключаемые при построении формулы var excludedSheets = [unionSheet,'Лист5']; // диапазон для объединения (на каждом листе) var dataRangeAddr = 'A:J'; // условие отбора строк для объединения (на каждом листе) var filterCondition = 'A:A<>""';
var oneSheet = function(sheetName) { return 'FILTER({\''+sheetName+'\'!'+dataRangeAddr+ '\\IF(\''+sheetName+'\'!'+filterCondition+ ';"'+sheetName+'")\\ROW(\''+sheetName+'\'!A:A)};\''+sheetName+'\'!'+filterCondition+')'; };
var ss = SpreadsheetApp.getActive(); var strFormula = ''; ss.getSheets().forEach(function(v) { if (!~excludedSheets.indexOf(v.getName())) { strFormula += oneSheet(v.getName())+';\n'; } }); strFormula = '={'+strFormula.slice(0, -2)+'}'; ss.getSheetByName(unionSheet).getRange('A1').setFormula(strFormula); }
[/vba] Обратите внимание на массив excludedSheets, который содержит список листов, которые НЕ НАДО включать в формулу объединения. Само собой, первым туда входит лист, на который нужно поместить формулу. Помимо него при необходимости можно указать и некоторые другие.
Также отмечу, что к исходным объединяемым диапазонам с рабочих листов (единый адрес указывается в переменной dataRangeAddr) на объединяющем листе добавляются справа две колонки: имя листа-источника и номер строки на листе-источнике.
По текущему состоянию файла по ссылке при помощи скрипта строится такая формула: [vba]