Састављање табела из различитих Екцел датотека помоћу Повер Куери-ја

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

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

Претпоставимо да имамо следећу фасциклу, која садржи неколико датотека са подацима из градова огранака:

Број датотека није битан и може се променити у будућности. Свака датотека има лист са именом Продајнигде се налази табела података:

Број редова (наредби) у табелама је, наравно, различит, али је скуп колона свуда стандардан.

Задатак: прикупити податке из свих фајлова у једну књигу са накнадним аутоматским ажурирањем приликом додавања или брисања градских фајлова или редова у табелама. Према коначној консолидованој табели, тада ће бити могуће направити било какве извештаје, заокретне табеле, податке за филтрирање и сортирање итд. Главна ствар је да будете у могућности да прикупљате.

Ми бирамо оружје

За решење нам је потребна најновија верзија Екцел 2016 (потребна функционалност је већ уграђена у њега подразумевано) или претходне верзије Екцел 2010-2013 са инсталираним бесплатним додатком Повер Куери од Мицрософта (преузмите га овде). Повер Куери је супер флексибилан и супер моћан алат за учитавање података у Екцел из спољашњег света, затим њихово уклањање и обраду. Повер Куери подржава скоро све постојеће изворе података – од текстуалних датотека до СКЛ-а, па чак и Фацебоок-а 🙂

Ако немате Екцел 2013 или 2016, онда не можете даље да читате (шалим се). У старијим верзијама Екцел-а, такав задатак се може остварити само програмирањем макроа у Висуал Басиц-у (што је веома тешко за почетнике) или монотоним ручним копирањем (које дуго траје и генерише грешке).

Корак 1. Увезите једну датотеку као узорак

Прво, хајде да увеземо податке из једне радне свеске као пример, тако да Екцел „покупи идеју“. Да бисте то урадили, направите нову празну радну свеску и…

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

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

Ако кликнете на дугме у доњем десном углу овог прозора Преузимање (Учитај), тада ће табела бити одмах увезена на лист у свом оригиналном облику. За једну датотеку, ово је добро, али морамо да учитамо много таквих датотека, па ћемо ићи мало другачије и кликнути на дугме Корекција (Ремик). Након тога, Повер Куери уређивач упита треба да се прикаже у посебном прозору са нашим подацима из књиге:

Ово је веома моћан алат који вам омогућава да „довршите“ табелу до приказа који нам је потребан. Чак би и површан опис свих његових функција заузео стотинак страница, али, ако врло кратко, користећи овај прозор можете:

• филтрирајте непотребне податке, празне редове, редове са грешкама
• сортирати податке по једној или више колона
• ослободити се понављања
• поделите лепљиви текст по колонама (по граничницима, броју знакова итд.)
• ставите текст у ред (уклоните додатне размаке, исправите велика и мала слова, итд.)
• конвертујте типове података на сваки могући начин (претворите бројеве попут текста у нормалне бројеве и обрнуто)
• транспоновати (ротирати) табеле и проширити дводимензионалне унакрсне табеле у равне
• додајте додатне колоне у табелу и користите формуле и функције у њима користећи М језик уграђен у Повер Куери.
• ...

На пример, додајмо колону са текстуалним називом месеца у нашу табелу, тако да би касније било лакше да направимо извештаје изведене табеле. Да бисте то урадили, кликните десним тастером миша на наслов колоне датуми изаберите команду Дупликат колоне (дупликат колоне), а затим кликните десним тастером миша на заглавље дуплиране колоне која се појављује и изаберите Команде Трансформ – Месец – Име месеца:

За сваки ред треба формирати нову колону са текстуалним називима месеца. Ако двапут кликнете на наслов колоне, можете је преименовати из Датум копирања на удобније месец, на пример.

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

Можете искључити редове са грешкама или празним редовима, као и непотребне менаџере или купце, користећи једноставан филтер:

Штавише, све извршене трансформације су фиксиране у десном панелу, где се увек могу вратити (укрстити) или променити своје параметре (зупчаник):

Лагано и елегантно, зар не?

Корак 2. Хајде да трансформишемо наш захтев у функцију

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

У уређивачу упита идите на картицу Приказ и кликните на дугме Напредни уређивач (Приказ — Напредни уређивач). Требало би да се отвори прозор где ће све наше претходне радње бити написане у облику кода на М језику. Имајте на уму да је путања до датотеке коју смо увезли за пример чврсто кодирана у коду:

Сада направимо неколико подешавања:

Њихово значење је једноставно: први ред (путања датотеке)=> претвара нашу процедуру у функцију са аргументом филепатх, а испод мењамо фиксну путању на вредност ове променљиве. 

Све. Кликните на завршити и требало би да видим ово:

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

Корак 3. Прикупљање свих датотека

Све најтеже је иза, остаје пријатно и лако. Идите на картицу Подаци – Креирај упит – Из датотеке – Из фасцикле (Подаци — Нови упит — Из датотеке — Из фолдера) или, ако имате Екцел 2010-2013, слично као на картици Повер Куери. У прозору који се појави наведите фасциклу у којој се налазе све наше изворне градске датотеке и кликните OK. Следећи корак би требало да отвори прозор у којем ће бити наведене све Екцел датотеке које се налазе у овој фасцикли (и њеним потфасциклима) и детаљи за сваку од њих:

Kliknite Променити (Ремик) и поново улазимо у познати прозор уређивача упита.

Сада морамо да додамо још једну колону у нашу табелу са нашом креираном функцијом, која ће „повући“ податке из сваке датотеке. Да бисте то урадили, идите на картицу Додај колону – Прилагођена колона (Додај колону — Додај прилагођену колону) и у прозору који се појави унесите нашу функцију гетДата, наводећи за њега као аргумент пуну путању до сваке датотеке:

После клика на OK креирану колону треба додати у нашу табелу са десне стране.

Сада избришемо све непотребне колоне (као у Екцелу, користећи десни тастер миша - уклонити), остављајући само додату колону и колону са именом датотеке, јер ће ово име (тачније град) бити корисно имати у укупним подацима за сваки ред.

А сада „вау тренутак“ – кликните на икону са сопственим стрелицама у горњем десном углу додате колоне са нашом функцијом:

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

За потпуну лепоту, такође можете да уклоните .клск екстензије из прве колоне са именима датотека – стандардном заменом са „ништа“ (десни клик на заглавље колоне – Субституте) и преименујте ову колону у град. И такође исправите формат података у колони са датумом.

Све! Кликните на Почетна – Затвори и учитај (Почетна — Затвори и учитај). Сви подаци прикупљени упитом за све градове биће учитани у тренутни Екцел лист у формату „паметна табела“:

Створену везу и нашу функцију монтаже није потребно ни на који начин посебно чувати – они се чувају заједно са тренутном датотеком на уобичајен начин.

Убудуће, са било каквим променама у фасцикли (додавање или уклањање градова) или у фајловима (промена броја редова), биће довољно да кликнете десним тастером миша директно на табелу или на упит у десном панелу и изаберете команда Ажурирајте и сачувајте (Освјежи) – Повер Куери ће поново „обновити“ све податке за неколико секунди.

PS

Амандман. Након ажурирања из јануара 2017., Повер Куери је научио како да сам прикупља Екцел радне свеске, тј. више нема потребе да правите посебну функцију – то се дешава аутоматски. Дакле, други корак из овог чланка више није потребан и цео процес постаје приметно једноставнији:

1. Изаберите Креирајте захтев – Из датотеке – Из фасцикле – Изаберите фасциклу – ОК
2. Након што се појави листа датотека, притисните Променити
3. У прозору уређивача упита проширите колону Бинарно двоструком стрелицом и изаберите назив листа који ће се узети из сваке датотеке

И то је све! Песма!

• Редизајн унакрсне табеле у равну погодну за израду стожерних табела
• Прављење анимираног графикона са мехурићима у Повер Виев-у
• Макро за састављање листова из различитих Екцел датотека у једну