Nesenajā rakstā mēs ieviesām Excel funkciju, ko sauc VLOOKUP un paskaidroja, kā to var izmantot, lai no vietnes darblapā ielādē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 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, kurš datu ieraksts mēs meklējam datu bāzē (piemēram, produkta kods 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 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. Atsevišķos gadījumos tas ir tieši tā kas mums nepieciešams.
Mēs ilustrēsim šo rakstu ar reālu piemēru - aprēķinot komisijas, kas tiek iegūtas par pārdošanas rādītāju kopumu. 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 darblapu, pārdevējs ieraksta savus pārdošanas rādītājus šūnā B1, un formula B2 skaitā aprēķina pareizo komisijas likmi, ko tie ir tiesīgi saņemt, ko izmanto šūnā B3, lai aprēķinātu kopējo komisijas naudu, kas pārdevējam jāmaksā (kas ir vienkāršs B1 un B2 reizinājums).
Šūnu B2 ir vienīgā interesanta šī darba lapas daļa - formula, kā izlemt, kāda komisijas likme jāizmanto: viena zemāk slieksnis 30 000 USD vai viens virs slieksnis. Šī formula izmanto Excel funkciju, ko sauc IF. Tiem lasītājiem, kas nav pazīstami ar IF, tas darbojas šādi:
IF (nosacījums, vērtība, ja ir patiesa, vērtība ja nepatiesa)
Kur nosacījums ir izteiksme, ko vērtē vai nu taisnība vai viltus. Iepriekš minētajā piemērā nosacījums ir izteiciens B1<>, ko var izlasīt kā "Vai B1 ir mazāks par B5?", vai arī citā veidā: "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ība, ja patiess parametrs, proti, B6 šajā gadījumā - komisijas likme, ja pārdošanas apjoms bija zemāk slieksnis. Ja atbilde uz jautājumu ir "nē" (false), tad mēs izmantojam vērtība, ja ir nepatiesa parametrs, proti, B7 šajā gadījumā - komisijas likme, ja pārdošanas apjoms bija virs slieksnis.
Kā redzat, izmantojot pārdošanas kopsummu 20 000 ASV dolāru apmērā, tiek nodrošināta komisijas likme 20% šūnā B2. Ja mēs ievadīsim vērtību 40 000 ASV dolāru apmērā, saņemsim atšķirīgu komisijas maksu:
Tātad mūsu izklājlapa darbojas.
Padaram to sarežģītāku. Ievadīsim otru slieksni: ja pārdevējs nopelna vairāk nekā 40 000 dolāru, tad komisijas maksa palielināsies līdz 40%:
Viegli saprotama 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 nepatiesa) tagad ir pilnīga IF funkcija pati par sevi. To sauc par ligzdota funkcija (funkcija funkcijā). 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 skrū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?
Protams, tur ir. VLOOKUP glābšanai!
Nedaudz pārstrādājam darblapu. Mēs saglabāsim visus vienādos skaitļus, bet organizēsim to jaunā veidā, vēl vairāk tabula veids:
Veikt kādu brīdi un pārliecinieties, ka jaunais Tabulā darbojas tieši tāpat kā iepriekš minēto sliekšņu virkne.
Konceptuāli, ko mēs gatavojamies darīt, ir izmantot VLOOKUP, lai meklētu pārdevēju pārdošanas apjomu (no B1) tarifu tabulā un atgrieztu pie mums atbilstošo komisijas maksu. Ņemiet vērā, ka pārdevējs patiešām varēja radīt pārdošanas apjomu nē viena no piecām vērtībām tabulas tabulā ($ 0, $ 30,000, $ 40,000, $ 50,000 vai $ 60,000). Viņi, iespējams, ir izveidojuši pārdošanas apjomu 34 988 ASV dolāri. Ir svarīgi atzīmēt, ka $ 34,988 dara nē parādās kursa tabulā. Apskatīsim, vai VLOOKUP jebkurā gadījumā var atrisināt mūsu problēmu ...
Mēs izvēlamies šūnu B2 (vietu, kurā vēlamies ievietot mūsu formulu), un pēc tam ievietojiet VLOOKUP funkciju no Formulas cilnē:
The Funkcijas Argumenti parādās 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 šūnu B1. Mēs ievietojam kursoru Lookup_value laukā un pēc tam vienreiz noklikšķiniet uz šūnas B1:
Nākam mums jāprecizē VLOOKUP, uz kuru tabulu ir jāreaģē šie dati. Šajā piemērā, protams, ir tabulas tabula. Mēs ievietojam kursoru Table_array laukā un pēc tam iezīmējiet 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ā slejā, tādēļ mēs ievadām a 2 iekšā Col_index_num lauks:
Visbeidzot, mēs ievadam vērtību Range_lookup laukā.
Svarīgi: šī joma tiek izmantota, lai atšķirtu abus VLOOKUP izmantošanas veidus. Lai izmantotu VLOOKUP ar datubāzi, šo galīgo parametru, Range_lookup, vienmēr jābūt iestatītam uz FĀDS, bet ar šo citu VLOOKUP izmantošanu, mums vai nu atstājiet to tukšu vai ievadiet vērtību TRUE. Lietojot VLOOKUP, ir svarīgi, lai jūs izdarītu pareizu izvēli šim galīgajam parametram.
Lai būtu skaidrs, mēs ievadīsim vērtību taisnība iekš Range_lookup laukā. Būtu arī kārtīgi 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 labi pogu un Excel izveido mūsu VLOOKUP formulu mums:
Ja mēs eksperimentējam ar dažām dažādām pārdošanas kopapjomām, mēs varam pārliecināties, ka šī formula darbojas.
Secinājums
VLOOKUP versijā "datubāze", kur Range_lookup parametrs ir FĀDS, vērtība, kas nodota pirmajā parametrā (Lookup_value) jābūt būt klāt datubāzē. Citiem vārdiem sakot, mēs meklējam precīzu atbilstību.
Bet šajā citā VLOOKUP izmantojumā 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 kopējo pārdošanas apjomu 34 988 ASV dolāri, mūsu VLOOKUP formula atgriež mums vērtību 30%, 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 skatīsies 1. slejā un saskaņosies augstākā vērtība, kas nav lielāka par the Lookup_value parametrs.
Ir arī svarīgi atzīmēt, ka, lai šī sistēma darbotos, tabula jāsadala 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.
