Советы и лайфхаки по работе с Excel
Например, мы имеем много рабочих книг Excel, и мы хотим собрать все рабочие листы со всех рабочих книг в один файл. Для этого откройте книгу, куда вы хотите собрать все листы, откройте Visual Basic Editor ( Alt + F11 ), добавьте новый бланк модуля ( Insert — Module ) и скопируйте текст макроса туда:
Dim x As Integer
On Error GoTo ErrHandler
FilesToOpen = Application.GetOpenFilename _
(FileFilter:= «Microsoft Excel Files (*.xls), *.xls» , _
MultiSelect:= True , Title:= «Files to Merge» )
If TypeName(FilesToOpen) = «Boolean» Then
MsgBox «Have been chosen no files!»
После этого можете вернуться в Excel и запустить макрос через меню Tools — Macro — Macros (Alt + F8) . Появится диалоговое окно, где нужно выбрать один или несколько (удерживая Ctrl ) файлов, листы из которых мы хотим добавить к текущей книги.
Если вы используете другое расширение Excel файлов (например, *.xlsx, *.xlsm, *.xlsb и т.п.), то вам необходимо будет внести соответствующие изменения в седьмую строку кода макроса.
Как собрать данные из разных рабочих книг на итоговый лист?
Некоторым пользователям приложения Microsoft Excel приходится решать задачу сбора данных из разных рабочих книг. Кому-то для формирования месячных, квартальных или годовых отчетов, а кому-то для составления реестров или баланса. При этом многократно повторяются такие операции как открытие файла, выделение определенного диапазона, копирование значений выделенного диапазона, закрытие файла, вставка скопированных значений на отдельный итоговый лист. Такие действия пользователей можно и нужно автоматизировать, ускоряя консолидацию данных и делая свою работу более эффективной.
Сбор данных из различных книг Excel на один лист в два этапа
В более ранних публикациях рассматривались инструменты, предназначенные для решения других задач, но при помощи которых можно собрать данные из разных файлов Excel на итоговый лист в два этапа.
Далее речь пойдет о том, как проделать все тоже самое, но в одно действие.
Сбор данных из нескольких рабочих книг Excel на отдельный лист
Для быстрого объединения данных, расположенных в разных рабочих книгах можно использовать надстройку для Excel.
Надстройка позволяет копировать заданные пользователем диапазоны данных на всех заданных листах всех выбранных рабочих книг и вставлять скопированные данные на новый лист активной рабочей книги. При работе с этой надстройкой пользователь имеет возможность:
1) Выбирать нужные рабочие книги для последующей обработки;
2) Осуществлять выбор листов по именам, по номерам, по заданному значению в ячейках выделенного диапазона;
3) Задавать диапазоны ячеек для копирования;
а) Используемые диапазоны — это диапазоны, начинающиеся с первой используемой ячейки и заканчивающиеся последней используемой ячейкой;
б) Предварительно выделенные диапазоны на каждом листе — диапазоны, выделенные пользователем самостоятельно, исходя из его потребностей по консолидации данных;
в) Одноименные диапазоны — диапазоны с одинаковым адресом на каждом листе, указанным на активном рабочем листе;
г) Выборочные диапазоны — диапазоны ячеек, начинающиеся с выбранной ячейки и заканчивающиеся:
— концом листа (последней используемой ячейкой);
— последней заполненной ячейкой в заданном столбце;
— последней заполненной ячейкой в заданной строке;
д) Именованные диапазоны.
4) При вставке данных на итоговый лист заменять формулы результатами их вычислений (при отсутствии объединенных ячеек);
5) При сборе данных на итоговый лист вставлять связи, при этом изменения значений в исходных диапазонах ячеек будут отображаться на итоговом листе;
6) Подставлять имена листов перед вставляемыми на итоговый лист диапазонами;
7) Подставлять имена рабочих книг перед вставляемыми на итоговый лист диапазонами;
8) Выбирать вертикальное либо горизонтальное размещение данных на листе с итогами;
9) Сохранять все настройки диалогового окна для последующих сеансов работы.
*В зависимости от выбранной опции диапазоны будут располагаться один ниже другого (вертикальное расположение), либо один правее другого (горизонтальное расположение).
После установки надстройки останется лишь запустить программу нажатием одной кнопки из главного меню Excel или с ленты, в зависимости от версии Excel и в диалоговом окне выбрать необходимые параметры для сбора информации.
В случае, если итоговый лист содержит ненужные пустые строки, можно провести их автоматическое удаление при помощи надстройки для выборочного удаления строк.