4 funkcie vyhľadávania v programe Excel na efektívne vyhľadávanie v tabuľkách

4 funkcie vyhľadávania v programe Excel na efektívne vyhľadávanie v tabuľkách

Väčšinu času vyhľadávanie a Microsoft Excel tabuľka je veľmi jednoduchá. Ak preň nemôžete len prehľadávať riadky a stĺpce, môžete ho vyhľadať pomocou Ctrl + F. Ak pracujete s naozaj veľkou tabuľkou, používanie jednej z týchto štyroch vyhľadávacích funkcií vám môže ušetriť veľa času.





Akonáhle budete vedieť, ako vyhľadávať v programe Excel pomocou vyhľadávania, bez ohľadu na to, aké veľké budú vaše tabuľky, v Exceli budete vždy môcť niečo nájsť!



1. Funkcia VLOOKUP

Táto funkcia vám umožňuje zadať stĺpec a hodnotu a vráti hodnotu zo zodpovedajúceho riadka iného stĺpca (ak to nedáva zmysel, o chvíľu to bude jasné). Dva príklady, kde to môžete urobiť, sú vyhľadanie priezviska zamestnanca podľa čísla zamestnanca alebo nájdenie telefónneho čísla zadaním priezviska.





Tu je syntax funkcie:

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup])
  • [lookup_value] je informácia, ktorú už máte. Ak napríklad potrebujete vedieť, v akom štáte sa mesto nachádza, bude to názov mesta.
  • [table_array] umožňuje zadať bunky, v ktorých bude funkcia hľadať vyhľadávacie a návratové hodnoty. Pri výbere rozsahu dbajte na to, aby prvý stĺpec zahrnutý vo vašom poli obsahoval vašu vyhľadávaciu hodnotu!
  • [col_index_num] je číslo stĺpca, ktorý obsahuje návratovú hodnotu.
  • [range_lookup] je voliteľný argument a má hodnotu 1 alebo 0. Ak zadáte 1 alebo tento argument vynecháte, funkcia vyhľadá zadanú hodnotu alebo nasledujúce najnižšie číslo. Takže na obrázku nižšie VLOOKUP hľadajúci SAT skóre 652 vráti 646, pretože je to najbližšie číslo v zozname, ktoré je menšie ako 652, a [range_lookup] je predvolene 1.

Pozrime sa, ako by ste to mohli využiť. Táto tabuľka obsahuje identifikačné čísla, mená a priezviská, skóre mesta, štátu a SAT. Povedzme, že chcete nájsť SAT skóre osoby s priezviskom „Zimy“. VLOOKUP to uľahčuje. Tu je vzorec, ktorý by ste použili:



=VLOOKUP('Winters', C2:F101, 4, 0)

Pretože skóre SAT je štvrtým stĺpcom od stĺpca priezviska, 4 je argument stĺpcového indexu. Všimnite si toho, že keď hľadáte text, je vhodné nastaviť [range_lookup] na 0. Bez toho môžete dosiahnuť zlé výsledky.

Tu je výsledok:



Vrátilo 651, SAT skóre študenta menom Kennedy Winters, ktorý je v riadku 92 (zobrazené na vložke vyššie). Skrolovanie pri hľadaní mena by trvalo oveľa dlhšie, než rýchle zadanie syntaxe!

Poznámky k VLOOKUP

Pri používaní funkcie VLOOKUP je dobré si zapamätať niekoľko vecí. Uistite sa, že prvý stĺpec vo vašom rozsahu je ten, ktorý obsahuje vašu vyhľadávaciu hodnotu. Ak nie je v prvom stĺpci, funkcia vráti nesprávne výsledky. Ak sú vaše stĺpce dobre usporiadané, nemal by to byť problém.



Majte tiež na pamäti, že VLOOKUP vráti iba jednu hodnotu. Ak by ste ako vyhľadávaciu hodnotu použili „Gruzínsko“, vrátilo by to skóre prvého študenta z Gruzínska a nenaznačilo by to, že v skutočnosti existujú dvaja študenti z Gruzínska.

2. Funkcia HLOOKUP

Kde VLOOKUP nájde zodpovedajúce hodnoty v inom stĺpci, HLOOKUP nájde zodpovedajúce hodnoty v inom riadku. Pretože je zvyčajne najľahšie prehľadať hlavičky stĺpcov, kým nenájdete ten správny, a pomocou filtra nájsť to, čo hľadáte, HLOOKUP sa najlepšie používa vtedy, ak máte skutočne veľké tabuľky alebo pracujete s hodnotami, ktoré sú usporiadané podľa času. .

Tu je syntax funkcie:

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [lookup_value] je hodnota, ktorú poznáte a pre ktorú chcete nájsť zodpovedajúcu hodnotu.
  • [table_array] sú bunky, v ktorých chcete hľadať.
  • [row_index_num] určuje riadok, z ktorého bude pochádzať návratová hodnota.
  • [range_lookup] je rovnaký ako vo VLOOKUP, nechajte ho prázdny, aby ste získali najbližšiu hodnotu, kedykoľvek je to možné, alebo zadajte 0, aby ste hľadali iba presné zhody.

Táto tabuľka obsahuje riadok pre každý štát spolu so skóre SAT v rokoch 2000–2014. Na vyhľadanie priemerného skóre v Minnesote v roku 2013 môžete použiť HLOOKUP. Urobíme to takto:

=HLOOKUP(2013, A1:P51, 24)

Ako vidíte na obrázku nižšie, skóre sa vráti:

Minnesotans mal v roku 2013 priemer 1014 bodov. Upozorňujeme, že rok 2013 nie je v úvodzovkách, pretože je to číslo a nie reťazec. Tiež 24 pochádza z Minnesoty, ktorá je v 24. rade.

Tu je ako vypočítať vážený priemer v programe Excel .

Poznámky k HLOOKUP

Rovnako ako pre VLOOKUP, vyhľadávacia hodnota musí byť v prvom riadku poľa tabuľky. Toto je pri HLOOKUP len zriedka problém, pretože na vyhľadávaciu hodnotu zvyčajne budete používať názov stĺpca. HLOOKUP tiež vráti iba jednu hodnotu.

3-4. Funkcie INDEX a MATCH

INDEX a MATCH sú dve rôzne funkcie, ale keď sa používajú spoločne, vyhľadávanie vo veľkej tabuľke môže byť oveľa rýchlejšie. Obe funkcie majú svoje nevýhody, ale ich kombináciou budeme stavať na silných stránkach oboch.

Najprv však syntax oboch funkcií:

=INDEX([array], [row_number], [column_number])
  • [pole] je pole, v ktorom budete hľadať.
  • [číslo_riadka] a [číslo_ stĺpca] možno použiť na zúženie vyhľadávania (na to sa chvíľu pozrieme.)
=MATCH([lookup_value], [lookup_array], [match_type])
  • [lookup_value] je hľadaný výraz, ktorý môže byť reťazec alebo číslo.
  • [lookup_array] je pole, v ktorom bude program Microsoft Excel hľadať hľadaný výraz.
  • [typ_zhody] je voliteľný argument, ktorý môže byť 1, 0 alebo -1. 1 vráti najväčšiu hodnotu, ktorá je menšia alebo rovná vášmu hľadanému výrazu. 0 vráti iba váš presný výraz a -1 vráti najmenšiu hodnotu, ktorá je väčšia alebo rovná vášmu hľadanému výrazu.

Možno nie je jasné, ako tieto dve funkcie použijeme spoločne, a preto to rozvediem tu. MATCH vezme hľadaný výraz a vráti referenciu na bunku. Na obrázku nižšie vidíte, že pri hľadaní hodnoty 646 v stĺpci F funkcia MATCH vráti 4.

INDEX na druhej strane robí opak: vezme odkaz na bunku a vráti hodnotu v nej. Tu môžete vidieť, že keď vám INDEX prikáže vrátiť šiestu bunku stĺpca Mesto, vráti hodnotu „Anchorage“, hodnotu z riadka 6.

Čo budeme robiť, je skombinovať tieto dva tak, aby MATCH vrátil odkaz na bunku a INDEX použil tento odkaz na vyhľadanie hodnoty v bunke. Povedzme, že si pamätáte, že existoval študent, ktorého priezvisko bolo Waters, a chcete vidieť, aké bolo skóre tohto študenta. Tu je vzorec, ktorý použijeme:

smartfóny s najlepšou výdržou batérie 2016
=INDEX(F:F, MATCH('Waters', C:C, 0))

Tu si všimnete, že typ zhody je nastavený na 0. Keď hľadáte reťazec, to budete chcieť použiť. Tu je to, čo dostaneme, keď spustíme túto funkciu:

Ako môžete vidieť z vložky, Owen Waters dosiahol skóre 1720, čo je číslo, ktoré sa zobrazí pri spustení funkcie. To sa nemusí zdať také užitočné, keď sa stačí pozrieť na niekoľko stĺpcov, ale predstavte si, koľko času by ste ušetrili, keby ste to museli urobiť 50 -krát za veľká databázová tabuľka ktorý obsahoval niekoľko stoviek stĺpcov!

Nechajte vyhľadávanie v Exceli začať

Program Microsoft Excel má veľa extrémne silné funkcie na manipuláciu s údajmi a vyššie uvedené štyri iba poškriabajú povrch. Naučiť sa ich používať vám výrazne uľahčí život.

Ak naozaj chcete ovládať program Microsoft Excel, mohlo by vám skutočne prospieť, keby ste mali cheat list Essential Excel poruke!

Obrazový kredit: Cico/ Shutterstock

zdieľam zdieľam Tweet E -mail Canon vs. Nikon: Ktorá značka fotoaparátu je lepšia?

Canon a Nikon sú dve najväčšie mená v kamerovom priemysle. Ale ktorá značka ponúka lepšiu radu fotoaparátov a objektívov?

Čítajte ďalej Súvisiace témy
  • Produktivita
  • Tabuľka
  • Microsoft Excel
  • Hľadať triky
O autorovi Ian Buckley(216 publikovaných článkov)

Ian Buckley je nezávislý novinár, hudobník, performer a producent videa žijúci v Berlíne v Nemecku. Keď práve nepíše alebo je na pódiu, pohráva si s elektronikou alebo kódom pre domácich majstrov v nádeji, že sa stane šialeným vedcom.

Viac od Iana Buckleyho

prihlásiť sa ku odberu noviniek

Pripojte sa k nášmu bulletinu a získajte technické tipy, recenzie, bezplatné elektronické knihy a exkluzívne ponuky!

Kliknutím sem sa prihlásite na odber