Obsah
10. den maratonu 30 funkcí Excelu za 30 dní budeme věnovat studiu funkce HLOOKUP (GPR). Tato funkce je velmi podobná VLOOKUP (VLOOKUP), funguje pouze s prvky vodorovného seznamu.
Nešťastná funkce HLOOKUP (GLOW) není tak populární jako jeho sestra, protože ve většině případů jsou data v tabulkách uspořádána svisle. Pamatujete si, kdy jste naposledy chtěli hledat řetězec? Co takhle vrátit hodnotu ze stejného sloupce, ale umístěného v jednom z řádků níže?
Každopádně dejme funkce HLOOKUP (GPR) zasloužený okamžik slávy a podívejte se blíže na informace o této funkci a také na příklady jejího použití. Nezapomeňte, že pokud máte zajímavé nápady nebo příklady, podělte se o ně v komentářích.
Funkce 10: HLOOKUP
funkce HLOOKUP (HLOOKUP) vyhledá hodnotu v prvním řádku tabulky a vrátí jinou hodnotu ze stejného sloupce v tabulce.
Jak mohu použít funkci HLOOKUP (HLOOKUP)?
Od funkce HLOOKUP (HLOOKUP) dokáže najít přesnou nebo přibližnou hodnotu v řetězci, pak může:
- Najděte celkové prodeje pro vybraný region.
- Najděte indikátor, který je relevantní pro vybrané datum.
Syntaxe HLOOKUP
funkce HLOOKUP (HLOOKUP) má následující syntaxi:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- lookup_value (lookup_value): Hodnota, která má být nalezena. Může to být hodnota nebo odkaz na buňku.
- table_array (tabulka): vyhledávací tabulka. Může to být odkaz na rozsah nebo pojmenovaný rozsah obsahující 2 nebo více řádků.
- řádek_index_num (číslo_řádku): Řetězec obsahující hodnotu, kterou má funkce vrátit. Nastavte podle čísla řádku v tabulce.
- range_lookup (range_lookup): Použijte FALSE nebo 0 k nalezení přesné shody; pro přibližné hledání TRUE (TRUE) nebo 1. V druhém případě musí být řetězec, ve kterém funkce hledá, seřazen vzestupně.
Pasti HLOOKUP (GPR)
Like VLOOKUP (VLOOKUP), funkce HLOOKUP (HLOOKUP) může být pomalé, zejména při hledání přesné shody textového řetězce v nesetříděné tabulce. Kdykoli je to možné, použijte přibližné vyhledávání v tabulce seřazené podle prvního řádku ve vzestupném pořadí. Nejprve můžete použít funkci MATCH (VÍCE EXPOZOVANĚ) popř COUNTIF (COUNTIF), abyste se ujistili, že hledaná hodnota v prvním řádku vůbec existuje.
Další funkce jako INDEX (INDEX) a MATCH (MATCH) lze také použít k načtení hodnot z tabulky a jsou efektivnější. Podíváme se na ně později v našem maratonu a uvidíme, jak silné a flexibilní mohou být.
Příklad 1: Najděte hodnoty prodeje pro vybranou oblast
Znovu připomínám, že funkce HLOOKUP (HLOOKUP) hledá pouze hodnotu v horním řádku tabulky. V tomto příkladu najdeme celkové tržby za vybranou oblast. Je pro nás důležité získat správnou hodnotu, proto používáme následující nastavení:
- Název regionu se zadává do buňky B7.
- Regionální vyhledávací tabulka má dva řádky a pokrývá rozsah C2:F3.
- Celkové tržby jsou na řádku 2 naší tabulky.
- Poslední argument je nastaven na FALSE pro nalezení přesné shody při vyhledávání.
Vzorec v buňce C7 je:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Pokud není název regionu v prvním řádku tabulky, výsledek funkce HLOOKUP (GPR) bude #AT (#N/A).
Příklad 2: Najděte míru pro vybrané datum
Obvykle při použití funkce HLOOKUP (HLOOKUP) vyžaduje přesnou shodu, ale někdy je přibližná shoda vhodnější. Pokud se například ukazatele změní na začátku každého čtvrtletí a první dny těchto čtvrtletí se použijí jako záhlaví sloupců (viz obrázek níže). V tomto případě pomocí funkce HLOOKUP (HLOOKUP) a přibližnou shodu, najdete ukazatel, který je relevantní pro dané datum. V tomto příkladu:
- Datum se zapisuje do buňky C5.
- Vyhledávací tabulka indikátorů má dva řádky a nachází se v rozsahu C2:F3.
- Vyhledávací tabulka je řazena vzestupně podle řádku data.
- Ukazatele jsou zaznamenány na řádku 2 naší tabulky.
- Poslední argument funkce je nastaven na TRUE, aby se hledala přibližná shoda.
Vzorec v buňce D5 je:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Pokud datum není nalezeno v prvním řádku tabulky, funkce HLOOKUP (HLOOKUP) najde nejbližší největší hodnotu, která je menší než argument lookup_value (hledaná_hodnota). V tomto příkladu je požadovaná hodnota Březen 15. Není na řádku data, takže vzorec převezme hodnotu 1 ledna a zpět 0,25.
