Bruk av OPSLAKK-funksjonen for å erstatte verdier

Hvem er for lat eller ikke har tid til å lese – se videoen. Detaljer og nyanser er i teksten nedenfor.

Formulering av problemet

Så vi har to bord – bestille bord и pris liste:

Oppgaven er å erstatte priser fra prislisten inn i bestillingstabellen automatisk, med fokus på navnet på produktet slik at du senere kan beregne kostnaden.

Oppløsning

I Excel-funksjonssettet, under kategorien Referanser og matriser (Slå opp og referere) det er en funksjon VPR (VISNING).Denne funksjonen ser etter en gitt verdi (i vårt eksempel er dette ordet "epler") i kolonnen lengst til venstre i den angitte tabellen (prisliste) som beveger seg fra topp til bunn og, etter å ha funnet den, viser innholdet i den tilstøtende cellen (23 rubler) .Skjematisk kan driften av denne funksjonen representeres Så:

For å lette videre bruk av funksjonen, gjør én ting på en gang – gi celleområdet i prislisten ditt eget navn. For å gjøre dette, velg alle cellene i prislisten bortsett fra "overskriften" (G3: H19), velg fra menyen Sett inn – Navn – Tildel (Sett inn - Navn - Definer) eller trykk CTRL + F3 og skriv inn et hvilket som helst navn (ingen mellomrom) som Pris… Nå, i fremtiden, kan du bruke dette navnet for å lenke til prislisten.

Nå bruker vi funksjonen VPR… Velg cellen der den skal legges inn (D3) og åpne fanen Formler – funksjonsinnsetting (Formler – Sett inn funksjon)… I kategorien Referanser og matriser (Oppslag og referanse) finne funksjonen VPR (VISNING) og trykk OK… Et vindu for å legge inn argumenter for funksjonen vises:

Bruk av OPSLAKK-funksjonen for å erstatte verdier

Vi fyller dem etter tur:

  • Ønsket verdi (Oppslagsverdi) – navnet på produktet som funksjonen skal finne i kolonnen lengst til venstre i prislisten. I vårt tilfelle, ordet "epler" fra celle B3.
  • Bord (Tabellarray) – en tabell der de ønskede verdiene uXNUMXbuXNUMXbare er tatt fra, det vil si vår prisliste. Som referanse bruker vi vårt eget navn "Pris" gitt tidligere. Hvis du ikke har gitt et navn, kan du bare velge tabellen, men ikke glem å trykke på knappen F4å feste lenken med dollartegn, fordi ellers vil den gli ned når formelen vår kopieres ned til resten av cellene i kolonne D3:D30.
  • Kolonne_nummer (Kolonneindeksnummer) – serienummer (ikke en bokstav!) På kolonnen i prislisten som vi tar prisverdier fra. Den første kolonnen i prislisten med navnene er nummerert 1, derfor trenger vi prisen fra kolonnen nummerert 2.
  • interval_lookup (Rekkeviddeoppslag) – kun to verdier kan angis i dette feltet: FALSE eller TRUE:
      • Hvis en verdi er angitt 0 or LØVER (FALSK), så betyr dette faktisk at kun søk er tillatt nøyaktig treff, dvs. hvis funksjonen ikke finner den ikke-standard varen som er spesifisert i bestillingstabellen i prislisten (hvis for eksempel “Coconut” er oppgitt), vil den generere #N/A (ingen data) feil.
      • Hvis en verdi er angitt 1 or TRUE (EKTE), så betyr dette at du tillater søket ikke for den eksakte, men omtrentlig match, dvs. når det gjelder «kokos», vil funksjonen prøve å finne et produkt med et navn som er så nært som mulig «kokos» og returnere prisen for dette navnet. I de fleste tilfeller kan en slik omtrentlig erstatning spille brukeren et puss ved å erstatte verdien av feil produkt som faktisk var der! Så for de fleste reelle forretningsproblemer er omtrentlig søk best å ikke tillate. Unntaket er når vi ser etter tall og ikke tekst – for eksempel ved beregning av trinnrabatter.

Alt! Det gjenstår å trykke på OK og kopier den angitte funksjonen til hele kolonnen.

# N / A feil og deres undertrykkelse

Funksjon VPR (VISNING) returnerer #N/A feil (#N/A) hvis en:

  • Nøyaktig søk aktivert (argument Intervallvisning = 0) og ønsket navn er ikke med Bord.
  • Grovsøk inkludert (Intervallvisning = 1), men Bord, der søket finner sted, er ikke sortert i stigende rekkefølge av navn.
  • Formatet til cellen der den nødvendige verdien av navnet kommer fra (for eksempel B3 i vårt tilfelle) og formatet til cellene i den første kolonnen (F3: F19) i tabellen er forskjellige (for eksempel numerisk og tekst ). Dette tilfellet er spesielt typisk når du bruker numeriske koder (kontonumre, identifikatorer, datoer osv.) i stedet for tekstnavn. I dette tilfellet kan du bruke funksjonene Ч и TEKST å konvertere dataformater. Det vil se omtrent slik ut:

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

    Du kan lese mer om dette her.

  • Funksjonen kan ikke finne den nødvendige verdien fordi koden inneholder mellomrom eller usynlige tegn som ikke kan skrives ut (linjeskift osv.). I dette tilfellet kan du bruke tekstfunksjoner TRIM (LISTVERK) и SKRIV(REN) for å fjerne dem:

    =SØK OPP(TRIMPLASSER(RENGJØR(B3)),pris,0)

    =SØKOPPSLAG(TRIMME(RENGJØR(B3));pris;0)

For å undertrykke feilmeldingen # N / A (#N/A) i tilfeller der funksjonen ikke finner en eksakt match, kan du bruke funksjonen FEIL (FEIL)… Så, for eksempel, fanger denne konstruksjonen opp eventuelle feil generert av VLOOKUP og erstatter dem med nuller:

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

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

PS

Hvis du ikke trenger å trekke ut én verdi, men hele settet på en gang (hvis det er flere forskjellige), må du shamanisere med matriseformelen. eller bruk den nye XLOOKUP-funksjonen fra Office 365.

 

  • En forbedret versjon av VLOOKUP-funksjonen (VLOOKUP 2).
  • Rask beregning av trinn (rekkevidde) rabatter ved å bruke funksjonen VLOOKUP.
  • Hvordan lage en "venstre VLOOKUP" ved å bruke INDEX- og MATCH-funksjonene
  • Hvordan bruke OPSLAKK-funksjonen til å fylle ut skjemaene med data fra listen
  • Hvordan trekke ut ikke den første, men alle verdiene fra tabellen på en gang
  • VLOOKUP2 og VLOOKUP3-funksjoner fra PLEX-tillegget

 

Legg igjen en kommentar