Meny
Är gratis
registrering
Hem  /  Program/ Hur man använder vp-funktionen i Excel. Hur vpr-funktionen fungerar

Hur man använder vpr-funktionen i Excel. Hur vpr-funktionen fungerar

VLOOKUP är en Excel-funktion som låter dig söka i en specifik kolumn med hjälp av data från en annan kolumn. VLOOKUP-funktionen i Excel används också för att överföra data från en tabell till en annan. Det finns tre villkor:

  1. Tabeller måste finnas i samma Excel-arbetsbok.
  2. Du kan bara söka bland statiska data (inte formler).
  3. Söktermen måste finnas i den första kolumnen av den data som används.

VLOOKUP formel i Excel

Syntaxen för VLOOKUP i Russified Excel är:

VLOOKUP (sökkriterium; dataintervall; resultatkolumnnummer; sökvillkor)

Argumenten inom parentes krävs för att hitta det slutliga resultatet.

Sökkriterier

Mobiladress Excel arbetsblad, som indikerar data för att söka i tabellen.

Dataområde

Alla adresser, bland vilka sökningen utförs. Den första kolumnen ska vara den där söktermen finns.

Kolumnnummer för totalt

Kolumnnummer där värdet kommer att tas från när en matchning hittas.

Sökvillkor

Ett booleskt värde (true / 1 eller false / 0) som indikerar en ungefärlig matchning att söka efter (1) eller en exakt matchning (0).

VLOOKUP i Excel: funktionsexempel

Funktionens princip är enkel. Det första argumentet innehåller sökkriterierna. Så snart en matchning hittas i tabellen (andra argumentet) tas information från den obligatoriska kolumnen (tredje argumentet) i den hittade raden och ersätts i cellen med formeln.
En enkel tillämpning av VLOOKUP är att hitta värden i ett Excel-kalkylblad. Det spelar roll i stora datamängder.

Låt oss hitta antalet faktiskt släppta produkter efter månadens namn.
Resultatet visas till höger om tabellen. I cellen med adressen H3 kommer vi att ange önskat värde. I exemplet skulle detta vara månadens namn.
I cell H4 anger du själva funktionen. Detta kan göras manuellt eller så kan du använda guiden. För att ringa, placera pekaren över cell H4 och klicka på Fx-ikonen bredvid formelfältet.


Fönstret Excel Function Wizard öppnas. Det är nödvändigt att hitta VLOOKUP i den. Välj "Komplett alfabetisk lista" från rullgardinsmenyn och börja skriva UPPLÖSNING. Markera den hittade funktionen och tryck på "OK".


Fönstret VLOOKUP för Excel-kalkylarket visas.


För att ange det första argumentet (kriteriet), placera markören på den första raden och klicka på cell H3. Dess adress kommer att visas på raden. För att välja ett område, placera markören på den andra raden och börja välja med musen. Fönstret kommer att kollapsa till en linje. Detta görs så att fönstret inte stör att se hela intervallet och inte stör att utföra åtgärder.


När du är klar med valet och släpp vänster knapp mus, kommer fönstret att återgå till sitt normala tillstånd, och adressen till området kommer att visas på den andra raden. Det beräknas från den övre vänstra cellen till den nedre högra cellen. Deras adresser separeras av operatorn ":" - alla adresser mellan den första och den sista tas.


Flytta markören till den tredje raden och läs från vilken kolumn data tas när en matchning hittas. I vårt exempel är detta 3.


Lämna den sista raden tom. Som standard kommer värdet att vara lika med 1, låt oss se vilket värde vår funktion visar. Klicka på OK.


Resultatet är nedslående. "N/A" betyder ogiltiga data för funktionen. Vi har inte angett ett värde i cell H3, och funktionen letar efter ett tomt värde.


Låt oss ange månadens namn så ändras värdet.


Bara det stämmer inte överens med verkligheten, eftersom det faktiska antalet produkter som släpptes i januari är 2000.
Detta är effekten av argumentet Sökvillkor. Ändra den till 0. För att göra detta, placera pekaren över cellen med formeln och tryck på Fx igen. I fönstret som öppnas anger du "0" på sista raden.


Klicka på OK. Som du kan se har resultatet förändrats.


För att kontrollera det andra villkoret från början av vår artikel (funktionen söker inte bland formler), kommer vi att ändra villkoren för funktionen. Låt oss öka intervallet och försöka mata ut värdet från kolumnen med beräknade värden. Ange värdena som visas på skärmdumpen.


Klicka på "Ok". Som du kan se blev sökresultatet 0, även om tabellen innehåller ett värde på 85%.


VLOOKUP i Excel "förstår" endast fasta värden.

Jämföra data från två Excel-tabeller

VLOOKUP i Excel kan användas för att jämföra data från två tabeller. Anta till exempel att vi har två arbetsblad med data om resultatet från två workshops. Vi kan matcha den faktiska releasen för båda. Kom ihåg att för att växla mellan ark, använd deras genvägar längst ned i fönstret.

På två blad har vi samma tabeller med olika data.

Som du kan se är deras releaseplan densamma, men den faktiska är annorlunda. Att byta och jämföra rad för rad, även för små mängder data, är mycket obekvämt. På det tredje arket skapar vi en tabell med tre kolumner.

Låt oss ange VLOOKUP-funktionen i cell B2. Som det första argumentet kommer vi att indikera cellen med månaden på det aktuella arket och välja intervallet från arket "Shop1". För att undvika att intervallet ändras under kopiering, tryck på F4 efter att du har valt intervall. Detta kommer att göra länken absolut.


Sträck ut formeln för att fylla hela kolumnen.

På samma sätt anger du formeln i nästa kolumn, välj bara intervallet på Workshop2-arket.


Efter kopiering får du en sammanfattande rapport från två ark.

Byte av data från en Excel-tabell till en annan

Denna åtgärd utförs på samma sätt. För vårt exempel kan du inte skapa en separat tabell, utan helt enkelt skriva in funktionen i en kolumn i någon av tabellerna. Låt oss visa med exemplet med den första. Ställ pekaren till den sista kolumnen.


Och i cell G3 lägg funktionen VLOOKUP. Vi tar sortimentet igen från det intilliggande arket.


Som ett resultat kommer kolumnen i den andra tabellen att kopieras till den första.


Det är all information om det oansenliga, men användbar funktion VLOOKUP i Excel för Dummies. Vi hoppas att det hjälper dig att lösa problem.

Ha en bra dag!

Exempel på användning Excel-formler med VLOOKUP-funktionen kan du ta reda på dess funktionsprincip och undvika fel:

  1. Funktionen tar fart ställa in parametrar, med kriteriet från den första tabellen, hänvisar till det andra intervallet, varifrån värdena kommer att "dras upp".
  2. Från den översta cellen i kriteriekolumnen tittar den igenom alla värden.
  3. Efter att ha hittat matchningar med de angivna kriterierna, räknar den det angivna antalet kolumner till höger och hamnar i cellen där det önskade värdet finns, som "dras" in i cellen där formeln anges.

Ett exempel på hur man använder VLOOKUP-funktionen

I det givna exemplet måste du ta reda på kostnaden för de sålda varorna. För att beräkna det måste du hitta produkten av kvantitet och priser (kolumner med data finns i intilliggande kolumner). Formeln för produkten av två celler föreskrivs i en tom kolumn bredvid den och sträcks ner till slutet av varulistan.

Den ursprungliga informationen kan finnas i olika intervall och i en annan ordning. Den första tabellen anger mängden sålda varor:

Den andra är för priser:

Om varorna i båda tabellerna sammanfaller, genom att använda tangentkombinationen Ctrl + C och Ctrl + V, kan prisindikatorerna enkelt ersätta kvantiteten. Men tabellerna har en annan ordningsföljd.

Information om många produkter stämmer inte överens med de intilliggande indikatorerna. I det här fallet är möjligheten att skriva formeln för multiplikation och "dra" nedåt för alla positioner utesluten.

Hur man byter ut priser från den andra tabellen till motsvarande kvantitetsindikatorer från den första, det vill säga priset på position A till kvantiteten på position A, pris B till kvantitet B.

Vid användning av SÖKUPPSÖKNING funktionen "dras" priserna från den andra tabellen upp till kvantiteten från den första tabellen, enligt varornas namn.

Procedur:

I den första tabellen måste du lägga till en ny kolumn där priserna kommer att ställas in med hjälp av VLOOKUP-formeln.

VLOOKUP-funktionen anropas via funktionsguiden eller skrivs manuellt.

Att anropa alternativet via guiden består i att aktivera cellen där formeln kommer att indikeras och klicka på f (x)-knappen i början av formelfältet. I den visade dialogrutan i guiden måste du ange VLOOKUP från den föreslagna listan.

VLOOKUP-formeln i Excel för Dummies kräver korrekt ifyllning av fälten i dialogrutan Funktionsguide:

  1. Den första kolumnen "Sökvärde" låter dig ställa in kriterierna för cellen där formeln ska skrivas. I det här exemplet innehåller cellen objektet "A".
  2. Nästa rad"Tabell". När du anger ett dataintervall kan du hitta önskade värden. Till exempel användes den andra tabellen med priser. Eftersom priserna "drar upp" till kvantiteten. Det är viktigt att ta hänsyn till behovet av att kolumnen längst till vänster (först till vänster) i det valda intervallet innehåller liknande sökkriterier. I exemplet är detta kolumnen med produktens namn. Därefter markeras tabellen till höger upp till kolumnen som innehåller nödvändiga data (priser). Du kan utöka urvalet till höger, men detta kommer inte att påverka någonting, eftersom kolumnen med de nödvändiga indikatorerna kommer att identifieras unikt nästa parameter... Det är viktigt att de valda tabellerna börjar med en kriteriekolumn och fångar den datakolumn av intresse.
  3. "Kolumnnummer" - siffrorna med vilka kolumnerna med nödvändiga data (priser) separeras från kolumnen med kriterierna (produktnamn). Nedräkningen börjar från själva kriteriekolumnen. Om båda kolumnerna är placerade sida vid sida i den andra tabellen, bör du ange siffran 2 (den första är kriterierna, den andra är priserna). Det är möjligt att placera data i förhållande till kriterierna för 10 eller 20 kolumner. Det spelar ingen roll, Excel gör de korrekta beräkningarna.
  4. Den sista kolumnen är "Intervallvy", där sökalternativen anges: exakt (0) eller ungefärlig (1) matchning av kriterierna. Nu ska du ange 0 (eller FALSK).

Det återstår att klicka på OK eller Enter-knappen. Om uppgifterna är korrekt inmatade och det finns ett kriterium i båda tabellerna, kommer en viss indikator att visas i stället för den angivna formeln. Det räcker med att sträcka (eller kopiera) formeln ner till den sista raden i tabellen.

Ytterligare beräkning görs genom att bestämma produkten av kvantitet och pris, sträcka formeln ner till slutet av tabellen, eftersom pris-kvantitet-paren redan är desamma.

Ett alternativt alternativ är att skriva VLOOKUP-formeln i en cell genom att lägga till ett ";"-tecken mellan parametrarna.

Det är tillåtet att ordinera namnet "vpr" med små bokstäver, registret spelar ingen roll.

Hur använder jag Paste Special?

Som ett resultat av användningen av VLOOKUP-funktionen förblir de två tabellerna länkade. När du gör ändringar i prislistan ändras även kostnaden för de varor som tas emot på lagret. För att undvika denna situation bör du använda "Klistra in special".

  • Välj kolumnen med de angivna priserna.
  • Högerklicka - "Kopiera".
  • Utan att ta bort markeringen, högerklicka - "Klistra in special".
  • Ställ kryssrutan mot "Värden". OK.

Endast värden kommer att finnas kvar i cellerna, formeln kommer att avbrytas.

Hur jämför man snabbt två tabeller med VLOOKUP?

Funktionen används för att kartlägga värden i stora tabeller. Till exempel om det sker förändringar i prislistan. Du vill jämföra gamla priser med nya.

Procedur:

I den gamla prislistan behöver du skapa en kolumn "Nya priser".

Markera den första cellen och välj formeln SÖKUPPSÖKNING. Ställ in argument (se ovan). Till exempel:

Den angivna formeln informerar om behovet av att ta namnet på produkten från intervallet A2: A15, se det i "Nytt pris" med hjälp av kolumn A. Använd sedan data från den andra kolumnen i den nya prislistan (nya priser) och skriv in dem i cell C2.

Genom att välja ett sätt att visa data kan du jämföra dem, bestämma skillnaden i siffror och procentsatser.

VLOOKUP formel i Excel med ett antal villkor

Ovan övervägdes exempel på analys med ett villkor - produktens namn. I praktiken kan det bli nödvändigt att jämföra flera intervall med data och välja värden enligt 2, 3 kriterier. Nedan finns en tabell för en visuell översikt:

För att söka efter priset till vilket wellpappen kom från JSC "Vostok", bör du skapa 2 villkor: med materialets namn och av leverantörer.

Den största svårigheten ligger i närvaron av flera produktnamn från en leverantör.

Steg-för-steg-instruktionerna inkluderar ett antal åtgärder:

Lägga till kolumnen längst till vänster i tabellen (viktigt!), Kombinera "Leverantörer" och "Material".

Kombinera de nödvändiga kriterierna analogt:

Placera markören på rätt plats och ställ in argumenten för formeln:

Excel söker efter rätt pris.

Detaljerad övervägande av formeln:

  1. Vad ska man söka.
  2. Var ska man leta.
  3. Vilken data ska man ta.

VLOOKUP formler och rullgardinslistor

Låt vissa uppgifter matas in i form av en rullgardinslista. I det här exemplet är de "Material". Det är viktigt att konfigurera funktionen så att priset visas när du väljer ett namn.

För att skapa en rullgardinslista måste du utföra ett antal åtgärder:

Placera markören i cell E8, där du planerar att placera listan.

Öppna fliken "Data". Kontrollera datamenyn.

Välj datatyp - "Listor". Källor - ett intervall med namn på material.

När du klickar på OK skapas en rullgardinslista.

Det återstår att ställa in en funktion som gör det möjligt att, när du väljer material, visa motsvarande värden i priskolumnen. Placera markören i cell E9 (där priset kommer att visas).

Öppna "Funktionsguiden" och välj VLOOKUP.

Det första argumentet är "Sökvärde" - celler med rullgardinslistor. Tabell - en serie med namn på materialet och priser. Kolumn - 2. Funktionen kommer att visas i följande format:

Det återstår att trycka på ENTER och njuta av resultatet.

Vid materialbyte ändras priset:

Så här fungerar en rullgardinslista i Excel med funktionen VLOOKUP. Allt görs i autoläge, på några sekunder.

Funktioner för att arbeta med VLOOKUP-formeln

Innan du använder VLOOKUP-funktionen i Excel bör du bekanta dig med dess funktioner:

  1. När du använder alternativet för ett antal celler genom att ange en formel i en av dem och kopiera till resten, är det viktigt att kontrollera referensernas relativitet och absolutitet. I VLOOKUP måste kriterierna (första fälten) ha relativa referenser (utan $), vilket bestäms av närvaron av sina egna kriterier för varje cell. Områden måste ha absoluta referenser (områdesadresser anges med $). Annars, när du kopierar formler, kommer intervallet att "flyta" ner och många indikatorer kommer inte att visas i sökningen, eftersom det inte finns någonstans att söka.
  2. Kolumnnummer som anges i det tredje "Kolumnnummer"-fältet när du använder funktionsguiden måste räknas från kriteriekolumnen.
  3. Om det inte finns några kriterier i tabellerna där uppgifterna söks fram visas felet # N/A, vilket gör att det blir svårt att beräkna summorna (summa, medelvärde etc.). För att lösa problemet kan du använda SUMIF-funktionen (istället för VLOOKUP) eller ESLIOSHIBKA (sätta före VLOOKUP).
  4. När du använder numeriska värden istället för kriterier (koder, artikelnummer) har VLOOKUP-formeln ökat känsligheten för cellformat. Om det finns kriterier i ett numeriskt format i en tabell och i ett textformat i en annan, och om indikatorerna helt matchar, kommer # N / A-felet att visas. Det räcker att kontrollera om fältformatet matchar kriterierna och göra dem identiska eller använda SUMIF-funktionen (formatet spelar ingen roll för det).
  5. Det rekommenderas inte att använda långa kriterier för att minska sannolikheten för "slumpmässiga" skillnader. Till exempel kommer närvaron av ett extra mellanslag mellan ord eller en felaktig bokstav att leda till bristande jämförbarhet av samma kriterievärden. Artikelnummer eller produktstreckkoder är bra, men flerordsnamn rekommenderas inte för kriterier.
  6. VLOOKUP-funktionerna från tabellen med nödvändiga data ger den första indikatorn från toppen. Om det finns ett antal celler med samma kriterier i den andra tabellen, varifrån data "dras", så fångas den första indikatorn från toppen inom det valda VLOOKUP-intervallet. Detta är viktigt att tänka på. Till exempel, om det är nödvändigt att strama åt kvantiteten från ett annat intervall till priset på varor, och den här produkten upprepas i flera kolumner, kommer de första indikatorerna (kvantiteten) från toppen att komma ikapp priset, andra kommer att finnas kvar ignoreras.
  7. Närvaron av den sista parametern i form av siffran 0 (noll) krävs. Annars fungerar formeln snett.
  8. Efter att ha använt VLOOKUP-funktionen, rekommenderas att du omedelbart raderar formeln och lämnar bara de erhållna resultaten. Förfarandet kräver att man väljer ett intervall med de erhållna resultaten, använder knappen "kopiera" och ställer in värdena till denna plats med specialinsats... När du placerar tabeller i olika Excel-arbetsböcker blir det bekvämare att bryta yttre förbindelser(lämnar data) med hjälp av verktyget, under Data → Redigera länkar.

Efter att ha anropat funktionen för att bryta externa länkar kommer en dialogruta upp, där det återstår att aktivera knappen "Kross länken" och "Stäng".

Detta tar bort alla externa länkar på en gång.

  1. Ett alternativt SÖKUPPSÄTTNING-alternativ är HSLÖKNING-alternativet. Skillnaden är den horisontella vyn av datalistan.

Säkert många aktiva användare kalkylbladsredigerare Excel var periodvis tvungen att hantera situationer där det blev nödvändigt att ersätta värden från en tabell till en annan. Tänk dig bara att en viss produkt har kommit in i ditt lager. Vi har två filer till vårt förfogande: en med en lista över namnen på de mottagna varorna, den andra - prislistan för just denna produkt. Efter att ha öppnat prislistan finner vi att det finns fler positioner i den och de ligger inte i samma ordningsföljd som i filen med namnlistan. Knappast någon av oss kommer att gilla idén att kontrollera båda filerna och överföra priser från ett dokument till ett annat manuellt. Naturligtvis, i fallet när vi pratar om 5-10 positioner, är mekanisk datainmatning fullt möjlig, men vad händer om antalet föremål överstiger 1000? I det här fallet kommer Excel och dess magiska VLOOKUP-funktion (eller vlookup, om vi pratar om den engelska versionen av programmet) att hjälpa oss att klara det monotona arbetet.


Så i början av vårt arbete med att konvertera data från en tabell till en annan, kommer det att vara lämpligt att göra en liten översikt över VLOOKUP-funktionen. Som du säkert redan förstått låter vlookup dig överföra data från en tabell till en annan, och fyller därmed i de celler vi behöver automatiskt. För att VLOOKUP-funktionen ska fungera korrekt, var uppmärksam på närvaron av sammanslagna celler i rubrikerna i din tabell. Om det finns några måste du bryta ner dem.

Låt oss säga att vi måste fylla i "Beställningstabellen" med data från "Prislistan"

Så vi står inför uppgiften att överföra priserna på befintliga varor till en tabell med deras namn och beräkna den totala kostnaden för varje produkt. För att göra detta måste vi utföra följande algoritm:

  1. Ta först med Excel kalkylark i den form du behöver. Lägg till två kolumner med namnet "Pris" och "Kostnad" till den förberedda datamatrisen. Välj valutaformat för celler i det nybildade kolumnintervallet.
  2. Aktivera nu den första cellen i "Pris"-blocket och anropa "Funktionsguiden". Detta kan göras genom att trycka på "fx"-knappen som finns framför formelfältet, eller genom att hålla ner tangentkombinationen "Skift + F3". Hitta kategorin "Referenser och matriser" i dialogrutan som öppnas. Här är vi inte intresserade av något annat än VLOOKUP-funktionen. Välj den och klicka på "OK". Förresten, det ska sägas att VLOOKUP-funktionen kan anropas via fliken Formler, vars rullgardinslista också innehåller kategorin Referenser och Arrayer.
  3. Efter att ha aktiverat SÖKNING, öppnas ett fönster med en lista över argumenten för den funktion du har valt framför dig. I fältet "Sökvärde" måste du ange dataintervallet som finns i den första kolumnen i tabellen med en lista över mottagna varor och deras kvantitet. Det vill säga, du måste berätta för Excel exakt vad den ska hitta i den andra tabellen och överföra den till den första.
  4. När det första argumentet har indikerats kan du gå vidare till det andra. I vårt fall är pristabellen det andra argumentet. Placera muspekaren i argumentfältet och flytta till arket med prislistan. Välj manuellt ett intervall med celler placerade i området för kolumner med namn på kommersiella produkter och deras priser. Tala om för Excel vilka värden som ska mappas till VLOOKUP-funktionen.
  5. För att Excel inte ska bli förvirrad och hänvisa till den data du behöver är det viktigt att fixa länken som ges till den. För att göra detta, välj önskade värden i fältet "Tabell" och tryck på F4-tangenten. Om allt är gjort korrekt bör tecknet $ visas på skärmen.
  6. Nu går vi till argumentfältet "Sidnummer" och ger det värdet "2". Detta block innehåller all data som behöver skickas till vårt kalkylblad, och därför är det viktigt att tilldela ett falskt värde till "Intervallvyn" (ställ positionen till "FALSK"). Detta är nödvändigt så att VLOOKUP-funktionen endast fungerar med exakta värden och inte avrundar dem.

Nu när alla nödvändiga åtgärder har slutförts behöver vi bara bekräfta dem genom att trycka på "OK"-knappen. Så snart informationen i den första cellen ändras måste vi använda funktionen VLOOKUP på hela Excel-dokumentet. För att göra detta räcker det att multiplicera VLOOKUP över hela kolumnen "Pris". Detta kan göras genom att dra det nedre högra hörnet av cellen med det ändrade värdet till botten av kolumnen. Om allt löste sig, och uppgifterna ändrades som vi behövde, kan vi börja beräkna den totala kostnaden för våra varor. För att utföra denna åtgärd måste vi hitta produkten av två kolumner - "Mängder" och "Priser". Eftersom Excel innehåller alla matematiska formler, kan beräkningen skickas till "Formelfältet" med hjälp av den redan välkända "fx"-ikonen.

En viktig punkt

Det verkar som att allt är klart och VLOOKUP klarade av vår uppgift, men så var inte fallet. Faktum är att VLOOKUP-funktionen fortfarande är aktiv i kolumnen "Pris", och detta faktum bevisas av visningen av den senare i formelfältet. Det vill säga att båda våra tabeller förblir länkade till varandra. En sådan tandem kan leda till att när uppgifterna i tabellen med prislistan ändras, kommer informationen i vår arbetsfil med varulistan också att ändras.

Denna situation undviks bäst genom att dela upp de två tabellerna. För att göra detta måste vi välja cellerna som är i intervallet för kolumnen "Pris" och högerklicka på den. I fönstret som öppnas, välj och aktivera alternativet "Kopiera". Efter det, utan att ta bort markeringen från det valda området av celler, tryck på höger musknapp igen och välj alternativet "Klistra in special".

Om du aktiverar det här alternativet öppnas en dialogruta på skärmen där du måste markera rutan bredvid kategorin "Värde". Bekräfta åtgärden du har vidtagit genom att klicka på knappen "OK".

Gå tillbaka till vår formelfält och kontrollera om UPPSÖKNINGSfunktionen är aktiv i kolumnen Pris. Om du i stället för formeln bara ser numeriska värden, fungerade allt och funktionen VLOOKUP är inaktiverad. Det vill säga sambandet mellan de två Excel-filer trasig, och det finns inget hot om oplanerade ändringar eller radering av data bifogad från tabellen med pris. Nu kan du säkert använda ett kalkylarksdokument och inte oroa dig för vad som kommer att hända om "Prislistan" stängs eller flyttas till en annan plats.

Hur jämför man två tabeller i Excel?

Med VLOOKUP-funktionen kan du matcha flera olika betydelser för att till exempel jämföra hur priserna på en befintlig produkt har förändrats. För att göra detta måste du skriva VLOOKUP i en tom kolumn och hänvisa funktionen till de ändrade värdena som finns i en annan tabell. Det är bäst om kolumnen "Nytt pris" finns direkt efter kolumnen "Pris". Denna lösning gör att du kan göra prisändringar mer visuella för jämförelse.

Förmåga att arbeta med flera förutsättningar

En annan obestridlig fördel med VLOOKUP-funktionen är dess förmåga att arbeta med flera parametrar som är inneboende i din produkt. För att hitta en produkt med två eller flera egenskaper måste du:

  1. Skapa två (eller, om nödvändigt, fler) söktermer.
  2. Lägg till en ny kolumn, där under driften av funktionen kommer alla andra kolumner att läggas till, genom vilken produkten söks.
  3. I den resulterande kolumnen, enligt ovanstående algoritm, anger vi den redan bekanta formeln för VLOOKUP-funktionen.

Avslutningsvis är det värt att säga att Excels stöd för en funktion som VLOOKUP gör det mycket lättare att arbeta med tabellinformation. Använd gärna VLOOKUP med stor mängd data, för oavsett hur de är formaterade är principen för funktionen alltid densamma. Allt du behöver göra är att definiera dess argument korrekt.

Använder funktionen VLOOKUP när du arbetar i Excel kan du extrahera den information som krävs från kalkylblad. Excel erbjuder flera funktioner för dessa ändamål, men VLOOKUP den vanligaste bland dem. I den här handledningen kommer vi att introducera funktionen VLOOKUP, och överväg också dess kapacitet med ett enkelt exempel.

Fungera VLOOKUP(vertikal skanning) letar efter ett värde i kolumnen längst till vänster i intervallet som undersöks och returnerar sedan resultatet från cellen som är i skärningspunkten mellan den hittade raden och den angivna kolumnen.

Exempel 1

Till exempel visar bilden nedan en lista med 10 efternamn, varje efternamn har sitt eget nummer. Det är nödvändigt att extrahera efternamnet vid det angivna numret.

Använder funktionen VLOOKUP det är ganska enkelt att göra så här:

Det kan ses från formeln att det första argumentet för funktionen VLOOKUPär cell C1, där vi anger önskat nummer. Den andra är A1: B10-serien, som visar var du ska leta. Och det sista argumentet är kolumnnumret som resultatet ska returneras från. I vårt exempel är detta den andra kolumnen. Genom att klicka Stiga på, vi får det önskade resultatet:

Exempel 2

Låt oss ta ett annat exempel. Bilden nedan visar samma 10 namn som tidigare, endast siffrorna följer med pass.

Om vi ​​försöker hitta efternamnet för ett icke-existerande nummer (till exempel 007), kommer formeln, istället för att ge ett fel, säkert att returnera resultatet till oss. Hur kan det vara såhär?

Poängen är att funktionen VLOOKUP har också ett fjärde argument, som låter dig specificera den så kallade intervallskanningen. Det kan ha två betydelser: SANT och FALSKT. Dessutom, om argumentet utelämnas, är detta liktydigt med sanningen.

I fallet när det fjärde argumentet är TRUE, letar funktionen först efter en exakt matchning, och om det inte finns någon sådan matchning, då den närmaste, som är mindre än den angivna. Det är därför funktionen VLOOKUP returnerade efternamnet "Panchenko". Om vi ​​hade gett "008", så skulle formeln också ha returnerat "Panchenko".

I fallet när det fjärde argumentet för funktionen VLOOKUP har det logiska värdet FALSE, söker funktionen efter en exakt matchning. Till exempel, i figuren nedan kommer formeln att returnera ett fel eftersom ingen exakt matchning hittades.

Om det fjärde argumentet för funktionen VLOOKUPär SANT eller utelämnat, måste kolumnen längst till vänster sorteras i stigande ordning. Om detta inte görs, funktionen VLOOKUP kan ge fel resultat.

För dem som gillar att skapa inte vertikala, utan horisontella tabeller, finns det en analog i Excel VLOOKUP men för horisontell sökning.

Horisontell VLOOKUP i Excel

Microsoft Excel har en funktion GPR(horisontell vy) som är väldigt lik VLOOKUP, den enda skillnaden är att intervallet inte ses vertikalt utan horisontellt. GPR letar efter satt värde v översta raden intervall och returnerar resultatet från cellen som är i skärningspunkten mellan den hittade kolumnen och den angivna raden.

Om du representerar ovanstående exempel i horisontell form kommer formeln att se ut så här:

Som du kan se är allt ganska enkelt!

Detta avslutar vår lektion. Idag träffade vi, förmodligen, det mest populära Microsoft Excel-verktyget - VLOOKUP-funktion och analyserade dess kapacitet för flera enkla exempel... Hoppas denna handledning var användbar för dig. Lycka till och framgång med att lära sig Excel.

VLOOKUP () funktion , engelsk version av VLOOKUP (), letar efter ett värde i den första (längst till vänster) kolumnen i en tabell och returnerar ett värde från samma rad men en annan kolumn i tabellen.

Funktionen VLOOKUP () är en av de mest använda i EXCEL, så låt oss titta på den i detalj.

I den här artikeln har ett icke-standardiserat tillvägagångssätt valts: tonvikten ligger inte på själva funktionen, utan på de uppgifter som kan lösas med dess hjälp.

Funktionssyntax

VLOOKUP (uppslagsvärde; tabell; kolumnnummer; områdesvy)

Lookup_valueär värdet du försöker hitta i datakolumnen. Lookup_value kan vara ett nummer eller text, men oftast är det ett nummer som det söks på. Värdet du letar efter måste finnas i den första (längst till vänster) kolumnen i cellintervallet som anges i tabell .

Bord - en referens till ett cellintervall. Den vänstra kolumnen i tabellen söks efter Lookup_value, och från kolumnerna till höger visas motsvarande resultat (även om du i princip kan mata ut värdet från den vänstra kolumnen (i det här fallet kommer det att vara lookup_value)). Ofta kallas den vänstra kolumnen nyckel-... Om den första kolumnen inte innehåller lookup_value , # N/A.

Kolumnnummer- kolumnnummer Tabeller varifrån resultatet ska matas ut. Kolumnen (nyckeln) längst till vänster har nummer 1 (det söks efter).

Parameter interval_view kan ta 2 värden: TRUE (det söks efter värdet närmast kriteriet eller sammanfaller med det) och FALSE (värdet söks efter exakt sammanfallande med kriteriet). TRUE antar att den första kolumnen i tabell sorterade alfabetiskt eller stigande. Denna metod används i standardfunktionen om inte annat anges.

Artikeln nedan diskuterar populära problem som kan lösas med funktionen VLOOKUP () .

Uppgift 1. Katalog över varor

Låt den ursprungliga tabellen ges (se. exempel på filreferens).

Uppgiften är att välja önskad leverantörskoder produkt, dra tillbaka den namn och Priset.

Notera... Detta är ett "klassiskt" problem för att använda VLOOKUP () (se artikel).

För uttag Namn använd formeln = VLOOKUP ($ E9, $ A $ 13: $ C $ 19,2, FALSE) eller = VLOOKUP ($ E9; $ A $ 13: $ C $ 19; 2; TRUE) eller = VLOOKUP ($ E9; $ A $ 13: $ C $ 19; 2) (dvs. värdet på parametern Intervallvy kan ställas in på FALSE eller TRUE, eller utelämnas helt). Parametervärde kolumnnummer du måste ställa in = 2, eftersom kolumnnummer namnär lika med 2 (nyckelkolumnen är alltid nummer 1).

För uttag Priser använd en liknande formel = VLOOKUP ($ E9, $ A $ 13: $ C $ 19, 3, FALSE)(parametervärde kolumnnummer måste ställas in = 3).

Nyckelkolumnen i vårt fall innehåller siffror och måste garanteras innehålla önskat värde (problemtillstånd). Om den första kolumnen inte innehåller objektet du letar efter , då returnerar funktionen felvärdet # N/A. Detta kan till exempel ske med ett stavfel när du skriver in en artikel. För att inte göra ett misstag när du anger önskad artikel kan du använda (se cell E9 ).

Det är tydligt att nyckelkolumnen i vår uppgift inte ska innehålla dubbletter (detta är meningen med artikeln, som unikt identifierar produkten). Annars kommer det översta värdet att visas.

När du löser sådana problem är nyckelkolumnen bättre i förväg (detta kommer också att hjälpa till Rullgardinslista klarare). Dessutom, i fallet med en osorterad lista, VLOOKUP () med parametern Intervallvy TRUE (eller utelämnad) kommer inte att fungera.

Alternativa formler övervägs också (vi kommer att få samma resultat) med funktionerna INDEX (), SÖK () och LOOKUP (). Om nyckelkolumnen (kolumnen med SKU:er) inte är längst till vänster i tabellen, är funktionen VLOOKUP () inte tillämplig. I det här fallet måste du använda alternativa formler. Ett gäng funktioner INDEX (), SÖK () bildar den så kallade "right VLOOKUP": = INDEX (B13: B19; SÖK ($ E $ 9; $ A $ 13: $ A $ 19,0); 1)

V exempel filblad Referens det visas att formlerna är tillämpliga för nyckelkolumner som innehåller textvärden, eftersom artikeln är ofta ett textvärde. Problemet är också löst för en osorterad nyckelkolumn.

Notera... Använd aldrig VLOOKUP () med parametern Intervallvy TRUE (eller utelämnad) om nyckelkolumnen inte är sorterad i stigande ordning. resultatet av formeln är oförutsägbart (om funktionen VLOOKUP () hittar ett värde som är större än det önskade, matar den ut värdet som finns en rad ovanför det).

Uppgift 2. Hitta närmaste nummer

Anta att du vill hitta en produkt vars pris är lika med eller närmast den önskade.

För att använda funktionen VLOOKUP () för att lösa detta problem måste du uppfylla flera villkor:

  1. Nyckelkolumnen som ska sökas i måste vara kolumnen längst till vänster i tabellen;
  2. Nyckelkolumnen måste sorteras i stigande ordning;
  3. Parametervärde Intervallvy måste ställas in på TRUE eller utelämnas helt.

För uttag Varans namn använd formeln = VLOOKUP ($ A7; $ A $ 11: $ B $ 17; 2; TRUE)

För att visa det hittade priset (det kommer inte nödvändigtvis att sammanfalla med det givna) använd formeln: = VLOOKUP ($ A7, $ A $ 11: $ B $ 17,1, TRUE)

Som du kan se på bilden ovan hittade VLOOKUP () det högsta priset, som är mindre än eller lika med den givna (se. exempelfilblad "Hitta närmaste nummer"). Detta beror på hur funktionen söker: om funktionen VLOOKUP () hittar ett värde som är större än det önskade värdet, visar den värdet som finns en rad ovanför det. Som en konsekvens, om det erforderliga värdet är mindre än minimum i nyckelkolumnen, kommer funktionen att returnera ett fel # N/A.

Det hittade värdet kanske inte är det närmaste. Om du till exempel försöker hitta det närmaste priset för 199, kommer funktionen att returnera 150 (även om det närmaste är 200). Detta är återigen en konsekvens av att funktionen hittar det största talet som är mindre än eller lika med det givna.

Om du behöver hitta det som verkligen ligger närmast det önskade värdet, så hjälper inte VLOOKUP () här. Den här typen av problem löses i avsnittet. Där kan du också hitta en lösning på problemet med att hitta den närmaste med en osorterad nyckelkolumn.

Notera... För enkelhetens skull är tabellraden som innehåller den hittade lösningen markerad. Detta kan göras med hjälp av formeln = SÖKNING ($ A $ 7; $ A $ 11: $ A $ 17; 1) = LINE () - LINE ($ A $ 10).

Notera: Om nyckelkolumnen har ett värde som matchar det önskade, då funktionen med parametern Intervallvy= FALSE returnerar det första hittade värdet lika med det önskade, och med parametern = TRUE - det sista (se bilden nedan).

Om kolumnen som söks inte är den längst till vänster, så kommer VLOOKUP () inte att hjälpa. I det här fallet måste du använda funktionerna SÖK () + INDEX () eller SÖKNING ().