Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:
Mřížka nebo-li hash # ve vzorcích Excelu. Jak funguje nejen ve vzorcích ale například i v ověření dat.
Funguje v nových verzích Excel, které mají k dispozici dynamické odkazy.
V tomto článku se dozvíte, jak využít a co znamená mřížka ve spojení s dynamickými funkcemi.
Mřížka #, neboli hash ve vzorcích znamená odkaz na celou dynamickou oblast. Představte si že máte vytvořenou dynamickou oblast využitím funkce (v buňce C6):
=SEQUENCE(3)
Pokud zadáte do prázdné buňky znak rovná se =, a začnete tuto oblast označovat objeví se známý rozsah =C6:C7, ale jak ji označíte celou, získáte =C6#:
=C6#
A celá oblast zůstane označena. Následně klikem na Enter se zobrazí celý sloupec (hodnoty) z původní tabulky vytvořené funkci SEQUENCE.
Samozřejmě vaše verze Excel musí mít k dispozici dynamické odkazování (např. 365).
Podobně pokud nepůjde o sloupec, ale oblast vytvořenou například využitím funkce (v buňce C10):
=SEQUENCE(3;2)
Pokud do buňky zadáte =C10#, opět se vám označí a po kliku na Enter přepíše celá oblast.
=C10#
Pokud vytvoříte řádek a sloupec pomocí funkci SEQUENCE:
Buňka C14: =SEQUENCE(;2)
Buňka B15: =SEQUENCE(3)
Následně pokud do buňky F15 zadáte vzorec =C14#*B15# dojde k pro násobení hodnot v řádku a ve sloupci, nepobo-li získáte malou násobilku:
=C14#*B15#
Mějte jednoduchou tabulku, se jmény, kdy následně z tabulky získáte unikátní hodnoty:
=UNIQUE(B7:B12)
Následně můžete využít funkci POČET2, kdy zjistíte počet hodnot v dynamické oblasti
=POČET2(D7#)
Případně ve spojení s funkci SEQUENCE můžete načíslovat, podle počtu hodnot.
=SEQUENCE(POČET2(D7#))
Nevýhodou je že pokud do tabulky přidáte hodnotu, budete muset upravit funkci =UNIQUE(B7:B12) a rozšířit ji o další hodnotu např na =UNIQUE(B7:B13). COž je nevýhoda. Výhoda, že další dvě funkce již se přepočtou automaticky.
=POČET2(D7#)
=SEQUENCE(POČET2(D7#))
Napadá-li vás otázka co s tím, můžete přijít na odpověď tabulka jako tabulka, což je popsáno v další kapitole.
V předchozí kapitole, jsme se podívali na klasickou tabulku a zjistili její nevýhody. Když ale tabulku převedeme na tabulku jako tabulka. Případně ji získate pokud využíváte nástroj Power Query Editor.
Vytvořit nad daty tabulku jako tabulka lze. Pás karet Domů > Tabulka jako tabulka a vybrati se design.
Následně se na tabulku jako tabulka odkázat do konkrétního sloupce. Kdy v odkaze vidíte název tabulky a v hranaté závorce název sloupce:
=UNIQUE(Tabulka1[Jméno])
Následně můžete využít funkce jako POČET2 a vytvořit seznam čísel:
=POČET2(D7#)
=SEQUENCE(POČET2(D7#))
Opět mějte tabulku vytvořenou pomocí funkce SEQUENCE:
=SEQUENCE(4;3)
Co když ale se budete chtít odkázat na konkrétní buňku, můžete využít funkci INDEX, případně POSUN:
' Hodnota na prvním řádku a prvním sloupci
=INDEX(B6#;1;1)
' třetí řádek a druhý sloupec
=INDEX(B6#;3;2)
' Pokud využijete funkci POSUN (OFFSET)
=POSUN(A5;1;1)
Pokud chcete získat sloupec:
' první sloupec
=INDEX(B6#;;1)
' druhý sloupec
=INDEX(B6#;0;2)
Pro oblast:
=POSUN(A6;1;1;2;2)
=POSUN(B6;0;0;2;2)
=POSUN(B6;2;1;2;3)
Mějte opět oblast vytvořenou funkci SEQUENCE:
=SEQUENCE(4;3)
Počet číselných hodnot
=POČET(B6#)
Počet neprázdných buněk
=POČET2(B6#)
Počet řádku
=ŘÁDKY(B6#)
Počet sloupců
=SLOUPCE(B6#)
Další možností je využít mřížku v ověření dat. Mějte funkci v buňce D7, kde využitím funkce UNIQUE získáme z tabulky (může jít o tabulku jako tabulka) unikáty.
=UNIQUE(Tabulka3[Jména])
Následně v ověření dat stačí vložit jako seznam do zdroje hodnotu:
=D7#
Mějte tabulku jako tabulka, kdy potřebujete z prvního sloupce vybrat hodnotu a na základě této hodnoty si vybrat z druhého sloupce hodnoty které jsou přiřazeny k první hodnotě.
Například máte tabulku jmen, kurzu (absolvovaných), Body (počty bodu v kurzu). Vy si vyberete jméno, následně Kurz (ale jen ten který daný člověk absolvoval). Následně se pak zobrazí body.
Ukázka tabulky:
Vytvořme pomocnou tabulku, která zobrazí unikátní jména a ke jménům unikátní absolvované kurzy a body z těchto kurzu.
=TRANSPOZICE(UNIQUE(Tabulka4[Jména]))
=FILTER(Tabulka4[[Kurzy]:[Kurzy]];Tabulka4[[Jména]:[Jména]]=G6;"")
Následně budu chtít na základě výběru jména se zobrazí absolvované kurzy (vybraného jména), které má daný člen absolvovány. Ke jménu potřebuji přiřadit kurzy. Mohu využít například funkci XLOOKUP, ale když ji napíši klasicky tak se zobrazí jen první nalezena hodnota:
=XLOOKUP(J6;G6:H6;G7:H7)
Pokud přidáte mřížku, obdržíte co potřebujete:
=XLOOKUP(J6;G6:H6;G7:H7)#
Následně do ověření dat doplníte:
' pro jména do D18
=G6#
' pro kurzy do E18 na základě jména se zobrazí kurzy
=XLOOKUP(D18;G6:H6;G7:H7)#
Zbývá dopočíst body například využitím funkce FILTER
=FILTER(Tabulka4[Body];(Tabulka4[Jména]=D18)*(Tabulka4[Kurzy]=E18))
V přípravě
Narazili jste na nějaký problém/ nejasnost? Máte tip na doplnění, můžete to zmínit v komentářích.
Článek byl aktualizován: 30.03.2022 21:24
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 - 2024 |