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

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

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

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

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

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

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

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

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

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

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

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

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

kak sobrat dannye so vseh listov na itogovyj list

CompleteSolutionмакрос (надстройка) для сбора данных со всех листов Excel на итоговый

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

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

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

Видео по сбору данных со всех листов на итоговый лист

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

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

1) При помощи надстройки для объединения рабочих книг в один файл, собрать все листы в одной рабочей книге;

2) При помощи надстройки для сбора данных на итоговый лист, объединить данные с разных листов.

А знаете ли Вы, что существует возможность выборочно собрать данные с определенных листов различных рабочих книг?