Excel: Referenční funkce pro konkrétní informace

Takto je správně použita funkce Excelu

Máte obrovské množství dat a naléhavě potřebujete informace o určité hodnotě, která je zadána ve vaší tabulce? V tomto okamžiku má smysl používat referenční funkci aplikace Excel. Důvodem je, že umožňuje určit požadované informace pomocí kritéria vyhledávání a oblastí buněk, ve kterých se má požadovaná hodnota hledat. Funguje to automaticky a umožňuje rychlé vyžádání informací. Pokud chcete provádět cílenější vyhledávání, měli byste použít vylepšené referenční funkce Vreference, Wreference nebo XVreference. Excel nabízí další alternativy pro organizaci a načítání dat.

Jak se používá referenční funkce aplikace Excel

Referenční funkce aplikace Excel je užitečná pro hledání hodnoty ve sloupci nebo řádku, který je na stejném místě v jiném řádku nebo sloupci. Reference lze použít na jedné straně ve vektorové verzi a na druhé straně v maticové verzi. Vektorová verze se používá, když má být ve sloupci nebo řádku prohledáváno konkrétní vyhledávací kritérium. V maticové verzi lze prohledávat několik sloupců a řádků nebo celý list. K tomuto účelu však existují vylepšené funkce Vreference a Wreference. Program povoluje maticovou verzi pouze z důvodu kompatibility s jinými tabulkovými programy.

Jak je referenční funkce aplikace Excel strukturována ve vektorové verzi?

Referenční funkce aplikace Excel se skládá ze tří argumentů a má následující strukturu: Reference (vyhledávací kritérium, vyhledávací vektor, [výsledný vektor]).

• Kritérium vyhledávání je povinný argument. Zde se zadává hledaná hodnota. Hodnota může být číslo, text, logická hodnota, jméno nebo druhá referenční funkce.
• Hledat vektor je druhý požadovaný argument vzorce. Zde je zadána oblast sloupce nebo řádku, ve které má být hledaná hodnota hledána. Může to být také text, čísla nebo pravdivostní hodnoty.
• Výsledkový vektor: Hranaté závorky kolem třetího argumentu, vektoru výsledků, naznačují, že tento argument je nepovinný. V tomto bodě lze určit další oblast, která musí obsahovat tolik prvků jako druhý argument.

Hledání ve sloupcích - příklad

Ve sloupci A máte seznam čísel článků s odpovídajícími oděvy ve sloupci B. Hledáte konkrétní číslo zboží a chcete zjistit, ke kterému oděvu se číslo výrobku vztahuje.

Vyberte prázdný řádek.

Vložte vzorec = reference ("ART7492031"; A2: A6; B2: B6).

Funkce proto vyhledá číslo článku ve sloupci A a vygeneruje výsledek ze sloupce B, například „Celkově“.

Syntaxe odkazu vzorce ve verzi matice má méně argumentů

U maticové funkce se referenční funkce aplikace Excel skládá z odkazu (vyhledávací kritérium, matice). Neobsahuje žádné nepovinné argumenty, ale spíše dva povinné.

• Kritérium vyhledávání je první požadovaný argument a může obsahovat číslo, text nebo logické hodnoty, jméno nebo jinou referenční funkci.
• matice je řada buněk, která se používá pro srovnání. Najdete zde také čísla, řetězce nebo logické hodnoty.

Jak již bylo zmíněno, doporučuje se používat další referenční funkce aplikace Excel Vreference a Wreference, protože mají lepší funkce. Můžete například zadat číslo sloupce nebo index sloupce, který obsahuje návratovou hodnotu.

Hledání ve sloupcích - vektorová verze: příklad

Pokud se ve vektorové verzi předpokládá stejná situace jako ve výše uvedeném příkladu, postupujte podobně.

Vyberte prázdný řádek.

Vložte vzorec = reference ("ART7492031"; A2: B6).

Funkce proto vyhledá číslo článku ve sloupci A až ve sloupci B a jako výsledek vydá odpovídající oděv („celkově“).

To je třeba vzít v úvahu při použití referenční funkce

Hodnoty v Hledat vektor obě verze musí být seřazeny vzestupně. To znamená například pro čísla -1, 0, 1, 2, 3, atd., pro textové hodnoty A, B, C, atd. a pro pravdivostní hodnoty nesprávné následován skutečný. V textu se nerozlišují malá a velká písmena. Pokud podle vyhledávacího kritéria nelze najít žádnou hodnotu, referenční funkce aplikace Excel nabere hodnotu Hledat vektorkterá se nejvíce blíží hodnotě v kritériu vyhledávání. Pokud je hodnota kritéria hledání menší než ve vyhledávací oblasti, Excel vrátí chybovou hodnotu #NV.

Rychlé vyhledávání s těmito třemi referenčními funkcemi aplikace Excel

Uživatelé, kteří z důvodu kompatibility nepoužívají funkci odkazu na Excel, by měli používat Sreference, Wreference nebo vylepšenou verzi XLOOKUP. Umožňují přesnější vyhledávání hodnot například pomocí zástupných znaků.

Vyhledejte sloupce nebo tabulku podle odkazu

Referenční funkce VLOOKUP aplikace Excel hledá hodnotu v řádcích pomocí matice a indexu sloupců. Zde je důležité, aby byla data uspořádána tak, aby se hledaná hodnota nacházela hned vedle návratové hodnoty. Hodnoty však lze přiřadit také tak, že patří do určitých kategorií nebo skupin. Tento postup šetří čas při vyhodnocování tabulky.

Vreference může nejen prohledávat celé tabulky, ale lze je také kombinovat s dalšími funkcemi. Například je možné vyhledávat několik kritérií současně. Pokud chcete použít referenční funkci aplikace Excel pro několik propojených listů, mohou se vám zobrazit chybové zprávy, ale ty lze rychle vyřešit pomocí nastavení. Hodnota chyby však bude #NV zobrazeno, jedná se o chybu aplikace. Tuto chybovou zprávu aplikace Excel lze zachytit pomocí jiných vzorců.

Použijte odkaz jako referenční funkci aplikace Excel a prohledávejte řádky

Na rozdíl od VLOOKUP, HLOOKUP hledá horizontálně. Tato referenční funkce aplikace Excel proto prohledává sloupec po sloupci v definovaném řádku podle kritéria vyhledávání v listu. Funkce HLOOKUP má následující vlastnosti:

• 3 povinné argumenty (Kritérium vyhledávání; Matice; Řádkový index)
• 1 volitelný argument (Area_reference)
• Specifické vyhledávání v tabulce úrovní
• Použití zástupných znaků jako vyhledávacích kritérií

Hledat ve všech směrech umožňuje XVreference

Microsoft 365 má vylepšenou referenční funkci Excel XLOOKUP, která kombinuje funkce VLOOKUP a HLOOKUP. Pomocí vzorce lze v listu hledat vyhledávací kritérium bez ohledu na to, kde se nachází sloupec s výsledky. Tato referenční funkce je se 6 argumenty o něco složitější, ale umožňuje uživatelům Excelu podrobné a dynamické vyhledávání ve všech směrech.

Přehled dalších maticových vzorců pro hodnocení tabulky

Je pro hodnocení dat relevantní zohlednění velkých a malých písmen? V této záležitosti může pomoci složitější vzorec pole, který referenční funkce nemohou provádět. Referenční funkce aplikace Excel nejsou jediným způsobem dotazování na informace ve sloupcích nebo řádcích, které má program ve svém repertoáru. Funkce index může například vydávat hodnoty nebo odkazy z buněk. Požadovaná data lze načíst zadáním proměnné polohy buňky nebo pomocí čísel sloupců a řádků.Pomocí funkce porovnání mohou uživatelé Excelu také extrahovat hodnoty z průsečíků řádků a sloupců. Rozsahy buněk lze také použít k určení poslední položky v seznamu aplikace Excel.

Další funkce Excelu, které mohou pomoci s organizací a vyhodnocením informací:

• Row () je funkce aplikace Excel, kterou můžete použít k nastavení čísel řádků v listu. Seznamy aplikace Excel můžete například číslovat v krocích po dvou. Tento vzorec může být dokonce vložen do jiného vzorce a může automaticky pokračovat v číslování řádků, jakmile prázdná buňka obdrží novou hodnotu.
• Indirect () vám pomůže rozdělit a uspořádat víceřádková data do sloupců nebo přečíst obsah prostřednictvím adres buněk.
• Adresa () je další maticový vzorec, který můžete použít k určení adresy buňky konkrétní hodnoty. Integrováním dalších funkcí můžete funkci rozšířit a například určit pozici největšího čísla v seznamu aplikace Excel.
• V kombinaci s jinými vzorci může Hyperlink () nejen vygenerovat přesnou polohu hodnoty, ale současně uložit odkaz na pozici.

Referenční funkce aplikace Excel umožňuje rychle vyhledávat informace

Bez ohledu na to, zda mají být data určena z konkrétního sloupce, prohledávaných řádků nebo má být vygenerována výsledná hodnota pro přidruženou vyhledávací hodnotu, lze referenční funkce v aplikaci Excel použít mnoha způsoby. Uživatelé si mohou vybrat mezi referenční funkcí Excelu, která je kompatibilní s jinými tabulkovými programy, nebo rozsáhlejšími funkcemi Vreference nebo Wreference. Pro pokročilé uživatele je XVerweis vhodný pro cílenější vyhledávání, které kombinuje funkce SVerweis a Wreweis. K organizaci dat lze použít další funkce Excelu, například automatické číslování řádků nebo vrácení pozice buňky hodnoty.