Külön név és vezetéknév az Excelben (osztott nevek képletek segítségével)

Videó megtekintése - Nevek felosztása Excelben (kereszt-, közép- és vezetéknévre)

Az Excel egy csodálatos eszköz a szöveges adatok szeletelésére és felkockázására.

Annyi hasznos képlet és funkció található, amelyekkel szöveges adatokkal dolgozhat az Excelben.

Az egyik nagyon gyakori kérdés, amit a szöveges adatok kezelésével kapcsolatban kapok:Hogyan lehet elválasztani a kereszt- és vezetéknevet (vagy kereszt-, közép- és vezetéknevet) az Excelben?“.

Van néhány egyszerű módszer a nevek felosztására az Excelben. A választott módszer attól függ, hogy az adatok hogyan vannak felépítve, és azt szeretné, hogy az eredmény statikus vagy dinamikus legyen.

Kezdjük tehát, és nézzük meg a nevek felosztásának különböző módjait az Excelben.

Nevek felosztása szöveg segítségével oszlopokra

Az Excel Szöveg oszlopok funkciója lehetővé teszi a szövegértékek gyors felosztását egy sor külön cellájába.

Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel, és el szeretné különíteni a vezeték- és vezetéknevet, és külön cellákba szeretné lekérni ezeket.

Az alábbiakban bemutatjuk a lépéseket az utónév és a vezetéknév szétválasztásához a Szöveg oszlopokba használatával:

1. Válassza ki az összes nevet az oszlopban (A2: A10 ebben a példában)
2. Kattintson az „Adatok” fülre
3. Az „Adateszközök” csoportban kattintson a „Szöveg oszlopokba” lehetőségre.
4. Végezze el a következő módosításokat a Szöveg konvertálása oszlop varázslóban:
   1. 1. lépés a 3 -ból: Válassza a Elhatárolt lehetőséget (ez lehetővé teszi a tér használatát elválasztóként), majd kattintson a Tovább gombra
   2. 2. lépés: Válassza ki a Szóköz opciót, majd kattintson a Tovább gombra
   3. 3 /3. Lépés: Állítsa be a B2 -t célcellának (különben felülírja a meglévő adatokat)
5. Kattintson a Befejezés gombra

A fenti lépések azonnal felosztják a neveket kereszt- és vezetéknévre (a B oszlopban a keresztnévvel, a C oszlopban pedig a vezetéknévvel).

Megjegyzés: Ha már vannak adatok a cellákban (azokban, ahol a Text to Columns kimenet várható), az Excel figyelmeztetést jelenít meg, amely tudatja, hogy a cellákban már vannak adatok. Választhat, hogy felülírja -e az adatokat, vagy törli a szöveget az oszlopokba, és először manuálisan eltávolítja azokat.

Ha kész, törölheti a teljes név adatokat, ha akarja.

Néhány tudnivaló, amikor a Szöveg oszlopokba programozással elkülöníti a kereszt- és vezetékneveket az Excelben:

1. Ennek eredménye statikus. Ez azt jelenti, hogy abban az esetben, ha új adatokkal rendelkezik, vagy az eredeti adatokon változtatások történnek, akkor ezt újra kell tennie a nevek felosztásához.
2. Ha csak a keresztnevet vagy csak a vezetéknevet szeretné, akkor hagyja ki a nem kívánt oszlopokat a „Szöveg oszlopba varázsló” párbeszédpanel 3. lépésében. Ehhez válassza ki az előnézetben az átugrani kívánt oszlopot, majd válassza a „Ne importálja az oszlopot (ugrás)” lehetőséget.
3. Ha nem adja meg a célcellát, a Szöveg oszlopba felülírja az aktuális oszlopot

A Szöveg oszlopokba opció akkor a legalkalmasabb, ha konzisztens adatai vannak (például minden név csak kereszt- és vezetéknévvel rendelkezik, vagy minden név kereszt-, közép- és vezetéknévvel rendelkezik).

Ebben a példában megmutattam, hogyan lehet elválasztani azokat a neveket, amelyeknél szóköz van. Ha a határoló vessző, vagy vessző és szóköz kombinációja, akkor is ugyanazokat a lépéseket használhatja. Ebben az esetben megadhatja az elválasztót a varázsló 2 /3 -as lépésében. Már van lehetőség a vessző elválasztóként való használatára, vagy kiválaszthatja az „Egyéb” opciót, és megadhat egyéni elválasztót is.

Míg a Szöveg oszlopokba gyors és hatékony módja a nevek felosztásának, csak akkor alkalmas, ha azt szeretné, hogy a kimenet statikus eredmény legyen. Ha van olyan adatkészlete, amely kibővülhet vagy módosulhat, akkor jobb, ha képletekkel választja el a neveket.

Különítse el a kereszt-, közép- és vezetékneveket képletek segítségével

A képletek lehetővé teszik a szöveges adatok szeletelését és feldarabolását, valamint a kívánt adatok kinyerését.

Ebben a részben különböző képleteket osztok meg, amelyekkel elválaszthatja a névadatokat (az adatok felépítése alapján).

Az alábbiakban bemutatjuk a három képletet, amelyekkel elkülönítheti a kereszt-, a közép- és a vezetéknevet (ezeket részletesen kifejtjük a következő szakaszokban).

A keresztnév megszerzésének képlete:

= BALRA (A2, KERESÉS ("", A2) -1)

A középső név megszerzésére szolgáló képlet:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

Vezetéknév a képlethez:

= JOBB (A15, LEN (A15) -SEARCH ("@", CSERÉLŐ (A15, "", "@", LEN (A15) -LEN (CSERÉLŐ (A15, "", ""))))))

Szerezd meg a keresztnevet

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és gyorsan el akarja különíteni a vezetéknevet egy cellában és a vezetéknevet egy cellában.

Az alábbi képlet megadja a keresztnevet:

= BALRA (A2, KERESÉS ("", A2) -1)

A fenti képlet a SEARCH függvény segítségével határozza meg a szóköz pozícióját a kereszt- és vezetéknév között. A LEFT függvény ezt a szóközpozíció számot használja az előtte lévő szöveg kibontásához.

Ez meglehetősen egyszerű módszer a szövegérték egy részének kinyerésére. Mivel mindössze annyit kell tennünk, hogy beazonosítjuk az első szóköz karaktert, nem mindegy, hogy a névnek van -e középső neve vagy sem. A fenti képlet jól fog működni.

Most minden példával haladjunk egy kicsit.

Szerezd meg a vezetéknevet

Tegyük fel, hogy ugyanaz az adatkészlete van, és ezúttal meg kell adnia a vezetéknevet.

Az alábbi képlet kivonja a vezetéknevet a fenti adatkészletből:

= JOBB (A2, LEN (A2) -SEARCH ("", A2))

Ismét nagyon egyszerű.

Ezúttal először megtaláljuk a szóköz karakter pozícióját, amelyet ezután a szóköz után maradt karakterek számának megállapítására használunk (ez lenne a vezetéknév).

Ezt úgy érjük el, hogy a szóköz karakter pozícióértékét kivonjuk a névben szereplő karakterek teljes számával.

Ezt a számot a JOBB funkcióban használjuk, hogy lekérjük ezeket a karaktereket a név jobb oldaláról.

Bár ez a képlet nagyszerűen működik, ha csak a kereszt- és a vezetéknév szerepel, nem működik, ha középső neve is van. Ennek az az oka, hogy csak egy szóközt vettünk figyelembe (kereszt- és vezetéknév között). A középső név további szóközzel egészíti ki a nevet.

A vezetéknév lekéréséhez, ha középső neve is van, használja az alábbi képletet:

= JOBB (A15, LEN (A15) -SEARCH ("@", CSERÉLŐ (A15, "", "@", LEN (A15) -LEN (CSERÉLŐ (A15, "", ""))))))

Most ez egy kicsit bonyolultabbá vált … nem?

Hadd magyarázzam meg, hogyan működik ez.

A fenti képlet először megtalálja a névben szereplő szóközök teljes számát. Ez úgy történik, hogy a név hosszát a szóközjellel és anélkül kapjuk meg, majd kivonjuk a szó nélkül lévőt a szóközzel rendelkezőből. Ez adja meg a szóközök teljes számát.

A SUBSTITUTE függvény ezután az utolsó szóköz karakterének „@” szimbólummal való helyettesítésére szolgál (bármilyen szimbólumot használhat - ez nem valószínű, hogy előfordul a név részeként).

Miután a @ szimbólumot lecseréltük az utolsó szóköz karakter helyére, könnyen megtalálhatja ennek a @ szimbólumnak a helyzetét. Ez a SEARCH funkció segítségével történik.

Most már csak ki kell húznia a @ szimbólumtól jobbra lévő összes karaktert. Ez a JOBB funkció használatával érhető el.

Szerezd meg a középső nevet

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és ki szeretné vonni a középső nevet.

A következő képlet ezt teszi:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

A fenti képlet a MID funkciót használja, amely lehetővé teszi a kezdő pozíció és az ebből a pozícióból kivonandó karakterek számának megadását.

A SEARCH funkció segítségével könnyen megtalálható a kiindulási helyzet.

A nehéz rész az, hogy megtaláljuk, hány karaktert kell kivonni a kezdő pozíció után. Ennek eléréséhez meg kell határoznia, hogy hány karakter van a kezdő pozíciótól az utolsó szóközig.

Ezt a SUBSTITUTE funkció használatával teheti meg, és az utolsó szóköz karaktert „@” szimbólumra cserélheti. Ha ez megtörtént, könnyen használhatja a SEARCH funkciót az utolsó szóköz karakterének pozíciójának megkereséséhez.

Most, hogy megvan a kiindulási helyzet és az utolsó szóköz pozíciója, a MID funkció használatával könnyen lekérheti a középső nevet.

A nevek elválasztására szolgáló képlet egyik előnye, hogy az eredmény dinamikus. Tehát abban az esetben, ha az adatkészlete kibővül, és több nevet ad hozzá, vagy ha néhány név megváltozik az eredeti adatkészletben, akkor nem kell aggódnia a kapott adatok miatt.

Nevek különválasztása a Keresés és csere segítségével

Szeretem a „Keresd meg és cseréld” rugalmasságot, mert helyettesítő karaktereket is használhatsz benne.

Először hadd magyarázzam meg, mi az a wild card karakter.

A helyettesítő karakter olyan szöveg, amelyet bármilyen szöveg helyett használhat. Például használhat csillagszimbólumot (*), és az tetszőleges számú karaktert képvisel az Excelben. Hogy egy példát mondjak, ha meg akarom találni az összes A betűvel kezdődő nevet, használhatom az A* karaktert a keresés és helyettesítés során. Ez megkeresi és kijelöli az összes cellát, ahol a név A -val kezdődik.

Ha még mindig nem világos, ne aggódjon. Olvassa tovább, és a következő néhány példa egyértelművé teszi, hogy melyek a helyettesítő karakterek, és hogyan lehet ezeket gyorsan elkülöníteni a nevekből (vagy az Excel szövegértékeiből).

Az alábbi példákban mindenképpen készítsen biztonsági másolatot az adatkészletről. A Find and Replace megváltoztatja a felhasznált adatokat. A legjobb, ha először másolja és illessze be az adatokat, majd használja a Keresés és csere lehetőséget a másolt adatkészleten.

Szerezd meg a keresztnevet

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és csak a keresztnevet szeretné megkapni.

Ennek lépései az alábbiakban találhatók:

1. Másolja a névadatokat az A oszlopba, és illessze be a B oszlopba.
2. A B oszlopban lévő adatok kiválasztásával kattintson a Kezdőlap fülre
3. A Szerkesztés csoportban kattintson a Keresés és kiválasztás gombra.
4. Kattintson a Csere gombra. Ezzel megnyílik a "Keresés és csere" párbeszédpanel.
5. A „Keresés és csere” párbeszédpanelen adja meg a következőt
   1. Találja meg, mit: * (szóköz és a csillag szimbólum)
   2. Csere erre: hagyja üresen
6. Kattintson az Összes cseréje gombra.

A fenti lépések megadják a keresztnevet, és eltávolítanak mindent a keresztnév után.

Ez akkor is működik, ha vannak nevei, amelyeknek középső neve van.

Profi tipp: A Keresés és csere párbeszédpanel megnyitásához a billentyűparancs a következő Control + H (tartsa lenyomva a vezérlőgombot, majd nyomja meg a H gombot).

Szerezd meg a vezetéknevet

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és csak a vezetéknevet szeretné megkapni.

Ennek lépései az alábbiakban találhatók:

1. Másolja a névadatokat az A oszlopba, és illessze be a B oszlopba.
2. A B oszlopban lévő adatok kiválasztásával kattintson a Kezdőlap fülre
3. A Szerkesztés csoportban kattintson a Keresés és kiválasztás gombra.
4. Kattintson a Csere gombra. Ezzel megnyílik a "Keresés és csere" párbeszédpanel.
5. A „Keresés és csere” párbeszédpanelen adja meg a következőt
   1. Találja meg, mit: * (csillag szimbólum, majd szóköz)
   2. Csere erre: hagyja üresen
6. Kattintson az Összes cseréje gombra.

A fenti lépések megadják a vezetéknevet, és eltávolítanak mindent a keresztnév előtt.

Ez akkor is működik, ha vannak nevei, amelyeknek középső neve van.

Távolítsa el a középső nevet

Abban az esetben, ha csak a középső névtől szeretne megszabadulni, és csak a kereszt- és a vezetéknévvel rendelkezik, akkor ezt a Find and Replace segítségével teheti meg.

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és el szeretné távolítani a középső nevet.

Ennek lépései az alábbiakban találhatók:

1. Másolja a névadatokat az A oszlopba, és illessze be a B oszlopba.
2. A B oszlopban lévő adatok kiválasztásával kattintson a Kezdőlap fülre
3. A Szerkesztés csoportban kattintson a Keresés és kiválasztás gombra.
4. Kattintson a Csere gombra. Ezzel megnyílik a "Keresés és csere" párbeszédpanel.
5. A „Keresés és csere” párbeszédpanelen adja meg a következőt
   1. Keresse meg: * (szóköz, utána csillag, majd szóköz)
   2. Csere erre: (csak tegyen ide egy szóközt)
6. Kattintson az Összes cseréje gombra.

A fenti lépések eltávolítják a középső nevet a teljes névből. Abban az esetben, ha egyes neveknek nincs középső neve, nem változtatják meg őket.

Külön nevek a Flash Fill használatával

A Flash kitöltést az Excel 2013 -ban vezették be, és nagyon egyszerűvé teszi a szöveges adathalmaz módosítását vagy tisztítását.

És amikor a nevek adatainak szétválasztásáról van szó, ez a Flash Fill sikátorában van.

A legfontosabb tudnivaló a Flash Fill használatakor, hogy szükség van egy olyan mintára, amelyet a Flash Fill képes azonosítani. Miután azonosította a mintát, könnyen segít a nevek felosztásában az Excelben (ezzel kapcsolatban világosabb lesz, ha néhány alábbi példát áttekint).

Szerezze meg a kereszt- vagy vezetéknevet a teljes névből

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és csak a keresztnevet szeretné megkapni.

1. A szomszédos cellába írja be manuálisan a keresztnevet a teljes névből. Ebben a példában Rick -et írnám be.
2. A második cellába kézzel írja be az első nevet a szomszédos cella nevéből. Gépelés közben látni fogja, hogy a Flash Fill automatikusan megjeleníti a keresztnév listáját (szürkén).
3. Ha szürkén látja a neveket, gyorsan nézzen végig, és győződjön meg róla, hogy a megfelelő neveket mutatja. Ha ezek helyesek, nyomja meg az Enter billentyűt, és a Flash Fill automatikusan kitölti a többi cellát a keresztnévvel.

A Flash Fill -nek olyan mintát kell adnia, amelyet követhet a módosított adatok megadásakor. Példánkban, amikor az első cellába írja be a keresztnevet, a Flash Fill nem tudja kitalálni a mintát.

De amint elkezdi beírni a keresztnevet a második cellába, a Flash Fill megérti a mintát, és javaslatot mutat. Ha a javaslat helyes, csak nyomja meg az Enter billentyűt.

És ha nem helyes, akkor megpróbálhat kézzel bevinni néhány cellába, és ellenőrizheti, hogy a Flash Fill képes -e felismerni a mintát.

Előfordulhat, hogy nem látja szürkén a mintát (a fenti 2. lépésben látható módon). Ebben az esetben kövesse az alábbi lépéseket a Flash Fill eredmény eléréséhez:

1. Írja be a szöveget kézzel két cellába.
2. Válassza ki mindkét cellát
3. Vigye a kurzort a kijelölés jobb alsó sarkába. Észre fogja venni, hogy a kurzor plusz ikonra változik
4. Kattintson duplán rá (bal egérgomb). Ez kitölti az összes cellát. Ezen a ponton az eredmények valószínűleg helytelenek, és nem azt várják.
5. A kapott adatok jobb alsó sarkában egy kis automatikus kitöltés ikon látható. Kattintson erre az automatikus kitöltés ikonra
6. Kattintson a Flash Fill elemre

A fenti lépések megadják a Flash Fill eredményét (az általa levezetett minta alapján).

A Flash Fill használatával is megszerezheti a vezetéknevet vagy a középső nevet. Az első két cellába írja be a vezetéknevet (vagy a középső nevet), és a flash kitöltés képes lesz megérteni a mintát

A név átrendezése a Flash Fill használatával

A Flash Fill egy intelligens eszköz, és kissé bonyolult mintákat is képes megfejteni

Tegyük fel például, hogy rendelkezik adathalmazzal az alábbiak szerint, és át szeretné rendezni a nevet Rick Novakról Novak, Rick névre (ahol először a vezetéknév következik, majd vessző, majd a keresztnév).

Ennek lépései az alábbiakban találhatók:

1. A szomszédos cellába írja be manuálisan Novak, Rick
2. A második cellába írja be manuálisan Connor, Susan. Gépelés közben látni fogja, hogy a Flash Fill megmutatja a nevek listáját ugyanabban a formátumban (szürke).
3. Ha szürkén látja a neveket, gyorsan nézzen végig, és győződjön meg róla, hogy a megfelelő neveket mutatja. Ha ezek helyesek, nyomja meg az Enter billentyűt, és a Flash Fill automatikusan kitölti a többi cellát az azonos formátumú nevekkel.

Távolítsa el a középső nevet (vagy csak szerezze be a középső nevet)

A Flash Fill használatával is megszabadulhat a középső névtől, vagy csak a középső nevet kaphatja meg.

Tegyük fel például, hogy rendelkezik az alább látható adatkészlettel, és csak a kereszt- és vezetéknevet szeretné megkapni, a középső nevet nem.

Ennek lépései az alábbiakban találhatók:

1. A szomszédos cellába írja be manuálisan Rick Novak
2. A második cellába írja be manuálisan Susan Connor. Gépelés közben látni fogja, hogy a Flash Fill megmutatja a nevek listáját ugyanabban a formátumban (szürke).
3. Ha szürkén látja a neveket, gyorsan nézzen végig, és győződjön meg arról, hogy a megfelelő neveket mutatja. Ha ezek helyesek, 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.

Hasonlóképpen, ha csak a középső neveket szeretné megkapni, írja be a középső nevet az első két cellába, és használja a Flash Fill billentyűt a középső név lekéréséhez az összes többi névből.

Az oktatóanyagban bemutatott példák neveket használnak a szöveges adatok kezelése közben. Ugyanezeket a fogalmakat használhatja más adatformátumokkal is (például címek, terméknevek stb.)