Reģistrjutīgs VLOOKUP - Excel un Google izklājlapas

Lejupielādēt darbgrāmatas piemēru

Lejupielādējiet darbgrāmatas piemēru

Ievērojami reģistrjutīgi VLOOKUP - Excel

Šī apmācība parādīs, kā programmā Excel veikt reģistrjutīgo VLOOKUP, izmantojot divas dažādas metodes.

1. metode - reģistrjutīgs VLOOKUP ar palīga kolonnu

= VLOOKUP (MAKS.

Funkcija VLOOKUP

Funkcija VLOOKUP tiek izmantota, lai meklētu aptuvenu vai precīzu atbilstību diapazona kreisākās kolonnas vērtībai un atgriež atbilstošo vērtību no citas kolonnas. Pēc noklusējuma VLOOKUP darbosies tikai tādām vērtībām, kas nav reģistrjutīgas, piemēram:

Reģistrjutīgs VLOOKUP

Apvienojot VLOOKUP, EXACT, MAX un ROW, mēs varam izveidot reģistrjutīgo VLOOKUP formulu, kas atgriež atbilstošo vērtību mūsu reģistrjutīgajam VLOOKUP. Apskatīsim piemēru.

Mums ir vienumu saraksts un to cenas (ievērojiet, ka vienuma ID ir reģistrjutīgs un unikāls):

Pieņemot, ka mums tiek prasīts noteikt preces cenu, izmantojot tās preces ID, šādi:

Lai to paveiktu, mums ir jāizveido palīga kolonna, izmantojot ROW:

= ROW () noklikšķiniet un velciet (vai veiciet dubultklikšķi), lai iepriekš aizpildītu visas diapazona rindas

Pēc tam apvienojiet VLOOKUP, MAX, EXACT un ROW šādā formulā:

= VLOOKUP (MAX (EXACT (,)*(ROW ())), ,, 0)

= VLOOKUP (MAKS.

Kā darbojas formula?

1. Funkcija EXACT pārbauda vienuma ID E2 (uzmeklēšanas vērtība) salīdzinājumā ar vērtībām B2: B7 (uzmeklēšanas diapazons) un atgriež masīvu TRUE, ja masīvā {FLASE, FLASE, FLASE, FLASE, ir precīza atbilstība vai FLASE FLASE, TRUE}.
2. Pēc tam šo masīvu reizina ar ROW masīvu {2, 3, 4, 5, 6, 7} (ņemiet vērā, ka tas atbilst mūsu palīga kolonnai).
3. Funkcija MAX atgriež maksimālo vērtību no iegūtā masīva {0,0,0,0,0,7}, kas mūsu piemērā ir 7.
4. Tad mēs izmantojam rezultātu kā mūsu meklēšanas vērtību VLOOKUP un izvēlamies mūsu palīga kolonnu kā uzmeklēšanas diapazonu. Mūsu piemērā formula atgriež atbilstošo vērtību 16,00 USD.

2. metode - reģistrjutīgo VLOOKUP ar “virtuālo” palīga kolonnu

= VLOOKUP (MAX (EXACT (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7)))), CHOOSE ({1,2}, ROW ($ B $ 2: $ B $ 7)) , 2 ASV dolāri: 7 ASV dolāri), 2,0)

Šī metode izmanto to pašu loģiku kā pirmā metode, taču novērš nepieciešamību izveidot palīga kolonnu, bet izmanto CHOOSE un ROW, lai izveidotu “virtuālu” palīgu kolonnu šādi:

= VLOOKUP (MAX (EXACT (,)*(ROW ())), CHOOSE ({1,2}, ROW (),),, 0)

= VLOOKUP (MAX (PRECĪZS (D2, $ B $ 2: $ B $ 7)*(ROW ($ B $ 2: $ B $ 7)))), CHOOSE ({1,2}, ROW ($ B $ 2: $ B $ 7)) , 2 ASV dolāri: 7 ASV dolāri), 2,0)

Kā darbojas formula?

1. Formulas pirmā daļa darbojas tāpat kā pirmā metode.
2. Apvienojot CHOOSE un ROW, tiek atgriezts masīvs ar divām kolonnām - vienu rindas numuram un otru cenu. Masīvs ir atdalīts ar semikolu, lai apzīmētu nākamo rindu, un komatu nākamajai kolonnai šādi: {2,45; 3,83; 4,23; 5,74; 6,4; 7,16}.
3. Pēc tam mēs varam izmantot VLOOKUP formulas pirmās daļas rezultātu, lai atrastu atbilstošo vērtību no mūsu CHOOSE un ROW masīva.

Reģistrjutīgs VLOOKUP Google izklājlapās

Visi iepriekš minētie piemēri Google izklājlapās darbojas tieši tāpat kā programmā Excel.