6 vzorcov na vyhľadávanie v programe Excel

Anonim

Všetci poznáme slávnu hviezdu funkcií Excel VLOOKUP. Bežne sa používa na vyhľadávanie hodnôt s jedinečným ID. Nie je to však jediná funkcia, ktorú je možné použiť na vyhľadávanie hodnôt v programe Excel. Na vyhľadanie hodnoty je možné použiť mnoho ďalších funkcií a vzorcov. V tomto článku vám predstavím všetky tieto funkcie a vzorce vyhľadávania v programe Excel. Niektoré sú dokonca lepšie ako funkcia VLOOKUP v programe Excel. Tak čítajte do konca.

1. Funkcia VLOOKUP programu Excel

Prvou funkciou vyhľadávania v Exceli je samozrejme funkcia VLOOKUP. Táto funkcia je známa z nejakého dôvodu. Túto funkciu môžeme použiť na viac než len na vyhľadávanie. Základnou úlohou tejto funkcie je však vyhľadávať hodnoty v tabuľke zľava doprava.

Syntax funkcie VLOOKUP:

= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])

Lookup_value: Hodnota, podľa ktorej chcete hľadať v prvom stĺpci poľa Tabuľka.

Pole_rozpisu: Tabuľka, v ktorej chcete hľadať/hľadať

col_index_number: Číslo stĺpca v poli tabuľky, z ktorého chcete načítať výsledky.

[range_lookup]: FALSE, ak chcete hľadať presnú hodnotu, TRUE, ak chcete približnú zhodu.

Výhody VLOOKUPU:

  • Jednoduché použitie.
  • Rýchlo
  • Viacnásobné použitie
  • Najlepšie na vyhľadávanie hodnôt vo vertikálnom poradí.

Nevýhody:

  • Používa sa iba na vertikálne vyhľadávanie
  • Vráti iba prvú zodpovedajúcu hodnotu.
  • Statický, kým sa nepoužije s funkciou MATCH.
  • Nie je možné vyhľadávať hodnoty zľava od vyhľadávanej hodnoty.

Tu si môžete podrobne prečítať o tomto vzorci vyhľadávania v Exceli.

2. Funkcia Excel HLOOKUP

Funkcia HLOOKUP je chýbajúcou časťou funkcie VLOOKUP. Funkcia HLOOKUP sa používa na horizontálne vyhľadávanie hodnôt. Inými slovami, ak chcete v Exceli vyhľadať hodnotu porovnaním hodnoty v stĺpcoch a získať hodnoty z riadkov, použijeme funkciu HLOOKUP. Presne to je obdoba funkcie VLOOKUP.

Syntax HLOOKUP

=HLOOKUP(vyhľadávacia hodnota, pole tabuľky, číslo indexu riadka, [rozsah_pohľadu])
  • vyhľadávacia hodnota: Hodnota, ktorú hľadáte.
  • Pole tabuľky: Tabuľka, v ktorej hľadáte hodnotu.
  • Indexové číslo riadka: Číslo riadka v tabuľke, z ktorého chcete načítať údaje.
  • [range_lookup]: je to typ zhody. 0 pre presnú zhodu a 1 pre približnú zhodu.

Výhody funkcie HLOOKUP:

  • Môže vyhľadávať hodnoty horizontálne.
  • Jednoduché použitie.
  • Viacnásobné použitie
  • Rýchlo

Nevýhody:

  • Používa sa iba na horizontálne vyhľadávanie
  • Vráti iba prvú zodpovedajúcu hodnotu.
  • Statický, kým sa nepoužije s funkciou MATCH.
  • Nie je možné vyhľadať hodnoty nad hodnotami vyhľadávania v tabuľke.

Tu sa môžete dozvedieť o tejto funkcii vyhľadávania v Exceli.

3. Vzorec na vyhľadávanie INDEX-MATCH

Tam, kde sa VLOOKUP a HLOOKUP nedostanú, môže tento vzorec dosiahnuť. Toto je najlepší vzorec na vyhľadávanie v Exceli do Excelu 2016 (XLOOKUP je na ceste).

Generický vzorec INDEX MATCH

= INDEX (Rozsah_výsledkov, MATCH (hodnota_hľadania, rozsah vyhľadávania, 0))

Výsledok_Rozsah: Je to rozsah rozsahu, odkiaľ chcete získať hodnotu.

Lookup_value: Je to hodnota, ktorej chcete zodpovedať.

Rozsah vyhľadávania:Je to rozsah, v ktorom chcete zodpovedať vyhľadávanej hodnote.

Výhody vyhľadávacieho vzorca INDEX-MATCH:

  • Môžete vyhľadávať v štyroch smeroch. Môže vyhľadávať hodnoty vľavo a hore od vyhľadávanej hodnoty.
  • Dynamický.
  • Nie je potrebné definovať index riadkov alebo stĺpcov.

Nevýhody:

  • Pre nových používateľov to môže byť náročné.
  • Kombinuje dve funkcie Excelu. Používatelia musia porozumieť fungovaniu funkcií INDEX a MATCH.

O tomto vzorci sa môžete dozvedieť tu.

4: Vzorec na vyhľadávanie Excel OFFSET-MATCH

Toto je ďalší vzorec, ktorý možno použiť na dynamické vyhľadávanie hodnôt. Tento vzorec na vyhľadávanie v Exceli používa funkciu OFFSET ako funkciu ukotvenia a MATCH ako funkciu podávača. Pomocou tohto vzorca môžeme dynamicky získavať hodnoty z tabuľky vyhľadávaním v riadkoch a stĺpcoch.

Generický vzorec,

= OFFSET (StartCell, MATCH (RowLookupValue, RowLookupRange, 0), MATCH (ColLookupValue, ColLookupRange, 0))

StartCell:Toto je počiatočná bunka vyhľadávacej tabuľky. Povedzme, že ak chcete vyhľadávať v rozsahu A2: A10, potom StartCell bude A1.

RowLookupValue: Toto je vyhľadávacia hodnota, ktorú chcete nájsť v riadkoch podStartCell.

Rozsah vyhľadávania riadkov:Toto je rozsah, v ktorom chcete vyhľadať RowLookupValue. Je to nižšie uvedený rozsah StartCell (A2: A10).

ColLookupValue: Toto je vyhľadávacia hodnota, ktorú chcete nájsť v stĺpcoch (hlavičkách).

Rozsah ColLookup:Toto je rozsah, v ktorom chcete vyhľadať ColLookupValue. Je to rozsah na pravej strane StartCell (ako B1: D1).

Výhody tejto techniky vyhľadávania v programe Excel:

  • Rýchlo
  • Vie hľadať horizontálne aj vertikálne.
  • Dynamický

Nevýhody:

  • Komplikované pre niektorých ľudí.
  • Musíte porozumieť fungovaniu funkcie OFFSET a funkcie MATCH.

Tu sa môžete podrobne dozvedieť o tomto vyhľadávacom vzorci.

5: Excel LOOKUP Formula viac hodnôt

Všetky vyššie uvedené vzorce vyhľadávania vracajú prvú nájdenú hodnotu z poľa. Ak je viac ako jeden zápas, ostatné zápasy nevrátia. V takom prípade tento vzorec vstúpi do činnosti, aby zachránil deň. Tento vzorec vráti všetky zodpovedajúce hodnoty zo zoznamu namiesto iba prvej zhody.

Tento vzorec používa ako hlavné funkcie funkcie INDEX, ROW a IF. Na spracovanie chýb je možné voliteľne použiť funkciu IFERROR.

Generický vzorec

{= INDEX (pole, SMALL (IF (lookup_value = lookup_value_range, ROW (lookup_value_range) -ROW (first cell of lookup_value_range) +1), ROW (1: 1)))}}

Pole: Rozsah, z ktorého chcete načítať údaje.
lookup_value: Hodnota lookup_value, ktorú chcete filtrovať.
rozsah_hľadanej_hodnoty: Rozsah, v ktorom chcete filtrovať hodnotu lookup_value.
Prvá bunka v rozsahu lookup_value: ak je váš rozsah parametra lookup_value $ A $ 5: $ A $ 100, potom je to $ A $ 5.
Dôležité: Všetko by malo byť absolútne odkazované. lookup_value môže byť relatívna podľa požiadavky.
Zadajte ho ako vzorec poľa. Po napísaní vzorca stlačte CTRL+SHIFT+ENTER, aby sa stal vzorcom poľa.

Ako vidíte na gif, vráti všetky zápasy z tabuľky programu Excel.

Výhody:

  • Vráti s viacerými zhodnými hodnotami z tabuľky programu Excel.
  • Dynamický

Nevýhody:

  • Je to príliš zložité na to, aby to nový používateľ pochopil.
  • Používa maticový vzorec
  • Je potrebné definovať možný počet výstupov a použiť tento vzorec ako vzorec viacbunkového poľa (nie v Exceli 2019 a 365).
  • Pomaly.

Tu sa môžete podrobne dozvedieť o tomto vzorci.

6: Vzorec VLOOKUP-ZVOLIŤ vyhľadávanie programu Excel

Väčšina ľudí teda tvrdí, že pomocou funkcie VLOOKUP nie je možné v programe Excel vyhľadávať hodnoty zľava od požadovanej hodnoty. Je mi ľúto, že sa mýlim, ale mýlia sa. Pomocou funkcie VLOOKUP pomocou funkcie CHOOSE môžeme v programe Excel vyhľadávať vľavo od hodnoty vyhľadávania.

Generický vzorec:

= VLOOKUP (lookup_value, CHOOSE ({1, 2}, lookup_range, req_range), 2, 0)

lookup_value : hodnota, ktorú treba hľadať

rozsah_hľadania : rozsah, kde hľadať lookup_value

rozsah_rozsahu : rozsah, kde sa požaduje zodpovedajúca hodnota

2 : druhý stĺpec, číslo predstavujúce rozsah_req

0 : vyhľadajte presnú zhodu

V tomto vzorci v podstate vytvárame virtuálnu tabuľku vo vnútri vzorca pomocou funkcie CHOOSE. Funkcia CHOOSE vytvorí tabuľku dvoch stĺpcov. Prvý stĺpec obsahuje rozsah vyhľadávania a druhý stĺpec obsahuje rozsah výsledkov.

Výhody vyhľadávacieho vzorca VLOOKUP-CHOOSE:

  • Môžete vyhľadávať vľavo od hodnoty vyhľadávania
  • Rýchlo
  • Ľahko

Nevýhody:

  • Funkcia CHOOSE sa používa len zriedka. Používatelia musia pochopiť, ako funguje.

Tu sa môžete dozvedieť o tomto vyhľadávacom vzorci.

Takže áno, chlapci, toto sú rôzne vyhľadávacie funkcie a vzorce. To nie je všetko. V programe Excel môže byť oveľa viac vyhľadávacích vzorcov, ktoré je možné vytvoriť pomocou rôznych kombinácií vzorcov programu Excel. Ak máte nejaké špeciálne vyhľadávacie techniky, podeľte sa o to v sekcii komentárov nižšie. Zahrneme to do nášho článku s vašim menom.

Dúfam, že to bolo nápomocné a poučné. Ak máte akékoľvek pochybnosti o tomto článku, opýtajte sa ma v sekcii komentárov nižšie.

10 a viac nových funkcií v Exceli 2019 a 365: Napriek tomu, že funkcie dostupné v Exceli 2016 a staršom stačia na výpočet a automatizáciu, niekedy sú vzorce náročné. Tieto druhy drobných, ale dôležitých vecí sú vyriešené v Exceli 2019 a 365.

17 vecí o VLOOKUPE v Exceli: VLOOKUP nie je vzorec na vyhľadávanie, je to viac než to. VLOOKUP má mnoho ďalších schopností a môže byť použitý na viacero účelov. V tomto článku skúmame všetky možné použitia funkcie VLOOKUP.

10 a viac kreatívnych pokročilých grafov Excelu, pomocou ktorých môžete ovládať svoj informačný panel: Excel je účinný nástroj na vizualizáciu údajov, ktorý je možné použiť na vytváranie ohromujúcich grafov v programe Excel. Týchto 15 pokročilých grafov Excelu možno použiť na hypnotizáciu vašich kolegov a šéfov.

4 rebríčky úspechov Creative Target Vs v programe Excel: Tabuľky cieľov vs úspechy sú najzákladnejšie a najdôležitejšie grafy v každom podniku. Preto je lepšie ich dať čo najkreatívnejšie. Vďaka týmto 4 kreatívnym grafom môžu vaše informačné panely rozhýbať prezentáciu.

Populárne články:

50 skratiek programu Excel na zvýšenie produktivity | Vykonajte svoju úlohu rýchlejšie. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.

Ako používať funkciu Excel VLOOKUP| Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.

Ako používať Excel Funkcia COUNTIF| Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.

Ako používať funkciu SUMIF v programe Excel | Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.