Mali ste niekedy veľkú tabuľku s údajmi v Exceli^(Excel) a potrebovali ste jednoduchý spôsob, ako z nej filtrovať a extrahovať konkrétne informácie? Ak sa naučíte používať funkciu VLOOKUP v Exceli^(Excel) , môžete toto vyhľadávanie vykonať pomocou jedinej výkonnej funkcie Excelu^(Excel) .

Funkcia VLOOKUP v Exceli^(Excel) vydesí veľa ľudí, pretože má veľa parametrov a existuje viacero spôsobov, ako ju použiť. V tomto článku sa dozviete všetky spôsoby, ako môžete použiť funkciu VLOOKUP v Exceli^(Excel) a prečo je táto funkcia taká výkonná.

Parametre VLOOKUP v Exceli^{(VLOOKUP Parameters In Excel)}

Keď začnete písať =VLOOKUP( do ľubovoľnej bunky v Exceli^(Excel) , zobrazí sa kontextové okno so všetkými dostupnými parametrami funkcií.

Poďme preskúmať každý z týchto parametrov a čo znamenajú.

• lookup_value : Hodnota, ktorú hľadáte z tabuľky
• table_array : Rozsah buniek v hárku, v ktorom chcete prehľadávať
• col_index_num : Stĺpec, z ktorého chcete získať výsledok
• [range_lookup] : režim zhody ( TRUE = približné, FALSE = presné)

Tieto štyri parametre vám umožňujú vykonávať množstvo rôznych užitočných vyhľadávaní údajov vo veľmi veľkých súboroch údajov.

Jednoduchý príklad VLOOKUP Excel^{(A Simple VLOOKUP Excel Example)}

VLOOKUP nepatrí medzi základné funkcie Excelu^{(the basic Excel functions)} , ktoré ste sa možno naučili, preto sa pozrime na jednoduchý príklad, ako začať.

V nasledujúcom príklade použijeme veľkú tabuľku skóre SAT pre školy v Spojených ^(United) štátoch^(States) . Táto tabuľka obsahuje viac ako 450 škôl spolu s individuálnymi skóre SAT pre čítanie, matematiku a písanie. Neváhajte^(Feel) a stiahnite si a sledujte. Existuje externé pripojenie, ktoré sťahuje údaje, takže pri otvorení súboru dostanete upozornenie, ale je to bezpečné.

Bolo by veľmi časovo náročné prehľadávať taký veľký súbor údajov, aby ste našli školu, o ktorú máte záujem.

Namiesto toho môžete vytvoriť jednoduchý formulár v prázdnych bunkách na strane tabuľky. Ak chcete vykonať toto vyhľadávanie, vytvorte jedno pole pre Škola^(School) a tri ďalšie polia pre čítanie, matematiku a písanie skóre.

Ďalej budete musieť použiť funkciu VLOOKUP v Exceli^(Excel) , aby tieto tri polia fungovali. V poli Čítanie^(Reading) vytvorte funkciu VLOOKUP takto:

1. Zadajte =VLOOKUP(
2. Vyberte pole Škola^(School) , ktoré je v tomto príklade I2 . Napíšte čiarku.
3. Vyberte celý rozsah buniek obsahujúcich údaje, ktoré chcete vyhľadať. Napíšte čiarku.

Keď vyberiete rozsah, môžete začať od stĺpca, ktorý používate na vyhľadávanie (v tomto prípade stĺpca s názvom školy), a potom vybrať všetky ostatné stĺpce a riadky, ktoré obsahujú údaje.

Poznámka^(Note) : Funkcia VLOOKUP v Exceli^(Excel) dokáže prehľadávať iba bunky napravo od vyhľadávacieho stĺpca. V tomto príklade musí byť stĺpec s názvom školy naľavo od údajov, ktoré hľadáte.

1. Ďalej, ak chcete získať skóre čítania^(Reading) , budete musieť vybrať 3. stĺpec z vybratého stĺpca úplne vľavo. Zadajte teda 3 a potom ďalšiu čiarku.
2. Nakoniec zadajte FALSE pre presnú zhodu a zatvorte funkciu pomocou a ) .

Vaša konečná funkcia VLOOKUP by mala vyzerať asi takto:

=VLOOKUP(I2,B2:G461,3,FALSE)

Keď prvýkrát stlačíte Enter a dokončíte funkciu, všimnete si, že pole Čítanie^(Reading) bude obsahovať #N/A .

Je to preto, že pole Škola^(School) je prázdne a funkcia VLOOKUP nemá čo nájsť. Ak však zadáte názov akejkoľvek strednej školy, ktorú chcete vyhľadať, uvidíte správne výsledky z daného riadku pre skóre čítania .^(Reading)

Ako sa vysporiadať s funkciou VLOOKUP, v ktorej sa rozlišujú malé a veľké písmená^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Môžete si všimnúť, že ak nezadáte názov školy v rovnakom prípade, ako je uvedený v súbore údajov, neuvidíte žiadne výsledky.

Je to preto, že funkcia VLOOKUP rozlišuje veľké a malé písmená. To môže byť nepríjemné, najmä pre veľmi veľkú množinu údajov, kde je stĺpec, v ktorom prehľadávate, nekonzistentný s tým, ako sa veci píšu veľkými písmenami.

Ak to chcete obísť, môžete pred vyhľadaním výsledkov prinútiť to, čo hľadáte, prepnúť na malé písmená. Ak to chcete urobiť, vytvorte nový stĺpec vedľa stĺpca, ktorý hľadáte. Napíšte funkciu:

=TRIM(LOWER(B2))

Týmto sa názov školy napíše malými písmenami a odstránia sa všetky nadbytočné znaky (medzery), ktoré sa môžu nachádzať na ľavej alebo pravej strane názvu.

Podržte stlačený kláves Shift^(Shift) a umiestnite kurzor myši nad pravý dolný roh prvej bunky, kým sa nezmení na dve vodorovné čiary. Dvojitým^(Double) kliknutím myšou automaticky vyplníte celý stĺpec.

A napokon, keďže sa funkcia VLOOKUP^(VLOOKUP) pokúsi použiť skôr vzorec ako text v týchto bunkách, musíte ich všetky previesť iba na hodnoty. Ak to chcete urobiť, skopírujte celý stĺpec, kliknite pravým tlačidlom myši na prvú bunku a vložte iba hodnoty.

Teraz, keď sú všetky vaše údaje vyčistené v tomto novom stĺpci, mierne upravte svoju funkciu VLOOKUP v Exceli^(Excel) tak, aby používala tento nový stĺpec namiesto predchádzajúceho, a to tak, že rozsah vyhľadávania^{(the lookup range)} spustíte na C2 namiesto B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Teraz si všimnete, že ak zadáte hľadaný výraz vždy malými písmenami, vždy dostanete dobrý výsledok vyhľadávania.

Toto je praktický tip programu Excel^{(handy Excel tip)} , ktorý vám pomôže prekonať skutočnosť, že funkcia VLOOKUP^(VLOOKUP) rozlišuje malé a veľké písmená.

VLOOKUP Približná zhoda

Zatiaľ čo príklad VYHĽADÁVANIA^(LOOKUP) presnej zhody opísaný v prvej časti tohto článku je celkom jednoduchý, približná zhoda je o niečo zložitejšia.

Približná zhoda sa najlepšie používa na vyhľadávanie v číselných radoch. Aby ste to urobili správne, rozsah vyhľadávania musí byť správne zoradený. Najlepším príkladom je funkcia VLOOKUP na vyhľadanie stupňa písmen, ktorý zodpovedá číselnému stupňu.

Ak má učiteľ dlhý zoznam známok za domáce úlohy študentov z celého roka s konečným spriemerovaným stĺpcom, bolo by pekné, keby sa známka za písmená zodpovedajúca tejto konečnej známke zobrazovala automaticky.

To je možné pomocou funkcie VLOOKUP . Všetko, čo je potrebné, je vyhľadávacia tabuľka vpravo, ktorá obsahuje príslušnú známku písmen pre každý číselný rozsah skóre.

Teraz pomocou funkcie VLOOKUP a približnej zhody môžete nájsť správnu známku písmen zodpovedajúcu správnemu číselnému rozsahu.

V tejto funkcii VLOOKUP:

• lookup_value : F2, konečná priemerná známka
• table_array : I2:J8, Rozsah vyhľadávania podľa písmen
• index_column : 2, druhý stĺpec vo vyhľadávacej tabuľke
• [range_lookup] : TRUE, približná zhoda

Keď dokončíte funkciu VLOOKUP v G2 a stlačíte Enter , môžete vyplniť zvyšok buniek pomocou rovnakého postupu, aký je opísaný v poslednej časti. Všetky známky za písmená uvidíte správne vyplnené.

Všimnite si, že funkcia VLOOKUP v Exceli^(Excel) vyhľadáva od spodného konca rozsahu známok s priradeným skóre písmena až po začiatok rozsahu skóre ďalšieho písmena.

Takže „C“ musí byť písmeno priradené nižšiemu rozsahu (75) a B je priradené spodnej časti (minimum) vlastného rozsahu písmen. Funkcia VLOOKUP^(VLOOKUP) „nájde“ výsledok pre 60 (D) ako najbližšiu približnú hodnotu pre čokoľvek medzi 60 až 75.

VLOOKUP v Exceli^(Excel) je veľmi výkonná funkcia, ktorá je dostupná už dlho. Je tiež užitočný pri hľadaní zodpovedajúcich hodnôt kdekoľvek v excelovom zošite^{(finding matching values anywhere in an Excel workbook)} .

Majte však na pamäti, že používatelia Microsoftu^(Microsoft) , ktorí majú mesačné predplatné Office 365 , majú teraz prístup k novšej funkcii XLOOKUP. Táto funkcia má viac parametrov a dodatočnú flexibilitu. Používatelia s polročným predplatným budú musieť počkať na uvedenie aktualizácie v júli 2020^{(July 2020)} .

How to Use VLOOKUP in Excel

Have you ever had a large spreadsheet with data in Excel and need аn easy way to filtеr and extract specific іnformation from it? If уou learn how to usе VLOOKUP in Excel, you can do this lookup with jυst a single, powerful Excel function.

The VLOOKUP function in Excel scares a lot of people because it has a lot of parameters and there are multiple ways to use it. In this article you’ll learn all of the ways you can use VLOOKUP in Excel and why the function is so powerful.

VLOOKUP Parameters In Excel

When you start typing =VLOOKUP( into any cell in Excel, you’ll see a pop-up showing all of the available function parameters.

Let’s examine each of these parameters and what they mean.

• lookup_value: The value you’re looking for from the spreadsheet
• table_array: The range of cells in the sheet you want to search through
• col_index_num: The column where you want to pull your result from
• [range_lookup]: Match mode (TRUE = approximate, FALSE = exact)

These four parameters let you do a lot of different, useful searches for data inside of very large datasets.

A Simple VLOOKUP Excel Example

VLOOKUP isn’t one of the basic Excel functions you might have learned, so let’s look at a simple example to get started.

For the following example, we’ll use a large spreadsheet of SAT scores for schools in the United States. This spreadsheet contains over 450 schools along with individual SAT scores for reading, math, and writing. Feel free to download to follow along. There is an external connection that pulls the data, so you’ll get a warning when opening the file, but it’s safe.

It would be very time consuming to search through such a large dataset to find the school that you’re interested in.

Instead, you can create a simple form in the blank cells on the side of the table. To conduct this search, just make one field for School, and three additional fields for reading, math, and writing scores.

Next, you’ll need to use the VLOOKUP function in Excel to make these three fields work. In the Reading field, create the VLOOKUP function as follows:

1. Type =VLOOKUP(
2. Select the School field, which in this example is I2. Type a comma.
3. Select the entire range of cells that contain the data you want to look up. Type a comma.

When you select the range, you can start from the column you’re using to look up (in this case the school name column), and then select all of the other columns and rows that contain the data.

Note: The VLOOKUP function in Excel can only search through cells to the right of the search column. In this example, the school name column needs to be to the left of the data you’re looking up.

1. Next, to retrieve the Reading score, you’ll need to select the 3rd column from the leftmost selected column. So, type a 3 and then type another comma.
2. Finally, type FALSE for an exact match, and close the function with a ).

Your final VLOOKUP function should look something like this:

=VLOOKUP(I2,B2:G461,3,FALSE)

When you first press Enter and finish the function, you’ll notice the Reading field will contain an #N/A.

This is because the School field is blank and there is nothing for the VLOOKUP function to find. However, if you enter the name of any high school you want to look up, you’ll see the correct results from that row for the Reading score.

How To Deal With VLOOKUP Being Case- Sensitive

You may notice that if you don’t type the name of the school in the same case as how it’s listed in the dataset, you will not see any results.

This is because the VLOOKUP function is case sensitive. This can be annoying, especially for a very large dataset where the column you’re searching through is inconsistent with how things are capitalized.

To get around this, you can force what you’re searching for to switch to lowercase before looking up the results. To do this, create a new column next to the column you’re searching. Type the function:

=TRIM(LOWER(B2))

This will lowercase the school name and remove any extraneous characters (spaces) that might be on the left or right side of the name.

Hold down the Shift key and place the mouse cursor over the lower right corner of the first cell until it changes to two horizontal lines. Double click the mouse to autofill the entire column.

Finally, since VLOOKUP will try to use the formula rather than the text in these cells, you need to convert them all to values only.  To do this, copy the entire column, right click in the first cell, and paste values only.

Now that all of your data is cleaned up in this new column, slightly modify your VLOOKUP function in Excel to use this new column instead of the previous one by starting the lookup range at C2 instead of B2.

=VLOOKUP(I2,C2:G461,3,FALSE)

Now you’ll notice that if you always type your search in lower case, you’ll always get a good search result.

This is a handy Excel tip to overcome the fact that VLOOKUP is case sensitive.

VLOOKUP Approximate Match

While the exact match LOOKUP example described in the first section of this article is pretty straightforward, the approximate match is a little more complex.

The approximate match is best used to search through number ranges. To do this correctly the search range needs to be properly sorted. The best example of this is a VLOOKUP function to search for a letter grade that corresponds to a number grade.

If a teacher has a long list of student homework grades from throughout the year with a final averaged column, it would be nice to have the letter grade corresponding to that final grade come up automatically.

This is possible with the VLOOKUP function. All that’s required is a lookup table off to the right that contains the appropriate letter grade for each numerical score range.

Now, using the VLOOKUP function and an approximate match, you can find the proper letter grade corresponding to the correct numeric range.

In this VLOOKUP function:

• lookup_value: F2, the final averaged grade
• table_array: I2:J8, The letter grade lookup range
• index_column: 2, the second column in the lookup table
• [range_lookup]: TRUE, approximate match

Once you finish the VLOOKUP function in G2 and press Enter, you can fill in the rest of the cells using the same approach described in the last section. You’ll see all of the letter grades properly filled in.

Note that the VLOOKUP function in Excel searches from the bottom end of the grade range with the assigned letter score to the top of the range of the next letter score.

So, “C” needs to be the letter assigned to the lower range (75), and B is assigned to the bottom (minimum) of its own letter range. VLOOKUP will “find” the result for 60 (D) as the closest approximate value for anything between 60 to 75.

VLOOKUP in Excel is a very powerful function that has been available for a long time. It is also useful for finding matching values anywhere in an Excel workbook.

Keep in mind, however, that Microsoft users who have a monthly Office 365 subscription now have access to a newer XLOOKUP function. This function has more parameters and additional flexibility. Users with a semi-annual subscription will need to wait for the update to roll out in July 2020.

Related posts

• Pridajte trendovú čiaru lineárnej regresie do bodového grafu Excel

• Ako vytvoriť histogram v Exceli

• Ako bezpečne chrániť heslom súbor programu Excel

• Ako vytvoriť vývojový diagram vo Worde a Exceli

• Ako vytvoriť štítky v programe Word z tabuľky programu Excel

• Ako pridať komentáre do bunky pracovného hárka programu Excel

• Ako vypočítať rozptyl v Exceli

• Vložte pracovný hárok programu Excel do dokumentu programu Word

• Ako používať COUNTIFS, SUMIFS, AVERAGEIFS v Exceli

• Ako vytvoriť rozbaľovací zoznam v Exceli

• Ako nájsť a vypočítať rozsah v Exceli

• 13 tipov a trikov OneNote na lepšie usporiadanie poznámok

• Zmapovanie údajov v Exceli

• Prepojenie buniek medzi hárkami a zošitmi v Exceli

• Ako používať funkciu YEARFRAC v Exceli

• Formátovanie buniek pomocou podmieneného formátovania v Exceli

• Ako odpočítať dátumy v Exceli

• Ako skryť hárky, bunky, stĺpce a vzorce v Exceli

• Použite novú funkciu Excel Mobile „Vložiť údaje z obrázku“.

• Ako automaticky zálohovať dokument programu Word na OneDrive