VLOOKUP-funktion käyttäminen arvojen korvaamiseen

Kuka on liian laiska tai jolla ei ole aikaa lukea – katso video. Yksityiskohdat ja vivahteet ovat alla olevassa tekstissä.

Ongelman muotoilu

Meillä on siis kaksi pöytää - tilauspöytä и hinta lista:

Tehtävänä on korvata hinnat hinnastosta automaattisesti tilaustaulukkoon keskittyen tuotteen nimeen, jotta voit myöhemmin laskea hinnan.

Ratkaisu

Excel-funktiojoukossa kategorian alla Viitteet ja taulukot (Haku ja viite) on toiminto VPR (HAKU).Tämä funktio etsii tiettyä arvoa (esimerkissämme sana "omenat") määritetyn taulukon (hinnaston) vasemmanpuoleisesta sarakkeesta liikkuen ylhäältä alas ja sen löydettyään näyttää viereisen solun sisällön (23 ruplaa) .Kaavamaisesti tämän funktion toiminta voidaan esittää Joten:

Toiminnon jatkokäytön helpottamiseksi tee yksi asia kerralla – anna hinnaston solualueelle oma nimesi. Valitse tätä varten kaikki hinnaston solut paitsi "otsikko" (G3: H19), valitse valikosta Lisää – Nimi – Määritä (Lisää — Nimi — Määritä) tai paina CTRL + F3 ja kirjoita mikä tahansa nimi (ei välilyöntejä), kuten Hinta… Nyt voit käyttää tätä nimeä jatkossa linkittämiseen hinnastoon.

Nyt käytämme toimintoa VPR… Valitse solu, johon se syötetään (D3) ja avaa välilehti Kaavat – funktion lisäys (Kaavat — Lisää funktio)… Luokassa Viitteet ja taulukot (Haku ja viite) löytää funktion VPR (HAKU) ja paina OK… Näyttöön tulee ikkuna funktion argumenttien syöttämiseen:

VLOOKUP-funktion käyttäminen arvojen korvaamiseen

Täytämme ne vuorotellen:

  • Haluttu arvo (Hakuarvo) – tuotteen nimi, joka toiminnon pitäisi löytää hinnaston vasemmanpuoleisesta sarakkeesta. Meidän tapauksessamme sana "omenat" solusta B3.
  • Pöytä (Pöytäryhmä) – taulukko, josta on otettu halutut arvot uXNUMXbuXNUMX, eli hinnastomme. Käytämme viitteenä omaa aiemmin annettua nimeämme "Hinta". Jos et antanut nimeä, voit vain valita taulukon, mutta älä unohda painaa nappia F4kiinnittääksesi linkin dollarimerkeillä, koska muuten se liukuu alas kopioitaessa kaavaamme alas muihin sarakkeen D3:D30 soluihin.
  • Sarakkeen_numero (Sarakkeen indeksinumero) – Sarjanumero (ei kirjain!) Hinnaston sarakkeesta, josta otamme hinta-arvot. Hinnaston ensimmäinen sarake nimineen on numeroitu 1, joten tarvitsemme hinnan sarakkeesta 2.
  • interval_lookup (Alueen haku) – tähän kenttään voidaan syöttää vain kaksi arvoa: FALSE tai TRUE:
      • Jos arvo syötetään 0 or VAHELEMINEN (VÄÄRÄ), tämä tarkoittaa itse asiassa, että vain haku on sallittu tarkka ottelu, eli jos toiminto ei löydä hinnaston tilaustaulukossa määritettyä ei-standardista tuotetta (jos esimerkiksi syötetään "Coconut"), se tuottaa #N/A (ei tietoja) -virheen.
      • Jos arvo syötetään 1 or TOSI (TOTTA), tämä tarkoittaa, että sallit haun ei tarkkaa, vaan likimääräinen ottelu, eli "kookos" tapauksessa toiminto yrittää löytää tuotteen, jonka nimi on mahdollisimman lähellä "kookosta" ja palauttaa hinnan tälle nimelle. Useimmissa tapauksissa tällainen likimääräinen korvaaminen voi huijata käyttäjää korvaamalla väärän tuotteen arvon, joka todella oli siellä! Joten useimmissa todellisissa liiketoimintaongelmissa likimääräinen haku on parasta olla sallimatta. Poikkeuksena on, kun etsimme numeroita emmekä tekstiä – esimerkiksi laskettaessa askelalennuksia.

Kaikki! Jäljelle jää painaminen OK ja kopioi syötetty funktio koko sarakkeeseen.

# N / A -virheet ja niiden tukahduttaminen

Toiminto VPR (HAKU) palauttaa virheen #N/A (#N/A) jos:

  • Tarkka haku käytössä (argumentti Intervallinäkymä = 0) ja haluttua nimeä ei ole Pöytä.
  • Mukana karkea haku (Intervallinäkymä = 1), mutta Pöytä, jossa haku tapahtuu, ei ole lajiteltu nimien nousevaan järjestykseen.
  • Sen solun muoto, josta nimen vaadittu arvo tulee (tapauksessamme esim. B3) ja taulukon ensimmäisen sarakkeen solujen muoto (F3: F19) ovat erilaisia ​​(esim. numeerinen ja teksti ). Tämä tapaus on erityisen tyypillinen käytettäessä numeerisia koodeja (tilinumerot, tunnisteet, päivämäärät jne.) tekstinimien sijaan. Tässä tapauksessa voit käyttää toimintoja Ч и TEKSTI muuntaa tietomuotoja. Se näyttää suunnilleen tältä:

    =HAKU(TEKSTI(B3),hinta,0)

    Voit lukea tästä lisää täältä.

  • Funktio ei löydä vaadittua arvoa, koska koodi sisältää välilyöntejä tai näkymättömiä ei-tulostettavia merkkejä (rivinvaihtoja jne.). Tässä tapauksessa voit käyttää tekstifunktioita TRIM (TRIMMATA) и PAINAA(PUHDAS) poistaaksesi ne:

    =VHAKU(TRIMSPACES(PUHDAS(B3)),hinta,0)

    =VHAKU(TRIM(PUHDISTA(B3));hinta;0)

Voit estää virheilmoituksen # N / A (#N/A) Jos funktio ei löydä tarkkaa vastaavuutta, voit käyttää toimintoa IFERROR (IFERROR)… Joten esimerkiksi tämä rakenne sieppaa kaikki VLOOKUP:n luomat virheet ja korvaa ne nolilla:

= IFERROR (VHAKU (B3, hinta, 2, 0), 0)

= IFERROR (VHAKU (B3; hinta; 2; 0); 0)

PS

Jos sinun ei tarvitse poimia yhtä arvoa, vaan koko joukko kerralla (jos niitä on useita), sinun on shamanisoitava taulukkokaavalla. tai käytä Office 365:n uutta XLOOKUP-ominaisuutta.

 

  • VLOOKUP-toiminnon parannettu versio (VLOOKUP 2).
  • Nopea askel-alennusten laskeminen VLOOKUP-toiminnolla.
  • Kuinka tehdä "vasen VLOOKUP" käyttämällä INDEX- ja MATCH-toimintoja
  • Kuinka käyttää VLOOKUP-toimintoa lomakkeiden täyttämiseen luettelon tiedoilla
  • Kuinka vetää taulukosta ei ensimmäinen, vaan kaikki arvot kerralla
  • VLOOKUP2- ja VLOOKUP3-toiminnot PLEX-lisäosasta

 

Jätä vastaus