01 no 03
Atrodiet aptuveno atbilstību datiem, izmantojot Excel VLOOKUP
Kā funkcija VLOOKUP darbojas
Excel VLOOKUP funkciju , kas nozīmē vertikālu uzmeklēšanu , var izmantot, lai meklētu konkrētu informāciju, kas atrodas datu tabulā vai datu bāzē.
VLOOKUP parasti atgriež vienu datu lauku kā tā produkciju. Kā tas notiek:
- Jūs sniedzat nosaukumu vai lookup_value, kas norāda VLOOKUP, kurā datu datu tabulas rindā vai ierakstā meklējiet vēlamos datus
- Jūs iesniedzat pieprasīto datu kolonnas numuru, kas pazīstams kā col_index_num
- Šī funkcija meklē vērtību lookup_value pirmajā datu tabulas slejā
- VLOOKUP pēc tam atrod un atgriež informāciju, kuru meklējat no citas tajā paša ieraksta lauka, izmantojot piegādātās kolonnas numuru
Datu sakārtošana vispirms
Lai gan tas ne vienmēr ir nepieciešams, vispirms ir vislabāk vispirms kārtot datu klāstu, ko VLOOKUP meklē pēc augošā secībā, izmantojot sortimenta atslēgas diapazona pirmo kolonnu.
Ja dati nav sakārtoti, VLOOKUP var atgriezt nepareizu rezultātu.
VLOOKUP funkciju sintakse un argumenti
Funkcijas sintakse attiecas uz funkcijas izkārtojumu un ietver funkcijas nosaukumu, iekavās un argumentus .
VLOOKUP funkcijas sintakse ir:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (vajadzīga) meklējamā vērtība - piemēram, daudzums, kas pārdots augšējā attēlā
table_array - (nepieciešams) šī ir datu tabula, ko VLOOKUP meklē, lai atrastu informāciju, kuru jūs izmantojat.
- Tabulas_arrayi ir jābūt vismaz divām datu kolonnām
- Pirmā sleja parasti satur lookup_value
col_index_num - (nepieciešams) nepieciešamās vērtības slejas numurs.
- Numerācija sākas ar sleju " search_key " kā 1. sleju
- Ja col_index_num ir iestatīts uz skaitli, kas lielāks par tabulā_ražojuma argumentā atlasīto kolonnu skaitu, tad #REF! kļūdu atgriež pēc funkcijas
range_lookup - (pēc izvēles) norāda, vai diapazons ir sakārtots augošā secībā.
- Dati pirmajā slejā tiek izmantoti kā kārtošanas atslēga
- Būla vērtība - TRUE vai FALSE ir vienīgās pieņemamās vērtības
- Ja ir izlaists, pēc noklusējuma vērtība tiek iestatīta uz TRUE
- Ja iestatījums ir TRUE vai izlaists un diapazona pirmā kolonna nav sakārtota augošā secībā, var rasties nepareizs rezultāts
- Ja iestatījums ir TRUE vai izlaists, un meklēšanas rezultāts _value precīza atbilstība nav atrasta, meklēšanas vaicājumā tiek izmantota tuvākā atbilstība, kas ir mazāka izmēra vai vērtības dēļ.
- Ja iestatīts uz FALSE, VLOOKUP pieņem tikai precīzu atbilstību meklēšanas _value . Ja ir vairākas atbilstības vērtības, tiek atgriezta pirmā atbilstošā vērtība
- Ja iestatījums ir FALSE un nav atrasta neviena atbilstoša meklēšanas_keya vērtība, funkcija atgriež # N / A kļūdu
Piemērs: Atrodiet pirkto daudzumu diskonta likmi
Piemērā attēlā, kas atrodas augšā, tiek izmantota VLOOKUP funkcija, lai atrastu diskonta likmi, kas atšķiras atkarībā no nopirkto priekšmetu daudzuma.
Piemērs parāda, ka 19 preču iegādes atlaide ir 2%. Tas ir tāpēc, ka slejā Daudzums ir vērtības diapazons. Rezultātā VLOOKUP nevar atrast precīzu atbilstību. Tā vietā, lai atgrieztu pareizo diskonta likmi, ir jāatrod aptuvena atbilstība.
Lai atrastu aptuvenas atbilstības:
- kārtot tabulā_array datus augošā secībā;
- iestatīt range_lookup argumentu TRUE
Piemēram, lai atrastu atlaidi pirktās preces daudzumam, tiek izmantota šāda formula, kas satur VLOOKUP funkciju.
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Lai arī šī formula var tikt vienkārši ievadīta darblapas šūnā, vēl viena iespēja, ko izmanto ar tālāk uzskaitītajiem soļiem, ir izmantot funkciju dialoglodziņu, lai ievadītu savus argumentus.
- Izmantojot dialoglodziņu, bieži vien ir vienkāršāk ievadīt funkciju argumentus.
VLOOKUP dialoglodziņa atvēršana
Darbības, kas tiek izmantotas, lai ievadītu VLOOKUP funkciju, kas parādīta augstāk esošajā attēlā, šūnā B2 ir:
- Noklikšķiniet uz šūnas B2, lai padarītu to par aktīvo šūnu - vietu, kurā tiek parādīti VLOOKUP funkcijas rezultāti
- Noklikšķiniet uz cilnes Formulas .
- Izvēlieties Lookup & Reference no lentes, lai atvērtu nolaižamo sarakstu funkciju
- Sarakstā noklikšķiniet uz VLOOKUP , lai atvērtu funkciju dialoglodziņu
02 no 03
Ievadiet Excel VLOOKUP funkciju argumentus
Norādot uz šūnu atsaucēm
Argumenti VLOOKUP funkcijai tiek ievadīti dialoglodziņa atsevišķās rindās, kā parādīts iepriekš attēlā.
Šūnu atsauces, ko var izmantot kā argumentus, var ievadīt pareizajā rindā, vai arī, kā tas ir izdarīts sekojošos posmos, dialoglodziņā var ievadīt norādi, kas ietver iezīmēšo šūnu diapazonu ar peles rādītāju. .
Norādes izmantošanas priekšrocības ietver:
- Tas ir ātrāk nekā rakstīt;
- Tiek izdarītas mazāk kļūdas, ievadot pareizas šūnu atsauces.
Izmantojot relatīvās un absolūto šūnu atsauces ar argumentiem
Nav neparasti izmantot vairākas VLOOKUP kopijas, lai atgrieztu dažādu informāciju no vienas datu tabulas. Lai to izdarītu vieglāk, bieži vien VLOOKUP var kopēt no vienas šūnas uz otru. Kad funkcijas tiek kopētas citās šūnās, rūpīgi jāpārliecinās, ka izrietošās šūnu atsauces ir pareizi, ņemot vērā funkcijas jauno atrašanās vietu.
Attēlā augšpusē dolāra zīmes ( $ ) aptver šūnu atsauces table_array argumentam, norādot, ka tās ir absolūtas šūnu atsauces , kas nozīmē, ka tie nemainīsies, ja funkcija tiek kopēta citā šūnā. Tas ir vēlams, jo vairākos VLOOKUP eksemplāros viss atsaucas uz to pašu datu tabulu kā informācijas avotu.
No otras puses , lookup_valuei izmantoto šūnu atsauci neaptver dolāra zīmes, kas to padara par relatīvu atsauci uz šūnu. Relatīvās šūnu atsauces mainās, kad tās tiek kopētas, lai atspoguļotu to jauno atrašanās vietu attiecībā pret to datu pozīciju, uz kuriem tās atsaucas.
Funkcijas argumentu ievadīšana
- VLOOKUP dialoglodziņā noklikšķiniet uz līnijas Lookup _value
- Noklikšķiniet uz cilnes C2 darblapā, lai ievadītu šo šūnu atsauci kā arguments par meklēšanas_key
- Noklikšķiniet uz dialoglodziņa Tabulas_izraides līnijas
- Izcelt skaitļus no C5 līdz D8 darblapā, lai ievadītu šo diapazonu kā Tabulas_paraugs arguments - tabulas virsraksti nav iekļauti
- Nospiediet tastatūras taustiņu F4, lai mainītu diapazonu līdz absolūtām šūnu atsaucēm
- Noklikšķiniet uz dialoglodziņa rindiņas Col_index_num
- Šajā rindā ierakstiet a2 kā Col_index_num argumentu, jo diskonta likmes ir izvietotas Table_array argumenta 2. slejā.
- Noklikšķiniet uz dialoglodziņa diapazona diapazona Range_lookup
- Ievadiet vārdu True kā argumentu Range_lookup
- Nospiediet tastatūras taustiņu Enter, lai aizvērtu dialoglodziņu un atgrieztos darblapā
- Atbilde 2% (diskonta likme par iegādāto daudzumu) jāparādās darblapas šūnā D2
- Noklikšķinot uz šūnas D2, formulas joslā virs darblapas parādās pilnā funkcija = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Kāpēc VLOOKUP kā rezultātu atgriezās 2%
- Piemēram, kolonnas Daudzums nesatur precīzu atbilstību 19 meklēšanas_key vērtībai.
- Tā kā is_sorted arguments ir iestatīts uz TRUE, VLOOKUP atradīs aptuvenu atbilstību meklēšanas_key vērtībai.
- Tuvākā lieluma vērtība, kas joprojām ir mazāka nekā 19 meklēšanas atslēgas vērtība, ir 11.
- Tādēļ VLOOKUP meklē rentabilitātes procentus rindā, kurā ir 11, kā rezultātā tiek atgriezta diskonta likme 2% apmērā.
03 no 03
Excel VLOOKUP nedarbojas: # N / A un #REF kļūdas
VLOOKUP kļūdu ziņojumi
Šādi kļūdu ziņojumi ir saistīti ar VLOOKUP.
A # N / A (vērtība nav pieejama) Tiek parādīta kļūda:
- Uzmeklēšanas _vērtība nav atrodama diapazona argumenta pirmajā kolonnā
- Table_array arguments ir neprecīzs. Piemēram, arguments var ietvert tukšas kolonnas diapazona kreisajā pusē
- Arguments Range_lookup ir iestatīts uz FALSE, un precīzu atbilstību argumentam search_key nevar atrast diapazona pirmajā kolonnā.
- Arguments Range_lookup ir iestatīts uz TRUE, un visas vērtības diapazona pirmajā slejā ir lielākas nekā meklēšanas_key
A # REF! ("Atsauces ārpus diapazona") Kļūda tiek parādīta Ja:
- Arguments Col_index_num ir lielāks par tabulas_array kolonnu skaitu