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

Jste zde: Úvodní stránka » excel » funkce-nove » hash-mrizka-Excel
Microsoft Excel logo

Jak na mřížku # hash - Excel

Videokurzy Excel

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.

Obsah článku o #

V tomto článku se dozvíte, jak využít a co znamená mřížka ve spojení s dynamickými funkcemi.

Microsoft Excel hash # ve vzorcích

Základní použití #

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.

Microsoft Excel hash # ve vzorcích

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# Excel hash

Použití mřížky nad tabulkou

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.

Excel hash

Použití mřížky nad tabulkou jako tabulka

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#)) Excel hash pro tabulku jako tabulka

Odkázat se na konkrétní buňku (řádek, sloupec)

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) Excel hash pro oblast

Počty v oblasti

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#) Excel hash pro oblast

Ověření dat

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# Excel hash pro oblast

Ověření dat - kombinace

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:

Excel zdroj

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)# Excel hash pro ověření dat

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)) Excel hash pro oblast

Další ukázky

V přípravě

Závěrem

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

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