Skatieties video - kā izmantot funkciju VLOOKUP ar vairākiem kritērijiem
Funkcija Excel VLOOKUP pamatformā var meklēt vienu uzmeklēšanas vērtību un atdot atbilstošo vērtību no norādītās rindas.
Bet bieži vien ir jāizmanto Excel VLOOKUP ar vairākiem kritērijiem.
Kā lietot VLOOKUP ar vairākiem kritērijiem
Pieņemsim, ka jums ir dati ar studentu vārdu, eksāmena veidu un matemātikas punktu skaitu (kā parādīts zemāk):
Funkcijas VLOOKUP izmantošana, lai iegūtu matemātikas rezultātu katram studentam par attiecīgajiem eksāmenu līmeņiem, varētu būt izaicinājums.
Var apgalvot, ka labāka iespēja būtu datu kopas pārstrukturēšana vai rakurstabulas izmantošana. Ja tas jums noder, nekas tamlīdzīgs. Bet daudzos gadījumos jums ir iestrēdzis jūsu rīcībā esošie dati, un rakurstabula var nebūt piemērota.
Šādos gadījumos šī apmācība ir paredzēta jums.
Tagad ir divi veidi, kā iegūt meklēšanas vērtību, izmantojot VLOOKUP ar vairākiem kritērijiem.
- Palīdzības kolonnas izmantošana.
- Funkcijas IZVĒLE izmantošana.
VLOOKUP ar vairākiem kritērijiem - izmantojot palīgu kolonnu
Es esmu Excel palīgu kolonnu cienītājs.
Es atklāju divas būtiskas priekšrocības, izmantojot palīgkolonnas, salīdzinot ar masīva formulām:
- Tas ļauj viegli saprast, kas notiek darblapā.
- Tas padara to ātrāku salīdzinājumā ar masīva funkcijām (pamanāms lielās datu kopās).
Tagad nepārprotiet mani. Es neesmu pret masīva formulām. Man patīk apbrīnojamās lietas, ko var izdarīt ar masīva formulām. Vienkārši es tos saglabāju īpašiem gadījumiem, kad visas citas iespējas nepalīdz.
Atgriežoties pie konkrētā jautājuma, palīga kolonna ir nepieciešama, lai izveidotu unikālu kvalifikāciju. Šo unikālo kvalifikatoru var izmantot, lai meklētu pareizo vērtību. Piemēram, datos ir trīs Matt, bet ir tikai viena Matt un Unit Test kombinācija vai Matt un Mid-Term.
Tālāk ir norādītas darbības.
- Ievietojiet palīgu kolonnu starp B un C kolonnu.
- Palīga kolonnā izmantojiet šādu formulu: = A2 & ”|” un B2
- Tas katram gadījumam radītu unikālus kvalifikatorus, kā parādīts zemāk.
- Tas katram gadījumam radītu unikālus kvalifikatorus, kā parādīts zemāk.
- Izmantojiet šādu formulu G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Kopēt visām šūnām.
Kā tas darbojas?
Katram vārda gadījumam un eksāmenam mēs izveidojam unikālus kvalifikatorus. Šeit izmantotajā funkcijā VLOOKUP uzmeklēšanas vērtība tika mainīta uz $ F3 & ”|” & G $ 2, lai abi uzmeklēšanas kritēriji tiktu apvienoti un izmantoti kā viena uzmeklēšanas vērtība. Piemēram, G2 funkcijas VLOOKUP uzmeklēšanas vērtība ir matēta | Vienības tests. Tagad šī uzmeklēšanas vērtība tiek izmantota, lai iegūtu rezultātu no C2: D19.
Skaidrojumi:
Ir daži jautājumi, kas, iespējams, jums ienāks prātā, tāpēc es domāju, ka mēģināšu atbildēt šeit:
- Kāpēc es izmantoju | simbols, pievienojoties abiem kritērijiem? - Dažos ārkārtīgi retos (bet iespējamos) apstākļos jums var būt divi kritēriji, kas ir atšķirīgi, bet kopā dod tādu pašu rezultātu. Šeit ir ļoti vienkāršs piemērs (piedodiet man par radošuma trūkumu):
Ņemiet vērā: lai gan A2 un A3 ir atšķirīgi, bet B2 un B3 ir atšķirīgi, kombinācijas galu galā ir vienādas. Bet, ja jūs izmantojat atdalītāju, tad pat kombinācija būtu atšķirīga (D2 un D3).
- Kāpēc es ievietoju palīgu kolonnu starp B un C kolonnu, nevis galējā kreisajā pusē? - Palīga kolonnas ievietošana galējā kreisajā pusē nekaitē. Patiesībā, ja nevēlaties savaldīties ar sākotnējiem datiem, tam vajadzētu būt pareizajam ceļam. Es to izdarīju, jo tas liek man izmantot mazāk šūnu VLOOKUP funkcijā. Tā vietā, lai tabulas masīvā būtu 4 kolonnas, es varētu pārvaldīt tikai ar 2 kolonnām. Bet tas esmu tikai es.
Tagad nav viena izmēra, kas derētu visiem. Daži cilvēki var izvēlēties neizmantot nevienu palīgu kolonnu, kamēr izmanto VLOOKUP ar vairākiem kritērijiem.
Tātad, šeit ir kolonna bez palīga.
Lejupielādējiet parauga failu
VLOOKUP ar vairākiem kritērijiem - izmantojot funkciju CHOOSE
Izmantojot masīva formulas palīgkolonnu vietā, tiek ietaupīts darblapas nekustamais īpašums, un veiktspēja var būt tikpat laba, ja darbgrāmatā to izmanto mazāk reižu.
Ņemot vērā to pašu datu kopu, kas izmantota iepriekš, šeit ir formula, kas sniegs jums rezultātu:
= VLOOKUP ($ E3 & ”|” & F $ 2, CHOOSE ({1,2}, $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19), 2, 0)
Tā kā šī ir masīva formula, izmantojiet to kopā ar Control + Shift + Enter, nevis tikai Enter.
Kā tas darbojas?
Formulā tiek izmantots arī palīga kolonnas jēdziens. Atšķirība ir tāda, ka tā vietā, lai darblapā ievietotu palīga kolonnu, uzskatiet to par virtuālajiem palīga datiem, kas ir formulas sastāvdaļa.
Ļaujiet man parādīt, ko es domāju ar virtuālajiem palīga datiem.
Iepriekš redzamajā attēlā, kad es izvēlos formulas daļu IZVĒLĒTIES un nospiediet F9, tas parāda rezultātu, ko dotu formula IZVĒLĒTIES.
Rezultāts ir {“Matt | Unit Test”, 91; “Bob | Unit Test”, 52;…}
Tas ir masīvs, kurā komats apzīmē nākamo šūnu tajā pašā rindā, bet semikols norāda, ka nākamajā slejā ir šādi dati. Tādējādi šī formula izveido 2 datu kolonnas - vienā kolonnā ir unikāls identifikators, bet vienā - rezultāts.
Tagad, kad izmantojat funkciju VLOOKUP, tā vienkārši meklē vērtību (šī virtuālā 2 kolonnu datu) pirmajā slejā un atgriež atbilstošo rezultātu.
Lejupielādējiet parauga failu
Varat arī izmantot citas formulas, lai meklētu ar vairākiem kritērijiem (piemēram, INDEX/MATCH vai SUMPRODUCT).
Vai ir kāds cits veids, kā jūs zināt, kā to izdarīt? Ja jā, dalieties ar mani komentāru sadaļā.
Jums varētu patikt arī šādas LOOKUP apmācības:
- VLOOKUP vs. INDEKSS/MATCH
- Iegūstiet vairākas uzmeklēšanas vērtības bez atkārtošanās vienā šūnā.
- Kā padarīt VLOOKUP reģistrjutīgu.
- Izmantojiet IFERROR kopā ar VLOOKUP, lai atbrīvotos no #N/A kļūdām.
