Як обєднати вкладки в Excel

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

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

У цій статті буде показано, як об’єднати дві (або більше) вкладки в Excel, а також деякі розширені функції та методи, які можна використовувати.

Об’єднання вкладок у Excel – це просто

Перед об’єднанням переконайтеся, що всі вкладки мають резервні копії. Ваші вихідні вкладки міститимуть необроблені дані, які ви використовуєте, тоді як цільова вкладка матиме остаточний результат. Залежно від вимог вашого проекту, це може бути та сама вкладка.

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

Майте на увазі, що функції консолідації працюють із числовими даними. Excel може обчислювати суми, середні значення, відхилення, а також мінімальні та максимальні значення, серед інших статистичних показників. Однак це не дозволяє більш тонких перетворень текстових даних.

Нижче наведено кроки для об’єднання за посадою чи категорією:

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

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

Об’єднання вкладок у Excel VBA

VBA означає Visual Basic for Applications, просту, але потужну мову програмування, яку можна використовувати для розширення програм 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. Щоб запустити код, на консолі макросів на вкладці «Виконати» натисніть «Виконати вкладену форму/форму користувача», після чого буде створено нову вкладку під назвою «Об’єднаний аркуш» . Ви також можете змінити код Marco, щоб редагувати діапазон даних і імена.
   
   

Об’єднання аркушів у Excel Online

Кілька безкоштовних онлайн-інструментів дозволяють об’єднувати аркуші Excel. У цих інструментах вам просто потрібно вибрати та завантажити робочі книги (або книгу з кількома вкладками, або різні книги). Прикладами є Aspose Cell Merger і DocSoSo Excel Combiner .

Майте на увазі, що об’єднання аркушів не маніпулює даними. Ці інструменти беруть дві або більше книг 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? Дайте нам знати в коментарях нижче.