Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

 

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

Просто вставьте приведенный выше текст в обычный модуль(про модули см.здесь) и потом макрос можно будет вызвать из этой книги, нажатием клавиш Alt+F8 и выбрав его, или создав на листе кнопку и назначив ей макрос.
После вызова макроса надо будет указать исходные параметры:
Диапазон сбора данных - Если в окне выбора диапазона выбрать только одну ячейку, то данные будут собраны со всех листов книги/книг, начиная с этой ячейки и до последней ячейки листа.
Если выбрать несколько ячеек, данные будут собраны только с указанного диапазона всех листов книги/книг.

Имя листа - Необязателен для указания. Если не указан - данные будут собраны со всех листов. Указать можно как точное соответствие имени листа, так и с частичным соответствием. Например, если в книгах для сбора данных необходимо собрать данные только с листа "Январь", то следует так и указать - "Январь". Если требуется собрать данные только с листов, начинающихся с "Продажи"("Продажи ЮГ", "Продажи НН", "Продажи Запад" и т.д.), то следует применить символ подстановки звездочку - "Продажи*". Если надо собрать с листов, содержащих в имени "продажи"("Итоговые продажи ЮГ", "Продажи НН", "Сезонные продажи" и т.д.), то указываем "*продажи*". Если надо собрать только с листа "Сезонные продажи", но известно, что вместо пробела может быть нижнее подчеркивание или тире("Сезонные продажи", "Сезонные_продажи", "Сезонные-продажи") или иной символ, то можно также применить звездочку - "Сезонные*продажи". Но если среди листов могут встречаться и такие как "Сезонные разовые продажи", "Сезонные корпоративные продажи" и т.п., но информацию с них собирать не надо, то можно применить вопросительный знак - "Сезонные?продажи". Вопросительный знак заменяет любой один символ, звездочка - любое количество любых символов.

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

И последний запрос: Собрать данные с нескольких книг? - если выбрать Да, то появится диалоговое окно выбора файлов. Надо указать все файлы, данные с которых необходимо собрать. Если выбрать Нет, то данные будут собираться с листов только активной книги.

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

Скачать пример:

  Tips_Macro_Consolidated.xls (50,0 KiB, 21 817 скачиваний)

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


Статья помогла? Сделай твит, поделись ссылкой с друзьями!

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 282 коммент.
  1. Добрый день, скажите пожалуйста, данные у меня собраны так - http://joxi.ru/BA01zBBtBOg7ar
    Возможно ли добавить название столбцов, и фильтр

  2. Алексей:

    Здравствуйте, Дмитрий!
    Решил воспользоваться Вашим макросом для копирования даных из 475 книг на один лист, но столкнулся с ошибкой VB Run-time eror '1004' (скрин ошибки https://1drv.ms/i/s!AhFACJaqXOEEgoxd8KR-BkJ-pHRAFQ), которая появляется после вставки из 8-ми первых кних. Книгу для вставки сохранял и в .xlsx и .xlsm - не помогло. Исходные файлы в формате .xls, по одному листу в каждой книге с идентичной структурой и размером данных A1:T101. т.е. конечный результат должен иметь 47500 строк данных, а работа скрипта заканчивается на 800...
    Помогите разобратся, пожалуйста!

    • Алексей, а Вы не заметили, что текст ошибки пишет? Ровно то, что книгу не получается открыть. Т.е. метод Open просто не срабатывает. Посмотрите на какой книге останавливается код и изучите книгу. Возможно, она повреждена и не может быть открыта в режиме чтения/записи.

  3. Дмитрий:

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

  4. Леонид:

    Спасибо за полезную статью! Очень пригодилось!

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 для всех  Войти