Vstavané triedenie údajov v Exceli je úžasné, ale nie je dynamické. Ak triedite údaje a potom k nim pridávate údaje, budete ich musieť znova zoradiť.
Zoradiť údaje v abecednom poradí
V tomto príspevku vám ukážem rôzne spôsoby radenia údajov v abecednom poradí pomocou vzorcov. To znamená, že môžete pridať údaje a automaticky ich za vás zoradí.
Keď sú všetky údaje text bez duplikátov
Predpokladajme, že máte údaje uvedené nižšie:
V tomto prípade sú všetky údaje v textovom formáte (žiadne čísla, medzery alebo duplikáty). Na triedenie použijem pomocný stĺpček. V stĺpci vedľa údajov použite nasledujúci vzorec COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Tento vzorec porovnáva textovú hodnotu so všetkými ostatnými textovými hodnotami a vracia jej relatívne poradie. Napríklad v bunke B2 vráti 8, pretože existuje 8 textových hodnôt, ktoré sú nižšie alebo sa rovnajú textu „USA“ (abecedné poradie).
Teraz na zoradenie hodnôt použite nasledujúcu kombináciu funkcií INDEX, MATCH a ROWS:
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Tento vzorec jednoducho extrahuje mená v abecednom poradí. V prvej bunke (C2) hľadá názov krajiny, ktorá má najnižšie číslo (Austrália má 1). V druhej bunke vráti Kanadu (ktorá má číslo 2) a tak ďalej…
Alergia na pomocné stĺpce ??
Tu je vzorec, ktorý urobí to isté bez pomocného stĺpca.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Toto je maticový vzorec, takže použite Ctrl + Shift + Enter namiesto Enter.
Dekódovanie nechám na vás.
Skúste to sami … Stiahnuť ukážkový súbor
Tento vzorec funguje dobre, ak máte textové alebo alfanumerické hodnoty.
Ale nešťastne zlyhá, ak:
- V údajoch máte duplikáty (skúste zadať USA dvakrát).
- V údajoch sú medzery.
- Máte kombináciu čísel a textu (skúste vložiť 123 do jednej z buniek).
Keď sú údaje kombináciou čísel, textu, duplikátov a medzier
Teraz je tento trochu zložitý. Použím 4 pomocné stĺpce, aby som vám ukázal, ako to funguje (a potom vám dám obrovský vzorec, ktorý to urobí bez pomocných stĺpcov). Predpokladajme, že máte údaje uvedené nižšie:
Môžete vidieť, že existujú duplicitné hodnoty, prázdne miesta a čísla. Na vyriešenie každého z týchto problémov teda použijem pomocné stĺpce.
Pomocník, stĺpec 1
Do stĺpca 1 pomocníka zadajte nasledujúci vzorec COUNTIF
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Tento vzorec robí nasledovné:
- Vráti 0 pre prázdne miesta.
- V prípade duplikátov vráti rovnaké číslo.
- Text a čísla sú spracované súbežne a tento vzorec vracia rovnaké číslo pre text a číslo (napríklad 123 a India dostanú 1).
Pomocník, stĺpec 2
Do stĺpca pomocníka 2 zadajte nasledujúcu funkciu IS:
=-ISNUMBER (A2)
Pomocník, stĺpec 3
Do stĺpca 3 pomocníka zadajte nasledujúci vzorec:
=-ISBLANK (A2)
Pomocník, stĺpec 4
Do stĺpca 4 pomocníka zadajte nasledujúci vzorec
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Cieľom tohto vzorca je oddeliť medzery, čísla a textové hodnoty.
- Ak je bunka prázdna, vráti hodnotu v bunke B2 (ktorá by bola vždy 0) a hodnotu pridá do bunky D10. Stručne povedané, vráti celkový počet prázdnych buniek v údajoch
- Ak je bunka číselnou hodnotou, vráti porovnávaciu pozíciu a pripočíta celkový počet medzier. Napríklad pre 123 vráti 2 (1 je poradie 123 v dátach a existuje 1 prázdna bunka)
- Ak ide o text, vráti porovnávaciu pozíciu a pripočíta celkový počet číselných hodnôt a medzier. Napríklad v Indii pridá porovnávaciu pozíciu textu k textu (ktorá je 1) a pridá počet prázdnych buniek a počet číselných hodnôt.
Konečný výsledok - zoradené údaje
Teraz použijeme tieto pomocné stĺpce na získanie triedeného zoznamu. Tu 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 spôsob triedenia sa teraz stáva bezpečný. Ukázal som vám metódu pre 8 položiek, ale môžete ju rozšíriť na ľubovoľný počet položiek.
Skúste to sami … Stiahnuť ukážkový súbor
Jeden vzorec na triedenie všetkého (bez stĺpcov pomocníka)
Ak zvládate extrémne vzorce, tu je vzorec všetko v jednom, ktorý zoradí údaje v abecednom poradí (bez stĺpca pomocníka).
Tu je vzorec:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (MALÉ (NIE ($ 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 USD: $ A $ 9))), ROWS ($ 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)), "")
Zadajte tento vzorec do bunky a potiahnutím nadol získate zoradený zoznam. Pretože ide o vzorec poľa, použite aj Ctrl + Shift + Enter namiesto Enter.
Tento vzorec má použiteľnosť v reálnom svete. Co si myslis? Rád by som sa od vás poučil. Zanechajte svoje stopy v sekcii komentárov!
