Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Объединить все листы в документе в один - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Объединить все листы в документе в один (Формулы/Formulas)
Объединить все листы в документе в один
romanmiroshnik7 Дата: Четверг, 30.06.2022, 19:54 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Здравствуйте,
Возник вопрос на который я не могу найти ответ никак.
Возможно ли использовать функции поиска по всех листах книги.
Я понимаю что через формулу query и массивы можно объединить все листы и в получившемся масиве уже производить поиск, но очень неудобно каждый раз добавлять в формулу новый лист.
Возможно вы знаете как это провернуть, возможно скриптом?
 
Ответить
СообщениеЗдравствуйте,
Возник вопрос на который я не могу найти ответ никак.
Возможно ли использовать функции поиска по всех листах книги.
Я понимаю что через формулу query и массивы можно объединить все листы и в получившемся масиве уже производить поиск, но очень неудобно каждый раз добавлять в формулу новый лист.
Возможно вы знаете как это провернуть, возможно скриптом?

Автор - romanmiroshnik7
Дата добавления - 30.06.2022 в 19:54
romanmiroshnik7 Дата: Четверг, 30.06.2022, 19:54 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

 
Ответить
СообщениеПример файла: https://docs.google.com/spreads....1614063

Автор - romanmiroshnik7
Дата добавления - 30.06.2022 в 19:54
Gustav Дата: Воскресенье, 03.07.2022, 16:35 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 2214
Репутация: 893 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Можно объединить данные со всех нужных рабочих листов на одном листе (назовем его "Слияние") при помощи формулы, помещаемой в единственную ячейку этого листа (допустим, 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]
Код
={FILTER({'Лист1'!A:J\IF('Лист1'!A:A<>"";"Лист1")\ROW('Лист1'!A:A)};'Лист1'!A:A<>"");
FILTER({TEs!A:J\IF(TEs!A:A<>"";"TEs")\ROW(TEs!A:A)};TEs!A:A<>"");
FILTER({'Лист3'!A:J\IF('Лист3'!A:A<>"";"Лист3")\ROW('Лист3'!A:A)};'Лист3'!A:A<>"");
FILTER({'Лист4'!A:J\IF('Лист4'!A:A<>"";"Лист4")\ROW('Лист4'!A:A)};'Лист4'!A:A<>"");
FILTER({Fridau!A:J\IF(Fridau!A:A<>"";"Fridau")\ROW(Fridau!A:A)};Fridau!A:A<>"")}
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеМожно объединить данные со всех нужных рабочих листов на одном листе (назовем его "Слияние") при помощи формулы, помещаемой в единственную ячейку этого листа (допустим, 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]
Код
={FILTER({'Лист1'!A:J\IF('Лист1'!A:A<>"";"Лист1")\ROW('Лист1'!A:A)};'Лист1'!A:A<>"");
FILTER({TEs!A:J\IF(TEs!A:A<>"";"TEs")\ROW(TEs!A:A)};TEs!A:A<>"");
FILTER({'Лист3'!A:J\IF('Лист3'!A:A<>"";"Лист3")\ROW('Лист3'!A:A)};'Лист3'!A:A<>"");
FILTER({'Лист4'!A:J\IF('Лист4'!A:A<>"";"Лист4")\ROW('Лист4'!A:A)};'Лист4'!A:A<>"");
FILTER({Fridau!A:J\IF(Fridau!A:A<>"";"Fridau")\ROW(Fridau!A:A)};Fridau!A:A<>"")}
[/vba]

Автор - Gustav
Дата добавления - 03.07.2022 в 16:35
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Объединить все листы в документе в один (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!