Integruotas „Excel“ duomenų rūšiavimas yra nuostabus, tačiau jis nėra dinamiškas. Jei surūšiuosite duomenis ir pridėsite prie jų duomenis, turėsite juos rūšiuoti dar kartą.
Rūšiuoti duomenis abėcėlės tvarka
Šiame įraše parodysiu įvairius būdus, kaip rūšiuoti duomenis abėcėlės tvarka naudojant formules. Tai reiškia, kad galite pridėti duomenų ir jis juos automatiškai surūšiuos.
Kai duomenys yra visi tekstai be dublikatų
Tarkime, kad turite duomenų, kaip parodyta žemiau:
Šiame pavyzdyje visi duomenys yra teksto formatu (be skaičių, tuščių ar dublikatų). Norėdami tai surūšiuoti, naudosiu pagalbinį stulpelį. Stulpelyje šalia duomenų naudokite šią COUNTIF formulę:
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ši formulė palygina teksto vertę su visomis kitomis teksto reikšmėmis ir grąžina jos santykinį reitingą. Pavyzdžiui, langelyje B2 jis pateikia 8, nes yra 8 teksto reikšmės, mažesnės arba lygios tekstui „JAV“ (abėcėlės tvarka).
Dabar, norėdami rūšiuoti vertes, naudokite šį funkcijų INDEX, MATCH ir ROWS derinį:
= INDEKSAS ($ A $ 2: $ A $ 9, MATCH (ROWS ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Ši formulė tiesiog ištraukia vardus abėcėlės tvarka. Pirmajame langelyje (C2) jis ieško šalies pavadinimo, kuriame yra mažiausias skaičius (Australija turi 1). Antrame langelyje ji grąžina Kanadą (kurios numeris 2) ir pan.
Alergiška pagalbinėms kolonoms ??
Čia yra formulė, kuri padarys tą patį be pagalbinio stulpelio.
= INDEX ($ A $ 2: $ A $ 9, MATCH (ROWS ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Tai masyvo formulė, todėl naudokite „Control“ + „Shift“ + „Enter“ vietoj Enter.
Paliksiu jums iššifruoti.
Išbandykite patys … Atsisiųskite pavyzdinį failą
Ši formulė gerai veikia, jei turite teksto ar raidžių ir skaičių reikšmes.
Bet tai nepavyksta, jei:
- Turite duomenų kopijas (du kartus pabandykite įvesti JAV).
- Duomenyse yra tuščių vietų.
- Turite skaičių ir teksto derinį (pabandykite įvesti vieną iš langelių 123).
Kai duomenys yra skaičių, teksto, dublikatų ir tuščių vietų derinys
Dabar šis yra šiek tiek sudėtingas. Naudosiu 4 pagalbinius stulpelius, kad parodyčiau, kaip tai veikia (ir tada pateiksiu didžiulę formulę, kuri tai padarys be pagalbinių stulpelių). Tarkime, kad turite duomenų, kaip parodyta žemiau:
Matote, kad yra pasikartojančių reikšmių, tuščių ir skaičių. Taigi naudoju pagalbinius stulpelius kiekvienai iš šių problemų spręsti.
Pagalbinė 1 stulpelis
1 pagalbinio stulpelio įveskite šią COUNTIF formulę
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Ši formulė atlieka šiuos veiksmus:
- Tuščiajai reikšmei grąžinama 0.
- Pasikartojančių dokumentų atveju jis grąžina tą patį numerį.
- Tekstas ir skaičiai apdorojami lygiagrečiai, o ši formulė grąžina tą patį teksto ir skaičiaus skaičių (pavyzdžiui, 123 ir Indija gauna 1).
Pagalbinė 2 stulpelis
2 pagalbinio stulpelio įveskite šią IS funkciją:
=-ISNUMBER (A2)
Pagalbinė kolona 3
3 pagalbinio stulpelio įveskite šią formulę:
=-ISBLANK (A2)
4 pagalbinė skiltis
4 pagalbinio stulpelio įveskite šią formulę
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Šios formulės idėja yra atskirti ruošinius, skaičius ir teksto reikšmes.
- Jei langelis tuščias, jis grąžina B2 langelio vertę (kuri visada būtų 0) ir prideda reikšmę langelyje D10. Trumpai tariant, jis grąžins bendrą tuščių langelių skaičių duomenyse
- Jei langelis yra skaitinė vertė, jis grąžins lyginamąjį reitingą ir pridės bendrą tuščių skaičių. Pvz., 123 atveju grąžina 2 (1 yra 123 reitingas duomenyse ir 1 tuščias langelis)
- Jei tai tekstas, jis grąžina lyginamąjį reitingą ir prideda bendrą skaičių reikšmių ir tuščių vietų skaičių. Pavyzdžiui, Indijoje ji prideda lyginamąjį teksto reitingą tekste (kuris yra 1) ir prideda tuščių langelių skaičių bei skaičių reikšmių.
Galutinis rezultatas - surūšiuoti duomenys
Dabar mes naudosime šiuos pagalbinius stulpelius, kad gautume surūšiuotą sąrašą. Čia yra formulė:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL ($ E $ 2: $ E $ 9, ROWS ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Šis rūšiavimo būdas dabar tampa kvailas. Aš jums parodžiau 8 elementų metodą, tačiau galite jį išplėsti tiek, kiek norite.
Išbandykite patys … Atsisiųskite pavyzdinį failą
Viena formulė viską surūšiuoti (be pagalbinių stulpelių)
Jei galite valdyti ekstremalias formules, čia yra viskas viename formulė, kuri surūšiuos duomenis abėcėlės tvarka (be jokio pagalbinio stulpelio).
Čia yra formulė:
= 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)+SUMA (-ISNUMBER ($ A $ 2: $ A $ 9))), ROWS ($ 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)+SUMA (-ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Įveskite šią formulę į langelį ir vilkite žemyn, kad gautumėte surūšiuotą sąrašą. Be to, kadangi tai yra masyvo formulė, naudokite „Control“ + „Shift“ + „Enter“ vietoj Enter.
Ši formulė yra naudinga realiame pasaulyje. Ką tu manai? Norėčiau iš jūsų pasimokyti. Palikite savo pėdsakus komentarų skiltyje!
