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

Сбор данных из различных книг Excel на один лист в два этапа 

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

1) объединить листы различных рабочих книг в одной книге;

2) собрать данные со всех листов рабочей книги на отдельный лист.

Далее речь пойдет о том, как проделать все тоже самое, но в одно действие.

Сбор данных из нескольких рабочих книг Excel на отдельный лист

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

 

Надстройка позволяет копировать заданные пользователем диапазоны данных на всех заданных листах всех выбранных рабочих книг и вставлять скопированные данные на новый лист активной рабочей книги. При работе с этой надстройкой пользователь имеет возможность:

1) Выбирать нужные рабочие книги для последующей обработки;

2) Осуществлять выбор листов по именам, по номерам, по заданному значению в ячейках выделенного диапазона;

3) Задавать диапазоны ячеек для копирования;

а) Используемые диапазоны - это диапазоны, начинающиеся с первой используемой ячейки и заканчивающиеся последней используемой ячейкой;

б) Предварительно выделенные диапазоны на каждом листе - диапазоны, выделенные пользователем самостоятельно, исходя из его потребностей по консолидации данных;

в) Одноименные диапазоны - диапазоны с одинаковым адресом на каждом листе, указанным на активном рабочем листе;

г) Выборочные диапазоны - диапазоны ячеек, начинающиеся с выбранной ячейки и заканчивающиеся:

- концом листа (последней используемой ячейкой);

- последней заполненной ячейкой в заданном столбце;

- последней заполненной ячейкой в заданной строке;

д) Именованные диапазоны.

4) При вставке данных на итоговый лист заменять формулы результатами их вычислений (при отсутствии объединенных ячеек);

5) При сборе данных на итоговый лист вставлять связи, при этом изменения значений в исходных диапазонах ячеек будут отображаться на итоговом листе;

6) Подставлять имена листов перед вставляемыми на итоговый лист диапазонами;

7) Подставлять имена рабочих книг перед вставляемыми на итоговый лист диапазонами;

8) Выбирать вертикальное либо горизонтальное размещение данных на листе с итогами;

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

*В зависимости от выбранной опции диапазоны будут располагаться один ниже другого (вертикальное расположение), либо один правее другого (горизонтальное расположение).

sbor dannyh iz raznyh rabochih knigCompleteSolutionнадстройка для сбора данных из разных рабочих книг на отдельный лист

После установки надстройки останется лишь запустить программу нажатием одной кнопки из главного меню Excel или с ленты, в зависимости от версии Excel и в диалоговом окне выбрать необходимые параметры для сбора информации.

В случае, если итоговый лист содержит ненужные пустые строки, можно провести их автоматическое удаление при помощи надстройки для выборочного удаления строк.