VLOOKUP programmā Excel, 2. daļa VLOOKUP izmantošana bez datu bāzes

Nesenajā rakstā mēs ieviesām Excel funkciju VLOOKUP un paskaidroja, kā to varētu izmantot, lai izgūtu informāciju no datubāzes vietējā darblapā. Šajā rakstā mēs pieminējām, ka VLOOKUP izmantoja divus lietojumus, un tikai viens no tiem nodarbojās ar vaicājumu datubāzēm. Šajā rakstā, otrajā un galīgajā VLOOKUP sērijā, mēs pārbaudām šo citu, mazāk zināmo VLOOKUP funkcijas izmantošanu.

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

Strādājot ar datu bāzēm, VLOOKUP tiek nodots “unikāls identifikators”, kas kalpo, lai noteiktu, kuru datu ierakstu mēs vēlamies atrast datubāzē (piemēram, produkta kodu vai klienta ID). Šis unikālais identifikators jābūt pastāv datu bāzē, pretējā gadījumā VLOOKUP atgriež mums kļūdu. Šajā rakstā mēs izskatīsim veidu, kā izmantot VLOOKUP, kur identifikatoram vispār nav nepieciešams. Tas ir gandrīz kā tad, ja VLOOKUP var pieņemt „pietiekami tuvu ir pietiekami laba” pieeja atgrieztajiem datiem. Noteiktos apstākļos tas ir precīzi kas mums ir vajadzīgs.

Mēs ilustrēsim šo rakstu ar reālu pasaules piemēru - komisiju aprēķināšanu, kas rodas no pārdošanas rādītājiem. Mēs sāksim ar ļoti vienkāršu scenāriju un pēc tam 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īvajā 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, komisijas nauda, ko tie nopelnījuši par 30%. Pretējā gadījumā to komisijas maksa ir tikai 20%. Līdz šim šī ir diezgan vienkārša darblapa:

Lai izmantotu šo darblapu, pārdevējs ievada savus pārdošanas rādītājus B1 šūnā, un B2 šūnas formula aprēķina pareizo komisijas likmi, ko viņiem ir tiesības saņemt, ko izmanto B3 šūnā, lai aprēķinātu kopējo komisijas maksu, ko pārdevējs ir parādā (kas ir vienkāršs B1 un B2 reizinājums).

Šūnā B2 ir vienīgā šīs darblapas interesanta daļa - formula, lai noteiktu, kādu komisijas maksu izmantot: vienu zemāk 30 000 ASV dolāru vai vienu virs slieksni. Šī formula izmanto Excel funkciju, ko sauc IF. Tiem lasītājiem, kas nav iepazinušies ar IF, tas darbojas šādi:

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

Kur stāvoklis ir izteiksme, kas novērtē vai nu taisnība vai nepatiesa. Iepriekš minētajā piemērā stāvoklis ir izteiksme B1, ko var lasīt kā “Vai B1 ir mazāks par B5?”, vai, citādi, „Vai kopējais pārdošanas apjoms ir mazāks par slieksni”. Ja atbilde uz šo jautājumu ir „jā” (taisnība), tad mēs izmantojam vērtība, ja tā ir patiesa funkcijas parametrs B6 šajā gadījumā - komisijas maksa, ja pārdošanas kopsumma bija zemāk slieksni. Ja atbilde uz jautājumu ir “nē” (nepatiesa), tad mēs izmantojam vērtība, ja tā ir nepatiesa funkcijas parametrs B7 šajā gadījumā - komisijas maksa, ja pārdošanas kopsumma bija virs slieksni.

Kā redzat, pārdošanas kopsummā $ 20,000 dod mums komisijas likmi 20% B2 šūnā. Ja ievadīsim vērtību $ 40,000, mēs saņemam atšķirīgu komisijas maksu:

Tātad mūsu izklājlapa darbojas.

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

Pietiekami viegli saprast reālajā pasaulē, bet B2 šūnā mūsu formula kļūst sarežģītāka. Ja jūs uzmanīgi skatāties uz formulu, redzēsiet, ka trešā sākotnējā IF funkcijas parametrs ( vērtība, ja tā ir nepatiesa) tagad ir pati IF funkcija. To sauc par ligzdota funkcija (funkcija funkcijā). Tas ir pilnīgi derīgs Excel (tas pat darbojas!), Bet ir grūtāk lasīt un saprast.

Mēs nenonāksim uz riekstiem un bultskrūvēm, kā un kāpēc tas darbojas, kā arī neanalizēsim ligzdoto funkciju nianses. Šī ir apmācība par VLOOKUP, nevis uz Excel kopumā.

Anyway, tas pasliktinās! Kā tad, kad mēs nolemjam, ka, ja viņi nopelnīs vairāk nekā $ 50,000, tad viņiem ir tiesības uz 50% komisijas maksu, un, ja viņi nopelnīs vairāk nekā $ 60,000, tad viņiem ir tiesības uz 60% komisijas maksu?

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

Tur noteikti ir. VLOOKUP glābšanai!

Pārveidosim darblapu mazliet. Mēs saglabāsim visus tos pašus skaitļus, bet organizēsim to jaunā veidā tabulas veids:

Uzņemiet brīdi un pārbaudiet, vai jaunais Tarifu tabula darbojas tieši tāpat kā iepriekš minēto sliekšņu sērija.

Konceptuāli tas, ko mēs gatavojamies darīt, ir izmantot VLOOKUP, lai meklētu pārdevēja pārdošanas kopapjomu (no B1) likmju tabulā un atgrieztu mums atbilstošo komisijas maksu. Ņemiet vērā, ka pārdevējs, iespējams, ir radījis pārdošanu ne viena no piecām likmju tabulas vērtībām ($ 0, $ 30,000, $ 40,000, $ 50,000 vai $ 60,000). Tie, iespējams, ir izveidojuši 34,988 ASV dolārus. Ir svarīgi atzīmēt, ka $ 34,988 dara ne parādās likmes tabulā. Apskatīsim, vai VLOOKUP var atrisināt mūsu problēmu jebkurā gadījumā ...

Mēs izvēlamies šūnu B2 (atrašanās vietu, kuru vēlamies ievietot mūsu formulu), un pēc tam ievietojiet VLOOKUP funkciju no Formulas cilne:

The Funkciju argumenti parādās VLOOKUP lodziņš. Mēs aizpildām argumentus (parametrus) pa vienam, sākot ar Lookup_value, kas šajā gadījumā ir pārdošanas kopsumma no šūnām B1. Mēs novietojam kursoru uz Lookup_value laukā un pēc tam noklikšķiniet uz šūnas B1:

Tālāk VLOOKUP ir jānorāda, kāda tabula ir šo datu meklēšanai. Šajā piemērā, protams, tā ir likmju tabula. Mēs novietojam kursoru uz Table_array lauks un pēc tam iezīmējiet visu likmju tabulu - izņemot pozīcijas:

Tālāk ir jānorāda, kura tabulas sleja 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 atrodama tabulas otrajā ailē, tāpēc mēs ievadām a 2 iekšā Col_index_num lauks:

Visbeidzot mēs ievadām vērtību Range_lookup jomā.

Svarīgi: Šī lauka izmantošana atšķir divus VLOOKUP izmantošanas veidus. Lai izmantotu VLOOKUP ar datubāzi, šis galīgais parametrs, Range_lookup, vienmēr ir jāiestata FALSE, bet ar šo citu VLOOKUP lietojumu mums ir vai nu jāatstāj tukša, vai jāievada vērtība TRUE. Lietojot VLOOKUP, ir svarīgi, lai jūs izdarītu pareizo izvēli attiecībā uz šo galīgo parametru.

Lai būtu skaidrs, mēs ievadīsim vērtību taisnība iekš Range_lookup jomā. Būtu labi arī atstāt to tukšu, jo tā ir noklusējuma vērtība:

Mēs esam pabeiguši visus parametrus. Tagad mēs noklikšķinām uz labi poga, un Excel izveido mūsu VLOOKUP formulu:

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

Secinājums

VLOOKUP “datu bāzes” versijā, kur Range_lookup parametrs ir FALSE, vērtība, kas nodota pirmajā parametrā (Lookup_value) jābūt būt datu bāzē. Citiem vārdiem sakot, mēs meklējam precīzu atbilstību.

Bet šajā citā VLOOKUP izmantoš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 par pārdošanas kopsummu $ 34,988, mūsu VLOOKUP formula atgriezīs mums 30% vērtību, kas ir pareizā atbilde. Kāpēc tā izvēlējās tabulas rindu, kurā bija 30%? Kas patiesībā nozīmē “pietiekami tuvu”? Būsim precīzi:

Kad Range_lookup ir iestatīts uz TRUE (vai izlaists), VLOOKUP izskatīsies 1. slejā un atbilst augstākā vērtība, kas nav lielāka par. \ t the Lookup_value parametru.

Ir svarīgi arī atzīmēt, ka šī sistēma darbojas, tabula ir sakārtota augošā secībā 1. slejā!

Ja vēlaties izmantot VLOOKUP, šajā rakstā parādīto paraugu failu var lejupielādēt no šejienes.