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

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

---

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

Основная форма содержит вкладки:

• Общие
• Настройки сбора с книг
• Расширенные настройки
• Агрегирование

---

вкладка Общие

Просматривать листы:

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

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

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

• Номер листа - указывается порядковый номер листа, с которого собирать данные. Можно применять символы подстановки - * и ?. Например, указав 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 и будут помещены все отобранные данные.
• начиная с указанной ячейки - указывается ячейка на любом листе любой открытой книги. Начиная с указанной ячейки в указанный лист указанной книги будут вставлены собранные данные. Данный пункт не доступен при выборе метода сбора данных Копировать листы целиком, собирая в одну книгу на вкладке Общие

---

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

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

здесь можно выбрать вариант добавления файлов в список для последующей обработки

• все файлы в папке - путем нажатия кнопки правее поля выбирается папка, файлы в которой необходимо просмотреть и внести в список
  включая подпапки - доступно для пункта все в папке. Если установлен, то файлы будут просматриваться не только в указанной папке, но и во всех вложенных в неё папках до самой глубоко вложенной. Если отключен, то файлы будут просматриваться исключительно в указанной папке.
  Отбирать файлы по маске - доступно для пункта все в папке. В данном случае в поле указывается шаблон и будут отбираться файлы, отвечающие маске шаблона. Например, если необходимо отбирать только файлы Excel, то можно указать маску: *.xls*. Если надо отбирать только текстовые файлы, содержащие в имени слово "отчет", то маску можно задать как: *отчет*. Если в шаблоне указано расширение файлов(например, *отчет*.xls*, то будут отбираться только файлы указанного типа - в данном случае Excel). Если ничего не указано, то отбираться будут все файлы Excel и текстовые(txt и CSV)
• только выбранные - после нажатия кнопки Добавить файлы появится диалоговое окно, в котором необходимо выбрать файлы для добавления в список

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

Преобразовать выбранные файлы в формат ".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 которых равно ТЦ Омега.

 

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

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

Определять последнюю строку данных на основании столбца № - указывается номер столбца. Применяется, если нет необходимости собирать все данные с листа, а только те, которые заканчиваются в определенном столбце. Например, если хотите исключить из сбора подпись под данными в виде итогов. На рисунке ниже наглядно показано, что будет, если собирать данные начиная с указанной ячейки и до конца и как будет определен конец для сбора данных, если указать номер столбца 6(F) для определения последней ячейки:

Для корректного сбора данных с листов как на рисунке выше необходимо:

• на вкладке Общие указать Собирать, начиная с указанной ячейки и до последней - A6;
• на вкладке Расширенные настройки включить Определять последнюю строку данных на основании столбца № - 6

Тогда будут собраны данные без шапок и без итоговой строки.
Чтобы не высчитывать номера столбцов на основании их названий(букв) можно воспользоваться командой MulTEx Стиль ссылок.

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

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

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

---

АГРЕГИРОВАНИЕ
На этой вкладке расположены опции, при помощи которых можно реализовать суммирование(минимум, максимум, среднее) собранных данных на основании значений указанных столбцов:

Агрегировать:

Если установлен, то все данные, которые отвечают заданным критериям сбора, будут агрегированы при помощи выбранной функции. Доступные функции агрегирования: Сумма, Среднее, Минимум, Максимум.

данные столбцов - указываются буквы(A,B,C) или номера столбцов(1,2,3) на листе, либо имена заголовков таблиц, значения которых необходимо агрегировать (просуммировать, вывести среднее, минимум или максимум).
используя функцию - выбирается одна из доступных функций агрегирования:
• Сумма - данные указанных столбцов будут просуммированы
• Среднее - для каждого из указанных столбцов будет выведено среднее арифметическое значение(общая сумма значений деленная на их количество)
• Минимум - для каждого из указанных столбцов будет отобрана строка с минимальным числом из всего набора данных
• Максимум - для каждого из указанных столбцов будет отобрана строка с максимальным числом из всего набора данных

Заменить нечисловые данные нулями - если установить, то все данные, которые не могут быть преобразованы в число будут заменены нулями. Если отключен, то такие данные будут пропущены. Для чего это может быть нужно? Если в столбцах для агрегирования присутствуют текстовые данные, то в определенных случаях общим результатом агрегирования может стать текстовое значение. Как правило это может относится к данным, которые являются уникальными во всем наборе данных. Если в дальнейшем итоговые данные планируется использовать для построения графиков и иных расчетов, то данный пункт лучше включить во избежание ошибок.

Если совпадают данные - перечисляются столбцы таблиц, на основании которых будут агрегированы значения. Столбцы перечисляются через запятую (,).
• всех столбцов одной строки (кроме столбцов агрегирования) - в данном случае в качестве критериев определения данных для агрегирования будут использоваться все столбцы каждого отдельного набора данных(каждого листа), кроме столбцов, указанных для агрегирования. Этот пункт следует выбирать только в том случае, если данные собираются с таблиц с одинаковым количеством и набором столбцов.
• указанных столбцов - в данном случае в качестве критериев определения данных для агрегирования будут использоваться только перечисленные столбцы в каждом наборе данных. Если какого-либо из столбцов не будет в наборе данных(такое возможно, если для определения столбцов используется пункт Имена столбцов в заголовке таблицы (первая строка)), то данные этой таблицы будут агрегированы отдельно от остальных данных.
Для определения столбцов использовать: - выбирается способ перечисления столбцов:
• Буквы столбцов листа (A,B,C) - для определения столбцов указываются имена столбцов на листе:
  
  A - первый столбец, B - второй столбец, C - третий столбец и т.д. Все имена должны перечисляться через запятую, без пробелов. Буквы могут быть записаны как заглавными, так и строчными, регистр не имеет значения. Такой метод перечисления наиболее предпочтителен в случаях, когда данные собираются с однотипных таблиц и их расположение на всех листах для сбора одинаковое. В ином случае данные могут быть агрегированы неверно.
• Номера столбцов листа (1,2,3) - для определения столбцов указываются номера столбцов на листе:
  
  1 - первый столбец, 2- второй столбец, 3 - третий столбец и т.д. Важно учитывать, что указываются номера столбцов именно на листе, а не в таблицах.
  Например, таблицы данных начинаются с ячейки В2 и агрегировать необходимо данные столбцов C и D. В этом случае первый номер столбца будет 2, а для агрегирования следует указать 3,4. Все номера должны перечисляться через запятую, без пробелов. Такой метод перечисления наиболее предпочтителен в случаях, когда данные собираются с однотипных таблиц и их расположение на всех листах для сбора одинаковое. В ином случае данные могут быть агрегированы неверно.
• Имена столбцов в заголовке таблицы (первая строка) - для определения столбцов указываются названия заголовков таблиц:
  
  Заголовки должны располагаться в первой строке собираемых данных. При этом используется первая строка именно данных для сбора, а не первая строка на листе.

  Если на вкладке Общие выбрано Собирать, начиная с указанной ячейки и до последней и указана ячейка A3, то строкой заголовка будет считаться строка 3 листа, с которого собираются данные, т.к. именно с этой строки начинается данные для сбора.
  Если на вкладке Общие выбрано Собирать данные только указанного диапазона и указан диапазон B12:H90, то строкой заголовка будет считаться строка 12 листа, с которого собираются данные, т.к. именно с этой строки начинается данные для сбора.

  Даже если при этом на вкладке Расширенные настройки выбран пункт Копировать только те строки, в которых присутствует значение - заголовком все равно будет считаться первая строка начального набора данных и именно в этой строке будут просматриваться имена заголовков.

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

Как это работает. Например, необходимо на отдельный лист собрать данные из всех листов книги. На каждом листе расположена таблица следующего вида:

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

ОБЩИЕ (одинаковые для всех вариантов агрегирования)

Собирать данные со всех листов
Собирать, начиная с указанной ячейки и до последней: A1

АГРЕГИРОВАНИЕ

Агрегировать
• данные столбцов: G,H,I
  используя функцию: Сумма
• если совпадают данные:
  указанных столбцов: A,D,E
• Для определения столбцов использовать: Буквы столбцов листа (A, B, C)
или:
Агрегировать
• данные столбцов: 7,8,9
  используя функцию: Сумма
• если совпадают данные:
  указанных столбцов: 1,4,5
• Для определения столбцов использовать: Номера столбцов листа (1, 2, 3)

Если в столбцах Магазин, Наименование товара и Месяц данные будут полностью совпадать - то будет оставлена только первая строка данных, а числовые данные столбцов для суммирования(Сумма, Количество, Остаток) будут сложены:

В итоге получится таблица из уникальных сочетаний Магазина, Наименования товара и Месяца и общих сумм для столбцов Сумма, Количество и Остаток по этим данным.

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

Тогда предпочтительнее применять агрегирование на основании имен столбцов в заголовке таблицы:

Агрегировать
• данные столбцов: Сумма,Количество,Остаток
  используя функцию: Сумма
• если совпадают данные:
  указанных столбцов: Магазин,Наименование товара,месяц
• Для определения столбцов использовать: Имена столбцов в заголовке таблицы (первая строка)

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