Brug af VLOOKUP-funktionen til at erstatte værdier

Hvem er for doven eller ikke har tid til at læse - se videoen. Detaljer og nuancer findes i teksten nedenfor.

Formulering af problemet

Så vi har to borde – bestille bord и prisliste:

Opgaven er automatisk at erstatte priser fra prislisten i ordretabellen med fokus på produktets navn, så du senere kan beregne prisen.

Løsning

I Excel-funktionssættet under kategorien Referencer og arrays (Opslag og reference) der er en funktion VPR (OPLYSNING).Denne funktion leder efter en given værdi (i vores eksempel er dette ordet "æbler") i kolonnen længst til venstre i den angivne tabel (prisliste), der bevæger sig fra top til bund og viser indholdet af den tilstødende celle efter at have fundet den. (23 rubler) .Skematisk kan driften af ​​denne funktion repræsenteres Så:

For at lette yderligere brug af funktionen skal du gøre én ting på én gang – giv celleområdet i prislisten dit eget navn. For at gøre dette skal du vælge alle cellerne i prislisten undtagen "overskriften" (G3: H19), vælg fra menuen Indsæt – Navn – Tildel (Indsæt — Navn — Definer) eller tryk på CTRL + F3 og indtast et hvilket som helst navn (ingen mellemrum) som Pris… Nu kan du i fremtiden bruge dette navn til at linke til prislisten.

Nu bruger vi funktionen VPR… Vælg den celle, hvor den skal indtastes (D3), og åbn fanen Formler – Funktionsindsættelse (Formler — Indsæt funktion)… I kategorien Referencer og arrays (Opslag og reference) finde funktionen VPR (OPLYSNING) og tryk OK… Et vindue til indtastning af argumenter for funktionen vises:

Brug af VLOOKUP-funktionen til at erstatte værdier

Vi udfylder dem på skift:

  • Ønsket værdi (Opslagsværdi) – navnet på det produkt, som funktionen skal finde i kolonnen længst til venstre i prislisten. I vores tilfælde er ordet "æbler" fra celle B3.
  • Bordlampe (Tabel Array) – en tabel, hvorfra de ønskede værdier er taget uXNUMXbuXNUMXbare, det vil sige vores prisliste. Til reference bruger vi vores eget navn "Pris" givet tidligere. Hvis du ikke har givet et navn, kan du bare vælge tabellen, men glem ikke at trykke på knappen F4at fastgøre linket med dollartegn, for ellers vil den glide ned, når du kopierer vores formel ned til resten af ​​cellerne i kolonne D3:D30.
  • Kolonne_nummer (Kolonneindeksnummer) – serienummer (ikke et bogstav!) På den kolonne i prislisten, hvorfra vi vil tage prisværdier. Den første kolonne på prislisten med navnene er nummereret 1, derfor skal vi bruge prisen fra kolonnen nummereret 2.
  • interval_opslag (Opslag på rækkevidde) – kun to værdier kan indtastes i dette felt: FALSK eller TRUE:
      • Hvis en værdi er indtastet 0 or AT LYVE (FALSK), så betyder det faktisk, at kun søgning er tilladt nøjagtig match, dvs. hvis funktionen ikke finder den ikke-standardvare, der er angivet i ordretabellen i prislisten (hvis f.eks. "Coconut" er indtastet), vil den generere #N/A (ingen data) fejl.
      • Hvis en værdi er indtastet 1 or TRUE (SAND), så betyder det, at du tillader søgningen ikke efter det nøjagtige, men omtrentlige match, altså i tilfælde af “kokos”, vil funktionen forsøge at finde et produkt med et navn, der er så tæt som muligt på “kokos” og returnere prisen for dette navn. I de fleste tilfælde kan en sådan omtrentlig erstatning spille brugeren et puds ved at erstatte værdien af ​​det forkerte produkt, der faktisk var der! Så for de fleste reelle forretningsproblemer er omtrentlig søgning bedst ikke at tillade. Undtagelsen er, når vi leder efter tal og ikke tekst – for eksempel ved beregning af Trinrabatter.

Alt! Det er tilbage at trykke på OK og kopier den indtastede funktion til hele kolonnen.

# N/A fejl og deres undertrykkelse

Funktion VPR (OPLYSNING) returnerer #N/A fejl (#N/A) hvis en:

  • Præcis søgning aktiveret (argument Intervalvisning = 0), og det ønskede navn er ikke i Bordlampe.
  • Grov søgning inkluderet (Intervalvisning = 1), men Bordlampe, hvor søgningen finder sted, er ikke sorteret i stigende rækkefølge af navne.
  • Formatet på den celle, hvor den nødvendige værdi af navnet kommer fra (for eksempel B3 i vores tilfælde) og formatet af cellerne i den første kolonne (F3: F19) i tabellen er forskellige (for eksempel numerisk og tekst ). Dette tilfælde er især typisk, når du bruger numeriske koder (kontonumre, identifikatorer, datoer osv.) i stedet for tekstnavne. I dette tilfælde kan du bruge funktionerne Ч и TEKST at konvertere dataformater. Det kommer til at se sådan ud:

    =OPSLAG(TEKST(B3);pris;0)

    Det kan du læse mere om her.

  • Funktionen kan ikke finde den ønskede værdi, fordi koden indeholder mellemrum eller usynlige tegn, der ikke kan udskrives (linjeskift osv.). I dette tilfælde kan du bruge tekstfunktioner TRIMME (TRIMME) и UDSKRIV(REN) for at fjerne dem:

    =OPSLAG(TRIMSPACES(CLEAN(B3)),pris,0)

    =OPSLAG(TRIM(CLEAN(B3));pris;0)

For at undertrykke fejlmeddelelsen # N / A (#N/A) i tilfælde, hvor funktionen ikke kan finde et nøjagtigt match, kan du bruge funktionen FEJL (FEJL)… Så f.eks. opsnapper denne konstruktion eventuelle fejl genereret af VLOOKUP og erstatter dem med nuller:

= IFERROR (VLOOKUP (B3, pris, 2, 0), 0)

= IFERROR (VLOOKUP (B3; pris; 2; 0); 0)

PS

Hvis du skal udtrække ikke én værdi, men hele sættet på én gang (hvis der er flere forskellige), så bliver du nødt til at shamanisere med matrixformlen. eller brug den nye XLOOKUP-funktion fra Office 365.

 

  • En forbedret version af VLOOKUP-funktionen (VLOOKUP 2).
  • Hurtig beregning af trin (område) rabatter ved hjælp af VLOOKUP-funktionen.
  • Sådan laver du en "venstre VLOOKUP" ved hjælp af INDEX- og MATCH-funktionerne
  • Sådan bruger du funktionen VOPSLAG til at udfylde formularerne med data fra listen
  • Sådan trækker du ikke den første, men alle værdierne ud fra bordet på én gang
  • VLOOKUP2 og VLOOKUP3 funktioner fra PLEX-tilføjelsen

 

Giv en kommentar