Vestavěné třídění dat v Excelu je úžasné, ale není dynamické. Pokud data třídíte a pak k nim přidáváte data, budete je muset znovu seřadit.
Seřadit data v abecedním pořadí
V tomto příspěvku vám ukážu různé způsoby řazení dat v abecedním pořadí pomocí vzorců. To znamená, že můžete přidávat data a automaticky je za vás třídí.
Když jsou všechna data textová bez duplikátů
Předpokládejme, že máte data, jak je uvedeno níže:
V tomto případě jsou všechna data v textovém formátu (žádná čísla, mezery nebo duplikáty). Abych to vyřešil, použiji pomocný sloupec. Ve sloupci vedle dat použijte následující vzorec COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Tento vzorec porovnává textovou hodnotu se všemi ostatními textovými hodnotami a vrací její relativní pořadí. Například v buňce B2 vrátí 8, protože existuje 8 textových hodnot, které jsou nižší nebo rovny textu „US“ (abecední pořadí).
Chcete -li hodnoty seřadit, použijte následující kombinaci funkcí INDEX, MATCH a ROWS:
= INDEX ($ A $ 2: $ A $ 9, MATCH (ŘADY ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Tento vzorec jednoduše extrahuje jména v abecedním pořadí. V první buňce (C2) vyhledá název země, který má nejnižší číslo (Austrálie má 1). Ve druhé buňce vrátí Kanadu (která má číslo 2) atd.…
Alergie na pomocné sloupce ??
Zde je vzorec, který udělá to samé bez pomocného sloupce.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ŘADY ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Toto je maticový vzorec, takže použijte Ctrl + Shift + Enter místo Enter.
Dekódování nechám na vás.
Vyzkoušejte sami … Stáhněte si ukázkový soubor
Tento vzorec funguje dobře, pokud máte textové nebo alfanumerické hodnoty.
Ale to selže, pokud:
- V datech máte duplikáty (zkuste zadat USA dvakrát).
- V datech jsou mezery.
- Máte kombinaci čísel a textu (zkuste vložit 123 do jedné z buněk).
Když jsou data kombinací čísel, textu, duplikátů a mezer
Nyní je tento trochu ošidný. Použiji 4 pomocné sloupce, abych vám ukázal, jak to funguje (a pak vám dám obrovský vzorec, který to zvládne bez pomocných sloupců). Předpokládejme, že máte data, jak je uvedeno níže:
Můžete vidět, že existují duplicitní hodnoty, prázdné hodnoty a čísla. K vyřešení každého z těchto problémů tedy použiji pomocné sloupce.
Pomocný sloupec 1
Do sloupce Pomocník 1 zadejte následující vzorec COUNTIF
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Tento vzorec dělá následující:
- Vrátí 0 pro mezery.
- V případě duplikátů vrátí stejné číslo.
- Text a čísla jsou zpracovávány souběžně a tento vzorec vrací stejné číslo pro text i číslo (například 123 a Indie dostanou 1).
Sloupec pomocníka 2
Do pomocného sloupce 2 zadejte následující funkci IS:
=-ČÍSLO (A2)
Sloupec pomocníka 3
Do sloupce Pomocníka 3 zadejte následující vzorec:
=-ISBLANK (A2)
Sloupec pomocníka 4
Do sloupce Pomocník 4 zadejte následující vzorec
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Cílem tohoto vzorce je oddělit mezery, čísla a textové hodnoty.
- Pokud je buňka prázdná, vrátí hodnotu v buňce B2 (která by byla vždy 0) a přidá hodnotu do buňky D10. Stručně řečeno, vrátí celkový počet prázdných buněk v datech
- Pokud je buňka číselná hodnota, vrátí srovnávací pořadí a přidá celkový počet mezer. Například pro 123 vrátí 2 (1 je pozice 123 v datech a je zde 1 prázdná buňka)
- Pokud se jedná o text, vrátí srovnávací pořadí a sečte celkový počet číselných hodnot a mezer. Například pro Indii přidá srovnávací pozici textu v textu (což je 1) a přidá počet prázdných buněk a počet číselných hodnot.
Konečný výsledek - seřazená data
Nyní použijeme tyto pomocné sloupce k získání seřazeného seznamu. Zde je vzorec:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (MALÉ ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Tento způsob třídění se nyní stává spolehlivým. Ukázal jsem vám metodu pro 8 položek, ale můžete ji rozšířit na tolik položek, kolik chcete.
Vyzkoušejte sami … Stáhněte si ukázkový soubor
Jeden vzorec, který to všechno seřadí (bez pomocných sloupců)
Pokud zvládnete extrémní vzorce, zde je vzorec vše v jednom, který bude třídit data v abecedním pořadí (bez jakéhokoli pomocného sloupce).
Zde je vzorec:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (MALÉ (NE ($ 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))), ŘADY ($ A $ 2: A2)+SUM ((ISBLANK ($ A $ 2: $ A $ 9))), NE ($ 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 (-ČÍSLO ($ A $ 2: $ A $ 9))), 0)), "")
Zadejte tento vzorec do buňky a přetažením dolů získáte seřazený seznam. Protože se jedná o vzorec pole, použijte také Ctrl + Shift + Enter místo Enter.
Tento vzorec má užitečnost v reálném světě. Co myslíš? Rád bych se od vás učil. Zanechte své stopy v sekci komentáře!
