Excel объединение листов в один: Объединить листы в excel в один

Глава 5. Объединение листов Excel в Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

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

Рис. 5.1. Доступные Таблицы в окне редактора Power Query

Скачать заметку в формате Word или pdf, примеры в формате архива

Объединение таблиц и диапазонов в текущем файле

Откройте файл с примерами Consolidate Tables—Start. xlsx. В файле три листа с информацией о подарочных сертификатах, выданных spa-салоном. Сотрудник, создавший файл, не указывал в Таблицах дату выпуска сертификатов, а размещал данные на отдельных листах по месяцам: Jan 2008, Feb2008,  Mar 2008. Для анализа данные желательно объединить, и Power Query справится с этим быстрее, чем ручная обработка.

Создайте пустой запрос: пройдите по меню Данные –> Получить данные –> Из других источников –> Пустой запрос. В окне редактора Power Query в строке формул ведите (рис. 5.1):

=Excel.CurrentWorkbook()

Нажмите Enter. Вы увидите доступные объекты текущей книги: Таблицы, именованные диапазоны, подключения к внешним источникам данных. К сожалению, нельзя получить перечень листов файла.

Как вы узнали из главы 4, можно щелкнуть пробел рядом с зелеными словами в столбце

Content для предварительного просмотра данных Table (рис. 5.2).

Рис. 5.2. Предварительный просмотр содержимого Table

В главе 4 вы также узнали, что кликнув на двуглавую стрелку в верхней части столбца Content, вы развернете содержимое Таблиц, сохраняя сведения из столбца Name. Итак, кликните двуглавую стрелку, в открывшемся окне настроек снимите флажок Использовать исходное имя столбца как префикс, нажмите Ok:

Рис. 5.3. Импорт и объединение данных с добавлением столбца с именем таблицы; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Преобразуйте имена таблиц в даты (подробнее см. главу 4):

• Щелкните правой кнопкой мыши столбец Name –> Замена значений
• Заменить символ _ (подчеркивание) на ˽ 1,˽ (пробел, единица, запятая, пробел)
• Щелкните правой кнопкой мыши столбец Name –> Тип изменения –> Дата
• Перейдите на вкладку Преобразование –> Дата –> Месяц –> Конец месяца
• Щелкните правой кнопкой мыши столбец Name –> Переименовать –> Конец месяца
• Измените имя запроса на Подарочные сертификаты

Рис. 5.4. Финальный вид запроса

Перейдите на вкладку Главная и кликните Закрыть и загрузить. Объединенные данные будут размещены на новом листе Excel. К сожалению, запрос содержит кучу ошибок:

Рис. 5.5. Данные объединены на листе Excel; правда, вкрались ошибки

Не страшно. Наведите курсор мыши на запрос Подарочные сертификаты

, и во всплывающем окне кликните Изменить. Снова откроется редактор Power Query. В области ПРИМЕНЕННЫЕ ШАГИ выберите первый шаг – Источник (цифра 1 на рис. 5.6). Вы заметите, что теперь у вас четыре таблицы в списке. Добавилась таблица Подарочные сертификаты, которая была создана в результате запроса. Чтобы избавиться от дублирования, необходимо добавить фильтрацию таблиц, участвующих в запросе. Кликните стрелочку возле названия столбца Name (2), выберите опцию Текстовые фильтры –> Не содержит. Подтвердите, что вы хотите вставить шаг. В окне Фильтрация строк выберите Не содержит – Подарочные (3):

Рис. 5.6. Фильтрация таблиц по имени

Нажмите Ok. В редакторе Power Query перейдите на вкладку Главная. Кликните кнопку Закрыть и загрузить. Теперь запрос содержит 62 строки; ошибок нет.

Существует и вторая возможность избавиться от ошибок – убрать дубли. Откройте редактор Power Query. Перейдите на шаг Измененный тип. Выберите столбец Name и на вкладке Главная кликните Удалить строки –> Удалить ошибки. Подтвердите, что вы хотите вставить новый шаг в середину запроса. На вкладке Главная кликните Закрыть и загрузить.

Объединение диапазонов и листов

Данные на листах Excel могут располагаться не в Таблицах. Напомню, что Power Query «не видит» листы Excel. Поэтому исходные данные можно организовать в именованные диапазоны. Это можно сделать, например, с помощью определения области печати. Трюк работает потому, что имя области печати является именем динамического диапазона.

Перейдите на лист Jan 2008. На вкладке Разметка страницы

кликните Печатать заголовки. На закладке Лист введите A:D в поле Выводить на печать диапазон, кликните Ok.

Рис. 5.7. Выбор области печати

Повторите процедуру для листов Feb 2008 и Mar 2008. Создайте пустой запрос, и в строке формул введите: =Excel.CurrentWorkbook(). Нажмите Enter. Вы увидите список трех таблиц и трех именованных диапазонов:

Рис. 5.8. Объекты книги Excel, доступные для импорта в Power Query

Чтобы не дублировать данные, отфильтруйте столбец Name: Заканчивается на – печати. Нажмите кнопку Развернуть в верхней части столбца Content. Оставьте выбранными все столбцы, снимите флажок Использовать исходное имя столбца как префикс. Обратите внимание на отличия от предыдущего случая. Показаны все строки в выбранных столбцах диапазона печати:

Рис. 5.9. Необработанный рабочий лист

Выполним дополнительную очистку данных:

• Главная –> Удалить строки –> Удалить верхние строки –> 2
• Главная –> Использовать первую строку в качестве заголовков
• Столбец Cert Number –> Фильтр –> снимите флажок c null
• Щелкните правой кнопкой мыши столбец Cert Number –> Тип изменения –> Целое число
• Выберите столбец Cert Number
• Закладка Главная –> Удалить строки –> Удалить ошибки
• Выберите столбец Cert Number. Удерживайте нажатой клавишу Shift выберите столбец Service
• Щелкните правой кнопкой мыши один из выбранных заголовков столбцов –> Удалить другие столбцы
• Измените имя запроса на Все листы
• Главная
  –> Закрыть и загрузить

При работе с областями печати рекомендуется ограничивать область печати необходимыми строками и столбцами. В примере выше мы выбрали целиком столбцы, что привело к импорту в Power Query около 3 млн. строк с трех листов. Наверное, вы заметили, как медленно выполнялись некоторые команды!

Агрегирование данных из других книг

Вам нужно создать список книг Excel и извлечь их содержимое, аналогично тому, что вы сделали в главе 4, когда вы извлекли содержимое файлов CSV.

Создайте новую книгу Excel. Создать новый запрос: Данные –> Получить данные –> Из файла –> Из папки. Выберите папку Source Files. В списке есть как файлы Excel, так и иные файлы:

Рис. 5.10. Файлы, доступные в папке Source Files

Нажмите Преобразовать данные, и отфильтруйте файлы Excel:

• Щелкните правой кнопкой мыши столбец Extension –> Преобразование –> нижний регистр
• Фильтр столбца Extension –> Текстовые фильтры –> Начинается с… –> .xlsx
• Выберите столбцы Content имя Name –> щелкните правой кнопкой мыши –> Удалить другие столбцы

У вас может возникнуть соблазн нажать кнопку Объединить файлы…

Рис. 5.11. Объединить файл

… и, к сожалению, Power Query позволит вам это сделать. Однако, вы обнаружите, что Power Query сделает что-то весьма странное. Чтобы откатить импорт, перейдите в область ПРИМЕНЕННЫЕ ШАГИ и удалите все шаги после шага Другие удаленные столбы.

Раз вы не можете объединить и импортировать файлы простым методом, пойдем трудным способом:

• В редакторе Power Query перейдите на вкладку Добавление столбца –> Настраиваемый столбец
• Введите формулу =Excel. Workbook([Content])
• Нажмите кнопку Ok
• Щелкните правой кнопкой мыши столбец Content –> Удалить

Новый пользовательский столбец содержит все объекты, к которым можно подключиться, включая все Таблицы Excel, именованные диапазоны и даже листы:

Рис. 5.12. Объекты, доступные для импорта

Столбец Пользовательская имеет двуглавую стрелку (значок расширения), поэтому его можно развернуть. Нажав на значок вы получаете список всех объектов трех файлов, доступных для импорта:

Рис. 5.13. Объекты, доступные для импорта

Столбец Kind показывает, что у вас есть Листы, определенное имя и Таблицы. Если не отфильтровать этот перечень объектов, у вас будет много дублей:

• Отфильтруйте столбец Kind, оставив только Sheet
• Отфильтруйте столбец Name, удалив файл Named
  Range.xlsx
• Выберите столбцы Name, Name. 1 и Data –> щелкните правой кнопкой мыши на заголовке одного из этих столбцов –> Удалить другие столбцы
• Кликните кнопку Развернуть у заголовка столбца Data (снимите настройки префикса)

Запрос теперь выглядит следующим образом:

Рис. 5.14. Запрос с шестью импортированными объектами Sheet

Преобразуем данные:

• Главная –> Использовать первую строку в качестве заголовков
• Щелкните правой кнопкой мыши на заголовке столбца Workbookxlsx –> Переименовать –> Source File
• Щелкните правой кнопкой мыши заголовке столбца Jan 2008 column –> Переименовать –> Month
• Выберите столбец Amount –> Главная –> Удалить строки –> Удалить ошибки
• Измените имя запроса FromExcelFiles
• Главная –> Закрыть и загрузить

Данные загружаются в Таблицу на листе Excel. На их основе можно создать сводную таблицу, чтобы увидеть, что вы смогли извлечь из внешних файлов Excel:

Рис. 5.15. Сводная позволяет проверить, что же вы импортировали

Видно, что вы успешно извлекли данные из двух Excel-файлов, каждый из которых содержит по три листа. В общей сложности извлекли более 12 000 записей.

Подытожим

Функция Excel.CurrentWorkbook() считывает все объекты текущего файла. Поскольку она является первой в применяемых шагах запроса, вы получаете эффект рекурсии. При обновлении Power Query добавит объекты, созданные в процессе выполнения запроса, к тем, что существовали первоначально. Стратегии защиты заключается в фильтрации объектов по имени или фильтрации ошибок в ключевых столбцах. Тестируйте метод фильтрации с помощью нескольких команд Обновить все.

Функция Excel.Workbook([Content]) не вызывает проблем с рекурсией, поскольку исходные данные считываются из внешних книг, а результаты запроса сохраняются в текущей книге. Но функция Excel.Workbook([Content]) создает иную проблему: она извлекает листы в дополнение к диапазонам и таблицам. Это может приводить к дублированию данных. Обратите особое внимание на фильтрацию столбца Kind (вид), чтобы избежать этой проблемы. Даже если при построении запроса в книге имеется только один вид данных, полезно применить фильтрацию, чтобы защитить решение от будущих изменений.

5 различных способов объединить файлы Excel в одну книгу • BUOM

Автор: редакционная команда Indeed

2 апреля 2022 г.

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

Почему важно объединять файлы Excel в одну книгу?

Объединение файлов Excel в одну работу может быть важно по многим причинам, в том числе:

Это может помочь вам работать быстрее

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

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

Это может помочь вам представить данные

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

Это может помочь вам улучшить документацию

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

Как объединить файлы Excel в одну книгу 5 способами

Если вы хотите объединить файлы Excel в одну книгу, рассмотрите следующие пять способов:

1. Скопируйте и вставьте ячейки из исходного файла в целевые файлы.

Один из самых быстрых способов объединить две книги в Excel — вручную скопировать ячейки из одной книги в другую. Этот метод может быть не таким быстрым, если вам нужно скопировать несколько рабочих листов или отдельных диапазонов ячеек из одного файла в другой. Чтобы следовать этому методу:

1. Выберите ячейки: выберите диапазон ячеек в одной книге, пока у вас открыта другая.

2. Копировать: как только вы скопируете свои ячейки, вставьте их во второй файл.

3. Удалить: для экономии памяти рассмотрите возможность удаления первого файла после копирования всех необходимых данных.

2. Ручное копирование

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

1. Выберите листы: начните с выбора листов в исходных книгах, которые вы хотите скопировать. Если в файле, который вы хотите скопировать, есть несколько листов, удерживайте нажатой клавишу Ctrl и щелкните вкладку каждого листа. Кроме того, вы можете выбрать все рабочие листы, перейдя к первому рабочему листу, который вы хотите скопировать, удерживая нажатой клавишу Shift и щелкнув последний рабочий лист.

2. Копировать данные: щелкните правой кнопкой мыши любой выбранный лист. Выберите «Переместить» или «Копировать», затем выберите целевую книгу. Затем у вас есть возможность сделать копию исходных листов для консолидации ваших данных.

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

3. Используйте формулу

Другой способ объединить две книги — использовать формулу ДВССЫЛ. Чтобы этот метод работал, убедитесь, что обе книги открыты во время передачи, и рассмотрите следующие два шага:

1. Копировать и вставить: скопируйте первую исходную ячейку в книге, из которой вы хотите перенести данные. Вставьте его в целевой файл, используя сочетание клавиш Ctrl+V или щелкнув правой кнопкой мыши и выбрав Вставить. Нажмите кнопку «Ссылка» в левом нижнем углу окна вставки. Это позволяет вам извлечь полный путь к файлу, а не его содержимое.

2. Примените формулу: Оберните формулу ДВССЫЛ вокруг пути ссылки. Затем разделите его на имя файла, имя листа и ссылку на ячейку. Это дает вам возможность позже изменить одну из этих ссылок.

4. Объединяйте файлы с помощью макроса

Если вы хотите объединить файлы с помощью макроса VBA, рассмотрите следующие шаги:

1. Выберите ленту: на ленте «Разработчик» с правой стороны установите флажок «Разработчик». Если лента «Разработчик» не отображается, щелкните правой кнопкой мыши любую ленту и выберите «Настроить ленту». Из ваших параметров в этом окне вы можете включить ленту разработчика.

2. Использование модуля: щелкните Visual Basic в левой части ленты разработчика. Щелкните правой кнопкой мыши имя вашей книги и выберите «Вставить», затем «Модуль». Как только вы это сделаете, появится модуль, требующий кода Visual Basic, и вы сможете ввести следующую информацию:

```
Подпрограмма слияния файлов ()
'Объединяет все файлы в папке с основным файлом.
'Определить переменные:
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog Как файлDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet как рабочий лист
Установите mainWorkbook = Application.ActiveWorkbook
Установите tempFileDialog = Application.fileDialog (msoFileDialogFilePicker)
'Разрешить пользователю выбирать несколько книг
tempFileDialog. AllowMultiSelect = Истина
numberOfFilesChosen = tempFileDialog.Show
'Пройтись по всем выбранным книгам
Для i = 1 для tempFileDialog.SelectedItems.Count
'Открыть каждую книгу
Workbooks.Open tempFileDialog.SelectedItems(i)
Установите sourceWorkbook = ActiveWorkbook
'Скопируйте каждый рабочий лист в конец основной рабочей книги
Для каждого tempWorkSheet в sourceWorkbook.Worksheets
tempWorkSheet.Copy после:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Следующий tempWorkSheet
'Закрыть исходную книгу
sourceWorkbook.Close
Далее я
Конец сабвуфера
### 5. Используйте инструменты получения и преобразования
В версии Excel 365 вы можете использовать функцию Get &  Инструмент преобразования для передачи данных.  Этот инструмент может передавать один лист или папки с документами.  Если вы хотите использовать инструменты «Получить и преобразовать» для объединения ваших файлов, рассмотрите следующие шаги:
1.  **Создайте целевую папку:** Создайте папку, содержащую все элементы, которые вы хотите импортировать.  Чтобы подготовить папку к передаче, скопируйте путь к файлу непосредственно из проводника.  Щелкните ленту данных в целевом файле Excel.
2. **Объедините файлы:** нажмите «Получить данные» на ленте данных, затем выберите «Из файла и папки».  Вставьте путь, скопированный на предыдущем шаге, в появившийся браузер и выберите ОК.  Как только вы нажмете «ОК», Excel отобразит все файлы в пути.  Если все, что вы хотите передать, присутствует, нажмите «Объединить».  Если какие-либо файлы отсутствуют, вы можете использовать параметр «Редактировать», чтобы добавить файлы вручную.
3. **Проверка результатов**. Перед подтверждением переноса Excel отображает пример окончательного результата.  Если то, что вы видите, выглядит правильно, продолжайте.  Если нет, вы можете настроить макет информации в соответствии с вашими потребностями, прежде чем завершить передачу данных.
*Обратите внимание, что ни одна из организаций, упомянутых в этой статье, не связана с компанией Indeed. *

Как объединить файлы Excel в один

В этом учебном пособии показаны три способа объединения файлов Excel в один: копирование вкладок листа, запуск VBA и использование инструмента «Копировать рабочие листы».

Гораздо проще обрабатывать данные в одном файле, чем переключаться между многочисленными источниками. Однако объединение нескольких книг Excel в один файл может быть громоздким и длительным процессом, особенно если книги, которые необходимо объединить, содержат несколько листов. Итак, как бы вы подошли к проблеме? Будете ли вы копировать листы вручную или с помощью кода VBA? Или вы используете один из специализированных инструментов для объединения файлов Excel? Ниже вы найдете несколько хороших способов справиться с этой задачей.

Примечание. В этой статье мы рассмотрим, как скопировать листы из нескольких книг Excel в одну книгу. Если вы ищете быстрый способ скопировать данные из нескольких рабочих листов на один лист, вы найдете подробное руководство в другом руководстве: Как объединить несколько листов в один.

Как объединить два файла Excel в один, скопировав листы

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

1. Откройте рабочие книги, которые вы хотите объединить.
2. Выберите рабочие листы в исходной книге, которые вы хотите скопировать в основную книгу.

   Чтобы выбрать несколько листов, используйте один из следующих способов:

   • Чтобы выбрать соседние листы, щелкните вкладку первого листа, который вы хотите скопировать, нажмите и удерживайте клавишу Shift, а затем щелкните вкладку последнего листа. Это выберет все рабочие листы между ними.
   • Чтобы выбрать несмежные листы, удерживайте клавишу Ctrl и щелкните вкладку каждого листа отдельно.
3. Выбрав все рабочие листы, щелкните правой кнопкой мыши любую из выбранных вкладок и выберите Переместить или скопировать… .
4. В диалоговом окне Переместить или скопировать выполните следующие действия.
   • В раскрывающемся списке Переместить выбранные листы в книгу выберите целевую книгу, в которую вы хотите объединить другие файлы.
   • Укажите, куда именно должны быть вставлены скопированные выступы листа. В нашем случае мы выбираем вариант move to end .
   • Выберите поле Создать копию , если вы хотите, чтобы исходные листы остались в исходном файле.
   • Нажмите OK, чтобы завершить процесс слияния.

На скриншоте ниже показан результат — листы из двух файлов Excel объединены в один. Чтобы объединить вкладки из других файлов Excel, повторите описанные выше шаги для каждой книги отдельно.

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

Как объединить файлы Excel с помощью VBA

Если у вас есть несколько файлов Excel, которые необходимо объединить в один файл, более быстрым способом будет автоматизировать процесс с помощью макроса VBA.

Ниже вы найдете код VBA, который копирует все листы из всех файлов Excel, которые вы выбираете, в одну книгу. Этот макрос MergeExcelFiles написан Алексом, одним из наших лучших гуру Excel.

Важное примечание! Макрос работает со следующей оговоркой — файлы для слияния не должен быть открыт физически или в памяти. В таком случае вы получите ошибку времени выполнения.

Sub MergeExcelFiles() Dim fnameList, fnameCurFile как вариант Dim countFiles, countSheets как целое число Dim wksCurSheet как рабочий лист Dim wbkCurBook, wbkSrcBook как рабочая книга fnameList = Application.GetOpenFilename(FileFilter:=»Книги Microsoft Excel (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm», Title:=»Выберите файлы Excel для объединения» , Множественный выбор: = Истина) Если (vbBoolean <> VarType(fnameList)) Тогда Если (UBound(fnameList) > 0) Тогда количество файлов = 0 количество листов = 0 Application. ScreenUpdating = Ложь Приложение.Расчет = кслкалкулатионмануал Установите wbkCurBook = ActiveWorkbook Для каждого fnameCurFile в fnameList количество файлов = количество файлов + 1 Установите wbkSrcBook = Workbooks.Open (имя файла: = fnameCurFile) Для каждого wksCurSheet в wbkSrcBook.Sheets количество листов = количество листов + 1 wksCurSheet.Copy после:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count) Следующий wbkSrcBook.Close SaveChanges:=False Следующий Application.ScreenUpdating = Истина Приложение. Расчет = кслкалкулатуатаматимат MsgBox «Обработано» & countFiles & «файлы» & vbCrLf & «Объединено» & countSheets & «рабочие листы», Title:=»Объединить файлы Excel» Конец, если Еще MsgBox «Файлы не выбраны», Title:=»Объединить файлы Excel» Конец, если Конец сабвуфера

Как добавить этот макрос в книгу

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

1. Нажмите клавиши ALT + F11, чтобы открыть редактор Visual Basic.
2. Щелкните правой кнопкой мыши ThisWorkbook на левой панели и выберите Вставить > Модуль в контекстном меню.
3. В появившемся окне (окно кода) вставьте приведенный выше код.

Подробные пошаговые инструкции см. в разделе Как вставить и запустить код VBA в Excel.

Кроме того, вы можете загрузить макрос в файле Excel, открыть его рядом с целевой книгой (включить макрос, если будет предложено), затем переключиться на свою книгу и нажать Alt + F8, чтобы запустить макрос. Если вы новичок в использовании макросов в Excel, выполните подробные действия, описанные ниже.

Как использовать макрос MergeExcelFiles

Откройте файл Excel, в который вы хотите объединить листы из других книг, и выполните следующие действия:

1. Нажмите Alt + F8, чтобы открыть макрос диалог.
2. В разделе Имя макроса выберите MergeExcelFiles и нажмите Выполнить .
3. Откроется стандартное окно проводника, вы выбираете одну или несколько книг, которые хотите объединить, и нажимаете Открыть . Чтобы выбрать несколько файлов , удерживайте нажатой клавишу Ctrl, щелкая имена файлов.

В зависимости от того, сколько файлов вы выбрали, дайте макросу несколько секунд или минут для их обработки. После завершения макроса он сообщит вам, сколько файлов было обработано и сколько листов было объединено:

Объединение нескольких файлов Excel в один с помощью Ultimate Suite

Если вам не очень удобно работать с VBA и вы ищете более простой и быстрый способ объединения файлов Excel, взгляните на инструмент Copy Sheets , один из 70+ раз функции сохранения, включенные в наш Ultimate Suite for Excel.

С Ultimate Suite объединить несколько книг Excel в одну очень просто, как раз-два-три (буквально, всего 3 быстрых шага). Вам даже не нужно открывать все рабочие книги, которые вы хотите объединить.

1. Открыв главную рабочую книгу, перейдите на вкладку Ablebits Data > группу Merge и щелкните Копировать листы > Выбранные листы в одну рабочую книгу .
2. В диалоговом окне Копировать рабочие листы выберите файлы (и, возможно, рабочие листы), которые вы хотите объединить, и нажмите Далее .

   Советы:

   • Чтобы выбрать все листы в определенной книге, просто поставьте галочку в поле рядом с именем книги, все листы в этом файле Excel будут выбраны автоматически.
   • Чтобы объединить листы из закрытых книг , нажмите кнопку Добавить файлы… и выберите столько книг, сколько хотите. Это добавит выбранные файлы только в окно «Копировать листы», не открывая их в Excel.
   • Чтобы скопировать только определенную область в определенную рабочую книгу, наведите указатель мыши на имя листа, затем щелкните значок Свернуть диалоговое окно и выберите нужный диапазон. По умолчанию копируются все данные.
3. При необходимости выберите один или несколько дополнительных параметров и нажмите Копировать . На скриншоте ниже показаны настройки по умолчанию: Вставить все (формулы и значения) и Сохранить форматирование .

Дайте мастеру Copy Worksheets несколько секунд для обработки и наслаждайтесь результатом!

Чтобы поближе познакомиться с этим и другими инструментами слияния для Excel, загрузите ознакомительную версию Ultimate Suite.

Другие способы объединения листов Excel и данных

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

• Объединение данных в Excel и объединение нескольких листов — как объединить данные из нескольких листов с помощью одной из функций суммирования и как объединить несколько листов в один без копирования и вставки.
• Объединить листы Excel с одинаковыми именами в один — три способа объединить листы с одинаковыми именами в один лист.
• Объединение двух столбцов в Excel без потери данных — как объединить несколько столбцов Excel в один с помощью формул, надстройки «Блокнот» и «Объединить ячейки».
• Объединение строк в Excel без потери данных — четыре способа объединения строк в Excel: объединение данных из нескольких строк в одну строку, объединение повторяющихся строк в одну, многократное объединение блоков строк, копирование совпадающих строк из двух разных электронных таблиц.

Доступные загрузки

Макрос для объединения нескольких файлов Excel (файл .xlsm)
Ultimate Suite 14-дневная полнофункциональная версия (файл .exe)

Война в Украине!

Чтобы поддержать Украину и спасти жизни , пожалуйста, посетите эту страницу.

4 метода копирования всех рабочих листов друг под другом

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

Содержание

• Метод 1: копирование и вставка листов вручную
• Метод 2: использование формулы ДВССЫЛ для объединения листов
  • Подход
  • Загрузка
• Метод 3: 40029 909019 Macro Объединить листы с помощью «Professor Excel Tools»
• (новый) Способ 5: объединить листы с помощью буфера обмена Office
  • Также интересно:

Способ 1: скопировать и вставить рабочие листы вручную

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

• Старайтесь как можно больше использовать сочетания клавиш. Например, для выбора всего рабочего листа (Ctrl + A), копирования данных (Ctrl + C), перехода к объединенному рабочему листу (Ctrl + Page Up или Page Down) и вставки скопированных ячеек (Ctrl + V).
• Также может помочь сочетание клавиш Ctrl на клавиатуре и нажатия на маленькую стрелку в левом нижнем углу рабочего листа. Таким образом вы переходите к первому или последнему рабочему листу в книге Excel.
• Этот метод особенно удобен, если вам нужно объединить листы один раз. Если вам нужно делать это неоднократно (например, вы получаете новые входные данные каждую неделю или месяц), вероятно, лучше проверить методы со 2 по 4 ниже.

Способ 2. Использование формулы ДВССЫЛ для объединения листов

Формулы Excel можно использовать для объединения данных со всех листов. Основная формула НЕПРЯМАЯ.

Этот метод имеет некоторые недостатки.

• Формула ДВССЫЛ в целом медленная, потому что она изменчива. Это означает, что он вычисляет каждый раз, когда Excel что-то вычисляет.
• Использование комбинации INDIRECT обычно нестабильно и подвержено ошибкам.
• Требуется некоторая работа, чтобы установить формулу ДВССЫЛ.

С другой стороны, у него есть одно большое преимущество: если вы потратите усилия на его настройку, этот метод будет динамическим. Это означает, что когда ваш ввод обновляется, объединенный рабочий лист также обновляется.

Подход

Настройте НЕПРЯМУЮ формулу для объединения листов.

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

• Столбец A содержит имя листа.
• Столбец B содержит номер строки.
• Начиная с столбца C, вы должны добавить буквы столбца.

Итак, давайте предположим, что вы хотите получить значение из ячейки A1 листа Sheet1. Затем вам понадобятся все части «Лист1», столбец «А» и строка «1». Объединение их в формулу ДВССЫЛ приведет к следующей формуле. Формула в ячейке C4 имеет вид =ДВССЫЛ(“‘”&$A4&”‘!”&C$2&$B4) .

Скачать

Скопируйте этот рабочий лист в файл Excel, и все листы будут автоматически объединены.

Хотите сэкономить время? Мы подготовили рабочий лист, который может автоматически объединять листы. Что ты должен делать? Загрузите эту книгу (~7 МБ) и скопируйте единственный лист в свою книгу. Вот и все.

Обратите внимание на следующие комментарии.

• Этот метод требует включения макросов (автоматически создается список всех рабочих листов в вашей книге). Когда вы захотите сохранить книгу, вам будет предложено переключиться на формат файла XSLM.
• Эта модель работает до 50 листов по 200 рядов каждый (готовится 10000 ячеек). Если вам нужно больше, вы должны расширить его. Причина такого ограничения в том, что файл уже довольно большой и требует некоторой вычислительной производительности.

Способ 3. Объединение листов с помощью макроса VBA

Вы достаточно уверены в себе, чтобы использовать простой макрос VBA? Вставьте следующий код в новый модуль VBA. Если вам нужна помощь с VBA, обратитесь к этой статье.

 Sub Merge_Sheets()
    'Вставить новый рабочий лист
    Листы.Добавить
    
    'Переименуйте новый рабочий лист
    ActiveSheet.Name = "ProfEx_Merge_Sheet"
    
    'Прокрутите рабочие листы и скопируйте их на новый рабочий лист
    Для каждого ws в рабочих листах
        ws.Активировать
        
        'Не копировать объединенный лист снова
        Если ws.Name <> "ProfEx_Merged_Sheet" Тогда
            ws.UsedRange.Select
            Выбор.Копировать
            Листы ("ProfEx_Merge_Sheet"). Активировать
            
            'Выбрать последнюю заполненную ячейку
            ActiveSheet.Range("A1048576").Выбрать
            Выбор.Конец(xlUp).Выбрать
            
            'Для первого рабочего листа вам не нужно спускаться на одну ячейку вниз
            Если ActiveCell.Address <> "$A$1" Тогда
                ActiveCell.Смещение(1, 0).Выбрать
            Конец, если
            
            «Вместо того, чтобы просто вставить, вы также можете вставить как ссылку, как значения и т.  д.
            ActiveSheet.Paste
        
        Конец, если
        
    Следующий
Конец суб 

Способ 4. Объедините листы с помощью «Professor Excel Tools»

Объедините рабочие листы с помощью Professor Excel Tools.

Любишь пользоваться самым удобным способом? Попробуйте надстройку Excel, Professor Excel Tools.

• Просто выберите все рабочие листы, которые вы хотите объединить,
• нажмите кнопку «Объединить листы» и
• нажмите «Пуск».

Кроме того, вы можете уточнить нужные параметры: Вы хотите добавить исходное имя листа в столбец A? Без проблем.

Также определите режим копирования и вставки, как показано на снимке экрана справа.

---

Эта функция включена в надстройку Excel «Professor Excel Tools»

(без регистрации, загрузка начинается напрямую)

---

(новый) Способ 5: объединение листов с использованием буфера обмена Office

Первый метод выше уже имел дело с копированием и вставкой листов вручную. Здесь есть еще одна хитрость: используйте буфер обмена Excel для объединения листов. На самом деле это довольно просто, просто выполните следующие действия.

Шаги 1–3. Используйте буфер обмена Office для быстрого копирования листов друг под другом.

1. Открытие буфера обмена. Щелкните маленькую стрелку в правом нижнем углу раздела «Буфер обмена» (на ленте «Главная»).
2. Теперь вы можете увидеть буфер обмена.
3. Затем просмотрите каждый рабочий лист. Скопируйте все диапазоны, которые вы позже захотите объединить на одном листе.

Шаги 4–5: Используйте кнопку «Вставить все» в буфере обмена, чтобы объединить листы.

4. Теперь вы можете видеть все скопированные диапазоны в буфере обмена.
5. Перейдите на лист, где вы хотите вставить их друг под другом. Выберите первую ячейку.
6. Нажмите «Вставить все»

Готово. Особенно с большими файлами этот метод может сэкономить некоторое время по сравнению с методом № 1 выше.