Általános képlet
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Összegzés
Az adatok dinamikus rendezéséhez mind a számokkal, mind a szöveggel ábécé sorrendben, egy képlet segítségével numerikus rangot generálhat egy segítő oszlopban, majd az INDEX és a MATCH segítségével jelenítheti meg az értékeket a rang alapján. A bemutatott példában a C5 képlete a következő:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
ahol az "adat" a B5: B13 elnevezett tartomány.
Magyarázat
Ez a képlet először egy rangértéket hoz létre a COUNTIF-alapú kifejezés segítségével:
=COUNTIF(data,"<="&B5)
amelyet itt részletesebben elmagyarázunk. Ha az adatok tartalmazzák az összes szöveges értéket, vagy az összes numerikus értéket, akkor a rangsor helyes lesz. Ha azonban az adatok szöveget és számokat egyaránt tartalmaznak, akkor az összes szöveges érték rangsorát "áthelyeznünk" kell a számértékek figyelembe vétele érdekében. Ez a képlet második részével történik itt:
+(COUNT(data)*ISTEXT(B7))
Itt a COUNT függvény segítségével számszerű értékeket kapunk az adatokból, majd megszorozzuk az eredményt az ISTEXT logikai eredményével, amely teszteli, hogy az érték szöveg-e, és visszaadja-e az IGAZ vagy HAMIS értéket. Ez gyakorlatilag megsemmisíti a COUNT eredményt, amikor az aktuális sorban lévő számmal dolgozunk.
Ismétlődések kezelése
Ha az adatok duplikátumokat tartalmaznak, a képletet az alábbiak szerint módosíthatjuk, hogy egymás után rangot rendeljünk a többször megjelenő értékekhez:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Ez a verzió módosítja a kezdeti COUNTIF függvény logikáját, és hozzáad egy másik COUNTIF-et kibővítő hivatkozással a növekményes duplikációkra.
Rendezett értékek megjelenítése
Az értékek rendezése az ábécé sorrendben a számított rangérték felhasználásával, és az E5 a következő INDEX és MATCH képletet tartalmazza:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
ahol az "adat" a B5: B13 elnevezett tartomány, a "rang" pedig a C5: C13 elnevezett tartomány.
További információ a képlet működéséről az itt található példában található.
Üresek kezelése
Az üres cellák nulla rangot generálnak. Feltételezve, hogy figyelmen kívül akarja hagyni az üres cellákat, ez jól működik, mert a fenti INDEX és MATCH képlet 1-től kezdődik. Ugyanakkor # N / A hibát fog látni a rendezett értékek végén, egyet-egyet minden üres cellához. Ennek kezelésének egyszerű módja, ha az INDEX és MATCH képletet az IFERROR-ba burkolja így:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
