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

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

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

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

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

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

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

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

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

1. На вкладке назначения выберите позиции для объединенных данных и щелкните левую верхнюю ячейку выбранных позиций.
   
   
2. Нажмите на вкладку «Данные».
   
   
3. Перейдите в «Инструменты обработки данных» и выберите «Объединить». Откроется всплывающее окно.
   
   
4. В поле «Функция» выберите функцию из раскрывающегося списка.
   
   
5. Выберите данные для объединения:
   • Если по позиции, перейдите в «Вкладки источников» и нажмите кнопку «Добавить», чтобы добавить данные в поле «Все ссылки» . Добавляемые данные можно ввести вручную, например « Sheet1!$B$2:$B$10» относится к ячейкам от B2 до B10 на вкладке с именем «Лист1» в текущем документе.
     
   • Если по категориям, то в поле «Метки пользователей в» выберите «Верхняя строка» (по строкам) или «Левый столбец» (по столбцам), либо «Создать ссылки на исходные данные» (впишите ссылки).
     
     
6. Нажмите кнопку «ОК» , и данные в поле «Все ссылки » или выбранных строках/столбцах будут объединены.
   
   

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

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

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

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

1. Выберите «Вид» на панели инструментов.
   
   
2. Нажмите «Макросы» в крайнем правом углу. Откроется всплывающее окно макроса.
   
   
3. Введите имя макроса (например, «test») и нажмите кнопку «Создать» справа, и появится консоль кодирования с основным содержимым, которое читает что-то вроде:
   
   ###
Sub test()
End Sub
###
   
   
4. Вы можете редактировать код макроса в консоли. Это пример объединения таблиц из листов: пример кода макроса перебирает общее количество вкладок и создает новый лист «Объединенный лист».
   
   ###
Sub Merge_Multiple_Sheets_Row_Wise()
Dim Work_Sheets() As String
ReDim Work_Sheets(Sheets.Count)
For i = 0 To Sheets.Count - 1
 Work_Sheets(i) = Sheets(i + 1).Name
Next i
Sheets.Add.Name = "Combined Sheet"
Dim Row_Index As Integer
Row_Index = Worksheets(1).UsedRange.Cells(1, 1).Row
Dim Column_Index As Integer
Column_Index = 0
For i = 0 To Sheets.Count - 2
 Set Rng = Worksheets(Work_Sheets(i)).UsedRange
Rng.Copy
 Worksheets("Combined Sheet").Cells(Row_Index, Column_Index + 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
 Column_Index = Column_Index + Rng.Columns.Count + 1
Next i
Application.CutCopyMode = False
End Sub
###
   
   
   
   
5. Чтобы запустить код, в консоли макроса на вкладке «Выполнить» нажмите «Выполнить Sub/UserForm», после чего будет создана новая вкладка с именем «Объединенный лист» . Вы также можете изменить код Марко, чтобы изменить диапазон данных и имена.
   
   

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

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

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

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

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

1. Перейдите на вкладку «Данные» и группу «Получить и преобразовать данные» и нажмите кнопку «Получить данные» .
   
   
2. В списке нажмите «Пустой запрос» в разделе «Из других источников» , и вы увидите новый редактор Power Query с именем по умолчанию «Запрос 1».
   
   
3. На панели функций ( fx ) введите формулу « =Excel.CurrentWorkbook() » (обратите внимание, что формула чувствительна к регистру) и нажмите клавишу «Enter» , и появится список имен вкладок исходной книги.
   
   
4. Выберите названия вкладок (столбцов) для объединения (или отметьте опцию «Выбрать все столбцы», если необходимо объединить все столбцы).
   
   
5. Нажмите кнопку с двойной стрелкой рядом с клавишей «Содержимое» .
   
   
6. Снимите флажок «Использовать исходное имя столбца в качестве префикса», если вы хотите, чтобы в объединенном файле использовались точные имена из исходной книги, а не в качестве префиксов.
   
   
7. Нажмите «ОК» , и вы увидите новую вкладку со всеми объединенными данными, в которой имена вкладок исходной книги будут указаны в крайнем правом столбце.
   
   

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

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

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

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

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