Стожерна табела низ повеќе опсези на податоци

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

Стожерните табели се една од најневеројатните алатки во Excel. Но, досега, за жал, ниту една од верзиите на Excel не може да направи толку едноставна и неопходна работа во лет како создавање резиме за неколку почетни опсези на податоци лоцирани, на пример, на различни листови или во различни табели:

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

• Табелите може да имаат кој било број на редови со какви било податоци, но тие мора да го имаат истото заглавие.
• Не треба да има дополнителни податоци на листовите со изворни табели. Еден лист - една маса. За контрола, ве советувам да користите кратенка на тастатурата Ctrl+крајот, што ве преместува до последната користена ќелија во работниот лист. Идеално, ова треба да биде последната ќелија во табелата со податоци. Ако кога ќе кликнете на Ctrl+крајот означена е секоја празна ќелија десно или под табелата - избришете ги овие празни колони надесно или редовите под табелата по табелата и зачувајте ја датотеката.

Метод 1: Изградете табели за стожер користејќи Power Query

Почнувајќи од верзијата 2010 за Excel, постои бесплатен додаток за Power Query кој може да собира и трансформира какви било податоци и потоа да ги даде како извор за градење на стожерна табела. Решавањето на нашиот проблем со помош на овој додаток воопшто не е тешко.

Прво, ајде да креираме нова празна датотека во Excel - во неа ќе се склопи, а потоа ќе се креира стожерна табела во неа.

Потоа на јазичето податоци (ако имате Excel 2016 или понова верзија) или на картичката Барање за напојување (ако имате Excel 2010-2013) изберете ја командата Креирајте барање - од датотека - Excel (Добијте податоци - од датотека - Excel) и наведете ја изворната датотека со табелите што треба да се соберат:

Во прозорецот што се појавува, изберете кој било лист (не е важно кој) и притиснете го копчето подолу Промени (Уреди):

Прозорецот Power Query Query Editor треба да се отвори на врвот на Excel. На десната страна на прозорецот на панелот Параметри за барање избришете ги сите автоматски креирани чекори освен првиот - извор (Извор):

Сега гледаме општ список на сите листови. Ако покрај листовите со податоци во датотеката има и некои други странични листови, тогаш на овој чекор наша задача е да ги избереме само оние листови од кои треба да се вчитаат информации, исклучувајќи ги сите други користејќи го филтерот во заглавието на табелата:

Избришете ги сите колони освен колоната податоцисо десен клик на наслов на колона и избирање Избришете ги другите колони (Отстрани други колони):

Потоа можете да ја проширите содржината на собраните табели со кликнување на двојната стрелка на врвот на колоната (поле за избор Користете го оригиналното име на колоната како префикс можете да го исклучите):

Ако сте направиле сè правилно, тогаш во овој момент треба да ја видите содржината на сите табели собрани една под друга:

Останува да се подигне првиот ред до заглавието на табелата со копчето Користете ја првата линија како заглавија (Користете го првиот ред како заглавија) табот Почетна (Дома) и отстранете ги дупликатите заглавија на табелите од податоците користејќи филтер:

Зачувајте сè што е направено со командата Затвори и вчитај – Затвори и вчитај во… (Затвори и вчитај - затвори и вчитај до…) табот Почетна (Дома)и во прозорецот што се отвора изберете ја опцијата Само врска (Само за поврзување):

Сè. Останува само да се изгради резиме. За да го направите ова, одете на јазичето Вметнете – Стожерна табела (Вметни - Стожерна табела), изберете ја опцијата Користете надворешен извор на податоци (Користете надворешен извор на податоци)а потоа со кликнување на копчето Изберете врска, наше барање. Понатамошното креирање и конфигурирање на стожерот се случува на сосема стандарден начин со влечење на полињата што ни се потребни во редовите, колоните и областа за вредности:

Ако изворните податоци се променат во иднина или се додадат уште неколку листови за продавница, тогаш ќе биде доволно да го ажурираме барањето и нашето резиме со помош на командата Освежете ги сите табот податоци (Податоци - Освежи ги сите).

Метод 2. Ги обединуваме табелите со командата UNION SQL во макро

Друго решение за нашиот проблем е претставено со ова макро, кое создава збир на податоци (кеш) за стожерната табела користејќи ја командата ЕДИНСТВО SQL јазик за пребарување. Оваа команда комбинира табели од сите наведени во низата Имиња на листови листови од книгата во една табела со податоци. Односно, наместо физички копирање и вметнување опсег од различни листови до еден, ние го правиме истото во RAM меморијата на компјутерот. Потоа макрото додава нов лист со даденото име (променлива Име на листот на резултати) и создава полноправно (!) резиме на него врз основа на собраниот кеш.

За да користите макро, користете го копчето Visual Basic на картичката инвеститорот (програмер) или кратенка на тастатура Alt+F11. Потоа вметнуваме нов празен модул низ менито Вметни – Модул и копирајте го следниов код таму:

Под New_Multi_Table_Pivot() Затемнување и затемнување на arSQL() Како стринг затемнување objPivotCache Како PivotCache Затемнување objRS Како објект затемнет ResultSheetName како низа затемнети листовиИмиња како варијанта 'име на листот каде што добиениот стожер ќе се прикажеNarrayaetSheet' имиња со изворни табели SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'формираме кеш за табели од листови од SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SeetsNames) + 1) ) За i = LBound (Имиња на листови) До UBound(Имиња на листови) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Следно i Set objRS = CreateObject ("ADODB.Recordset") objRS .Отвори Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Заврши со 'повторно креирај го листот за да се прикаже добиената стожерна табела На грешка Продолжи Следна апликација.DisplayAlerts = Лажни работни листови(ResultSheetName).Избриши го сет wsPivot = Работни листови.Додади wsPivot. Name = ResultSheetName 'прикажи го резимето на генерираната кеш на овој лист Поставете objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Поставете objPivotCache.Recordset = objRS Поставете objRS = Ништо со wsPivotacheeest. ge ("A3") Комплет objPivotCache = Ништо Опсег ("A3").Изберете End With End Sub    

Завршеното макро потоа може да се изврши со кратенка на тастатурата Alt+F8 или копчето Макроа на јазичето инвеститорот (Програмер - макроа).

Недостатоци на овој пристап:

• Податоците не се ажурираат бидејќи кешот нема врска со изворните табели. Ако ги промените изворните податоци, мора повторно да го извршите макрото и повторно да го изградите резимето.
• Кога го менувате бројот на листови, неопходно е да се уреди макро кодот (низа Имиња на листови).
  

Но, на крајот добиваме вистинска полноправна стожерна табела, изградена на неколку опсези од различни листови:

Voila!

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

	 Provider=Microsoft.Jet.OLEDB.4.0;  

на:

	Provider=Microsoft.ACE.OLEDB.12.0;  

И преземете го и инсталирајте го бесплатниот мотор за обработка на податоци од Access од веб-локацијата на Microsoft – Microsoft Access Database Engine 2010 Redistributable

Метод 3: Консолидирајте го Волшебникот за PivotTable од старите верзии на Excel

Овој метод е малку застарен, но сепак вреди да се спомене. Формално кажано, во сите верзии до и вклучувајќи ја 2003 година, постоеше опција во Волшебникот за PivotTable „да се изгради стожер за неколку опсези на консолидација“. Меѓутоа, вака изработениот извештај, за жал, ќе биде само тажен привид на вистинско полноправно резиме и не поддржува многу од „чиповите“ на конвенционалните стожерни табели:

Во таков стожер, нема наслови на колони во списокот на полиња, нема поставки за флексибилна структура, множеството користени функции е ограничено и, генерално, сето ова не е многу слично на стожерната табела. Можеби тоа е причината зошто, почнувајќи од 2007 година, Мајкрософт ја отстрани оваа функција од стандардниот дијалог кога креираше извештаи за стожерна табела. Сега оваа функција е достапна само преку приспособено копче Волшебник за стожерна табела(Волшебник за стожерна табела), кој по желба може да се додаде во лентата со алатки за брз пристап преку Датотека – Опции – Прилагодете ја лентата со алатки за брз пристап – Сите команди (Датотека - Опции - Приспособете ја лентата со алатки за брз пристап - сите команди):

Откако ќе кликнете на додаденото копче, треба да ја изберете соодветната опција на првиот чекор од волшебникот:

А потоа во следниот прозорец, изберете го секој опсег по ред и додајте го во општата листа:

Но, повторно, ова не е целосно резиме, затоа не очекувајте премногу од него. Можам да ја препорачам оваа опција само во многу едноставни случаи.

• Креирање извештаи со PivotTables
• Поставете пресметки во PivotTables
• Што се макроа, како да ги користите, каде да копирате VBA код итн.
• Собирање податоци од повеќе листови до еден (додаток PLEX)