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).
- Úvodní
SUMA
naznačuje, že celý tento vzorec budeme sčítat. - 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ě. - Výraz:
(B2:B20=H2)
zajistí, že budeme sčítat pouze prodeje obchodníka Jirky. - 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ý?
- 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í funkciODKAZ
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 (funkcePOZVYHLEDAT
je stejná u obou výskytů funkceODKAZ
) - 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) - 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. - Výsledkem bude odkaz na oblast F2:F20 – takže změnou buňky J1 měníme i oblast sčítání.
- 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).
Komentáře![rss](./plugins/templates/wall_2C/images/icons/rss.png)
Zasílate odpověď ke stávajícímu příspěvku (zrušit).
Nemáte oprávnění přidávat příspěvky.
Registrace >
Ztracené heslo >
Ušetřil jsi mi pěkných pár hodin práce, díky.citovat