Nacházíte se: WALL.czExcel návod › Maticové vzorce III
Kategorie: Excel návod

Maticové vzorce III

ExcelNebaví vás neustále měnit oblasti, které má Excel sčítat, zkuste maticové vzorce ve spojení s funkcemi NEPŘÍMÝ.ODKAZ a ODKAZ.

V druhém dílu našeho miniseriálu Maticové vzorce II. jsme prozkoumali možnosti maticových vzorců na poli podmíněných součtů a počtů. Dnes si tuto oblast mírně rozšíříme a podíváme se na to, jak dynamicky měnit sčítanou oblast; samozřejmě zachováme podmíněný součet.

O co vlastně jde…

Používáte-li funkci SUMIFS, musíte vždy definovat, kterou oblast budete sčítat. Maticové vzorce dokáží měnit sčítanou oblast (nebo i oblasti podmínek) na základě hodnoty v buňce, což může být mnohdy velice praktické (např. při aplikování maticových vzorců na výjezdy z databází, které nemají požadované sloupce vždy na stejném místě, tedy někdy ve sloupci C a podruhé např. ve sloupci F).

Podívejme se na obrázek níže, který nápadně připomíná naši úlohu z minulého dílu, jen jsme přidali údaje o počtu zákazníků a časové náročnosti prodeje. Jak už zřejmě tušíte, budeme schopni napsat takový vzorec, který splní, jak kritéria v buňkách H2:H5, tak v závislosti na hodnotě v buňce J1 sečte:

  • Částku.
  • Počet zákazníků.
  • Strávený čas.
a to vše bez zásahů do vzorce, pouze na základě změny vstupních údajů v buňkách.

Maticový vzorec

V řádku vzorců vidíte zmíněný vzorec – vypadá strašlivě, ale rozebereme si ho postupně a uvidíte, že je v něm jen jediná novinka oproti minulému dílu.

Vezmeme to ale hezky od začátku – z přechozích dílů víte, proč vzorec začíná a končí složenou závorkou a že se tyto závorky nepíší (Excel je doplní sám po ukončení vzorce stiskem CTRL + SHIFT + ENTER, protože jde o maticový vzorec).

  1. Úvodní SUMA naznačuje, že celý tento vzorec budeme sčítat.
  2. Výraz: (A2:A20>=H3)*(A2:A20<=H4) říká, že chceme sčítat jen prodeje, které se uskutečnily v období od 1.1.2011 do 31.3.2012 včetně.
  3. Výraz: (B2:B20=H2) zajistí, že budeme sčítat pouze prodeje obchodníka Jirky.
  4. Výraz: (C2:C20=H5) je zde proto, abychom sečetli pouze prodeje v oblasti sport.

A nyní se dostáváme k samotné novince tohoto dílu

(NEPŘÍMÝ.ODKAZ(ODKAZ(2;POZVYHLEDAT(J1;A1:G1;0))
& ":" &
ODKAZ(20;POZVYHLEDAT(J1;A1:G1;0))))

Abychom tomuto porozuměli, uděláme slavný Cimrmanovský krok stranou a podíváme se na funkce NEPŘÍMÝ.ODKAZ a ODKAZ.

Nepřímý.odkaz

Tato funkce je velice platná, protože dokáže z textového řetězce (z textu) udělat odkaz. Zřejmé to bude na následujícím obrázku.

Maticový vzorec

V buňce A1 je text „B3“, v buňce B3 je text „obsah buňky B3“ a buňce C5 je vzorec =NEPŘÍMÝ.ODKAZ(A1), se stejným výsledkem by skončil vzorec =NEPŘÍMÝ.ODKAZ("B3"), protože obsah buňky A1 je „B3“.

Odkaz

Tato funkce je vlastně opakem funkce NEPŘÍMÝ.ODKAZ, protože vrací adresu buňky podle zadaných hodnot řádku a sloupce. Funkce ODKAZ, tedy přijímá dva argumenty =ODKAZ(řádek, sloupec) a tyto číselné hodnoty do ní lze vložit jak přímým zápisem čísla, tak odkazem na buňku. Jak je vidět v následujícím obrázku.

Maticový vzorec

V buňce B4 se funkce odkazuje na buňky B1 (řádek) a B2 (sloupec), kdežto v buňce B5 jsou hodnoty zapsány přímo do funkce. V obou případech je však výsledek stejný, tedy textový odkaz na buňku E10.

Zpět k našemu maticovému vzorci

Nyní už víme, že funkce NEPŘÍMÝ.ODKAZ přijímá text a udělá z něj odkaz. Podívejme se tedy znovu na celý závěrečný výraz vzorce:
(NEPŘÍMÝ.ODKAZ(ODKAZ(2;POZVYHLEDAT(J1;A1:G1;0))
&":"&
ODKAZ(20;POZVYHLEDAT(J1;A1:G1;0))))

Zvýrazněné části výrazu představují text, ale jaký?

  1. Podíváme-li se na oba tvary funkce ODKAZ, zjistíme, že jediný rozdíl je v první části (určení řádku) – tedy první funkci ODKAZ je předán parametr 2 a v druhém výskytu parametr 20
    2 a 20 jsme zadali proto, že i ostatní matice máme od řádku 2 do řádku 20 (funkce POZVYHLEDAT je stejná u obou výskytů funkce ODKAZ)
  2. Funkce POZVYHLEDAT potom vrátí pořadí v oblasti A1:G1, kde byla nalezena hodnota zapsaná v buňce J1 („Strávený čas (hod)“) -> ta je ve sloupci F, kterému odpovídá číslo 6 (A=1, B=2…F=6)
  3. Mezi oběma ODKAZy je ještě tento zápis: & ":" & - ampersand (&) se používá pro spojování textů a přímo zapsaný text musí být v uvozovkách.
  4. Výsledkem bude odkaz na oblast F2:F20 – takže změnou buňky J1 měníme i oblast sčítání.
  5. Pokud bychom totiž do buňky J1 napsali slovo „Částka“ byla by výsledná oblast D2:D20

Na závěr bych jenom rád připomněl, že maticové vzorce je možné kombinovat s velkou spoustou tradičních funkcí, jako jsou SMALL, LARGE, ŘÁDEK, SLOUPEC a s mnohými dalšími. Na to se podíváme v dalším dílu našeho miniseriálu.

Soubor s příklady maticových vzorců z článku Nejste přihlášen(a).

print Formát pro tisk

Sdílet článek:

Komentáře rss

Nemáte oprávnění přidávat příspěvky.

Uživatelské jméno
Heslo
     

Registrace >
Ztracené heslo >


, Díky! odpovědět
avatar
Úžasný článek. Už drahnou dobu mě štve, že excel nemá sčítání podle argumentů v záhlaví řádků i sloupců, takový SUMIF(S), ale ve 2D matici. Má! Ale trochu komplikovanější 1

Ušetřil jsi mi pěkných pár hodin práce, díky.citovat

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura IV

Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.

Aktivní diskuse

spojení vzorců v PQ

lubo • 19.3. 10:47

Podmínka pro spuštění makra

elninoslov • 19.3. 9:19

duplicity

elninoslov • 19.3. 9:13

Podmínka pro spuštění makra

MilanKop • 18.3. 18:51

Podmínka pro spuštění makra

Stalker • 18.3. 18:34

duplicity

fortes • 18.3. 17:48

Podmínka pro spuštění makra

MilanKop • 18.3. 17:31