Atskirkite „Excel“ vardą ir pavardę (padalinkite vardus naudodami formules)

Žiūrėkite vaizdo įrašą - padalinkite vardus „Excel“ (į vardą, vidurį ir pavardę)

„Excel“ yra nuostabus įrankis teksto duomenų pjaustymui ir supjaustymui.

Yra tiek daug naudingų formulių ir funkcijų, kurias galite naudoti dirbdami su teksto duomenimis „Excel“.

Vienas iš dažniausiai pasitaikančių klausimų, susijusių su manipuliavimu teksto duomenimis, yra „Kaip atskirti „Excel“ vardą ir pavardę (arba vardą, vidurį ir pavardę)?“.

Yra keletas paprastų būdų suskaidyti vardus „Excel“. Pasirinktas metodas priklausys nuo jūsų duomenų struktūros ir nuo to, ar norite, kad rezultatas būtų statiškas ar dinamiškas.

Taigi pradėkime ir pamatysime skirtingus būdus, kaip padalinti vardus „Excel“.

Padalinkite vardus naudodami tekstą į stulpelius

„Excel“ funkcija „Tekstas į stulpelius“ leidžia greitai padalyti teksto reikšmes į atskirus langelius iš eilės.

Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite atskirti vardą ir pavardę bei gauti juos atskiruose langeliuose.

Žemiau pateikiami veiksmai, skirti atskirti vardą ir pavardę naudojant tekstą į stulpelius:

1. Pasirinkite visus stulpelio pavadinimus (A2: A10 šiame pavyzdyje)
2. Spustelėkite skirtuką „Duomenys“
3. Grupėje „Duomenų įrankiai“ spustelėkite parinktį „Tekstas į stulpelius“.
4. Atlikite šiuos pakeitimus teksto konvertavimo į stulpelį vedlyje:
   1. Žingsnis 1 iš 3: Pasirinkite Atskirtas (tai leidžia naudoti erdvę kaip skyriklį) ir spustelėkite Pirmyn
   2. 2 veiksmas iš 3: Pasirinkite erdvės parinktį ir spustelėkite Pirmyn
   3. 3 veiksmas iš 3: nustatykite B2 kaip paskirties langelį (kitaip jis perrašys esamus duomenis)
5. Spustelėkite Baigti

Pirmiau minėti veiksmai akimirksniu padalintų vardus į vardą ir pavardę (su vardais B stulpelyje ir pavarde C stulpelyje).

Pastaba: jei langeliuose jau yra duomenų (tų, kuriuose tikimasi išvesties iš teksto į stulpelius), „Excel“ parodys įspėjimą, pranešdama, kad ląstelėse jau yra duomenų. Galite pasirinkti perrašyti duomenis arba atšaukti tekstą į stulpelius ir pirmiausia rankiniu būdu juos pašalinti.

Baigę, jei norite, galite ištrinti visus vardo duomenis.

Keletas dalykų, kuriuos reikia žinoti naudojant tekstą į stulpelius, norint atskirti „Excel“ vardą ir pavardę:

1. To rezultatas yra statiškas. Tai reiškia, kad tuo atveju, jei turite naujų duomenų arba kai kurie pradiniai duomenys turi tam tikrų pakeitimų, turite tai padaryti iš naujo, kad padalytumėte vardus.
2. Jei norite tik vardo arba tik pavardės, dialogo lango „Teksto į stulpelį vedlys“ 3 veiksme galite praleisti nenorimus stulpelius. Norėdami tai padaryti, peržiūroje pasirinkite stulpelį, kurį norite praleisti, tada pasirinkite parinktį „Neimportuoti stulpelio (praleisti)“.
3. Jei nenurodysite paskirties langelio, tekstas į stulpelį perrašys esamą stulpelį

Teksto į stulpelius parinktis geriausiai tinka, kai turite nuoseklius duomenis (pavyzdžiui, visi vardai turi tik vardą ir pavardę arba visi vardai turi vardą, vidurį ir pavardę).

Šiame pavyzdyje aš jums parodžiau, kaip atskirti pavadinimus, kurių skiriamoji vieta yra tarpas. Jei ribotuvas yra kablelis arba kablelio ir tarpo derinys, vis tiek galite atlikti tuos pačius veiksmus. Tokiu atveju skiriamąjį ženklą galite nurodyti vedlio 2 veiksme iš 3. Jau yra galimybė naudoti kablelį kaip ribotuvą, arba galite pasirinkti parinktį „Kita“ ir taip pat nurodyti pasirinktinį skyriklį.

Nors tekstas į stulpelius yra greitas ir efektyvus būdas suskaidyti vardus, jis tinka tik tada, kai norite, kad išvestis būtų statinis rezultatas. Jei turite duomenų rinkinį, kuris gali būti išplėstas ar pakeistas, geriau naudoti formules pavadinimams atskirti.

Atskirkite vardus, vidurius ir pavardes naudodami formules

Formulės leidžia supjaustyti ir supjaustyti teksto duomenis ir išgauti tai, ko norite.

Šiame skyriuje aš pasidalinsiu įvairiomis formulėmis, kuriomis galite atskirti vardo duomenis (pagal tai, kaip jūsų duomenys yra struktūrizuoti).

Žemiau pateikiamos trys formulės, kuriomis galite atskirti vardą, vidurinę ir pavardę (išsamiai paaiškinta vėliau sekančiuose skyriuose).

Formulė, skirta gauti vardą:

= LEFT (A2, SEARCH ("", A2) -1)

Vidurinio vardo gavimo formulė:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", PAKAITA (A2, "", "@", 1))-SEARCH ("", A2))

Pavardės gavimo formulė:

= DEŠINĖS (A15, LEN (A15)-PAIEŠKA ("@", PAKAITA (A15, "", "@", LEN (A15) -LEN (PAKEITIMAS (A15, "", "")))))

Gaukite vardą

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite greitai atskirti vardą viename lange ir pavardę viename lange.

Žemiau pateikta formulė suteiks jums vardą:

= LEFT (A2, SEARCH ("", A2) -1)

Aukščiau pateikta formulė naudoja funkciją PAIEŠKA, kad gautų tarpo vietą tarp vardo ir pavardės. Tada funkcija LEFT naudoja šį tarpo pozicijos numerį, kad išgautų visą prieš jį esantį tekstą.

Tai gana paprastas būdas išgauti dalį teksto vertės. Kadangi mums tereikia nustatyti pirmąją tarpo vietą, nesvarbu, ar vardas turi antrąjį vardą, ar ne. Aukščiau pateikta formulė veiks puikiai.

Dabar pažvelkime šiek tiek į priekį su kiekvienu pavyzdžiu.

Gaukite pavardę

Tarkime, kad turite tą patį duomenų rinkinį ir šį kartą turite gauti pavardę.

Žemiau pateikta formulė ištrauks pavardę iš aukščiau pateikto duomenų rinkinio:

= DEŠINĖ (A2, LEN (A2)-SEARCH ("", A2))

Vėlgi, gana paprasta.

Šį kartą pirmiausia randame tarpo simbolio poziciją, kuri vėliau naudojama išsiaiškinti, kiek simbolių liko po tarpo (tai būtų pavardė).

Tai pasiekiama atėmus tarpo simbolio pozicijos vertę iš viso vardo simbolių.

Tada šis skaičius naudojamas funkcijoje „DEŠINĖ“, norint gauti visus šiuos simbolius iš pavadinimo dešinės.

Nors ši formulė puikiai veikia, kai yra tik vardas ir pavardė, ji neveiks, jei taip pat turite antrąjį vardą. Taip yra todėl, kad mes įtraukėme tik vieną tarpo ženklą (tarp vardo ir pavardės). Jei turite vidurinį pavadinimą, prie jo pridedama daugiau tarpo simbolių.

Norėdami gauti pavardę, kai taip pat turite vidurinį vardą, naudokite šią formulę:

= DEŠINĖS (A15, LEN (A15)-PAIEŠKA ("@", PAKAITA (A15, "", "@", LEN (A15) -LEN (PAKEITIMAS (A15, "", "")))))

Dabar tai tapo šiek tiek sudėtinga … ar ne?

Leiskite man paaiškinti, kaip tai veikia.

Aukščiau pateikta formulė pirmiausia nustato bendrą pavadinimo tarpo skaičių. Tai daroma gaunant vardo ilgį su tarpo simboliu ir be jo, o po to atimant iš be tarpo esantį tarpą. Tai suteikia bendrą tarpinių simbolių skaičių.

Funkcija „PAKEITIMAS“ naudojama paskutiniam tarpo simboliui pakeisti „@“ simboliu (galite naudoti bet kurį simbolį - tai mažai tikėtina, kad atsiras kaip pavadinimo dalis).

Kai @ simbolis pakeičiamas paskutinio tarpo simboliu, galite lengvai rasti šio @ simbolio padėtį. Tai atliekama naudojant SEARCH funkciją.

Dabar viskas, ką jums reikia padaryti, yra išgauti visus simbolius, esančius šio simbolio @ dešinėje. Tai atliekama naudojant funkciją DEŠINĖ.

Gaukite vidurinį vardą

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite išgauti vidurinį pavadinimą.

Tai atliks ši formulė:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", PAKAITA (A2, "", "@", 1))-SEARCH ("", A2))

Aukščiau pateikta formulė naudoja MID funkciją, kuri leidžia nurodyti pradžios poziciją ir iš tos pozicijos išgaunamų simbolių skaičių.

Pradinę padėtį lengva rasti naudojant SEARCH funkciją.

Sunkiausia yra rasti, kiek simbolių išgauti po šios pradžios pozicijos. Norėdami tai padaryti, turite nustatyti, kiek simbolių yra nuo pradžios iki paskutinio tarpo.

Tai galima padaryti naudojant funkciją SUBSTITUTE ir paskutinį tarpą pakeičiant simboliu „@“. Kai tai bus padaryta, galite lengvai naudoti funkciją SEARCH, kad surastumėte šio paskutinio tarpo simbolio vietą.

Dabar, kai turite pradinę ir paskutinės vietos poziciją, galite lengvai gauti vidurinį pavadinimą naudodami MID funkciją.

Vienas iš formulių naudojimo pavadinimams atskirti pranašumų yra tas, kad rezultatas yra dinamiškas. Taigi, jei jūsų duomenų rinkinys išsiplečia ir prie jo pridedama daugiau pavadinimų arba jei kai kurie pavadinimai pasikeičia pradiniame duomenų rinkinyje, jums nereikia jaudintis dėl gautų duomenų.

Atskirkite vardus naudodami „Rasti ir pakeisti“

Man patinka lankstumas, kurį suteikia „Rasti ir pakeisti“, nes jame galite naudoti „wild card“ simbolius.

Pirmiausia leiskite man paaiškinti, kas yra „wild card“ personažas.

Pakaitos simbolis yra tai, ką galite naudoti vietoj bet kokio teksto. Pavyzdžiui, galite naudoti žvaigždutės simbolį (*) ir jis parodys bet kokį „Excel“ simbolių skaičių. Pavyzdžiui, jei noriu rasti visus vardus, prasidedančius abėcėle A, galiu rasti ir pakeisti A*. Tai suras ir pažymės visas langelius, kurių pavadinimas prasideda raide A.

Jei vis dar neaišku, nesijaudinkite. Skaitykite toliau, o keli pavyzdžiai paaiškins, kas yra pakaitos simboliai ir kaip juos naudoti norint greitai atskirti vardus (ar bet kokias „Excel“ teksto reikšmes).

Visuose toliau pateiktuose pavyzdžiuose būtinai sukurkite atsarginę duomenų rinkinio kopiją. „Rasti ir pakeisti“ keičia duomenis, kuriais jis naudojamas. Geriausia pirmiausia nukopijuoti ir įklijuoti duomenis, o tada nukopijuotame duomenų rinkinyje naudoti funkciją Rasti ir pakeisti.

Gaukite vardą

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite gauti tik vardą.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Nukopijuokite vardo duomenis A stulpelyje ir įklijuokite į B stulpelį.
2. Pasirinkę B stulpelio duomenis, spustelėkite skirtuką Pagrindinis
3. Redagavimo grupėje spustelėkite Rasti ir pasirinkti.
4. Spustelėkite Pakeisti. Bus atidarytas dialogo langas „Rasti ir pakeisti“.
5. Dialogo lange „Rasti ir pakeisti“ įveskite šiuos duomenis
   1. Raskite ką: * (tarpo simbolis ir žvaigždutės simbolis)
   2. Pakeisti: palikite šį lauką tuščią
6. Spustelėkite Pakeisti viską.

Pirmiau minėti veiksmai suteiks jums vardą ir pašalins viską po vardo.

Tai veikia net jei turite vardus, turinčius vidurinį vardą.

Profesionalus patarimas: Spartusis klavišas, skirtas atidaryti dialogo langą Rasti ir pakeisti, yra „Control“ + H. (palaikykite nuspaudę valdymo klavišą, tada paspauskite mygtuką H).

Gaukite pavardę

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite gauti tik pavardę.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Nukopijuokite vardo duomenis A stulpelyje ir įklijuokite į B stulpelį.
2. Pasirinkę B stulpelio duomenis, spustelėkite skirtuką Pagrindinis
3. Redagavimo grupėje spustelėkite Rasti ir pasirinkti.
4. Spustelėkite Pakeisti. Bus atidarytas dialogo langas „Rasti ir pakeisti“.
5. Dialogo lange „Rasti ir pakeisti“ įveskite šiuos duomenis
   1. Raskite ką: * (žvaigždutės simbolis, po kurio eina tarpo simbolis)
   2. Pakeisti: palikite šį lauką tuščią
6. Spustelėkite Pakeisti viską.

Pirmiau minėti veiksmai suteiks jums pavardę ir pašalins viską prieš vardą.

Tai veikia net jei turite vardus, turinčius vidurinį vardą.

Pašalinkite vidurinį vardą

Jei norite atsikratyti tik vidurinio vardo ir turite tik vardą ir pavardę, tai galite padaryti naudodami „Rasti ir pakeisti“.

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite iš jų pašalinti vidurinį pavadinimą.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Nukopijuokite vardo duomenis A stulpelyje ir įklijuokite į B stulpelį.
2. Pasirinkę B stulpelio duomenis, spustelėkite skirtuką Pagrindinis
3. Redagavimo grupėje spustelėkite Rasti ir pasirinkti.
4. Spustelėkite Pakeisti. Bus atidarytas dialogo langas „Rasti ir pakeisti“.
5. Dialogo lange „Rasti ir pakeisti“ įveskite šiuos duomenis
   1. Raskite ką: * (tarpo simbolis, po kurio yra žvaigždutė, po kurio eina tarpo simbolis)
   2. Pakeiskite: (tiesiog įdėkite čia tarpo simbolį)
6. Spustelėkite Pakeisti viską.

Pirmiau minėti veiksmai pašalintų vidurinį vardą iš viso vardo. Jei kai kurie vardai neturi vidurinio vardo, jie nebus pakeisti.

Atskiri vardai naudojant „Flash Fill“

„Flash“ užpildymas buvo įdiegtas programoje „Excel 2013“ ir labai palengvina teksto duomenų rinkinio keitimą ar valymą.

Ir kai reikia atskirti vardų duomenis, tai yra „Flash Fill“ alėjoje.

Svarbiausias dalykas, kurį reikia žinoti naudojant „Flash Fill“, yra tas, kad reikia modelio, kurį galima atpažinti naudojant „Flash“ užpildymą. Nustačius modelį, jis lengvai padės suskaidyti vardus „Excel“ (daugiau aiškumo gausite, kai peržiūrėsite kelis žemiau pateiktus pavyzdžius).

Gaukite vardą arba pavardę iš viso vardo

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite gauti tik vardą.

1. Gretimame langelyje rankiniu būdu įveskite vardą iš viso vardo. Šiame pavyzdyje įvesčiau Ricką.
2. Antrame langelyje rankiniu būdu įveskite vardą iš gretimos ląstelės pavadinimo. Rašydami matysite, kad „Flash Fill“ automatiškai parodys vardo sąrašą (pilka spalva).
3. Kai matote pilkus pavadinimus, greitai peržiūrėkite jį ir įsitikinkite, kad jame rodomi teisingi vardai. Jei tai teisinga, paspauskite įvesties klavišą ir „Flash Fill“ automatiškai užpildys likusias ląsteles vardu.

„Flash Fill“ turi pateikti šabloną, kuriuo jis galėtų vadovautis pateikdamas jums pakeistus duomenis. Mūsų pavyzdyje, kai pirmame langelyje įvedate vardą, „Flash Fill“ negali išsiaiškinti modelio.

Bet kai tik pradėsite įvesti vardą antrame langelyje, „Flash Fill“ supras modelį ir parodys jums pasiūlymą. Jei pasiūlymas teisingas, tiesiog paspauskite įvesties klavišą.

Ir jei tai neteisinga, galite pabandyti įvesti rankiniu būdu į dar keletą langelių ir patikrinti, ar „Flash Fill“ gali atpažinti modelį, ar ne.

Kartais šablono galite nematyti pilkai (kaip parodyta 2 veiksme aukščiau). Jei taip yra, atlikite toliau nurodytus veiksmus, kad gautumėte „Flash Fill“ rezultatą:

1. Įveskite tekstą rankiniu būdu į du langelius.
2. Pasirinkite abi šias ląsteles
3. Užveskite žymeklį apatinėje dešinėje pasirinkimo dalyje. Pastebėsite, kad žymeklis pasikeis į pliuso piktogramą
4. Dukart spustelėkite jį (kairysis pelės klavišas). Tai užpildys visas ląsteles. Šiuo metu rezultatai greičiausiai yra neteisingi ir ne tokie, kokių tikėjotės.
5. Gautų duomenų apačioje dešinėje pamatysite mažą automatinio pildymo piktogramą. Spustelėkite šią automatinio pildymo piktogramą
6. Spustelėkite „Flash Fill“

Pirmiau minėti veiksmai suteiks jums „Flash Fill“ rezultatą (remiantis modeliu, kurį jis padarė).

Taip pat galite naudoti „Flash Fill“, kad gautumėte pavardę arba vidurinį vardą. Pirmuose dviejuose langeliuose įveskite pavardę (arba vidurinį vardą) ir „flash fill“ galės suprasti modelį

Pertvarkykite pavadinimą naudodami „Flash Fill“

„Flash Fill“ yra protingas įrankis ir gali iššifruoti ir šiek tiek sudėtingą modelį

Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite pertvarkyti vardą iš Rick Novak į Novak, Rick (kur pirmiausia pateikiama pavardė, po kurios - kablelis, o po to - vardas).

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Gretimoje ląstelėje įveskite rankiniu būdu Novakas, Rikas
2. Antrame lange įveskite rankiniu būdu Konoras, Siuzana. Rašydami matysite, kad „Flash Fill“ parodys to paties formato vardų sąrašą (pilka spalva).
3. Kai matote pilkus pavadinimus, greitai peržiūrėkite jį ir įsitikinkite, kad jame rodomi teisingi pavadinimai. Jei tai teisinga, paspauskite įvesties klavišą ir „Flash Fill“ automatiškai užpildys likusias ląsteles to paties formato pavadinimais.

Pašalinkite vidurinį vardą (arba tiesiog gaukite vidurinį vardą)

Taip pat galite naudoti „Flash Fill“, kad atsikratytumėte vidurinio vardo arba gautumėte tik vidurinį pavadinimą.

Pvz., Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, ir norite gauti tik vardą ir pavardę, o ne vidurinį vardą.

Žemiau pateikiami žingsniai, kaip tai padaryti:

1. Gretimoje ląstelėje įveskite rankiniu būdu Rikas Novakas
2. Antrame lange įveskite rankiniu būdu Susan Connor. Rašydami matysite, kad „Flash Fill“ parodys to paties formato vardų sąrašą (pilka spalva).
3. Kai matote pilkus pavadinimus, greitai peržiūrėkite jį ir įsitikinkite, kad jame rodomi teisingi pavadinimai. Jei tai teisinga, paspauskite įvesties klavišą ir „Flash Fill“ automatiškai užpildys likusias ląsteles pavadinimais be vidurinio pavadinimo.

Panašiai, jei norite gauti tik vidurinius vardus, įveskite vidurinį pavadinimą į pirmuosius du langelius ir naudokite „Flash Fill“, kad gautumėte vidurinį pavadinimą iš visų likusių pavadinimų.

Šiame vadove parodyti pavyzdžiai naudoja pavadinimus manipuliuodami teksto duomenimis. Tomis pačiomis sąvokomis galite dirbti ir su kitais duomenų formatais (pvz., Adresais, produktų pavadinimais ir kt.)