Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » Query-editor » Unpivotovat-Pivotovat-PowerQuery-Excel
Microsoft Excel logo

Unpivotovat - PowerQuery v Excel

Videokurzy Excel

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.

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.

Unpivotovat - PowerQuery  Excel ukázka jak vypadá

Co se v článku dozvíte

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

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).

Vložení dat do PowerQuery  Excel ukázka jak vypadá

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

dialogové okno - PowerQuery - Excel ukázka jak vypadá

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.

Unpivotovat - PowerQuery  Excel ukázka jak vypadá

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

Uložit a zavřít

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

Uložit a zavřít - PowerQuery  Excel ukázka jak vypadá

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.

pivotovat - kontingenční sloupec - PowerQuery  Excel ukázka jak vypadá

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ů.

Pivotovat - PowerQuery  Excel ukázka jak vypadá

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

Uložit a zavřít - PowerQuery  Excel ukázka jak vypadá

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:


Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor PowerQuery pivotovat / unpivotovat - data pro praktické příklady - Excel soubor ve formátu *.xlsm 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: 03.12.2018 14:44

Odměna

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.


Pavel Lasák - autor webu

Pavel Lasák

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ů.

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


TOPlist Licence Creative Commons webarchiv rss XML

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 - 2018 | 165

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.