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

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

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

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

В этой статье будет показано, как объединить две (или более) вкладки в Excel, а также некоторые дополнительные функции и методы, которые вы можете использовать.

Объединение вкладок в Excel – это просто

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

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

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

Шаги по объединению по позициям или категориям показаны ниже:

На вкладке назначения выберите позиции для объединенных данных и щелкните левую верхнюю ячейку выбранных позиций.
Нажмите на вкладку «Данные».
Перейти к «Инструменты обработки данных» и выберите «Объединиться». Откроется всплывающее окно.
В «Функция» выберите функцию из раскрывающегося списка.
Выберите данные для объединения:
- Если по должности, перейдите к «Исходные вкладки» и нажмите кнопку “Добавлять” кнопка для добавления данных в «Все ссылки» коробка. Добавляемые данные можно ввести вручную, например, «Лист1!$B$2:$B$10» относится к ячейкам от B2 до B10 на вкладке с именем Лист1 в текущем документе.
- Если по категориям, то в «Ярлыки пользователей включены» поле, выберите «Верхний ряд» (по рядам) или «Левый столбец» (по столбцам), или «Создавайте ссылки на исходные данные» (пишите в ссылках).
Нажмите кнопку “ХОРОШО” кнопку и данные в «Все ссылки” или выбранные строки/столбцы будут объединены.

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

Объединение вкладок в Excel VBA

VBA означает Visual Basic для приложений, простой, но мощный язык программирования, который можно использовать для расширения приложений Microsoft Office, включая Excel. Основная проблема при использовании VBA заключается в том, что вам необходимо понимать и использовать код для создания приложений или макросов.

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

Выбирать “Вид” на панели инструментов.
Нажмите на «Макросы» в крайнем правом углу. Откроется всплывающее окно макроса.
Введите имя макроса (например, «тест») и нажмите кнопку “Создавать” Кнопка справа, и появится консоль кодирования с основным содержимым, которое читает что-то вроде:
###
Подтест()
Конец субтитра
###
Вы можете редактировать код макроса в консоли. Это пример объединения таблиц из листов:
###
Sub Merge_Multiple_Sheets_Row_Wise()
Dim Work_Sheets() как строка
ReDim Work_Sheets(Sheets.Count)
Для i = 0 To Sheets.Count – 1
Work_Sheets(i) = Листы(i + 1).Имя
Дальше я
Sheets.Add.Name = “Объединенный лист”
Dim Row_Index как целое число
Row_Index = Рабочие листы(1).UsedRange.Cells(1, 1).Row
Тусклый Column_Index как целое число
Индекс_столбца = 0
Для i = 0 To Sheets.Count – 2
Set Rng = Worksheets(Work_Sheets(i)).UsedRange
Звонок.Копировать
Worksheets(“Объединенный лист”).Cells(Row_Index, Column_Index + 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
Индекс_столбца = Индекс_столбца + Rng.Columns.Count + 1
Дальше я
Приложение.CutCopyMode = Ложь
Конец субтитра
###
Пример кода макроса перебирает общее количество вкладок и создает новый лист. «Комбинированный лист».
Чтобы запустить код, в консоли макроса в “Бегать” вкладка, нажмите «Запустить Sub/UserForm», затем новая вкладка с именем «Комбинированный лист» будет сгенерирован. Вы также можете изменить код Марко, чтобы изменить диапазон данных и имена.

Объединение листов в Excel Online

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

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

Объединение вкладок в Excel с помощью Power Query

Power Query — это еще один способ объединения вкладок в Excel. Для книги Excel с несколькими вкладками выполните следующие действия:

Перейти к “Данные” вкладка и «Получить и преобразовать данные» группу и нажмите кнопку «Получить данные» кнопка.
В списке нажмите «Пустой запрос» в «Из других источников» вариант, и вы увидите новый редактор Power Query с именем по умолчанию «Запрос 1».
В функции (валюта) бар, введите формулу «=Excel.Текущаярабочая книга()(обратите внимание, что формула чувствительна к регистру) и нажмите кнопку “Входить” и появится список названий вкладок исходной книги.
Выберите названия вкладок (столбцов) для объединения (или отметьте опцию «Выбрать все столбцы» если все столбцы должны быть объединены).
Нажмите кнопку с двойной стрелкой рядом с “Содержание” ключ.
Снимите отметку с «Использовать исходное имя столбца в качестве префикса» если вы хотите, чтобы в объединенном файле использовались точные имена из исходной книги, а не в качестве префиксов.
Нажмите “ХОРОШО” и вы увидите новую вкладку со всеми объединенными данными, в которой имена вкладок исходной книги будут указаны в крайнем правом столбце.

Альтернативные методы

Excel — полезный инструмент для обработки данных двумерных массивов. Однако, если вы специалист по данным, вы можете получить доступ к данным напрямую, используя такие языки программирования, как Python или R. Ключевым моментом является то, что структура данных 2D-массивов является общей: использование разделителей (пробел, табуляция, запятая и т. д.) разделить все точки данных. Например, в формате .csv используются разделители-запятые, и его можно напрямую открыть для чтения/редактирования в Microsoft Excel. Кроме того, вы также можете открывать файлы Excel (.xlsx) с помощью этих языков программирования, например, пакета «readxl» в R, который может открывать и обрабатывать данные из разных листов Excel. В этих случаях для слияния можно использовать простые, но удобные функции, такие как cbind (привязка столбцов с одинаковым измерением). Все данные из разных вкладок Excel можно объединить в фреймы данных для статистического анализа и визуализации без использования программного обеспечения Microsoft Excel.

Получите больше из меньшего

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

Есть ли у вас лучший способ объединить вкладки в Excel? Дайте нам знать в комментариях ниже.