Разделите имя и фамилию в Excel (разделите имена с помощью формул)

Посмотреть видео - Разделение имен в Excel (на имя, отчество и фамилию)

Excel - отличный инструмент для нарезки текстовых данных.

Существует так много полезных формул, а также функций, которые вы можете использовать для работы с текстовыми данными в Excel.

Один из самых распространенных вопросов, которые я задаю об управлении текстовыми данными: «Как разделить имя и фамилию (или имя, отчество и фамилию) в Excel?“.

Есть несколько простых способов разделить имена в Excel. Выбранный вами метод будет зависеть от того, как структурированы ваши данные, и от того, хотите ли вы, чтобы результат был статическим или динамическим.

Итак, давайте начнем и рассмотрим различные способы разделения имен в Excel.

Разделить имена с помощью текста на столбцы

Функция преобразования текста в столбцы в Excel позволяет быстро разбивать текстовые значения на отдельные ячейки в строке.

Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите разделить имя и фамилию и поместить их в отдельные ячейки.

Ниже приведены шаги по разделению имени и фамилии с помощью текста в столбцы:

1. Выберите все имена в столбце (A2: A10 в этом примере)
2. Перейдите на вкладку «Данные».
3. В группе «Инструменты для работы с данными» выберите вариант «Текст в столбцы».
4. Внесите следующие изменения в мастере преобразования текста в столбец:
   1. Шаг 1 из 3: выберите с разделителями (это позволяет использовать пробел в качестве разделителя) и нажмите «Далее».
   2. Шаг 2 из 3: Выберите опцию Пробел и нажмите Далее.
   3. Шаг 3 из 3: установите B2 как целевую ячейку (иначе существующие данные будут перезаписаны)
5. Нажмите Готово

Вышеупомянутые шаги мгновенно разделят имена на имя и фамилию (с именами в столбце B и фамилией в столбце C).

Примечание. Если в ячейках уже есть какие-либо данные (те, где ожидается вывод текста в столбцы), Excel покажет вам предупреждение, сообщающее, что в ячейках уже есть некоторые данные. Вы можете перезаписать данные или отменить текст в столбцы и сначала удалить его вручную.

После этого вы можете удалить данные полного имени, если хотите.

Несколько вещей, которые нужно знать при использовании текста в столбцы для разделения имени и фамилии в Excel:

1. Результат статичен. Это означает, что в случае, если у вас есть новые данные или в исходных данных есть некоторые изменения, вам нужно сделать это снова, чтобы разделить имена.
2. Если вам нужно только имя или только фамилия, вы можете пропустить ненужные столбцы на шаге 3 диалогового окна «Text to Column Wizard». Для этого выберите столбец в предварительном просмотре, который вы хотите пропустить, а затем выберите опцию «Не импортировать столбец (пропустить)».
3. Если вы не укажете целевую ячейку, текст в столбец перезапишет текущий столбец.

Параметр «Текст в столбцы» лучше всего подходит, когда у вас есть согласованные данные (например, все имена имеют только имя и фамилию или все имена имеют имя, отчество и фамилию).

В этом примере я показал вам, как разделять имена с пробелом в качестве разделителя. Если разделителем является запятая или комбинация запятой и пробела, вы все равно можете использовать те же шаги. В этом случае вы можете указать разделитель на шаге 2 из 3 мастера. Уже есть возможность использовать запятую в качестве разделителя, или вы можете выбрать вариант «Другое» и указать собственный разделитель.

Хотя текст в столбцы - это быстрый и эффективный способ разделения имен, он подходит только тогда, когда вы хотите, чтобы вывод был статическим. Если у вас есть набор данных, который может расширяться или изменяться, вам лучше использовать формулы для разделения имен.

Разделите имя, отчество и фамилию с помощью формул

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

В этом разделе я поделюсь различными формулами, которые вы можете использовать для разделения данных имен (в зависимости от того, как структурированы ваши данные).

Ниже приведены три формулы, которые вы можете использовать для разделения имени, отчества и фамилии (более подробно они объяснены позже в следующих разделах).

Формула для получения имени:

= ЛЕВЫЙ (A2; ПОИСК (""; A2) -1)

Формула получения отчества:

= MID (A2; ПОИСК (""; A2) + 1, ПОИСК ("", ПОДСТАВИТЬ (A2, "", "@", 1)) - ПОИСК ("", A2))

Формула получения фамилии:

= ВПРАВО (A15; LEN (A15) -ПОИСК ("@", ПОДСТАВИТЬ (A15, "", "@", LEN (A15) -LEN (ПОДСТАВИТЬ (A15, "", "")))))

Получите имя

Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро разделить имя в одной ячейке и фамилию в одной ячейке.

Приведенная ниже формула даст вам имя:

= ЛЕВЫЙ (A2; ПОИСК (""; A2) -1)

В приведенной выше формуле используется функция ПОИСК, чтобы получить положение символа пробела между именем и фамилией. Затем функция LEFT использует этот номер позиции пробела для извлечения всего текста перед ним.

Это довольно простой способ извлечения части текстового значения. Поскольку все, что нам нужно сделать, это определить позицию первого пробела, не имеет значения, есть ли в имени какое-либо отчество или нет. Вышеупомянутая формула будет работать нормально.

Теперь давайте рассмотрим каждый пример более подробно.

Получить фамилию

Допустим, у вас такой же набор данных, и на этот раз вам нужно получить фамилию.

Приведенная ниже формула извлечет фамилию из указанного выше набора данных:

= ВПРАВО (A2; LEN (A2) -ПОИСК (""; A2))

Опять же, довольно просто.

На этот раз мы сначала находим позицию символа пробела, которая затем используется для определения количества символов, оставшихся после пробела (которое будет фамилией).

Это достигается путем вычитания значения позиции символа пробела из общего количества символов в имени.

Этот номер затем используется в функции ВПРАВО для извлечения всех этих символов справа от имени.

Хотя эта формула отлично работает, когда есть только имя и фамилия, она не будет работать, если у вас также есть отчество. Это потому, что мы учли только один пробел (между именем и фамилией). При наличии отчества к имени добавляется больше пробелов.

Чтобы получить фамилию, когда у вас есть отчество, используйте следующую формулу:

= ВПРАВО (A15; LEN (A15) -ПОИСК ("@", ПОДСТАВИТЬ (A15, "", "@", LEN (A15) -LEN (ПОДСТАВИТЬ (A15, "", "")))))

Теперь это стало немного сложнее… не так ли?

Позвольте мне объяснить, как это работает.

Приведенная выше формула сначала находит общее количество пробелов в имени. Это делается путем получения длины имени с пробелом и без него, а затем вычитания имени без пробела из имени с пробелом. Это дает общее количество пробелов.

Затем функция SUBSTITUTE используется для замены последнего символа пробела символом «@» (вы можете использовать любой символ - что-то, что вряд ли может быть частью имени).

После того, как символ @ был заменен на последний пробел, вы можете легко найти положение этого символа @. Это делается с помощью функции ПОИСК.

Теперь все, что вам нужно сделать, это извлечь все символы справа от этого символа @. Это делается с помощью функции ВПРАВО.

Получите отчество

Предположим, у вас есть набор данных, показанный ниже, и вы хотите извлечь отчество.

Следующая формула сделает это:

= MID (A2; ПОИСК (""; A2) + 1, ПОИСК ("", ПОДСТАВИТЬ (A2, "", "@", 1)) - ПОИСК ("", A2))

В приведенной выше формуле используется функция MID, которая позволяет указать начальную позицию и количество символов, извлекаемых из этой позиции.

Начальную позицию легко найти с помощью функции ПОИСК.

Сложнее всего определить, сколько символов извлечь после этой начальной позиции. Чтобы получить это, вам нужно определить, сколько символов существует от начальной позиции до последнего символа пробела.

Это можно сделать, используя функцию ЗАМЕНА и заменив последний пробел символом «@». Как только это будет сделано, вы можете легко использовать функцию ПОИСК, чтобы найти положение этого последнего символа пробела.

Теперь, когда у вас есть начальная позиция и позиция последнего пробела, вы можете легко получить отчество с помощью функции MID.

Одним из преимуществ использования формулы для разделения имен является то, что результат является динамическим. Так что в случае, если ваш набор данных расширяется и к нему добавляются новые имена или если некоторые имена изменяются в исходном наборе данных, вам не нужно беспокоиться о полученных данных.

Разделяйте имена с помощью поиска и замены

Мне нравится гибкость функции «Найти и заменить», потому что в ней можно использовать символы подстановки.

Сначала позвольте мне объяснить, что такое подстановочный знак.

Подстановочный знак - это то, что вы можете использовать вместо любого текста. Например, вы можете использовать символ звездочки (*), и он будет представлять любое количество символов в Excel. Чтобы дать вам пример, если я хочу найти все имена, начинающиеся с алфавита A, я могу использовать A * в поиске и замене. Это найдет и выберет все ячейки, имя которых начинается с A.

Если вам все еще непонятно, не волнуйтесь. Продолжайте читать, и следующие несколько примеров прояснят, что такое подстановочные знаки и как их использовать для быстрого разделения имен (или любых текстовых значений в Excel).

Во всех приведенных ниже примерах убедитесь, что вы создали резервную копию набора данных. Найти и заменить изменяет используемые данные. Лучше сначала скопировать и вставить данные, а затем использовать «Найти и заменить» для скопированного набора данных.

Получите имя

Предположим, у вас есть набор данных, показанный ниже, и вы хотите получить только имя.

Ниже приведены шаги для этого:

1. Скопируйте данные имени в столбец A и вставьте их в столбец B.
2. Выбрав данные в столбце B, перейдите на вкладку «Главная».
3. В группе редактирования нажмите «Найти и выбрать».
4. Щелкните "Заменить". Откроется диалоговое окно «Найти и заменить».
5. В диалоговом окне «Найти и заменить» введите следующее
   1. Найдите что: * (пробел, за которым следует символ звездочки)
   2. Заменить на: оставьте это поле пустым
6. Нажмите «Заменить все».

Вышеупомянутые шаги дадут вам имя и удалят все после имени.

Это работает, даже если у вас есть имена с отчеством.

Совет профессионала: Сочетание клавиш для открытия диалогового окна "Найти и заменить": Ctrl + H (удерживайте клавишу управления, а затем нажмите клавишу H).

Получить фамилию

Предположим, у вас есть набор данных, показанный ниже, и вы хотите получить только фамилию.

Ниже приведены шаги для этого:

1. Скопируйте данные имени в столбец A и вставьте их в столбец B.
2. Выбрав данные в столбце B, перейдите на вкладку «Главная».
3. В группе редактирования нажмите «Найти и выбрать».
4. Щелкните "Заменить". Откроется диалоговое окно «Найти и заменить».
5. В диалоговом окне «Найти и заменить» введите следующее
   1. Найдите что: * (символ звездочки, за которым следует пробел)
   2. Заменить на: оставьте это поле пустым
6. Нажмите «Заменить все».

Вышеупомянутые шаги дадут вам фамилию и удалят все, что находится перед именем.

Это работает, даже если у вас есть имена с отчеством.

Удалить отчество

Если вы хотите избавиться только от отчества и иметь только имя и фамилию, вы можете сделать это с помощью функции «Найти и заменить».

Предположим, у вас есть набор данных, как показано ниже, и вы хотите удалить из него отчество.

Ниже приведены шаги для этого:

1. Скопируйте данные имени в столбец A и вставьте их в столбец B.
2. Выбрав данные в столбце B, перейдите на вкладку «Главная».
3. В группе редактирования нажмите «Найти и выбрать».
4. Щелкните "Заменить". Откроется диалоговое окно «Найти и заменить».
5. В диалоговом окне «Найти и заменить» введите следующее
   1. Найдите что: * (пробел, за которым следует символ звездочки, за которым следует пробел)
   2. Заменить на: (просто введите здесь пробел)
6. Нажмите «Заменить все».

Приведенные выше шаги удаляют отчество из полного имени. Если у некоторых имен нет отчества, они не будут изменены.

Разделяйте имена с помощью Flash Fill

Флэш-заливка была введена в Excel 2013 и позволяет очень легко изменять или очищать набор текстовых данных.

А когда дело доходит до разделения данных об именах, это прямо на пути к Flash Fill.

Самая важная вещь, которую нужно знать при использовании Flash Fill, - это то, что должен быть узор, который может идентифицировать флэш-заливка. Как только он определит шаблон, он легко поможет вам разделить имена в Excel (вы получите больше ясности по этому поводу, когда рассмотрите несколько примеров ниже).

Получите имя или фамилию из полного имени

Предположим, у вас есть набор данных, как показано ниже, и вы хотите получить только имя.

1. В соседней ячейке вручную введите имя из полного имени. В этом примере я бы напечатал Рик.
2. Во второй ячейке вручную введите имя из имени соседней ячейки. Пока вы набираете текст, вы увидите, что Flash Fill автоматически покажет вам список имен (серым цветом).
3. Когда вы видите имена в сером цвете, быстро просмотрите их, чтобы убедиться, что они отображают правильные имена. Если они верны, нажмите клавишу ввода, и Flash Fill автоматически заполнит остальные ячейки первым именем.

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

Но как только вы начнете вводить имя во второй ячейке, Flash Fill поймет шаблон и покажет вам несколько предложений. Если предложение верное, просто нажмите клавишу ввода.

А если это не так, вы можете попробовать ввести вручную еще несколько ячеек и проверить, может ли 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. Когда вы видите имена в сером цвете, быстро просмотрите их, чтобы убедиться, что они отображают правильные имена. Если они верны, нажмите клавишу ввода, и Flash Fill автоматически заполнит остальные ячейки именами в том же формате.

Удалите отчество (или просто получите отчество)

Вы также можете использовать Flash Fill, чтобы избавиться от отчества или получить только отчество.

Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите получить только имя и фамилию, а не отчество.

Ниже приведены шаги для этого:

1. В соседней ячейке введите вручную Рик Новак
2. Во второй ячейке введите вручную Сьюзан Коннор. Пока вы набираете текст, вы увидите, что Flash Fill покажет вам список имен в том же формате (серым цветом).
3. Когда вы видите имена в сером цвете, быстро просмотрите их, чтобы убедиться, что отображаются правильные имена. Если они верны, нажмите клавишу ввода, и Flash Fill автоматически заполнит остальные ячейки именами без отчества.

Точно так же, если вы хотите получить только отчество, введите отчество в первых двух ячейках и используйте Flash Fill, чтобы получить отчество из всех оставшихся имен.

Примеры, показанные в этом руководстве, используют имена при манипулировании текстовыми данными. Вы можете использовать те же концепции для работы с другими форматами данных (такими как адреса, названия продуктов и т. Д.)