Ako vytvoriť vyhľadávací panel v programe Excel. Nájdite ľubovoľné slovo zo zoznamu v bunke. Funkcia Searchpos v Exceli - príklad funkcie excel searchpos so zástupnými znakmi

Povedzme, že potrebujete nájsť telefónne číslo zamestnanca pomocou jeho čísla a tiež správne odhadnúť pomer provízií k sume predaja. Údaje vyhľadávate, aby ste rýchlo a efektívne našli konkrétne údaje v zozname a automaticky skontrolovali, či sa údaje používajú správne. Po zobrazení údajov môžete vykonávať výpočty a zobraziť výsledky s uvedením návratových hodnôt. Existuje niekoľko spôsobov, ako vyhľadať hodnoty v zozname údajov a zobraziť výsledky.

V tomto článku

Nájdite hodnoty v zozname vertikálne podľa presnej zhody

Na vykonanie tejto úlohy môžete použiť funkciu VLOOKUP alebo kombináciu funkcií INDEX a MATCH.

Príklady použitia funkcie VLOOKUP

Funkcia VLOOKUP.

Príklady INDEXov a MATCHES

Čo to znamená:

=INDEX(musíte vrátiť hodnotu z C2:C10, ktorá sa bude zhodovať s MATCH(prvá hodnota "Cabbage" v poli B2:B10))

Vzorec hľadá prvú hodnotu v bunke C2:C10, ktorá zodpovedá kapusta(v B7) a vráti hodnotu v C7 ( 100 ) - prvá zodpovedajúca hodnota kapusta.

Ďalšie informácie nájdete v časti Funkcia INDEX a funkcia MATCH.

Nájdite hodnoty v zozname vertikálne podľa približnej zhody

Ak to chcete urobiť, použite funkciu VLOOKUP.

Dôležité:

Vo vyššie uvedenom príklade funkcia VLOOKUP hľadá meno študenta, ktorý má 6 oneskorení v rozsahu A2:B7. V tabuľke nie je žiadny záznam pre 6 oneskorene, takže funkcia VLOOKUP hľadá najbližšiu najvyššiu zhodu pod 6 a nájde hodnotu 5 spojenú s krstným menom Dave, a preto sa vracia Dave.

Ďalšie informácie nájdete v časti Funkcia VLOOKUP.

Nájdenie vertikálnych hodnôt v zozname neznámej veľkosti s presnou zhodou

Na vykonanie tejto úlohy použite funkcie OFFSET a MATCH.

Poznámka: Tento prístup sa používa, ak sú údaje v externom rozsahu údajov, ktorý aktualizujete každý deň. Viete, že stĺpec B obsahuje Cena, ale neviete, koľko riadkov údajov server vráti a prvý stĺpec nie je zoradený podľa abecedy.

C1 je ľavá horná bunka rozsahu (nazývaná aj počiatočná bunka).

Zhoda("pomaranče"; C2: C7; 0) hľadá oranžovú farbu v rozsahu C2:C7. Do rozsahu by ste nemali zahrnúť počiatočnú bunku.

1 - počet stĺpcov napravo od počiatočnej bunky, pre ktoré sa má vrátiť vrátená hodnota. V našom príklade je návratová hodnota v stĺpci D, Predaj.

Nájdite hodnoty v zozname vodorovne podľa presnej zhody

Na vykonanie tejto úlohy sa používa funkcia GLOOKUP. Nižšie je uvedený príklad.

Funkcia VYHĽADAŤ vyhľadáva stĺpec Predaj a vráti hodnotu z riadku 5 v zadanom rozsahu.

Ďalšie informácie nájdete v časti Funkcie VYHĽADÁVANIA.

Vyhľadanie hodnôt v zozname vodorovne pomocou približnej zhody

Na vykonanie tejto úlohy sa používa funkcia GLOOKUP.

Dôležité: Uistite sa, že hodnoty v prvom riadku sú zoradené vzostupne.

Vo vyššie uvedenom príklade funkcia GLOOKUP hľadá hodnotu 11000 v riadku 3 v zadanom rozsahu. Nenájde 11 000, a preto hľadá nasledujúce najvyššia hodnotačo je menej ako 1100 a vráti číslo 10543.

Ďalšie informácie nájdete v časti Funkcie VYHĽADÁVANIA.

Vytvorte vyhľadávací vzorec pomocou Sprievodcu vyhľadávaním (iba Excel 2007)

Poznámka: Doplnok Sprievodca vyhľadávaním bol v Exceli 2010 ukončený funkčnosť nahradený sprievodcom funkciou a dostupnými vyhľadávacími a referenčnými (referenčnými) funkciami.

V Exceli 2007 vytvorí Sprievodca vyhľadávaním vzorec na vyhľadávanie založený na údajoch pracovného hárka, ktorý obsahuje nadpisy riadkov a stĺpcov. Sprievodca vyhľadávaním vám pomôže nájsť ďalšie hodnoty v riadku, keď poznáte hodnotu v jednom stĺpci, a naopak. Sprievodca vyhľadávaním používa index a MATCH vo vzorcoch, ktoré vytvára.

Dátum: 3. januára 2016 Kategória:

Vyhľadávanie znakov v reťazci je najdôležitejšou úlohou, ktorá umožňuje automatizovať prácu s textom. Uľahčuje to napríklad, keď je neznáma poloha cieľovej čiary.

Dotkol som sa vyhľadávacieho nástroja Excel v , ale tu sa pozrieme na funkcie, ktoré vyhľadávajú text v reťazci a vracajú jeho počiatočnú pozíciu.

Ak chcete vyhľadať text v Microsoft Excel existujú dve funkcie:

NÁJSŤ() . Táto funkcia má 2 povinné argumenty a jeden voliteľný:
- Vyhľadajte text– poradie znakov, ktoré je potrebné nájsť. Toto je povinný argument
- Pôvodný text– reťazec, v ktorom sa vyhľadávanie vykonáva. Požadovaný argument
- Východisková pozícia– poradové číslo znaku v zdrojovom texte, od ktorého vyhľadávanie začína. Voliteľný argument. Ak nie je zadaný, vyhľadávanie začne od prvého znaku

Funkcia NÁJSŤ berie do úvahy A nepodporuje použitie zástupných znakov(prečítajte si o nich ďalej).

Funkcia NÁJSŤ v Exceli

VYHĽADÁVANIE(Hľadaný text; Pôvodný text; [Počiatočná pozícia]) má podobnú syntax a argumenty. V tom sa líši nerozlišuje veľké a malé písmená A podporuje používanie zástupných znakov.

Funkcia HĽADAŤ v Exceli

Ako vidíte, táto funkcia poskytla iný výsledok, pretože vyhľadávanie nerozlišovalo veľké a malé písmená.

Čo sú to zástupné znaky, pýtate sa? Toto sú špeciálne znaky, ktoré vám umožňujú preskočiť neznámu časť textu na vyhľadávanie:

? - ľubovoľný symbol
* – ľubovoľná postupnosť znakov

Ak je otáznik alebo hviezdička súčasťou hľadaného reťazca a nie zástupný znak, uveďte pred ním vlnovku (~).

Zapíšme si nasledujúci vzorec: =SEARCH(“??chn”,A1,1). Funkcia vyhľadá reťazec dvoch ľubovoľných znakov, za ktorým nasledujú písmená „chn“. Vyhľadávanie sa zhoduje s časťou slova „Tabuľkový“ vo vzorovom texte, t.j. kombinácia znakov „osobné“. Výsledkom funkcie bude hodnota „4“.

Zástupné znaky v Exceli

Teraz napíšme vzorec =SEARCH(“p*s”,A2,1) . Funkcia hľadá kombináciu „p“ + ľubovoľný počet ľubovoľných znakov + „s“. V príklade je to časť slova „procesor“. Výsledok funkcie je 11.

Zástupný znak hviezdičky v Exceli

Funkcie vyhľadávania vám uľahčia sledovanie polohy požadovaného textu, takže je jednoduchšie ho nahradiť. Určite si ich nacvičte. Môj post-tutorial o textových funkciách vám s tým pomôže.

A ďalší článok bude venovaný. Dôležitá téma, ktorú si určite treba naštudovať!

Opýtajte sa na otázky v komentároch a prihláste sa na odber aktualizácií, aby vám nič neušlo!

Tento článok popisuje syntax vzorca a použitie funkcií VYHĽADÁVANIE A VYHĽADÁVANIE v programe Microsoft Excel.

Popis

Funkcie VYHĽADÁVANIE A VYHĽADÁVANIE nájsť jeden textový reťazec v druhom a vrátiť počiatočnú pozíciu prvého textového reťazca (počítajúc od prvého znaku druhého textového reťazca). Ak chcete napríklad nájsť polohu písmena „n“ v slove „tlačiareň“, môžete použiť nasledujúcu funkciu:

HĽADAŤ("n","tlačiareň")

Táto funkcia sa vráti 4 , keďže „n“ je štvrtý znak v slove „tlačiareň“.

Slová môžete nájsť aj inými slovami. Napríklad funkcia

HĽADAŤ("základňa","databáza")

sa vracia 5 , keďže slovo „základňa“ sa začína piatym znakom slova „databáza“. Je možné použiť funkcie VYHĽADÁVANIE A VYHĽADÁVANIE určiť polohu znaku alebo textového reťazca v inom textovom reťazci a potom vrátiť text pomocou funkcií PSTR A PSTRB alebo ho nahradiť funkciami NAHRADIŤ A NAHRADIŤ. Tieto funkcie sú uvedené v tomto článku.

Dôležité:

Tieto funkcie nemusia byť dostupné vo všetkých jazykoch.

Funkcia HĽADAŤ počíta dva bajty na znak iba vtedy, ak je predvolený jazyk jazyk s povoleným BDC. V opačnom prípade funkcia SEARCH funguje rovnako ako funkcia SEARCH a počíta jeden bajt na znak.

Medzi jazyky, ktoré podporujú BDCS, patrí japončina, zjednodušená čínština, tradičná čínština a kórejčina.

Syntax

HĽADAŤ(hľadaný_text; zobrazený_text; [počiatočná_pozícia])

SEARCHB(hľadaný_text; zobrazený_text; [počiatočná_pozícia])

Argumenty funkcií SEARCH a SEARCHB sú popísané nižšie.

Hľadaný_text Požadovaný. Text, ktorý chcete nájsť.

Zobrazený_text Požadovaný. Text, v ktorom nájdete hodnotu argumentu vyhľadávací_text .

Počiatočná_pozícia Voliteľné. Číslo znaku v argumente zobrazený_text , kde by ste mali začať hľadať.

Komentujte

Funkcie VYHĽADÁVANIE A VYHĽADÁVANIE nerozlišujú veľké a malé písmená. Ak potrebujete rozlišovať veľké a malé písmená, použite funkcie NÁJSŤ A NIGHTIB.

V argumentácii vyhľadávací_text Môžete použiť zástupné znaky: otáznik ( ? ) a hviezdičkou ( * ). Otáznik zodpovedá ľubovoľnému znaku, hviezdička ľubovoľnej sekvencii znakov. Ak chcete nájsť otáznik alebo hviezdičku, napíšte vlnovku ( ~ ).

Ak je hodnota argumentu vyhľadávací_text nenájdené, #HODNOTA! vrátená chybová hodnota.

Ak argument počiatočná_pozícia sa vynechá, potom sa nastaví na 1.

Ak Počiatočná_pozícia nie viac ako 0 alebo viac ako dĺžka argumentu zobrazený_text , #HODNOTA! vrátená chybová hodnota.

Argument počiatočná_pozícia možno použiť na preskočenie určitého počtu znakov. Predpokladajme, že funkcia VYHĽADÁVANIE musí byť použitý na prácu s textovým reťazcom "MDS0093.Pánske oblečenie". Ak chcete nájsť prvý výskyt "M" v popisnej časti textového reťazca, nastavte argument na počiatočná_pozícia hodnotu 8, aby sa vyhľadávanie nevykonalo v tej časti textu, ktorá je sériové číslo(v tomto prípade - "MDS0093"). Funkcia VYHĽADÁVANIE spustí vyhľadávanie od ôsmeho znaku, nájde znak uvedený v argumente vyhľadávací_text , na ďalšej pozícii a vráti číslo 9. Funkcia VYHĽADÁVANIE vždy vráti číslo znaku, počítajúc od začiatku prezeraného textu , vrátane znakov, ktoré sa preskočia, ak je hodnota argumentu počiatočná_pozícia viac ako 1.

Príklady

Skopírujte vzorové údaje z nasledujúcej tabuľky a prilepte ich do bunky A1 nového excelového hárka. Ak chcete zobraziť výsledky vzorcov, vyberte ich a stlačte F2 a potom stlačte Enter. V prípade potreby zmeňte šírku stĺpcov, aby ste videli všetky údaje.

Údaje

Príjmy: marža

Tu je "šéf".
Vzorec	Popis	Výsledok
HĽADAŤ("a";A2;6)	Pozícia prvého „a“ v riadku bunky A2 počnúc šiestym znakom.
HĽADAŤ(A4;A3)	Počiatočná pozícia riadku "marža" (hľadaný riadok je v bunke A4) v riadku "Výnosy: marža" (hľadaná bunka je A3).
REPLACE(A3;HĽADAŤ(A4;A3);6;"zväzok")	Nahradí slovo „okraj“ slovom „objem“ tak, že v bunke A3 nájde slovo „okraj“ a nahradí tento znak a nasledujúcich päť znakov textovým reťazcom „objem“.	Výnosy: objem
PSTR(A3;HĽADAŤ(" ";A3)+1,4)	Vráti prvé štyri znaky, ktoré nasledujú po prvej medzere v riadku Výnosy: Marža (bunka A3).
HĽADAŤ("""";A5)	Pozícia ako prvá dvojité úvodzovky(") v bunke A5.
PSTR(A5;HĽADAŤ("""";A5)+1;HĽADAŤ("""";A5;HĽADAŤ("""";A5)+1)-HĽADAŤ("""";A5)-1)	Vráti iba text v úvodzovkách z bunky A5.

V tomto článku

Nájdite hodnoty v zozname vertikálne podľa presnej zhody

Na vykonanie tejto úlohy môžete použiť funkciu VLOOKUP alebo kombináciu funkcií INDEX a MATCH.

Príklady použitia funkcie VLOOKUP

Funkcia VLOOKUP.

Príklady INDEXov a MATCHES

Čo to znamená:

=INDEX(musíte vrátiť hodnotu z C2:C10, ktorá sa bude zhodovať s MATCH(prvá hodnota "Cabbage" v poli B2:B10))

Vzorec hľadá prvú hodnotu v bunke C2:C10, ktorá zodpovedá kapusta(v B7) a vráti hodnotu v C7 ( 100 ) - prvá zodpovedajúca hodnota kapusta.

Ďalšie informácie nájdete v časti Funkcia INDEX a funkcia MATCH.

Nájdite hodnoty v zozname vertikálne podľa približnej zhody

Ak to chcete urobiť, použite funkciu VLOOKUP.

Dôležité:

Ďalšie informácie nájdete v časti Funkcia VLOOKUP.

Nájdenie vertikálnych hodnôt v zozname neznámej veľkosti s presnou zhodou

Na vykonanie tejto úlohy použite funkcie OFFSET a MATCH.

C1 je ľavá horná bunka rozsahu (nazývaná aj počiatočná bunka).

Zhoda("pomaranče"; C2: C7; 0) hľadá oranžovú farbu v rozsahu C2:C7. Do rozsahu by ste nemali zahrnúť počiatočnú bunku.

1 - počet stĺpcov napravo od počiatočnej bunky, pre ktoré sa má vrátiť vrátená hodnota. V našom príklade je návratová hodnota v stĺpci D, Predaj.

Nájdite hodnoty v zozname vodorovne podľa presnej zhody

Na vykonanie tejto úlohy sa používa funkcia GLOOKUP. Nižšie je uvedený príklad.

Funkcia VYHĽADAŤ vyhľadáva stĺpec Predaj a vráti hodnotu z riadku 5 v zadanom rozsahu.

Ďalšie informácie nájdete v časti Funkcie VYHĽADÁVANIA.

Vyhľadanie hodnôt v zozname vodorovne pomocou približnej zhody

Na vykonanie tejto úlohy sa používa funkcia GLOOKUP.

Dôležité: Uistite sa, že hodnoty v prvom riadku sú zoradené vzostupne.

Vo vyššie uvedenom príklade funkcia GLOOKUP hľadá hodnotu 11000 v riadku 3 v zadanom rozsahu. Nenájde 11000, a preto hľadá ďalšiu najväčšiu hodnotu, ktorá je menšia ako 1100 a vráti číslo 10543.

Ďalšie informácie nájdete v časti Funkcie VYHĽADÁVANIA.

Vytvorte vyhľadávací vzorec pomocou Sprievodcu vyhľadávaním (iba Excel 2007)

Poznámka: Doplnok Sprievodca vyhľadávaním bol v Exceli 2010 ukončený. Táto funkcia bola nahradená funkciou Sprievodca funkciami a dostupnou funkciou vyhľadávania a odkazov (referenčných).

Všetci najlepší priatelia! A dnes sa dozviete, ako nájsť správne slovo v Exceli. Práca s Excelom často zahŕňa značné množstvo informácií. V takýchto súboroch sa vytvárajú skutočne obrovské tabuľky s tisíckami riadkov, stĺpcov a tiež pozícií. Nájdenie akýchkoľvek konkrétnych hodnôt v takom obrovskom poli môže byť niekedy neuveriteľne ťažké.

Táto úloha sa dá zjednodušiť. Keď viete, ako nájsť požadované slovo, môžete ľahko prechádzať súborom. Tabuľkový editor ponúka niekoľko variácií vyhľadávania.

Ako nájsť správne slovo podľa buniek v Exceli

Ak chcete zobraziť adresy tých buniek, ktoré obsahujú to, čo sa pokúšate nájsť, postupujte takto:

Ak ste používateľom programu 2010, mali by ste prejsť do ponuky, potom kliknúť na „Upraviť“ a potom na „Nájsť“.
Potom sa otvorí okno, v ktorom musíte zadať hľadaný výraz.
Program predošlá verzia má toto tlačidlo v ponuke s názvom „Domov“, ktorá sa nachádza na paneli úprav.
Podobný výsledok je možné dosiahnuť v ktorejkoľvek z verzií súčasným použitím tlačidiel Ctrl a F.
Do poľa by ste mali zadať frázu, hľadané slová alebo čísla.
Kliknutím na „Nájsť všetko“ spustíte vyhľadávanie v celom súbore. Kliknutím na „Ďalej“ ich program vyberie po jednej bunke umiestnenej pod kurzorom bunky súboru.
Stojí za to počkať, kým sa proces nedokončí. Navyše, čím väčší je dokument, tým viac času zaberie vyhľadávanie.
Zobrazí sa zoznam výsledkov: názvy a adresy buniek, ktoré obsahujú zhody so zadanou hodnotou alebo frázou.
Kliknutím na ľubovoľný riadok sa príslušná bunka zvýrazní.
Pre pohodlie môžete okno „roztiahnuť“. Týmto spôsobom bude viditeľných viac riadkov.
Ak chcete údaje zoradiť, musíte kliknúť na názvy stĺpcov nad nájdenými výsledkami. Kliknutím na „Hárok“ sa riadky zoradia abecedne v závislosti od názvu hárku a výberom „Hodnoty“ sa zoradia v závislosti od hodnoty. Mimochodom, tieto stĺpce môžu byť tiež „natiahnuté“.

Parametre vyhľadávania

Je možné nastaviť si vlastné podmienky. Hľadajte napríklad podľa viacerých znakov. Tu je návod, ako nájsť slovo, ktoré si úplne nepamätáte:

Mali by ste zadať iba časť nápisu. Môžete dokonca použiť jedno z písmen - budú označené všetky oblasti, kde je k dispozícii.
Použite ikony hviezdičky a otáznika. Sú schopní nahradiť chýbajúce znaky.
Otázka označuje jednu chýbajúcu pozíciu. Ak napríklad zadáte „Huh????“, zobrazia sa bunky, ktoré obsahujú päťznakové slovo, ktoré začína na „A“.
Vďaka hviezdičke je možné nahradiť ľubovoľný počet znakov. Ak chcete nájsť všetky hodnoty obsahujúce koreň „rast“, mali by ste začať hľadať podľa kľúča „rast*“.

Okrem toho môžete navštíviť nastavenia:

Vo vyhľadávacom poli kliknite na „Možnosti“.
V sekciách oblasti zobrazenia a vyhľadávania budete musieť presne uviesť, kde a na základe akých kritérií chcete hľadať zhody. Môžete si vybrať vzorce, rôzne poznámky alebo hodnoty.
Ak chcete rozlišovať medzi malými a veľkými písmenami, musíte začiarknuť políčko s názvom Rozlišovať malé a veľké písmená.
Zaškrtnutím políčka „Úplná bunka“ sa výsledky zobrazia vo forme buniek obsahujúcich výlučne zadané hľadaná fráza a nič okrem nej.

Nastavenia formátovania buniek

Ak chcete hľadať hodnoty s konkrétnou výplňou alebo štýlom, mali by ste použiť nastavenia. Ak teda slovo vyzerá inak ako zvyšok textu, musíte urobiť nasledovné:

Vo vyhľadávacom poli kliknite na parametre a stlačte tlačidlo „Formátovať“. Otvorí sa ponuka obsahujúca niekoľko kariet.
Môžete určiť konkrétne písmo, typ rámu, farbu pozadia, ako aj formát vstupných údajov. Systém zobrazí tie oblasti, ktoré spĺňajú zadané kritériá.
Ak chcete prevziať informácie z aktuálnej bunky (zvýraznená tento moment), kliknite na „Použiť formát tejto bunky“. V tomto prípade program nájde všetky hodnoty, ktoré majú rovnakú veľkosť a typ znakov, rovnakú farbu, ako aj rovnaké hranice atď.

Ako nájsť viac slov v Exceli

V súboroch programu Excel môžete vyhľadávať bunky pomocou celých výrazov. Zadaním kľúča, napríklad „Červená guľa“, však systém vykoná prácu špecificky túto žiadosť. Výsledky nezobrazia hodnoty, ktoré vyzerajú ako „Červená šumivá gulička“ alebo „Červená lesklá gulička“.

Ak chcete hľadať nie jedno, ale niekoľko slov súčasne, musíte urobiť nasledovné:

Zadajte ich do vyhľadávacieho panela.
Umiestnite medzi ne hviezdy. Týmto spôsobom sa nájdu hodnoty, ktoré obsahujú zadaný nápis, bez ohľadu na to, či sú medzi nimi určité znaky alebo nie.
Pomocou tejto metódy môžete zadať kľúč aj so samostatnými písmenami.

Použitie filtra

Ak teda chcete použiť filter, musíte:

Vyberte konkrétnu bunku obsahujúcu údaje.
Kliknite na hlavnú stránku, potom – „Sorting“ a potom – „Filter“.
V riadku v hornej časti budú bunky vybavené šípkami. Toto je ponuka, ktorú je potrebné otvoriť.
Do textového poľa musíte zadať požiadavku a kliknúť na potvrdenie.
V stĺpci sa zobrazia iba bunky. V ktorej sa nachádza hľadaná fráza.
Ak chcete obnoviť výsledky, v rozbaľovacom zozname vyberte možnosť „Vybrať všetko“.
Ak chcete filter zakázať, znova naň kliknite v triedení.

Predložená metóda pravdepodobne nebude užitočná, ak nemáte informácie, v ktorom riadku sa nachádza hľadaná hodnota.

To je na dnes všetko, teraz viete, ako nájsť správne slovo v Exceli. Ak teda chcete vyhľadať konkrétnu frázu alebo číslo, mali by ste použiť funkcie zabudované do rozhrania. Je možné si aj vybrať ďalšie možnosti vyhľadajte alebo použite filter.