Telefonszámunk: 1-472-0679

Elemi értékekre bontás – UNPIVOT, a nagy trükk

2018-05-27 - horvimi - Megtekintések száma: 774 - Kategória: Általános tippek, PIVOT tábla
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016 Office365

Gyakran találkozom azzal a problémával, hogy egy PIVOT jellegű elrendezésben lévő tartományt át kellene rendezni a következő ábra szerint. Ezt a műveletet UNPIVOT néven nevezi a világ. Magyarul elemi értékekre bontásnak nevezik.

Unpivot-probléma

Azt hiszem, hogy egyetérthetünk abban, hogy ezt manuális munkával elvégezni eléggé reménytelen vállalkozás. Főleg akkor, ha sok sor vagy/és sok oszlop van az eredeti táblában.

Erre a problémára makrót szoktak írni, vagy használható a PowerQuery egyik menüje is, de lehet találni fizetős UNPIVOT Add-in-t, azaz bővítményt is,  ami segít a megoldásban. Volt egy Fórum kérdés is még 2016-ban, ahol szintén a PowerQuery-s megoldás született.

A minap tanulmányoztam azt, hogy hogyan lehet több, azonos szerkezetű adatforrás konszolidálásával PIVOT táblát készíteni. Ez a lehetőség be van építve elég régóta az Excel PIVOT varázslóba. Újra megnéztem, és újra arra jutottam, hogy használhatatlan. Megerősödött az a véleményem, hogy a több adatforrást bármilyen eszközzel előbb egy közös adatforrásba kel tenni, és utána kell elkészíteni a riportot.

A megvilágosodás

Miközben a fentieket tanulmányoztam, elém tárult egy lehetőség, amit korábban nem ismertem, pedig eléggé beleástam magam a PIVOT táblák témájába. Eddig az UNPIVOT típusú feladatokra a Power Query-t használtam és ezt is tanítottam. Erre kiderül, hogy sima PIVOT tábla megoldással is megoldható ez a kérdés.

UNPIVOT lépések

Elsőként el kell indítani a PIVOT tábla varázslót. Itt beleütközünk abba a problémába, hogy a PIVOT varázsló nincs alapból fent egyik szalagon sem. De előcsalogathatjuk  a magyar Excelben az Alt->D->I billentyűk egymás utáni (nem egyszerre!!!) lenyomásával. Angol Excelben ugyanez az Alt->D->P.

Itt beállítjuk, hogy Több tartományt szeretnénk összesíteni. Ez nem igaz ugyan, de most ezt kell választani.

Pivot-varázsló-első-lépés

Második lépésben beállítjuk, hogy az oldalmezőket a felhasználó készíti

Pivot-varázsló-második-lépés

A harmadik lépésben megadjuk, hogy melyik tartományt akarjuk elemeire bontani. Kijelöljük a teljes tartományt, és a Hozzáadás gombbal érvényesítjük. Lent jóváhagyjuk, hogy nem kell oldal mező.

UNPIVOT harmadik lépés

A negyedik lépésben a kimutatást egy új oldalra kérjük. Eddig még semmi nem történt, “csak” az eredeti tartományt tettük be egy PIVOT táblába.

UNPIVOT negyedik lépés

Itt jön a meglepetés!

Ha kettőt kattintunk a végösszegen, akkor egy új munkalapon elénk tárul a csoda, az UNPIVOT végeredmény. Már csak annyi dolgunk marad, hogy átnevezzük a fejléc tételeit.

UNPIVOT-végeredmény

Figyelem!
Csak abban az esetben használható ez a megoldás, ha az eredeti táblában csak az egy, azaz az első oszlopot akarjuk alábontani a többi oszlop adataival.

Ha több oszlopot szeretnénk elemi értékekre bontani, akkor marad a Power Query.

Lássuk mindezt videón

Vélemény, hozzászólás?