Kapitola 17 · 17.4
Vyhledávání podle dvou podmínek současně
SVYHLEDAT a XLOOKUP vyhledávají podle jedné hodnoty (viz SVYHLEDAT vrací #N/A: jak to vyřešit a XLOOKUP: vyhledávání doleva a s vlastní zprávou). V praxi však často hledáte podle dvou věcí současně: cenu kytice ve velikosti L, sazbu zákazníka v určitém roce nebo zásobu produktu v určité barvě. K tomu slouží tři dobré způsoby, které všechny fungují s obyčejným Enter. Čtvrtý způsob, se kterým se na internetu často setkáte, v Euro-Office bez dalšího nefunguje.
Jako příklad použijeme ceník květinářství Bloemisterij Het Zonnetje. Stejný produkt je v něm uveden několikrát, v různých velikostech:
| A | B | C | |
|---|---|---|---|
| 1 | Produkt | Velikost | Cena |
| 2 | Kytice | S | 17,50 |
| 3 | Kytice | M | 24,50 |
| 4 | Kytice | L | 34,50 |
| 5 | Smuteční věnec | M | 65,00 |
| 6 | Smuteční věnec | L | 89,00 |
| 7 | Svatební kytice | M | 79,50 |
Hledaný produkt je v buňce F2 (Kytice), velikost v buňce G2 (L). Hledáme hodnotu 34,50.
Způsob 1: XLOOKUP se spojenými hodnotami
=XLOOKUP(F2&"|"&G2;A2:A7&"|"&B2:B7;C2:C7;"nenalezeno")
Znak & spojuje text dohromady. Z F2&"|"&G2 vznikne "Kytice|L". A2:A7&"|"&B2:B7 vytvoří stejnou kombinaci z každého řádku seznamu ("Kytice|S", "Kytice|M" a tak dále) a XLOOKUP v nich vyhledává. Svislá čára | mezi hodnotami zabraňuje tomu, aby dvě různé kombinace náhodou daly stejný text. Pokud kombinace neexistuje, například smuteční věnec ve velikosti S, zobrazí se "nenalezeno".

Když se po zadání podíváte do řádku vzorců, uvidíte před A2:A7 a B2:B7 znak @. Ten tam doplní Euro-Office sám; výsledek je přesto správný.
Způsob 2: FILTER pro všechny shody
=FILTER(C2:C7;(A2:A7=F2)*(B2:B7=G2);"žádné")
FILTER nevrací jen první, ale všechny řádky, které splňují obě podmínky. Pokud se kombinace vyskytuje dvakrát, uvidíte obě ceny pod sebou. Zajistěte proto, aby pod vzorcem bylo volné místo (viz Seznamy, které se samy aktualizují, s funkcemi FILTER, UNIQUE a SORT). Pro „nebo“ místo „a“ použijte znaménko plus: (A2:A7="Kytice")+(A2:A7="Smuteční věnec").
Způsob 3: SUMIFS, pokud je kombinace jedinečná
=SUMIFS(C2:C7;A2:A7;F2;B2:B7;G2)
Zní to zvláštně, protože nic sčítat nechcete. Pokud se ale každá kombinace vyskytuje jen jednou, je součtem jednoduše ta jedna cena. Výhoda: SUMIFS je jednoduchá, rychlá a zná ji každá verze Excelu. Nevýhoda: pokud kombinace neexistuje, dostanete 0 (0,00 €) místo hlášení. A pokud je kombinace omylem uvedena dvakrát, dostanete obě ceny sečtené.
Co najdete na internetu: INDEX a POZVYHLEDAT s násobením
Mnoho webů o Excelu uvádí =INDEX(C2:C7;POZVYHLEDAT(1;(A2:A7=F2)*(B2:B7=G2);0)). V Euro-Office tento vzorec po potvrzení obyčejným Enter vrací #N/A, i když kombinace existuje. Pokud ho potvrdíte klávesami Ctrl+Shift+Enter, vzorec dostane složené závorky {…} a funguje. Na to se snadno zapomene, proto raději použijte XLOOKUP nebo FILTER popsané výše.
Vyhledávání podle řádku i sloupce
Pokud máte ceník jako tabulku s produkty pod sebou a velikostmi vedle sebe (matici), použijte INDEX s dvojím POZVYHLEDAT. S produkty v A11:A13, velikostmi S, M a L v B10:D10 a cenami v B11:D13:
=INDEX(B11:D13;POZVYHLEDAT(F2;A11:A13;0);POZVYHLEDAT(G2;B10:D10;0))
První POZVYHLEDAT najde řádek produktu, druhá sloupec velikosti. INDEX vrátí buňku na jejich průsečíku.
Který způsob zvolit?
| Situace | Nejlepší volba |
|---|---|
| Jedna odpověď s přehledným hlášením, pokud neexistuje | XLOOKUP se spojenými hodnotami |
| Soubor musí fungovat i ve starém Excelu | SUMIFS |
| Může existovat více shod | FILTER |
| Cenová tabulka s velikostmi jako záhlavími sloupců | INDEX s dvojím POZVYHLEDAT |
Na co si dát pozor
- #N/A, přestože kombinace v tabulce opravdu je: často jde o mezeru za hodnotou ("Kytice ") nebo o číslo uložené jako text. Viz SVYHLEDAT vrací #N/A: jak to vyřešit.
- Oblasti různé délky: =XLOOKUP(F2;A2:A7;C2:C50) vrací #VALUE!. Nechte všechny oblasti začínat a končit na stejném řádku.
- Celé sloupce (A:A) v těchto vzorcích velký soubor zpomalují, protože Euro-Office pak spojuje milion řádků. Použijte pevnou oblast, například A2:A500. Viz Zrychlení pomalého sešitu.
- Kolega s Excelem 2019 nebo starším uvidí u XLOOKUP a FILTER chybu #NAME?.
