VLOOKUP ir viena no Excel vispazīstamākajām funkcijām. Parasti to izmantosit, lai meklētu precīzas atbilstības, piemēram, produktu vai klientu ID, taču šajā rakstā mēs izpētīsim, kā izmantot VLOOKUP ar vērtību diapazonu.
Pirmais piemērs: VLOOKUP izmantošana, lai eksāmena rezultātiem piešķirtu burtu atzīmes
Piemēram, pieņemsim, ka mums ir eksāmenu punktu saraksts un mēs vēlamies katram rezultātam piešķirt atzīmi. Mūsu tabulas A slejā ir parādīti faktiskie eksāmenu rezultāti, un kolonna B tiks izmantota, lai parādītu mūsu aprēķinātos burtu vērtējumus. Mēs esam arī izveidojuši tabulu labajā pusē (D un E kolonnas), kas parāda katras burta pakāpes sasniegšanai nepieciešamo punktu skaitu.
Izmantojot VLOOKUP, mēs varam izmantot diapazona vērtības kolonnā D, lai piešķirtu burtu atzīmes kolonnā E visiem faktiskajiem eksāmenu rezultātiem.
VLOOKUP formula
Pirms sākam lietot formulu savam piemēram, īsi atgādināsim par VLOOKUP sintaksi:
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Šajā formulā mainīgie darbojas šādi:
lookup_value: šī ir vērtība, kuru meklējat. Mums šis ir rezultāts A slejā, sākot ar šūnu A2.
table_array: to bieži neoficiāli dēvē par uzmeklēšanas tabulu. Mums šī ir tabula, kurā ir ietverti rādītāji un saistītie vērtējumi (diapazons D2:E7).
col_index_num: tas ir kolonnas numurs, kurā tiks ievietoti rezultāti. Mūsu piemērā tā ir B kolonna, taču, tā kā komandai VLOOKUP ir nepieciešams skaitlis, tā ir 2. kolonna.
range_lookup> Šis ir loģiskās vērtības jautājums, tāpēc atbilde ir patiesa vai nepatiesa. Vai veicat diapazona meklēšanu? Mums atbilde ir jā (vai “TRUE” VLOOKUP izteiksmē).
Mūsu piemēra pabeigtā formula ir parādīta zemāk:
=VLOOKUP(A2,$D$2:$E$7,2,TRUE)
Tabulas masīvs ir labots, lai tas nemainītos, kad formula tiek kopēta pa kolonnas B šūnām.
Kaut kas jāuzmanās
Meklējot diapazonus, izmantojot VLOOKUP, ir svarīgi, lai tabulas masīva pirmā kolonna (šajā scenārijā D kolonna) tiktu sakārtota augošā secībā. Formula balstās uz šo secību, lai ievietotu uzmeklēšanas vērtību pareizajā diapazonā.
Tālāk ir parādīts to rezultātu attēls, ko mēs iegūtu, ja tabulas masīvu sakārtotu pēc atzīmes burta, nevis pēc rezultāta.
Ir svarīgi skaidri saprast, ka secība ir būtiska tikai diapazona meklējumos. Ja funkcijas VLOOKUP beigās ievietojat False, secība nav tik svarīga.
Otrais piemērs: atlaides nodrošināšana, pamatojoties uz to, cik klients tērē
Šajā piemērā mums ir daži pārdošanas dati. Mēs vēlamies nodrošināt atlaidi pārdošanas apjomam, un šīs atlaides procentuālā daļa ir atkarīga no iztērētās summas.
Uzmeklēšanas tabulā (D un E kolonna) ir ietvertas atlaides katrā izdevumu kategorijā.
Zemāk esošo VLOOKUP formulu var izmantot, lai atgrieztu pareizo atlaidi no tabulas.
=VLOOKUP(A2,$D$2:$E$7,2,TRUE)
Šis piemērs ir interesants, jo mēs varam to izmantot formulā, lai atņemtu atlaidi.
Jūs bieži redzēsit, ka Excel lietotāji raksta sarežģītas formulas šāda veida nosacījuma loģikai, taču šī VLOOKUP sniedz īsu veidu, kā to panākt.
Tālāk VLOOKUP tiek pievienots formulai, lai atņemtu atgriezto atlaidi no pārdošanas summas A slejā.
=A2-A2*VLOOKUP(A2,$D$2:$E$7,2,TRUE)
VLOOKUP ir noderīgs ne tikai, meklējot konkrētus ierakstus, piemēram, darbiniekus un produktus. Tas ir daudzpusīgāks, nekā daudzi cilvēki zina, un tam ir piemērs, ja tas atgriežas no dažādām vērtībām. Varat to izmantot arī kā alternatīvu citādi sarežģītām formulām.