Az Excelben nagyon egyszerű az ugyanabban az oszlopban megjelenő kereszt-, közép- és vezetékneveket külön oszlopokra osztani, különböző módokon.
Tegyük fel, hogy kapott egy névjegyzéket, amelyben az összes név teljes névvel szerepel egyetlen oszlopban, és valószínűleg el kell különítenie az utó-, közép- és vezetéknevet, és külön oszlopokra kell osztania. A nevek elválasztásának néhány módja van – a Szöveg oszlopokká funkció, a Flash-kitöltés és a képletek használatával.
Az Excelben nagyon egyszerű a neveket egy oszlopból két vagy több oszlopra osztani. Ebben a cikkben bemutatjuk, hogyan oszthatja fel a neveket különböző oszlopokra az Excelben különböző módokon.
Hogyan lehet felosztani a neveket az Excelben
Három egyszerű módja van a nevek felosztásának az Excelben. Az adatszerkezettől és attól függően, hogy statikus vagy dinamikus felosztási neveket szeretne-e, válassza a következő módszerek egyikét:
- A nevek felosztása a Szöveg oszlopokká funkcióval
- A neveket képletekkel válassza el
- A neveket különítse el a Flash Fill segítségével
A nevek elválasztása a Szövegből oszlopba varázsló segítségével
A Szöveg oszlopba varázsló az egyik leggyakoribb módja a kereszt- és vezetéknevek, valamint a középső nevek elválasztásának az Excelben. Győződjön meg arról, hogy a felosztani kívánt nevek mellett vannak üres oszlopok, mert a teljes nevek külön oszlopokra lesznek felosztva.
Például az alábbi adatkészlet teljes nevekkel rendelkezik, és fel szeretné osztani/elválasztani a vezeték- és utónevet, és külön cellákba szeretné tárolni.
Először jelölje ki a teljes neveket tartalmazó oszlopot, amelyet el szeretne választani. Ezután lépjen az „Adatok” fülre, és kattintson a „Szöveg az oszlopokba” lehetőségre az „Adateszközök” részben.
Megnyílik a Szöveg oszlopokká konvertálása varázsló. A varázsló első lépésében válassza a „Határozott” lehetőséget, és kattintson a „Tovább” gombra.
A Szöveg oszlopokká konvertálása varázsló 2/3. lépésében válassza ki az adatokat elválasztó határolót, távolítsa el a többi pipát, majd kattintson a „Tovább” gombra. Esetünkben a „szóköz” választja el a kereszt- és vezetéknevet, ezért ezt a határolót választjuk.
Az ablak lenti Adat-előnézete szakasza megmutatja a nevek elemzésének módját.
A 3/3 lépésben válassza ki az adatformátumot és a célt, majd kattintson a „Befejezés” gombra.
Általában az alapértelmezett „Általános” a legtöbb adattípusnál jól működik. A „Cél” mezőben adja meg azt a helyet, ahol a kimenetet meg kívánja jeleníteni. Meg kell adnunk annak az oszlopnak az első cellájának címét, ahol az eredményeket szeretnénk elérni (esetünkben B2).
Ne feledje, ha nem adja meg a célcellát, a varázsló felülírja az eredeti adatokat, ezért ügyeljen arra, hogy üres oszlopot válasszon.
Miután rákattint a „Befejezés” gombra, azonnal szétválasztja a teljes neveket két külön oszlopba (Keresztnév és Vezetéknév).
Kövesse ugyanezeket a lépéseket, ha van kereszt-, középső és vezetékneve, és a nevei kettő helyett három oszlopra lesznek osztva.
Jegyzet: Ennek a módszernek az eredménye statikus. Ez azt jelenti, hogy ha megváltoztatja az eredeti nevet, akkor ezt újra meg kell tennie a nevek felosztásához.
Felosztott nevek vesszővel elválasztva
Ha az utó- és vezetékneveket vessző választja el, kövesse az alábbi lépéseket a vessző eltávolításához, valamint az utó- és vezetéknevek kettéválasztásához.
A következő példában a nevek fordított formátumban vannak formázva (Vezetéknév, Keresztnév), ahol először a vezetéknév, majd egy vessző, majd az utónév következik.
Válassza ki a neveket, és lépjen az Adatok -> Szöveg oszlopba menüpontra. Az 1. lépésben válassza ki a „Határolót”, és kattintson a „Tovább” gombra. A 2. lépésben a Határolójelek alatt jelölje be a „Vessző” (,) lehetőséget határolóként, mert a neveket vessző választja el.
Az utolsó lépésben válassza ki az adatformátumot „Általános”-ként, adja meg a célt, és kattintson a „Befejezés” gombra.
Most a nevek külön oszlopokban lesznek.
A nevek elkülönítése képletekkel
A Szöveg oszlopokká varázsló segítségével gyorsan és egyszerűen elválaszthatja a neveket. Ha azonban szerkeszteni szeretné az eredeti neveket, és olyan dinamikus metódust szeretne, amely automatikusan frissül minden alkalommal, amikor megváltoztatja a neveket, akkor a nevek képletekkel való felosztása a megfelelő választás. A nevek elválasztásához használhatja a LEFT, RIGHT, MID, LEN, valamint a SEARCH vagy FIND funkciókat.
Különítse el a kereszt- és vezetéknevet az Excelben képletek segítségével
Szerezze meg a keresztnevet
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és az utónevet külön cellába szeretné választani. A KERESÉS és a LEFT függvényt egy képletbe kell kombinálnia, hogy megkapja a keresztnevet.
Használja a következő képletet a keresztnév megszerzéséhez:
=BAL(A2;KERES(" ",A2)-1)Ez a képlet a KERESÉS függvényt használja a szóköz (" ") helyének megtalálásához a vezeték- és utónév között, és kivonja az 1-et, hogy kizárja magát a szóközt. Ez a szám ezután a LEFT funkcióhoz kerül, amely ezt a pozíciószámot használja az összes előtte lévő szöveg kibontására. A KERESÉS funkciót is használhatja a KERESÉS funkció helyett.
Miután beírta a képletet egy üres cellába (B2), húzza le a kitöltő fogantyút a többi cellára a képlet alkalmazásához, és az összes keresztnév fel lett osztva a B oszlopba az alábbiak szerint:
A KERESÉS és KERESÉS függvényt beágyazhatja a LEFT függvénybe, hogy kivonja a keresztnevet. A fő különbség a két függvény között az, hogy a FIND megkülönbözteti a kis- és nagybetűket, míg a SEARCH nem tesz különbséget a kis- és nagybetűk között.
Ha az utó- és vezetéknév között szóköz helyett vessző (,) van, akkor használja a vesszőt első argumentumként a FIND függvényben:
=BAL(A2;KERES(",",A2)-1)Szerezze meg a vezetéknevet
Ha most ki kell bontania a vezetéknevet, használja a JOBBRA funkciót. A következő képlet kivonja a vezetéknevet ugyanabból az adatkészletből:
=JOBBRA(A2,LEN(A2)-KERESÉS(" ",A2))A képlet először megkeresi a szóköz karakter pozícióját, levonja ezt a számot a karakterlánc teljes hosszából (amelyet a LEN függvény ad vissza), majd ezt a számot továbbítja a RIGHT függvénynek, hogy kivonja ennyi karaktert a karakterlánc végéből. (név).
Különítse el az első, középső és vezetéknevet az Excelben képletek segítségével
A középső nevet tartalmazó nevek felosztásához különböző képletekre van szükség a névformátumtól függően.
Ahhoz, hogy a keresztnév ha van középső neve vagy kezdőbetűje, használja ugyanazt a LEFT FIND formulát, amelyet már ismer.
Szerezze meg a vezetéknevet
A fenti RIGHT FIND képlet jól működik, ha csak az utó- és vezetéknév szerepel, nem lesz sok haszna, ha az eredeti nevek középső nevet vagy középső kezdőbetűt tartalmaznak. Ez azért van, mert nem vett figyelembe két szóköz karaktert a névben.
A vezetéknév megadásához, ha van középső neve is, használja ezt a képletet:
=JOBBRA(A2,LEN(A2)-KERESÉS(" ",A2,KERESÉS(" ",A2,1)+1))A vezetéknév kinyeréséhez először határozza meg a második szóköz karakterének pozícióját a beágyazott SEARCH függvényekkel, és adjon hozzá 1-et a SEARCH(” “,A2,1) értékhez, hogy a kivonás a következő karakterrel induljon. Ezután vonja ki a 2. szóköz helyét a karakterlánc teljes hosszából, és kapja meg a vezetéknév hosszát eredményként. Ezután adja meg ezt a számot a RIGHT függvénynek, hogy kivonja a karakterek számát a karakterlánc végéből.
Szerezd meg a középső nevet
A MID függvény három argumentumot használ, az első argumentum a szöveget vagy a cella címét adja meg, a második a kezdő pozíciót, az utolsó argumentum pedig a karakterek számát adja meg a középső név kinyeréséhez az adott pozícióból.
A szintaxis:
=MID(szöveg, kezdő_szám, karakterek száma)A középső név megadásához írja be ezt a képletet egy üres cellába:
=KÖZÉP(A2,KERESÉS(" ",A2)+1,KERESÉS(" ",A2,KERESÉS(" ",A2)+1)-KERESÉS(" ",A2)-1)Lássuk, hogyan működik ez az összetett képlet:
A középső név vagy a középső kezdőbetű kinyeréséhez meg kell határoznia mindkét szóköz helyét a teljes névben. Az első szóköz helyének meghatározásához írja be ezt KERESÉS(" ",A2) függvényt a „start_num” argumentumban, és adjunk hozzá 1-et, hogy a kivonás a következő karaktertől induljon.
Ezután, hogy megtudja a középső név hosszát, tegye ezt KERESÉS(" ",A2,KERESÉS(" ",A2)+1)-KERESÉS(" ",A2)-1 beágyazott függvény a „karakterek száma” argumentumban, amely kivonja az 1. szóköz pozícióját a 2. szóköz pozíciójából, és kivonja az 1-et az eredményből, hogy eltávolítsa a záró szóközt. A végeredmény megmutatja, hány karaktert kell kivonni.
Most a MID funkció a középső név kezdőpozíciójával és a kivonandó számkarakterekkel elválasztja a középső nevet a teljes névtől (A2).
A nevek elválasztása az Excelben a Flash Fill segítségével
A Flash-kitöltés többnyire egy adott minta adatainak automatikus kitöltésére szolgál. Használható nevek felosztására is. Csak az Excel 2013, 2016, 2019 és 365 verziókban érhető el.
Tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és csak a keresztneveket szeretné kiszűrni a teljes nevek közül.
Az eredeti név melletti cellába írja be a keresztnevet. Ebben az esetben írja be a „Steve” kifejezést a B2 cellába.
Ezután kezdje el beírni a keresztnevet az oszlop második cellájába. Gépelés közben, ha az Excel mintát érzékel, a Flash Fill automatikusan megjeleníti a többi cellában lévő keresztnév listáját (szürkével).
Ha szürkén látja a nevek listáját, és ha ezek a nevek helyesek, akkor csak nyomja meg az „Enter” billentyűt, és a Flash Fill automatikusan kitölti az oszlop többi részét a keresztnevekkel.
Kövesse ugyanezt az eljárást a vezetéknevek külön oszlopba történő elkülönítéséhez.
Az eredmény:
A Flash Fill úgy működik, hogy észlel egy mintát az adatokban, és követi azt, miközben megadja a módosított adatokat. Először, amikor beírja a keresztnevet az első cellába, a Flash Fill nem ismeri fel a mintát. De amikor újra elkezdi beírni a keresztnevet a második cellába, a Flash Fill felismeri a mintát, és megmutatja a keresztnevek felosztására vonatkozó javaslatot. Ezután csak nyomja meg az „Enter” billentyűt.
Általában a Flash Fill funkció alapértelmezés szerint engedélyezve van. Ha az Excelben nem működik, az első cellába való keresztnév beírása után egyszerűen jelölje ki a második cellát, és kattintson a „Flash Fill” gombra az „Adatok” fül Adateszközök csoportjában.
Alternatív megoldásként megnyomhatja a „Ctrl” + „E” billentyűket is, hogy ugyanazokat az eredményeket kapja.
Most a többi cella utónevekkel lesz kitöltve.
Előfordulhat, hogy nem látja a mintajavaslatot szürkén, ebben az esetben a kitöltő fogantyúval kaphatja meg a Flash Fill eredményt.
Először kézzel írja be a neveket két cellába, és jelölje ki mindkét cellát. Ezután vigye a kurzort a kijelölés jobb alsó sarkába. Észre fogja venni, hogy a kurzor egy kis zöld négyzetről (Kitöltő ikon) plusz ikonra változik.
Ezután kattintson duplán a plusz ikonra. Ez kitölti a többi cellát. Ezen a ponton az eredmények hibásak, látni fogja, hogy mindkét keresztnév újra és újra megismétlődik. Ezután a kapott adatok jobb alsó sarkában egy kis automatikus kitöltési ikon jelenik meg, az alábbiak szerint. Kattintson erre az „Automatikus kitöltés” ikonra, és válassza a „Flash Fill” lehetőséget.
Ez kitölti a keresztneveket az összes cellában:
Távolítsa el a középső nevet
A Flash Fill eszközzel is megszabadulhat a középső névtől a teljes névből.
Tegyük fel például, hogy rendelkezik az alábbi adatkészlettel, és csak az utó- és vezetéknevet szeretné megkapni a középső név vagy a középső kezdőbetű nélkül.
Ha középső név vagy középső kezdőbetű nélküli neveket szeretne kapni, kézzel írja be a „Lord Stark” kifejezést a szomszédos cellába. Ezután a második szomszédos cellába írja be a „Daenerys Targaryen” kifejezést. Gépelés közben a Flash Fill felismeri a mintát, és megjeleníti a nevek listáját középső nevek nélkül (szürkén).
Ha a javaslat helyes, nyomja meg az „Enter” billentyűt, és a Flash Fill automatikusan kitölti a többi cellát a középső név nélküli nevekkel.
Ha csak a középső neveket szeretné megkapni utó- és vezetéknév nélkül, írja be a középső nevet az első két cellába, és használja a Flash Fill eszközt az oszlopban lévő összes teljes név középső neveihez.
Ez az oktatóanyag bemutatja, hogyan lehet elválasztani a neveket a szöveges adatok kezelése közben. Ezek a módszerek akkor is hasznosak lehetnek, ha más típusú adatokkal, például címekkel, terméknevekkel, márkanevekkel stb. dolgozik.