Indeks podudara više redaka i stupaca s više kriterija
Svi koristimo VLOOKUP iz dana u dan za dohvaćanje podataka, a također smo svjesni činjenice da VLOOKUP može dohvatiti podatke slijeva udesno, pa bi vrijednost pretraživanja uvijek trebala biti s lijeve strane stupaca rezultata. Međutim, imamo nekoliko alternativa koje se mogu koristiti kao alternativa funkciji VLOOKUP u Excelu. Naprednom tehnologijom možemo koristiti ovu INDEX + MATCH formulu kako bismo podudarali više kriterija za retke i stupce. Stoga će vas ovaj posebni članak detaljno provesti o ovoj tehnici.
Kako koristiti INDEX + MATCH Formulu za podudaranje više kriterija?
Ovdje ćemo objasniti kako se pomoću primjera formule indeks + podudaranje može podudarati s više kriterija za retke i stupce s primjerima.
Primjer # 1 - INDEKS + UTAKMICA Formula
Nije većina funkcija pretraživanja korisnika excel izvan VLOOKUP-a, razloga može biti toliko. U svakom slučaju, napravimo jednostavan uvod u ovu formulu prije nego što prijeđemo na naprednu razinu.
Na primjer, pogledajte donju strukturu podataka u Excelu.
Imamo nazive "Sales Rep" i njihove prodajne vrijednosti. S druge strane, u padajućem popisu "Rep. Prodaje" u ćeliji D2.
Na temelju odabira koji napravimo s padajućeg popisa iznos prodaje mora se pojaviti u ćeliji E2.
Problem je što ne možemo primijeniti formulu VLOOKUP jer je vrijednost pretraživanja "Sales Rep" desno od stupca rezultata "Prodaja", pa u tim slučajevima možemo koristiti formulu vrijednosti kombinacije pretraživanja INDEX + MATCH.
INDEX traži spomenutu vrijednost broja retka u rasponu A2: A11, a u tom rasponu moramo navesti iz kojeg retka trebamo doći iz prodajne vrijednosti. Ova vrijednost retka temelji se na imenu "Sales Rep" odabranom na padajućem popisu u Excelu, pa funkcija MATCH traži broj retka "Sales Rep" u rasponu B2: B11 i vraća broj retka odgovarajuće vrijednosti .
Primjer # 2 - Višestruki kriteriji u formuli INDEX + MATCH
Sada imamo strukturu podataka poput donje.
Imamo mjesečne prodajne vrijednosti od “Sales Rep.” Iz ove tablice trebamo dinamičke rezultate kao u ćeliji A15, stvorio sam padajući popis "Prodajni zastupnik", a u ćeliji B14 stvorio sam padajući popis "Mjesec".
Na temelju odabira izvršenog u ove dvije ćelije, naša formula mora dohvatiti podatke iz gornje tablice.
Na primjer, ako odaberem „Rep 8“ i „Apr“, tada mora prikazati prodajnu vrijednost „Rep 8“ za mjesec „Travanj“
Dakle, u tim slučajevima moramo podudarati i retke i stupce. Slijedite korake u nastavku da biste primijenili formulu kako bi se podudarali s redovima i stupcima.
Korak 1: Otvorite funkciju INDEX u ćeliji B15.
Korak 2: Prvi argument funkcije INDEX je "Array", tj. Iz kojeg raspona ćelija trebamo rezultat. Dakle, u ovom su nam slučaju potrebne vrijednosti prodaje, pa odaberite raspon ćelija od B2 do G11.
Korak 3: Sljedeći argument funkcije INDEX iz kojeg reda odabranog raspona trebamo rezultat. U ovom slučaju, moramo doći do broja retka "Sales Rep" na temelju odabira napravljenog u padajućoj ćeliji A15. Dakle, za dinamičko dohvaćanje broja retka na temelju odabrane funkcije MATCH.
Korak 4: VRIJEDNOST TRAŽENJA funkcije MATCH je "Sales Rep", pa za referencu odaberite ćeliju A15.
Korak 5: Polje pretraživanja bit će raspon imena "Sales Rep" u glavnoj tablici. Dakle, odaberite raspon od A2 do A11.
Korak 6: Tip podudaranja funkcije MATCH bit će točan pa unesite nulu kao vrijednost argumenta.
Korak 7: Sljedeći argument funkcije INDEX je "Broj stupca", tj. Iz odabranog raspona ćelija iz kojeg stupca trebamo rezultat. To ovisi o mjesecu koji odaberemo s padajućeg popisa ćelije B14. Dakle, da biste automatski dobili broj stupca, otvorite drugu funkciju MATCH.
Korak 8: Ova vrijednost vremenskog traženja bit će naziv mjeseca, pa za referencu odaberite ćeliju B14.
Korak 9: Polje za pretraživanje bit će mjesečni raspon ćelija u glavnoj tablici, tj. Od B1 do G1.
Korak 10: Posljednji argument je vrsta podudaranja; kao kriterije odaberite "Točno podudaranje". Zatvorite dvije zagrade i pritisnite tipku Enter da biste dobili rezultat.
Kao što gore možemo vidjeti, odabrali smo „Rep 6“ i „Apr“ za mjesec, a naša formula vratila je vrijednost prodaje za mjesec „Apr“ za „Rep 6“.
Napomena: Ćelija žute boje je referenca za vas.
Stvari koje treba zapamtiti
- Kombinacija INDEX + MATCH može biti moćnija od formule VLOOKUP.
- INDEX & MATCH mogu se podudarati i sa zaglavljima redaka i stupaca i vraćati rezultat iz srednje tablice.
- MATCH može vratiti broj retka i broj stupca zaglavlja tablice oba retka i stupca.
