MulTEx »

Сбор данных с нескольких листов/книг

Данная функция является частью надстройки MulTEx

MulTEx

Вызов команды:
MulTEx -группа Книги/Листы -Книги -Сбор данных с листов/книг


Команда поможет собрать данные с разных листов, книг и текстовых файлов в один лист или книгу(если на один лист данные не помещаются). Собирать данные можно по условию присутствия определенного значения в строке, только с указанных листов, исключив при этом некоторые листы. Возможность добавить имя листа и книги перед данными.
Основная форма содержит три вкладки:



вкладка Общие
вкладка Общие
Просматривать листы:

  • текущей книги - данные будут собираться с листов активной на момент запуска команды книги.
  • выбранных книг - данные будут собираться с листов книг, которые присутствуют в списке файлов, расположенном на вкладке Настройка сбора с книг.

Собрать данные со всех листов - собираются данные со всех листов(кроме листа, выбранного для сбора данных) активной книги или всех указанных книг, в зависимости от того, какой тип просмотра листов был выбран(текущей книги или выбранных книг).

Собирать данные с листа, если - данные собираются только с определенных листов книг. Делится на два значения:

  • Номер листа - указывается порядковый номер листа, с которого собирать данные. Можно применять символы подстановки - * и ?. Например, указав 1* Вы в итоге соберете данные со всех листов, индекс которых начинается с 1(1,10,11,12,13,...,101,102 и т.д.), а указав 1? - 10,11,12,...,19. При подсчете порядкового номера учитываются только видимые листы.
  • Имя листа - указывается имя листа, с которого собирать данные. Также как и с номером можно применять символы подстановки - * и ?. Взять пример с рисунка - *отчет*. Данные будут собраны со всех листов, в имени которых встречается слово отчет - первый отчет, 2 отчет, отчет №3, четвертый отчет за период и т.д.
  • Просматривать скрытые листы - если установлен флажок, то данные будут собираться со всех листов книги, независимо от того видимые они или нет. Если снять - то данные будут собираться только с видимых листов книги. При этом, если флажок не установлен, то аргументом Номер листа подсчитываются и учитываются только видимые листы. Что это значит: когда листы скрыты, они так же являются частью книги и подсчитываются Excel-ем так же, как и видимые. Если в книге 5 листов: Лист1, Лист_итог, Лист_пром, Лист2, Лист3 и 2 из них скрыты(Лист_итог, Лист_пром), то фактически Лист2 будет иметь номер 4, а Лист3 номер 5. Хотя среди видимых листов Лист2 будет иметь номер 2, а Лист3 номер 3. И если бы программа брала номера листов в реальном порядке(а не в порядке только для видимых листов), то указывая в качестве аргумента Номер листа цифру 2 ожидается, что данные будут собираться с Лист2. Но по факту данные бы собирались с Лист_итог, т.к. именно он имеет 2-ой номер по порядку. Иными словами - MulTEx всегда работает с логическим для пользователя порядком листов, не заставляя думать есть ли еще какие-то листы, если надо собирать данные только с видимых.
    Эта опция так же применяется для настройки Исключить из сбора листы
    Изменять видимость листов(а так же обнаружить скрытые листы) можно командой MulTEx Изменить видимость листов

Исключить из сбора листы - указываются листы, данные с которых собирать не надо. Делится на два значения:

  • Номер листа - указывается порядковый номер листа, с которого собирать данные не требуется.
  • Имя листа - указывается имя листа, с которого собирать данные не требуется.

Так же как и при указании листов для сбора, здесь допускается использование подстановочных символов для указания имени/номера листа. Таким образом можно исключить из сбора группу листов.
Например, в каждой книге для сбора есть листы с отчетами. Отчеты ежедневные("Ежедневный отчет за 01.04.2014", "Ежедневный отчет за 02.04.2014" и т.д.) и отчеты за весь период, которые могут называться "Итоговый отчет" или "Отчет за месяц" и т.п. Если необходимо собрать только итоговые отчеты, то можно указать в качестве имени листа для сбора данных значение "*отчет*", а в исключениях "*ежедневный*". Или в книге только ежедневные отчеты, но известно, что за 02.04.2014 отчеты были сформированы неверно и их надо исключить. В качестве имени листа для сбора данных указываем "*отчет*", а в исключениями имя листа - "*02.04.2014".

Собирать начиная с указанной ячейки и до последней - данные с листов будут собираться начиная с ячейки, указанной в окне выбора. На рисунке указан адрес - $B$12, следовательно данные будут собираться, начиная именно с этой ячейки. Может пригодиться, если в данных присутствуют заголовки, шапки и прочие ненужные в итоговых данных строки или столбцы. Применяя данную опцию необходимо следить, чтобы структура документов во всех книгах была одинакова.
Собирать данные только указанного диапазона - будут собраны данные ячеек исключительно в пределах указанного диапазона. Актуально в случае, когда помимо шапки, есть еще и подпись, итоги и т.п. и количество строк в отчетах не меняется(например, всегда 31 строка).
Копировать листы целиком, собирая в одну книгу - в результирующую книгу будут добавлены целые листы, данные в которых отвечают условиям настроек. Имена листов остаются такими же, как они записаны в исходной книге.

    Не переносить листы с одинаковым именем - если установлен, то в итоговую книгу не будут копироваться те листы, имена которых уже присутствуют в итоговой книге. Т.е. если сбор идет с нескольких книг и в каждой из книг присутствует лист "Отчет", то в итоговую книгу будет добавлен лист только из первой книги. Если пункт Не переносить листы с одинаковым именем отключен, то при совпадении имени листа в итоговой и в просматриваемой книге для итогового листа будет добавлен номер в скобках(Итог, Итог(1), Итог(2) и т.д.)

Добавить имена листов перед данными - в итоговый лист будет добавлен столбец, в который напротив каждого первого значения нового листа будет записано имя листа, с которого эти данные были занесены. Столбец добавляется перед данными(т.е. первым столбцом).
Примечание: данный пункт не учитывается при выборе метода сбора данных Копировать листы целиком, собирая в одну книгу на вкладке Общие

Поместить результат сбора:

  • в новую книгу - после нажатия ОК будет создана новая книга, на первый лист которой, начиная с ячейки A1 и будут помещены все отобранные данные. В случае, если выбрано Копировать листы целиком, собирая в одну книгу, в новой книге будут собраны только листы, подходящие под критерии сбора. Если ни один из листов не подходит под критерии, то по окончании сбора будет отображена созданная книга с одним пустым листом.
  • на новый лист активной книги - после нажатия ОК будет создан новый чистый лист, на который начиная с ячейки A1 и будут помещены все отобранные данные.
  • начиная с указанной ячейки - указывается ячейка на любом листе любой открытой книги. Начиная с указанной ячейки в указанный лист указанной книги будут вставлены собранные данные. Данный пункт не доступен при выборе метода сбора данных Копировать листы целиком, собирая в одну книгу на вкладке Общие


вкладка Настройки сбора с книг
Настройки сбора с книг

Вкладка неактивна и указанные в ней параметры не учитываются, если на вкладке Общие выбрано Просматривать листы текущей книги.
Важно: данные собираются со всех файлов, добавленных в список, даже если они не отмечены флажками(галочками). Отметка флажками файлов применяется для удаления файлов из списка, добавленных по ошибке или уже не нужных для включения в сбор данных.

Добавить имена книг перед данными - в итоговый лист будет добавлен столбец, в который напротив каждого первого значения нового листа будет записано имя книги, с которой эти данные были занесены. Столбец добавляется перед данными первым столбцом (если также был выбран пункт - Добавить имена листов перед данными - то информация об именах книг будет добавлена перед информацией об именах листов). Пункт недоступен если на вкладке Общие выбрано Просматривать листы текущей книги. Примечание: данный пункт не учитывается при выборе метода сбора данных Копировать листы целиком, собирая в одну книгу на вкладке Общие

Преобразовать выбранные файлы в формат ".xlsx" - 2007-2010 Excel - Преобразует выбранные для обработки файлы в формат 2007 Excel. Необходимо для тех случаев, когда собираемые данные могут занять больше места на листе, чем 65536 строк(максимум для файлов, созданных в 2003 Excel). Все дело в том, что даже работая в 2007 Excel(и выше) с файлами формата 2003 Excel(.xls) Excel начинает работать в режиме совместимости с предыдущими версиями и накладывает на файлы определенные ограничения. В том числе эти ограничения накладываются и на перенос данных из одного файла в другой. Если попытаться собрать данные с файлов, среди которых будет файл 2003 Excel(.xls) и при переносе данных из этого файла в итоговый лист потребуется строк больше 65536 - то приложение выдаст ошибку, т.к. Excel не может пропустить это ограничение при переносе данных из файла в режиме совместимости.

Удалить преобразованные файлы после сбора данных - Если файлы, преобразованные при помощи опции Преобразовать выбранные файлы в формат ".xlsx" - 2007-2010 Excel не нужны после сбора данных - необходимо отметить данный пункт и все преобразованные файлы будут удалены сразу после сбора данных. Исходники при этом никаким образом не пострадают.

Обновлять связи при открытии книг - по умолчанию данная опция отключена, т.к. в противном случае при открытии каждой книги со связями будет появляться сообщение от Excel с предложением обновить связи или нет. Если для некоторых книг связи все же требуется обновлять - включите опцию.

Использовать пароль для открытия книг - если на открытие файлов установлен пароль, то необходимо включить эту опцию и в поле напротив указать пароль для открытия книг. Важно: для всех книг должен быть установлен один и тот же пароль. Если пароль не совпадает, то данные с такой книги собраны не будут. Если на открытие книги пароль не установлен, то данные будут собраны в любом случае, независимо от указанного в поле пароля.



вкладка Расширенные настройки
Расширенные настройки
Копировать только те строки, в которых присутствует значение: - включение данной опции позволяет копировать из диапазонов листов лишь те строки, в ячейках которых присутствует указанное в текстовом поле(расположено ниже) значение. Можно применять символы подстановки - * и ?. На рисунке введено слово "*итог*". Значит скопированы будут лишь те строки, в ячейках которых будет найдено слово или словосочетание, содержащие "итог". Это и "подитог" и "промежуточный итог", и "итого" и т.д.
Заданное значение будет искаться в диапазоне, указанном на вкладке Общие. Например, если выбран пункт Собирать только выбранный диапазон и указан диапазон $B$5:$F$60, то значение "итог" будет искаться только в этом диапазоне листа. И скопирована будет только строка, не выходящая за пределы указанного диапазона. Если слово "итог" будет найдено в ячейке D25, то скопированы будут ячейки B25:F25.
Примечание: данный пункт не учитывается при выборе метода сбора данных Копировать листы целиком, собирая в одну книгу на вкладке Общие

Собирать данные, если содержимое ячейки [адрес ячейки] равно:

  • Значению - в итоговый лист будут собраны данные лишь с тех листов, значение указанной ячейки в которых равно записанному в данное поле. Можно применять символы подстановки - * и ?. Если отметить пункт и оставить пустым - появится сообщение, предупреждающее о пустом значении. Можно согласиться собирать данные, если ячейка пустая, либо отменить выполнение и заполнить поле значением. В примере указано значение "Ведомость" и ячейка, в которой это значение просматривать: $A$1. Значит данные будут собираться только с тех листов, содержимое ячейки A1 которых равно Ведомость.
  • Содержимому ячейки [адрес ячейки] - указывается адрес ячейки(выделением ячейки на листе). В итоговый лист будут собраны данные лишь с тех листов, значение указанной ячейки в которых равно значению в этой ячейке. Например, на рисунке указан адрес ячейки для просмотра - $A$1. Если в поле Содержимому ячейки указать 'Лист2'!$B$10 и в этой ячейке записано "ТЦ Омега", то перед сбором данных из этой ячейки будет считано её значение - "ТЦ Омега". И именно с ним впоследствии будет сравниваться значение в ячейке $A$1 каждого листа: данные будут собираться только с тех листов, содержимое ячейки A1 которых равно ТЦ Омега.

 
Параметры вставки:

  • Не учитывать фильтр на листах - если установить, то при установленном на листах фильтре значения будут скопированы даже со скрытых фильтром строк. Иногда это может пригодиться, чтобы не снимать фильтр со множества листов. Важно: если на вкладке Общие выбрано Просматривать листы текущей книги, то фильтры на листах, с которых собирались данные, будут сброшены.
  • Копировать только значения и форматы - в итоговый лист будут скопированы исключительно значения и форматы с выбранных файлов/листов. Это значит, что если в каких-либо листах есть формулы, то будет скопирован лишь результат их вычислений - сами формулы на итоговый лист не попадут. Может пригодиться, когда на листах для сбора есть много формул, ссылающихся на разные листы и книги. При копировании таких формул в итоговом листе может появиться ошибочное значение(#ССЫЛКА!, #Н/Д и др.). При включенной опции Копировать только значения и форматы опция Создать связь с источником становится недоступной.
  • Создать связь с источником - в итоговом листе будут созданы ссылки на ячейки выбранных файлов/листов (=[Книга1.xlsx]Лист2!A4). Может пригодится, если данные собираются в итоговую книгу/лист и впоследствии необходимо получать актуальные данные в итоговом листе после изменения данных в книгах/листах с первоначальными данными. Однако, следует учитывать, что создание связей не всегда лучший вариант: файл увеличивается в размерах и при открытии может появляться запрос на обновление связей (если связи создавались не в пределах той же книги, а на ячейки других книг). При включенной опции Создать связь с источником опция Копировать только значения и форматы становится недоступной.
  • Вставлять данные по столбцам - если выбран, то данные будут вставляться не в классическом варианте друг под другом после последней заполненной строки, а будут продляться вправо - после последнего заполненного столбца. Схемы того, как будут собраны данные в зависимости от включенного или отключенного данного пункта:
    Отключен(классический вариант сбора данных)
    Вставлять данные по строкам
    Включен(вариант сбора данных по столбцам)
    Вставлять данные по столбцам

Определять последнюю строку данных на основании столбца № - указывается номер столбца. Применяется, если нет необходимости собирать все данные с листа, а только те, которые заканчиваются в определенном столбце. Например, если хотите исключить из сбора подпись под данными в виде итогов. На рисунке ниже наглядно показано, что будет, если собирать данные начиная с указанной ячейки и до конца и как будет определен конец для сбора данных, если указать номер столбца 6(F) для определения последней ячейки:
Поиск последней строки
Для корректного сбора данных с листов как на рисунке выше необходимо на вкладке Общие указать Собирать, начиная с указанной ячейки и до последней - A6; на вкладке Расширенные настройки включить Определять последнюю строку данных на основании столбца № - 6. Тогда будут собраны данные без шапок и без итоговой строки.
Чтобы не высчитывать номера столбцов на основании их названий(букв) можно воспользоваться командой MulTEx Стиль ссылок.

Если лист с просматриваемыми данными защищен - опция пригодится в случаях, когда данные собираются со множества листов, которые защищены. Данные с таких листов могут быть не собраны или собраны некорректно, если не снять защиту. Опция включает несколько вариантов:

  • Ввести пароль - в данном случае необходимо указать пароль для снятия защиты с листов. Если пароль неверный, то возможно возникновение ошибки при сборе данных, а данные в результате могут быть не собраны или собраны неверно. Если листы защищены без пароля, то следует указать данный пункт, а поле для ввода пароля оставить пустым.
  • Пропустить - в данном случае листы с установленной защитой будут игнорироваться и данные с них не попадут в результирующий отчет
  • Ручной режим - в данном случае при обнаружении защищенного листа надстройка предложит собрать данные с этого листа без ввода пароля. Но в данном случае данные могут быть собраны некорректно
  • Попытаться снять защиту без пароля - в данном случае при обнаружении защищенного листа надстройка без всяких уведомлений попытается снять защиту с листа и после этого собрать данные. Если снять защиту не получится, то данные с этого листа могут быть собраны некорректно.

Примечание: Если во время сбора данных количество строк собранных данных превысит количество строк на результирующем листе, появится сообщение с выбором: либо создать новый лист и продолжать собирать данные на него, либо прекратить выполнение команды.

Также см.:
Как собрать данные с нескольких листов или книг?
Как объединить несколько текстовых файлов в один?
Создание отдельных книг из листов текущей книги


Расскажи друзьям, если статья оказалась полезной:
Обсуждение: оставлено 52 коммент.
  1. Рыжик:

    Привет, Дмитрий!

    Команда "Сбор данных с книг...".
    Если листы в книгах защищены, каждый раз выдаёт диалоговое окно с предупреждением. Неудобно. Вместо чай-кофе сижу тычу "Да".
    Предложение - добавить в окошко настроек птичку "Собирать данные с защищённых листов" Или "Предупреждать при обнаружении защищённых листов". Другой вариант - добавить птичку в вылезающее диалоговое окошко "Запомнить выбор для подобных предупреждений". Хорошо, книжек обрабатывалось всего 30. А если бы 500?

    • Добрый день.
      Спасибо, подумаю над реализацией более удобного механизма сбора с защищенных листов.

      • Рыжик:

        Ну, тогда вдогонку. Пометка книжек во вкладке для работы с книгами - нет кнопочки "Пометить всё".
        Результаты сбора выводятся последовательно вниз. Хотелось бы ещё вариант последовательно вправо.

        • Пометить все. А зачем, собственно? Ведь отметки нужны исключительно для удаления файлов из списка. На сбор они не влияют, собраны будут данные все равно из всех файлов в списке, даже если ничего не отметить. А для удаления всех есть кнопка: Очистить все :) В справке об этом написано, кстати.
          По сбору вправо: думаю над этим давно, но много очень нюансов. Пара из них: столбцов гораздо меньше, чем строк. Поэтому много шансов не вместить данные на лист в принципе. Плюс имена листов и книг точно тогда за бортом останутся, ибо нелогично. Да и сбор строк по критерию тоже тогда будет невозможен. В общем здесь все не так просто, а скорее печально. Но все еще думаю над реализацией.

          • Рыжик:

            Я "Пометить всё" использую, когда нужно выделить несколько файлов из большого списка. Не критично - можно удалить всё и добавить несколько нужных заново :)

            Сбор вправо. Вряд ли кому-то понадобится анализ, при котором количество столбиков не влезет на лист. Можно выдавать предупреждение с предложением "Использовать сбор вниз" или "Отменить", если столбики закончились. А имена листов и книг могут занять две верхние строчки над данными (причём, если столбцов несколько, их ещё и объединить можно).

          • Словами алгоритм несложный, я знаю. Но т.к. сама по себе команда сбора довольно напичкана различными настройками - реализация усложняется. Идея записи имен листов и книг в верхние строчки хорошая, возьму на заметку. Как говорил - над реализацией думаю, но чего-то цельного удобного пока не в голову не приходит.

      • Рыжик:

        Ещё про работу с книгами/листами.

        Возможность разнести листы по разным книжкам есть. А можно обратную? Собрать из выбранных книжек одинаково называющиеся листы, переименовав их по названию соответствующего файла, в одну книжку.
        Пример. Есть три книги (книга 1, книга 2, книга 3). В каждой книге есть лист "Финплан". Надо получить файл "книга4", с листами книга 1, книга 2, книга 3. Содержимое каждого листа - копия листа "Финплан" из соответствующего файла.

  2. Юрий:

    Спасибо хорошая вещь. Надо было из 425 файлов все склеить в 1 чтоб имена листов еще были. Сначала склеил все на 1 лист с добавлением имени файла, а потом разнос сделал и оп - все листы переименованы как надо.

    Хотя как вариант развития чтоб при склейке в одну книгу все по листам переименовывало листы по имени файла!

  3. Юрий:

    Ничего не вводится в окошко "Адрес ячейки:" при создании списка /оглавления. Текст ссылкии на оглавление на каждом листе активно, а ячекйу не могу указать

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти