Razvrstavanje podataka ugrađenih u Excel nevjerojatno je, ali nije dinamično. Ako sortirate podatke, a zatim im dodate podatke, morat ćete ih ponovno sortirati.
Sortirajte podatke po abecednom redu
U ovom postu ću vam pokazati različite načine razvrstavanja podataka po abecednom redu pomoću formula. To znači da možete dodati podatke i automatski će ih sortirati umjesto vas.
Kad su svi podaci Tekst bez duplikata
Pretpostavimo da imate podatke kao što je prikazano u nastavku:
U ovom primjeru svi su podaci u tekstualnom formatu (bez brojeva, praznina ili duplikata). Za ovo ću upotrijebiti pomoćni stupac. U stupcu pored podataka upotrijebite sljedeću formulu COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ova formula uspoređuje tekstualnu vrijednost sa svim ostalim tekstualnim vrijednostima i vraća njezin relativni rang. Na primjer, u ćeliji B2 vraća 8 jer ima 8 tekstualnih vrijednosti koje su niže ili jednake tekstu ‘US’ (abecednim redom).
Sada za sortiranje vrijednosti upotrijebite sljedeću kombinaciju funkcija INDEX, MATCH i ROWS:
= INDEKS ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Ova formula jednostavno izdvaja imena abecednim redom. U prvoj ćeliji (C2) traži se naziv zemlje s najmanjim brojem (Australija ima 1). U drugoj ćeliji vraća Kanadu (koja ima broj 2) i tako dalje …
Alergijski na pomoćne stupce ??
Evo formule koja će učiniti isto bez pomoćnog stupca.
= INDEKS ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Ovo je formula niza, pa upotrijebite Control + Shift + Enter umjesto Enter.
Ostavit ću vam da dekodirate.
Pokušajte sami … Preuzmite datoteku primjera
Ova formula dobro funkcionira ako imate tekstualne ili alfanumeričke vrijednosti.
Ali jadno ne uspijeva ako:
- Imate duplikate u podacima (pokušajte dvaput staviti US).
- U podacima postoje praznine.
- Imate mješavinu brojeva i teksta (pokušajte staviti 123 u jednu od ćelija).
Kad su podaci mješavina brojeva, teksta, duplikata i praznina
Sad je ovo malo zeznuto. Koristit ću 4 pomoćna stupca da vam pokažem kako to radi (a zatim ću vam dati ogromnu formulu koja će to učiniti bez pomoćnih stupaca). Pretpostavimo da imate podatke kao što je prikazano u nastavku:
Možete vidjeti da postoje dvostruke vrijednosti, prazno i brojevi. Stoga ću upotrijebiti pomoćne stupce za rješavanje svakog od ovih problema.
Kolona pomoćnika 1
Unesite sljedeću formulu COUNTIF u stupac Pomoćnik 1
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ova formula čini sljedeće:
- Vraća 0 za prazna polja.
- U slučaju duplikata, vraća isti broj.
- Tekst i brojevi obrađuju se paralelno i ova formula vraća isti broj za tekst i broj (na primjer 123 i Indija dobivaju 1).
Kolona pomoćnika 2
U kolonu 2 za pomoć unesite sljedeću funkciju IS:
=-ISNUMBER (A2)
Kolona pomoćnika 3
U pomoćnu kolonu 3 unesite sljedeću formulu:
=-ISBLANK (A2)
Kolona pomoćnika 4
Unesite sljedeću formulu u pomoćni stupac 4
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Ideja ove formule je odvajanje praznina, brojeva i tekstualnih vrijednosti.
- Ako je ćelija prazna, vraća vrijednost u ćeliju B2 (koja bi uvijek bila 0) i dodaje vrijednost u ćeliju D10. Ukratko, vratit će ukupan broj praznih ćelija u podacima
- Ako je ćelija numerička vrijednost, vratit će usporedni rang i dodati ukupan broj praznih mjesta. Na primjer, za 123 vraća 2 (1 je rang 123 u podacima, a postoji 1 prazna ćelija)
- Ako je to tekst, vraća usporedni rang i dodaje ukupan broj numeričkih vrijednosti i praznina. Na primjer, za Indiju, dodaje usporedni poredak teksta u tekstu (koji je 1) i dodaje broj praznih ćelija i broj numeričkih vrijednosti.
Konačni rezultat - sortirani podaci
Sada ćemo koristiti ove pomoćne stupce za dobivanje sortiranog popisa. Evo formule:
= IFERROR (INDEKS ($ A $ 2: $ A $ 9, MATCH (MALO ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Ova metoda razvrstavanja sada postaje sigurna od budala. Pokazao sam vam metodu za 8 stavki, ali možete je proširiti na onoliko stavki koliko želite.
Pokušajte sami … Preuzmite datoteku primjera
Jedna formula za sve sortiranje (bez pomoćnih stupaca)
Ako se možete nositi s ekstremnim formulama, evo sveobuhvatne formule koja će podatke sortirati po abecednom redu (bez pomoćnog stupca).
Evo formule:
= IFERROR (INDEKS ($ 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))), ROWS ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9))), NE ($ A $ 2: $ A $ 9 = "")*AKO (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)), "")
Unesite ovu formulu u ćeliju i povucite je prema dolje da biste dobili sortirani popis. Također, budući da je ovo formula niza, koristite Control + Shift + Enter umjesto Enter.
Ova formula ima korisnost u stvarnom svijetu. Što misliš? Volio bih učiti od vas. Ostavite svoje tragove u odjeljku za komentare!
