Как да обедините раздели в Excel

Excel е невероятно полезен инструмент за съхраняване, управление и показване на големи набори от данни. Независимо дали работите с повтарящи се резултати от научни експерименти, информация за служители на компанията, проучвания за цените на продуктите или други, всичко това може да се покаже като електронни таблици в Excel.

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

Тази статия ще покаже как да обедините два (или повече) раздела в Excel, заедно с някои разширени функции и методи, които можете да използвате.

Обединяване на раздели в Excel – това е просто

Преди да обедините, уверете се, че всички раздели имат резервни копия. Вашите раздели източник ще съдържат необработените данни, които използвате, докато разделът местоназначение ще има крайния резултат. В зависимост от изискванията на вашия проект, това може или не може да бъде един и същи раздел.

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

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

Стъпките за обединяване по позиция или категория са показани по-долу:

1. В раздела местоназначение решете позициите за обединените данни и щракнете върху горната лява клетка на избраните позиции.

   

2. Кликнете върху раздела „Данни“.

   

3. Отидете на „Инструменти за данни“ и изберете „Консолидиране“. Това отваря изскачащ прозорец.

   

4. В полето „Функция“ изберете функция от падащия списък.

   

5. Изберете данните за обединяване:
   • Ако по позиция, отидете на „Изходни раздели“ и щракнете върху бутона „Добавяне“, за да добавите данните в полето „Всички препратки“. Данните, които трябва да се добавят, могат да бъдат въведени ръчно, като например „Sheet1!$B$2:$B$10“ се отнася за клетките от B2 до B10 на раздела с име Sheet1 в текущия документ.
     
   • Ако по категория, в полето „Потребителски етикети в“ изберете „Горен ред“ (по редове) или „Лява колона“ (по колони), или „Създаване на връзки към изходни данни“ (пишете във връзки).

     

6. Щракнете върху бутона „OK“ и данните в полето „Всички препратки“ или избраните редове/колони ще бъдат обединени.

   

Трябва да се спомене, че винаги можете да използвате копиране и поставяне за прехвърляне на данни от един раздел в друг. Това обаче може да отнеме много време и да доведе до грешки. Има по-елегантни начини за постигане на консолидация без повтаряне на информация.

Обединяване на раздели в Excel VBA

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

За да създадете и редактирате VBA макрос, направете следното:

1. Изберете „Преглед“ в лентата с инструменти.

   

2. Кликнете върху „Макроси“ най-вдясно. Това отваря изскачащ прозорец с макроси.

   

3. Въведете името на макроса (например „тест“) и щракнете върху бутона „Създаване“ от дясната страна и ще се появи конзола за кодиране с основното съдържание, което чете нещо по следния начин:

   ###
   Подтест()
   End Sub
   ###

   

4. Можете да редактирате макро кода в конзолата. Това е пример, който комбинира таблици от листове:

   ###
   Sub Merge_Multiple_Sheets_Row_Wise()
   Dim Work_Sheets() като низ
   ReDim Work_Sheets(Sheets.Count)
   За i = 0 To Sheets.Count – 1
   Работни_листове(i) = Листове(i + 1).Име
   Следваща i
   Sheets.Add.Name = “Комбиниран лист”
   Dim Row_Index като цяло число
   Индекс_на_ред = Работни листове(1).Използван диапазон.Клетки(1, 1).Ред
   Dim Column_Index като цяло число
   Индекс_на_колона = 0
   За i = 0 To Sheets.Count – 2
   Задайте Rng = Worksheets(Work_Sheets(i)).UsedRange
   Rng.Copy
   Работни листове (“Комбиниран лист”). Клетки (Индекс_на_ред, Индекс_на_колона + 1).PasteSpecial Paste:=xlPasteAllUsingSourceTheme
   Индекс_на_колона = Индекс_на_колона + Rng.Columns.Count + 1
   Следваща 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. Щракнете върху „OK“ и ще видите нов раздел с всички обединени данни, в който имената на разделите на оригиналната работна книга ще бъдат изброени в най-дясната колона.

   

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

Excel е полезен инструмент за работа с 2D масиви. Ако сте учен по данни обаче, можете да получите директен достъп до данните, като използвате езици за програмиране като Python или R. Ключът е, че структурата на данните на 2D масивите е обща: използване на разделители (интервал, раздел, запетая и т.н.) за разделяне на всички точки от данни. Форматът .csv например използва разделители със запетая и може директно да се отваря за четене/редактиране от Microsoft Excel. Като алтернатива можете също да отворите файловете на Excel (.xlsx) от тези езици за програмиране, като пакета „readxl“ в R, който може да отваря и обработва данните от различни работни листове на Excel. В тези случаи за обединяване могат да се използват прости, но удобни функции, като cbind (свързване на колони със същото измерение). Всички данни от различни раздели на Excel могат да бъдат обединени в рамки с данни за статистически анализи и визуализации, без да се използва софтуерът Microsoft Excel.

Вземете повече от по-малко

От процесите, обобщени по-горе, ще получите един интегриран работен лист с данни от множество ресурси. Този работен лист може да служи като архивна или резервна база данни. Освен това обединеният раздел ви позволява да придобивате перспективи, които може да липсват в данните от един ресурс. Препоръчваме ви да тествате функциите за консолидация и макросите върху примерни данни и да ги архивирате предварително.

Имате ли по-добър начин за консолидиране на раздели в Excel? Кажете ни в коментарите по-долу.