Najděte hodnotu v rozsahu buněk aplikace Excel. Jak používat funkci vyhledávání v excelu

Je poměrně obtížné objevit informace, které potřebujete, v listu s velkým množstvím dat. Dialogové okno Najít a nahradit však hledání informací mnohem usnadňuje. Kromě toho má některé užitečné funkce, o kterých mnoho uživatelů neví.

Spusťte příkaz Domácí úpravy Najděte a zvýrazněte Najít(nebo klikněte Ctrl+F) otevřete dialogové okno Najít a nahradit. Pokud potřebujete nahradit data, vyberte příkaz Domácí úpravy Najděte a zvýrazněte Nahradit(nebo klikněte Ctrl+H). Přesný příkaz, který provedete, určuje, na které ze dvou karet se dialogové okno otevře.

Pokud v dialogovém okně, které se otevře Nalézt a stiskněte tlačítko pro výměnu Možnosti, poté se zobrazí další možnosti vyhledávání informací (obr. 21.1).

V mnoha případech je lepší zadat při vyhledávání spíše přibližný než přesný text, například držáky na klíče na zeď. Chcete-li například najít údaje o klientovi Ivanu Smirnovovi, můžete samozřejmě zadat přesný text do vyhledávacího pole. Je však pravděpodobné, že nic nenajdete, jelikož jste jméno klienta zadali jinak, jako Ivan Smirnov nebo I. Smirnov, nebo se dokonce v příjmení spletli. Při hledání takového názvu je nejlepší použít zástupné znaky.

Vstupte iv*smir* v terénu Nalézt a potom klikněte na tlačítko Najít všechny. Použití zástupných znaků nejen snižuje počet slov, která zadáváte, ale také zajišťuje, že najdete údaje o zákaznících, pokud na listu existují. Výsledky vyhledávání mohou samozřejmě obsahovat položky, které nesplňují účel vašeho vyhledávání, ale je to lepší než nic.

Při vyhledávání pomocí dialogového okna Najít a nahradit lze použít dva zástupné znaky:

? - odpovídá libovolné postavě;
* - odpovídá libovolnému počtu znaků.

Tyto zástupné znaky lze navíc použít také při hledání číselných hodnot. Pokud například zadáte do vyhledávacího pole 3* , výsledek zobrazí všechny buňky, které obsahují hodnotu začínající 3, a pokud zadáte 1?9, dostanete všechny trojmístné položky, které začínají 1 a končí 9.

Chcete-li vyhledat otazník nebo hvězdičku, vložte před něj vlnovku (~).
Například následující vyhledávací řetězec najde text *NONE*: -*N0NE~*
Chcete-li najít znak vlnovky, vložte do vyhledávacího pole dvě vlnovky.

Pokud si myslíte, že vaše vyhledávání nefunguje správně, zkontrolujte následující tři nastavení (která se někdy sama od sebe změní).

Zaškrtávací políčko Shodný případ- nastavte jej tak, aby se velikost písmen hledaného textu shodovala s velkými a malými písmeny zadaného textu. Pokud například zadáte do vyhledávání slovo Ivan a zaškrtnete zadané políčko, slovo Ivan se ve výsledcích vyhledávání nezobrazí.
Zaškrtávací políčko Celá buňka- nastavte jej tak, aby našel buňku, která obsahuje přesně ten text, který je zadán ve vyhledávacím řádku. Pokud například do vyhledávacího pole zadáte Excel a zaškrtnete políčko, nenajdete buňku obsahující frázi Microsoft Excel.
Rozbalovací seznam Oblast hledání- seznam obsahuje tři položky: hodnoty, vzorce a poznámky. Pokud například zadáte číslo 900 do vyhledávacího pole a do rozevíracího seznamu Oblast hledání Pokud vyberete položku hodnoty, neuvidíte ve výsledcích hledání buňku obsahující hodnotu 900, pokud byla získána pomocí vzorce.

Pamatujte, že vyhledávání se provádí ve vybraném rozsahu buněk. Pokud potřebujete prohledat celý list, vyberte před zahájením vyhledávání pouze jednu buňku.

Všimněte si také, že pomocí okna Najít a nahradit formátované číselné hodnoty nelze nalézt. Pokud například zadáte do vyhledávacího pole $5* , hodnota, která má použité formátování měny a vypadá jako 54,00 $, nebude nalezena.

Práce s daty může být náročná, protože Excel podporuje tolik formátů data. Pokud hledáte datum, které má výchozí formátování, Excel najde data, i když jsou formátována různými způsoby. Pokud například váš systém používá formát data m/d/y, vyhledávací řetězec 10/*/2010 najde všechna data v říjnu 2010 bez ohledu na jejich formátování.

Použijte prázdné pole Nahrazen rychle odstranit některé informace z listu. Do pole zadejte například - * Nalézt a opustit hřiště Nahradit na prázdno. Poté klikněte na tlačítko Vyměnit vše aby Excel našel a odstranil všechny hvězdičky v listu.

Mám obrovské množství dat a často musím hledat například podle názvu produktu, abych viděl určité vlastnosti. Použití běžného vyhledávání je nepohodlné, protože tabulka má mnoho sloupců a často se musí dvakrát kontrolovat. Dá se to nějak zautomatizovat?

To lze provést pomocí funkce VLOOKUP nebo GLOOKUP. VLOOKUP se používá, pokud se požadovaná hodnota nachází ve sloupci. GPR - pokud je v řadě. Ale pořadí a princip fungování funkcí jsou stejné. Tyto funkce lze použít, pokud jsou data jedinečná (tj. neopakují se).

Vzhledem k tomu, že se předpokládá, že existuje mnoho údajů, můžete vytvořit rozbalovací seznam, který zjednoduší proces zadávání požadovaných údajů a zabrání chybám. Chcete-li to provést, umístěte kurzor do buňky, která bude polem se seznamem (můžete to provést na prázdném listu, který neobsahuje žádná data, což v budoucnu použijeme k zobrazení výsledků vyhledávání). Na kartě Data skupiny Práce s údaji klikněte na tlačítko Kontrola dat vyberte položku z rozevíracího seznamu Kontrola dat…

V dialogovém okně Zkouška na kartě Možnosti v terénu Datový typ Vybrat Seznam. V poli, které se objeví Zdroj Označujeme odkaz na oblast buněk, která obsahuje název zboží. Klepněte na tlačítko OK.

Měli byste skončit s rozevíracím seznamem takto:

Nyní přistoupíme přímo k vytvoření funkce pro vyhledání požadovaných hodnot. Tabulku, ve které bude vyhledávání probíhat, je nutné nejprve seřadit podle požadované hodnoty vzestupně. V našem příkladu podle značky telefonu.

Umístěte kurzor myši do řádku vedle vytvořeného rozevíracího seznamu nebo na jiné místo, kde chcete zobrazit výsledky vyhledávání. Pomocí karty nebo tlačítka Vzorce Funkce vložení na Tyče vzorce vyberte funkci VLOOKUP.

Stůl- vyberte tabulku s údaji tak, aby požadovaný argument byl v prvním sloupci, a stiskněte tlačítko F4 nebo ručně zadejte znaky $, aby se odkaz stal absolutním. Vyberte tabulku bez záhlaví, pouze data.

Číslo_sloupce- uveďte pořadové číslo sloupce, jehož údaje chceme vidět jako výsledek vyhledávání.

Time-lapse_view- FALSE - protože potřebujeme přesnou shodu údajů uvedených ve vyhledávání.

Klepněte na tlačítko OK.

Měnící se Číslo sloupce Ve vytvořené funkci VLOOKUP můžete ze zdrojové tabulky vybrat několik hodnot, které budou splňovat vaše podmínky vyhledávání.

Je možné provést vyhledávání podobné vyhledávání pomocí funkce VLOOKUP, ale bez třídění dat? V tomto případě VLOOKUP vytvoří chybu 9.

Můžete najít libovolnou hodnotu v řádku, pokud znáte hodnotu, která je v jednom sloupci, pomocí dvou funkcí SEARCH a INDEX.

Jako příklad si uveďme tabulku, která obsahuje názvy značek telefonů a jejich ceny.

Pro zjednodušení hledání požadovaných hodnot si můžete vytvořit rozbalovací seznam, který se bude skládat z údajů, které pro vyhledávání zadáme (například název produktu). Chcete-li to provést, umístěte kurzor do volné buňky a přejděte na kartu Data skupina Práce s daty a stiskněte tlačítko Kontrola dat, z rozevíracího seznamu vyberte položku Kontrola dat… V dialogovém okně Validace zadaných hodnot na kartě Možnosti v terénu Datový typ Vybrat Seznam a označte rozsah, který obsahuje data k vyhledávání.

Poté pomocí tabulátoru umístěte kurzor do volné buňky Vzorce nebo tlačítka Funkce vložení na Tyče vzorce vyberte funkci SEARCH.

Search_value- označíme buňku, která obsahuje rozbalovací seznam, který jsme vytvořili na samém začátku.

View_array- vyberte rozsah buněk, které obsahují buňky, které hledáte. V našem příkladu je sloupec se značkami telefonů, ale musíte jej zvýraznit bez záhlaví.

Typ_shody- zadejte "0" (nula) - to umožní funkci najít první argument, který odpovídá požadované hodnotě.

Klepněte na tlačítko OK.

Funkce MATCH indikuje, který řádek obsahuje požadovanou hodnotu. Umístěním kurzoru na buňku, která obsahuje vzorec, uvidíte syntaxi funkce v řádku vzorců, vyberte ji až po znaménko "=" a zkopírujte ji (stisknutím Ctrl+C) a stisknutím klávesy Enter ukončete buňka.

Poté umístěte kurzor do volné buňky, ve které se zobrazí výsledky, a pomocí tabulátoru Vzorce nebo tlačítka Funkce vložení na Tyče vzorce vyberte funkci INDEX. Tato funkce obsahuje dva typy argumentů, o kterých budete okamžitě informováni, potřebujeme „pole číslo_sloupce;

V dialogovém okně Funkční argumenty uveďte následující hodnoty:

Pole- vybereme celé datové pole (bez záhlaví tabulky), které obsahuje jak požadované hodnoty, tak data, která následně chceme zobrazovat. V našem příkladu se jedná o sloupce, které obsahují značku telefonu a cenu.

Počet_dat- zde vložíme funkci MATCH stisknutím Ctrl+V.

Číslo_sloupce- zde je třeba zadat číslo sloupce v poli, jehož hodnota se zobrazí jako výsledek vyhledávání.

Klepněte na tlačítko OK.

Tento článek popisuje syntaxi funkce MATCH Excel a také jak funkci používat MATCH v Excelu s příklady vzorce

Tento článek také pojednává o kombinaci vzorců INDEX MATCH v Excelu.

V aplikaci Microsoft Excel existuje mnoho různých vyhledávacích funkcí, které vám mohou pomoci najít konkrétní hodnotu v řadě buněk a VYHLEDÁVÁNÍ- jeden z nich. HLEDAT v angličtině MATCH v podstatě identifikuje relativní polohu prvku v rozsahu buněk. Nicméně funkce MATCH Excel v kombinaci s dalšími funkcemi dokáže mnohem více.

Funkce MATCH v Excelu – syntaxe a použití

Funkce MATCH v Excelu vyhledá danou hodnotu v rozsahu buněk a vrátí relativní pozici této hodnoty.

Syntax MATCH vzorce jak následuje:

MATCH(hledaná_hodnota; vyhledávací_pole; [typ_shody])

Search_value(povinný argument) – hodnota, kterou chcete najít. Může to být číselná, textová nebo logická hodnota nebo odkaz na buňku.

View_array(povinný argument) – rozsah buněk k prohledání.

Typ_shody(volitelný argument) - určuje typ shody. Může to být jedna z následujících hodnot: 1, 0, -1.

1 nebo chybí (výchozí) - najde nejvyšší hodnotu v poli, které je menší nebo rovno hledané hodnotě. Vyhledávací pole musí být seřazeny ve vzestupném pořadí, od nejmenšího k největšímu nebo od A do Z.
0 - najde první hodnotu v poli, která přesně stejné hledaná hodnota. Není nutné žádné třídění.
-1 - nálezy nejmenší hodnotu v poli, které je větší nebo rovno hledané hodnotě. Vyhledávací pole musí být seřazeno v sestupném pořadí, od největšího po nejmenší nebo od Z po A.

Pro lepší pochopení této funkce si napíšeme jednoduchý MATCH vzorec, na základě následujících údajů: jména studentů ve sloupci A a jejich výsledky ze zkoušek ve sloupci B, seřazené od nejvyšší po nejnižší. Chcete-li zjistit, kde se konkrétní student (například studentka Vinogradova) nachází mezi ostatními, použijte tento jednoduchý vzorec:

MATCH(E1; A2:A8; 0)

Volitelně můžete umístit vyhledávací hodnotu do buňky (v tomto příkladu E1) a odkazovat na tuto buňku ve vzorci Excel MATCH:

Funkce MATCH v Excelu - Příklad použití funkce MATCH v Excelu

Jak můžete vidět na obrázku výše, příjmení studentů se zadávají v náhodném pořadí, takže argument nastavíme match_type rovná 0 (přesná shoda), protože toto je jediný typ shody, který nevyžaduje řazení hodnot ve vyhledávacím poli. Technicky vzato vzorec MATCH vrací relativní pozici studentky Vinogradové v rozsahu zájmu. Ale protože jsou známky seřazeny od nejvyšší po nejnižší, také nám to říká, že Vinogradová je pátá mezi všemi studenty.

Vlastnosti funkce Excel MATCH

Jak jste právě viděli, použití MATCH v Excelu je snadné. Jako každá funkce Excelu však existuje několik zvláštností, o kterých byste si měli být vědomi:

Vrátí se funkce MATCH relativní pozice hledanou hodnotu v poli, nikoli samotnou hodnotu.
VYHLEDÁVÁNÍ nerozlišuje malá a velká písmena, což znamená, že při práci s textovými hodnotami nerozlišuje mezi malými a velkými písmeny.
Pokud vyhledávací pole obsahuje více výskytů hledané hodnoty, vrátí se pozice první hodnoty.
Pokud hledaná hodnota není v prohledávaném poli nalezena, vrátí se chyba #N/A.

Použití MATCH v Excelu – příklad vzorců

Nyní, když znáte základní funkce MATCH funkce v Excelu, podívejme se na několik dalších příkladů vzorců, které jdou nad rámec základů.

Vzorec Excel MATCH se zástupnými znaky

Stejně jako mnoho dalších funkcí Excelu, VYHLEDÁVÁNÍ rozpoznává následující zástupné znaky:

Otazník(?) - nahrazuje libovolný znak
Hvězdička (*) - nahrazuje libovolnou sekvenci znaků

Poznámka: Zástupné znaky lze použít pouze v MATCH vzorce s parametrem match_type rovna 0.

MATCH vzorec se zástupnými znaky je užitečné v situacích, kdy nechcete prohledávat celý textový řetězec, ale pouze některé znaky nebo část řetězce. Chcete-li to ilustrovat, zvažte následující příklad.

Řekněme, že máte seznam společností a jejich prodejní údaje za poslední týden. Chcete najít relativní pozici určité společnosti v seznamu (seřazeném podle prodejů v sestupném pořadí), ale nepamatujete si přesně její název, přestože si pamatujete prvních pár písmen.

Za předpokladu, že názvy společností jsou v rozsahu A2:A8 a hledáte název začínající na „východ“, vzorec je následující:

MATCH("východ*"; A2:A8; 0)

Aby naše MATCH vzorec Všestrannější je, že můžete zadat hledanou hodnotu do libovolné buňky (v tomto příkladu E1) a do této buňky se zástupným znakem, jako je tento:

MATCH(E1&"*"; A2:A8; 0)

Jak je znázorněno na obrázku níže, vzorec vrací pozici 2, která odpovídá společnosti Vostok-auto:

Funkce Excel MATCH - Příklad funkce Excel MATCH se zástupnými znaky

Chcete-li nahradit pouze jeden znak ve vyhledávací hodnotě, použijte zástupný operátor "?", například:

MATCH("kupující"; A2:A8;0)

Výše uvedený vzorec bude odpovídat názvu „Baker“ a vrátí jeho relativní pozici, která je 5.

Vzorec MATCH s rozlišením malých a velkých písmen

Jak bylo zmíněno na začátku tohoto článku, Funkce Excel MATCH nerozlišuje velká a malá písmena. Chcete-li vytvořit vzorec pro párování rozlišující malá a velká písmena, použijte VYHLEDÁVÁNÍ v kombinaci s funkcí MATCH, která přesně porovnává buňky, včetně velkých a malých písmen.

Rozlišování malých a velkých písmen vzorce pro párování dat v Excelu je následující:

MATCH(TRUE,MATCH(hledaná_hodnota,looked_array); 0)

Logika tohoto vzorce je následující:

Funkce MATCH porovnává hledanou hodnotu s každým prvkem pole, který prohlíží. Pokud jsou porovnávané buňky přesně stejné, funkce vrátí hodnotu TRUE nebo FALSE.
Dále funkce MATCH porovná TRUE (což je její vyhledávací_hodnota) s každou hodnotou v poli vrácenou funkcí MATCH a vrátí pozici první shody.

Poznámkaže se jedná o maticový vzorec, který ke správnému zpracování vyžaduje stisknutí Ctrl+Shift+Enter.

Nechť je hledaná hodnota v buňce E1 a vyhledávací pole A2:A8, potom je vzorec následující:

MATCH(PRAVDA; MATCH(A2:A8; E1);0)

Následující obrázek ukazuje MATCH vzorec rozlišovat malá a velká písmena v Excelu:

Funkce MATCH v Excelu - Příklad funkce Excel MATCH rozlišuje velká a malá písmena

Porovnejte 2 sloupce pro shody a rozdíly (END a MATCH)

Testování dvou seznamů na podobnosti a rozdíly je jedním z nejběžnějších úkolů v Excelu a lze jej provádět různými způsoby. Použití vzorce UNM/MATCH je jedním z nich:

IF(END(MATCH (1. hodnota v seznamu 1; Seznam 2; 0)); "Není v seznamu 1"; "")

Pro jakoukoli hodnotu v seznamu 2, která není v seznamu 1, vzorec vrátí "Není v seznamu 1." A takhle se to stane:

Funkce MATCH hledá hodnotu ze seznamu 1 v seznamu 2. Pokud je hodnota nalezena, vrátí relativní pozici, jinak chybu #N/A.
Funkce UND v Excelu dělá pouze jednu věc: kontroluje chyby #N/A (což znamená „není k dispozici“). Pokud je zadaná hodnota chyba #N/A, funkce vrátí hodnotu TRUE, jinak FALSE. V našem případě TRUE znamená, že hodnota ze seznamu 1 nebyla nalezena v seznamu 2 (MATCH vrací chybu #N/A).
Vzhledem k tomu, že výsledky TRUE/FALSE budou pro ostatní uživatele obtížně interpretovatelné, použijeme funkci IF k zobrazení textu „Není v seznamu 1“ pro hodnoty, které nejsou v seznamu 1.

Předpokládejme, že potřebujeme porovnat hodnoty ve sloupci B s hodnotami ve sloupci A, vzorec bude mít následující tvar (kde B2 je nejvyšší buňka):

IF(END(MATCH(B2,A:A,0));"Není v seznamu 1"; "")

Jak si možná vzpomínáte, samotná funkce MATCH aplikace Excel nerozlišuje velká a malá písmena. Chcete-li rozlišovat malá a velká písmena, vložte funkci MATCH do argumentu vyhledávacího pole a nezapomeňte stisknout Ctrl+Shift+Enter, abyste dokončili tento maticový vzorec:

IF(END(MATCH(TRUE, MATCH(A:A,B2); 0));"Není v seznamu 1"; "")

Následující obrázek ukazuje oba vzorce v akci:

Funkce MATCH v Excelu - Porovnejte 2 sloupce pro shody pomocí UNM a MATCH

INDEX a MATCH v Excelu

S funkcí MATCH Excel už se známe. Pojďme se nyní podívat, jak lze kombinaci využít INDEX MATCH v Excelu. Začněme syntaxí funkce INDEX.

Syntaxe a použití funkce INDEX

Funkce INDEX Excel vrátí hodnotu v poli na základě zadaných řádků a sloupců. Syntaxe funkce INDEX je jednoduchá:

INDEX(pole; číslo_řádku; [číslo_sloupce])

Zde je velmi jednoduché vysvětlení každé možnosti:

pole je rozsah buněk, ze kterých chcete vrátit hodnotu.
číslo_řádku - číslo řádku v poli, ze kterého chcete vrátit hodnotu. Pokud je tento parametr vynechán, je vyžadováno číslo_sloupce.
číslo_sloupce - číslo sloupce v poli, ze kterého chcete vrátit hodnotu. Pokud je tento parametr vynechán, je vyžadován line_number.

Pokud je použito číslo_řádku i číslo_sloupce, funkce INDEX vrátí hodnotu v buňce v průsečíku zadaného řádku a sloupce.

Zde je jednoduchý příklad vzorce INDEX:

INDEX(A1:C10;2;3)

Vzorec prohledá buňky A1 až C10 a vrátí hodnotu buňky ve druhém řádku a třetím sloupci, což je buňka C2.

Velmi snadné, že? Při práci se skutečnými daty však pravděpodobně nebudete vědět, které řádky a sloupce potřebujete, takže potřebujete pomoc funkce VYHLEDÁVÁNÍ.

INDEX MATCH v příkladu Excelu

Nyní, když znáte syntaxi a základy těchto dvou funkcí, pravděpodobně již dobře rozumíte tomu, jak fungují. INDEX MATCH Excel.

funkce MATCH určuje relativní polohu hledané hodnoty v zadaném rozsahu buněk. A Funkce INDEX vezme toto číslo a vrátí hodnotu do odpovídající buňky.

kombinace INDEX MATCH v Excelu lze reprezentovat takto:

INDEX(sloupec pro návratovou hodnotu, MATCH(hledaná hodnota, sloupec pro vyhledávání, 0))

Pro lepší pochopení se podívejme na názorný příklad. Řekněme, že máme seznam zemí s tímto počtem obyvatel:

Funkce MATCH v Excelu – Zdrojová data pro vzorec MATCH INDEX v Excelu

Pomocí následujícího najdeme populaci například Ruska Vzorec INDEX MATCH:

INDEX($C$2:$C$11,MATCH("Rusko"$B$2:$B$11,0))

Nyní se podívejme na to, co každá složka tohoto vzorce skutečně dělá:

Funkce MATCH hledá hodnotu "Rusko" ve sloupci B, konkrétně v buňkách B2:B11, a vrátí číslo 7, protože "Rusko" je sedmé v seznamu.
Funkce INDEX převezme ve svém druhém parametru (číslo_řádku) číslici „7“, která určuje, ze kterého řádku chcete vrátit hodnotu, a změní se na jednoduchý vzorec =INDEX($C$2:$C$11, 7). To znamená, že vzorec prohledá buňky C2-C11 a vrátí hodnotu buňky v sedmém řádku, což je buňka C8, protože začínáme počítat od druhého řádku.

A zde je výsledek, který dostaneme v Excelu:

Funkce MATCH v Excelu – příklad vzorce INDEX MATCH

Poznámka! Počet řádků a sloupců v poli INDEX musí odpovídat hodnotám v parametrech row_number a/nebo column_number funkce MATCH. Jinak vzorec vrátí nesprávný výsledek.

Předpokládejme, že v závislosti na nich dostaneme měsíc, město a cenu produktu. Je potřeba najít odpovídající hodnotu ve velkém dvourozměrném poli/matici/tabulce a zobrazit ji v buňce pomocí vzorce. V podstatě potřebujeme vrátit hodnotu na průsečíku požadovaného řádku a sloupce tabulky. Nelze vyhledávat v dvourozměrném poli Excelu s hotovými funkcemi - SUMIF nebo jen VLOOKUP zde již nejsou vhodné. Používám konstrukci z funkcí VLOOKUP a MATCH. Jak to správně napsat, viz níže.

Design je zde zcela netriviální. Doporučuji proto, abyste se před přečtením článku níže seznámili s principy fungování funkce, zvláště pokud jste se s takovými funkcemi dosud nesetkali.

Aby to bylo jasnější, začněme příkladem.

Existuje tabulka podle města a náklady na služby závisí na měsíci.

Je nutné najít náklady na třetí měsíc ve městě Krasnodar nebo najít Sij, pokud jste chytří. Jak to uděláme?

Vyhledávejte ve dvourozměrném poli Excelu. Jak napsat vzorec

Jak jsme diskutovali, v Excelu neexistuje žádný hotový vzorec. Proto vytvoříme kombinaci dvou vzorců. V libovolné buňce napíšeme:

VLOOKUP($G:$G,$B:$E,MATCH($H:$H,$B$2:$E$2);)

Kde sloupec G je požadované město (Krasnodar) a sloupec H je měsíc (třetí). Při nahrávání můžete jednoduše použít jednu buňku, například G4 a H4

VLOOKUP($G$4,$B:$E,MATCH($H$4,$B$2:$E$2);)

Jak vidíte, vzorec na obrázku (pomocí sloupců) lze níže rozšířit do dalších buněk.

Popis designu formule:

Funkce VLOOKUP hledá hodnotu, kterou potřebujeme (Cities, modrý sloupec G) ve velké tabulce $B:$E (označené zeleně). K tomu musíme uvést, ve kterém sloupci této tabulky VPRu hledat město - druhý, třetí nebo čtvrtý (C, D, E)
Číslo tohoto sloupce zjistíme pomocí funkce MATCH, která zjistí číslo prvku v poli. Přesněji najdeme číslo hodnoty fialového sloupce H (měsíc) v poli $B$2:$E$2. Proto jsme určili, že SVYHLEDAT by měl používat třetí sloupec.
Čtvrtý argument necháme prázdný (za posledním „;“), protože výchozí může být ponecháno FALSE nebo prázdné. Přečtěte si více.

Poznámka! Pro správný výpočet vzorce musí být splněny dvě podmínky!

— Šířka pole $B$2:$E$2 v MATCH a šířka tabulky $B:$E se musí shodovat.

— Data v poli pro MATCH ($B$2:$E$2) musí být seřazena od největšího po nejmenší nebo od A do Z!

Microsoft Excel často pracuje s velkým množstvím informací. Vytváří obrovské tabulky s tisíci řádků, sloupců a pozic. V takovém poli může být obtížné najít nějaká konkrétní data. A někdy je to úplně nemožné. Tento úkol lze zjednodušit. Zjistěte, jak najít správné slovo v Excelu. Usnadní vám to orientaci v dokumentu. A můžete rychle přejít k informacím, které hledáte.

Chcete-li zobrazit adresy všech buněk, které obsahují to, co hledáte, postupujte takto:

Pokud máte Office 2010, přejděte do nabídky – Upravit – Najít.
Otevře se okno se vstupním polem. Napište do něj hledaný výraz.
V aplikaci Excel 2007 je toto tlačítko v nabídce Domů na panelu Úpravy. Je to vpravo.
Obdobného výsledku ve všech verzích lze dosáhnout stisknutím Ctrl+F.
Do pole zadejte slovo, frázi nebo čísla, které chcete najít.
Klepnutím na Najít vše prohledáte celý dokument. Pokud kliknete na „Další“, program postupně vybere buňky, které jsou umístěny pod kurzorem buňky Excel.
Počkejte na dokončení procesu. Čím větší dokument, tím déle bude systém hledat.
Zobrazí se seznam s výsledky: názvy a adresy buněk, ve kterých jsou shody se zadanou frází, a text, který je v nich napsán.
Když kliknete na každý řádek, zvýrazní se odpovídající buňka.
Pro větší pohodlí můžete okno „roztáhnout“. Tímto způsobem bude vidět více řádků.
Chcete-li data seřadit, klikněte na názvy sloupců nad výsledky vyhledávání. Pokud kliknete na „List“, budou řádky uspořádány abecedně v závislosti na názvu listu, pokud zvolíte „Hodnoty“, budou seřazeny podle hodnoty.
Tyto sloupy se také „natahují“.

Můžete si nastavit vlastní podmínky. Například spusťte vyhledávání pomocí několika znaků. Zde je návod, jak v Excelu najít slovo, které si úplně nepamatujete:

Zadejte pouze část titulku. Můžete napsat alespoň jedno písmeno – všechna místa, kde existuje, budou zvýrazněna.
Použijte symboly * (hvězdička) a ? (otazník). Nahrazují chybějící znaky.
Otázka ukazuje na jednu chybějící položku. Pokud napíšete například „P???“, objeví se buňky obsahující čtyřznakové slovo začínající na „P“: „Pluh“, „Pole“, „Pár“ a tak dále.
Hvězdička (*) může nahradit libovolný počet znaků. Chcete-li najít všechny hodnoty, které obsahují kořen „rast“, začněte hledat pomocí klíče „*rast*“.

Můžete také přejít do nastavení:

V okně Najít klikněte na Možnosti.
V sekcích „Procházet“ a „Oblast vyhledávání“ určete, kde a podle jakých kritérií hledat shody. Můžete vybrat vzorce, poznámky nebo hodnoty.
Chcete-li systému umožnit rozlišovat mezi malými a velkými písmeny, zaškrtněte políčko „Rozlišovat malá a velká písmena“.
Pokud zaškrtnete políčko „Celá buňka“, výsledky zobrazí buňky, které obsahují pouze zadanou hledanou frázi a nic jiného.

Možnosti formátu buňky

Chcete-li najít hodnoty s konkrétní výplní nebo stylem, použijte nastavení. Zde je návod, jak najít slovo v Excelu, pokud vypadá jinak než zbytek textu:

V okně vyhledávání klikněte na „Možnosti“ a klikněte na tlačítko „Formátovat“. Otevře se nabídka s několika kartami.
Můžete určit konkrétní písmo, typ rámečku, barvu pozadí, formát dat. Systém zobrazí místa, která odpovídají zadaným kritériím.
Chcete-li převzít informace z aktuální buňky (aktuálně zvýrazněné), klikněte na „Použít formát této buňky“. Poté program najde všechny hodnoty, které mají stejnou velikost a typ symbolů, stejnou barvu, stejné okraje a podobně.

Hledejte více slov

V Excelu můžete najít buňky podle celých frází. Ale pokud jste zadali klávesu „Blue Ball“, systém bude pracovat přesně na tento požadavek. Hodnoty s "Blue Crystal Ball" nebo "Blue Glitter Ball" se ve výsledcích nezobrazí.

Chcete-li v Excelu najít nejen jedno slovo, ale několik slov najednou, postupujte takto:

Napište je do vyhledávacího pole.
Umístěte mezi ně hvězdy. Výsledkem bude „*Text* *Text2* *Text3*“. Tím se najdou všechny hodnoty obsahující zadané nápisy. Bez ohledu na to, zda jsou mezi nimi nějaké postavy nebo ne.
Takto můžete zadat klíč i s jednotlivými písmeny.