Excel iebūvētā datu šķirošana ir pārsteidzoša, taču tā nav dinamiska. Ja jūs kārtojat datus un pēc tam tiem pievienojat datus, tie ir jāšķiro vēlreiz.
Kārtot datus alfabētiskā secībā
Šajā rakstā es parādīšu dažādus veidus, kā kārtot datus alfabētiskā secībā, izmantojot formulas. Tas nozīmē, ka varat pievienot datus, un tas automātiski tos sakārtos jūsu vietā.
Ja visi dati ir teksts bez dublikātiem
Pieņemsim, ka jums ir šādi dati:
Šajā piemērā visi dati ir teksta formātā (bez cipariem, tukšām vietām vai dublikātiem). Lai to sakārtotu, es izmantošu palīgu kolonnu. Kolonnā blakus datiem izmantojiet šādu COUNTIF formulu:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Šī formula salīdzina teksta vērtību ar visām pārējām teksta vērtībām un atgriež tās relatīvo rangu. Piemēram, šūnā B2 tas atgriež 8, jo ir 8 teksta vērtības, kas ir zemākas vai vienādas ar tekstu “ASV” (alfabētiskā secībā).
Tagad, lai kārtotu vērtības, izmantojiet šādu funkciju INDEX, MATCH un ROWS kombināciju:
= INDEKSS ($ A $ 2: $ A $ 9, MATCH (RIJAS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Šī formula vienkārši izraksta vārdus alfabētiskā secībā. Pirmajā šūnā (C2) tiek meklēts tās valsts nosaukums, kurai ir mazākais skaitlis (Austrālijā ir 1). Otrajā šūnā tas atgriež Kanādu (kuras numurs ir 2) un tā tālāk …
Alerģiska pret palīgu kolonnām ??
Šeit ir formula, kas darīs to pašu bez palīga kolonnas.
= INDEKSS ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Šī ir masīva formula, tāpēc izmantojiet Control + Shift + Enter Enter vietā.
Es atstāšu to, lai jūs atkodētu.
Izmēģiniet pats … Lejupielādējiet piemēra failu
Šī formula darbojas labi, ja jums ir teksta vai burtciparu vērtības.
Bet tas neizdodas, ja:
- Jums ir datu dublikāti (mēģiniet divreiz ievietot ASV).
- Datos ir tukšas vietas.
- Jums ir ciparu un teksta sajaukums (mēģiniet ievietot 123 vienā no šūnām).
Ja dati ir ciparu, teksta, dublikātu un tukšu elementu sajaukums
Tagad šis ir nedaudz sarežģīts. Es izmantošu 4 palīgu kolonnas, lai parādītu, kā tas darbojas (un pēc tam sniegšu jums milzīgu formulu, kas to darīs bez palīgu kolonnām). Pieņemsim, ka jums ir šādi dati:
Jūs varat redzēt, ka ir dublētas vērtības, tukšs un cipari. Tāpēc es izmantošu palīgu kolonnas, lai risinātu katru no šīm problēmām.
Palīgs 1. aile
Palīga 1. slejā ievadiet šādu COUNTIF formulu
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Šī formula veic šādas darbības:
- Tukšajām vietām tas atgriež 0.
- Dublikātu gadījumā tas atgriež to pašu numuru.
- Teksts un skaitļi tiek apstrādāti paralēli, un šī formula atgriež teksta un skaitļa vienādu skaitli (piemēram, 123 un Indija iegūst 1).
Palīgs 2. sleja
Palīga 2. slejā ievadiet šādu IS funkciju:
=-SKAITS (A2)
Palīgs 3. sleja
Palīga 3. slejā ievadiet šādu formulu:
=-ISBLANK (A2)
Palīgs 4. aile
Palīga 4. slejā ievadiet šādu formulu
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Šīs formulas ideja ir nošķirt tukšās vietas, ciparus un teksta vērtības.
- Ja šūna ir tukša, tā atgriež vērtību šūnā B2 (kas vienmēr būtu 0) un pievieno vērtību šūnā D10. Īsumā tas atgriezīs datu kopējo tukšo šūnu skaitu
- Ja šūna ir skaitliska vērtība, tā atgriezīs salīdzinošo rangu un pievienos kopējo tukšo vietu skaitu. Piemēram, 123 atgriež 2 (1 ir 123 rangs datos, un ir 1 tukša šūna)
- Ja tas ir teksts, tas atgriež salīdzinošo rangu un pievieno kopējo skaitlisko vērtību un tukšo vietu skaitu. Piemēram, Indijai tas pievieno teksta salīdzinošo rangu (kas ir 1) un pievieno tukšo šūnu skaitu un skaitlisko vērtību skaitu.
Gala rezultāts - sakārtoti dati
Tagad mēs izmantosim šīs palīgu kolonnas, lai iegūtu sakārtotu sarakstu. Šeit ir formula:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Šī šķirošanas metode tagad kļūst droša. Es jums parādīju metodi 8 vienumiem, bet jūs varat to paplašināt līdz tik daudziem vienumiem, cik vēlaties.
Izmēģiniet pats … Lejupielādējiet piemēra failu
Viena formula visu sakārtošanai (bez palīgkolonnām)
Ja jūs varat rīkoties ar ekstremālām formulām, šeit ir visaptveroša formula, kas sakārtos datus alfabētiskā secībā (bez palīga kolonnas).
Šeit ir formula:
= 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))), ROWS ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9)))), NAV ($ 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)), "")
Ievadiet šo formulu šūnā un velciet to uz leju, lai iegūtu sakārtoto sarakstu. Turklāt, tā kā šī ir masīva formula, izmantojiet Control + Shift + Enter Enter vietā.
Šai formulai ir reāla lietderība. Ko tu domā? Es labprāt mācītos no jums. Atstājiet savas pēdas komentāru sadaļā!
