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

Jste zde: Úvodní stránka » excel » Query-editor » Dynamicka-prace-s-radky-Editor-Power-Query
Microsoft Excel logo

Dynamická práce s řádky v Editoru Power Query

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Jak na dynamickou práci s řádky v nástroji Editor Power Query. Noboli potřebujete zobrazit data od požadovaného do jiného požadovaného řádku.

Návod funguje pro Excel i Power BI.

Úvodem do dynamické práce s řádky v Editoru Power Query

Představte si, že máte tabulku, ze které potřebujete získat data, které jsou ve středu. Bohužel pozice dat se mění, ohraničují jí konkrétní slova (ID, Celkem). Jak na to s využitím Editoru Power Query se dozvíte v tomto článku.

Ukázka, kdy potřebujete zobrazit pouze šedou oblast:

Dynamická práce s řádky - Data

Pro přehlednost je článek rozdělen na kapitoly:

Data

K dispozici mějme v Excel tabulku, kde potřebujete zjistit (zobrazit) prostřední čas dat. Kdy víte že tabulka začíná/končí pokud je ve v první sloupci:

  • Začátek - obsahuje slovo/text ID, a zároveň chcete i tento řádek
  • Konec - obsahuje slovo Celkem. Tento řádek ve výpise dat nepotřebujete.

Z důvodu zjednodušení se v prvním sloupci nacházejí tato slova (ID, Celkem) pouze jednou.

Data pro zpracování.

Načtení do Editoru Power Query

Tabulku načteme do Editoru Power Query, jelikož neznáme kolik řádku tabulka může obsahovat, navíc některé řádky mohou být prazdné. Proto označíme sloupce od A do D (pokud by sloupců bylo více tak samozdřejmě jich označíte příslušný počet ).

V Excelu na pásu karet Data vyberte Z tabulky nebo oblasti

V zobrazeném dialogovém okně Vytvořit tabulku pak stačí klik na OK.

Data se načtou do Editoru Power Query

Editor Power Query - načtená data

Následně pro první sloupec odstraňte hodnoty null. Klik na malou šipku a odškrtnout položku null a klik na OK, protože nevíte jak je tabulka velká a řádky, které v prvním sloupci neobsahují hodnoty nepotřebujete.

Editor Power Query - načtená data - upravena

Nalezení řádku od a řádku do

Protože potřebujete najít v prvním sloupci názvy (ID a Celkem), pro jistotu nejprve první sloupec převeďte na text (pokud ještě nemáte). Klik na ikonku ABC123, a vyberte Text.

Editor Power Query - první sloupec jako text

Protože budete potřebovat čísla řádku, přidejte číslování řádku. Na pásu karet Přidaní sloupce rozbalit ikonu Indexový sloupec a vyberte Od 0. Za mně číslování od nuly je jednodušší, ale klidně můžete začít řádky číslovat od jedničky.

Máte přidány sloupec Index

Sloupec očíslován od nuly...

Máte-li hotovo, tak pro první sloupec naleznete číslo pro řádek s textem "ID" a zároveň číslo řádku pro hodnotu v prvním sloupci s textem "Celkem".

Rozklikněte šipku u prvního sloupce a vyberte Filtr textu a následně Začíná na...

Filtr začíná na v Power Query Editoru

V dialogovém okně Filtrovat řádky zadáte:

  • má na začátku a zadejte ID
  • Nebo
  • má na začátku a zadejte Celkem
 Power Query Editoru - filtrovat nastavení

Výsledkem bude:

 Power Query Editoru - vyfiltrované řádky

Tak a máte čísla řádku mezi kterými, potřebujete mít data. Následně se podíváme na vyfiltrování oblasti.

Vyfiltrování oblasti

Čísla máte, teď potřebujete je vyfiltrovat, ale v základu Power Query Editor postupuje, krok za krokem a teď už nevidíte ostatní data, ale můžete použít fintu a opět budete vidět všechna data.

Teď bude poptřeba trochu vstupovat do M-kódu a bude se hodit mít zobrazen řádek vzorců. Pokud jej ještě nevidíte, můžete řádek vzorců zobrazit na pásu karet Zobrazení a zakliknout Řádek vzorců

Řádek vzorců

Jelikož potřebujete opět vidět všechny data, klik na ikonu fx v řádků vzorců.

Řádek vzorců

Na první pohled vypadá, že se nic nestalo, ale přidal se nový krok Vlastní1 a přidal se do řádku vzorců text (vzorec):

= #"Filtrované řádky1" Řádek vzorců - přidáno

Tento potřebujete přepsat, ale ať to máte snadnější, tak nejprve přepíšete v použitém postupu krok Filtrované řádky na Filtr přes pravé tlačítko mto krokem nad vybrat přejmenovat na "Filtr".

Přejmenovat krok

Výsledek

Přejmenovat krok

Přesuneme se na poslední krok Vlastní1 - (onen poslední přidaný vzorec)

= #"Filtrované řádky1"

A přepíšete ho na:

=Filtr

A obdržíte (uvidíte) zase všechny data.

Excel

Perfektní co? Teď už zbýva jen vyfiltrovat...

Odstranit řádky za Celkem včetně

Pokud si pamatujete ještě, kdy jste měli pouze dva řádky (pokud ne mrkněte v článku výše, nebo se podívejte na předchozí kroky v Editoru). Teď víte, že číslo řádku na kterém se ono slovo nachází je 11. Takže postačí když ponecháte data po 11 řádek.

Zvolte na pásu karet Domů - Zachovat řádky - Zachovat horní řádky a do dialogového okna zadáte 11.

Přidá se krok Zachované první řádky, který obsahuje vzorec:

= Table.FirstN(Vlastní1,11) Excel

Jistě vám dojde, že ta 11 by se měla měnit dynamicky. V tom máte pravdu. Číslo řádku víte, že je v kroku Filtrované řádky 1. Pro snadnější použití tento krok přejmenujete na Radky (využitím pravého tlačítka a přejmenovat, viz výše).

Čísla-řádku

Tak a teď zbývá v posledním kroku nahradit vzorec = Table.FirstN(Vlastní1,11) za:

= Table.FirstN(Vlastní1,Radky[Index]{1})

Neboli číslo 11 vyměníte za

Radky[Index]{1}

Kde Radky je název kroku na který se odkazujete, [Index] slov v hranatých závorkách je název sloupec a {1} číslo v složených závorkách je číslo řádku, z kterého chcete hodnotu (chceme hodnotu z druhého řádku, proto jednička, jelikož se řádky číslují od nuly).

Čísla-řádku

Samozřejmě se na výstupu nezměnilo. Stále vidíte data bez řádsku začinajicího Celkem a dále. Jen se zobrazují dynamicky (když se změní zdroj, přepočtou se čísla řádku a máte zobrazeno co potřebujete).

Odstranit řádky k ID

Odstranit první řádky provedeme podobně. Potřebujete odstranit prvních 6 (pokud nevěříte můžete se podívat výše).

Vyberete z menu (pásu karet) Domů - Odebrat řádky - Odebrat horní řádky a do dialogového okna zadat číslo 6.

Potěšilo vás? Je vidět i řádek s ID.

Samozřejmě, že ve stejném duchu potřebujete upravít vzorec tak, ať funguje dynamicky na základě pozice ID:

= Table.Skip(#"Zachované první řádky",6)

Teď jen přepíšete číslo 6 vzorcem:

Radky[Index]{0}

Nebo-li výsledný vzorec bude vypadat:

= Table.Skip(#"Zachované první řádky",Radky[Index]{0})

Název kroku, název sloupce a číslo řádku.

Jen požadované řádky

Máte hotovo. Teď je postačí uložit a zavřít.

Další úpravy a vylepšení

Tipy na další úpravy a vylepšení kódu (před uložením):

  • První řádek jak záhlaví
  • Typ pro sloupce
  • Zkrácení kódu

První řádek jak záhlaví

Na pásu karet Domů a Použít první řádek jako záhlaví

= Table.PromoteHeaders(#"Odebrané horní řádky", [PromoteAllScalars=true])

Zkrácení kódu

Typy hodnot ve sloupci, ať je datum jako datum a čísla jako čísla. Což určitě zvládnete sami.

Zkrácení kódu

Zkrácení a vylepšení kódu bude předmětem samostatného článku.

Stejně tak v dalších článcích zmíním, proč jsme kroky přejmenovávali na jednoslovné a nenechávali dvouslovné s mezerou.

Poznámka: Třeba v některé z novějších verzí Editoru Microsoft upraví a bude používat pro názvy kroků velbloudí konvenci, neboli názvy kroku bez mezer například: "CisloRadkuPoslemni" atd.

Související články

Další související články:

Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor ke stažení zdarma je v přípravě. Podpořit zveřejnění můžete podporou na Patreon.


Závěrem

Narazili jste na nějaké problémy, máte tip na vylepšení nebo doplnění článku, můžete se zmínit v komentářích.

Článek byl aktualizován: 13.01.2022 20:08

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ář.






Excel


Sdílejte

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

Nové články


Reklama


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 - 2021 |