Az első egyező érték lekérése a cellában egy lista alapján
Tegyük fel, hogy van egy kulcsszólistája, és az első kulcsszó lekéréséhez, amely egy adott cellában jelent meg, miközben a cella több más értéket is tartalmaz, egy INDEX és a MÉRKŐZÉS képlet segítségével ÖSSZESÍTETT és a KERESÉS funkciókat.
Hogyan lehet lekérni az első egyező értéket a cellában a listához képest?
A az első egyezésű kulcsszó egy cellában a Kulcsszavak listában Ahogy a fenti táblázat is mutatja, ez azt jelenti, hogy a pontos egyezés helyett a tartalmazott egyezést kell végrehajtanunk. Ehhez a KERESÉS funkcióval a cellában numerikus számokkal megjelenő kulcsszavak pozícióját betáplálhatja az ÖSSZESÍTÉS funkcióba. Ekkor az AGGREGATE a legkisebb számot kapja annak beállításával a függvény_száma 15-re, a ref2 argumentum pedig 1-re. Ezután a MATCH segítségével keresse meg az első legkisebb értéket, és adja meg a pozíciószámot az INDEX-nek, hogy lekérje az értéket az adott pozícióban.
Általános szintaxis
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
√ Megjegyzés: Ez egy tömbképlet, amellyel be kell írnia Ctrl + műszak + belép.
- kulcsszó_rng: A kulcsszavakkal ellátott cellák tartománya.
- lookup_cell: A keresendő cella, ha tartalmazza a kulcsszavakat.
Hogy visszaszerezze a Az első egyező kulcsszó a B5 cellában jelent meg a Kulcsszavak oszlopban, másolja vagy írja be az alábbi képletet a C5 cellába, és nyomja meg a gombot Ctrl + műszak + belép az eredmény eléréséhez:
= INDEX ($ E $ 5: $ 7 USD,MATCH(AGGREGATE(15,6;XNUMX;SEARCH($ E $ 5: $ 7 USD,B5),1),KERESÉS($ E $ 5: $ 7 USD,B5), 0))
√ Megjegyzés: A fenti dollárjelek ($) abszolút hivatkozásokat jeleznek, ami azt jelenti, hogy a kulcsszó_rng a képlet nem változik, amikor áthelyezi vagy más cellákba másolja a képletet. Azonban nincsenek dollárjelek hozzáadva a lookup_cell mivel azt szeretné, hogy dinamikus legyen. A képlet beírá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($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- KERESÉS ($E$5:$E$7,B5): A SEARCH függvény minden kulcsszó pozícióját adja vissza a tartományból $ E $ 5: $ 7 USD ha numerikus számokban található, és a #ÉRTÉK! hiba, ha nem található. Az eredmény egy ilyen tömbben lenne: {15;11;#ÉRTÉK!}.
- ÖSSZESÍTÉS(15,6;XNUMX,KERESÉS ($E$5:$E$7,B5), 1) = ÖSSZESÍTÉS(15,6;XNUMX,{15;11;#ÉRTÉK!}, 1): Az AGGREGATE függvény a függvény_száma 15 és a 6-os lehetőség szerint a tömb legkisebb értékét adja vissza ref2 argumentum 1, miközben figyelmen kívül hagyja a hibaértékeket. Tehát a részlet visszatérne 11.
- MÉRKŐZÉS(ÖSSZESÍTÉS(15,6;XNUMX,KERESÉS ($E$5:$E$7,B5), 1),KERESÉS ($E$5:$E$7,B5), 0) = MÉRKŐZÉS(11,{15;11;#ÉRTÉK!}, 0): A match_type 0 arra kényszeríti a MATCH függvényt, hogy pontos egyezést hajtson végre, és visszaadja a pozícióját 11 a tömbben {15;11;#ÉRTÉK!}. Tehát a funkció visszatér 2.
- INDEX($E$5:$E$7,MÉRKŐZÉS(ÖSSZESÍTÉS(15,6;XNUMX,KERESÉS ($E$5:$E$7,B5), 1),KERESÉS ($E$5:$E$7,B5), 0)) = INDEX($E$5:$E$7,2): Az INDEX függvény ezután a 2nd érték a tartományban $ E $ 5: $ 7 USD, Amely bbb.
Megjegyzés
- Ha egy cellában nincsenek kulcsszavak, a # EGYEN! hiba kerül visszaadásra.
- A képlet nem különbözteti meg a kis- és nagybetűket. A kis- és nagybetűk közötti egyezés végrehajtásához egyszerűen helyettesítheti a KERESÉS funkciót a következőre: FIND.
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 alkalmazásban a KERESÉS funkció segít megtalálni egy adott karakter vagy alszöveg helyzetét az adott szöveges karaktersorozatból, az alábbi képernyőkép szerint. Ez az oktatóanyag bemutatja, hogyan kell használni ezt a KERESÉS funkciót az Excelben.
Az Excel AGGREGATE függvénye számítások összesítését adja vissza, például SUM, COUNT, SMALLL és így tovább, a hibák és a rejtett sorok figyelmen kívül hagyásának lehetőségével.
Kapcsolódó képletek
A lista első értékének lekérése egy cellából
A tartományban szereplő első kulcsszó lekéréséhez egy adott cellából, miközben a cella több érték közül egyet tartalmaz, egy meglehetősen összetett tömbképletet kell használnia az INDEX, MATCH, ISNUMBER és SEARCH függvényekkel.
Pontos egyezés az INDEX és a MATCH kifejezésekkel
Ha meg kell találnia az Excelben felsorolt információkat egy adott termékről, filmről vagy személyről stb., Akkor jól használja ki az INDEX és a MATCH funkciók kombinációját.
Ellenőrizze, hogy egy cella tartalmaz-e meghatározott szöveget
Az oktatóanyag néhány képletet tartalmaz annak ellenőrzésére, hogy egy cella tartalmaz-e egy adott szöveget, és az IGAZ és HAMIS értéket adja vissza, az alábbi képernyőképen látható módon, és elmagyarázza az érveket és a képletek működését.
Ellenőrizze, hogy a cella tartalmaz-e sok mindent
Ha feltételezzük, hogy az Excelben van egy értéklista az E oszlopban, ellenőrizni szeretné, hogy a B oszlop cellái tartalmazzák-e az E oszlop összes értékét, és az IGAZ vagy HAMIS értéket adja vissza az alábbi képernyőképen. Itt, ebben az oktatóanyagban, képletet nyújt a feladat megoldására.
Ellenőrizze, hogy a cella tartalmazza-e a sok dolog egyikét
Ez az oktatóanyag képletet biztosít annak ellenőrzésére, hogy egy cella tartalmazza-e az Excel több értékének egyikét, és elmagyarázza a képlet argumentumait és a képlet működését.
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.
