INDEX és MATCH több oszlopban
Ha több oszlopban egyeztetve szeretne megkeresni egy értéket, a INDEX és a MÉRKŐZÉS funkciókat, amelyek magukban foglalják MMULT, TRANSZPONÁLÁS és a oszlop szívességet tesz neked.
Hogyan kereshet meg egy értéket több oszlop egyeztetésével?
A kitöltéséhez minden tanuló megfelelő osztálya ahogy a fenti táblázat mutatja, ahol a az információk több oszlopban vannak felsorolva, először az MMULT, TRANSPOSE és COLUMN függvények trükkjét használhatja mátrixtömb létrehozásához. Ezután a MATCH függvény megadja a keresési érték pozícióját, amely az INDEX-be kerül, hogy lekérje a tömbben keresett értéket.
Általános szintaxis
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Megjegyzés: Ez egy tömbképlet, amellyel be kell írnia Ctrl + műszak + belép.
- return_range: Az a tartomány, ahonnan a képlet visszaadja az osztályinformációkat. Itt az osztálytartományra vonatkozik.
- lookup_value: Az az érték, amelyet a képlet használt a megfelelő osztályinformáció megtalálásához. Itt az adott névre utal.
- lookup_array: A sejtek tartománya, ahol a keresési_érték szerepel a listán; A tartomány az értékekkel összehasonlítani keresési_érték. Itt a névtartományra utal.
- 0 egyezési típus: Arra kényszeríti a MATCH -t, hogy megtalálja az első értéket, amely pontosan megegyezik a keresési_érték.
Megtalálni a osztály Jimmy, másolja vagy írja be az alábbi képletet a H5 cellába, majd nyomja meg a gombot Ctrl + műszak + belép az eredmény eléréséhez:
= INDEX ($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--(5 USD: 7 USD=G5),TRANSPOSE(COLUMN(5 USD: 7 USD)^0)),0)))
√ Megjegyzés: A fenti dollárjelek ($) abszolút hivatkozásokat jeleznek, ami azt jelenti, hogy a képlet neve és osztálytartományai nem változnak, amikor áthelyezi vagy más cellákba másolja a képletet. Vegye figyelembe, hogy ne adjon dollárjeleket a keresési értéket jelentő cellahivatkozáshoz, mivel azt szeretné, hogy az relatív legyen, amikor más cellákba másolja. A képlet megadása után húzza le a kitöltő fogantyút, hogy a képletet az alábbi cellákra alkalmazza.
A képlet magyarázata
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- ---($C$5:$E$7=G5): Ez a szegmens a tartomány minden értékét ellenőrzi 5 USD: 7 USD ha megegyeznek a G5 cellában lévő értékkel, és egy IGAZ és HAMIS tömböt generál, így:
{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
A kettős negatív ezután az IGAZ és HAMIS értékeket 1-re és 0-ra konvertálja, így egy ehhez hasonló tömböt eredményez:
{1,0,0; 0,0,0; 0,0,0}. - OSZLOP ($C$5:$E$7): A COLUMN függvény a tartomány oszlopszámait adja vissza 5 USD: 7 USD egy ilyen tömbben: 3,4,5 {}.
- TRANSPOSE(OSZLOP ($C$5:$E$7)^0) = TRANSPOSE(3,4,5 {}^0): Miután a hatványt 0-ra emeltük, a(z) {3,4,5} tömb összes száma 1-re lesz konvertálva: {1,1,1}. A TRANSPOSE függvény ezután az oszloptömböt sortömbbé alakítja, így: {1; 1; 1}.
- MMULT(---($C$5:$E$7=G5),TRANSPOSE(OSZLOP ($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Az MMULT függvény a két tömb mátrixszorzatát adja vissza, így: {1; 0; 0}.
- MATCH (1,MMULT(---($C$5:$E$7=G5),TRANSPOSE(OSZLOP ($C$5:$E$7)^0)), 0) = MATCH (1,{1; 0; 0}, 0): A match_type 0 kényszeríti a MATCH függvényt, hogy visszaadja az első egyezés pozícióját 1 a tömbben {1; 0; 0}, Amely 1.
- INDEX($ B $ 5: $ B $ 7,(MATCH(1,MMULT(---($C$5:$E$7=G5),TRANSPOSE(OSZLOP ($C$5:$E$7)^0)), 0))) = INDEX ($ B $ 5: $ B $ 7,1): Az INDEX függvény a 1st érték az osztálytartományban $ B $ 5: $ B $ 7, Amely A.
Ha több oszlopban egyeztetve szeretne egyszerűen megkeresni egy értéket, használhatja professzionális Excel-bővítményünket is Kutools az Excelhez. A küldetés teljesítéséhez lásd itt az utasítást.
Kapcsolódó funkciók
Az Excel INDEX függvény a tartomány vagy tömb adott pozíciója alapján adja vissza a megjelenített értéket.
Az Excel MATCH függvény egy adott értéket keres egy cellatartományban, és visszaadja az érték relatív pozícióját.
Az Excel MMULT függvénye két tömb mátrixszorzatát adja vissza. A tömb eredményének ugyanannyi sora van, mint a tömb1-ben, és ugyanannyi oszlopa van, mint a tömb2-nek.
Az Excel TRANSPOSE funkciója elforgatja egy tartomány vagy tömb tájolását. Például elforgathat egy vízszintesen sorokba rendezett táblázatot függőlegesen oszlopokba, vagy fordítva.
Az OSZLOP függvény annak az oszlopnak a számát adja vissza, amelyben a képlet megjelenik, vagy az adott hivatkozás oszlopszámát adja vissza. Például az =OSZLOP(BD) képlet 56-ot ad vissza.
Kapcsolódó képletek
Többkritériumú keresés az INDEX és a MATCH használatával
Amikor egy nagy adatbázissal foglalkozik egy Excel táblázatban, amely több oszlopot és feliratot tartalmaz, mindig bonyolult találni valamit, amely több kritériumnak megfelel. Ebben az esetben tömbképletet használhat az INDEX és MATCH függvényekkel.
Kétirányú keresés INDEX és MATCH segítségével
Ha az Excelben sorokban és oszlopokban keresünk valamit, vagy azt mondjuk, hogy az adott sor és oszlop metszéspontjában keresünk egy értéket, használhatjuk az INDEX és a MATCH függvényeket.
Keresse meg a legközelebbi egyezési értéket több kritériummal
Bizonyos esetekben előfordulhat, hogy több feltétel alapján kell keresnie a legközelebbi vagy közelítő egyezési értéket. Az INDEX, MATCH és IF funkciók kombinációjával gyorsan elvégezheti az Excelben.
A legjobb irodai termelékenységi eszközök
Kutools for Excel - segít kitűnni a tömegből
A Kutools for Excel több mint 300 funkcióval büszkélkedhet, Győződjön meg arról, hogy amire szüksége van, csak egy kattintásnyira van...
Office lap – A lapos olvasás és szerkesztés engedélyezése a Microsoft Office programban (beleértve az Excelt is)
- Egy másodperc a tucatnyi nyitott dokumentum közötti váltáshoz!
- Csökkentse az egérkattintások százait az Ön számára minden nap, búcsút mondjon az egér kezének.
- 50% -kal növeli a termelékenységet több dokumentum megtekintésekor és szerkesztésekor.
- Hatékony lapokat hoz az Office-ba (beleértve az Excelt is), akárcsak a Chrome, az Edge és a Firefox.