Bizonyos esetekben fel kell osztania a cellákat az Excelben.Ez lehet az, hogy adatokat szerez be egy adatbázisból, adatokat másol az internetről, vagy adatokat kap egy kollégától.
Egy egyszerű példa arra, amikor az Excelben cellákat kell felosztani, amikor teljes nevei vannak, és fel szeretné osztani őket kereszt- és vezetéknévre.
Vagy megkapja a címet, és fel akarja osztani a címet, hogy külön elemezhesse a várost vagy a jelszót.
Cellák felosztása az Excelben
Ebből az oktatóanyagból megtudhatja, hogyan oszthat fel cellákat az Excelben a következő technikák használatával:
- Használja a Szöveg oszlopokká funkciót.
- Használja az Excel szöveges függvényeit.
- A Flash Fill használata (2013-ban és 2016-ban érhető el).
Fogjunk hozzá!
Cellák felosztása az Excelben szöveggel oszlopra
Az alábbiakban felsoroltam néhány kedvenc kitalált karakterem nevét, akiket külön cellákba szeretnék szétválasztani. :
Íme a lépések a nevek kereszt- és vezetéknevekre való felosztásához:
- Jelölje ki a felosztani kívánt szöveget tartalmazó cellát (jelen esetben A2:A7).
- Kattintson az Adatok fülre
- Az Adateszközök csoportban kattintson a Szöveg oszlopokba elemre.
- A Szöveg átalakítása oszlopokká varázslóban:
Ez azonnal felosztja a cella szövegét két különböző oszlopra.
megjegyzések:
- A Szöveg oszlopokká funkció a cellák tartalmát határolók alapján osztja fel.Bár ez jól működik, ha el szeretné választani a vezeték- és utóneveket, a kereszt-, közép- és vezetéknevek esetében három részre osztja.
- A Szöveg oszlopba funkcióval kapott eredmény statikus.Ez azt jelenti, hogy ha bármilyen változás történik az eredeti adatokban, meg kell ismételnie a folyamatot a frissített eredmények eléréséhez.
Cellák felosztása az Excelben szöveges függvényekkel
Az Excel szöveges funkciói akkor hasznosak, ha szöveges karakterláncokat szeretne feldarabolni.
Míg a Szöveg oszlopba funkció statikus eredményeket biztosít, a funkció használatával kapott eredmények dinamikusak, és automatikusan frissülnek, amikor módosítja az eredeti adatokat.
A név felosztása vezeték- és utónévvel
Tegyük fel, hogy ugyanazokkal az adatokkal rendelkezik:
kivonat név
A listából a keresztnevek lekéréséhez használja a következő képletet:
=BAL(A2;KERESÉS(" ",A2)-1)
Ez a képlet megkeresi az első szóköz karaktert, majd visszaadja a szóköz előtti összes szöveget:
Ez a képlet a SEARCH függvényt használja a szóköz karakter pozíciójának megállapításához.Bruce Wayne példájában az űrkarakter a 6. helyen áll.Ezután a LEFT függvény segítségével kivonja a tőle balra lévő összes karaktert.
Kivonat a vezetéknévből
Hasonlóképpen, a vezetéknév meghatározásához használja a következő képletet:
=JOBBRA(A2,LEN(A2)-KERESÉS(" ",A2))
Ez a képlet a SEARCH függvényt használja a szóköz helyének megtalálásához a KERESÉS funkció segítségével.Ezután levonja ezt a számot a név teljes hosszából (amelyet a LEN függvény adja meg).Ez adja meg a vezetéknév karaktereinek számát.
Ezután használja a JOBB funkciót a vezetéknév kivonásához.
Megjegyzés:Előfordulhat, hogy ezek a függvények nem működnek megfelelően, ha a névben kezdő, záró vagy kettős szóköz van.Ide kattintva megtudhatja, hogyan távolíthatja el a kezdő/záró/dupla szóközöket az Excelben.
A nevek felosztása kereszt-, közép- és vezetéknévvel
Egyes esetekben előfordulhat, hogy nevek kombinációját kapja, amelyek közül néhánynak van középső neve is.
A képlet ebben az esetben egy kicsit bonyolult.
kivonat név
A név megszerzéséhez:
=BAL(A2;KERESÉS(" ",A2)-1)
Ez ugyanaz a képlet, amelyet középső név nélkül használunk.Csak az első szóköz karaktert keresi, és visszaadja az összes karaktert a szóköz előtt.
kivonat a középső névből
A középső név megszerzéséhez:
=HIBA(KÖZÉP(A2,KERESÉS(" ",A2)+1,KERESÉS(" ",A2,KERESÉS(" ",A2)+1)-KERESÉS(" ",A2)),"")
A MID függvény az első szóköz karakterrel kezdődik, és az első és a második szóköz karakter pozíciójának különbségét használja fel a középső név kivonásához.
Ha nincs középső név, a MID függvény hibát ad vissza.A hibák elkerülése érdekében az IFERROR függvénybe van csomagolva.
Kivonat a vezetéknévből
A vezetéknév meghatározásához használja a következő 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 a középső nevet (a szóköz karakterek számának megszámlálásával).Ha csak 1 szóköz van, akkor az összes szöveget a szóköztől jobbra adja vissza.
De ha 2 van, akkor megkeresi a második szóközt, és visszaadja a második szóköz utáni karakterek számát.
Megjegyzés: Ezek a képletek jól működnek, ha csak kereszt- és vezetéknév, vagy kereszt-, közép- és vezetéknév van.Ha azonban utótagok vagy üdvözlések kombinációja van, akkor tovább kell módosítania a képletet.
Cellák felosztása az Excelben a QuickFill segítségével
A Flash Fill egy új funkció, amelyet az Excel 2013-ban vezettek be.
Nagyon hasznos lehet, ha van egy mintája, és gyorsan ki akarja húzni annak részeit.
Például vegyünk kereszt- és vezetéknévadatokat:
A Gyorskitöltés úgy működik, hogy felismer egy mintát, és átmásolja az összes többi cellába.
A következőképpen használhatja a Flash Fill-t a nevek listából való kiemeléséhez:
Hogyan működik a Flash Fill?
A Flash Fill megtalál egy mintát az adatkészletben, és lemásolja azt.
A Flash Fill meglepően intelligens funkció, amely a legtöbb esetben jól működik.De bizonyos esetekben ez is meghiúsul.
Például, ha van egy névlistám névkombinációkkal, akkor egyeseknek van középső neve, másoknak nincs.
Ha ebben az esetben kivonom a középső nevet, a Flash Fill hibásan adja vissza a vezetéknevet, ha nincs keresztnév.
Őszintén szólva ez még mindig jó közelítés a trendhez.Én azonban nem ezt akarom.
De még mindig elég jó eszköz ahhoz, hogy a fegyvertárban tartsd, és szükség esetén használd.
Íme egy másik példa a nagyszerűen működő gyors kitöltésre.
Van egy sor címem, ahonnan gyorsan ki akarok gyűjteni városokat.
A város gyors megkereséséhez írja be az első cím városnevét (ebben a példában London a B2 cellában), és használja az automatikus kiegészítést az összes cella kitöltéséhez.Most használja a Flash Fill-t, és azonnal megadja a város nevét minden címhez.
Hasonlóképpen feloszthatja a címet, és kibonthatja a cím bármely részét.
Vegye figyelembe, hogy ehhez a címeknek homogén adathalmazoknak kell lenniük, ugyanazzal az elválasztójellel (ebben az esetben vesszővel).
Ha a Flash Fill-t minta nélkül próbálja használni, az ehhez hasonló hibát fog jelezni:
Ebben az oktatóanyagban három különböző módszerrel foglalkoztam, hogyan oszthat fel cellákat az Excelben több oszlopra (a Szöveg oszlopokká, Képletekkel és Gyors kitöltéssel)
Remélem, hasznosnak találta ezt az Excel oktatóanyagot.
