Hogyan készítsünk ábécé sorrendet az Excelben

Ez az útmutató bemutatja, hogyan kell ábécé sorrendet rendezni az Excelben a Rendezés és szűrés funkcióval, valamint az adatok sorrendbe rendezéséhez és formázásához szükséges képleteket.

A Microsoft Excel egy hatékony szoftver fontos adatok, például pénzügyek, értékesítési adatok, ügyfélinformációk stb. rendszerezésére. Az adatok ábécé szerinti rendezése a munkalapokon az egyik legjobb módja az adatok gyors rendezésének, elérésének és hivatkozásának. Egy cellatartomány ábécé rendezése egy tartomány ábécé szerinti rendezését jelenti az Excelben. Az adatok mindkét irányban rendezhetők, növekvő vagy csökkenő sorrendben.

Tegyük fel például, hogy nagy adatkészlettel rendelkezik az ügyfelek rendelési információiról, és meg szeretné keresni egy adott ügyfél rendelési előzményeit. A teljes lista átfésülése meglehetősen melós lesz. Ahelyett, hogy időt vesztegetne a görgetéssel, ábécé sorrendbe rendezheti az oszlopokat, hogy gyorsan megtalálja az adatokat.

Az adatok ábécé szerinti sorrendbe rendezésére különféle módok állnak rendelkezésre az Excelben, attól függően, hogy milyen adatkészleten dolgozik. Az Excel lehetővé teszi egyetlen oszlop vagy egy sor, vagy egy kiválasztott tartomány vagy egy teljes munkalap rendezését, valamint több oszlop vagy sor csoportosítását a rendezés során. Tanuljuk meg, hogyan lehet az Excel-táblázatokban lévő adatokat abc-re rendezni sorok vagy oszlopok szerint.

Miért írja be az adatokat ábécé szerint az Excelben?

Az adatok ábécé szerinti rendezése számos előnnyel jár:

  • Érthetőbbé teszi az adatokat, és könnyebben olvashatóvá teszi őket.
  • Segítségével a felhasználó könnyebben megkereshet egy adott értéket vagy ügyfél nevét az Excel adatlapon.
  • Segíti a felhasználót az ismétlődő rekordok vizuális azonosításában, így megelőzhetjük az adatbeviteli hibákat.
  • Lehetővé teszi a felhasználók számára, hogy könnyen csoportosítsák az oszlopokat vagy listákat, így a felhasználók egymás mellett tekinthetik meg őket.

Az ábécé sorrendbe rendezés (ábécé sorrendben) az adatok gyors rendezésének egyszerű, általános módja. Különösen nagy adathalmazok esetén hasznos. Összességében 4 módja van az adatok ábécé szerinti rendezésének az Excelben: A-Z vagy Z-A gomb, Rendezés funkció, szűrőfunkció és képletek.

Oszlop ábécé rendezése Excelben

Az ábécé szerinti rendezés leggyorsabb módja az Excelben a beépített Rendezés funkció használata.

Először nyissa meg az adatkészletet tartalmazó táblázatot. Jelölje ki a rendezni kívánt cellákat vagy oszlopokat, és ügyeljen arra, hogy ne jelöljön ki üres cellákat. Ezután lépjen az „Adatok” fülre, és kattintson az „A-Z” elemre a növekvő sorrendben, vagy a „Z-A” lehetőségre a csökkenő rendezéshez a Rendezés és szűrés csoportban.

Ha vannak közöttük üres cellák, az Excel azt feltételezi, hogy az összes adatot rendezték, és megáll a fekete cella felett. Ha a teljes oszlopot szeretné rendezni, egyszerűen válassza ki az oszlop bármelyik celláját, és használja a fenti lehetőséget.

Ugyanezek a beállítások elérhetők a „Főoldal” lap Szerkesztés részében található „Rendezés és szűrés” eszközben is.

Miután rákattintott a gombra, azonnal rendezze az Excel ábécé sorrendjét.

Sorok rendezése és együtt tartása az Excelben

Könnyen rendezhet egy oszlopot, ha csak egy oszlopa van a munkalapon, de ez kissé bonyolulttá válik, ha a munkalap több oszlopot tartalmaz a rendezni kívánt oszlop mellett. Néha olyan táblázatokon is dolgozunk, ahol az oszlopok rendezése után érintetlenül kell hagynunk a megfelelő sorokat.

Például, ha van egy táblázatunk a tanulók pontlistájáról. Amikor a tanulónevek oszlopát ábécé sorrendbe rendezzük, azt is szeretnénk, hogy a mellettük lévő sorokba beírt jelek is ennek megfelelően mozogjanak.

Ilyen esetekben az „A-Z” vagy a „Z-A” gombbal az egyik oszlopot ábécé sorrendbe rendezheti, és automatikusan a többi oszlopba rendezi az adatokat, a sorokat egyben tartja. Nézzük meg, hogyan működik ez egy másik példán.

Először válassza ki az első oszlopot, amely szerint rendezni szeretné a többi oszlopot. Ezután válassza az „A-Z” vagy a „Z-A” lehetőséget az Adatok lapon vagy a Kezdőlapon az adatok egy oszlopba rendezéséhez, és az Excel ennek megfelelően automatikusan átrendezi a többi oszlopot.

Ha a rendezés gombra kattint, megjelenik egy Rendezési figyelmeztetés párbeszédablak. Válassza ki a „Kiválasztás kiterjesztése” választógombot, majd kattintson a „Rendezés” gombra.

Ez egyben tartja a sorokat az oszlopok rendezése közben, anélkül, hogy az adatok hibásan illeszkednének (lásd alább).

Ha a „Folytatás az aktuális kijelöléssel” lehetőséget választja, az Excel csak a kijelölt oszlopot rendezi.

Több oszlop szerinti ábécé szerinti rendezés

Ha egy adatkészletet több oszlop szerint szeretne ábécé sorrendbe rendezni, ezt megteheti az Excel Rendezés funkciójával.

Például a következő táblázatot először ország, majd keresztnév szerint ábécé sorrendbe szeretnénk rendezni:

Először válassza ki a teljes táblázatot, amelyet ábécé szerint rendezni szeretne, majd lépjen az „Adatok” fülre, és a Rendezés és szűrés csoportban kattintson a „Rendezés” gombra.

Vagy kattintson a „Rendezés és szűrés” ikonra a „Főoldal” lap Szerkesztés részében, és válassza az „Egyéni rendezés” lehetőséget, hogy elérje ugyanazt a funkciót.

Megjelenik a Rendezés párbeszédpanel, és megmutatja az első oszlopot (paramétert), amelyben a táblázat rendezve lesz. A "Rendezés" legördülő mezőben válassza ki azt az elsődleges oszlopot, amely alapján először szeretné ábécére rendezni az adatokat, esetünkben az "Ország"-ot, és a "Rendelés" legördülő menüben válassza az "A-tól Z-ig" vagy a "Z-től A-ig"-t. .

Ezután kattintson a „Szint hozzáadása” gombra a második rendezési szint hozzáadásához. Válassza ki a második oszlopot (esetünkben Keresztnév), amellyel a „Rendezési szempont” mezőben lévő adatokat betűrendbe szeretné rendezni, és válassza az „A-tól Z-ig” vagy a „Z-től A-ig” lehetőséget. Ha a táblázat tetején vannak fejlécek, jelölje be a „Az adatoknak fejlécei vannak” jelölőnégyzetet, hogy a rendezés során kihagyhassa a fejléceket. Ha szükséges, adjon hozzá további rendezési szinteket, majd kattintson az „OK” gombra.

A táblázat ABC sorrendben van elrendezve: először ország, majd keresztnév szerint, az alábbiak szerint.

A sorok ábécé szerinti rendezése az Excelben

Néha érdemes lehet a sorokat ábécé sorrendbe rendezni az Excelben az oszlopok helyett. Ezt az Excel Rendezés funkciójával lehet megtenni. Tegyük fel például, hogy van egy táblázata, ahol az első sor B-től T-ig tartó oszlopa tartalmazza a városneveket, ahol a különböző kiskereskedelmi típusok alatt elérhető üzletek száma, az A oszlop pedig a kiskereskedő típusát tartalmazza. A cellák nyomon követésére szolgálnak, hogy az egyes városokban hány üzlet van a különböző kiskereskedői kategóriákban.

Először jelölje ki az összes sort, amelyet ábécé sorrendbe szeretne rendezni; Ha nem szeretné áthelyezni a sorcímkéket, hagyja ki őket a választásból. Most lépjen az „Adatok” fülre, és kattintson a „Rendezés” gombra a Rendezés és szűrés csoportban.

A Rendezés párbeszédpanelen kattintson az „Opciók” gombra.

Megjelenik egy másik kis párbeszédpanel, amelyben kiválaszthatja a „Rendezés balról jobbra” vagy a „Rendezés fentről lefelé” lehetőséget. Válassza a „Rendezés balról jobbra” lehetőséget, ha sorok szerint szeretne ábécé rendezni, majd kattintson az „OK” gombra a Rendezés párbeszédpanelhez való visszatéréshez.

Most válassza ki az ABC sorrendbe rendezni kívánt sort a „Rendezés alapja” legördülő listában (ebben a példában az 1. sor). A másik mezőben tartsa meg a cellaértékeket a „Rendezés” mezőben, és válassza az „A-tól Z-ig” (növekvő sorrend) lehetőséget a Sorrend mezőben. Ezután kattintson az „OK” gombra a párbeszédpanel bezárásához.

Most a táblázatunk első sora ábécé (növekvő) sorrendben van rendezve, a többi sor pedig ennek megfelelően átrendeződik az alábbiak szerint.

Oszlopok ábécé szerinti rendezése szűrő használatával

Az ábécé sorrendbe rendezésének másik gyors módja az Excelben a Szűrő funkció használata. Miután szűrőket alkalmaz az oszlopokra, az összes oszlop rendezési beállításai egyetlen egérkattintással elérhetők.

Ha szűrőt szeretne hozzáadni az oszlopokhoz, válasszon ki egy vagy több oszlopot, és kattintson a „Szűrő” lehetőségre az „Adatok” lap Rendezés és szűrés csoportjában. Ha a szűrőt a teljes táblázatra szeretné alkalmazni, jelölje ki a táblázat bármelyik celláját, és kattintson a „Szűrő” lehetőségre.

A Szűrő opciót úgy is elérheti, hogy a "Főoldal" lap Szerkesztés csoportjában a "Rendezés és szűrés" eszközre kattint, és a "Szűrő" lehetőséget választja.

Minden oszlopfejlécben megjelenik egy kis legördülő nyíl. Kattintson az ABC sorrendben rendezni kívánt oszlopok nyílára, és válassza a „Rendezés A-tól Z-ig” vagy a „Rendezés Z-től A-ig” lehetőséget.

Ez a kívánt oszlopot a választott ábécé sorrendbe rendezi, és ha a „Rendezés A-tól Z-ig” lehetőséget választotta, egy kis felfelé mutató nyíl jelenik meg a szűrőgombon, amely jelzi a rendezési sorrendet (növekvő):

Speciális rendezés egyéni sorrendben

Az ábécé szerinti rendezés nem ideális minden alfabetikus adathoz. Néha az adatokat ábécé sorrendbe lehet rendezni, de nem ez a leghatékonyabb módja a rendezésnek.

Képzeljünk el egy olyan helyzetet, ahol lehet, hogy van egy listája, amely hónapok vagy hétköznapok nevét tartalmazza, az ábécé szerinti rendezés ebben a helyzetben nem hasznos. Ésszerűbb a listát időrendi sorrendbe rendezni. Ha A-tól Z-ig rendezi, akkor ábécé sorrendbe rendezi a hónapokat, először április, majd augusztus, február, június stb. De nem ezt akarod. Szerencsére nagyon könnyű időrendi sorrendbe rendezni az Excelben az Advanced Custom Sort (Speciális egyéni rendezés) opcióval.

Kezdje a rendezni kívánt táblázat kiválasztásával. Ezután válassza az „Egyéni rendezés” lehetőséget a „Rendezés és szűrés” alatt a Kezdőlap lap Szerkesztés részében.

A Rendezés párbeszédpanel oszloprészében válassza ki az év hónapjait tartalmazó oszlopot, rendezze az "Értékek" szerint a szokásos módon, és a Rendezés részben válassza az "Egyéni lista" lehetőséget.

Az Egyéni listák párbeszédpanelen létrehozhat egy saját listát. Létezik néhány alapértelmezett egyéni lista, beleértve a hetek napjait, a rövidített hónapokat, az év hónapjait és így tovább. Válassza ki az igényeinek megfelelő rendezési lehetőséget (esetünkben január, február,...), majd kattintson az „OK” gombra. Ezután kattintson ismét az „OK” gombra a lista időrendi rendezéséhez.

Észre fogja venni, hogy sikeresen rendezte a listát a havi sorrend szerint.

Hogyan rendezzünk ábécé sorrendben az Excelben Excel-képletek használatával

Ha rajong a képletekért, használhatja az Excel képleteket a lista ábécé rendezésére. Az adatok ábécé rendezésére használható két képlet a COUNTIF és a VLOOKUP.

Például van egy listánk azokról a nevekről, amelyeket képlet segítségével szeretnénk ábécére rendezni.

Ennek rendezéséhez a meglévő táblázathoz hozzáadunk egy ideiglenes oszlopot, melynek neve ez a „Rendezési sorrend”.

Az adatok melletti cellában (A2) használja a következő COUNTIF képletet:

=COUNTIF($B$2:$B$20,"<="&B2) 

A fenti képlet összehasonlítja a B2 cellában lévő szövegértéket az összes többi szövegértékkel (B3:B20), és visszaadja a relatív rangját. Például a B2 cellában 11-et ad vissza, mivel 11 olyan szövegérték van, amelyek ábécé sorrendben kisebbek vagy egyenlőek, mint a „Nancy” szöveg. Az eredmény az alkalmazott nevének rendezési sorrendje a B2 cellában.

Ezután a kitöltő fogantyúval húzza ezt a képletet a teljes tartomány kitöltéséhez. Ez megadja a listában szereplő nevek rendezési sorrendjét.

Most a fenti képernyőképen látható adatokat a rendezési sorrendszám alapján kell rendeznünk, ehhez pedig egy VLOOKUP funkciót fogunk használni.

A szintaxis:

=VLOOKUP(;A:B;2;0)

Itt a „rendezési szám” a számokat 1-től 20-ig növekvő sorrendben jelenti. Csökkenő sorrendben a számoknak 20-tól 1-ig kell lenniük.

Itt van a D oszlop a rendezett nevek számára. A D2 cellához írja be a következő VLOOKUP képletet:

=KERESÉS(1;A:B;2;0)

Hasonlóképpen, a második és a harmadik cellához a következő képletet kell használnia:

=KERESÉS(2;A:B;2;0) és =KERESÉS(3;A:B;2;0) stb…

Miután beírta ezt a VLOOKUP képletet az adatok melletti minden cellába, a lista ábécé szerint rendeződik.

Ahelyett, hogy minden cellába manuálisan beírná a fent említett képletet (1-20), a sor funkcióval is megkönnyítheti a munkát. A Sor () függvény az aktuális cella sorszámát adja vissza. Tehát a sorfüggvény segítségével a képlet a következő lesz:

=KERESÉS(SOOR()-1;A:B;2;0)

Ez a képlet ugyanazt az eredményt adja, mint a fenti képlet.

Ezután a kitöltő fogantyúval húzza a képletet a teljes tartomány kitöltéséhez.

A bejegyzések ábécé szerinti sorrendje a vezetéknév alapján

Időnként gyakran kell ábécé sorrendbe rendeznünk a vezetékneveket tartalmazó adatlapokat. Ilyen esetekben annak ellenére, hogy a nevek a keresztnévvel kezdődnek, a vezetéknév szerint kell ábécé sorrendbe rendezni őket. Ezt Excel szöveges képletekkel teheti meg.

Ehhez először ki kell bontania a kereszt- és vezetékneveket a teljes nevekből két különböző oszlopba. Ezután fordítsa meg a neveket, rendezze őket, majd fordítsa vissza eredeti formájukra.

Ha az A2-ben van egy teljes név, írja be a következő képleteket két különböző cellába (B2 és C2), majd másolja le a képleteket az oszlopokba (a kitöltési fogantyú segítségével) az utolsó adatokat tartalmazó celláig:

A keresztnév kivonásához írja be ezt a képletet a C2 cellába:

=BAL(A2;KERESÉS(" ",A2)-1)

A vezetéknév kivonásához írja be ezt a képletet a D2 cellába:

=JOBBRA(A2,LEN(A2)-KERESÉS(" ",A2,1))

Ezután az E2 cellában az utó- és vezetékneveket fordított sorrendben vesszővel elválasztva összefűzte:

=D2&", "&C2

A kereszt- és vezetékneveket különválasztottuk, és felcseréltük. Most ábécé sorrendbe kell rendeznünk és vissza kell fordítanunk őket. Ahogy fentebb látható, amikor a képlet segítségével kivonjuk a neveket, a C, D és E oszlopok valójában képleteket tartalmaznak, de úgy vannak formázva, hogy értékeknek tűnjenek. Tehát a képletet értékekké kell konvertálnunk.

Ehhez először jelölje ki az összes képletcellát (E1:E31), és nyomja meg a gombot Ctrl + C lemásolni őket. Kattintson a jobb gombbal a kijelölt cellákra, kattintson a „Beillesztési beállítások” alatti „Értékek” ikonra, és nyomja meg az „Enter” billentyűt.

Ezután válasszon ki egy cellát a kapott oszlopban, és igényei szerint kattintson az „A-tól Z-ig” vagy a „Z-től A-ig” lehetőségre az „Adatok” vagy a „Kezdőlap” lapon.

A gombra kattintás után egy Rendezési figyelmeztetés párbeszédpanel jelenik meg. Válassza a „Kiválasztás kiterjesztése” opciót, és kattintson a „Rendezés” gombra.

Amint látja, a teljes oszlop a vezetéknévvel ábécé szerint rendezve.

Most, ha vissza szeretné állítani az eredeti „Keresztnév Vezetéknév formátumra”, csak ugyanazokat a képleteket kell megadnia, de különböző cellahivatkozásokkal.

A nevet (E2) ismét két részre kell osztania az alábbi képletekkel:

A G2-ben vegye ki a keresztnevet:

=JOBBRA(E2,LEN(E2)-KERESÉS(" ",E2))

A G2-ben húzza ki a vezetéknevet:

=BAL(E2,KERESÉS(" ",E2)-2)

És csatlakoztassa a két részt, hogy megkapja az eredeti teljes nevet:

=G2&" "&H2

Minden alkalommal, amikor beírja a képletet az első cellába, ügyeljen arra, hogy a képletet lemásolja az oszlopban (a kitöltési fogantyú segítségével) az utolsó adatot tartalmazó celláig.

Most már csak annyit kell tennie, hogy még egyszer konvertálja át a képleteket értékkonverzióvá, ahogy fent tettük, és kész. ez megtörtént és.