Razvrščanje podatkov, vgrajenih v Excel, je neverjetno, vendar ni dinamično. Če razvrstite podatke in jim nato dodate podatke, jih boste morali znova razvrstiti.
Razvrsti podatke po abecednem vrstnem redu
V tem prispevku vam bom pokazal različne načine razvrščanja podatkov po abecednem vrstnem redu s formulami. To pomeni, da lahko dodate podatke in jih samodejno razvrsti.
Ko so vsi podatki besedilo brez podvojenih besed
Recimo, da imate podatke, kot je prikazano spodaj:
V tem primeru so vsi podatki v besedilni obliki (brez številk, praznin ali podvojenih podatkov). Za razvrščanje bom uporabil stolpec za pomoč. V stolpcu poleg podatkov uporabite naslednjo formulo COUNTIF:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ta formula primerja besedilno vrednost z vsemi drugimi besedilnimi vrednostmi in vrne njeno relativno uvrstitev. Na primer, v celici B2 vrne 8, saj obstaja 8 besedilnih vrednosti, ki so nižje ali enake besedilu "US" (po abecednem vrstnem redu).
Zdaj za razvrščanje vrednosti uporabite naslednjo kombinacijo funkcij INDEX, MATCH in ROWS:
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Ta formula preprosto izvleče imena po abecednem vrstnem redu. V prvi celici (C2) išče ime države z najmanjšim številom (Avstralija ima 1). V drugi celici se vrne Kanada (ki ima številko 2) in tako naprej …
Alergičen na pomožne stolpce ??
Tukaj je formula, ki bo storila enako brez stolpca pomočnikov.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
To je matrična formula, zato uporabite Control + Shift + Enter namesto Enter.
Pustil bom, da dekodirate.
Poskusite sami … Prenesite datoteko z primerom
Ta formula dobro deluje, če imate besedilne ali alfanumerične vrednosti.
Toda neuspešno, če:
- V podatkih imate podvojene podatke (dvakrat poskusite vnesti ZDA).
- V podatkih so prazne točke.
- Imate kombinacijo številk in besedila (poskusite v eno od celic vnesti 123).
Ko so podatki mešanica števil, besedila, podvojenih in praznih mest
Zdaj je ta malce zapleten. Uporabil bom 4 pomožne stolpce, da vam pokažem, kako deluje (in vam nato dal ogromno formulo, ki bo to naredila brez pomožnih stolpcev). Recimo, da imate podatke, kot je prikazano spodaj:
Vidite lahko, da so podvojene vrednosti, prazne in številke. Zato bom za reševanje vsakega od teh vprašanj uporabil pomožne stolpce.
Stolpec za pomoč 1
V stolpec 1 za pomoč vnesite naslednjo formulo COUNTIF
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ta formula naredi naslednje:
- Za praznine vrne 0.
- V primeru dvojnikov vrne isto številko.
- Besedilo in številke se obdelujejo vzporedno in ta formula vrne isto število za besedilo in številko (na primer 123 in Indija dobita 1).
Pomočni stolpec 2
V stolpec 2 za pomoč vnesite naslednjo funkcijo IS:
=-ISNUMBER (A2)
Pomočni stolpec 3
V stolpec 3 za pomoč vnesite naslednjo formulo:
=-ISBLANK (A2)
Pomočni stolpec 4
V stolpec 4 za pomoč vnesite naslednjo formulo
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Ideja te formule je ločiti prazna polja, številke in besedilne vrednosti.
- Če je celica prazna, vrne vrednost v celico B2 (ki bi bila vedno 0) in doda vrednost v celico D10. Na kratko bo vrnilo skupno število praznih celic v podatkih
- Če je celica številska vrednost, bo vrnila primerjalni rang in dodala skupno število praznih mest. Na primer, za 123 vrne 2 (1 je rang 123 v podatkih in obstaja 1 prazna celica)
- Če je besedilo, vrne primerjalni rang in doda skupno število numeričnih vrednosti in praznin. Za Indijo na primer doda primerjalno uvrstitev besedila v besedilo (ki je 1) in doda število praznih celic in število numeričnih vrednosti.
Končni rezultat - razvrščeni podatki
Zdaj bomo uporabili te pomožne stolpce za pridobitev razvrščenega seznama. Tukaj je formula:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (MALO ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Ta način razvrščanja je zdaj varen. Pokazal sem vam metodo za 8 elementov, vendar jo lahko razširite na poljubno število elementov.
Poskusite sami … Prenesite datoteko z primerom
Ena formula za vse razvrščanje (brez pomožnih stolpcev)
Če lahko obvladate skrajne formule, je tukaj formula "vse v enem", ki bo podatke razvrstila po abecednem vrstnem redu (brez pomožnega stolpca).
Tukaj je formula:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL (NOT ($ A $ 2: $ A $ 9 = ""))*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: 9 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 = "")*ČE (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)), "")
Vnesite to formulo v celico in jo povlecite navzdol, da dobite razvrščen seznam. Ker je to matrična formula, uporabite Control + Shift + Enter namesto Enter.
Ta formula ima uporabnost v resničnem svetu. Kaj misliš? Rad bi se učil od vas. Pustite svoje sledi v oddelku za komentarje!
