Заокретна табела у више опсега података

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

Заокретне табеле су један од најневероватнијих алата у Екцел-у. Али до сада, нажалост, ниједна од верзија Екцела не може да уради тако једноставну и неопходну ствар у ходу као што је прављење резимеа за неколико почетних опсега података који се налазе, на пример, на различитим листовима или у различитим табелама:

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

• Табеле могу имати било који број редова са било којим подацима, али морају имати исто заглавље.
• На листовима са изворним табелама не би требало бити додатних података. Један лист - један сто. За контролу, саветујем вам да користите пречицу на тастатури Цтрл+Крај, што вас помера на последњу коришћену ћелију на радном листу. У идеалном случају, ово би требало да буде последња ћелија у табели података. Ако када кликнете на Цтрл+Крај било која празна ћелија десно или испод табеле је истакнута – избришите ове празне колоне десно или редове испод табеле после табеле и сачувајте датотеку.

Метод 1: Направите табеле за пивот користећи Повер Куери

Почевши од верзије 2010 за Екцел, постоји бесплатни додатак за Повер Куери који може да прикупи и трансформише све податке, а затим их да као извор за прављење заокретне табеле. Решавање нашег проблема уз помоћ овог додатка уопште није тешко.

Прво, направимо нову празну датотеку у Екцел-у – у њој ће се одвијати монтажа, а затим ће се у њој креирати пивот табела.

Затим на картици Датум (ако имате Екцел 2016 или новију верзију) или на картици Повер Куери (ако имате Екцел 2010-2013) изаберите команду Креирајте упит – Из датотеке – Екцел (Преузми податке — Из датотеке — Екцел) и наведите изворну датотеку са табелама које треба прикупити:

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

Прозор Повер Куери Куери Едитор-а би требало да се отвори на врху Екцел-а. На десној страни прозора на панелу Параметри захтева избришите све аутоматски креиране кораке осим првог – извор (Извор):

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

Избришите све колоне осим колоне Датумдесним кликом на наслов колоне и одабиром Избришите друге колоне (Уклони друге колоне):

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

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

Остаје да подигнете први ред до заглавља табеле помоћу дугмета Користите први ред као заглавља (Користите први ред као заглавља) табулатор Početna (Кућа) и уклоните дупликате заглавља табеле из података помоћу филтера:

Сачувајте све што је урађено командом Затвори и учитај – Затвори и учитај у… (Затвори и учитај — Затвори и учитај до…) табулатор Početna (Кућа), а у прозору који се отвори изаберите опцију Само веза (Само веза):

Све. Остаје само да се направи резиме. Да бисте то урадили, идите на картицу Уметање – заокретна табела (Инсерт — Пивот Табле), изаберите опцију Користите екстерни извор података (Користите спољни извор података)а затим кликом на дугме Изаберите везу, наш захтев. Даље креирање и конфигурисање пивота се дешава на потпуно стандардан начин превлачењем поља која су нам потребна у редове, колоне и област вредности:

Ако се изворни подаци промене у будућности или се дода још неколико листова продавнице, биће довољно да ажурирате упит и наш резиме користећи команду Освежи све табулатор Датум (Подаци — Освежи све).

Метод 2. Уједињујемо табеле са УНИОН СКЛ командом у макро

Друго решење нашег проблема представља овај макро, који креира скуп података (кеш) за стожерну табелу користећи команду ЈЕДИНСТВО СКЛ упитни језик. Ова команда комбинује табеле из свих наведених у низу СхеетНамес листове књиге у једну табелу са подацима. То јест, уместо физичког копирања и лепљења опсега од различитих листова до једног, ми радимо исто у РАМ-у рачунара. Затим макро додаје нови лист са датим именом (променљива РесултСхеетНаме) и креира пуноправни (!) резиме на основу прикупљеног кеша.

Да бисте користили макро, користите дугме Висуал Басиц на картици развијач (Програмер) или пречица на тастатури други+ФКСНУМКС. Затим убацујемо нови празан модул кроз мени Инсерт – Модул и копирајте тамо следећи код:

Суб Нев_Мулти_Табле_Пивот() Дим и Ас Лонг Дим арСКЛ() Ас Стринг Дим објПивотЦацхе Ас ПивотЦацхе Дим објРС Ас Објецт Дим РесултСхеетНаме Ас Стринг Дим СхеетсНамес Ас Вариант 'име листа где ће бити приказан резултујући пивотНа схеет оф Ресулт'арПхе имена са изворним табелама СхеетсНамес = Арраи("Алпха", "Бета", "Гамма", "Делта") 'формирамо кеш за табеле из листова из СхеетсНамес са АцтивеВоркбоок РеДим арСКЛ(1 То (УБоунд(СхеетсНамес) + 1) ) За и = ЛБоунд (СхеетсНамес) То УБоунд(СхеетсНамес) арСКЛ(и + 1) = "СЕЛЕЦТ * ФРОМ [" & СхеетсНамес(и) & "$]" Следеће и Поставите објРС = ЦреатеОбјецт("АДОДБ.Рецордсет") објРС .Опен Јоин$( арСКЛ, " УНИОН АЛЛ "), _ Јоин$(Арраи("Провидер=Мицрософт.Јет.ОЛЕДБ.4.0; Дата Соурце=", _ .ФуллНаме, ";Ектендед Пропертиес=""Екцел 8.0;" ""), вбНуллСтринг ) Енд Витх 'поново креирајте лист да бисте приказали резултујућу заокретну табелу У случају грешке Наставите следећу апликацију.ДисплаиАлертс = Фалсе Ворксхеетс(РесултСхеетНаме).Делете Сет всПивот = Ворксхеетс.Адд всПивот. Назив = РезултатисхеетНаме 'Прикажи генерирани сажетак кеш меморије на овом листу Објпивотцацхе = АцтивеВоркБоок.пивотцацхес.адд (клектернал) сет ОбјпивотЦацхе.рецордсет = Објрс сет Објрс = Ништа са вСпивот објпивотцацхе.цреатепивоттабле тхаблеСтатион: = вспивот.ранге ("А3") објПивотЦацхе = Нотхинг Ранге("А3").Изаберите Енд Витх Енд Суб    

Готов макро се затим може покренути помоћу пречице на тастатури други+F8 или дугме Макрои на картици развијач (Програмер — Макрои).

Недостаци овог приступа:

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

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

Воила!

Техничка напомена: ако добијете грешку попут „Добављач није регистрован“ када покрећете макро, онда највероватније имате 64-битну верзију Екцел-а или је инсталирана непотпуна верзија Оффицеа (нема приступа). Да бисте поправили ситуацију, замените фрагмент у коду макроа:

	 Провидер=Мицрософт.Јет.ОЛЕДБ.4.0;  

да:

	Провидер=Мицрософт.АЦЕ.ОЛЕДБ.12.0;  

И преузмите и инсталирајте бесплатни механизам за обраду података са Аццесс-а са Мицрософт веб локације – Мицрософт Аццесс Датабасе Енгине 2010 који се може редистрибуирати

Метод 3: Чаробњак за консолидацију изведене табеле из старих верзија Екцел-а

Овај метод је мало застарео, али ипак вредан помена. Формално говорећи, у свим верзијама до и укључујући 2003, постојала је опција у чаробњаку за изведене табеле да се „изгради стожер за неколико опсега консолидације“. Међутим, овако конструисан извештај, нажалост, биће само јадан привид правог пуног резимеа и не подржава многе „чипове“ конвенционалних обртних табела:

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

Након што кликнете на дугме за додавање, потребно је да изаберете одговарајућу опцију у првом кораку чаробњака:

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

Али, опет, ово није потпуни сажетак, тако да не очекујте превише од њега. Ову опцију могу препоручити само у врло једноставним случајевима.

• Креирање извештаја са изведеним табелама
• Подесите прорачуне у изведеним табелама
• Шта су макрои, како их користити, где копирати ВБА код итд.
• Прикупљање података са више листова у један (ПЛЕКС додатак)