Отделно име и фамилия в Excel (разделяне на имена чрез формули)

Гледайте видео - Разделяне на имена в Excel (на собствено, средно и фамилно име)

Excel е невероятен инструмент, когато става въпрос за нарязване и нарязване на текстови данни.

Има толкова много полезни формули, както и функционалности, които можете да използвате за работа с текстови данни в Excel.

Един от много често срещаните въпроси, които получавам относно манипулирането на текстови данни, е - „Как да разделим име и фамилия (или име, бащино и фамилно име) в Excel?“.

Има няколко лесни начина за разделяне на имена в Excel. Избраният от вас метод ще зависи от това как са структурирани вашите данни и дали искате резултатът да е статичен или динамичен.

Така че нека да започнем и да видим различни начини за разделяне на имена в Excel.

Разделяне на имена чрез използване на текст към колони

Функцията Text to Columns в Excel ви позволява бързо да разделяте текстовите стойности на отделни клетки в един ред.

Да предположим например, че имате набора от данни, както е показано по -долу, и искате да разделите името и фамилията и да ги получите в отделни клетки.

По -долу са описани стъпките за разделяне на името и фамилията с помощта на текст в колони:

1. Изберете всички имена в колоната (A2: A10 в този пример)
2. Щракнете върху раздела „Данни“
3. В групата „Инструменти за данни“ кликнете върху опцията „Текст в колони“.
4. Направете следните промени в съветника за преобразуване на текст в колона:
   1. Стъпка 1 от 3: Изберете Разграничено (това ви позволява да използвате пространството като разделител) и щракнете върху Напред
   2. Стъпка 2 от 3: Изберете опцията Space и кликнете върху Next
   3. Стъпка 3 от 3: Задайте B2 като клетка дестинация (иначе тя ще презапише съществуващите данни)
5. Щракнете върху Край

Горните стъпки незабавно биха разделили имената на име и фамилия (с имена в колона B и фамилия в колона C).

Забележка: В случай, че вече има данни в клетките (тези, при които се очаква извеждане на текст в колони), Excel ще ви покаже предупреждение, което ви уведомява, че вече има някои данни в клетките. Можете да изберете да презапишете данните или да отмените Text to Columns и първо да го премахнете ръчно.

След като приключите, можете да изтриете данните за пълното име, ако искате.

Няколко неща, които трябва да знаете, когато използвате Text to Columns за разделяне на име и фамилия в Excel:

1. Резултатът от това е статичен. Това означава, че в случай, че имате нови данни или оригиналните данни имат някои промени, трябва да направите това отново, за да разделите имената.
2. Ако искате само името или само фамилията, можете да пропуснете колоните, които не искате, в стъпка 3 на диалоговия прозорец „Съветник за текст към колона“. За да направите това, изберете колоната в визуализацията, която искате да пропуснете, след което изберете опцията „Не импортирай колона (пропускане)“.
3. В случай, че не посочите целевата клетка, Text to Column ще презапише текущата колона

Опцията Текст в колони е най -подходяща, когато имате последователни данни (например всички имена имат само име и фамилия или всички имена имат име, бащино и фамилно име).

В този пример ви показах как да разделите имената, които имат пространство като разделител. В случай, че разделителят е запетая или комбинация от запетая и интервал, все още можете да използвате същите стъпки. В този случай можете да посочите разделителя в стъпка 2 от 3 на съветника. Вече има опция за използване на запетая като разделител или можете да изберете опцията „Други“ и да посочите и персонализиран разделител.

Докато Text to Columns е бърз и ефективен начин за разделяне на имена, той е подходящ само когато искате изходът да бъде статичен резултат. В случай, че имате набор от данни, който може да се разшири или промени, по -добре е да използвате формули за разделяне на имената.

Разделете първо, средно и фамилно име с помощта на формули

Формулите ви позволяват да нарязвате и нарязвате текстовите данни и да извличате това, което искате.

В този раздел ще споделя различни формули, които можете да използвате за разделяне на данните за имената (въз основа на това как са структурирани вашите данни).

По -долу са трите формули, които можете да използвате за разделяне на собствено, средно и фамилно име (обяснени подробно по -късно в следващите раздели).

Формула за получаване на първото име:

= НАЛЯВО (A2, ТЪРСЕНЕ ("", A2) -1)

Формула за получаване на средно име:

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

Формула за получаване на фамилното име:

= НАДЯСНО (A15, LEN (A15) -SERCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))

Вземете първото име

Да предположим, че имате набора от данни, както е показано по -долу и искате бързо да разделите първото име в една клетка и фамилното име в една клетка.

Формулата по -долу ще ви даде името:

= НАЛЯВО (A2, ТЪРСЕНЕ ("", A2) -1)

Горната формула използва функцията SEARCH, за да получи позицията на интервала между името и фамилията. След това функцията LEFT използва този номер на интервал за извличане на целия текст преди него.

Това е доста директно използване на извличане на част от текстовата стойност. Тъй като всичко, което трябва да направим, е да идентифицираме първата позиция на интервала, няма значение дали името има средно име или не. Горната формула ще работи добре.

Сега нека да станем малко по -напреднали с всеки пример.

Вземете фамилното име

Да предположим, че имате същия набор от данни и този път трябва да получите фамилното име.

Формулата по -долу ще извлече фамилията от горния набор от данни:

= НАДЯСНО (A2, LEN (A2) -SEARCH ("", A2))

Отново съвсем ясно.

Този път първо откриваме позицията на интервала, която след това се използва за установяване на броя знаци, които са останали след интервал (което би било фамилното име).

Това се постига чрез изваждане на позиционната стойност на интервала с общия брой знаци в името.

След това този номер се използва във функцията НАДЯСНО за извличане на всички тези знаци отдясно на името.

Въпреки че тази формула работи чудесно, когато има само име и фамилия, тя няма да работи в случай, че имате и бащино име. Това е така, защото сме отчитали само един интервал (между име и фамилия). Наличието на бащино име добавя още знаци за интервал към името.

За да извлечете фамилното име, когато имате и бащино име, използвайте формулата по -долу:

= НАДЯСНО (A15, LEN (A15) -SERCH ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))

Сега това започна да става малко сложно … нали?

Нека обясня как работи това.

Горната формула първо намира общия брой знаци за интервал в името. Това става чрез получаване на дължината на името със и без знак за интервал и след това изваждане на това без интервал от това с интервал. Това дава общия брой знаци за интервал.

След това функцията SUBSTITUTE се използва за замяна на последния интервал със символ „@“ (можете да използвате всеки символ - нещо, което е малко вероятно да се появи като част от името).

След като символът @ е заменен вместо последния знак за интервал, можете лесно да намерите позицията на този символ @. Това става с помощта на функцията SEARCH.

Сега всичко, което трябва да направите, е да извлечете всички знаци вдясно от този символ @. Това става с помощта на функцията НАДЯСНО.

Вземете средното име

Да предположим, че имате набора от данни, както е показано по -долу, и искате да извлечете средното име.

Следната формула ще направи това:

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

Горната формула използва функцията MID, която ви позволява да посочите начална позиция и броя на знаците, които да извлечете от тази позиция.

Началната позиция е лесна за намиране с помощта на функцията SEARCH.

Трудната част е да намерите колко знака да извлечете след тази начална позиция. За да получите това, трябва да определите колко знака има от началната позиция до последния знак за интервал.

Това може да стане с помощта на функцията SUBSTITUTE и да се замени последният интервал със символ „@“. След като това стане, можете лесно да използвате функцията SEARCH, за да намерите позицията на този последен знак за интервал.

Сега, когато имате началната позиция и позицията на последното пространство, можете лесно да извлечете средното име, като използвате функцията MID.

Едно от предимствата на използването на формула за разделяне на имената е, че резултатът е динамичен. Така че в случай, че вашият набор от данни се разширява и към него се добавят още имена или ако някои имена се променят в първоначалния набор от данни, не е нужно да се притеснявате за получените данни.

Отделни имена с помощта на Find and Replace

Обичам гъвкавостта, която идва с „Намери и замени“ - защото можете да използвате заместващи символи в нея.

Позволете ми първо да обясня какво е „wild card“ характер.

Заместващ знак е нещо, което можете да използвате вместо всеки текст. Например, можете да използвате символ на звездичка (*) и той ще представлява произволен брой знаци в Excel. За да ви дам пример, ако искам да намеря всички имена, които започват с азбуката А, мога да използвам А* при намиране и замяна. Това ще намери и избере всички клетки, където името започва с А.

Ако все още не сте наясно, не се притеснявайте. Продължете да четете и следващите няколко примера ще изяснят какво представляват заместващите знаци и как да ги използвате за бързо разделяне на имена (или всякакви текстови стойности в Excel).

Във всички примери, разгледани по -долу, не забравяйте да създадете резервно копие на набора от данни. Намиране и замяна променя данните, за които се използва. Най -добре е първо да копирате и поставите данните и след това да използвате Find and Replace върху копирания набор от данни.

Вземете първото име

Да предположим, че имате набор от данни, както е показано по -долу и искате да получите само първото име.

По -долу са описани стъпките за това:

1. Копирайте данните за името в колона А и ги поставете в колона Б.
2. Когато данните в колона B са избрани, щракнете върху раздела Начало
3. В групата Редактиране щракнете върху Намиране и избор.
4. Щракнете върху Замяна. Това ще отвори диалоговия прозорец „Намери и замени“.
5. В диалоговия прозорец „Намери и замени“ въведете следното
   1. Намерете какво: * (интервал, последван от символ на звездичка)
   2. Заменете с: оставете това поле празно
6. Кликнете върху Замени всички.

Горните стъпки ще ви дадат първото име и ще премахнат всичко след първото име.

Това работи дори ако имате имена, които имат средно име.

Професионален съвет: Клавишната комбинация за отваряне на диалоговия прозорец Намиране и замяна е Control + H (задръжте клавиша за управление и след това натиснете клавиша H).

Вземете фамилното име

Да предположим, че имате набор от данни, както е показано по -долу и искате да получите само фамилното име.

По -долу са описани стъпките за това:

1. Копирайте данните за името в колона А и ги поставете в колона Б.
2. Когато данните в колона B са избрани, щракнете върху раздела Начало
3. В групата Редактиране щракнете върху Намиране и избор.
4. Щракнете върху Замяна. Това ще отвори диалоговия прозорец „Намери и замени“.
5. В диалоговия прозорец „Намери и замени“ въведете следното
   1. Намерете какво: * (символ на звездичка, последван от интервал)
   2. Заменете с: оставете това поле празно
6. Кликнете върху Замени всички.

Горните стъпки ще ви дадат фамилното име и ще премахнат всичко преди първото име.

Това работи дори ако имате имена, които имат средно име.

Премахнете средното име

В случай, че искате само да се отървете от средното име и да имате само собствено и фамилно име, можете да направите това с помощта на Find and Replace.

Да предположим, че имате набор от данни, както е показано по -долу, и искате да премахнете средното име от тях.

По -долу са описани стъпките за това:

1. Копирайте данните за името в колона А и ги поставете в колона Б.
2. Когато данните в колона B са избрани, щракнете върху раздела Начало
3. В групата Редактиране щракнете върху Намиране и избор.
4. Кликнете върху Замяна. Това ще отвори диалоговия прозорец „Намери и замени“.
5. В диалоговия прозорец „Намери и замени“ въведете следното
   1. Намерете какво: * (интервал, последван от символ на звездичка, последван от интервал)
   2. Заменете с: (просто поставете интервал тук)
6. Кликнете върху Замени всички.

Горните стъпки биха премахнали средното име от пълното име. В случай, че някои имена нямат средно име, те няма да бъдат променени.

Отделни имена с помощта на Flash Fill

Flash запълването е въведено в Excel 2013 и улеснява промяната или почистването на набор от текстови данни.

А що се отнася до разделянето на данни за имената, това е точно в алеята на Flash Fill.

Най -важното нещо, което трябва да знаете, когато използвате Flash Fill, е, че трябва да има модел, който flash fill може да идентифицира. След като идентифицира модела, той лесно ще ви помогне да разделите имена в Excel (ще получите повече яснота по това, когато преминете през няколко примера по -долу).

Вземете името или фамилията от пълното име

Да предположим, че имате набор от данни, както е показано по -долу и искате да получите само първото име.

1. В съседната клетка въведете ръчно първото име от пълното име. В този пример бих написал Рик.
2. Във втората клетка въведете ръчно първото име от името на съседната клетка. Докато пишете, ще видите Flash Fill, който автоматично ви показва списък с първото име (в сиво).
3. Когато видите имената в сиво, бързо прегледайте, за да се уверите, че показва правилните имена. Ако те са правилни, натиснете клавиша enter и Flash Fill автоматично ще запълни останалите клетки с първото име.

Flash Fill се нуждае от вас, за да му дадете модел, който той може да следва, когато ви предоставя променените данни. В нашия пример, когато въведете първото име в първата клетка, Flash Fill не може да разбере модела.

Но веднага щом започнете да въвеждате първото име във втората клетка, Flash Fill разбира модела и ви показва някакво предложение. Ако предложението е правилно, просто натиснете клавиша Enter.

И ако не е правилно, можете да опитате да въведете ръчно в още няколко клетки и да проверите дали Flash Fill е в състояние да различи модела или не.

Понякога може да не видите шаблона в сиво (както е показано в стъпка 2 по -горе). Ако случаят е такъв, следвайте стъпките по -долу, за да получите резултата от Flash Fill:

1. Въведете текста ръчно в две клетки.
2. Изберете и двете клетки
3. Задръжте курсора на мишката в долната дясна част на селекцията. Ще забележите, че курсорът се променя на икона плюс
4. Щракнете двукратно върху него (левия бутон на мишката). Това ще запълни всички клетки. В този момент резултатите вероятно са неправилни и не са това, което очаквате.
5. В долния десен ъгъл на получените данни ще видите малка икона за автоматично попълване. Кликнете върху тази икона за автоматично попълване
6. Щракнете върху Flash Fill

Горните стъпки ще ви дадат резултат от Flash Fill (въз основа на модела, който е извел).

Можете също да използвате Flash Fill, за да получите фамилното или бащиното име. В първите две клетки въведете фамилното име (или средното име) и флаш пълненето ще може да разбере модела

Пренаредете името с помощта на Flash Fill

Flash Fill е интелигентен инструмент и може да дешифрира и леко сложен модел

Да предположим например, че имате набор от данни, както е показано по -долу и искате да пренаредите името от Рик Новак в Новак, Рик (където фамилното име идва първо, последвано от запетая и след това първото име).

По -долу са описани стъпките за това:

1. Въведете ръчно в съседната клетка Новак, Рик
2. Във втората клетка въведете ръчно Конър, Сюзън. Докато пишете, ще видите Flash Fill да ви покаже списък с имената в същия формат (в сиво).
3. Когато видите имената в сиво, бързо прегледайте, за да се уверите, че показва правилните имена. Ако те са правилни, натиснете клавиша enter и Flash Fill автоматично ще запълни останалите клетки с имената в същия формат.

Премахнете средното име (или просто вземете второто име)

Можете също да използвате Flash Fill, за да се отървете от средното име или да получите само второто име.

Да предположим например, че имате набор от данни, както е показано по -долу и искате да получите само първото и фамилното име, а не средното име.

По -долу са описани стъпките за това:

1. Въведете ръчно в съседната клетка Рик Новак
2. Във втората клетка въведете ръчно Сюзън Конър. Докато пишете, ще видите Flash Fill да ви покаже списък с имената в същия формат (в сиво).
3. Когато видите имената в сиво, бързо прегледайте, за да се уверите, че показва правилните имена. Ако те са правилни, натиснете клавиша enter и Flash Fill автоматично ще запълни останалите клетки с имената без средното име.

По същия начин, ако искате да получите само средните имена, въведете средното име в първите две клетки и използвайте Flash Fill, за да получите средното име от всички останали имена.

Примерите, показани в този урок, използват имена, докато манипулират текстовите данни. Можете да използвате същите концепции, за да работите и с други формати на данни (като адреси, имена на продукти и др.)