Maticové vzorce III

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.

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.

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.

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