Általános képlet
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Összegzés
Az egyedi értékek dinamikus rendezéséhez és kivonásához az adatok listájából egy tömbképlet segítségével állíthat be rangot egy segítő oszlopban, majd egy speciálisan felépített INDEX és MATCH képletet használhat az egyedi értékek kinyeréséhez. A bemutatott példában a C5: C13 rang meghatározásának képlete:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
ahol az "adat" a B5: B13 elnevezett tartomány.
Megjegyzés: ez egy több cellás tömbképlet, amelyet a vezérlő + shift + enter billentyűvel adunk meg.
Magyarázat
Megjegyzés: ennek a képletnek az alapgondolata Mike Girvin Control + Shift + Enter című kiváló könyvében szereplő példából adódik.
A bemutatott példa több képletet használ, amelyeket az alábbiakban ismertetünk. Magas szinten az MMULT függvény segítségével kiszámítják a numerikus rangot egy segítő oszlopban (C oszlop), majd ezt a rangot az INDEX és MATCH képlet használja a G oszlopban az egyedi értékek kinyerésére.
Az adatok értékeinek rangsorolása
Az MMULT függvény mátrix szorzást hajt végre, és minden értékhez numerikus rangot rendel. Az első tömb a következő kifejezéssel jön létre:
--(data>TRANSPOSE(data))
Itt használjuk a TRANSPOSE funkciót hozzon létre egy vízszintes sor adatot , és az összes értékeket összehasonlítjuk egymással. Lényegében minden értéket összehasonlítanak minden más értékkel, hogy megválaszolják a kérdést: "nagyobb-e ez az érték, mint minden más érték". Ez kétdimenziós tömböt eredményez, 9 oszlop x 9 sor, tele IGAZ és HAMIS értékekkel. A kettős negatív (-) az IGAZ HAMIS értékek 1-re és nullára kényszerítésére szolgál. A kapott tömböt így képzelheti el:
A fenti 1s és nullák mátrixa array1 lesz az MMULT függvényen belül. Az Array2 ezzel a kifejezéssel jön létre:
ROW(data)^0
Itt az "adatok" sorainak minden számát nulla erejéig emeljük, hogy létrehozzunk egy egydimenziós tömböt, 1 oszlop x 9 sor, amelyet az 1. szám tölt. a rang oszlopban látható értékek.
Egy tömbben egyszerre kapjuk vissza mind a 9 rangsort, ezért az eredményeket egyszerre különböző cellákba kell helyeznünk. Ellenkező esetben minden cella csak a visszaküldött tömb első rangsorát mutatja.
Megjegyzés: ez egy több cellás tömbképlet, amelyet a vezérlő + shift + enter billentyűkombinációval adunk meg, a C5: C13 tartományba.
Üres cellák kezelése
Az üres cellákat a rangsorolási képlet ezen részével kezeljük:
=IF(data="",ROWS(data)
Itt, mielőtt futtatnánk az MMULT-ot, ellenőrizzük, hogy az "adatok" aktuális cellája üres-e. Ha igen, hozzárendelünk egy rangértéket, amely megegyezik az adatok sorszámával. Ezt úgy végezzük, hogy az üres cellákat a lista végére kényszerítsük, ahol később könnyen kizárhatók lesznek, amikor az egyedi értékeket kinyerjük (az alábbiakban kifejtjük).
Egyedi értékek számlálása
Az adatokban az egyedi értékek számlálásához az E5 képlete a következő:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Mivel a fenti rangsorolási képlet minden értékhez numerikus rangot rendel, az egyedi értékek számlálásához használhatjuk a FREQUENCY függvényt a SUM-mal. Ezt a képletet itt részletesen elmagyarázzuk. Ezután kivonunk 1-et az eredményből, ha vannak üres cellák az adatokban:
-(blank>0)
ahol "blank" az elnevezett E8 tartomány, és ezt a képletet tartalmazza:
=COUNTBLANK(data)
Lényegében csökkentjük az egyedi számot eggyel, ha üres cellák vannak az adatokban, mivel ezeket nem vesszük bele az eredményekbe. Az E5 cellában lévő egyedi számot "egyedi" (egyedi számláláshoz) nevezzük, és az INDEX és MATCH képlet felhasználja az üres cellák kiszűrésére (az alábbiakban leírjuk).
Egyedi értékek kinyerése
Az egyedi értékek kinyeréséhez a G5 a következő képletet tartalmazza, lemásolva:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Mielőtt lefuttatnánk az INDEX és MATCH képletet, először ellenőrizzük, hogy a kibontási területen az aktuális sorok száma nagyobb-e, mint az "egyedi" (E5) nevű tartomány egyedi száma:
=IF(ROWS($G$5:G5)>unique,"",
Ha igen, akkor elkészültünk az egyedi értékek kinyerésével, és egy üres karakterláncot adunk vissza (""). Ha nem, akkor futtatjuk az extrakciós képletet:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Megjegyezzük, hogy itt két MATCH funkció van, az egyik a másik belsejében. A belső MATCH kiterjesztő tartományt használ egy tömbhöz, és a megnevezett "adatok" tartományt használja a keresési értékhez:
MATCH(data,$G$4:G4,0)
Figyelje meg, hogy a bővülő tartomány a fenti "4. sor" elején kezdődik. A belső MATCH eredménye egy tömb, amely az adatok minden egyes értékéhez tartalmaz egy numerikus pozíciót (az értéket már kivonták), vagy a # N / A hibát (az érték még nincs kibontva). Ezután az IF és az ISNA segítségével szűrjük ezeket az eredményeket, és a még kibontatlan "adatokban" visszaadjuk a rang értékét:
IF(ISNA(results),rank))
Ez a művelet egy tömböt eredményez, amelyet betáplálnak a MIN függvénybe annak érdekében, hogy megkapja a még minimálisan kivont adatértékek "minimális rangértékét". A MIN függvény ezt az értéket adja vissza a külső MATCH-nak keresési értékként, a megnevezett tartomány pedig "rang" tömbként:
MATCH(min_not_extracted,rank)),rank,0)
Végül a MATCH a legalacsonyabb rangérték pozícióját adja vissza az INDEX-nek sorszámként, az INDEX pedig az adatértéket a kibontási tartomány aktuális sorában.
