A cellák felosztása az Excelben (több oszlopra bontva)

Előfordulhatnak olyan helyzetek, amikor az Excelben fel kell osztania a cellákat. Ez akkor lehet, ha az adatokat adatbázisból szerezi be, vagy az internetről másolja, vagy kollégájától szerezi be.

Egy egyszerű példa, ahol fel kell osztania a cellákat az Excelben, amikor teljes nevekkel rendelkezik, és ezeket keresztnévre és vezetéknévre szeretné osztani.

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

Hogyan lehet felosztani a cellákat az Excelben

Ebben az oktatóanyagban megtudhatja, hogyan oszthatja fel a cellákat Excelben a következő technikákkal:

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

Kezdjük!

A cellák felosztása Excelben a Szöveg oszlopba használatával

Az alábbiakban felsorolom néhány kedvenc kitalált karakterem nevét, és ezeket a neveket külön cellákra akarom osztani:

A következő lépésekkel oszthatja fel ezeket a neveket kereszt- és vezetéknévre:

• Válassza ki azokat a cellákat, amelyekben a felosztani kívánt szöveg található (ebben az esetben A2: A7).
• Kattintson az Adatok fülre
• Az „Adateszközök” csoportban kattintson a „Szöveg oszlopokba” lehetőségre.
• A Szöveg konvertálása oszlopokba varázslóban:
  • A Szöveg oszlopokba varázsló 1/3. Lépése: Győződjön meg arról, hogy a Elhatárolt elem van kiválasztva (ez az alapértelmezett beállítás). Ez lehetővé teszi, hogy a kereszt- és a vezetéknevet egy meghatározott elválasztó (ebben az esetben szóköz) alapján választhassa el.
  • Kattintson a Tovább gombra.
  • 2. lépés: 3 Szöveg oszlopokba varázsló: Válassza a Szóközet elválasztóként, és törölje a kijelölést. Az eredmény megjelenését a párbeszédpanel Adatok előnézete szakaszában tekintheti meg.
  • Kattintson a Tovább gombra.
  • 3. lépés: 3 Szöveg oszlopokba varázsló: Ebben a lépésben megadhatja az adatformátumot és azt, hogy hol szeretné az eredményt. Az adatformátumot általánosnak fogom tartani, mivel fel kell osztani a szöveges adatokat. Az alapértelmezett célállomás az A2, és ha ezzel folytatja, akkor lecseréli az eredeti adatkészletet. Ha meg szeretné őrizni az eredeti adatokat, válasszon másik cellát célállomáské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.

Jegyzet:

• A Szöveg oszlopba funkció felosztja a cellák tartalmát a határoló alapján. Bár ez jól működik, ha el akarja különíteni a keresztnevet és a vezetéknevet, a kereszt-, közép- és vezetéknév esetében három részre osztja.
• A Szöveg oszlopba funkció használatá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, hogy frissített eredményeket kapjon.

A cellák felosztása Excelben szöveges függvények használatával

Az Excel szöveg funkciók nagyszerűek, ha szeletelni és kockázni szeretné 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 megváltoztatja az eredeti adatokat.

A kereszt- és vezetéknévvel rendelkező nevek felosztása

Tegyük fel, hogy az alábbi adatokkal rendelkezik:

A keresztnév kibontása

A keresztnév lekéréséhez a következő képletet használja:

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

Ez a képlet észleli az első szóközt, majd visszaadja az összes szöveget a szóköz előtt:

Ez a képlet a SEARCH függvényt használja a szóköz karakter pozíciójának meghatározásához. Bruce Wayne esetében a space karakter a 6. pozícióban van. Ezután a bal oldali karaktereket kivonja a LEFT funkció használatával.

A vezetéknév kibontása

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

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

Ez a képlet a keresőfunkció segítségével találja meg a szóköz pozícióját a SEARCH funkció segítségével. Ezután kivonja ezt a számot a név teljes hosszából (amelyet a LEN függvény ad meg). Ez adja meg a vezetéknév karaktereinek számát.

Ezt a vezetéknevet ezután a JOBB funkcióval vonják ki.

Jegyzet: Előfordulhat, hogy ezek a funkciók nem működnek jól, ha a nevekben előtag, zárójel vagy kettős szóköz található. Kattintson ide, hogy megtudja, hogyan távolíthatja el a vezető/záró/kettős szóközöket az Excelben.

A kereszt-, közép- és vezetéknévvel rendelkező nevek felosztása

Előfordulhatnak olyan esetek, amikor olyan névkombinációt kap, ahol néhány névnek középső neve is van.

A képlet ilyen esetekben kissé bonyolult.

A keresztnév kibontása

A keresztnév megszerzéséhez:

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

Ugyanezt a képletet használtuk, 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:

= HIBÁS (MID (A2, SEARCH ("", A2)+1, SEARCH ("", A2, SEARCH ("", A2) +1) -SEARCH ("", A2)), "")

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

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

A vezetéknév kibontása

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

= HA (LEN (A2) -LEN (Helyettesítő (A2, "", "")) = 1, JOBB (A2, LEN (A2) -KERESÉS ("", A2)), JOBB (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 számításával). Ha csak 1 szóköz van, akkor egyszerűen visszaadja az összes szöveget a szóköz karaktertől jobbra.

De ha 2 van, akkor a második szóköz karaktert észleli, és a második szóköz után adja vissza a karakterek számát.

Megjegyzés: Ez a képlet akkor működik jól, ha olyan nevekkel rendelkezik, amelyeknek csak az ököl- és vezetékneve, vagy a kereszt-, közép- és vezetékneve van. Ha azonban olyan keveréke van, ahol utótagok vagy üdvözlések vannak, akkor tovább kell módosítania a képleteket.

A cellák felosztása Excelben a Flash Fill használatával

A Flash Fill az Excel 2013 új funkciója.

Nagyon hasznos lehet, ha van egy minta, és gyorsan ki akarja szedni annak egy részét.

Vegyük például a kereszt- és vezetéknév -adatokat:

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

A Flash Fill használatával így veheti ki a keresztnevet a listából:

• A B2 cellába írja be Bruce Wayne keresztnevét (azaz Bruce).
• A cella kiválasztásával egy kis négyzetet fog látni a cellakiválasztás jobb végén. Kattintson duplán rá. Ez ugyanazt a nevet tölti ki az összes cellában.
• Amikor a cellák megteltek, a jobb alsó sarokban megjelenik az Automatikus kitöltés beállításai ikon. Kattintson rá.
• Válassza a Flash Fill lehetőséget a listából.
• Amint kiválasztja a Flash Fill lehetőséget, észre fogja venni, hogy az összes cella frissíti magát, és most minden névnél megjeleníti a keresztnevet.

Hogyan működik a Flash Fill?

A Flash Fill megkeresi a mintákat az adatkészletben, és megismétli a mintát.

A Flash Fill meglepően okos funkció, és a legtöbb esetben a várt módon működik. De bizonyos esetekben ez is kudarcot vall.

Például, ha van egy névlistám, amely kombinálja a neveket, néhánynak középső neve van, néhánynak nincs.

Ha ilyenkor kibontom 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. Azonban nem ezt akartam.

De még mindig elég jó eszköz az arzenálban tartásához és használatához, amikor csak szükség van rá.

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

Van egy címcsoportom, ahonnan gyorsan ki akarom vonni a várost.

A város gyors eléréséhez írja be a város nevét az első címhez (ebben a példában írja be Londonba a B2 cellát), és az automatikus kitöltéssel töltse ki az összes cellát. Most használja a Flash Fill funkciót, és azonnal megadja a város nevét minden címről.

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

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

Ha megpróbálja használni a Flash Fill -et, ha nincs minta, akkor az alábbi módon jelenik meg a hiba:

Ebben az oktatóanyagban három különböző módszert ismertettem az Excel celláinak több oszlopra történő felosztására (a Szöveg oszlopokba, képletek és a Flash Kitöltés használatával)

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