Excel vyhledávání v další buňce. Jak najít text, slova a symboly v Excelu? Odstranění řádků pomocí vzoru. Změna proměnných buněk

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.

Práce s projektem VB (12)
Podmíněné formátování (5)
Seznamy a rozsahy (5)
Makra (procedury VBA) (63)
Různé (39)
Chyby a závady Excelu (3)

Najděte libovolné slovo ze seznamu v buňce

Předpokládejme, že obdržíte od dodavatele/zákazníka/klienta vyplněnou tabulku se seznamem produktů:

A tato tabulka musí být porovnána s čísly zboží/produktovými kódy ve vašem stávajícím katalogu produktů:

Jak vidíte, náš katalog obsahuje pouze články bez názvu. Zákazník má kromě čísel zboží k dispozici také název produktu, tzn. spoustu zbytečných věcí. A musíte pochopit, které produkty jsou ve vašem katalogu přítomny a které ne:


Pro takové vyhledávání a porovnávání neexistují v Excelu žádné standardní vzorce. Samozřejmě můžete zkusit použít SVYHLEDAT se zástupnými znaky nejprve na jednu tabulku a poté na druhou. Ale pokud je třeba takovou operaci dělat znovu a znovu, pak napsat několik vzorců pro každou tabulku, upřímně řečeno, není comme il faut.
Proto jsem se dnes rozhodl demonstrovat vzorec, který bez jakýchkoliv extra. k takovému srovnání pomohou manipulace. Abyste na to přišli sami, doporučuji stáhnout soubor:
Stáhnout soubor:

(49,5 kB, 13 249 stažení)


Na listu „Objednávka“ v tomto souboru je tabulka obdržená od zákazníka a na listu „Katalog“ jsou naše články.
Samotný vzorec s použitím ukázkového souboru bude vypadat takto:

BROWSE(2,1/HLEDAT(Adresář!$A$2:$A$11, A2);Adresář!$A$2:$A$11)
=LOOKUP(2,1/HLEDAT(Adresář!$A$2:$A$11,A2),Adresář!$A$2:$A$11)
tento vzorec vrátí název článku, pokud text obsahuje alespoň jeden článek z katalogu a #N/A (#N/A) pokud článek není v katalogu nalezen.
Než tento vzorec upřesníme nejrůznějšími doplňky (jako je odstranění zbytečného #N/A), pojďme zjistit, jak to funguje.
Funkce VYHLEDAT hledá zadanou hodnotu (2) v zadaném rozsahu (pole - druhý argument). Rozsah je obvykle pole buněk, ale funkce VIEW má první vlastnost, kterou potřebujeme – pokouší se převést přímo do pole jakýkoli výraz zapsaný jako druhý argument. Jinými slovy, vyhodnotí výraz v tomto argumentu, který použijeme tak, že výraz dosadíme jako druhý argument: 1/SEARCH(Directory!$A$2:$A$11;A2) . Část HLEDAT (Katalog!$A$2:$A$11;A2) postupně hledá každou hodnotu ze seznamu Katalog v buňce A2 (název z tabulky Zákazník). Pokud je nalezena hodnota, je vráceno číslo pozice prvního znaku nalezené hodnoty. Pokud hodnota není nalezena, je vrácena chybová hodnota #HODNOTA! (#HODNOTA!). Nyní druhá vlastnost: funkce vyžaduje, aby data byla uspořádána v poli ve vzestupném pořadí. Pokud jsou data umístěna jinak, bude funkce skenovat pole, dokud nenajde hodnotu, která je větší než hledaná, ale co nejblíže k ní (i když pokud to data umožňují, pro přesnější vyhledávání je stále lepší seřadit seznam ve vzestupném pořadí). Nejprve tedy vydělíme 1 výrazem SEARCH(Adresář!$A$2:$A$11,A2), abychom získali pole ve tvaru: (0,0181818181818182:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!: #HODNOTA !:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA !}
No a jako požadovanou hodnotu dáme funkci číslo 2 - zjevně větší číslo, než lze obecně nalézt v poli (protože jedna dělená libovolným číslem bude menší než dvě). A ve výsledku získáme pozici v poli, kde se vyskytuje poslední shoda z adresáře. Poté si funkce VIEW zapamatuje tuto pozici a vrátí hodnotu z pole Directory!$A$2:$A$11 (třetí argument) zapsané v tomto poli pro tuto pozici.
Fáze výpočtu funkce si můžete prohlédnout sami pro každou buňku; zde jednoduše uvedu fáze v mírně rozšířené podobě pro pochopení:

  1. =BROWSE(2,1/SEARCH(Adresář!$A$2:$A$11, A2);Adresář!$A$2:$A$11)
  2. =ZOBRAZIT(2;
    1/(55:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA;!}
    Katalog!$A$2:$A$11)
  3. =VIEW(2;(0,0181818181818182:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA!:#HODNOTA;Каталог!$A$2:$A$11)!}
  4. =ZOBRAZIT(2;
    1;
    ("FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX29271"
  5. "FM2-3320"

Nyní funkci trochu upřesníme a provedeme několik dalších implementací
Implementace 1:
Místo čísel artiklů a #N/A zobrazíme „Ano“ pro nalezené pozice a „Nenalezeno v katalogu“ pro chybějící:
=IF(END(VIEW(2,1/SEARCH(Katalog!$A$2:$A$11,A2)));"Nenalezeno v katalogu","Ano")
=IF(ISNA(LOOKUP(2,1/SEARCH(Adresář!$A$2:$A$11,A2))),,"Nenalezeno v adresáři","Ano")
Obsluha funkce je jednoduchá – protřídili jsme LOOKUP, takže zbývá jen UND a IF.
UNM (ISNA) se vrací SKUTEČNÝ pokud výraz v něm vrací chybovou hodnotu #N/A (#N/A) A NEPRAVDIVÉ pokud výraz uvnitř nevrací tuto chybovou hodnotu.
IF (IF) vrátí to, co je určeno druhým argumentem, pokud se výraz v prvním rovná SKUTEČNÝ a co je určeno třetím argumentem, je-li výrazem prvního argumentu NEPRAVDIVÉ.

Implementace 2:
Místo #N/A zobrazíme „Nenalezeno v katalogu“, ale pokud jsou články nalezeny, zobrazíme názvy těchto článků:
=IFERROR(ZOBRAZIT(2,1/HLEDAT(Katalog!$A$2:$A$11,A2),Katalog!$A$2:$A$11);"Není v katalogu")
=IFERROR(VYHLEDAT(2,1/HLEDAT(Katalog!$A$2:$A$11,A2);Katalog!$A$2:$A$11),"Není v katalogu")
Podrobně jsem o funkci IFERROR hovořil v tomto článku: Jak zobrazit 0 místo chyby v buňce se vzorcem.
Stručně řečeno, pokud výraz určený prvním argumentem funkce vrátí hodnotu jakékoli chyby, pak funkce vrátí to, co je zapsáno druhým argumentem (v našem případě text „Nenalezeno v adresáři“). Pokud výraz nevrátí chybu, pak funkce IFERROR zapíše hodnotu, kterou výraz získal v prvním argumentu (v našem případě to bude název článku).

Realizace 3
Je nutné nejen určit, kterému artiklu odpovídá, ale také zobrazit cenu za položku u tohoto artiklu (samotné ceny by měly být umístěny ve sloupci B Katalogového listu):
=IFERROR(ZOBRAZIT(2,1/HLEDAT(Adresář!$A$2:$A$11,A2),Adresář!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/HLEDAT(Adresář!$A$2:$A$11,A2),Adresář!$B$2:$B$11),"")

Pár důležitých poznámek:

  • údaje na listu s čísly artiklů by neměly obsahovat prázdné buňky. V opačném případě s vysokou mírou pravděpodobnosti vzorec vrátí hodnotu prázdné buňky, nikoli tu, která odpovídá podmínkám vyhledávání
  • Vzorec hledá takovým způsobem, že je nalezena jakákoliv shoda. Například číslo je napsáno jako článek 1 a v řádku názvu může být kromě celé 1 také 123 , 651123 , FG1412NM a tak dále. Pro všechny tyto položky lze vybrat položku číslo 1, protože je obsažen v každém titulu. Obvykle k tomu může dojít, pokud je článek 1 umístěn na konci seznamu

Proto je vhodné Před použitím vzorce seznam seřaďte vzestupně (od nejmenšího k největšímu, od A do Z).

V příkladu připojeném na začátku článku najdete všechny analyzované možnosti.

Pokud potřebujete zobrazit všechna jména, můžete použít funkci CONTAIN_ONE_OF z mého doplňku MulTEx.

Pomohl článek? Sdílejte odkaz se svými přáteli! Video lekce

("Spodní lišta":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":150," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; barva pozadí:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; poloha:relativní; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; poloha:relativní; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; barva:#fff; margin-top:8px;","buttoncss":"display:block; poloha:relativní; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Je poměrně obtížné najít potřebné informace v pracovním listu velké množství data. Dialogové okno Najít a nahradit však hledání informací mnohem usnadňuje. Navíc má nějaké užitečné funkce, což si mnoho uživatelů neuvědomuje.

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 mohli zadat jinak, jako Ivan Smirnov nebo I. Smirnov, nebo dokonce udělali chybu v příjmení. Při hledání takového názvu je nejlepší použít zástupné znaky.

Vstupte iv*smir* v terénu Nalézt a poté klikněte na tlačítko Najít všechny. Použití zástupných znaků nejen snižuje počet slov, která píšete, 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 velikost písmen hledaného textu odpovídala velikosti písmen daný text. 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í liště. 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 do vyhledávacího pole a do rozevíracího seznamu číslo 900 Oblast hledání Pokud vyberete položku hodnoty, neuvidíte buňku obsahující hodnotu 900 ve výsledcích hledání, 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 nelze najít naformátovaný číselné hodnoty. 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é způsoby. Pokud váš systém například 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.

Dobrý den, přátelé. Jak často musíte hledat shodu hodnoty v excelové tabulce? Například potřebujete najít adresu osoby v adresáři nebo cenu produktu v ceníku. Pokud se takové úkoly vyskytnou, tento příspěvek je právě pro vás!

Podobné procedury provádím každý den a bez níže popsaných funkcí bych to měl opravdu těžké. Berte na vědomí a aplikujte je ve své práci!

Vyhledávání v excelové tabulce, funkce VLOOKUP a GLOOKUP

Roli těchto funkcí v životě běžného uživatele je těžké přeceňovat. Nyní můžete snadno najít vhodný záznam v datové tabulce a vrátit odpovídající hodnotu.

Syntaxe funkce VLOOKUP je: =VLOOKUP( vyhledávací_hodnota; vyhledávací_tabulka; výstupní_číslo_sloupce; [typ_mapování]). Podívejme se na argumenty:

  • Hodnota hledání– hodnota, kterou budeme hledat. Toto je povinný argument;
  • Hledat tabulku– pole buněk, ve kterých bude vyhledávání probíhat. Sloupec s hledanými hodnotami musí být první v tomto poli. Toto je také povinný argument;
  • Číslo sloupce k zobrazení– pořadové číslo sloupce (počínaje prvním v poli), ze kterého funkce zobrazí data, pokud se požadované hodnoty shodují. Požadovaný argument;
  • Odpovídající typ– vyberte „1“ (nebo „PRAVDA“) pro slabou shodu, „0“ („FALSE“) pro úplnou shodu. Argument je volitelný, pokud je vynechán, provede se vyhledávání nepřísný zápas.

Nalezení přesné shody pomocí funkce VLOOKUP

Podívejme se na příklad, jak funguje funkce SVYHLEDAT, když je typ shody FALSE a hledá přesnou shodu. Pole B5:E10 označuje dlouhodobý majetek určité společnosti, jeho účetní hodnotu, inventární číslo a umístění. Buňka B2 obsahuje název, pro který je potřeba v tabulce najít inventární číslo a umístit jej do buňky C2.

Funkce VLOOKUP v Excelu

Napíšeme vzorec: =VLOOKUP(B2,B5:E10,3,FALSE) .

Zde první argument naznačuje, že v tabulce je třeba hledat hodnotu z buňky B2, tzn. slovo "Fax". Druhý argument říká, že prohledávaná tabulka je v rozsahu B5:E10 a v prvním sloupci musíte hledat slovo „Fax“, tzn. v poli B5:B10. Třetí argument říká programu, že výsledek výpočtu je obsažen ve třetím sloupci pole, tzn. D5:D10. Čtvrtý argument je FALSE, tzn. je vyžadována úplná shoda.

Funkce tak přijme řetězec „Fax“ z buňky B2 a bude jej hledat v poli B5:B10 shora dolů. Jakmile je nalezena shoda (řádek 8), funkce vrátí odpovídající hodnotu ze sloupce D, tzn. Obsah D8. To je přesně to, co jsme potřebovali, problém je vyřešen.

Pokud hledaná hodnota není nalezena, funkce vrátí .

Nalezení nepřesné shody pomocí funkce VLOOKUP

Díky této možnosti ve VLOOKUP se můžeme vyhnout složitým vzorcům, abychom našli požadovaný výsledek.

Pole B5:C12 zobrazuje úrokové sazby z úvěrů v závislosti na výši úvěru. V buňce B2 uvedeme výši půjčky a chceme obdržet sazbu za takovou transakci v C2. Úkol je obtížný v tom, že částka může být jakákoliv a je nepravděpodobné, že by se shodovala s těmi, která jsou uvedena v poli; hledání přesné shody není vhodné:

Poté napíšeme vzorec pro nepřísné vyhledávání: =VLOOKUP(B2;B5:C12;2;PRAVDA). Nyní, ze všech údajů uvedených ve sloupci B, program vyhledá nejbližší menší. Tzn., že pro částku 8000 se vybere hodnota 5000 a zobrazí se odpovídající procento.


Nepřísné vyhledávání VLOOKUP v Excelu

Aby funkce fungovala správně, je potřeba seřadit první sloupec tabulky vzestupně. V opačném případě může dojít k chybnému výsledku.

Funkce GLOOKUP má stejnou syntaxi jako VLOOKUP, ale hledá výsledek v řádcích, nikoli ve sloupcích. To znamená, že neprohledává tabulky shora dolů, ale zleva doprava a zobrazuje zadané číslo řádku, nikoli sloupec.

Vyhledávání dat pomocí funkce VIEW

Funkce VYHLEDAT funguje podobně jako VLOOKUP, ale má jinou syntaxi. Používám ho, když datová tabulka obsahuje několik desítek sloupců a pro použití funkce VLOOKUP je třeba dodatečně vypočítat číslo výstupního sloupce. V takových případech funkce VIEW usnadňuje práci. A tak syntaxe: =ZOBRAZIT( vyhledávací_hodnota; pole_k_hledání; Array_to_display) :

  • Hodnota hledání– údaje nebo odkaz na údaje, které mají být vyhledány;
  • Pole k vyhledávání– jeden řádek nebo sloupec, ve kterém hledáme podobnou hodnotu. Toto pole musíme seřadit ve vzestupném pořadí;
  • Pole k zobrazení– rozsah obsahující data pro zobrazení výsledků. Přirozeně musí mít stejnou velikost jako vyhledávací pole.

Při psaní tímto způsobem dáváte nerelativní odkaz na pole výsledků. A ukazujete přímo na to, tzn. není třeba nejprve počítat číslo výstupního sloupce. Funkci VIEW použijeme v prvním příkladu pro funkci VLOOKUP (dlouhodobý majetek, inventární čísla): =VIEW(B2;B5:B10;D5:D10). Problém byl úspěšně vyřešen!


Funkce VIEW v aplikaci Microsoft Excel

Vyhledávání podle relativních souřadnic. Funkce MATCH a INDEX

Dalším způsobem vyhledávání dat je kombinace funkcí MATCH a INDEX.

První z nich se používá k vyhledání hodnoty v poli a získání jejího sériového čísla: SEARCH( vyhledávací_hodnota; prohlížené_pole; [Odpovídající typ] ). Argumenty funkce:

  • Hodnota hledání– požadovaný argument
  • Pole k zobrazení– jeden řádek nebo sloupec, ve kterém hledáme shodu. Povinný argument
  • Odpovídající typ– zadejte „0“ pro vyhledání přesné shody, „1“ pro nejbližší menší shodu, „-1“ pro nejbližší větší shodu. Protože funkce vyhledává od začátku seznamu do konce, při hledání nejbližšího menšího seřaďte vyhledávací sloupec sestupně. A až budete hledat další, seřaďte je vzestupně.

Pozice požadované hodnoty byla nalezena, nyní ji můžete pomocí funkce zobrazit na obrazovce INDEX( Array; číslo řádku; [číslo_sloupce]) :

  • Pole– argument určuje, ze kterého pole buněk má být hodnota vybrána
  • Číslo řádku– uveďte sériové číslo řádku (počínaje první buňkou pole), který chcete zobrazit. Zde můžete hodnotu zapsat ručně, nebo použít výsledek výpočtu jiné funkce. Například HLEDAT.
  • Číslo sloupce– volitelný argument, zadaný, pokud pole sestává z několika sloupců. Pokud je argument vynechán, vzorec použije první sloupec tabulky.

Nyní zkombinujme tyto funkce, abychom získali výsledek:


Funkce MATCH a INDEX v Excelu

Toto jsou metody pro vyhledávání a zobrazování dat, které existují v Excelu. Dále je můžete použít ve výpočtech, použít je v prezentacích, provádět s nimi operace, specifikovat je jako argumenty pro jiné funkce atd.

Cítíte, jak vaše znalosti a dovednosti rostou a posilují? Pak nepřestávejte, čtěte dál! V dalším příspěvku budeme uvažovat: bude to obtížné a zajímavé!

Často se nabízí otázka « Jak najít v Excelu»? V Excelu můžete najít libovolné informace: text, část textu, číslo, telefonní číslo, email. adresa, příjmení, vzorec, poznámka, formát buňky atd.
Najděte buňku na průsečíku řádku a sloupce v Excelu – « Jak najít buňku v Excelu na průsečíku řádku a sloupce» ( Funkce INDEX v Excelu).
Najděte a přesuňte se do jiného umístění v Excelu(například na formuláři)několik dat najednou – viz článek „Vyhledání několika dat najednou v Excelu“ (Funkce VLOOKUP v Excelu).
Nebo najděte buňku s odkazem Vzorec Excelu, pro výměnu odkazu viz « Změňte odkazy na jiné listy ve vzorcích aplikace Excel».
Nalézt v Excel buňky s poznámkou - článek "Vložit poznámku v Excelu" .
Pro rychlé hledání existuje klávesová zkratka - Ctrl+F. Klikněte klávesa Ctrl a podržte jej a stiskněte klávesu F. Zobrazí se okno pro vyhledávání.
Okno vyhledávání můžete otevřít také takto: na kartě „Domů“ klikněte na tlačítko „Najít a vybrat“.
Na kartě „Najít“ do buňky „najít“ napište hledané slovo (je možná část slova) a klikněte na „najít další“. První takové slovo bude nalezeno. Poté klikněte na „najít další“ a hledání se přesune na druhé takové slovo.

A pokud potřebujete zobrazit všechna taková slova najednou, klikněte na tlačítko „najít vše“ a ve spodní části vyhledávacího okna se zobrazí seznam s adresou buňky. Chcete-li přejít na požadované slovo v tabulce, klikněte na požadované slovo v seznamu vyhledávacího okna.

Pokud vyhledávání nic nenašlo a víte, že tato data tam určitě jsou, zkuste odstranit odsazení z buněk tabulky. Jak odstranit odsazení v buňkách, viz článek " Excel text. Formát".
Hledání čísla v Excelu vyžaduje drobnou úpravu podmínek vyhledávání - platí pokročilé vyhledávání v Excelu.
Rada.
Pokud pracujete s tabulkou delší dobu a často potřebujete přejít k vyhledávání od jednoho slova ke druhému.Pak je výhodnější vyhledávací okno pokaždé nezavírat, ale přesunout do té části stolu, kde nebude překážet. Můžete jej přesunout pod obrazovku a ponechat pouze vstupní buňku pro hledané slovo („najít“) a poté stisknout Enter.
Tento vyhledávací dialog vždy zůstane na obrazovce, i když přejdete na jinou stránku. Pomocí tohoto okna můžete vyhledávat na jakékoli stránce, stačí ji aktivovat otevřít stránku. Chcete-li to provést, klikněte kurzorem na řádek „najít“.
Pro pokročilejší vyhledávání klikněte na tlačítko Možnosti a vyberte požadovanou možnost vyhledávání.
Vyberte například „Hodnota“. Poté vyhledá čísla, telefonní čísla atd.
Pokud potřebujete najít všechna slova se stejnou váhou, ale v pádech (mléko, mléko, mléko atd.), pak napíšeme vzorec se zástupnými znaky. O tom viz "Zástupné znaky v Excelu".
Excel funkce "Najít a zvýraznit" pomůže data nejen najít, ale i nahradit. Viz článek „Jak zkopírovat vzorec v Excelu bez změny odkazů“.
Jak odstranit nadbytečné mezery, které narušují zpracování dat v tabulce, přečtěte si článek "Jak odstranit nadbytečné mezery v Excelu".
V Excelu najdete libovolné informace nejen pomocí funkce Hledat nebo vzorců, ale také pomocí funkce podmíněného formátování. Přečtěte si o tom článek „Podmíněné formátování v Excelu“.
O funkci „Najít a vybrat“ si také můžete přečíst v článku „Filtrujte v Excelu".