Обобщена таблица в множество диапазони от данни

Формулиране на проблема

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

Преди да започнем, нека изясним няколко точки. Априори смятам, че в нашите данни са изпълнени следните условия:

• Таблиците могат да имат произволен брой редове с всякакви данни, но трябва да имат една и съща заглавка.
• Не трябва да има допълнителни данни на листовете с изходни таблици. Един лист – една маса. За контрол ви съветвам да използвате клавишна комбинация Ctrl+Край, което ви премества до последната използвана клетка в работния лист. В идеалния случай това трябва да е последната клетка в таблицата с данни. Ако, когато щракнете върху Ctrl+Край всяка празна клетка вдясно или под таблицата се маркира – изтрийте тези празни колони вдясно или редове под таблицата след таблицата и запазете файла.

Метод 1: Създайте таблици за обобщена таблица с помощта на Power Query

Започвайки от версията 2010 за Excel, има безплатна добавка Power Query, която може да събира и трансформира всякакви данни и след това да ги предоставя като източник за изграждане на обобщена таблица. Решаването на нашия проблем с помощта на тази добавка изобщо не е трудно.

Първо, нека създадем нов празен файл в Excel - в него ще се извърши асемблиране и след това в него ще бъде създадена обобщена таблица.

След това в раздела Дата (ако имате Excel 2016 или по-нова версия) или в раздела Запитване за захранване (ако имате Excel 2010-2013) изберете командата Създаване на заявка – От файл – Excel (Получаване на данни — От файл — Excel) и посочете изходния файл с таблиците, които да бъдат събрани:

В прозореца, който се показва, изберете произволен лист (няма значение кой) и натиснете бутона по-долу промяна (Edit):

Прозорецът Power Query Query Editor трябва да се отвори върху Excel. От дясната страна на прозореца на панела Параметри на заявката изтрийте всички автоматично създадени стъпки с изключение на първата – източник (Източник):

Сега виждаме общ списък на всички листове. Ако в допълнение към листовете с данни има някои други странични листове във файла, тогава на тази стъпка нашата задача е да изберем само онези листове, от които трябва да се зареди информация, като изключим всички останали, като използваме филтъра в заглавката на таблицата:

Изтрийте всички колони с изключение на колоната Датакато щракнете с десния бутон върху заглавие на колона и изберете Изтрийте други колони (Премахване други колони):

След това можете да разширите съдържанието на събраните таблици, като щракнете върху двойната стрелка в горната част на колоната (поле за отметка Използвайте оригиналното име на колона като префикс можете да го изключите):

Ако сте направили всичко правилно, тогава в този момент трябва да видите съдържанието на всички таблици, събрани една под друга:

Остава да повдигнете първия ред до заглавката на таблицата с бутона Използвайте първия ред като заглавки (Използвайте първия ред като заглавки) етикет Начало (У дома) и премахнете дублиращите се заглавки на таблици от данните с помощта на филтър:

Запазете всичко направено с командата Затворете и заредете – Затворете и заредете в... (Затвори и зареди — Затвори и зареди до...) етикет Начало (У дома), и в прозореца, който се отваря, изберете опцията Само връзка (Само връзка):

Всичко. Остава само да се изгради резюме. За да направите това, отидете на раздела Вмъкване – обобщена таблица (Вмъкване — осева таблица), изберете опцията Използвайте външен източник на данни (Използване на външен източник на данни)и след това като щракнете върху бутона Изберете връзка, нашето искане. По-нататъшното създаване и конфигуриране на пивота става по напълно стандартен начин чрез плъзгане на полетата, от които се нуждаем, в областта на редовете, колоните и стойностите:

Ако изходните данни се променят в бъдеще или се добавят още няколко листа за съхранение, тогава ще бъде достатъчно да актуализираме заявката и нашето резюме с помощта на командата Опресни всички етикет Дата (Данни — Обновяване на всички).

Метод 2. Обединяваме таблици с командата UNION SQL в макрос

Друго решение на нашия проблем е представено от този макрос, който създава набор от данни (кеш) за обобщената таблица с помощта на командата UNITY SQL език за заявки. Тази команда комбинира таблици от всички посочени в масива Имена на листове листове от книгата в една таблица с данни. Тоест, вместо физически да копираме и поставяме диапазони от различни листове в един, ние правим същото в RAM паметта на компютъра. След това макросът добавя нов лист с даденото име (променлива ResultSheetName) и създава пълноценно (!) резюме върху него въз основа на събрания кеш.

За да използвате макрос, използвайте бутона Visual Basic в раздела предприемач (Разработчик) или клавишна комбинация Друг+F11. След това вмъкваме нов празен модул през менюто Вмъкване – Модул и копирайте следния код там:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'име на лист, където ще се покаже полученият пивот ResultSheetName = "Pivot" 'масив от лист имена с изходни таблици SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'формираме кеш за таблици от листове от SheetsNames С ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Край с 'пресъздайте листа, за да покажете получената обобщена таблица При грешка Възобновяване Следващо приложение.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo T. Name = ResultSheetName 'показва генерираното обобщение на кеша на този лист Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination:=wsPivot.Range("A3") Set objPivotCache = Нищо Range("A3"). Изберете End With End Sub    

След това готовият макрос може да се стартира с клавишна комбинация Друг+F8 или бутона Макроси в раздела предприемач (Програмист — Макроси).

Минуси на този подход:

• Данните не се актуализират, защото кешът няма връзка с изходните таблици. Ако промените изходните данни, трябва да изпълните отново макроса и отново да изградите резюмето.
• Когато променяте броя на листовете, е необходимо да редактирате кода на макроса (масив Имена на листове).
  

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

Готово!

Техническа бележка: ако получите грешка като „Доставчикът не е регистриран“ при изпълнение на макроса, най-вероятно имате 64-битова версия на Excel или е инсталирана непълна версия на Office (няма достъп). За да коригирате ситуацията, заменете фрагмента в кода на макроса:

	 Доставчик=Microsoft.Jet.OLEDB.4.0;  

до:

	Доставчик=Microsoft.ACE.OLEDB.12.0;  

И изтеглете и инсталирайте безплатната машина за обработка на данни от Access от уебсайта на Microsoft – Microsoft Access Database Engine 2010 Redistributable

Метод 3: Консолидиране на съветника за обобщена таблица от стари версии на Excel

Този метод е малко остарял, но все пак си струва да се спомене. Формално казано, във всички версии до и включително 2003 г. имаше опция в съветника за обобщена таблица за „изграждане на опорна точка за няколко диапазона на консолидация“. Въпреки това, съставен по този начин отчет, за съжаление, ще бъде само жалко подобие на истинско пълно резюме и не поддържа много от „чиповете“ на конвенционалните обобщени таблици:

В такъв пивот няма заглавия на колони в списъка с полета, няма настройка на гъвкава структура, наборът от използвани функции е ограничен и като цяло всичко това не е много подобно на обобщена таблица. Може би затова, от 2007 г., Microsoft премахна тази функция от стандартния диалог при създаване на отчети с обобщени таблици. Сега тази функция е достъпна само чрез персонализиран бутон Съветник за обобщена таблица(Съветник за обобщена таблица), който при желание може да се добави към лентата с инструменти за бърз достъп чрез Файл – Опции – Персонализиране на лентата с инструменти за бърз достъп – Всички команди (Файл — Опции — Персонализиране на лентата с инструменти за бърз достъп — Всички команди):

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

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

Но, отново, това не е пълноценно резюме, така че не очаквайте твърде много от него. Мога да препоръчам тази опция само в много прости случаи.

• Създаване на отчети с обобщени таблици
• Настройте изчисления в обобщени таблици
• Какво представляват макросите, как да ги използвате, къде да копирате VBA код и т.н.
• Събиране на данни от няколко листа в един (PLEX добавка)