Jak vytvořit vyhledávací panel v excelu. Najděte libovolné slovo ze seznamu v buňce. Funkce Searchpos v Excelu - příklad funkce excel searchpos se zástupnými znaky

Řekněme, že potřebujete najít telefonní linku zaměstnance pomocí jeho čísla a také správně odhadnout poměr provizí pro částku prodeje. Vyhledáváte data, abyste rychle a efektivně našli konkrétní data v seznamu a automaticky kontrolovali, zda jsou data používána správně. Po zobrazení dat můžete provádět výpočty a zobrazovat výsledky s uvedením návratových hodnot. Existuje několik způsobů, jak vyhledávat hodnoty v seznamu dat a zobrazovat výsledky.

V tomto článku

Najděte hodnoty v seznamu vertikálně podle přesné shody

K provedení tohoto úkolu můžete použít funkci VLOOKUP nebo kombinaci funkcí INDEX a MATCH.

Příklady použití funkce VLOOKUP

funkce VLOOKUP.

Příklady INDEXů a MATCHES

Co to znamená:

=INDEX(musíte vrátit hodnotu z C2:C10, která bude odpovídat MATCH(první hodnota "Cabbage" v poli B2:B10))

Vzorec hledá první hodnotu v buňce C2:C10 odpovídající zelí(v B7) a vrátí hodnotu v C7 ( 100 ) - první odpovídající hodnota zelí.

Další informace naleznete v části Funkce INDEX a funkce MATCH.

Najděte hodnoty v seznamu svisle podle přibližné shody

K tomu použijte funkci VLOOKUP.

Důležité:

Ve výše uvedeném příkladu funkce SVYHLEDAT hledá jméno studenta, který má 6 zpoždění v rozsahu A2:B7. V tabulce není žádný záznam pro 6 opožděně, takže funkce SVYHLEDAT hledá další nejvyšší shodu pod 6 a najde hodnotu 5 spojenou s křestním jménem Dave, a proto se vrací Dave.

Další informace naleznete v části Funkce VLOOKUP.

Hledání vertikálních hodnot v seznamu neznámé velikosti s přesnou shodou

K provedení tohoto úkolu použijte funkce OFFSET a MATCH.

Poznámka: Tento přístup se používá, pokud jsou data v externím datovém rozsahu, který aktualizujete každý den. Víte, že sloupec B obsahuje Cena, ale nevíte, kolik řádků dat server vrátí, a první sloupec není řazen abecedně.

C1 je levá horní buňka rozsahu (nazývaná také počáteční buňka).

Shoda("pomeranče"; C2: C7; 0) hledá oranžovou barvu v rozsahu C2:C7. Do rozsahu byste neměli zahrnout počáteční buňku.

1 - počet sloupců napravo od počáteční buňky, pro které má být vrácena vrácená hodnota. V našem příkladu je vrácená hodnota ve sloupci D, Odbyt.

Najděte hodnoty v seznamu vodorovně podle přesné shody

K provedení tohoto úkolu se používá funkce GLOOKUP. Níže je uveden příklad.

Funkce VYHLEDAT prohledává sloupec Odbyt a vrátí hodnotu z řádku 5 v určeném rozsahu.

Další informace naleznete v části Funkce VYHLEDÁVÁNÍ.

Hledání hodnot v seznamu vodorovně pomocí přibližné shody

K provedení tohoto úkolu se používá funkce GLOOKUP.

Důležité: Ujistěte se, že hodnoty v prvním řádku jsou seřazeny ve vzestupném pořadí.

Ve výše uvedeném příkladu funkce GLOOKUP hledá hodnotu 11000 v řádku 3 v zadaném rozsahu. Nenajde 11000, a proto hledá následující nejvyšší hodnotu což je menší než 1100 a vrátí číslo 10543.

Další informace naleznete v části Funkce VYHLEDÁVÁNÍ.

Vytvořte vyhledávací vzorec pomocí Průvodce vyhledáváním (pouze Excel 2007)

Poznámka: Doplněk Průvodce vyhledáváním byl ukončen v aplikaci Excel 2010. Tyto funkčnost nahrazeno průvodcem funkcí a dostupnými vyhledávacími a referenčními (referenčními) funkcemi.

V aplikaci Excel 2007 vytvoří Průvodce vyhledáváním vyhledávací vzorec založený na datech listu, která mají záhlaví řádků a sloupců. Průvodce vyhledáváním vám pomůže najít další hodnoty v řádku, když znáte hodnotu v jednom sloupci, a naopak. Průvodce vyhledáváním používá index a MATCH ve vzorcích, které vytváří.

Datum: 3. ledna 2016 Kategorie:

Hledání znaků v řetězci je nejdůležitější úkol, který umožňuje automatizovat práci s textem. Usnadňuje to například, když je neznámá poloha cílového vedení.

Dotkl jsem se vyhledávacího nástroje Excel v , ale zde se podíváme na funkce, které vyhledávají text v řetězci a vracejí jeho výchozí pozici.

Chcete-li vyhledat text v Microsoft Excel jsou dvě funkce:

NALÉZT() . Tato funkce má 2 povinné argumenty a jeden volitelný:
- Hledat text– posloupnost znaků, které je třeba najít. Toto je povinný argument
- Původní text– řetězec, ve kterém se provádí vyhledávání. Povinný argument
- Začáteční pozice– pořadové číslo znaku ve zdrojovém textu, od kterého začíná vyhledávání. Volitelný argument. Pokud není zadán, vyhledávání začíná od prvního znaku

Funkce FIND bere v úvahu A nepodporuje použití zástupných znaků(přečtěte si o nich dále).

Funkce FIND v Excelu

VYHLEDÁVÁNÍ(Vyhledat text; Původní text; [počáteční pozice]) má podobnou syntaxi a argumenty. V tom se liší nerozlišuje malá a velká písmena A podporuje použití zástupných znaků.

Funkce HLEDAT v Excelu

Jak vidíte, tato funkce poskytla jiný výsledek, protože vyhledávání nerozlišovalo velká a malá písmena.

Co jsou to zástupné znaky, ptáte se? Jedná se o speciální znaky, které umožňují přeskočit neznámou část textu pro vyhledávání:

? – libovolný symbol
* – libovolná posloupnost znaků

Pokud je otazník nebo hvězdička součástí hledaného řetězce a není zástupným znakem, uveďte před něj vlnovku (~).

Zapišme si následující vzorec: =SEARCH(“??chn”,A1,1). Funkce vyhledá řetězec libovolných dvou znaků následovaný písmeny „chn“. Vyhledávání odpovídá části slova „Tabular“ v ukázkovém textu, tzn. kombinace znaků "osobní". Výsledkem funkce bude hodnota "4".

Zástupné znaky v Excelu

Nyní napíšeme vzorec =SEARCH(“p*s”,A2,1) . Funkce hledá kombinaci „p“ + libovolný počet libovolných znaků + „s“. V příkladu je to část slova „procesor“. Výsledek funkce je 11.

Zástupný znak hvězdička v Excelu

Funkce vyhledávání vám usnadní sledování polohy požadovaného textu, takže je snazší jej nahradit. Určitě si jejich používání nacvičte. Můj post-tutorial o textových funkcích vám s tím pomůže.

A tomu bude věnován další článek. Důležité téma, které je rozhodně potřeba nastudovat!

Zeptejte se v komentářích a přihlaste se k odběru aktualizací, aby vám nic neuniklo!

Tento článek popisuje syntaxi vzorce a použití funkcí VYHLEDÁVÁNÍ A VYHLEDÁVÁNÍ v aplikaci Microsoft Excel.

Popis

Funkce VYHLEDÁVÁNÍ A VYHLEDÁVÁNÍ najít jeden textový řetězec v druhém a vrátit počáteční pozici prvního textového řetězce (počítáno od prvního znaku druhého textového řetězce). Chcete-li například najít pozici písmene „n“ ve slově „tiskárna“, můžete použít následující funkci:

HLEDAT("n","tiskárna")

Tato funkce se vrací 4 , protože "n" je čtvrtý znak ve slově "tiskárna".

Můžete také najít slova jinými slovy. Například funkce

HLEDAT("báze","databáze")

se vrací 5 , protože slovo „databáze“ začíná pátým znakem slova „databáze“. Lze použít funkce VYHLEDÁVÁNÍ A VYHLEDÁVÁNÍ určit pozici znaku nebo textového řetězce v jiném textovém řetězci a poté vrátit text pomocí funkcí PSTR A PSTRB nebo jej nahradit funkcemi NAHRADIT A NAHRADIT. Tyto funkce jsou uvedeny v tomto článku.

Důležité:

Tyto funkce nemusí být dostupné ve všech jazycích.

Funkce SEARCH počítá dva bajty na znak pouze v případě, že výchozí jazyk je jazyk s povoleným BDC. Jinak funkce SEARCH funguje stejně jako funkce SEARCH a počítá jeden bajt na znak.

Mezi jazyky, které podporují BDCS, patří japonština, zjednodušená čínština, tradiční čínština a korejština.

Syntax

HLEDAT(hledaný_text; zobrazený_text; [počáteční_pozice])

SEARCHB(hledaný_text; zobrazený_text; [počáteční_pozice])

Argumenty funkcí SEARCH a SEARCHB jsou popsány níže.

Hledat_text Požadované. Text, který chcete najít.

Zobrazený_text Požadované. Text, ve kterém se má najít hodnota argumentu hledaný_text .

Start_position Volitelný. Číslo znaku v argumentu zobrazený_text , kde byste měli začít hledat.

Komentář

Funkce VYHLEDÁVÁNÍ A VYHLEDÁVÁNÍ nerozlišují velká a malá písmena. Pokud potřebujete rozlišovat malá a velká písmena, použijte funkce NALÉZT A NOC.

V argumentaci hledaný_text Můžete použít zástupné znaky: otazník ( ? ) a hvězdičkou ( * ). Otazník odpovídá libovolnému znaku, hvězdička odpovídá libovolné posloupnosti znaků. Pokud chcete najít otazník nebo hvězdičku, napište vlnovku ( ~ ).

Pokud je hodnota argumentu hledaný_text nenalezeno, #HODNOTA! vrácena chybová hodnota.

Pokud argument počáteční_pozice je vynechán, pak je nastaven na 1.

Li Start_position ne více než 0 nebo větší než délka argumentu zobrazený_text , #HODNOTA! vrácena chybová hodnota.

Argument počáteční_pozice lze použít k přeskočení určitého počtu znaků. Předpokládejme, že funkce VYHLEDÁVÁNÍ by měl být použit pro práci s textovým řetězcem "MDS0093.Pánské oblečení". Chcete-li najít první výskyt "M" v popisné části textového řetězce, nastavte argument na počáteční_pozice hodnotu 8, aby se vyhledávání neprovádělo v té části textu, která je sériové číslo(v tomto případě - "MDS0093"). Funkce VYHLEDÁVÁNÍ spustí vyhledávání od osmého znaku, najde znak uvedený v argumentu hledaný_text , na další pozici a vrátí číslo 9. Funkce VYHLEDÁVÁNÍ vždy vrátí číslo znaku, počítá se od začátku prohlíženého textu , včetně znaků, které jsou přeskočeny, pokud je hodnota argumentu počáteční_pozice více než 1.

Příklady

Zkopírujte ukázková data z následující tabulky a vložte je do buňky A1 nového listu aplikace Excel. Chcete-li zobrazit výsledky vzorců, vyberte je a stiskněte F2 a poté stiskněte Enter. V případě potřeby změňte šířku sloupců, abyste viděli všechna data.

Data

Výnosy: marže

Tady "šéfe".
Vzorec	Popis	Výsledek
HLEDAT("a";A2;6)	Pozice prvního "a" v řádku buňky A2 počínaje šestým znakem.
HLEDAT(A4;A3)	Počáteční pozice řádku "marže" (hledaný řádek je v buňce A4) v řádku "Příjmy: marže" (hledaná buňka je A3).
REPLACE(A3;HLEDAT(A4;A3);6;"svazek")	Nahradí slovo „okraj“ slovem „svazek“ vyhledáním slova „okraj“ v buňce A3 a nahrazením tohoto znaku a následujících pěti znaků textovým řetězcem „objem“.	Výnosy: objem
PSTR(A3;HLEDAT(" ";A3)+1,4)	Vrátí první čtyři znaky, které následují za první mezerou v řádku Příjmy: Marže (buňka A3).
HLEDAT("""";A5)	Umístěte první dvojité uvozovky(") v buňce A5.
PSTR(A5;HLEDAT("""";A5)+1;HLEDAT("""";A5;HLEDAT("""";A5)+1)-HLEDAT("""";A5)-1)	Vrátí pouze text z buňky A5 uzavřený do dvojitých uvozovek.

V tomto článku

Najděte hodnoty v seznamu vertikálně podle přesné shody

K provedení tohoto úkolu můžete použít funkci VLOOKUP nebo kombinaci funkcí INDEX a MATCH.

Příklady použití funkce VLOOKUP

funkce VLOOKUP.

Příklady INDEXů a MATCHES

Co to znamená:

=INDEX(musíte vrátit hodnotu z C2:C10, která bude odpovídat MATCH(první hodnota "Cabbage" v poli B2:B10))

Vzorec hledá první hodnotu v buňce C2:C10 odpovídající zelí(v B7) a vrátí hodnotu v C7 ( 100 ) - první odpovídající hodnota zelí.

Další informace naleznete v části Funkce INDEX a funkce MATCH.

Najděte hodnoty v seznamu svisle podle přibližné shody

K tomu použijte funkci VLOOKUP.

Důležité:

Další informace naleznete v části Funkce VLOOKUP.

Hledání vertikálních hodnot v seznamu neznámé velikosti s přesnou shodou

K provedení tohoto úkolu použijte funkce OFFSET a MATCH.

C1 je levá horní buňka rozsahu (nazývaná také počáteční buňka).

Shoda("pomeranče"; C2: C7; 0) hledá oranžovou barvu v rozsahu C2:C7. Do rozsahu byste neměli zahrnout počáteční buňku.

1 - počet sloupců napravo od počáteční buňky, pro které má být vrácena vrácená hodnota. V našem příkladu je vrácená hodnota ve sloupci D, Odbyt.

Najděte hodnoty v seznamu vodorovně podle přesné shody

K provedení tohoto úkolu se používá funkce GLOOKUP. Níže je uveden příklad.

Funkce VYHLEDAT prohledává sloupec Odbyt a vrátí hodnotu z řádku 5 v určeném rozsahu.

Další informace naleznete v části Funkce VYHLEDÁVÁNÍ.

Hledání hodnot v seznamu vodorovně pomocí přibližné shody

K provedení tohoto úkolu se používá funkce GLOOKUP.

Důležité: Ujistěte se, že hodnoty v prvním řádku jsou seřazeny ve vzestupném pořadí.

Ve výše uvedeném příkladu funkce GLOOKUP hledá hodnotu 11000 v řádku 3 v zadaném rozsahu. Nenajde 11000, a proto hledá další největší hodnotu, která je menší než 1100, a vrátí číslo 10543.

Další informace naleznete v části Funkce VYHLEDÁVÁNÍ.

Vytvořte vyhledávací vzorec pomocí Průvodce vyhledáváním (pouze Excel 2007)

Poznámka: Doplněk Průvodce vyhledáváním byl v Excelu 2010 ukončen. Tato funkce byla nahrazena Průvodcem funkcí a dostupnými funkcemi vyhledávání a odkazů (referenční).

Všichni nejlepší přátelé! A dnes se dozvíte, jak najít to správné slovo v Excelu. Práce s Excelem často zahrnuje značné množství informací. V takových souborech jsou vytvořeny skutečně obrovské tabulky s tisíci řádků, sloupců a také pozic. Najít nějaké konkrétní hodnoty v tak obrovském poli může být někdy neuvěřitelně obtížné.

Tento úkol lze zjednodušit. Vědět, jak najít požadované slovo, můžete snadno procházet souborem. Editor tabulek nabízí několik variant vyhledávání.

Jak najít správné slovo podle buněk v Excelu

Chcete-li zobrazit adresy těch buněk, které obsahují to, co se snažíte najít, postupujte takto:

Pokud jste uživatelem programu 2010, měli byste přejít do nabídky, poté kliknout na „Upravit“ a poté na „Najít“.
Dále se otevře okno, ve kterém musíte zadat hledaný výraz.
Program předchozí verze má toto tlačítko v nabídce nazvané „Domů“, umístěné na panelu úprav.
Podobného výsledku lze dosáhnout v kterékoli z verzí současným použitím tlačítek Ctrl a F.
Do pole byste měli napsat frázi, hledaná slova nebo čísla.
Kliknutím na „Najít vše“ spustíte vyhledávání v celém souboru. Kliknutím na „Další“ je program vybere jednu buňku po druhé, která se nachází pod kurzorem buňky souboru.
Vyplatí se počkat, až bude proces dokončen. Navíc, čím větší je dokument, tím více času zabere hledání.
Zobrazí se seznam výsledků: názvy a adresy buněk, které obsahují shodu se zadanou hodnotou nebo frází.
Kliknutím na libovolný řádek se příslušná buňka zvýrazní.
Pro větší pohodlí můžete okno „roztáhnout“. Tímto způsobem bude vidět více čar.
Chcete-li data seřadit, musíte kliknout na názvy sloupců nad nalezenými výsledky. Kliknutím na „List“ se řádky seřadí abecedně podle názvu listu a výběrem „Hodnoty“ se seřadí v závislosti na hodnotě. Mimochodem, tyto sloupy lze také „natáhnout“.

Parametry vyhledávání

Je možné si nastavit vlastní podmínky. Hledejte například podle několika znaků. Zde je návod, jak najít slovo, které si úplně nepamatujete:

Měli byste zadat pouze část nápisu. Můžete dokonce použít jedno z písmen - budou označeny všechny oblasti, kde je k dispozici.
Použijte ikony hvězdičky a otazníku. Jsou schopni nahradit chybějící znaky.
Otázka ukazuje na jednu chybějící pozici. Pokud například zadáte „Huh????“, zobrazí se buňky obsahující pětimístné slovo začínající na „A“.
Díky hvězdičce lze nahradit libovolný počet znaků. Chcete-li najít všechny hodnoty obsahující kořen „rast“, měli byste začít hledat podle klíče „rast*“.

Kromě toho můžete navštívit nastavení:

Ve vyhledávacím poli klikněte na „Možnosti“.
V sekcích oblasti zobrazení a vyhledávání budete muset přesně uvést, kde a na základě jakých kritérií chcete hledat shody. Můžete si vybrat vzorce, různé poznámky nebo hodnoty.
Chcete-li rozlišovat mezi malými a velkými písmeny, musíte zaškrtnout políčko s názvem Rozlišovat malá a velká písmena.
Zaškrtnutím políčka „Úplná buňka“ se výsledky zobrazí ve formě buněk obsahujících výhradně zadané hledaná fráze a nic než ona.

Nastavení formátování buněk

Chcete-li hledat hodnoty s konkrétní výplní nebo stylem, měli byste použít nastavení. Pokud tedy slovo vypadá jinak než zbytek textu, musíte provést následující:

Ve vyhledávacím poli klikněte na parametry a stiskněte tlačítko „Formátovat“. Otevře se nabídka obsahující několik záložek.
Můžete určit konkrétní písmo, typ rámečku, barvu pozadí a také formát vstupních dat. Systém zobrazí ty oblasti, které splňují zadaná kritéria.
Chcete-li převzít informace z aktuální buňky (zvýrazněno tento moment), klikněte na „Použít formát této buňky“. V tomto případě program najde všechny hodnoty, které mají stejnou velikost a typ znaků, stejnou barvu, stejně jako stejné hranice atd.

Jak najít více slov v Excelu

V souborech aplikace Excel můžete vyhledávat buňky pomocí celých výrazů. Zadáním klíče, například „Červené koule“, však systém provede specifickou práci tento požadavek. Výsledky nebudou ukazovat hodnoty, které vypadají jako „Red Sparkling Ball“ nebo „Red Shiny Ball“.

Chcete-li hledat ne jedno, ale několik slov současně, musíte provést následující:

Zadejte je do vyhledávacího pole.
Umístěte mezi ně hvězdy. Tímto způsobem budou nalezeny hodnoty, které obsahují zadaný nápis, bez ohledu na to, zda jsou mezi nimi určité znaky nebo ne.
Pomocí této metody můžete zadat klíč i se samostatnými písmeny.

Použití filtru

Chcete-li tedy použít filtr, musíte:

Vyberte konkrétní buňku obsahující data.
Klikněte na hlavní stránku, poté – „Řazení“ a poté – „Filtr“.
V řádku nahoře budou buňky opatřeny šipkami. Toto je nabídka, kterou je třeba otevřít.
Musíte zadat požadavek do textového pole a kliknout na potvrzení.
Ve sloupci se zobrazí pouze buňky. Ve kterém je přítomna hledaná fráze.
Chcete-li obnovit výsledky, vyberte v rozevíracím seznamu možnost „Vybrat vše“.
Chcete-li filtr zakázat, klikněte na něj znovu v řazení.

Uvedená metoda pravděpodobně nebude užitečná, pokud nemáte informace, ve kterém řádku se nachází hledaná hodnota.

To je pro dnešek vše, nyní víte, jak najít správné slovo v Excelu. Chcete-li tedy vyhledat konkrétní frázi nebo číslo, měli byste použít funkce zabudované do rozhraní. Je také možné si vybrat další možnosti vyhledat nebo použít filtr.