01 iš 03
Raskite apytikslius atitikimus duomenims naudodami "Excel" VLOOKUP
Kaip funkcija VLOOKUP veikia
"Excel" VLOOKUP funkcija , kuri reiškia vertikalią peržvalgą , gali būti naudojama ieškant konkrečios informacijos, esančios duomenų lentelėje ar duomenų bazėje.
VLOOKUP paprastai grąžina vieną duomenų lauką kaip savo produkciją. Kaip tai veikia:
- Pateikiate pavadinimą arba lookup_value, kuris nurodo VLOOKUP, kurioje duomenų lentelės eilutėje arba įraše ieškoma norimų duomenų
- Jūs pateikiate stulpelio numerį - žinomą kaip col_index_num - ieškomų duomenų
- Pirmoje duomenų lentelės stulpelyje funkcija ieško " lookup_value "
- VLOOKUP tada suranda ir grąžina jūsų ieškomą informaciją iš kito to paties įrašo lauko, naudodamas pridėtą stulpelio numerį
Pirmiausia rūšiuojame duomenis
Nors tai ne visada reikalinga, dažniausiai geriausia pirmiausia rūšiuoti duomenų, kuriuos VLOOKUP ieško didėjančia tvarka , diapazonas, naudodamas pirmą raktinio raktinio žodžio diapazono stulpelį.
Jei duomenys nėra rūšiuojami, VLOOKUP gali grąžinti neteisingą rezultatą.
VLOOKUP funkcijos sintaksė ir argumentai
Funkcijos sintaksė nurodo funkcijos išdėstymą ir apima funkcijos pavadinimą, skliaustus ir argumentus .
VLOOKUP funkcijos sintaksė yra:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Paieškos _value - (reikalinga) vertė, kurią reikia ieškoti, pvz., kiekis, parduotas aukščiau esančiame paveikslėlyje
table_array - (reikalinga) tai duomenų lentelė, kurią VLOOKUP ieško, norėdami rasti informaciją, kurią jūs gaunate.
- Table_array turi būti bent du duomenų stulpeliai
- Pirmajame stulpelyje paprastai yra "lookup_value"
col_index_num - (būtinas) norimos vertės stulpelio numeris.
- Numeracija prasideda skiltimi "search_key" kaip 1 stulpeliu
- Jei col_index_num yra nustatytas didesniam skaičiui nei stulpelių skaičius, pasirinktas lentelės_radianto argumentu, yra #REF! klaida grąžinama funkcija
interval_lookup - (neprivaloma) nurodo, ar diapazonas yra rūšiuojamas didėjančia tvarka.
- Pirmojo stulpelio duomenys naudojami kaip rūšiuoti raktas
- Būtinoji vertė - TRUE arba FALSE yra vienintelės priimtinos vertės
- Jei praleistas, pagal numatytuosius nustatymus vertė yra TRUE
- Jei nustatytas TRUE arba praleistas, o pirmoji diapazono stulpelis nesuskaitoma didėjimo tvarka, gali atsirasti netinkamas rezultatas
- Jei nustatytas TRUE arba praleistas, ir tikslios atitikties _value atitiktis nerasta, artima atitiktis, kuri yra mažesnio dydžio ar vertės, naudojama kaip paieškos_key
- Jei nustatytas FALSE, VLOOKUP priima tik tikslią paieškos rezultato _value atitiktį . Jei yra kelios atitikties reikšmės, grąžinama pirmoji atitikimo reikšmė
- Jei nustatytas FALSE ir nerasta tinkamos paieškos_key reikšmės, funkcija grąžina # N / A klaidą
Pavyzdys: suraskite supirkto kiekio nuolaidą
Paveikslėlyje esantis pavyzdys naudoja VLOOKUP funkciją, norint rasti diskonto normą, kuri skiriasi priklausomai nuo įsigytų prekių kiekio.
Pavyzdys rodo, kad nuolaida 19 vienetų pirkimui yra 2%. Taip yra todėl, kiekio stulpelyje yra verčių diapazonai. Dėl to VLOOKUP negali rasti tikslios atitikties. Vietoje to, norint grąžinti teisingą diskonto normą, turi būti nustatyta apytikslė atitiktis.
Norėdami rasti apytikslius atitikmenis:
- rūšiuoti duomenis table_array didėjančia tvarka;
- nustatykite interval_lookup argumentą TRUE
Pavyzdyje, norint rasti nuolaidą įsigytų prekių kiekiui, naudojama ši formulė, kurioje yra VLOOKUP funkcija.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, tiesa)
Nors ši formulė gali būti tiesiog įvedama į darbalapio langelį, kita galimybė, naudojama su toliau išvardytais veiksmais, yra funkcijos dialogo lango įvedimas, kad būtų nurodyti jo argumentai.
- Dialogo lango naudojimas dažnai leidžia teisingai įvesti funkcijos argumentus.
VLOOKUP dialogo lango atidarymas
Veiksmai, naudojami įvedant VLOOKUP funkciją, parodytą paveikslėlyje aukščiau, į langelį B2 yra:
- Spustelėkite langelį B2, kad jis taptų aktyvia ląstele - vieta, kurioje rodomi VLOOKUP funkcijos rezultatai
- Spustelėkite skirtuką Formulės .
- Pasirinkite " Lookup & Reference" iš juostos, kad atidarytumėte išskleidžiamąjį sąrašą
- Spustelėkite VLOOKUP sąraše, kad pamatytumėte funkcijos dialogo langą
02 iš 03
Įeikite į Excel "VLOOKUP" funkcijos argumentus
Nuorodos į ląstelių nuorodas
Argumentai už funkciją VLOOKUP įrašomi į atskiras dialogo lango eilutes, kaip parodyta paveikslėlyje aukščiau.
Ląstelių nuorodos, kurios turi būti naudojamos kaip argumentai, gali būti įvedamos į teisingą eilutę arba, kaip parodyta toliau pateiktuose veiksmuose, nurodant, kuriam reikia nurodyti norimą ląstelių diapazoną su pelės žymekliu, juos galima įvesti dialogo lange .
Rodyklės naudojimo privalumai yra šie:
- Tai greičiau nei rašyti;
- Mažiau klaidų įvedama į teisingą ląstelių nuorodą.
Santykinių ir absoliučių ląstelių nuorodų naudojimas su argumentais
Tai nėra neįprasta naudoti keletą VLOOKUP kopijų, norint grąžinti skirtingą informaciją iš tos pačios duomenų lentelės. Kad būtų lengviau tai padaryti, dažnai VLOOKUP galima kopijuoti iš vienos langelio į kitą. Kai funkcijos nukopijuojamos į kitas ląsteles, reikia pasirūpinti, kad gautos ląstelės nuorodos būtų teisingos, atsižvelgiant į naują funkcijos vietą.
Aukščiau pateiktame paveikslėlyje table_array argumentas nurodo dolerio ženklus ( $ ), nurodančius, kad jie yra absoliučios ląstelių nuorodos , taigi jie nepakeis, jei funkcija bus nukopijuota į kitą langelį. Tai pageidautina, nes daugelis VLOOKUP kopijų būtų tos pačios duomenų lentelės kaip informacijos šaltinis.
Kita vertus , ląstelės nuorodą, naudojamą " lookup_value", neapsaugo dolerio ženklai, todėl tai yra santykinė ląstelių nuoroda. Santykinės ląstelių nuorodos keičia, kai jos nukopijuojamos, kad atspindėtų jų naują vietą, palyginti su jų duomenų padėtimi.
Funkcijų argumentų įvedimas
- VLOOKUP dialogo lange spustelėkite langą "Lookup _value"
- Spustelėkite langelį C2 darbalapyje, kad įvedėte šią nuorodą kaip paieškos_key argumentą
- Spustelėkite langelį " Table_array"
- Išskleiskite lenteles C5-D8 darbalapyje, kad įeitumėte į šį diapazoną kaip " Table_array" argumentas - lentelių antraštės nėra įtrauktos
- Paspauskite klaviatūros klaviatūros klavišą F4, kad pakeistumėte diapazoną absoliučiais elementais
- Spustelėkite eilutę " Col_index_num"
- Šioje eilutėje įveskite 2 eilutėje kaip " Col_index_num" argumentą, nes diskonto normos yra stulpelio " Table_array" argumento 2 stulpelyje.
- Spustelėkite dialogo lango eilutę Range_lookup
- Įveskite žodį True kaip " Range_lookup" argumentą
- Paspauskite klaviatūros klavišą Enter, kad uždarytumėte dialogo langą ir grįžtumėte į darbalapį
- Atsakymas 2% (diskonto norma įsigyjamam kiekiui) turėtų pasirodyti lakšto D2 lape
- Kai paspausite langelį D2, visa formos funkcija = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) pasirodys juostos formulėje virš darbalapio.
Kodėl VLOOKUP kaip rezultatas grąžino 2%
- Pavyzdyje skiltyje " Kiekis " nėra tikslios " search_key" reikšmės 19 atitikties.
- Kadangi is_sorted argumentas yra TRUE, VLOOKUP suras apytikstą atitikimą paieškos_key reikšmei.
- Artimiausias vertes dydis, kuris vis dar mažesnis už 19 " search_key" reikšmę, yra 11.
- Todėl VLOOKUP ieško nuolaidų procentų eilutėje, kurioje yra 11, ir todėl grąžina 2% diskonto normą.
03 iš 03
Excel VLOOKUP neveikia: # N / A ir #REF klaidos
VLOOKUP klaidos pranešimai
Šie klaidų pranešimai yra susiję su VLOOKUP.
A # N / A ("value not available") Rodoma klaida:
- Paieškos _value nerasta pirmojo diapazono argumento stulpelyje
- Table_array argumentas yra netikslus. Pavyzdžiui, argumentas gali apimti tuščius stulpelius kairėje diapazono pusėje
- " Range_lookup" argumentas yra nustatytas kaip "FALSE", o tiksliojo atitikimo argumentui " search_key" negalima rasti pirmame diapazono stulpelyje.
- " Range_lookup" argumentas yra nustatytas ties "TRUE", o visos pirmojo diapazono stulpelio reikšmės yra didesni nei " search_key"
A # REF! ("atskaitos iš diapazono") Rodoma klaida, jei:
- Col_index_num argumentas yra didesnis už stulpelių skaičių table_array