Как объединить несколько файлов Excel: полное пошаговое руководство

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

Конечно, не всегда хорошая идея смешивать все в одном файле; Excel позволяет связывать данные между рабочими книгами с формулами, что иногда избавляет от необходимости открывать огромные файлы и ускоряет работу. В этом руководстве вы узнаете, когда вам нужна консолидация, как объединять данные по позиции или категории, как использовать Power Query для добавления данных или создания перекрестных ссылок, а также увидите реальный пример работы с CSV-файлами по серийному номеру, макрос VBA для объединения рабочих книг в папке и, если захотите, веб-инструменты для объединения Excel «на лету» (со всеми плюсами и минусами конфиденциальности).

Когда объединять, а когда связывать

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

Если у вас большой проект с несколькими зонами, возможно, стоит оставить листы отдельно и извлекать формулы, которые ссылаются на другие файлы. Таким образом, вы уменьшите количество видимых вкладок и по-прежнему сохраните четкий «центр управления». Но если вам нужен общий обзор или подготовка результатов для руководства, имеет смысл объединить всё в одну рабочую книгу (или консолидировать в одну). мастер-лист).

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

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

Собственные методы Excel для объединения листов и рабочих книг

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

Перемещать или копировать листы между рабочими книгами

Идеально подходит для группировки вкладок из нескольких файлов в одну книгу. Работает без плагинов и это быстро для нескольких файлов.

1. Откройте исходную и конечную книги. Расположитесь на листе, который вы хотите нести..
2. Щелкните правой кнопкой мыши по вкладке листа и выберите «Переместить или копировать». Вы также можете перейти в раздел «Главная» > «Ячейки» > «Формат»..
3. Выберите целевую книгу (существующую или «новую книгу»). Определите место вставки относительно существующих листьев.
4. Выберите «Создать копию», чтобы не потерять исходный лист, и подтвердите, нажав OK. Повторите для всех листов. вам нужно.
5. Чтобы переместить несколько вкладок одновременно, выберите несколько вкладок с помощью Shift или Ctrl и повторите процесс. Это короткий путь, который экономит много времени..

Консолидировать по позиции

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

1. Откройте каждый исходный лист и убедитесь, что таблица находится в том же месте и имеет ту же форму. Однородность здесь — это всё.
2. На листе назначения выберите верхнюю левую ячейку, в которой вы хотите отобразить консолидированные данные. Это будет якорь..
3. Перейдите в раздел Данные > Консолидация. Откроется окно со списком функций. доступны.
4. В разделе «Функция» выберите способ консолидации (Сумма, Среднее и т. д.). Подумайте, нужны ли вам итоги, средние значения, минимумы....
5. На каждом исходном листе выберите диапазон данных. Маршрут и дальность будут добавлены. на «Все ссылки».
6. После добавления всех ссылок нажмите кнопку «ОК». Вы получите единую таблицу по позиции.

Объединить по категориям

Идеально, когда листья делятся одинаковые метки строк или столбцов, но данные находятся в разных местах. Именно здесь важен текст метки: если вы укажете «Средн.» на одном листе и «Средн.» на другом, они не будут совпадать.

1. Открыть все исходные листы. Проверьте, что этикетки точно совпадают. по имени и написанию.
2. В целевой книге выберите начальную ячейку для консолидации. Поместите курсор туда, где вы хотите увидеть сводку..
3. Перейдите в раздел Данные > Консолидация. Выберите функцию консолидации (Сумма, Среднее…).
4. Отметьте, где находятся метки в разделе «Использовать метки в»: в верхней строке, в левом столбце или в обоих. Это сообщает Excel, как сопоставлять категории.
5. Выберите диапазоны каждого листа, включая выбранные метки. Маршруты появятся в разделе «Все ссылки»..
6. Подтвердите, нажав «Принять», чтобы создать главный лист. Excel присоединится по названию категории, а не по должности.

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

Присоединяйтесь и преобразуйтесь с Power Query

Power Query — это современный «движок данных» Excel. Он позволяет импортировать, преобразовывать, добавлять и объединять данные из нескольких источников, обновлять данные одним щелчком мыши и автоматизировать действия. Если вы работаете с большими объемами или вам нужны повторяющиеся процессы, ваш лучший союзник.

Чтобы объединить файлы из папки в одну таблицу, выполните следующие действия: Загрузите папку, отфильтруйте ее и используйте «Объединить файлы»..

1. Данные > Получить данные > Из файла > Из папки. Выберите содержащую папку из ваших файлов.
2. Вы увидите список файлов в папке и подпапках. Проверьте, что отображаются те, которые вам нужны..
3. Нажмите «Преобразовать данные», чтобы открыть Power Query для просмотра всех файлов. Это «машинное отделение».
4. Отфильтруйте столбцы, такие как «Расширение» или «Путь к папке», чтобы оставить только правильные типы (например, .xlsx или .csv). Вот как избежать смешивания яблок с грушами.
5. Выберите столбец «Содержимое» (двоичный, обычно первый) и перейдите в раздел «Главная» > «Объединить файлы». Откроется окно «Объединить файлы»..
6. Если вы не хотите использовать первый вариант, выберите «Образец файла» в раскрывающемся меню. Power Query анализирует его, чтобы обнаружить столбцы и соединители..
7. При желании установите флажок «Пропускать файлы с ошибками», чтобы исключить их из результата. Полезно, если есть файлы с неправильной структурой..
8. Вы принимаете. Запрос, консолидирующий данные, будет создан автоматически. каждого файла в одной таблице.

Результат не волшебный, но почти: Power Query создает вспомогательные запросы и шаги на основе выбранной команды, включая применение общих преобразованийЕсли завтра вы добавите в папку еще файлы, просто нажмите «Обновить», чтобы добавить их в набор.

• Автоматизация: обновления с новыми файлами без повторения процесса.
• легкость: Программирование не требуется; все визуально.
• Производительность: Обрабатывает большие объемы данных лучше, чем традиционное копирование/вставка.

Например, вы хотите объединить два CSV-файла по общему столбцу? «Серийный номер» как ключЗдесь важно различать «Добавить» и «Слить»:

• Append Таблицы располагаются друг над другом. Если заголовки совпадают не на 100%, вы увидите много столбцов с пустыми значениями. Это не то, что нужно для перекрёстных ссылок на информацию по ключу.
• скомбинировать объединяет таблицы по ключу, как в объединении базы данных (левый, внутренний, правый…). Подходит для «присоединения по серийному номеру».

Рекомендуемые шаги для вашего примера с двумя CSV-файлами (один из Apple Business Manager с 13 столбцами и 8.743 строками и один из MDM с 24+ столбцами и 6.012 строками): импортировать оба CSV-файла в Power Query, задайте правильные типы данных и используйте «Комбинированные запросы».

1. Данные > Получить данные > Из файла > Из текста/CSV, импортируйте ABM и MDM. Откройте каждый в редакторе.
2. Убедитесь, что «Серийный номер» на обоих устройствах имеет одинаковый тип (текст обычно является самым безопасным). При необходимости измените тип..
3. При активном запросе ABM выберите «Главная» > «Объединить запросы» > «Объединить запросы как новые». Выберите MDM в качестве таблицы для присоединения..
4. Выберите столбец «Серийный номер» в обеих таблицах. Устанавливает тип объединения:
   • Левый внешний (левый): сохраняет все ABM и приносит спички из MDM.
   • Внутренний (пересечение): только строки с порядковым номером, присутствующим в обоих.
   • Правый/Полный: другие варианты в зависимости от ваших потребностей.
5. Разверните столбец полученной таблицы, чтобы отобразить только интересующие вас столбцы MDM (установите/снимите флажок). Вы избегаете шума и лишних столбцов.
6. Примените дополнительные преобразования, если применимо (удалите столбцы, нормализуйте метки). Затем «Закрыть и загрузить».

Если вы попробовали Append и получили много столбцов со значением «null», то этого следовало ожидать: вы объединяли разные структуры. Для перехода по ключу используйте Merge с соответствующим объединением.. Это работает в O365 как на macOS (включая Apple Silicon), так и Windows, и у вас также будет преимущество в виде возможности обновления в один клик по мере поступления новых CSV-файлов.

Объединение рабочих книг с помощью макроса VBA

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

1. Откройте новую книгу (пункт назначения). Нажмите ALT + F11, чтобы открыть редактор VBA..
2. Вставьте модуль (Вставка > Модуль) и вставьте этот код, изменив путь и маску файла, если применимо:

Sub UnirHojasDeCarpeta()
  Dim ruta As String, nombre As String
  Dim sh As Worksheet
  ruta = "C:\MisDatos\Origen\"
  nombre = Dir(ruta & "*.xlsx")
  Do While nombre <> ""
    Workbooks.Open Filename:=ruta & nombre, ReadOnly:=True
    For Each sh In ActiveWorkbook.Sheets
      sh.Copy After:=ThisWorkbook.Sheets(1)
    Next sh
    Workbooks(nombre).Close SaveChanges:=False
    nombre = Dir()
  Loop
End Sub

Запустите, нажав F5, и дождитесь завершения. Вы получите все листы в вашем путеводителе Файлов .xlsx в папке, вставленных после первой вкладки. Вы можете изменить подстановочный знак на «*.xlsm» или «*.xlsb», и, конечно же, путь. Не забудьте включить макросы и предоставить доступ к объектной модели VBA, если у вас высокий уровень безопасности.

Онлайн-решения для объединения Excel: ASPOSE, DOCSOSO и GROUPDOCS

Если вы не хотите открывать Excel или работаете из Mac, Linux, Android o iOS, вы можете воспользоваться услугами веб-слияния. Они быстрые и кроссплатформенные., но помните о конфиденциальности: при загрузке таблиц с конфиденциальными данными вы рискуете раскрыть информацию на сторонних серверах.

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

• Объединение таблиц: XLSX, XLS, XLSB, XLSM, XLTX, XLT, XLTM, ODS.
• Сохранить в желаемом формате: PDF, объединить Excel с Word, DOCX, POWERPOINT, PPTX, XLS, XLSX, XLSM, XLSB, XLT, XLTX, XLTM, ODS, OTS, FODS, NUMBERS, ET, CSV, TSV, HTML, WEBP, SVG, TIFF, XPS, ZIP, MHTML, SQL, TEXT, TXT.
• Другие преимущества: быстрый способ слияния, комбинировать разные форматы в одном, легко сохранять как PDF, изображения или HTML, объединять файлы OpenDocument, выберите порядок объединенных файлов и решите, будет ли результат на нескольких листах или на одном.

ASPOSE предлагает набор веб-утилитарных программ для работы с документами. В вашем инструменте Excel вы можете загружать файлы На компьютере используйте Dropbox или вставьте URL-адрес. Вы можете объединить несколько листов или сохранить всё на одном листе. В разделе «Сохранить как» выберите XLSX. Нажмите «Объединить», и через несколько секунд Вы скачаете результат или отправите его в Dropbox.. Он также поддерживает работу с другими офисными форматами (Word, PDF, изображения и т. д.).

DOCSOSO очень прост: вы входите в систему, загружаете файлы Excel и нажимаете «Объединить Excel». В нем есть коробка, в которой все собрано на одном листе. Если вам подходит. Для простых задач более чем достаточно.

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

Другие простые методы и «экспертный» инструмент

В отдельных случаях применима традиционная процедура: скопировать и вставитьОткройте каждую книгу, выберите её, нажмите Ctrl + A, скопируйте Ctrl + C и вставьте на нужный лист. Повторите. Это быстро, если у вас два-три файла; если их десятки, это становится утомительно.

Вы также можете положиться на опцию Excel «Переместить или скопировать«объяснили выше: выберите нужные листы и перенесите их в итоговую книгу, не открывая редактор запросов и не программируя ничего. Чтобы реорганизовать представления и сохранить оригиналы, выберите «Создать копию».

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

1. Установите приложение и выберите опцию «Объединить Excel». Это вход в мастер слияния..
2. Импортируйте отдельные файлы или папки с помощью функций «Добавить файлы» и «Добавить папки». Инструмент отобразит иерархию.
3. Выберите файлы, которые вы хотите включить, и нажмите «Далее». Вот как вы точно настраиваете партию.
4. Выберите «Отдельные рабочие листы» (несколько вкладок) или «Один рабочий лист» и укажите путь вывода. Вы определяете окончательную структуру.
5. Нажмите «Объединить» и дождитесь завершения. Когда закончите, нажмите ОК. и проверьте результат.

Если вы рассматриваете ручные методы, помните об их ограничениях: Они не идеальны для больших файлов.Они требуют повторных действий, отнимают много времени, требуют профессионального владения Excel/VBA и сопряжены с риском потери иерархий или данных в случае ошибки. Для больших рабочих нагрузок Power Query или автоматизированный процесс (макрос или специальный инструмент) часто оказываются более надёжными.

Основная идея заключается в том, что у вас есть ряд вариантов в зависимости от вашего случая: консолидировать по позиции или категории Если структура выровнена; перемещение/копирование листов при реорганизации рабочих книг; Power Query для добавления или объединения по ключу с автоматическими обновлениями; макросы VBA для массовой загрузки десятков файлов; веб-сервисы или специализированное программное обеспечение, если для вас важна скорость и вы не работаете с конфиденциальными данными. Правильный выбор сэкономит вам время и нервы, а также позволит контролировать отчёты без ущерба для производительности или качества данных.

Связанная статья:

Как интегрировать Excel в PowerPoint: полное руководство и все методы

Исаак

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