Az Excel beépített adatrendezése elképesztő, de nem dinamikus. Ha rendezi az adatokat, majd adatokat ad hozzá azokhoz, újra kell rendeznie azokat.
Rendezze az adatokat ábécé sorrendbe
Ebben a bejegyzésben különböző módszereket mutatok be az adatok ábécé szerinti sorrendbe állítására képletek segítségével. Ez azt jelenti, hogy adhat hozzá adatokat, és automatikusan rendezi azokat az Ön számára.
Ha az adatok mind szövegek, ismétlődések nélkül
Tegyük fel, hogy rendelkezik az alább látható adatokkal:
Ebben a példában minden adat szöveges formátumban van (számok, üres vagy duplikátumok nélkül). Ennek rendezéséhez segítő oszlopot fogok használni. Az adatok melletti oszlopban használja a következő COUNTIF képletet:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ez a képlet összehasonlítja a szövegértékeket az összes többi szövegértékkel, és visszaadja annak relatív rangját. Például a B2 cellában 8 -at ad vissza, mivel 8 olyan szövegérték van, amely alacsonyabb vagy egyenlő az „US” szöveggel (ábécé sorrend).
Az értékek rendezéséhez használja az INDEX, MATCH és ROWS függvények alábbi kombinációját:
= INDEX ($ A $ 2: $ A $ 9, MATCH (SOROK ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Ez a képlet egyszerűen kivonja a neveket ábécé sorrendben. Az első cellában (C2) a legalacsonyabb számmal rendelkező ország nevét keresi (Ausztrália 1). A második cellában Kanadát adja vissza (amelynek 2 -es száma van) és így tovább …
Allergiás a segítő oszlopokra ??
Itt van egy képlet, amely ugyanezt teszi a segítő oszlop nélkül.
= INDEX ($ A $ 2: $ A $ 9, MATCH (SOROK ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Ez egy tömbképlet, ezért használja Control + Shift + Enter az Enter helyett.
Hagyom, hogy dekódolja.
Próbálja ki maga… Példa fájl letöltése
Ez a képlet jól működik, ha szöveges vagy alfanumerikus értékei vannak.
De ez kudarcot vall, ha:
- Ismétlődések vannak az adatokban (próbálja meg kétszer megadni az USA -t).
- Vannak üres helyek az adatokban.
- Számok és szöveg keveréke van (próbáljon 123 -at írni az egyik cellába).
Ha az adatok számok, szöveg, ismétlődések és üres anyagok keveréke
Most ez egy kicsit trükkös. 4 segítő oszlopot fogok használni, hogy megmutassam, hogyan működik (majd adok egy hatalmas képletet, amely a segítő oszlopok nélkül is meg fogja tenni). Tegyük fel, hogy rendelkezik az alább látható adatokkal:
Láthatja, hogy vannak ismétlődő értékek, üres és számok. Tehát segítő oszlopokat fogok használni ezeknek a problémáknak a kezelésére.
Segítő oszlop 1
Írja be a következő COUNTIF képletet az 1. segédoszlopba
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ez a képlet a következőket teszi:
- Az üres helyekhez 0 -t ad vissza.
- Ismétlődések esetén ugyanazt a számot adja vissza.
- A szöveg és a számok párhuzamosan kerülnek feldolgozásra, és ez a képlet ugyanazt a számot adja vissza a szöveghez és a számhoz (például 123 és India egyaránt 1 -et kap).
Segítő oszlop 2
Írja be a következő IS függvényt a 2. segédoszlopba:
=-SZÁM (A2)
Segítő oszlop 3
Írja be a következő képletet a 3. segédoszlopba:
=-ISBLANK (A2)
Segítő oszlop 4
Írja be a következő képletet a 4. segédoszlopba
= HA (SZÁM (A2), B2, HA (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Ennek a képletnek az ötlete az üres helyek, számok és szövegértékek elkülönítése.
- Ha a cella üres, akkor visszaadja a B2 cella értékét (ami mindig 0 lenne), és hozzáadja a D10 cellába. Dióhéjban visszaadja az adatok üres celláinak teljes számát
- Ha a cella számérték, akkor visszaadja az összehasonlító rangot, és hozzáadja az összes üres számot. Például 123 esetén 2 -t ad vissza (1 az adatok 123 -as rangja, és 1 üres cella van)
- Ha szöveg, akkor az összehasonlító rangot adja vissza, és hozzáadja a számértékek és üres számok teljes számát. Például India esetében hozzáadja a szöveg összehasonlító rangsorát a szövegben (ami 1), és hozzáadja az üres cellák számát és a számértékek számát.
Végeredmény - Rendezett adatok
Most ezeket a segítő oszlopokat használjuk a rendezett lista megszerzéséhez. Íme a képlet:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Ez a válogatási módszer most bolondbiztos lesz. Megmutattam a módszert 8 elemre, de kiterjesztheti azt annyi elemre, amennyit csak akar.
Próbálja ki maga… Példa fájl letöltése
Egy képlet az összes rendezéséhez (segítő oszlopok nélkül)
Ha tudja kezelni az extrém képleteket, itt van egy all-in-one képlet, amely ABC sorrendbe rendezi az adatokat (segédoszlop nélkül).
Íme a képlet:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL (NOT ($ A $ 2: $ A $ 9 = ""))*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9))), SOROK ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9)))), NOT ($ A $ 2: $ A $ 9 = "")*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUM (-ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Írja be ezt a képletet egy cellába, és húzza lefelé a rendezett lista megjelenítéséhez. Továbbá, mivel ez egy tömbképlet, használja Control + Shift + Enter az Enter helyett.
Ennek a képletnek valós hasznossága van. Mit gondolsz? Szívesen tanulnék tőled. Hagyja a lábnyomát a megjegyzések rovatban!
