VLOOKUP programmā Excel, 2. daļa: VLOOKUP izmantošana bez datubāzes

Nesenajā rakstā mēs ieviesām Excel funkciju VLOOKUP un paskaidroja, kā to varētu izmantot, lai no vietnes darblapā iegūtu informāciju no datubāzes šūnā.Šajā rakstā mēs minējām, ka VLOOKUP bija divi lietojumi, un tikai viens no tiem izmantoja vaicājumu datubāzes.Šajā rakstā, otrā un pēdējā VLOOKUP sērijā, mēs pārbaudām šo citu, mazāk zināmo izmantošanu VLOOKUP funkciju.

Ja jūs vēl neesat to izdarījis, lūdzu, izlasiet pirmo VLOOKUP rakstu - šajā rakstā tiks pieņemts, ka daudzi no šajā pantā paskaidrotajiem jēdzieniem jau ir zināmi lasītājam.

Strādājot ar datu bāzēm, VLOOKUP tiek nodots "unikālais identifikators", kas kalpo, lai noteiktu, kuru datu ierakstu mēs meklējam datu bāzē( piem., Produkta kodu vai klienta ID).Šim unikālajam identifikatoram jābūt , kas atrodas datu bāzē, pretējā gadījumā VLOOKUP atgriež mums kļūdu.Šajā rakstā mēs izpētīsim veidu, kā izmantot VLOOKUP, ja identifikatoram vispār nav nepieciešams eksistēt. Tas ir gandrīz tā, it kā VLOOKUP varētu pieņemt "pietiekami tuvu ir pietiekami laba" pieeja, lai atgrieztu meklējamos datus. Noteiktos apstākļos tas ir

precīzi , kas mums nepieciešams.

Mēs ilustrēsim šo rakstu ar reālu piemēru - komisiju, kas tiek aprēķinātas par pārdošanas rādītāju kopumu, aprēķināšanai. Mēs sāksim ar ļoti vienkāršu scenāriju, un tad pakāpeniski padarīsim to sarežģītāku, līdz vienīgais racionālais problēmas risinājums ir izmantot VLOOKUP.Sākotnējais scenārijs mūsu fiktīvā uzņēmumā darbojas šādi: ja pārdevējs konkrētajā gadā rada vairāk nekā 30 000 ASV dolāru vērtu pārdošanas apjomu, komisija, ko viņi nopelna par šiem pārdošanas darījumiem, ir 30%.Pretējā gadījumā to komisija ir tikai 20%.Līdz šim tas ir diezgan vienkāršs darblapas:

Lai izmantotu šo darba lapu, pārdevējs ieraksta savus pārdošanas rādītājus šūnā B1, bet formula B2 skaitā aprēķina pareizo komisijas likmi, kuru tie ir tiesīgi saņemt, ko izmanto, lai aprēķinātu šūnu B3kopējā komisijas nauda, ​​kas pārdevējam pienākas( kas ir vienkāršs B1 un B2 reizinājums).

Šūnu B2 ir vienīgā interesanta šī darba lapas daļa - formula, kā izlemt, kura komisijas maksa jāizmanto: viena zem ir robežvērtība 30 000 USD vai viena virs sliekšņa.Šī formula izmanto Excel funkciju IF .Tiem lasītājiem, kas nav pazīstami ar IF, tas darbojas šādi:

IF( stāvoklis, vērtība, ja ir patiesība, vērtība, ja ir nepatiesa )

Ja nosacījums ir izteiksme, kas novērtē vai nu patieso vai false .Iepriekš minētajā piemērā nosacījums ir izteiksme B1 , ko var izlasīt kā "Vai B1 ir mazāks par B5?", Vai arī citādi norādot: "Vai kopējais pārdošanas apjoms ir mazāks par slieksni".Ja atbilde uz šo jautājumu ir "jā"( patiesa), tad mēs izmantojam vērtību, ja šajā gadījumā patiesais parametrs, proti, B6 , šajā gadījumā - komisijas likme, ja pārdošanas apjoms bija zem sliekšņa .Ja atbilde uz jautājumu ir "nē"( false), tad mēs izmantojam vērtību, ja šajā gadījumā ir kļūdains parametrs, proti, B7 - šajā gadījumā komisijas kurss, ja pārdošanas apjoms bija virs sliekšņa.

Kā redzat, izmantojot 20 000 ASV dolāru pārdošanas apjomu, mēs piedāvājam komisijas likmi 20% šūnā B2.Ja mēs ievadīsim vērtību 40 000 ASV dolāru, mēs saņemsim atšķirīgu komisijas maksu:

Tātad mūsu izklājlapa darbojas.

Padarīsim to sarežģītāku. Ievadīsim otru slieksni: ja pārdevējs nopelna vairāk nekā 40 000 ASV dolāru, tad tā komisijas likme palielinās līdz 40%:

Viegli saprast reālajā pasaulē, bet B2 šūnā mūsu formula kļūst sarežģītāka. Ja paskatās uz formulu, jūs redzēsiet, ka oriģinālo IF funkciju trešais parametrs( vērtība, ja ir nepareiza ) tagad ir pilnīga IF funkcija pati par sevi. To sauc par ligzdoto funkciju ( funkcijas ietvaros funkcija).Tas ir pilnīgi derīgs programmā Excel( tas pat darbojas!), Taču to ir grūtāk lasīt un saprast.

Mēs neieskatīsim uzgriežņus un bultskrūves par to, kā un kāpēc tas darbojas, kā arī netiks pārbaudītas ligzdotu funkciju nianses.Šī ir apmācība par VLOOKUP, nevis Excel kopumā.

Jebkurā gadījumā tas pasliktinās! Kā tad, kad mēs nolemjam, ka, ja viņi nopelna vairāk nekā 50 000 ASV dolāru, tad viņiem ir tiesības uz 50% komisijas naudu un ja viņi nopelna vairāk nekā 60 000 ASV dolāru, tad viņiem ir tiesības uz 60% komisijas maksu?

Tagad formula B2 šūnā, lai arī pareiza, ir kļuvusi gandrīz neizlasāma. Nevienam nevajadzētu rakstīt formulas, kurās funkcijas ir nostiprinātas četros līmeņos dziļi! Protams, ir jābūt vienkāršākam?

Tur noteikti ir. VLOOKUP glābšanai!

Nedaudz pārstrādājiet darblapu. Mēs saglabāsim visus vienādos skaitļus, bet organizēsim to jaunā veidā, vairāk tabulas veidā:

Veikt brīdi un pārliecinieties, ka jaunā tabulas tabula darbojas tieši tāpat kā iepriekš minēto sliekšņu virkne.

Konceptuāli, ko mēs gatavojamies, ir izmantot VLOOKUP, lai meklētu pārdevēju pārdošanas kopsummu( no B1) tarifu tabulā un atgrieztu pie mums atbilstošo komisijas maksu.Ņemiet vērā, ka pārdevējs patiešām ir radījis pārdošanas apjomu, kas ir , nevis viena no piecām vērtībām tabulas tabulā( $ 0, $ 30,000, $ 40,000, $ 50000 vai $ 60,000).Viņi, iespējams, ir izveidojuši pārdošanas apjomu 34 988 ASV dolāri. Svarīgi atzīmēt, ka tabulas tabulā parādās $ 34,988 vai , nevis .Apskatīsim, vai VLOOKUP jebkurā gadījumā var atrisināt mūsu problēmu. ..

Mēs izvēlamies šūnu B2( vietu, kurā mēs vēlamies ievietot mūsu formulu), un pēc tam ievietojiet VLOOKUP funkciju no formulas tab:

Parādās funkciju argumenti lodziņš VLOOKUP.Mēs aizpildām argumentus( parametrus) pa vienam, sākot ar LookUp_value , kas šajā gadījumā ir kopējais pārdošanas apjoms no šūnas B1.Mēs ievietojam kursoru laukā Lookup_value un pēc tam vienreiz noklikšķiniet uz šūnas B1:

. Tālāk mums ir jānorāda VLOOKUP, kurā tabulā ir jāpārraida šie dati. Šajā piemērā, protams, ir tabulas tabula. Mēs novieto kursoru laukā Table_array un pēc tam iezīmē visu ātruma tabulu - , izņemot pozīcijas :

Tālāk mums jānorāda, kura sleja tabulā satur informāciju, kuru mēs vēlamies, lai mūsu formula atgrieztos pie mums.Šajā gadījumā mēs vēlamies komisijas likmi, kas ir atrodama tabulas otrajā ailē, tāpēc mēs laukā ierakstiet 2 laukā Col_index_num :

. Visbeidzot, ievadiet vērtību laukā Range_lookup .

Svarīgi: šī joma tiek izmantota, lai atšķirtu abus VLOOKUP izmantošanas veidus. Lai izmantotu VLOOKUP ar datubāzi, šis galīgais parametrs Range_lookup vienmēr ir jāiestata kā FALSE , bet, izmantojot šo citu VLOOKUP izmantošanu, mums vai nu atstājiet tukšu vai ievadiet TRUE vērtību. Lietojot VLOOKUP, ir svarīgi, lai jūs izdarītu pareizu izvēli šim galīgajam parametram.

Lai būtu skaidra, mēs ievadīsim patieso vērtību Range_lookup laukā.Būtu arī labi atstāt tukšu, jo tā ir noklusējuma vērtība:

Mēs esam pabeidzuši visus parametrus. Mēs tagad noklikšķinām uz OK pogas, un Excel izveido mūsu VLOOKUP formulu mums:

. Ja mēs eksperimentējam ar dažām atšķirīgām pārdošanas kopapjomām, mēs varam pārliecināties, ka šī formula darbojas.

Secinājums

VLOOKUP "datubāzē", kur Range_lookup parametrs ir FALSE , pirmajā parametrā( Lookup_value ) jābūt jābūt datu bāzē.Citiem vārdiem sakot, mēs meklējam precīzu atbilstību.

Bet šajā citā VLOOKUP lietošanā mēs ne vienmēr meklējam precīzu atbilstību.Šajā gadījumā "pietiekami tuvu ir pietiekami labs".Bet ko mēs domājam ar "pietiekami tuvu"?Izmantosim piemēru. Meklējot komisijas likmi pārdošanas kopsummā 34 988 ASV dolāri, mūsu VLOOKUP formula atgriezīs mums vērtību 30% apmērā, kas ir pareizā atbilde. Kāpēc tā izvēlējās rindu tabulā, kurā ir 30%?Kas patiesībā nozīmē "pietiekami tuvu" šajā gadījumā?Būsim precīzi:

Kad Range_lookup ir iestatīts uz TRUE ( vai izlaists), VLOOKUP izskatīsies 1. slejā un atbilst augstākajai vērtībai, kas nav lielāka par parametru lookup_value .

Tāpat ir svarīgi atzīmēt, ka, lai šī sistēma darbotos, tabulai jābūt šķirai augošā secībā 1. kolonnā !

Ja jūs vēlētos praktizēt ar VLOOKUP, šajā rakstā attēlotā parauga failu var lejupielādēt no šejienes.