|
Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Jak pivotovat nebo unpivotovat tabulku v Excel. Aneb jak převést sloupce na opakující se řádky.
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).
Než dlouze popisovat je lepší ukázka (je vidět na první pohled co se děje)). Jak převádět.
Opět pro přehlednost je článek rozdělen na kapitoly
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:
Město | Rok | Leden | Únor | Březen | Duben |
---|---|---|---|---|---|
Brno | 2017 | 8 | 12 | 13 | .. |
Praha | 2017 | 9 | 13 | 16 | .. |
Brno | 2018 | 9 | 13 | 14 | .. |
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í.
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.
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
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.
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.
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.
Chcete-li se dozvědět o PowerQuery více doporučuji:
Soubor
PowerQuery pivotovat / unpivotovat - data pro praktické příklady - Excel
ke stažení zdarma.
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: 19.09.2020 11:02
Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.
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ů.
Můžete být prvními co zanechají smysluplný komentář.
Pomohl Vám návod? Sdílejte na Facebooku, G+ |
||
LinkedIn... |
Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2021 |