V nekaterih primerih morate celice v Excelu razdeliti.To so lahko podatki iz baze podatkov ali kopiranje podatkov iz interneta ali pridobivanje podatkov od kolega.
Preprost primer, ko morate celice v Excelu razdeliti, je, ko imate polna imena in jih želite razdeliti na imena in priimke.
Ali pa dobite naslov" in želite naslov razdeliti, tako da lahko ločeno analizirate mesto ali geslo.
Kako razdeliti celice v Excelu
V tej vadnici se boste naučili, kako razdeliti celice v Excelu z naslednjimi tehnikami:
- Uporabite funkcijo Besedilo v stolpce.
- Uporabite besedilne funkcije Excela.
- Uporabite Flash Fill (na voljo v letih 2013 in 2016).
Začnimo!
Razdelite celice v Excelu z besedilom v stolpec
Spodaj sem navedel imena nekaterih mojih najljubših izmišljenih likov, ki bi jih rad ločil v ločene celice. :
Tukaj so koraki za razdelitev teh imen na imena in priimke:
- Izberite celico, ki vsebuje besedilo, ki ga želite razdeliti (v tem primeru A2:A7).
- Kliknite zavihek Podatki
- V skupini Podatkovna orodja kliknite Besedilo v stolpce.
- V čarovniku za pretvarjanje besedila v stolpce:
To bo takoj razdelilo besedilo celice v dva različna stolpca.
opombe:
- Funkcija Besedilo v stolpce razdeli vsebino celic na podlagi ločil.Čeprav to dobro deluje, če želite ločiti imena in priimke, bo v primeru imena, srednjega imena in priimka razdeljeno na tri dele.
- Rezultat, pridobljen s funkcijo Besedilo v stolpec, je statičen.To pomeni, da boste morali, če pride do kakršnih koli sprememb v prvotnih podatkih, postopek ponoviti, da dobite posodobljene rezultate.
Razdelite celice v Excelu z besedilnimi funkcijami
Excelove besedilne funkcije so uporabne, ko želite razrezati besedilne nize na kocke.
Medtem ko funkcija Besedilo v stolpec zagotavlja statične rezultate, so rezultati, dobljeni s funkcijo, dinamični in se samodejno posodabljajo, ko spremenite izvirne podatke.
Razdelite ime z imenom in priimkom
Recimo, da imate enake podatke, kot je ta:
ime izvlečka
Če želite dobiti imena s tega seznama, uporabite naslednjo formulo:
=LEVO(A2,ISKANJE(" ",A2)-1)
Ta formula bo poiskala prvi presledek in nato vrnila vse besedilo pred tem presledkom:
Ta formula uporablja funkcijo SEARCH za pridobitev položaja presledka.V primeru Brucea Wayna je vesoljski lik na 6. mestu.Nato s funkcijo LEFT izvleče vse znake levo od nje.
Izvlecite priimek
Podobno za pridobitev priimka uporabite naslednjo formulo:
=DESNO(A2,LEN(A2)-ISKANJE(" ",A2))
Ta formula uporablja funkcijo SEARCH za iskanje lokacije preslednice s funkcijo SEARCH.Nato to število odšteje od celotne dolžine imena (ki jo poda funkcija LEN).To daje število znakov v priimku.
Nato uporabite funkcijo DESNO, da izvlečete ta priimek.
Opomba:Te funkcije morda ne bodo delovale pravilno, če so v imenu začetni, zadnji ali dvojni presledki.Kliknite tukaj, če želite izvedeti, kako odstraniti vodilne/končne/dvojne presledke v Excelu.
Razdelite imena z imenom, srednjim imenom in priimkom
V nekaterih primerih lahko na koncu dobite kombinacijo imen, od katerih imajo nekatera tudi vmesna imena.
Formula v tem primeru je nekoliko zapletena.
ime izvlečka
Če želite dobiti ime:
=LEVO(A2,ISKANJE(" ",A2)-1)
To je ista formula, ki jo uporabljamo brez srednjega imena.Samo poišče prvi presledek in vrne vse znake pred presledkom.
izvleči srednje ime
Če želite dobiti srednje ime:
=ČE NAPAKA(MID(A2,ISKANJE(" ",A2)+1,ISKANJE(" ",A2,ISKANJE(" ",A2)+1)-ISKANJE(" ",A2)),"")
Funkcija MID se začne s prvim presledkom in uporablja razliko v položaju prvega in drugega presledka za ekstrakcijo srednjega imena.
Če srednjega imena ni, funkcija MID vrne napako.Da bi se izognili napakam, je zavit v funkcijo IFERROR.
Izvlecite priimek
Če želite dobiti priimek, uporabite naslednjo formulo:
=IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1,RIGHT(A2,LEN(A2)-SEARCH(" ",A2)),RIGHT(A2,LEN(A2) -SEARCH(" ",A2,SEARCH(" ",A2)+1)))
Ta formula preveri srednje ime (s štetjem presledkov).Če je samo 1 presledek, vrne vse besedilo desno od presledka.
Če pa sta 2, potem poišče drugi presledek in vrne število znakov za drugim presledkom.
Opomba: Te formule dobro delujejo, če imate samo ime in priimek ali ime, srednje in priimek.Če pa imate kombinacijo priponk ali pozdravov, boste morali formulo dodatno spremeniti.
Razdelite celice v Excelu s QuickFill
Flash Fill je nova funkcija, predstavljena v Excelu 2013.
To je lahko zelo priročno, če imate vzorec in želite hitro izvleči njegove dele.
Na primer, dobimo podatke o imenu in priimku:
Hitro polnjenje deluje tako, da prepozna vzorec in ga kopira v vse druge celice.
Takole uporabite Flash Fill za ekstrakcijo imen s seznama:
Kako deluje Flash Fill?
Flash Fill poišče vzorec v naboru podatkov in ga kopira.
Flash Fill je presenetljivo pametna funkcija, ki v večini primerov deluje dobro.Toda v nekaterih primerih tudi ne uspe.
Na primer, če imam seznam imen s kombinacijami imen, imajo nekatera vmesna imena, druga pa ne.
Če v tem primeru izvlečem srednje ime, bo Flash Fill napačno vrnil priimek, če ni imena.
Iskreno povedano, to je še vedno dober približek trenda.Vendar to ni tisto, kar želim.
Vendar je še vedno dovolj dobro orodje, da ga hranite v svojem arzenalu in ga uporabite, ko je to potrebno.
Tukaj je še en primer hitrega polnjenja, ki deluje odlično.
Imam nabor naslovov, iz katerih želim hitro izluščiti mesta.
Če želite hitro dobiti mesto, vnesite ime mesta prvega naslova (v tem primeru London v celici B2) in uporabite samodokončanje, da izpolnite vse celice.Zdaj uporabite Flash Fill in takoj vam bo dal ime mesta za vsak naslov.
Prav tako lahko razdelite naslov in izvlečete kateri koli del naslova.
Upoštevajte, da to zahteva, da so naslovi homogen nabor podatkov z enakim ločilom (v tem primeru vejica).
Če poskusite uporabiti Flash Fill brez vzorca, se prikaže napaka, kot je ta:
V tej vadnici sem obravnaval tri različne načine za razdelitev celic v Excelu na več stolpcev (z uporabo besedila v stolpce, formule in hitrega polnjenja)
Upam, da se vam je ta vadnica za Excel zdela koristna.
