Lahtrite jagamine Excelis (mitmeks veeruks eraldamiseks)

Võib juhtuda olukordi, kus peate Excelis lahtreid jagama. Need võivad olla siis, kui saate andmed andmebaasist või kopeerite need Internetist või saate need kolleegilt.

Lihtne näide, kus peate Excelis lahtrid jagama, on see, kui teil on täisnimed ja soovite need jagada ees- ja perekonnanimeks.

Või saate aadressi ja soovite aadressi jagada, et saaksite linnu või PIN -koodi eraldi analüüsida.

Lahtrite jagamine Excelis

Selles õpetuses saate teada, kuidas Excelis lahtreid jagada järgmiste tehnikate abil:

• Teksti veergu funktsiooni kasutamine.
• Exceli tekstifunktsioonide kasutamine.
• Flash Filli kasutamine (saadaval 2013. ja 2016. aastal).

Alustagem!

Lahtrite jagamine Excelis teksti abil veergu

Allpool on mul nimekiri mõne minu lemmik väljamõeldud tegelase nimedest ja ma tahan jagada need nimed eraldi lahtritesse:

Nende nimede jagamiseks eesnimeks ja perekonnanimeks toimige järgmiselt.

• Valige lahtrid, milles on tekst, mille soovite jagada (antud juhul A2: A7).
• Klõpsake vahekaarti Andmed
• Klõpsake rühmas „Andmetööriistad” nuppu „Tekst veergudele”.
• Teksti veergude teisendamise viisardis tehke järgmist.
  • Teksti veergudesse viisardi samm 1/3: Veenduge, et oleks valitud Eraldatud (see on vaikimisi valitud). See võimaldab teil eraldada eesnime ja perekonnanime määratud eraldaja (antud juhul tühik) abil.
  • Klõpsake nuppu Edasi.
  • Samm 2/3 Tekst veergudesse viisard: Valige eraldajaks tühik ja tühistage kõik muu. Saate vaadata, kuidas teie tulemus välja näeks dialoogiboksi jaotises Andmete eelvaade.
  • Klõpsake nuppu Edasi.
  • Samm 3/3 Tekst veergudele viisard: Selles etapis saate määrata andmevormingu ja selle, kuhu tulemust soovite. Jätan andmevormingu üldiseks, kuna mul on tekstiandmeid jagada. Vaikimisi sihtkoht on A2 ja kui jätkate sellega, asendab see algse andmekogumi. Kui soovite esialgsed andmed puutumatuna hoida, valige sihtkohaks teine ​​lahter. Sel juhul valitakse B2.
  • Klõpsake nuppu Lõpeta.

See jagab lahtri teksti koheselt kaheks erinevaks veeruks.

Märge:

• Teksti veergu funktsioon jagab lahtrite sisu eraldaja põhjal. Kuigi see toimib hästi, kui soovite eraldada eesnime ja perekonnanime, jagab see ees-, kesk- ja perekonnanime puhul selle kolmeks osaks.
• Funktsiooni Tekst veergu kasutamise tulemus on staatiline. See tähendab, et kui algandmetes on mingeid muudatusi, peate värskendatud tulemuste saamiseks seda protsessi kordama.

Lahtrite jagamine Excelis tekstifunktsioonide abil

Exceli teksti funktsioonid on suurepärased, kui soovite teksti stringe lõigata ja tükeldada.

Funktsioon Tekst veergu annab staatilise tulemuse, kuid funktsioonide kasutamisel saadav tulemus on dünaamiline ja uuendatakse automaatselt, kui muudate algseid andmeid.

Eesnime ja perekonnanimega nimede jagamine

Oletame, et teil on samad andmed, mis allpool näidatud:

Eesnime väljavõtmine

Loendist eesnime saamiseks kasutage järgmist valemit:

= VASAK (A2, OTSI ("", A2) -1)

See valem märkaks esimest tühikut ja tagastaks seejärel kogu teksti enne seda tühikut:

See valem kasutab tühiku märgi asukoha leidmiseks funktsiooni OTSI. Bruce Wayne'i puhul on kosmosetegelane 6. positsioonil. Seejärel ekstraheerib see LEFT funktsiooni abil kõik sellest vasakul olevad märgid.

Perekonnanime väljavõtmine

Sarnaselt kasutage perekonnanime saamiseks järgmist valemit:

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

See valem kasutab otsingufunktsiooni, et leida tühiku asukoht funktsiooni SEARCH abil. Seejärel lahutab see selle nime nime kogupikkusest (mille annab funktsioon LEN). See annab perekonnanime tähemärkide arvu.

See perekonnanimi ekstraheeritakse seejärel funktsiooni PAREM abil.

Märge: Need funktsioonid ei pruugi hästi töötada, kui nimedes on esi-, taga- või topeltruumid. Klõpsake siin, et teada saada, kuidas Excelis esi-/lõpp-/topeltruume eemaldada.

Eesnime, kesknime ja perekonnanimega nimede jagamine

Võib juhtuda, et saate nimede kombinatsiooni, kus mõnel nimel on ka teine ​​nimi.

Sellistel juhtudel on valem natuke keeruline.

Eesnime väljavõtmine

Eesnime saamiseks tehke järgmist.

= VASAK (A2, OTSI ("", A2) -1)

See on sama valem, mida kasutasime siis, kui keskmist nime polnud. See lihtsalt otsib esimest tühimärki ja tagastab kõik tühikud enne tühikut.

Keskmise nime väljavõtmine

Keskmise nime saamiseks tehke järgmist.

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

Funktsioon MID algab esimesest tühimärgist ja ekstraheerib keskmise nime, kasutades esimese ja teise tühiku asukoha erinevust.

Kui keskmist nime pole, tagastab funktsioon MID vea. Vea vältimiseks on see pakitud funktsiooni IFERROR sisse.

Perekonnanime väljavõtmine

Perekonnanime saamiseks kasutage järgmist valemit:

= IF (LEN (A2) -LEN (ASENDUS (A2, "", "")) = 1, PAREM (A2, LEN (A2) -SEARCH ("", A2)), PAREM (A2, LEN (A2) -SEARCH ("", A2, SEARCH ("", A2) +1)))

See valem kontrollib, kas on olemas keskmine nimi või mitte (lugedes tühikute arvu). Kui on ainult üks tühik, tagastab see lihtsalt kogu tühikutähe paremal oleva teksti.

Aga kui neid on 2, siis märkab see teist tühikut ja tagastab pärast teist tühikut märkide arvu.

Märkus. Need valemid toimivad hästi, kui teil on nimed, millel on kas rusika- ja perekonnanimi või ees-, keskmine ja perekonnanimi. Kui aga teil on segu, kus teil on järelliiteid või tervitusi, peate valemeid veelgi muutma.

Lahtrite jagamine Excelis välktäite abil

Flash Fill on Excel 2013 uus funktsioon.

See võib olla tõesti mugav, kui teil on muster ja soovite selle osa kiiresti välja võtta.

Võtame näiteks ees- ja perekonnanime andmed:

Välktäide toimib mustrite tuvastamise ja kopeerimisega kõigi teiste lahtrite jaoks.

Eesnime saate loendist Flash Fill abil eemaldada järgmiselt.

• Lahtrisse B2 sisestage Bruce Wayne'i eesnimi (st Bruce).
• Lahtri valimisel märkate lahtri valiku paremas otsas väikest ruutu. Topeltklõpsake seda. See täidab kõikides lahtrites sama nime.
• Kui lahtrid on täidetud, näete paremas alanurgas ikooni Automaattäite valikud. Klõpsake seda.
• Valige loendist Flash Fill.
• Niipea kui valite välktäite, märkate, et kõik lahtrid värskendavad ennast ja näitavad nüüd iga nime eesnime.

Kuidas Flash Fill töötab?

Flash Fill otsib andmestiku mustreid ja kordab mustrit.

Flash Fill on üllatavalt nutikas funktsioon ja töötab enamikul juhtudel ootuspäraselt. Kuid see ebaõnnestub ka mõnel juhul.

Näiteks kui mul on nimede loend, millel on nimede kombinatsioon, mõnel on keskmine nimi ja mõnel mitte.

Kui ma sellisel juhul keskmise nime välja võtan, tagastab Flash Fill ekslikult perekonnanime juhuks, kui eesnime pole.

Ausalt öeldes on see suundumusele endiselt hea ligikaudne. Siiski pole see see, mida ma tahtsin.

Kuid see on siiski piisavalt hea tööriist, mida hoida oma arsenalis ja kasutada vajadusel.

Siin on veel üks näide, kus Flash Fill töötab suurepäraselt.

Mul on hulk aadresse, millest tahan kiiresti linna välja võtta.

Linna kiireks hankimiseks sisestage esimese aadressi jaoks linna nimi (sisestage selle näite lahtrisse London) ja kasutage kõigi lahtrite täitmiseks automaatset täitmist. Kasutage nüüd Flash Fill'i ja annab teile kohe igast aadressist linna nime.

Samamoodi saate aadressi jagada ja aadressi mis tahes osa välja võtta.

Pange tähele, et selleks peab aadress olema homogeenne andmekogum, millel on sama eraldaja (sel juhul koma).

Kui proovite Flash Fillit kasutada, kui mustrit pole, näitab see teile allpool näidatud viga:

Selles õpetuses olen käsitlenud kolme erinevat võimalust Exceli lahtrite jagamiseks mitmeks veeruks (kasutades teksti veergudeks, valemeid ja välktäitmist)

Loodetavasti leidsite selle Exceli õpetuse kasulikuks.