Indekss atbilst vairāku kritēriju rindām un kolonnām
Mēs visi katru dienu izmantojam VLOOKUP, lai iegūtu datus, un mēs arī zinām, ka VLOOKUP var iegūt datus no kreisās uz labo pusi, tāpēc uzmeklēšanas vērtībai vienmēr jābūt rezultātu kolonnu kreisajā pusē. Tomēr mums ir vairākas alternatīvas, kuras var izmantot kā alternatīvu funkcijai VLOOKUP programmā Excel. Izmantojot progresīvas tehnoloģijas, mēs varam izmantot šo INDEX + MATCH formulu, lai atbilstu vairākiem rindu un kolonnu kritērijiem. Tātad šis īpašais raksts jūs detalizēti iepazīstinās ar šo tehniku.
Kā izmantot INDEX + MATCH formulu, lai atbilstu vairākiem kritērijiem?
Šeit mēs paskaidrojam, kā izmantot indeksu + atbilstības formulu, lai piemērotu vairākus rindu un kolonnu kritērijus.
1. piemērs - INDEX + MATCH formula
Ne lielākā daļa Excel lietotāju meklēšanas funkciju pārsniedz VLOOKUP, iemeslu varētu būt tik daudz. Jebkurā gadījumā mums būs vienkāršs ievads šai formulai, pirms mēs ejam uz augstāko līmeni.
Piemēram, programmā Excel apskatiet zemāk redzamo datu struktūru.
Mums ir “Sales Rep” nosaukumi un to attiecīgās pārdošanas vērtības. No otras puses, šūnā D2 ir nolaižams saraksts ar “Sale Rep”.
Pamatojoties uz atlasi, ko veicam no nolaižamā saraksta, pārdošanas summai jāparādās šūnā E2.
Problēma ir tāda, ka mēs nevaram piemērot formulu VLOOKUP, jo uzmeklēšanas vērtība “Sales Rep” atrodas rezultātu kolonnas “Sales” labajā pusē, tāpēc šajos gadījumos varam izmantot uzmeklēšanas kombinācijas formulas INDEX + MATCH kombināciju.
INDEX meklē minēto rindas numura vērtību diapazonā A2: A11, un šajā diapazonā mums jānorāda, no kuras rindas mums vajadzīga pārdošanas vērtība. Šīs rindas vērtība ir balstīta uz “Sales Rep” nosaukumu, kas atlasīts Excel nolaižamajā sarakstā, tāpēc funkcija MATCH meklē rindas numuru “Sales Rep” diapazonā B2: B11 un atgriež atbilstošās vērtības rindas numuru .
2. piemērs - vairāki kritēriji formulā INDEX + MATCH
Tagad mums ir tāda datu struktūra kā zemāk.
Mēneša pārdošanas vērtības ir “Pārdevējs”. No šīs tabulas mums ir nepieciešami dinamiski rezultāti, piemēram, šūnā A15, esmu izveidojis nolaižamo sarakstu “Pārdevējs” un šūnā B14 - “Mēnesis” nolaižamo sarakstu.
Pamatojoties uz atlasi, kas veikta šajās divās šūnās, mūsu formulai ir jāiegūst dati no iepriekš minētās tabulas.
Piemēram, ja izvēlos “Rep 8” un “Apr”, tad tajā ir jāparāda “Rep 8” pārdošanas vērtība “Apr.” mēnesim.
Tātad šajos gadījumos mums jāsaskaņo gan rindas, gan kolonnas. Veiciet tālāk norādītās darbības, lai piemērotu formulu gan rindām, gan kolonnām.
1. darbība: šūnā B15 atveriet funkciju INDEX.
2. solis: Funkcijas INDEX pirmais arguments ir “Masīvs”, ti, no kura šūnu diapazona mums ir nepieciešams rezultāts. Tātad, šajā gadījumā mums ir vajadzīgas pārdošanas vērtības, tāpēc izvēlieties šūnu diapazonu no B2 līdz G11.
3. solis: nākamais INDEX funkcijas arguments, no kuras izvēlētā diapazona rindas mums ir nepieciešams rezultāts. Šajā gadījumā mums jānonāk pie rindas “Pārdevējs”, pamatojoties uz atlasi, kas veikta šūnas A15 nolaižamajā šūnā. Tātad, lai dinamiski iegūtu rindas numuru, pamatojoties uz atlases atvērto funkciju MATCH.
4. darbība: MATCH funkcijas MEKLĒŠANAS VĒRTĪBA ir “Sales Rep”, tāpēc par atsauci izvēlieties A15 šūnu.
5. darbība: Uzmeklēšanas masīvs galvenajā tabulā būs “Sales Rep” nosaukumu diapazons. Tāpēc izvēlieties diapazonu no A2 līdz A11.
6. darbība: Funkcijas MATCH atbilstības veids būs precīzs, tāpēc kā argumenta vērtību ievadiet nulli.
7. solis: Nākamais INDEX funkcijas arguments ir “Kolonnas numurs”, ti, no atlasītā šūnu diapazona, no kuras kolonnas mums ir nepieciešams rezultāts. Tas ir atkarīgs no mēneša, kuru mēs atlasām no šūnas B14 nolaižamā saraksta. Tātad, lai automātiski iegūtu kolonnas numuru, atveriet citu funkciju MATCH.
8. darbība: Šoreiz uzmeklēšanas vērtība būs mēneša nosaukums, tāpēc kā atsauci atlasiet šūnu B14.
9. solis: Uzmeklēšanas masīvs būs mēneša šūnu diapazons galvenajā tabulā, ti, no B1 līdz G1.
10. solis: pēdējais arguments ir atbilstības veids; kā kritēriju izvēlieties “Precīza atbilstība”. Aizveriet divas iekavas un nospiediet taustiņu Enter, lai iegūtu rezultātu.
Kā redzam iepriekš, mēs par mēnesi esam izvēlējušies “Rep 6” un “Apr”, un mūsu formula ir atgriezusi “Apr 6” pārdošanas vērtību “Apr” mēnesī.
Piezīme: dzeltenās krāsas šūna jums ir atsauce.
Atceramās lietas
- INDEX + MATCH kombinācija var būt jaudīgāka par formulu VLOOKUP.
- INDEX & MATCH var saskaņot gan rindu, gan kolonnu galvenes un atgriezt rezultātu no vidējās tabulas.
- MATCH var atgriezt abu rindu un kolonnu tabulas galvenes rindas numuru un kolonnas numuru.
