Atdaliet vārdu un uzvārdu programmā Excel (sadaliet vārdus, izmantojot formulas)

Skatieties video - sadaliet vārdus programmā Excel (vārdā, uzvārdā un uzvārdā)

Excel ir pārsteidzošs rīks teksta datu sagriešanai un kubiciņos.

Ir tik daudz noderīgu formulu, kā arī funkcijas, kuras varat izmantot, lai strādātu ar teksta datiem programmā Excel.

Viens no ļoti bieži uzdotajiem jautājumiem par manipulēšanu ar teksta datiem ir - “Kā programmā Excel atdalīt vārdu un uzvārdu (vai vārdu, vidējo un uzvārdu)?“.

Ir daži vienkārši veidi, kā sadalīt vārdus programmā Excel. Izvēlētā metode būs atkarīga no tā, kā jūsu dati ir strukturēti un vai vēlaties, lai rezultāts būtu statisks vai dinamisks.

Tāpēc sāksim darbu un apskatīsim dažādus veidus, kā sadalīt vārdus programmā Excel.

Sadaliet vārdus kolonnās, izmantojot tekstu

Funkcija Teksts kolonnās programmā Excel ļauj ātri sadalīt teksta vērtības atsevišķās šūnās pēc kārtas.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties atdalīt vārdu un uzvārdu un iegūt tos atsevišķās šūnās.

Tālāk ir norādītas darbības, lai atdalītu vārdu un uzvārdu, izmantojot tekstu kolonnās.

1. Atlasiet visus nosaukumus kolonnā (šajā piemērā A2: A10)
2. Noklikšķiniet uz cilnes “Dati”
3. Grupā “Datu rīki” noklikšķiniet uz opcijas “Teksts uz kolonnām”.
4. Veiciet šādas izmaiņas teksta konvertēšanas kolonnas vednī:
   1. 1. darbība no 3: izvēlieties Atdalīts (tas ļauj izmantot atstarpi kā atdalītāju) un noklikšķiniet uz Tālāk
   2. 2. darbība no 3: atlasiet opciju Space un noklikšķiniet uz Tālāk
   3. 3. darbība no 3: iestatiet B2 kā galamērķa šūnu (pretējā gadījumā tā pārrakstīs esošos datus)
5. Noklikšķiniet uz Pabeigt

Iepriekš minētās darbības uzreiz sadalītu vārdus vārdos un uzvārdos (ar vārdiem B slejā un uzvārdu C slejā).

Piezīme. Ja šūnās jau ir dati (tajos, kur tiek gaidīta teksta izveide uz kolonnām), programma Excel parādīs brīdinājumu, informējot, ka šūnās jau ir daži dati. Varat izvēlēties pārrakstīt datus vai atcelt tekstu kolonnās un vispirms manuāli noņemt.

Kad esat pabeidzis, varat dzēst pilnu vārdu datus, ja vēlaties.

Dažas lietas, kas jāzina, lietojot tekstu kolonnās, lai programmā Excel atdalītu vārdus un uzvārdus:

1. Rezultāts ir statisks. Tas nozīmē, ka gadījumā, ja jums ir jauni dati vai sākotnējos datos ir dažas izmaiņas, jums tas jādara vēlreiz, lai sadalītu nosaukumus.
2. Ja vēlaties tikai vārdu vai tikai uzvārdu, dialoglodziņa “Teksts uz kolonnu vednis” 3. darbībā varat izlaist slejas, kuras nevēlaties. Lai to izdarītu, priekšskatījumā atlasiet kolonnu, kuru vēlaties izlaist, un pēc tam atlasiet opciju “Neimportēt kolonnu (izlaist)”.
3. Ja nenorādāt galamērķa šūnu, teksts kolonnā pārrakstīs pašreizējo kolonnu

Opcija Teksts uz kolonnām ir vislabāk piemērota, ja jums ir konsekventi dati (piemēram, visiem vārdiem ir tikai vārds un uzvārds vai visiem vārdiem ir vārds, vidējais un uzvārds).

Šajā piemērā es jums parādīju, kā atdalīt vārdus, kuru norobežotājs ir atstarpe. Ja norobežotājs ir komats vai komata un atstarpes kombinācija, joprojām varat izmantot tās pašas darbības. Šādā gadījumā norobežotāju varat norādīt vedņa 2. darbībā no 3. Jau ir iespēja izmantot komatu kā norobežotāju, vai arī varat izvēlēties opciju “Cits” un norādīt arī pielāgotu norobežotāju.

Lai gan teksts kolonnās ir ātrs un efektīvs veids, kā sadalīt vārdus, tas ir piemērots tikai tad, ja vēlaties, lai izvade būtu statisks rezultāts. Ja jums ir datu kopa, kas var paplašināties vai mainīties, labāk ir izmantot formulas, lai atdalītu nosaukumus.

Atdaliet vārdu, vidējo un uzvārdu, izmantojot formulas

Formulas ļauj sagriezt un sasmalcināt teksta datus un iegūt vajadzīgo.

Šajā sadaļā es dalīšos ar dažādām formulām, kuras varat izmantot vārdu datu atdalīšanai (pamatojoties uz jūsu datu struktūru).

Tālāk ir norādītas trīs formulas, kuras varat izmantot, lai atdalītu vārdu, uzvārdu un uzvārdu (sīkāk izskaidrots vēlāk turpmākajās sadaļās).

Formula vārda iegūšanai:

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

Formula, lai iegūtu otro vārdu:

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

Uzvārda iegūšanas formula:

= RIGHT (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))))

Iegūstiet vārdu

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties ātri atdalīt vārdu vienā šūnā un uzvārdu vienā šūnā.

Tālāk sniegtā formula sniegs jums vārdu:

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

Iepriekšminētā formula izmanto funkciju MEKLĒT, lai iegūtu atstarpes rakstzīmes pozīciju starp vārdu un uzvārdu. Funkcija LEFT pēc tam izmanto šo atstarpes pozīcijas numuru, lai izvilktu visu tekstu pirms tā.

Tas ir diezgan vienkāršs veids, kā iegūt daļu no teksta vērtības. Tā kā viss, kas mums jādara, ir identificēt pirmo atstarpes rakstzīmes pozīciju, nav nozīmes tam, vai nosaukumam ir otrais vārds vai nē. Iepriekš minētā formula darbosies lieliski.

Tagad ar katru piemēru uzlabosimies.

Iegūstiet uzvārdu

Pieņemsim, ka jums ir tāda pati datu kopa, un šoreiz jums jāiegūst uzvārds.

Tālāk sniegtā formula iegūs uzvārdu no iepriekš minētās datu kopas:

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

Atkal diezgan vienkārši.

Šoreiz mēs vispirms atrodam atstarpes rakstzīmes pozīciju, kuru pēc tam izmanto, lai noskaidrotu pēc atstarpes atstāto rakstzīmju skaitu (kas būtu uzvārds).

Tas tiek panākts, atņemot atstarpes rakstzīmes pozīcijas vērtību ar kopējo rakstzīmju skaitu nosaukumā.

Pēc tam šis numurs tiek izmantots funkcijā RIGHT, lai visas šīs rakstzīmes iegūtu no vārda labās puses.

Lai gan šī formula darbojas lieliski, ja ir tikai vārds un uzvārds, tā nedarbosies, ja jums ir arī otrais vārds. Tas ir tāpēc, ka mēs veidojām tikai vienu atstarpes rakstzīmi (starp vārdu un uzvārdu). Izmantojot vidējo vārdu, nosaukumam tiek pievienotas vairāk atstarpes rakstzīmes.

Lai iegūtu uzvārdu, ja jums ir arī otrais vārds, izmantojiet zemāk esošo formulu:

= RIGHT (A15, LEN (A15) -SEARCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))))

Tagad tas ir kļuvis mazliet sarežģīts … vai ne?

Ļaujiet man paskaidrot, kā tas darbojas.

Iepriekš minētā formula vispirms atrod kopējo nosaukuma atstarpes rakstzīmju skaitu. Tas tiek darīts, iegūstot vārda garumu ar atstarpes rakstzīmi un bez tās, un pēc tam no tās, kurā ir atstarpe, atņemot vārdu bez atstarpes. Tas parāda kopējo atstarpju rakstzīmju skaitu.

Funkcija SUBSTITUTE pēc tam tiek izmantota, lai pēdējo atstarpes rakstzīmi aizstātu ar simbolu “@” (varat izmantot jebkuru simbolu, kas, visticamāk, nenotiks kā nosaukuma daļa).

Kad @ simbols ir aizstāts pēdējās atstarpes rakstzīmes vietā, jūs varat viegli atrast šī @ simbola atrašanās vietu. Tas tiek darīts, izmantojot funkciju MEKLĒT.

Tagad viss, kas jums jādara, ir izvilkt visas rakstzīmes pa labi no šī @ simbola. Tas tiek darīts, izmantojot RIGHT funkciju.

Iegūstiet otro vārdu

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt vidējo nosaukumu.

To darīs šāda formula:

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

Iepriekšminētā formula izmanto MID funkciju, kas ļauj norādīt sākuma pozīciju un no šīs pozīcijas iegūstamo rakstzīmju skaitu.

Sākuma pozīciju ir viegli atrast, izmantojot funkciju MEKLĒT.

Grūtākais ir atrast, cik rakstzīmju jāizgūst pēc šīs sākuma pozīcijas. Lai to iegūtu, jums jāidentificē, cik rakstzīmju ir no sākuma pozīcijas līdz pēdējai atstarpes rakstzīmei.

To var izdarīt, izmantojot funkciju SUBSTITUTE un pēdējo atstarpes rakstzīmi aizstāt ar simbolu “@”. Kad tas ir izdarīts, varat viegli izmantot funkciju MEKLĒT, lai atrastu šīs pēdējās atstarpes rakstzīmes atrašanās vietu.

Tagad, kad jums ir sākuma pozīcija un pēdējās atstarpes pozīcija, varat viegli iegūt vidējo nosaukumu, izmantojot funkciju MID.

Viena no formulu izmantošanas priekšrocībām nosaukumu atdalīšanai ir tā, ka rezultāts ir dinamisks. Tātad, ja jūsu datu kopa tiek paplašināta un tai tiek pievienoti vairāk nosaukumu, vai ja sākotnējā datu kopā daži nosaukumi mainās, jums nav jāuztraucas par iegūtajiem datiem.

Atdaliet vārdus, izmantojot funkciju Atrast un aizstāt

Man patīk elastība, kas ir saistīta ar funkciju “Atrast un aizstāt”, jo tajā varat izmantot aizstājējzīmju rakstzīmes.

Vispirms ļaujiet man paskaidrot, kas ir aizstājējzīme.

Aizstājējzīmes var izmantot teksta vietā. Piemēram, varat izmantot zvaigznītes simbolu (*), un tas programmā Excel atspoguļos jebkuru rakstzīmju skaitu. Lai sniegtu piemēru, ja vēlos atrast visus vārdus, kas sākas ar alfabētu A, meklēšanā un aizstāšanā varu izmantot A*. Tas atradīs un atlasīs visas šūnas, kurās nosaukums sākas ar A.

Ja jums joprojām nav skaidrs, neuztraucieties. Turpiniet lasīt, un nākamajos piemēros būs skaidrs, kas ir aizstājējzīmes un kā tās izmantot, lai ātri atdalītu nosaukumus (vai visas teksta vērtības programmā Excel).

Visos tālāk sniegtajos piemēros noteikti izveidojiet datu kopas rezerves kopiju. Atrast un aizstāt maina datus, kuriem tas tiek izmantots. Vislabāk ir vispirms kopēt un ielīmēt datus un pēc tam kopētajā datu kopā izmantot funkciju Atrast un aizstāt.

Iegūstiet vārdu

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt tikai vārdu.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Kopējiet nosaukuma datus A slejā un ielīmējiet tos B slejā.
2. Kad ir atlasīti dati B slejā, noklikšķiniet uz cilnes Sākums
3. Rediģēšanas grupā noklikšķiniet uz Atrast un atlasīt.
4. Noklikšķiniet uz Aizstāt. Tiks atvērts dialoglodziņš “Atrast un aizstāt”.
5. Dialoglodziņā “Atrast un aizstāt” ievadiet tālāk norādīto
   1. Atrodiet: * (atstarpes zīme, kam seko zvaigznītes simbols)
   2. Aizstāt ar: atstājiet šo lauku tukšu
6. Noklikšķiniet uz Aizstāt visu.

Iepriekš minētās darbības dotu jums vārdu un noņemtu visu pēc vārda.

Tas darbojas pat tad, ja jums ir vārdi ar vidējo vārdu.

Pro padoms: Ir īsinājumtaustiņš, lai atvērtu dialoglodziņu Atrast un aizstāt Ctrl + H (turiet nospiestu vadības taustiņu un pēc tam nospiediet taustiņu H).

Iegūstiet uzvārdu

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt tikai uzvārdu.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Kopējiet nosaukuma datus A slejā un ielīmējiet tos B slejā.
2. Kad ir atlasīti dati B slejā, noklikšķiniet uz cilnes Sākums
3. Rediģēšanas grupā noklikšķiniet uz Atrast un atlasīt.
4. Noklikšķiniet uz Aizstāt. Tiks atvērts dialoglodziņš “Atrast un aizstāt”.
5. Dialoglodziņā “Atrast un aizstāt” ievadiet tālāk norādīto
   1. Atrodiet: * (zvaigznītes simbols, kam seko atstarpe)
   2. Aizstāt ar: atstājiet šo lauku tukšu
6. Noklikšķiniet uz Aizstāt visu.

Iepriekš minētās darbības dotu jums uzvārdu un noņemtu visu pirms vārda.

Tas darbojas pat tad, ja jums ir vārdi ar vidējo vārdu.

Noņemiet otro vārdu

Ja vēlaties atbrīvoties tikai no otrā vārda un jums ir tikai vārds un uzvārds, varat to izdarīt, izmantojot funkciju Atrast un aizstāt.

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties no tiem noņemt otro vārdu.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Kopējiet nosaukuma datus A slejā un ielīmējiet tos B slejā.
2. Kad ir atlasīti dati B slejā, noklikšķiniet uz cilnes Sākums
3. Rediģēšanas grupā noklikšķiniet uz Atrast un atlasīt.
4. Noklikšķiniet uz Aizstāt. Tiks atvērts dialoglodziņš “Atrast un aizstāt”.
5. Dialoglodziņā “Atrast un aizstāt” ievadiet tālāk norādīto
   1. Atrodiet, ko: * (atstarpes zīme, kurai seko zvaigznītes simbols, kam seko atstarpes zīme)
   2. Aizstāt ar: (šeit vienkārši ievietojiet atstarpes rakstzīmi)
6. Noklikšķiniet uz Aizstāt visu.

Iepriekš minētās darbības noņemtu vidējo vārdu no pilna vārda. Ja dažiem vārdiem nav otrā vārda, tie netiks mainīti.

Atsevišķi vārdi, izmantojot Flash Fill

Flash aizpilde tika ieviesta programmā Excel 2013, un tas patiešām atvieglo teksta datu kopas modificēšanu vai tīrīšanu.

Un, runājot par vārdu datu atdalīšanu, tas atrodas Flash Fill alejā.

Vissvarīgākā lieta, kas jāzina, izmantojot zibspuldzes aizpildi, ir tāda, ka ir nepieciešams modelis, ko zibspuldzes aizpilde var identificēt. Kad tas ir identificējis modeli, tas viegli palīdzēs sadalīt vārdus programmā Excel (jūs iegūsit lielāku skaidrību par to, izskatot dažus tālāk sniegtos piemērus).

Iegūstiet vārdu vai uzvārdu no pilna vārda

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt tikai vārdu.

1. Blakus esošajā šūnā manuāli ierakstiet vārdu no pilna vārda. Šajā piemērā es ierakstītu Riku.
2. Otrajā šūnā manuāli ierakstiet vārdu no blakus esošās šūnas nosaukuma. Rakstīšanas laikā Flash Fill automātiski parādīs vārda sarakstu (pelēkā krāsā).
3. Kad redzat vārdus pelēkā krāsā, ātri ieskatieties tajā, lai pārliecinātos, ka tas parāda pareizos vārdus. Ja tie ir pareizi, nospiediet ievadīšanas taustiņu, un Flash Fill automātiski aizpildīs pārējās šūnas ar vārdu.

Flash Fill jums ir jānorāda modelis, pēc kura tas var sekot, sniedzot jums modificētos datus. Mūsu piemērā, ievadot vārdu pirmajā šūnā, Flash Fill nevar saprast modeli.

Bet, tiklīdz jūs sākat ievadīt vārdu otrajā šūnā, Flash Fill saprot modeli un parāda dažus ieteikumus. Ja ieteikums ir pareizs, vienkārši nospiediet taustiņu Enter.

Un, ja tas nav pareizi, varat mēģināt ievadīt manuāli vēl dažās šūnās un pārbaudīt, vai Flash Fill spēj noteikt modeli vai nē.

Dažreiz modelis var nebūt redzams pelēkā krāsā (kā parādīts 2. darbībā iepriekš). Ja tas tā ir, izpildiet tālāk norādītās darbības, lai iegūtu Flash Fill rezultātu:

1. Manuāli ievadiet tekstu divās šūnās.
2. Atlasiet abas šīs šūnas
3. Virziet kursoru atlases apakšējā labajā pusē. Jūs pamanīsit, ka kursors mainās uz pluszīmes ikonu
4. Veiciet dubultklikšķi uz tā (peles kreisais taustiņš). Tas aizpildīs visas šūnas. Šobrīd rezultāti, visticamāk, ir nepareizi un nav tādi, kādus jūs gaidījāt.
5. Iegūto datu apakšējā labajā stūrī redzēsit nelielu automātiskās aizpildes ikonu. Noklikšķiniet uz šīs automātiskās aizpildīšanas ikonas
6. Noklikšķiniet uz Flash Fill

Iepriekš minētās darbības sniegs jums Flash Fill rezultātu (pamatojoties uz tā secināto modeli).

Varat arī izmantot Flash Fill, lai iegūtu uzvārdu vai otro vārdu. Pirmajās divās šūnās ievadiet uzvārdu (vai otro vārdu), un zibspuldzes aizpildīšana varēs saprast modeli

Pārkārtojiet nosaukumu, izmantojot Flash Fill

Flash Fill ir gudrs rīks, un tas var arī atšifrēt nedaudz sarežģītu modeli

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties pārkārtot vārdu no Rick Novak uz Novak, Rick (kur vispirms tiek uzvārds, kam seko komats un pēc tam vārds).

Tālāk ir norādītas darbības, kā to izdarīt:

1. Blakus esošajā šūnā ierakstiet manuāli Novaks, Riks
2. Otrajā šūnā ierakstiet manuāli Konors, Sjūzena. Rakstīšanas laikā Flash Fill parādīs vārdu sarakstu tādā pašā formātā (pelēkā krāsā).
3. Kad redzat vārdus pelēkā krāsā, ātri ieskatieties tajā, lai pārliecinātos, ka tas parāda pareizos vārdus. Ja tie ir pareizi, nospiediet ievadīšanas taustiņu, un Flash Fill automātiski aizpildīs pārējās šūnas ar nosaukumiem tādā pašā formātā.

Noņemiet otro vārdu (vai vienkārši iegūstiet otro vārdu)

Varat arī izmantot Flash Fill, lai atbrīvotos no otrā vārda vai iegūtu tikai otro vārdu.

Piemēram, pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt tikai vārdu un uzvārdu, nevis otro vārdu.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Blakus esošajā šūnā ierakstiet manuāli Riks Novaks
2. Otrajā šūnā ierakstiet manuāli Sjūzena Konora. Rakstīšanas laikā Flash Fill parādīs vārdu sarakstu tādā pašā formātā (pelēkā krāsā).
3. Kad redzat vārdus pelēkā krāsā, ātri ieskatieties tajā, lai pārliecinātos, ka tas parāda pareizos vārdus. Ja tie ir pareizi, nospiediet ievadīšanas taustiņu, un Flash Fill automātiski aizpildīs pārējās šūnas ar nosaukumiem bez vidējā nosaukuma.

Līdzīgi, ja vēlaties iegūt tikai vidējos vārdus, pirmajās divās šūnās ierakstiet vidējo nosaukumu un izmantojiet Flash Fill, lai iegūtu vidējo nosaukumu no visiem atlikušajiem nosaukumiem.

Šajā apmācībā redzamajos piemēros tiek izmantoti vārdi, manipulējot ar teksta datiem. Jūs varat izmantot tos pašus jēdzienus, lai strādātu arī ar citiem datu formātiem (piemēram, adresēm, produktu nosaukumiem utt.).