Rodyklė atitinka kelias kriterijų eilutes ir stulpelius
Mes visi kasdien naudojame VLOOKUP, kad gautume duomenis, taip pat žinome, kad VLOOKUP gali gauti duomenis iš kairės į dešinę, todėl paieškos vertė visada turėtų būti kairėje rezultatų stulpelių pusėje. Tačiau mes turime keletą alternatyvų, kurios gali būti naudojamos kaip „VLOOKUP“ funkcijos „Excel“ alternatyva. Naudodami pažangias technologijas, galime naudoti šią INDEX + MATCH formulę, kad atitiktų kelis eilučių ir stulpelių kriterijus. Taigi šis specialus straipsnis išsamiai apžvelgs šią techniką.
Kaip naudoti „INDEX + MATCH“ formulę, kad atitiktų kelis kriterijus?
Čia paaiškiname, kaip naudoti indekso + atitikties formulę, kad atitiktų kelis eilučių ir stulpelių kriterijus, pateikdami pavyzdžius.
1 pavyzdys - „INDEX + MATCH“ formulė
Ne dauguma „Excel“ naudotojų paieškos funkcijų viršija VLOOKUP, priežasčių gali būti tiek daug. Bet kokiu atveju, prieš eidami į aukštesnį lygį, turėkime paprastą šios formulės įvadą.
Pavyzdžiui, pažiūrėkite į žemiau esančią duomenų struktūrą „Excel“.
Mes turime „Sales Rep“ pavadinimus ir jų atitinkamas pardavimo vertes. Kita vertus, D2 langelyje turime išskleidžiamąjį sąrašą „Pardavimo atstovas“.
Remiantis pasirinkimu, kurį atliekame iš išskleidžiamojo sąrašo, pardavimo suma turi būti rodoma langelyje E2.
Problema ta, kad negalime pritaikyti VLOOKUP formulės, nes paieškos reikšmė „Pardavimų atstovas“ yra rezultatų stulpelio „Pardavimai“ dešinėje, todėl šiais atvejais galime naudoti kombinuotą peržiūros vertės formulę INDEX + MATCH.
INDEX ieško minėtos eilutės numerio vertės diapazone A2: A11, ir šiame diapazone turime nurodyti, iš kurios eilutės mums reikia pardavimo vertės. Šios eilutės vertė pagrįsta „Sales Rep“ pavadinimu, pasirinktu išskleidžiamajame sąraše „Excel“, todėl funkcija MATCH ieško eilutės „Sales Rep“ numerio diapazone B2: B11 ir grąžina suderintos vertės eilutės numerį. .
2 pavyzdys - keli kriterijai „INDEX + MATCH“ formulėje
Dabar turime tokią duomenų struktūrą kaip žemiau.
Mes turime mėnesio „Sales Rep“ pardavimo vertes. Iš šios lentelės mums reikia dinamiškų rezultatų, pavyzdžiui, langelyje A15, aš sukūriau išskleidžiamąjį sąrašą „Pardavimų atstovas“, o B14 langelyje - išskleidžiamąjį sąrašą „Mėnuo“.
Remiantis šiose dviejose ląstelėse atliktu pasirinkimu, mūsų formulė turi gauti duomenis iš pirmiau pateiktos lentelės.
Pvz., Jei pasirenku „Rep 8“ ir „Apr“, tada jame turi būti nurodyta „Rep 8“ pardavimo vertė „Apr“ mėnesiui.
Taigi, šiais atvejais turime sutapti ir eilutes, ir stulpelius. Atlikite toliau nurodytus veiksmus, kad pritaikytumėte formulę, kad ji atitiktų eiles ir stulpelius.
1 žingsnis: langelyje B15 atidarykite INDEX funkciją.
2 žingsnis: Pirmasis funkcijos INDEX argumentas yra „Masyvas“, ty iš kurio langelių diapazono mums reikia rezultato. Taigi, šiuo atveju mums reikalingos pardavimo vertės, todėl pasirinkite langelių diapazoną nuo B2 iki G11.
3 žingsnis: Kitas funkcijos INDEX argumentas, iš kurios pasirinkto diapazono eilutės mums reikia rezultato. Tokiu atveju turime pasiekti eilutę „Pardavimų atstovas“, atsižvelgdami į langelio A15 išskleidžiamajame langelyje atliktą pasirinkimą. Taigi, norint dinamiškai gauti eilutės numerį, atsižvelgiant į pasirinkimą, atidarykite MATCH funkciją.
4 žingsnis: MATCH funkcijos PAIEŠKOS VERTĖ yra „Pardavimų atstovas“, todėl kaip nuorodą pasirinkite langelį A15.
5 žingsnis: Paieškos masyvas bus „Pardavimų atstovo“ pavadinimų diapazonas pagrindinėje lentelėje. Taigi pasirinkite diapazoną nuo A2 iki A11.
6 žingsnis: Funkcijos MATCH atitikties tipas bus tikslus, todėl įveskite nulį kaip argumento vertę.
7 žingsnis: Kitas funkcijos INDEX argumentas yra „Stulpelio numeris“, ty iš pasirinkto langelių diapazono, iš kurio stulpelio mums reikia rezultato. Tai priklauso nuo mėnesio, kurį pasirenkame iš išskleidžiamojo langelio B14 sąrašo. Taigi, jei norite automatiškai gauti stulpelio numerį, atidarykite kitą funkciją MATCH.
8 žingsnis: Ši laiko paieškos vertė bus mėnesio pavadinimas, todėl kaip nuorodą pasirinkite langelį B14.
9 žingsnis: Paieškos masyvas bus pagrindinės lentelės langelių mėnesio intervalas, ty nuo B1 iki G1.
10 žingsnis: paskutinis argumentas yra atitikties tipas; kaip kriterijus pasirinkite „Tiksli atitiktis“. Norėdami gauti rezultatą, uždarykite du skliaustus ir paspauskite klavišą Enter.
Kaip matome aukščiau, mes pasirinkome „Rep 6“ ir „Apr“ kaip mėnesį, o mūsų formulė grąžino „Apr 6“ mėnesio pardavimo vertę.
Pastaba: geltonos spalvos langelis yra jūsų nuoroda.
Ką reikia atsiminti
- „INDEX + MATCH“ derinys gali būti galingesnis nei „VLOOKUP“ formulė.
- „INDEX & MATCH“ gali atitikti tiek eilučių, tiek stulpelių antraštes ir grąžinti rezultatą iš vidurinės lentelės.
- „MATCH“ gali grąžinti abiejų eilučių ir stulpelių lentelių antraščių eilučių ir stulpelių numerius.
