Как объединить несколько файлов 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. Примените дополнительные преобразования, если применимо (удалите столбцы, нормализуйте метки). Затем «Закрыть и загрузить».

Если при попытке добавления данных вы получили много пустых столбцов, это вполне ожидаемо: вы объединяли разные структуры. Для объединения по ключу используйте функцию «Объединение» с соответствующим ключом . Это работает в Office 365 как на 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 или 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 . Он также поддерживает работу с другими форматами Office ( 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: полное руководство и все методы

Исаак

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