Előfordulhat, hogy cellákat kell felosztani az Excelben. Ilyen lehet, ha az adatokat adatbázisból szerzi be, vagy az internetről másolja, vagy egy kollégától kapja.

Egyszerű példa arra, hogy az Excelben fel kell osztani a cellákat, ha teljes nevei vannak és ezeket fel akarja osztani keresztnévre és vezetéknévre.

Vagy megkapja a címet ‘, és fel akarja osztani a címet, hogy külön elemezhesse a városokat vagy a PIN-kódot.

A cellák felosztása az Excelben

Ebben az oktatóanyagban megtudhatja, hogyan kell felosztani cellák az Excel-ben a következő technikákkal:

• A Szöveg oszlopba funkció használata.
• Excel szövegfüggvények használata.
• A Flash Fill használata (elérhető 2013 és 2016).

Kezdjük!

Ossza fel a cellákat az Excelben a Szöveg oszlop használatával

Az alábbiakban felsorolok néhányat néhány névről a kedvenc kitalált karaktereim közül, és ezeket a neveket külön cellákra szeretném osztani:

Az alábbi lépések megtörténnek t ezeket a neveket az utónévre és a vezetéknévre:

• Jelölje ki azokat a cellákat, amelyekben fel van osztva a szöveg (ebben az esetben A2: A7).
• Kattintson az Adatok fülre
• Az „Adateszközök” csoportban kattintson a „Szöveg oszlopokba” elemre.
• a Szöveg konvertálása oszlopokká varázsló:
  • A Szövegből oszlopokba varázsló 3/3-as lépése: Győződjön meg arról, hogy a Leválasztott van kiválasztva (ez az alapértelmezett választás). Ez lehetővé tenné az utónév és a vezetéknév elválasztását egy megadott elválasztó alapján (ebben az esetben szóköz).
  • Kattintson a Tovább gombra.
  • 3/3-as lépés: Szöveg az oszlopokhoz varázsló: Válassza a szóközt elválasztónak, és törölje az összes többi jelölését. A párbeszédpanel Adatok előnézete szakaszában láthatja, hogyan néz ki az eredménye.
  • Kattintson a Tovább gombra.
  • Lépés 3/3 Szöveg oszlopokba varázsló: Ebben a lépésben megadhatja az adatformátumot és az eredményt. Az adatformátumot általánosként fogom megtartani, mivel szöveges adataim vannak felosztásra. Az alapértelmezett rendeltetési hely A2, és ha ezzel folytatja, akkor az az eredeti adathalmaz helyébe lép. Ha az eredeti adatokat sértetlenül kívánja megőrizni, jelöljön ki egy másik cellát célként. Ebben az esetben a B2 van kiválasztva.
  • Kattintson a Befejezés gombra.

Ez azonnal felosztja a cella szövegét két különböző oszlopra.

Megjegyzés:

• A Szöveg oszlopba funkció felosztja a cellák tartalmát az elválasztó alapján. Bár ez jól működik, ha el akarja különíteni a keresztnevet és a vezetéknevet, az első, a középső és a vezetéknév esetében három részre osztja.
• A A szöveg az oszlopba funkció statikus. Ez azt jelenti, hogy ha bármilyen változás történik az eredeti adatokban, akkor meg kell ismételnie a folyamatot a friss eredmények eléréséhez.

Cellák felosztása az Excelben a Szöveges függvények használatával

Az Excel szövegfüggvények nagyszerűek, ha szeletelni és felvenni a szöveges karakterláncokat.

Míg a Szöveg oszlopba funkció statikus eredményt ad, a függvények használatával kapott eredmény dinamikus, és automatikusan frissül, amikor változtassa meg az eredeti adatot.

Keresztnévvel és vezetéknévvel rendelkező nevek felosztása

Tegyük fel, hogy ugyanazokkal az adatokkal rendelkezik, mint az alábbiak:

A keresztnév kibontása

A keresztnév megszerzéséhez a listából használja a következő képletet:

=LEFT(A2,SEARCH(" ",A2)-1)

Ez a képlet észreveszi az első szóköz karaktert, majd az egész szöveget visszaadja az adott szóköz előtt:

Ez a képlet a KERESÉS funkcióval kapja meg a szóköz karakter pozícióját. Bruce Wayne esetében az űrkarakter a 6. pozícióban van. Ezután a bal oldali karaktereket kivonja a BAL funkció segítségével.

A vezetéknév kivonása

Hasonlóképpen a vezetéknév megszerzéséhez használja a következő képletet:

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

Ez a képlet a keresési funkcióval keresi meg a szóköz helyzetét a a KERESÉS funkció. Ezután kivonja ezt a számot a név teljes hosszából (amelyet a LEN függvény ad meg). Ez megadja a vezetéknévben szereplő karakterek számát.

Ez a vezetéknév a RIGHT függvény használatával kerül kibontásra.

Megjegyzés: Előfordulhat, hogy ezek a függvények nem működnek jól, ha Ön vezet, záró vagy kettős szóköz a nevekben. Ide kattintva megtudhatja, hogyan távolíthatja el a vezető / záró / dupla szóközöket az Excel-ben.

Nevek felosztása, amelyek kereszt- és középnévvel és vezetéknévvel rendelkeznek

Előfordulhat, hogy kapjon egy olyan névkombinációt, ahol egyes neveknek középső neve is van.

Ilyen esetekben a képlet kissé összetett.

Az utónév kibontása

Az utónév megszerzéséhez:

=LEFT(A2,SEARCH(" ",A2)-1)

Ez ugyanaz a képlet, amelyet akkor használtunk, amikor nem volt középső név. Egyszerűen megkeresi az első szóköz karaktert, és az összes karaktert visszaadja a szóköz előtt.

A középső név kibontása

A középső név megszerzése:

=IFERROR(MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)),"")

A MID függvény az első szóköz karaktertől indul, és az első és a második szóköz karakter különbségének felhasználásával kivonja a középső nevet.

Esetekben nincs középső név, a MID függvény hibát ad vissza. A hiba elkerülése érdekében az IFERROR függvénybe van csomagolva.

A vezetéknév kivonása

A vezetéknév megszerzéséhez használja az alábbi képletet:

=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2)+1)))

Ez a képlet ellenőrzi, hogy van-e középső név, vagy sem (a szóközök számának megszámolásával). Ha csak 1 szóköz van, akkor az egész szöveget egyszerűen a szóköztől jobbra adja vissza.

Ha azonban 2 van, akkor a második szóköz karaktert kiszúrja, és a karakterek számát adja vissza a második szóköz.

Megjegyzés: Ezek a képletek jól működnek, ha olyan nevek vannak, amelyeknek vagy az ököl és a vezetéknév, vagy az első, a középső és a vezetéknév szerepelnek. Ha azonban van olyan keveréke, ahol utótagjai vagy üdvözletei vannak, akkor tovább kell módosítania a képleteket.

Cellák felosztása az Excelben a Flash Fill használatával

A Flash Fill egy az Excel 2013-ban bevezetett új funkció.

Nagyon hasznos lehet, ha van mintád, és gyorsan ki akarod vonni annak egy részét.

Vegyük például az utónevet és a vezetéknév adatai:

A Flash kitöltés úgy működik, hogy azonosítja a mintákat és megismétli az összes többi cellát.

Így tudja kinyerni az első nevet a listából a Flash Fill használatával:

• A B2 cellába írja be Bruce Wayne (azaz Bruce) keresztnevét.
• A kiválasztott cellával egy kis négyzetet észlel a cellaválasztás jobb végén. Kattintson duplán rá. Ez ugyanazt a nevet fogja kitölteni az összes cellában.
• A cellák kitöltésekor a jobb alsó sarokban megjelenik az Automatikus kitöltés beállításai ikon. Kattintson rá.
• Válassza ki a Flash Fill elemet a listából.
• Mint amint kiválasztja a Flash Fill elemet, észreveszi, hogy az összes cella frissíti önmagát, és mostantól minden névnél megjelenik a keresztnév.

Hogyan működik a Flash Fill? A Flash Fill megkeresi az adatkészlet mintáit, és megismétli a mintát.

A Flash Fill meglepően okos szolgáltatás, és a legtöbb esetben az elvárásoknak megfelelően működik. De bizonyos esetekben ez is kudarcot vall.

Például, ha van egy névjegyzékem, amely nevek kombinációját tartalmazza némelyiknek középső neve van, és némelyiknek nincs.

Ha ilyen esetben kibontom a középső nevet, a Flash Fill hibásan adja vissza a vezetéknevet, ha nincs keresztnév.

Hogy őszinte legyek, ez még mindig jó közelítés a trendhez. Ez azonban nem az, amit szerettem volna.

De ez még mindig elég jó eszköz ahhoz, hogy az arzenálban maradjon, és bármikor használhassa.

Itt van egy másik példa, ahol a Flash Fill remekül működik.

Van egy címkészletem, amelyről gyorsan ki akarom vonni a várost.

A város gyors megszerzéséhez írja be az első cím városnevét (írja be Londonot a B2 cellába ebben a példában), és az automatikus kitöltés segítségével töltse ki az összes cellát. Most használja a Flash Fill alkalmazást, és azonnal megadja a város nevét az egyes címekből.

Hasonlóképpen feloszthatja a címet, és kibonthatja a cím bármely részét.

Vegye figyelembe, hogy ez a címnek homogén adathalmaznak kell lennie, ugyanazzal a határolóval (ebben az esetben vesszővel).

Ha a Flash Fill használatát próbálja használni, ha nincs minta, akkor az az alábbiak szerint hibát jelenít meg az alábbiakban:

Ebben az oktatóanyagban három különböző módszert ismertettem az Excel celláinak több oszlopra osztására (a Text to Oszlopok, képletek és Flash Fill)

Remélem, hogy hasznosnak találta ezt az Excel oktatóanyagot.

Ezeknek az Excel oktatóanyagoknak is tetszhet:

• Hogyan a cellák gyors kombinálásához az Excelben.
• Hogyan vonhatunk ki egy alstringet az Excelben a képletek segítségével.
• Hogyan lehet megszámolni a szöveges karakterláncokat tartalmazó cellákat.
• Felhasználónévek kivonása E-mail azonosítók az Excel programban.
• Hogyan oszthassunk fel egy cellában több sort külön cellákra / oszlopokra.