Excel

23 lietas, kas jums jāzina par VLOOKUP

23 Things You Should Know About Vlookup

Ja vēlaties iegūt informāciju no tabulas, Excel VLOOKUP funkcija ir lielisks risinājums. Spēja dinamiski meklēt un izgūt informāciju no tabulas daudziem lietotājiem var mainīt spēli, un VLOOKUP atradīsit visur.





Un tomēr, lai gan VLOOKUP ir salīdzinoši viegli lietojams, ir daudz kas var noiet greizi. Viens iemesls ir tas, ka VLOOKUP ir būtisks dizaina trūkums - pēc noklusējuma tiek pieņemts, ka ar aptuveno atbilstību jums viss ir kārtībā. Ko jūs droši vien neesat.

Tas var radīt tādus rezultātus izskatās pilnīgi normāli , lai gan tie ir pilnīgi nepareizi . Ticiet man, tas nav tas, ko vēlaties mēģināt paskaidrot priekšniekam pēc tam, kad viņš jau ir nosūtījis jūsu izklājlapu vadībai :)





Lasiet tālāk, lai uzzinātu, kā pārvaldīt šo izaicinājumu, un atklājiet citus padomus, kā to apgūt Excel VLOOKUP funkcija .

1. Kā darbojas VLOOKUP

VLOOKUP ir funkcija tabulas datu meklēšanai un izgūšanai. “V” VLOOKUP apzīmē vertikāli, kas nozīmē, ka tabulas datiem jābūt sakārtotiem vertikāli, datiem rindās. (Par horizontāli strukturētiem datiem sk MEKLĒŠANA ).



Ja jums ir labi strukturēta tabula ar informāciju, kas sakārtota vertikāli, un kolonna kreisajā pusē, kuru varat izmantot rindas saskaņošanai, iespējams, varat izmantot funkciju VLOOKUP.

VLOOKUP nepieciešama, lai tabula būtu strukturēta tā, lai uzmeklēšanas vērtības tiktu parādītas kreisajā slejā. Dati, kurus vēlaties izgūt (rezultātu vērtības), var parādīties jebkurā kolonnā pa labi. Izmantojot VLOOKUP, iedomājieties, ka katra tabulas kolonna ir numurēta, sākot no kreisās puses. Lai iegūtu vērtību no konkrētas kolonnas, vienkārši ievadiet atbilstošo skaitli kā “kolonnu indeksu”. Tālāk redzamajā piemērā mēs vēlamies atrast e -pasta adresi, tāpēc kolonnu indeksam izmantojam skaitli 4:

Pārskats par VLOOKUP darbību

Iepriekš redzamajā tabulā darbinieku ID ir 1. slejā pa kreisi, un e -pasta adreses ir 4. slejā pa labi.

Lai izmantotu VLOOKUP, jūs sniedzat 4 informāciju vai “argumentus”:

  1. Vērtība, kuru meklējat ( lookup_value )
  2. Šūnu diapazons, kas veido tabulu ( tabulas_masīvs )
  3. Kolonnas numurs, no kura izgūt rezultātu ( column_index )
  4. Spēles režīms ( range_lookup , TRUE = aptuvens, FALSE = precīzs)

Video: Kā lietot VLOOKUP (3 min)

Ja jūs joprojām nesaprotat VLOOKUP pamatideju, Jonam Acamporam, Excel Campus, ir lielisks skaidrojums pamatojoties uz Starbucks kafijas ēdienkarti.

2. VLOOKUP izskatās tikai pareizi

Varbūt lielākais VLOOKUP ierobežojums ir tas, ka tas var skatīties tikai pa labi, lai izgūtu datus.

Tas nozīmē, ka VLOOKUP var iegūt datus tikai no slejām pa labi no tabulas pirmās kolonnas. Kad uzmeklēšanas vērtības tiek parādītas pirmajā (kreisajā) kolonnā, šis ierobežojums nenozīmē daudz, jo visas pārējās slejas jau atrodas labajā pusē. Tomēr, ja uzmeklēšanas kolonna kaut kur parādās tabulas iekšpusē, jūs varēsit meklēt vērtības tikai no slejām, kas atrodas pa labi no šīs slejas. Jums būs arī jāpiegādā mazāka tabula VLOOKUP, kas sākas ar uzmeklēšanas kolonnu.

VLOOKUP var skatīties tikai pa labi no kolonnas uzmeklēšanas vērtības

Šo ierobežojumu var pārvarēt, izmantojot INDEX un MATCH, nevis VLOOKUP.

3. VLOOKUP atrod pirmo sakritību

Precīzās atbilstības režīmā, ja uzmeklēšanas slejā ir dublikātu vērtības, VLOOKUP atbilst tikai pirmajai vērtībai. Tālāk sniegtajā piemērā mēs izmantojam VLOOKUP, lai atrastu vārdu, un VLOOKUP ir iestatīts, lai veiktu precīzu atbilstību. Lai gan sarakstā ir divas Dženetas, VLOOKUP atbilst tikai pirmajam:

VLOOKUP vienmēr atrod pirmo atbilstību

Piezīme: uzvedība var mainīties, ja VLOOKUP tiek izmantots aptuvenās atbilstības režīmā. Šis raksts detalizēti izskaidro tēmu.

4. VLOOKUP nav reģistrjutīgs

Meklējot vērtību, VLOOKUP neapstrādā lielos un mazos burtus atšķirīgi. VLOOKUP gadījumā produkta kods, piemēram, “PQRF”, ir identisks “pqrf”. Zemāk redzamajā piemērā mēs meklējam lielos burtus 'JANET', bet VLOOKUP neatšķir burtus, tāpēc tie vienkārši atbilst 'Janet', jo tā ir pirmā atbilstība, ko tā atrod:

VLOOKUP NAV reģistrjutīgs

Mēs arī piedāvājam apmaksāta apmācība VLOOKUP un INDEX/MATCH

5. VLOOKUP ir divi atbilstības režīmi

VLOOKUP ir divi darbības režīmi: precīza atbilstība un aptuvena atbilstība. Vairumā gadījumu jūs, iespējams, vēlēsities izmantot VLOOKUP precīzās atbilstības režīmā. Tam ir jēga, ja vēlaties meklēt informāciju, pamatojoties uz kāda veida unikālu atslēgu, piemēram, informāciju par produktu, pamatojoties uz produkta kodu, vai filmas datus, pamatojoties uz filmas nosaukumu:

VLOOKUP precīzās atbilstības piemērs - atbilstošas ​​filmas

H6 formula uzmeklēšanas gadam, pamatojoties uz precīzu filmas nosaukuma atbilstību, ir šāda:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Tomēr jūs vēlaties izmantot aptuveno režīmu gadījumos, kad neatbilstat unikālajam ID, bet drīzāk meklējat “labāko atbilstību” vai “labāko kategoriju”. Piemēram, varbūt jūs meklējat pasta izdevumus, pamatojoties uz svaru, meklējat nodokļa likmi, pamatojoties uz ienākumiem, vai meklējat komisijas maksu, pamatojoties uz ikmēneša pārdošanas numuru. Šādos gadījumos jūs, iespējams, neatradīsit precīzu uzmeklēšanas vērtību tabulā. Tā vietā jūs vēlaties, lai VLOOKUP iegūtu vislabāko atbilstību noteiktai uzmeklēšanas vērtībai.

VLOOKUP aptuvenās atbilstības piemērs - komisijas maksas

D5 formula aptuveni atbilst, lai iegūtu pareizo komisijas maksu:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. Uzmanību: VLOOKUP pēc noklusējuma izmanto aptuveno atbilstību

Precīzu un aptuvenu atbilstību VLOOKUP kontrolē ceturtais arguments, ko sauc par “diapazona meklēšanu”. Šis nosaukums nav intuitīvs, tāpēc jums vienkārši jāiegaumē, kā tas darbojas.

Lai iegūtu precīzu atbilstību, izmantojiet FALSE vai 0. Lai iegūtu aptuvenu atbilstību, iestatiet range_lookup uz TRUE vai 1:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

Diemžēl ceturtais arguments - range_lookup - nav obligāts un pēc noklusējuma ir TRUE, kas nozīmē, ka VLOOKUP pēc noklusējuma veiks aptuvenu atbilstību. Veicot aptuvenu atbilstību, VLOOKUP pieņem, ka tabula ir sakārtota, un veic bināro meklēšanu. Ja binārās meklēšanas laikā VLOOKUP atrod precīzas atbilstības vērtību, tā atgriež vērtību no šīs rindas. Ja tomēr VLOOKUP sastop vērtību, kas ir lielāka par uzmeklēšanas vērtību, tā atgriež vērtību no iepriekšējās rindas.

Tas ir bīstams noklusējums, jo daudzi cilvēki neapzināti atstāj VLOOKUP tā noklusējuma režīmā, kas var izraisīt nepareizs rezultāts kad tabula nav sakārtota.

Lai izvairītos no šīs problēmas, noteikti izmantojiet FALSE vai nulli kā 4. argumentu, ja vēlaties precīzu atbilstību.

7. Jūs varat piespiest VLOOKUP veikt precīzu atbilstību

Lai piespiestu VLOOKUP atrast precīzu atbilstību, noteikti iestatiet 4 argumentu (range_lookup) uz FALSE vai nulli. Šīs divas formulas ir līdzvērtīgas:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

Precīzās atbilstības režīmā, kad VLOOKUP nevar atrast vērtību, tas atgriezīs #N/A. Tas skaidri norāda, ka vērtība nav atrodama tabulā.

8. Jūs varat pateikt VLOOKUP veikt aptuvenu atbilstību

Lai izmantotu VLOOKUP aptuvenās atbilstības režīmā, izlaidiet ceturto argumentu (diapazona skatījums) vai norādiet kā TRUE vai 1. Šīs 3 formulas ir līdzvērtīgas:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

Mēs iesakām vienmēr skaidri iestatīt argumentu range_lookup, lai gan VLOOKUP to neprasa. Tādā veidā jums vienmēr ir vizuāls atgādinājums par gaidīto spēles režīmu.

Video: Kā lietot VLOOKUP aptuvenām atbilstībām

9. Lai iegūtu aptuvenas atbilstības, dati ir jāsakārto

Ja izmantojat aptuveno režīmu atbilstību, jūsu dati ir jāsakārto augošā secībā pēc uzmeklēšanas vērtības. Pretējā gadījumā jūs varat saņemt nepareizi rezultāti . Ņemiet vērā arī to, ka dažkārt var būt pieejami teksta dati Skaties sakārtoti, lai gan tā nav.

Feljēns Hermans šeit ir lielisks šīs problēmas piemērs , no foršas analīzes, ko viņa veica par faktisko Enron izklājlapas!

10. VLOOKUP var apvienot datus dažādās tabulās

VLOOKUP bieži tiek izmantots, lai savienotu datus no divām vai vairākām tabulām. Piemēram, iespējams, ka vienā tabulā ir pasūtījumu dati, bet citā - klientu dati, un vēlaties iekļaut dažus klientu datus pasūtījumu tabulā analīzei.

VLOOKUP sapludina datus, iepriekš pievienojot tabulas

Tā kā klienta ID ir abās tabulās, varat izmantot šo vērtību, lai ievilktu vajadzīgos datus, izmantojot VLOOKUP. Vienkārši konfigurējiet VLOOKUP, lai izmantotu ID vērtību pirmajā tabulā un datus 2. tabulā ar nepieciešamo kolonnu indeksu. Zemāk redzamajā piemērā mēs izmantojam divas VLOOKUP formulas. Viens, lai ievilktu klienta vārdu, un otrs, lai ievilktu klienta stāvokli.

VLOOKUP sapludina datus, savienojot tabulas pēc

Saite: Apvienošanas piemērs ar VLOOKUP .

Video: Kā izmantot VLOOKUP, lai apvienotu tabulas .

11. VLOOKUP var klasificēt vai kategorizēt datus

Ja kādreiz datu ierakstiem ir jāpiemēro patvaļīgas kategorijas, to var viegli izdarīt, izmantojot VLOOKUP, izmantojot tabulu, kas darbojas kā “atslēga” kategoriju piešķiršanai.

Klasisks piemērs ir atzīmes, kurās jums jāpiešķir atzīme, pamatojoties uz punktu skaitu:

VLOOKUP mēdza kategorizēt - piešķirt atzīmes

Šajā gadījumā VLOOKUP ir konfigurēts aptuvenai atbilstībai, tāpēc ir svarīgi tabulu sakārtot augošā secībā.

Bet jūs varat arī izmantot VLOOKUP, lai piešķirtu patvaļīgas kategorijas. Tālāk redzamajā piemērā mēs izmantojam VLOOKUP, lai aprēķinātu grupu katrai nodaļai, izmantojot nelielu tabulu (ar nosaukumu “atslēga”), kas definē grupēšanu.

VLOOKUP mēdza kategorizēt - piešķirt patvaļīgas grupas

12. Absolūtās atsauces padara VLOOKUP pārnēsājamu

Situācijās, kad plānojat izgūt informāciju no vairākām tabulas kolonnām vai ja jums ir nepieciešams kopēt un ielīmēt VLOOKUP, varat ietaupīt laiku un pasliktināšanos, izmantojot uzmeklēšanas vērtības un tabulas masīva absolūtās atsauces. Tas ļauj kopēt formulu un pēc tam mainīt tikai kolonnu indeksa numuru, lai izmantotu to pašu uzmeklēšanu, lai iegūtu vērtību no citas kolonnas.

Piemēram, tā kā uzmeklēšanas vērtība un tabulas masīvs ir absolūti, mēs varam kopēt formulu pa kolonnām, pēc tam atgriezties un pēc vajadzības mainīt sleju indeksu.

Absolūtās atsauces padara VLOOKUP formulas pārnēsājamākas

13. Nosauktie diapazoni padara VLOOKUP vieglāk lasāmu (un pārnēsājamu)

Absolūtie diapazoni izskatās diezgan neglīti, tāpēc var padarīt jūsu VLOOKUP formulas daudz tīrākas un vieglāk lasāmas, aizstājot absolūtās atsauces ar nosauktajiem diapazoniem, kas automātiski ir absolūti.

Piemēram, iepriekš minētajā darbinieku datu piemērā varat ievades šūnai piešķirt nosaukumu “id” un pēc tam tabulā norādīt datus “data”. Formulu varat uzrakstīt šādi:

Nosauktie diapazoni atvieglo VLOOKUP formulas lasīšanu

Šī formula ir ne tikai vieglāk lasāma, bet arī pārnēsājama, jo nosaukti diapazoni automātiski ir absolūti.

14. Kolonnas ievietošana var sabojāt esošās VLOOKUP formulas

Ja darblapā ir esošas VLOOKUP formulas, formulas var salūzt, ja tabulā ievietojat kolonnu. Tas notiek tāpēc, ka kolonnas tiek ievietotas vai izdzēstas, ja ir kodētas kolonnu indeksa vērtības.

Šajā piemērā ranga un pārdošanas uzmeklēšana tika pārtraukta, kad starp gadu un rangu tika ievietota jauna sleja. Gads turpina strādāt, jo tas atrodas ievietotās kolonnas kreisajā pusē:

Kolonnas ievietošana tabulā var sabojāt VLOOKUP

Lai izvairītos no šīs problēmas, varat aprēķināt kolonnu indeksu, kā aprakstīts nākamajos divos padomos.

15. Lai aprēķinātu kolonnu indeksu, varat izmantot ROW vai COLUMN

Ja jūs esat veids, kuru pēc formulas kopēšanas traucē jebkāda rediģēšana, dinamisko kolonnu indeksu ģenerēšanai varat izmantot ROW vai COLUMN. Ja iegūstat datus no secīgām kolonnām, šis triks ļauj iestatīt vienu VLOOKUP formulu un pēc tam kopēt to bez izmaiņām.

Piemēram, izmantojot tālāk norādītos darbinieku datus, mēs varam izmantot funkciju SLEJA, lai ģenerētu dinamisku kolonnu indeksu. Šūnas C3 pirmajai formulai COLUMN pati atgriezīs 3 (jo C lapa darblapā ir trešā), tāpēc mums vienkārši jāatņem viena un jāpārkopē formula:

Piemērs kolonnas izmantošanai, lai aprēķinātu kolonnu indeksu VLOOKUP

Visas formulas ir identiskas, un nav nepieciešama pēcrediģēšana.

Mūsu izmantotā formula ir šāda:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. Izmantojiet VLOOKUP + MATCH, lai iegūtu pilnīgi dinamisku kolonnu indeksu

Ņemot soli tālāk, varat izmantot MATCH, lai tabulā atrastu kolonnas pozīciju un atgrieztu pilnīgi dinamisku kolonnu indeksu.

To dažreiz sauc par divvirzienu uzmeklēšanu, jo jūs meklējat gan rindu, gan kolonnu.

Piemērs varētu būt pārdevēja pārdošanas meklēšana noteiktā mēnesī vai cenas noteikšana konkrētam produktam no konkrēta piegādātāja.

Piemēram, pieņemsim, ka jums ir pārdošanas apjoms mēnesī, sadalot pa pārdevējam:

VLOOKUP divvirzienu uzmeklēšana - kā meklēt mēnesi?

VLOOKUP var viegli atrast pārdevēju, taču tam nav iespējas automātiski apstrādāt mēneša nosaukumu. Viltība ir izmantot funkciju MATCH statiskā kolonnu indeksa vietā.

VLOOKUP divvirzienu uzmeklēšana, izmantojot MATCH, lai iegūtu kolonnu indeksu

Ņemiet vērā, ka mēs piešķiram atbilstības diapazonu, kas ietver visas tabulas kolonnas, lai “sinhronizētu” kolonnu numurus, ko izmanto VLOOKUP.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

Piezīme. Jūs bieži redzēsit divvirzienu meklēšanu, izmantojot INDEX un MATCH - pieeju, kas piedāvā lielāku elastību un labāku veiktspēju lielām datu kopām. Skatiet šajā ātrajā video: Kā veikt divvirzienu meklēšanu, izmantojot INDEX un MATCH .

17. VLOOKUP ļauj aizstāt aizstājējzīmes daļējai atbilstībai

Ikreiz, kad izmantojat VLOOKUP precīzās atbilstības režīmā, uzmeklēšanas vērtībā varat izmantot aizstājējzīmes. Tas var šķist pretintuitīvs, bet aizstājējzīmes ļauj veikt precīzu atbilstību, pamatojoties uz daļēju atbilstību :)

Programmā Excel ir divas aizstājējzīmes: zvaigznīte (*) atbilst vienai vai vairākām rakstzīmēm, bet jautājuma zīme (?) - vienai rakstzīmei.

Piemēram, varat ierakstīt zvaigznīti tieši šūnā un izmantot to VLOOKUP kā uzmeklēšanas vērtību. Tālāk redzamajā ekrānā mēs esam ievadījuši 'Mon*' H3, kas ir nosaukts diapazons ar nosaukumu 'val'. Tādējādi VLOOKUP atbilst nosaukumam 'Monet'.

VLOOKUP ar aizstājējzīmēm - tieši izmantojot zvaigznīti

Formula šajā gadījumā ir vienkārša:

 
= VLOOKUP (val,data,1,0)

Ja vēlaties, varat pielāgot VLOOKUP formulu, lai izmantotu iebūvēto aizstājējzīmi, piemēram, zemāk redzamo piemēru, kur mēs vienkārši savienojam vērtību H3 ar zvaigznīti.

VLOOKUP ar aizstājējzīmēm - zvaigznīte ir savienota ar uzmeklēšanas vērtību

Šajā gadījumā mēs zvaigznīti savienojam ar meklēšanas vērtību VLOOKUP funkcijā:

 
= VLOOKUP (val&'*',data,1,0)

Piezīme. Esiet piesardzīgs, izmantojot aizstājējzīmes un VLOOKUP. Tie sniedz vienkāršu veidu, kā izveidot “slinku maču”, bet arī ļauj viegli atrast nepareizo atbilstību.

18. Jūs varat notvert #N/A kļūdas un parādīt draudzīgu ziņojumu

Precīzās atbilstības režīmā, kad neatbilstība nav atrasta, VLOOKUP parādīs kļūdu #N/A. Vienā ziņā tas ir noderīgi, jo tas noteikti norāda, ka uzmeklēšanas tabulā nav atbilstības. Tomēr #N/A kļūdas nav īpaši jautri aplūkot, tāpēc ir vairāki veidi, kā šo kļūdu notvert un parādīt kaut ko citu.

Kad sākat lietot VLOOKUP, jums var rasties kļūda #N/A, kas rodas, ja VLOOKUP nevar atrast atbilstību.

kā lietot stdev

Šī ir noderīga kļūda, jo VLOOKUP skaidri norāda, ka nevar atrast uzmeklēšanas vērtību. Šajā piemērā tabulā latte nepastāv kā dzēriens, tāpēc VLOOKUP rada kļūdu #N/A

VLOOKUP, kurā ir kļūda #N/A

Formula šajā gadījumā ir pilnīgi standarta precīza atbilstība:

 
= VLOOKUP (E6,data,2,0)

Tomēr #N/A kļūdas nav īpaši jautri skatīties, tāpēc, iespējams, vēlēsities pamanīt šo kļūdu un parādīt draudzīgāku ziņojumu.

Vienkāršākais veids, kā fiksēt kļūdas, izmantojot VLOOKUP, ir iesaiņot VLOOKUP funkcijā IFERROR. IFERROR ļauj “noķert” jebkuru kļūdu un atgriezt jūsu izvēlēto rezultātu.

Lai aizturētu šo kļūdu un parādītu kļūdas vietā ziņojumu “nav atrasts”, varat vienkārši iesaiņot sākotnējo formulu IFERROR iekšpusē un iestatīt vēlamo rezultātu:

VLOOKUP #N/Kļūda, kas ietverta IFERROR

Ja tiek atrasta uzmeklēšanas vērtība, kļūda nenotiek un funkcija VLOOKUP atgriež normālu rezultātu. Šeit ir formula:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. Skaitļi kā teksts var izraisīt atbilstības kļūdu

Dažreiz tabulā, ar kuru strādājat VLOOKUP, var būt skaitļi, kas ievadīti kā teksts. Ja jūs vienkārši izgūstat skaitļus kā tekstu no tabulas kolonnas, tam nav nozīmes. Bet, ja tabulas pirmajā slejā ir skaitļi, kas ievadīti kā teksts, jūs saņemsit kļūdu #N/A, ja uzmeklēšanas vērtība nav arī teksts.

Nākamajā piemērā planētas tabulas ID ir skaitļi ievadīts kā teksts , kas liek VLOOKUP atgriezt kļūdu, jo uzmeklēšanas vērtība ir numurs 3:

Skaitļi, kas ievadīti kā teksta VLOOKUP kļūdas piemērs

Lai atrisinātu šo problēmu, jums jāpārliecinās, vai uzmeklēšanas vērtība un tabulas pirmā sleja ir viens un tas pats datu tips (vai nu abi skaitļi, vai abi teksti).

Viens veids, kā to izdarīt, ir pārvērst meklēšanas kolonnā esošās vērtības skaitļos. Vienkāršs veids, kā to izdarīt, ir pievienot nulli, izmantojot paste special.

Ja jums nav viegli kontrolēt avota tabulu, varat arī pielāgot VLOOKUP formulu, lai pārvērstu uzmeklēšanas vērtību tekstā, apvienojot '' uz vērtību šādi:

 
= VLOOKUP (id&'',planets,2,0)

Skaitļi ievadīti kā teksta VLOOKUP kļūdas risinājums

Ja nevarat būt pārliecināts, kad jums būs cipari un kad būs teksts, varat apmierināt abas iespējas, iesaiņojot VLOOKUP IFERROR un uzrakstot formulu, lai apstrādātu abus gadījumus:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. VLOOKUP varat izmantot, lai aizstātu ligzdotos IF paziņojumus

Viens no interesantākajiem VLOOKUP lietojumiem ir ligzdotu IF paziņojumu aizstāšana. Ja esat kādreiz izveidojis ligzdotu IF sēriju, jūs zināt, ka tie darbojas labi, taču tiem ir nepieciešama neliela iekavu ķilda. Jums arī jābūt uzmanīgam attiecībā uz kārtību, kādā strādājat, lai neieviestu loģisku kļūdu.

Piemēram, ligzdotos IF parasti izmanto, lai piešķirtu atzīmes, pamatojoties uz sava veida rezultātu. Tālāk redzamajā piemērā varat redzēt, ka formula ir izveidota ar ligzdotiem IF, lai to izdarītu, izmantojot atzīmi labajā pusē kā norādījumu.

Novērtējumu piešķiršana ar garu ligzdotu IF formulu

Pilna ligzdotā IF formula izskatās šādi:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Tas darbojas labi, taču ņemiet vērā, ka gan loģika, gan faktiskie rādītāji tiek iekļauti formulā. Ja vērtējums kāda iemesla dēļ mainās, jums rūpīgi jāatjaunina viena formula un pēc tam jānokopē visa tabula.

Turpretī VLOOKUP var piešķirt vienādas atzīmes ar vienkāršu formulu. Viss, kas jums jādara, ir pārliecināties, ka atzīmju atslēgu tabula ir iestatīta VLOOKUP.

Pēc pakāpes atslēgu tabulas nosaukta diapazona atslēgas definēšanas VLOOKUP formula ir ļoti vienkārša un ģenerē tādas pašas pakāpes kā sākotnējā ligzdotā IF formula:

Novērtējumu piešķiršana ar vienkāršu VLOOKUP formulu

Ar pakāpes atslēgu tabulu ar nosaukumu “atslēga” mums ir ļoti vienkārša VLOOKUP formula:

 
= VLOOKUP (C5,key,2,TRUE)

Jauka šīs pieejas priekšrocība ir tā, ka gan loģika, gan rādītāji ir iebūvēti pakāpju tabulā. Ja kaut kas mainās, varat vienkārši atjaunināt tabulu tieši, un VLOOKUP formulas tiks automātiski atjauninātas - rediģēšana nav nepieciešama.

Video: Kā nomainīt ligzdotos IF ar VLOOKUP

21. VLOOKUP var apstrādāt tikai vienu kritēriju

Pēc konstrukcijas VLOOKUP var atrast vērtības, pamatojoties tikai uz atsevišķiem kritērijiem, kas tiek piegādāti kā uzmeklēšanas vērtība, kas atrodama tabulas pirmajā slejā (uzmeklēšanas kolonna).

Tas nozīmē, ka jūs nevarat viegli veikt tādas darbības kā uzmeklēt darbinieku ar uzvārdu “Smits” sadaļā “Grāmatvedība” vai meklēt darbinieku, pamatojoties uz vārdu un uzvārdu atsevišķās slejās.

Tomēr ir veidi, kā pārvarēt šo ierobežojumu. Viens risinājums ir izveidot palīgu kolonnu, kas apvieno dažādu kolonnu vērtības, lai izveidotu uzmeklēšanas vērtības, kas darbojas kā vairāki nosacījumi. Piemēram, šeit mēs vēlamies atrast nodaļu un grupu darbiniekam, bet vārds un uzvārds parādās atsevišķās slejās. Kā mēs varam meklēt abus vienlaikus?

VLOOKUP vairāku kritēriju problēma - kā meklēt gan vārdu, gan uzvārdu?

Vispirms pievienojiet palīgu kolonnu, kurā vienkārši tiek apvienoti vārdi un uzvārdi:

VLOOKUP vairāku kritēriju 2. darbība - pievienojiet palīgu kolonnu, kas savieno vairākus kritērijus

Pēc tam konfigurējiet VLOOKUP, lai izmantotu tabulu, kurā ir šī jaunā sleja, un pievienojiet uzmeklēšanas vērtības vārdus un uzvārdus:

VLOOKUP vairāku kritēriju 3. darbība - apvienojiet kritērijus, lai izveidotu uzmeklēšanas vērtību

Galīgajā VLOOKUP formulā tiek meklēti vārdi un uzvārdi kopā, izmantojot kā palīgu kolonnu:

 
= VLOOKUP (C3&D3,data,4,0)

22. Divi VLOOKUPS ir ātrāki par vienu VLOOKUP

Tas var šķist pilnīgi traki, bet, ja jums ir liels datu kopums un jums ir jāveic precīza atbilstība, jūs varat ievērojami paātrināt VLOOKUP, pievienojot formulai vēl vienu VLOOKUP!

Priekšvēsture: iedomājieties, ka jums ir daudz pasūtījumu datu, teiksim, vairāk nekā 10 000 ierakstu un jūs izmantojat VLOOKUP, lai uzmeklētu kopējo pasūtījumu, pamatojoties uz pasūtījuma ID. Tātad, jūs izmantojat kaut ko līdzīgu:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

FALSE beigās liek VLOOKUP veikt precīzu atbilstību. Jūs vēlaties precīzu atbilstību, jo pastāv iespēja, ka pasūtījuma numurs netiks atrasts. Šādā gadījumā precīzās atbilstības iestatījuma dēļ VLOOKUP tiks atgriezta kļūda #N/A.

Problēma ir tā, ka precīzās atbilstības ir patiešām lēnas, jo programmai Excel ir jāturpinās lineāri, izmantojot visas vērtības, līdz tiek atrasta atbilstība.

Un otrādi, aptuvenās atbilstības ir zibenīgi ātras, jo Excel spēj veikt tā saukto a binārā meklēšana .

Tomēr bināro meklējumu problēma (ti, VLOOKUP aptuvenās atbilstības režīmā) ir tāda, ka VLOOKUP var atgriezt nepareizu rezultātu, ja vērtība netiek atrasta. Sliktāk, rezultāts varētu izskatīties pilnīgi normāls, tāpēc to var būt ļoti grūti pamanīt.

Risinājums ir izmantot VLOOKUP divas reizes, abas reizes aptuvenās atbilstības režīmā. Pirmā instance vienkārši pārbauda, ​​vai vērtība patiešām pastāv. Ja tā, tiek palaists cits VLOOKUP (atkal aptuvenas atbilstības režīmā), lai iegūtu vajadzīgos datus. Ja nē, varat atgriezt jebkuru vērtību, kuru vēlaties norādīt, ka rezultāts netika atrasts.

Galīgā formula izskatās šādi:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

Es uzzināju šo pieeju no Čārlza Viljamsa no FastExcel, kuram šeit ir fantastisks, detalizēts raksts: Kāpēc 2 VLOOKUPS ir labāki par 1 VLOOKUP .

Piezīme. Lai izmantotu šo triku, jūsu dati ir jāsakārto. Tas ir vienkārši veids, kā aizsargāties pret trūkstošo uzmeklēšanas vērtību, vienlaikus saglabājot ātru uzmeklēšanu.

23. INDEX un MATCH kopā var darīt visu, ko VLOOKUP, un vēl vairāk

Ja sekojat Excel tiešsaistē, jūs, iespējams, sasniegsit VLOOKUP pret INDEX/MATCH debates. Strīds var kļūt pārsteidzoši karsts :)

Būtība ir šāda: INDEX + MATCH var paveikt visu, ko var veikt VLOOKUP (un HLOOKUP), ar daudz lielāku elastību un samaksu par nedaudz sarežģītāku. Tātad, tie, kas atbalsta INDEX + MATCH, apgalvos (ļoti prātīgi), ka jūs varētu arī sākt mācīties INDEX un MATCH, jo tas galu galā sniedz labāku rīku komplektu.

Arguments pret INDEX + MATCH ir tāds, ka tam ir vajadzīgas divas funkcijas, nevis viena, tāpēc lietotājiem (īpaši jauniem lietotājiem) pēc būtības ir sarežģītāk mācīties un apgūt.

Mani divi centi ir tādi, ka, bieži lietojot Excel, jūs vēlēsities iemācīties lietot INDEX un MATCH. Tā ir ļoti spēcīga kombinācija.

Bet es arī domāju, ka jums vajadzētu iemācīties VLOOKUP, ar kuru jūs sastapsities visur, bieži darblapās, kuras mantojat no citiem. Vienkāršās situācijās VLOOKUP veiks darbu bez problēmām.

Lai uzzinātu vairāk par INDEX un MATCH, skatiet šo rakstu .

Autors Deivs Bruns


^