Tweet

Unpivotovat - PowerQuery v Excel

Teorie k pivotování/unpivotování

Mnohdy jsou k dispozici tabulky které se nehodí k automatickému zpracování (ale jsou přehledné pro zobrazení). Pro možnost automatického zpracovávání je potřeba nahradit (převést) sloupce do řádku - neboli "unpivotovat". Případně lze provést opak, pro vložení do prezentací dat potřebujete nevzhlednou dlouhou tabulku "pivotovat" (v češtině pod názvem kontingenční sloupec).

Ukázka

Než dlouze popisovat je lepší ukázka (je vidět na první pohled co se děje)). Jak převádět.

Co se v článku dozvíte

Opět pro přehlednost je článek rozdělen na kapitoly

• Data
• Vložení dat do PowerQuery
• Unpivotovat
• Pivotovat
• Související články
• Ke stažení

Data

Pro ukázku máme data ve dvojím formát jelikož potřebujeme projít obě možnosti. Navíc aby nešlo o klasické učebnicové případy, tak jsou tabulky nepatrně komplikovanější, mají dva sloupce, které se nebudou unpivotovat a 12 sloupců (měsíce), které unpivotovat potřebujete.

Jdou o fiktivní průměrné teploty ve dvou městech (Brno, Praha). Data budou ve dvou formátech:

• pro unpivotování - měsíce jsou v řádku
• pro pivotování - měsíc je ve sloupci a v druhém přislušné hodnoty

Pro unpivotování

Město	Rok	Leden	Únor	Březen	Duben
Brno	2017	8	12	13	..
Praha	2017	9	13	16	..
Brno	2018	9	13	14	..

Pro pivotování

Město	Rok	Měsíc	Teplota
Brno	2017	Leden	8
Brno	2017	Únor	12
Brno	2017	Březen	13
..	..	..	..

Zdrojová data jsou k dispozici v kapitole ke stažení.

Vložení dat do PowerQuery

Než se podaří unpivotovat (nebo pivotovat) je potřeba data vložit do PowerPivot. Stačí mít aktivní buňku v tabulce a přes menu (pás karet):

Data v sekci Načíst a transformovat klik na ikonu Z tabulky nebo oblasti (v některých verzích je název ikonky "Z tabulky").

Obdržíte dotaz s informaci, kterou oblast Excel pro tabulku vybral. A případně zkontrolujete zda správně Excel poznal, zda tabulka obsahuje záhlaví (zaškrtnete/odškrtnete podle pravdy).

Následně se zobrazí samostatná aplikace PowerQuery, kde lze již bez problému pivotovat, nebo unpivotovat vaše tabulky.

Unpivotovat

V tabulce potřebuji řádky a hodnoty pro jednotlivé měsíce (kdy daný měsíc má hodnoty ve sloupci) dostat do dvou sloupců, jeden bude měsíc (jeho název) a druhý příslušná hodnota.

Protože již data máte v PowerQuery, tak stačí jen označit sloupce s názvy měsíců (k označování můžete použít i klávesové zkratky Shift atd.) a z menu:

Transformace v sekci Libovolný sloupce klik na ikonu Převést řádky na sloupce a z rozbaleného menu vybrat Převést jen vybrané sloupce. A máte HOTOVO.

Tip: můžete jít na to i jinak označit sloupce které unpivotovat nechcete a pak z menu vybrat Převést další sloupce na řádky

Pokud máte Excel (Power Query) v angličtině tak na kartě Transform klik na ikonu Unpivots Columns a Unpivots Columns. Případně lze také mít označeny sloupce, které nechcete unpivotovat (převést na řádky) a využít Unpivots Columns a Unpivots Only Selected ColumnsColumns

Uložit a zavřít

Pro potvrzení úprav stačí z menu Soubor vybrat Zavřít a načíst

Poznámka. Podrobněji o menu Soubor v samostatném článku.

Pivotovat

Pokud jste v opačné situaci, máte velice dlopuhou tabulku a potřebujete ji pivotovat, ať se zpřehlední pro publikaci v tištěné podobě.

Nejprve je potřeba označit v PowerQuery sloupec, které potřebujete dostat do záhlaví, v našem případě to budou sloupec měsíc. Následně z menu:

Transformace v sekci Libovolný sloupce klik na ikonu Kontingenční sloupec.

V následujícím kroku se Excel PowerQuery zeptá, který sloupec je ten jehož hodnoty bude pivotovat. Což v našem ukázkovém příkladu bude teplota. Neboli čísla (teploty), která potřebujete seskupit podle měsíců.

Pro anglickou verzi Power Query z pásu karet Transform využijete ikonu Pivot Column poté co jste si označily sloupec, který se má stát popisem řádku.

V následujícím dialogovém okně vyberete co se má do hodnot vložit. číselný sloupec a pak klik na OK. A máte hotovo.

Uložit a zavřít

Pro potvrzení úprav stačí jako v předchozím případě) z menu Soubor vybrat Zavřít a načíst

Poznámka. Podrobněji o menu Soubor v samostatném článku.

Související články

Chcete-li se dozvědět o PowerQuery více doporučuji:

• PowerQuery Editor - popis okna
• PowerQuery Editor - práce se sloupci
• PowerQuery Editor - práce s řádky

Ke stažení

Soubor PowerQuery pivotovat / unpivotovat - data pro praktické příklady - Excel ke stažení zdarma.

Závěr

Máte nějaký tip jak pivotovat (kontingenční sloupec) a unpivotovat (řádky na sloupce) v PowerQuery pod Excel? Můžete zmínit v komentářích.

Článek byl aktualizován: 24.10.2019 15:02

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.