U ovom ćemo članku naučiti kako tražiti vrijednosti u 2d tablici pomoću funkcije INDEX-MATCH-MATCH u Excelu.
Scenarij:
Pretpostavimo da morate napraviti više pregleda iz tablice koja ima stotine stupaca. U takvim će slučajevima korištenje različitih formula za svako pretraživanje potrajati previše vremena. Kako bi bilo da kreirate formulu za dinamičko pretraživanje koju možete potražiti u predviđenom zaglavlju. Da, možemo to učiniti. Ova se formula naziva INDEX MATCH MATCH formula, ili recimo 2d formula za pretraživanje.
Kako riješiti problem?
Da bismo formulu prvo razumjeli, moramo se malo osvrnuti na sljedeće funkcije
- INDEX funkcija
- MATCH funkcija
Funkcija INDEX vraća vrijednost u danom indeksu u nizu.
MATCH funkcija vraća indeks prvog pojavljivanja vrijednosti u nizu (niz s jednom dimenzijom).
Sada ćemo napraviti formulu koristeći gornje funkcije. Funkcija podudaranja vratit će indeks tražene vrijednosti1 u polje zaglavlja retka. Druga funkcija MATCH vratit će indeks tražene vrijednosti2 u polje zaglavlja stupca. Brojevi indeksa sada će se unositi u funkciju INDEX kako bi se dobile vrijednosti ispod tražene vrijednosti iz podataka 2D tablice.
Opća formula:
= INDEX (podaci, MATCH (tražilica_value1, zaglavlja_reda, 0, MATCH (lookup_value2, zaglavlja_stupaka, 0)))
Podaci: niz vrijednosti unutar tablice bez zaglavlja
lookup_value1 : vrijednost za traženje u zaglavlju retka.
zaglavlja_reda : Niz indeksa redaka za traženje.
lookup_value1 : vrijednost za pretraživanje u zaglavlju stupca.
stupci_zaglavlja : stupac Indeksni niz za pretraživanje.
Primjer:
Gore navedene izjave mogu biti komplicirane za razumijevanje. Pa shvatimo to koristeći formulu u primjeru
Ovdje imamo popis bodova koje su studenti postigli svojim popisom predmeta. Moramo pronaći rezultat za određenog učenika (Gary) i predmet (društvene studije) kako je prikazano na donjoj snimci.
Vrijednost učenika1 mora odgovarati nizu Row_header, a vrijednost Subject2 mora odgovarati nizu Column_header.
Upotrijebite formulu u ćeliji J6:
= INDEX (tablica, MATCH (J5, redak, 0, MATCH (J4, stupac, 0)))
Obrazloženje:
- Funkcija MATCH podudara učenikovu vrijednost u ćeliji J4 s nizom zaglavlja retka i vraća njegovu poziciju 3 kao broj.
- Funkcija MATCH podudara vrijednost Subject u ćeliji J5 s nizom zaglavlja stupca i vraća njegovu poziciju 4 kao broj.
- Funkcija INDEX uzima broj indeksa retka i stupca i traži podatke u tablici i vraća odgovarajuću vrijednost.
- Argument tipa MATCH fiksiran je na 0. Budući da će formula izvući točno podudaranje.
Ovdje su vrijednosti formule date kao reference ćelija, a zaglavlje redaka, tablice i zaglavlja stupaca imenovani rasponi.
Kao što možete vidjeti na gornjoj snimci, dobili smo rezultat koji je student dobio Gary u Predmetu Društvene studije kao 36.
To dokazuje da formula radi dobro, a za nedoumice pogledajte donje napomene radi razumijevanja.
Sada ćemo kao brojeve koristiti približno podudaranje s zaglavljima redaka i stupcima. Približno podudaranje uzima samo brojčane vrijednosti jer se ne može primijeniti na tekstualne vrijednosti
Ovdje imamo cijenu vrijednosti prema visini i širini proizvoda. Moramo pronaći cijenu za određenu visinu (34) i širinu (21) kako je prikazano na donjoj snimci.
Vrijednost Height1 mora odgovarati nizu Row_header, a vrijednost Width2 mora se podudarati s nizom Column_header.
Upotrijebite formulu u ćeliji K6:
= INDEKS (podaci, MATCH (K4, Visina, 1, MATCH (K5, Širina, 1)))
Obrazloženje:
- Funkcija MATCH podudara vrijednost Height u ćeliji K4 s nizom zaglavlja retka i vraća svoj položaj 3 kao broj.
- Funkcija MATCH podudara vrijednost Width u ćeliji K5 s nizom zaglavlja stupca i vraća svoj položaj 2 kao broj.
- Funkcija INDEX uzima broj indeksa retka i stupca i traži podatke u tablici i vraća odgovarajuću vrijednost.
- Argument tipa MATCH fiksiran je na 1. Budući da će formula izvući približno podudaranje.
Ovdje su vrijednosti formule date kao reference ćelija i zaglavlje redaka, podaci i zaglavlje stupca dani su kao imenovani rasponi kako je spomenuto u gornjoj snimci.
Kao što možete vidjeti na gornjoj snimci, imamo cijenu dobivenu po visini (34) & Širina (21) kao 53.10. To dokazuje da formula radi dobro i za nedoumice pogledajte donje napomene za više razumijevanja.
Bilješke:
- Funkcija vraća pogrešku #NA ako je argument polja za pretraživanje funkciji MATCH 2D niz koji je polje zaglavlja podataka …
- Funkcija odgovara točnoj vrijednosti jer je argument vrste podudaranja funkciji MATCH 0.
- Vrijednosti traženja mogu se dati kao referenca ćelije ili izravno pomoću znaka navoda (") u formuli kao argumenata.
Nadam se da ste razumjeli kako koristiti pretraživanje u 2 -D tablici pomoću funkcije INDEKS & MATCH u Excelu. Ovdje istražite više članaka o vrijednosti pretraživanja programa Excel. Slobodno navedite svoje upite ispod u okvir za komentare. Mi ćemo vam svakako pomoći.
Upotrijebite INDEX i MATCH za traženje vrijednosti : INDEX & MATCH funkcija za traženje vrijednosti prema potrebi.
Raspon SUM s INDEX -om u Excelu : Upotrijebite INDEX funkciju za traženje SUM -a vrijednosti prema potrebi.
Kako koristiti funkciju ZBOR u Excelu : Pronađite SUM brojeva pomoću funkcije SUM objašnjene na primjeru.
Kako koristiti funkciju INDEKS u Excelu : Pronađite INDEX niza pomoću funkcije INDEX objašnjene na primjeru.
Kako koristiti funkciju MATCH u Excelu : Pronađite MATCH u nizu koristeći vrijednost INDEX unutar funkcije MATCH objašnjeno na primjeru.
Kako koristiti funkciju LOOKUP u Excelu : Pronađite vrijednost pretraživanja u nizu pomoću funkcije LOOKUP objašnjene na primjeru.
Kako koristiti funkciju VLOOKUP u Excelu : Pronađite vrijednost pregledavanja u nizu pomoću funkcije VLOOKUP objašnjene na primjeru.
Kako koristiti funkciju HLOOKUP u Excelu : Pronađite vrijednost pregledavanja u nizu pomoću funkcije HLOOKUP objašnjene na primjeru.
Popularni članci
50 Excel prečac za povećanje vaše produktivnosti
Uredite padajući popis
Apsolutna referenca u Excelu
Ako s uvjetnim oblikovanjem
Ako sa zamjenskim znakovima
Pogled prema datumu
Pridružite se imenu i prezimenu u excelu
