Obstajajo lahko situacije, ko morate celice razdeliti v Excelu. To je lahko, ko podatke dobite iz zbirke podatkov ali jih kopirate iz interneta ali jih dobite od sodelavca.
Preprost primer, kjer morate celice v Excelu razdeliti, je, če imate polna imena in jih želite razdeliti na ime in priimek.
Ali pa dobite naslov 'in želite razdeliti naslov, tako da lahko ločeno analizirate mesta ali pin kodo.
Kako razdeliti celice v Excelu
V tej vadnici se boste naučili, kako razdeliti celice v Excelu z naslednjimi tehnikami:
- Uporaba funkcije Besedilo v stolpec.
- Uporaba besedilnih funkcij Excel.
- Uporaba Flash Fill (na voljo v letih 2013 in 2016).
Začnimo!
Razdelite celice v Excelu z uporabo besedila v stolpec
Spodaj imam seznam imen nekaterih mojih najljubših izmišljenih likov in jih želim razdeliti v ločene celice .:
Tu so koraki za razdelitev teh imen na ime in priimek:
- Izberite celice, v katerih imate besedilo, ki ga želite razdeliti (v tem primeru A2: A7).
- Kliknite zavihek Podatki
- V skupini »Podatkovna orodja« kliknite »Besedilo v stolpce«.
- V čarovniku za pretvorbo besedila v stolpce:
- 1. ali 3. korak čarovnika za besedilo v stolpce: Prepričajte se, da je izbrano Razmejeno (to je privzeta izbira). Tako boste lahko ločili ime in priimek na podlagi podanega ločevalnika (v tem primeru preslednica).
- Kliknite Naprej.
- Korak 2 od 3 Čarovnik za besedilo v stolpce: Kot preslednik izberite presledek in počistite vse ostalo. V razdelku Predogled podatkov v pogovornem oknu si lahko ogledate, kako bi izgledal vaš rezultat.
- Kliknite Naprej.
- Korak 3 od 3 Čarovnik za besedilo v stolpce: V tem koraku lahko določite obliko podatkov in kraj, kjer želite rezultat. Podatkovni format bom ohranil kot Splošno, saj moram deliti besedilne podatke. Privzeti cilj je A2 in če nadaljujete s tem, bo nadomestil prvotni nabor podatkov. Če želite ohraniti izvirne podatke nedotaknjene, izberite drugo celico kot cilj. V tem primeru je izbran B2.
- Kliknite Dokončaj.
- 1. ali 3. korak čarovnika za besedilo v stolpce: Prepričajte se, da je izbrano Razmejeno (to je privzeta izbira). Tako boste lahko ločili ime in priimek na podlagi podanega ločevalnika (v tem primeru preslednica).
Tako boste besedilo celice v trenutku razdelili v dva različna stolpca.
Opomba:
- Funkcija Besedilo v stolpec razdeli vsebino celic na podlagi razmejevalnika. Čeprav to dobro deluje, če želite ločiti ime in priimek, ga bo v primeru imena, priimka in priimka razdelil na tri dele.
- Rezultat, ki ga dobite z uporabo funkcije Besedilo v stolpec, je statičen. To pomeni, da morate v primeru kakršnih koli sprememb v izvirnih podatkih ponoviti postopek, da dobite posodobljene rezultate.
Razdelite celice v Excelu z uporabo besedilnih funkcij
Excel Text funkcije so odlične, če želite razrezati in narezati besedilne nize.
Medtem ko funkcija Text to Column daje statičen rezultat, je rezultat, ki ga dobite z uporabo funkcij, dinamičen in bi se samodejno posodobil, ko spremenite izvirne podatke.
Razdelitev imen z imenom in priimkom
Recimo, da imate iste podatke, kot je prikazano spodaj:
Izvlečenje imena
Če želite dobiti ime s tega seznama, uporabite naslednjo formulo:
= LEVO (A2, ISKANJE ("", A2) -1)
Ta formula bi zaznala prvi presledek in nato vrnila vse besedilo pred tem presledkom:
Ta formula uporablja funkcijo SEARCH za določitev položaja presledka. V primeru Brucea Wayna je vesoljski lik na 6. mestu. Nato s funkcijo LEVO izvleče vse znake levo od nje.
Izvlečenje priimka
Podobno za priimek uporabite naslednjo formulo:
= DESNO (A2, LEN (A2) -SEARCH ("", A2))
Ta formula uporablja funkcijo iskanja za iskanje položaja preslednice s funkcijo SEARCH. Nato to število odšteje od skupne dolžine imena (ki ga poda funkcija LEN). To daje število znakov v priimku.
Ta priimek se nato izvleče s funkcijo DESNO.
Opomba: Te funkcije morda ne bodo delovale dobro, če imate v imenih vodilne, zadnje ali dvojne presledke. Kliknite tukaj, če želite izvedeti, kako odstraniti začetne/zadnje/dvojne presledke v Excelu.
Razdelitev imen, ki imajo ime, srednje ime in priimek
Obstajajo lahko primeri, ko dobite kombinacijo imen, pri katerih imajo nekatera imena tudi srednje ime.
Formula v takih primerih je nekoliko zapletena.
Izvlečenje imena
Če želite dobiti ime:
= LEVO (A2, ISKANJE ("", A2) -1)
To je ista formula, ki smo jo uporabili, ko ni bilo srednjega imena. Preprosto poišče prvi presledek in vrne vse znake pred presledkom.
Izvlečenje srednjega imena
Če želite dobiti srednje ime:
= IFERROR (MID (A2, SEARCH ("", A2)+1, SEARCH ("", A2, SEARCH ("", A2) +1) -SEARCH ("", A2)), "")
Funkcija MID se začne od prvega presledka in izvleče srednje ime z uporabo razlike v položaju prvega in drugega presledka.
Če srednje ime ni, funkcija MID vrne napako. Da bi se izognili napaki, je zavita v funkcijo IFERROR.
Izvlečenje priimka
Za priimek uporabite naslednjo formulo:
= ČE (LEN (A2) -LEN (ZAMENIK (A2, "", "")) = 1, DESNO (A2, LEN (A2) -ISKALNIK ("", A2)), DESNO (A2, LEN (A2) -SEARCH ("", A2, SEARCH ("", A2) +1)))
Ta formula preveri, ali obstaja srednje ime ali ne (s štetjem števila presledkov). Če je samo 1 presledek, preprosto vrne vse besedilo desno od presledka.
Če pa sta 2, potem opazi drugi presledek in vrne število znakov po drugem presledku.
Opomba: Ta formula dobro deluje, če imate imena, ki imajo bodisi ime prvega in priimka bodisi ime, ime in priimek. Če pa imate mešanico, kjer imate pripone ali pozdrave, boste morali formule še spremeniti.
Razdelite celice v Excelu z uporabo Flash Fill
Flash Fill je nova funkcija, uvedena v Excelu 2013.
Lahko bi bilo zelo priročno, če imate vzorec in želite hitro izvleči njegov del.
Vzemimo na primer podatke o imenu in priimku:
Flash fill deluje tako, da identificira vzorce in jih podvoji za vse druge celice.
Tako lahko s seznama Flash Fill izvlečete ime s seznama:
- V celico B2 vnesite ime Brucea Wayna (to je Bruce).
- Ko je celica izbrana, boste na desnem koncu izbire celice opazili majhen kvadrat. Dvokliknite nanjo. To bo zapolnilo isto ime v vseh celicah.
- Ko so celice napolnjene, boste spodaj desno videli ikono možnosti samodejnega izpolnjevanja. Kliknite nanjo.
- Na seznamu izberite Flash Fill.
- Takoj, ko izberete Flash Fill, boste opazili, da se vse celice posodobijo in zdaj prikazujejo ime za vsako ime.
Kako deluje Flash Fill?
Flash Fill išče vzorce v naboru podatkov in jih ponavlja.
Flash Fill je presenetljivo pametna funkcija in v večini primerov deluje po pričakovanjih. Toda tudi v nekaterih primerih ne uspe.
Na primer, če imam seznam imen, ki ima kombinacijo imen, nekatera imajo srednje ime, druga pa ne.
Če v takem primeru izvlečem srednje ime, bo Flash Fill pomotoma vrnil priimek, če ni imena.
Če sem iskren, je to še vedno dober približek trendu. Vendar pa ni tisto, kar sem hotel.
Je pa še vedno dovolj dobro orodje, ki ga lahko obdržite v svojem arzenalu in ga uporabite, kadar se pojavi potreba.
Tu je še en primer, ko Flash Fill odlično deluje.
Imam nabor naslovov, s katerih želim hitro izvleči mesto.
Če želite hitro pridobiti mesto, vnesite ime mesta za prvi naslov (v tem primeru vnesite London v celico B2) in s samodejnim izpolnjevanjem zapolnite vse celice. Zdaj uporabite Flash Fill in takoj vam bo iz vsakega naslova dal ime mesta.
Podobno lahko razdelite naslov in izvlečete kateri koli del naslova.
Upoštevajte, da bi moral biti naslov homogen podatkovni niz z istim ločilnikom (v tem primeru vejica).
Če poskusite uporabiti Flash Fill, ko ni vzorca, se prikaže napaka, kot je prikazano spodaj:
V tej vadnici sem obravnaval tri različne načine razdelitve celic v Excelu na več stolpcev (z uporabo besedila v stolpce, formul in Flash Fill)
Upam, da vam je bila ta vadnica v Excelu uporabna.
