Általános képlet
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
Összegzés
Az összes találat részleges egyezés alapján történő kibontásához használhatja az INDEX és az AGGREGATE függvényeken alapuló tömbképletet, az ISNUMBER és a SEARCH támogatásával. A bemutatott példában a G5 képlete a következő:
=IF(F5>ct,"",INDEX(data,AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)))
a következő megnevezett tartományokkal: "keresés" = D5, "ct" = D8, "adat" = B5: B55.
Megjegyzés: ez egy tömbképlet, de nem igényel vezérlést + shift + enter, mivel az AGGREGATE natívan képes kezelni a tömböket.
Magyarázat
Ennek a képletnek a lényege az INDEX függvény, az AGGREGATE segítségével kiszámíthatjuk a kivonat minden egyes sorának "n-edik egyezését":
INDEX(data,nth_match_formula)
Szinte az összes munka annak kitalálása és jelentése, hogy az "adatok" sorok egyeznek-e a keresési karakterlánccal, és az egyes illesztési értékek helyzetének jelentése az INDEX számára. Ez az így konfigurált AGGREGATE funkcióval történik:
AGGREGATE(15,6,(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data)),F5)
Az első argumentum, 15, azt mondja az AGGREGATE-nek, hogy viselkedjen KIS, és adja vissza az n-edik legkisebb értéket. A második, 6. Argumentum a hibák figyelmen kívül hagyására szolgál. A harmadik érv egy kifejezés, amely egyező találati tömböt generál (az alábbiakban leírjuk). A negyedik argumentum, az F5, "k" -ként működik a SMALL-ban az "n-edik" érték megadásához.
Az AGGREGATE tömbökön működik, és az alábbi kifejezés tömböt épít az AGGREGATE belsejében található harmadik argumentumra:
(ROW(data)-ROW($B$5)+1)/ISNUMBER(SEARCH(search,data))
Itt a ROW függvényt használjuk a relatív sorszámok tömbjének előállításához, az ISNUMBER és a SEARCH együtt pedig a keresési karakterláncot az adatokban szereplő értékekhez igazítjuk, ami TRUE és FALSE értékek tömbjét generálja.
Az okos bit az, hogy elosztjuk a sorszámokat a keresési eredményekkel. Egy ilyen matematikai műveletnél az IGAZ úgy viselkedik, mint 1, a FALSE pedig nulla. Ennek eredményeként a pozitív egyezéshez tartozó sorszámokat elosztjuk 1-vel, és túléljük a műveletet, míg a nem egyező értékekhez tartozó sorszámok megsemmisülnek és # DIV / 0 hibává válnak. Mivel az AGGREGATE a hibák figyelmen kívül hagyására van beállítva, figyelmen kívül hagyja a # DIV / 0 hibákat, és a fennmaradó értékek "n-edik" legkisebb számát adja vissza, az F oszlopban szereplő számot használva az "n" -re.
A teljesítmény kezelése
Mint minden tömbképlet, ez a képlet is "drága" a nagy adathalmazú erőforrások szempontjából. A teljesítményre gyakorolt hatások minimalizálása érdekében a teljes INDEX és MATCH képletet IF-be csomagolják:
=IF(F5>ct,"",formula)
ahol a "ct" (D8) nevű tartomány rendelkezik ezzel a képlettel:
=COUNTIF(data,"*"&search&"*")
Ez az ellenőrzés leállítja a képlet INDEX és AGGREGATE részének futtatását, miután az összes egyező érték kibontásra került.
Tömb képlet KIS
Ha az Excel verziója nem rendelkezik az AGGREGATE függvénnyel, használhat egy alternatív képletet, amely a SMALL és az IF alapul:
=IF(F5>ct,"",INDEX(data,SMALL(IF(ISNUMBER(SEARCH(search,data)),ROW(data)-ROW($B$5)+1),F5)))
Megjegyzés: ez egy tömbképlet, amelyet a control + shift + enter billentyűkombinációval kell megadni.
