Onko sinulla koskaan ollut suuri laskentataulukko, jossa on tietoja Excelissä^(Excel) ja tarvitset helpon tavan suodattaa ja poimia siitä tiettyjä tietoja? Jos opit käyttämään VLOOKUPia ^(VLOOKUP)Excelissä^(Excel) , voit tehdä tämän haun yhdellä tehokkaalla Excel - toiminnolla.

Excelin VLOOKUP^(VLOOKUP) - toiminto pelottaa^(Excel) monia ihmisiä, koska siinä on paljon parametreja ja sitä voidaan käyttää useilla tavoilla. Tässä artikkelissa opit kaikki tavat , joilla voit käyttää VLOOKUPia ^(VLOOKUP)Excelissä^(Excel) ja miksi toiminto on niin tehokas.

VLOOKUP-parametrit Excelissä^{(VLOOKUP Parameters In Excel)}

Kun alat kirjoittaa =VLOOKUP( mihin tahansa Excelin^(Excel) soluun , näet ponnahdusikkunan, joka näyttää kaikki käytettävissä olevat funktioparametrit.

Tarkastellaan jokaista näistä parametreista ja mitä ne tarkoittavat.

• lookup_value : arvo, jota etsit laskentataulukosta
• table_array : Arkin solualue, josta haluat etsiä
• col_index_num : Sarake, josta haluat hakea tuloksesi
• [range_lookup] : Vastaavuustila ( TRUE = likimääräinen, FALSE = tarkka)

Näiden neljän parametrin avulla voit tehdä monia erilaisia ​​hyödyllisiä hakuja erittäin suurien tietojoukkojen sisältä.

Yksinkertainen VLOOKUP Excel -esimerkki^{(A Simple VLOOKUP Excel Example)}

VLOOKUP ei ole yksi niistä Excelin perustoiminnoista^{(the basic Excel functions)} , jotka olet ehkä oppinut, joten katsotaanpa yksinkertaista esimerkkiä aloittaaksesi.

Seuraavassa esimerkissä käytämme suurta taulukkoa yhdysvaltalaisten^(United) koulujen SAT -^(States) pisteistä . Tämä laskentataulukko sisältää yli 450 koulua sekä yksittäisiä SAT -pisteitä lukemisesta, matematiikasta ja kirjoittamisesta. Voit^(Feel) vapaasti ladata seurataksesi. Siellä on ulkoinen yhteys, joka vetää tiedot, joten saat varoituksen tiedoston avaamisesta, mutta se on turvallista.

Olisi hyvin aikaa vievää etsiä näin laajaa tietoaineistoa löytääksesi sinua kiinnostavan koulun.

Sen sijaan voit luoda yksinkertaisen lomakkeen taulukon sivun tyhjiin soluihin. Suorita tämä haku tekemällä yksi kenttä Koululle^(School) ja kolme muuta kenttää luku-, matematiikasta ja kirjoittamista varten.

Seuraavaksi sinun on käytettävä Excelin ^(Excel)VLOOKUP - toimintoa saadaksesi nämä kolme kenttää toimimaan. Luo Luku^(Reading) - kenttään VHAKU^(VLOOKUP) - funktio seuraavasti:

1. Kirjoita =VLOOKUP(
2. Valitse Koulu^(School) - kenttä, joka tässä esimerkissä on I2 . Kirjoita pilkku.
3. Valitse koko solualue, joka sisältää etsittävät tiedot. Kirjoita pilkku.

Kun valitset alueen, voit aloittaa sarakkeesta, jota käytät etsimiseen (tässä tapauksessa koulun nimisarakkeesta) ja valita sitten kaikki muut sarakkeet ja rivit, jotka sisältävät tiedot.

Huomautus^(Note) : Excelin ^(Excel)VLOOKUP -funktio voi hakea vain hakusarakkeen oikealla puolella olevista soluista. Tässä esimerkissä koulun nimisarakkeen on oltava etsimiesi tietojen vasemmalla puolella.

1. Seuraavaksi sinun on noudettava lukupisteet^(Reading) valitsemalla kolmas sarake vasemmanpuoleisesta valitusta sarakkeesta. Joten kirjoita 3 ja kirjoita sitten toinen pilkku.
2. Kirjoita lopuksi FALSE saadaksesi tarkan vastaavuuden ja sulje funktio painamalla ) .

Viimeisen VLOOKUP- funktiosi pitäisi näyttää suunnilleen tältä:

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

Kun painat Enter -näppäintä ensimmäisen kerran ja lopetat toiminnon, huomaat, että Reading - kentässä on #N/A .

Tämä johtuu siitä, että Koulu^(School) - kenttä on tyhjä eikä VHAKU^(VLOOKUP) - funktiolla ole mitään löydettävää. Jos kuitenkin kirjoitat minkä tahansa lukion nimen, jota haluat etsiä, näet oikeat tulokset kyseiseltä riviltä lukupisteiden^(Reading) osalta .

Kuinka käsitellä VLOOKUP-kirjainkoon erottelua^{(How To Deal With VLOOKUP Being Case- Sensitive)}

Saatat huomata, että jos et kirjoita koulun nimeä samassa tapauksessa kuin se on tietojoukossa, et näe tuloksia.

Tämä johtuu siitä, että VLOOKUP - toiminto on isojen ja pienten kirjainten välinen ero. Tämä voi olla ärsyttävää varsinkin erittäin suuressa tietojoukossa, jossa etsimäsi sarake on ristiriidassa isojen kirjainten kanssa.

Voit kiertää tämän pakottamalla etsimäsi vaihtamaan pieniin kirjaimiin ennen tulosten etsimistä. Voit tehdä tämän luomalla uuden sarakkeen etsimäsi sarakkeen viereen. Kirjoita funktio:

=TRIM(LOWER(B2))

Tämä pienentää koulun nimen ja poistaa kaikki ylimääräiset merkit (välilyönnit), jotka saattavat olla nimen vasemmalla tai oikealla puolella.

Pidä Shift - näppäintä painettuna ja aseta hiiren osoitin ensimmäisen solun oikean alakulman päälle, kunnes se muuttuu kahdeksi vaakaviivaksi. Kaksoisnapsauta^(Double) hiirtä täyttääksesi koko sarakkeen automaattisesti.

Lopuksi, koska VLOOKUP yrittää käyttää kaavaa näiden solujen tekstin sijaan, sinun on muutettava ne kaikki vain arvoiksi. Voit tehdä tämän kopioimalla koko sarakkeen, napsauttamalla ensimmäistä solua hiiren kakkospainikkeella ja liittämällä vain arvot.

Nyt kun kaikki tietosi on siivottu tässä uudessa sarakkeessa, muokkaa hieman Excelin ^(Excel)VLOOKUP - funktiota käyttämään tätä uutta saraketta aiemman sijasta aloittamalla hakualueen^{(the lookup range)} C2:sta B2:n sijaan.

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

Nyt huomaat, että jos kirjoitat haun aina pienillä kirjaimilla, saat aina hyvän hakutuloksen.

Tämä on kätevä Excel-vinkki^{(handy Excel tip)} , jonka avulla voit välttää sen tosiasian, että VLOOKUP on kirjainkoolla.

VLOOKUP Likimääräinen vastaavuus

Vaikka tämän artikkelin ensimmäisessä osassa kuvattu tarkan vastaavuuden HAKU^(LOOKUP) -esimerkki on melko suoraviivainen, likimääräinen vastaavuus on hieman monimutkaisempi.

Likimääräistä vastaavuutta käytetään parhaiten numeroalueiden hakuun. Jotta tämä onnistuisi oikein, hakualue on lajiteltava oikein. Paras esimerkki tästä on VHAKU -funktio, joka etsii kirjainarvosanaa , joka vastaa numeroluokkaa.^(VLOOKUP)

Jos opettajalla on pitkä lista opiskelijoiden kotitehtävien arvosanoista ympäri vuoden ja lopullinen keskiarvosarake, olisi mukavaa, että kyseistä loppuarvosanaa vastaava kirjainarvosana ilmestyisi automaattisesti.

Tämä on mahdollista VLOOKUP - toiminnolla. Tarvitaan vain oikealla oleva hakutaulukko, joka sisältää kunkin numeerisen pistemäärän sopivan kirjaimen arvosanan.

Nyt käyttämällä VLOOKUP - toimintoa ja likimääräistä vastaavuutta, voit löytää oikean kirjaimen arvosanan, joka vastaa oikeaa numeroaluetta.

Tässä VLOOKUP-toiminnossa:

• lookup_value : F2, lopullinen keskiarvoarvosana
• table_array : I2:J8, kirjainluokan hakualue
• index_column : 2, hakutaulukon toinen sarake
• [range_lookup] : TOSI, likimääräinen vastaavuus

Kun olet lopettanut VLOOKUP - toiminnon G2:ssa ja painanut Enter , voit täyttää loput solut käyttämällä samaa lähestymistapaa, joka on kuvattu viimeisessä osassa. Näet kaikki kirjainarvosanat oikein täytettyinä.

Huomaa, että Excelin ^(Excel)VLOOKUP - toiminto etsii arvosana-alueen alimmasta päästä määritetyn kirjaimen pistemäärän kanssa seuraavan kirjaimen pistemäärän alueen yläosaan.

Joten "C":n on oltava alemmalle alueelle (75) määritetty kirjain, ja B on määritetty oman kirjainalueensa alimmalle (minimialueelle). VLOOKUP "löytää" tuloksen arvolle 60 (D) lähimpänä likimääräisenä arvona mille tahansa välillä 60-75.

VLOOKUP Excelissä^(Excel) on erittäin tehokas toiminto, joka on ollut saatavilla pitkään. Se on hyödyllinen myös vastaavien arvojen etsimiseen mistä tahansa Excel-työkirjasta^{(finding matching values anywhere in an Excel workbook)} .

Muista kuitenkin, että Microsoft -käyttäjillä, joilla on kuukausittainen Office 365 -tilaus, on nyt pääsy uudempaan XLOOKUP-toimintoon. Tällä toiminnolla on enemmän parametreja ja lisää joustavuutta. Puolivuosittaisen tilauksen tehneiden käyttäjien on odotettava päivityksen julkaisua heinäkuussa 2020^{(July 2020)} .

How to Use VLOOKUP in Excel

Havе you ever had a large spreadsheet with data in Excel and need an easy way to filter and extract specіfic information frоm it? If you learn how to use VLOOKUP in Excel, you can dо this lоokup with just 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

• Lisää lineaarisen regression trendiviiva Excelin sirontakaavioon

• Kuinka tehdä histogrammi Excelissä

• Kuinka suojata Excel-tiedosto turvallisesti salasanalla

• Vuokaavion luominen Wordissa ja Excelissä

• Tarrojen luominen Wordissa Excel-laskentataulukosta

• Unohdetun Excel XLS -salasanan poistaminen, murtaminen tai rikkominen

• Kuinka piilottaa taulukoita, soluja, sarakkeita ja kaavoja Excelissä

• Kuinka tehdä yksinkertainen kaavio tai kaavio Excelissä

• Kuinka löytää vastaavat arvot Excelissä

• Käytä yhteenvetofunktioita tietojen yhteenvetoon Excelissä

• Kuinka vertailla kahta Excel-tiedostoa ja korostaa eroja

• Kuinka laskea varianssi Excelissä

• Käytä Exceliä laskeaksesi tehokkaan koron nimelliskorosta

• Kuinka käyttää Sparklinesia Excelissä

• Lisää Excel-laskentataulukko Word-dokumenttiin

• Kuinka avata useita Excelin esiintymiä

• Word-asiakirjan automaattinen varmuuskopiointi OneDriveen

• 13 OneNote-vinkkiä ja temppuja muistiinpanojen järjestämiseen paremmin

• Kuinka ymmärtää mitä jos -analyysi Microsoft Excelissä

• Gantt-kaavioiden luominen Microsoft Excelissä