Преобразование имени и отчества в инициалы с помощью формул Excel и присоединение их к фамилии. Разделение фамилий, имен и отчеств, расположенных в одной ячейке, по разным ячейкам и наоборот.
Фамилия, имя и отчество в разных ячейках
Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:
- при заполнении таких ячеек в таблицах срабатывает автоподбор значений, так как имена и отчества часто повторяются;
- для заполнения таких ячеек можно использовать раскрывающиеся списки наиболее распространенных имен и отчеств;
- фамилия, имя и отчество, записанные в разные ячейки, легко объединить в одну, а имя и отчество заменить инициалами.
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";B1;" ";C1), или просто соединить строки с помощью & (амперсандов) =A1&" "&B1&" "&C1, не забыв добавить между словами пробелы.
В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:
Здесь также можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1;1);".";ЛЕВСИМВ(C1;1);".") или & (амперсанды) =A1&" "&ЛЕВСИМВ(B1;1)&"."&ЛЕВСИМВ(C1;1)&".", не забыв добавить между фамилией и инициалами пробел, а к инициалам точки. В этом примере мы извлекаем левые символы из имени и отчества для присоединения их к фамилии в виде инициалов.
Фамилия, имя и отчество в одной ячейке
Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:
- уменьшается количество колонок в таблице;
- в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.
*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.
Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:
Для этого преобразования используются следующие формулы в соответствующих ячейках:
- ячейка B1 —
=ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1) - ячейка C1 —
=ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1) - ячейка D1 —
=ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1))
Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».
Необходимость следующего преобразования возникает чаще предыдущего, используется для заполнения различных документов наряду с полным именем:
Для этого преобразования используется следующая формула в ячейке B1 — =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")
Здесь тоже функцию «СЦЕПИТЬ» можно заменить & (амперсандами) — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1))&" "&ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)&"."&ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1)&"."
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Добрый день. Помогите, пожалуйста, в одной ячейке содержится фамилия, в другой имя отчество, как задать формулу в третью ячейку, чтобы фамилия была указан полностью, а имя-отчество инициалами.
Здравствуйте, Наталья!
Формула в ячейке C1:
=A1&" "&ЛЕВСИМВ(B1;1)&"."&ПСТР(B1;НАЙТИ(" ";B1;1)+1;1)&"."Добрый день. Спасибо большое! Все получилось!
А вот такой задаче есть решение?
В одной ячейке записана фамилия, в другой — имя+отчество. Можно ли задать формулы по переводу фамилии и имени+отчества в родительном падеже и в дательном падеже? Запись фамилии — одна ячейка, имя+отчество — другая ячейка.
Добрый день, Наталья!
Самый простой способ: на отдельном листе в первый столбец записать все встречающиеся у вас фамилии, имена и отчества в именительном падеже, во второй — в родительном, в третий — в дательном, и в документах заменять их с помощью функции ВПР.
Подскажите, пожалуйста, следующее:
Имеется список сотрудников, Где ФИО указаны в одной строке, как выделить в этом списке сотрудников, у которых у фамилии есть буква «О»?
Здравствуйте, Павел!
Примените к строке с ФИО условное форматирование. Если нужна первая буква фамилии и фамилия стоит на первом месте, тогда можно так:
Формула:
=(ЛЕВСИМВ(A1)="О")Формат: выберите цвет заливки или полужирный шрифт
Применяется к:
=$1:$1— укажите номер своей строкиСпасибо за ответ!
Пожалуйста, подскажите, а если требуется не первый символ, а фамилия, которая содержит «О» в любом месте, какую можно использовать формулу?
Формула для «о» и «О» в любом месте ФИО:
=(ЕСЛИОШИБКА(ПОИСК("О";A1);0)>0)Спасибо!
Вероятно, я не совсем корректно описал задачу.
Имеется список сотрудников выглядящий примерно так:
Белова Светлана Валентиновна
Белокопытов Александр Викторович
Беляев Михаил Геннадьевич (ФИО написано в одной ячейке)
Нужно выделить только ту ячейку, в которой фамилия содержит букву «О» в любом месте, будь то начало или середина самой фамилии.
Ваша формула выше работает, но выделяет также ячейки, в которых имена и отчества, содержащие букву «О», чего делать не нужно.
=ЕСЛИОШИБКА(ПОИСК("О";ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1));0)Здравствуйте!
Подскажите, пожалуйста, как с помощью формулы поменять инициалы местами, чтобы было так Галанин М.П.
Добрый день, Дмитрий!
Из какого варианта ФИО должно получиться Галанин М.П.? Из Галанин П.М.?
Из П.М. Галанин
Будем исходить из предположения, что инициалы всегда состоят из двух символов с точками, иначе, формула будет слишком громоздкой. Преобразование П.М. Галанин (ячейка A1) в Галанин М.П.:
=СЦЕПИТЬ(ПСТР(A1;6;ДЛСТР(A1)-5);" ";ПСТР(A1;3;2);ЛЕВСИМВ(A1;2))Евгений, огромное Вам спасибо!
Здравствуйте. В ячейках записаны имя отчество фамилия, как сделать фамилия имя отчество?
Здравствуйте, Аркадий!
=СЦЕПИТЬ(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1));" ";ПСТР(A1;1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-1))Доброго времени суток!
Подскажите пожалуйста, вопрос в следующем в ячейке написано к примеру ИВАНОВ Иван Иванович, как из этого сделать Иванов И.И., чтобы фамилия была с большой а остальной текст был с маленькой буквы?
Буду крайне признателен
Здравствуйте, Лев!
Формула для вас:
=ПРОПНАЧ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)))&" "&ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)&"."&ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1)&"."Добрый день. Можете помочь? Никак не работает формула по поиску максимального и минимального значения в диапазоне по заданному критерию. Вроде бы задано все правильно, но не считает….
=МАКС(Лист1!A:A;Лист1!D:D;B35)
Здравствуйте, Наталья!
Функция МАКС возвращает наибольшее значение из набора значений и не предусматривает использование каких-либо критериев.
ЗДРАВСТВУЙТЕ, а как преобразовать целый список полного ФИО в Фамилия инициалы. Формула
=ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1))&" "&ПСТР(A2;НАЙТИ(" ";A2;1)+1;1)&"."&ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)+1;1)&"."работает только в строке А2, а таблица большая и менять в формуле А3, А4 и тд долго. Ответьте пожалуйста.Добрый день, Настя!
В формуле — относительные ссылки, и при копировании они должны меняться на нужные без ручного редактирования. Протяните ячейку с формулой вниз за черный крестик в правом нижнем углу.
Здравствуйте, подскажите пожалуйста можно ли как то настроить автозаполнения в экселе одинаковых имен и отчеств? Например Иванов Пётр Иванович в одной строчке и Сидоров Пётр Иванович, чтобы имя и отчество он предложил как т9?
Здравствуйте, Мария!
Мне такая возможность неизвестна. Я для автозаполнения использовал под ФИО три ячейки.
Добрый вечер!
Помогите в решении.
Имеется список с некорректно заполненными ФИО (например: Николаевна Симонова Ирина)
Так же имеется список с корректными ФИО.
Как построить формулу для поиска совпадений некорректной ФИО в корректном списке и вывода правильного варианта?
Надоумьте ещё, пожалуйста, до следующего.
Вот эта формула превращает в ячейке B1 из «Фамилия Имя Отчество» в «Фамилия И.О.» –
=ЕСЛИ(Сводная!F6>0;ЛЕВСИМВ(B1;НАЙТИ(СИМВОЛ(32);B1))&ЕСЛИ(ДЛСТР(B1)-ДЛСТР(ПОДСТАВИТЬ(B1;СИМВОЛ(32);""))=1;ПСТР(B1;НАЙТИ(СИМВОЛ(32);B1)+1;1);ПСТР(B1;НАЙТИ(СИМВОЛ(32);B1)+1;1)&". "&ПСТР(B1;НАЙТИ(СИМВОЛ(32);B1;НАЙТИ(СИМВОЛ(32);B1)+1)+1;1))&".";"")А можно дописать формулу, чтобы если в ячейке B1 стоит только «Имя» либо «Фамилия», то формула бы их возвращала? Сейчас в рамках данной формулы, если стоит только Имя либо Фамилия появляется #ЗНАЧ! …
Привет, Antag!
Для моей формулы это будет выглядеть следующим образом:
=ЕСЛИОШИБКА(ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1))&" "&ПСТР(B1;НАЙТИ(" ";B1;1)+1;1)&"."&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)&".";B1)Добрый вечер, Евгений!
Подскажите пожалуйста!
Как правильно написать формулу извлечения после запятой ФАМИЛИИ И.О.
В ячейке А1 открывающийся список вот с такими данными
«Ансамбль народной песни Русские потешки», Новикова Ольга Александровна
В ячейке В1 должно быть
Новикова О.А.
Пробывал формулу
=ЕСЛИ(ЕОШИБКА(ПСТР(A1;НАЙТИ(",";A1)+1;ДЛСТР(A1)));"";ПСТР(A1;НАЙТИ(",";A1)+1;ДЛСТР(A1)))но результат НЕ тот что нужен.
Помогите пожалуйста исправить, дополнить или изменить на другую формулу.
Спасибо!
=ЕСЛИОШИБКА(ЛЕВСИМВ(ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));1))&" "&ПСТР(ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));1)+1;1)&"."&ПСТР(ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));1)+1)+1;1)&".";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1)))Доброе утро, Евгений!
Супер! У Вас получилось!
Вот только бы к ней добавить маленький момент
Если в ячейке А1 пусто (ничего не выбрано), то в В1 тоже должно быть пусто.
Спасибо большое!
=ЕСЛИОШИБКА(ЛЕВСИМВ(ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));1))&" "&ПСТР(ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));1)+1;1)&"."&ПСТР(ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));НАЙТИ(" ";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1));1)+1)+1;1)&".";"")Огромнейшее Вам СПАСИБО!
Все работает, как и хотели Наши педагоги.
Вы по ИСТИНЕ делаете доброе дело.
Спасибо!
Добрый вечер. Подскажите пожалуйста ответ. Есть список сотрудников, в котором некоторые сотрудники записаны несколько раз, но с ошибками в имени или фамилии. Иногда имя и фамилия просто поменялись местами (столбцами). Можно ли сделать так, чтобы таблица считала Серова Сергея и Сирова Сергея, как одного и того же человека? И можно ли как-то устранить эти ошибки не прибегая к ручному исправлению каждой позиции. Список пару тысяч сотрудников(((
Здравствуйте, Николай!
Вашу таблицу можно исправить только вручную.
Понял. Грусть, тоска. Спасибо за ответ.
Здравствуйте, подскажите, пожалуйста, мне необходимо привести к единому формату столбец в котором указаны ФИО, которые прописаны с ошибками: Фамилия И.О или Фамилия ИО, Фамилия И.т О., Фамилия И.О.., Фамилия И О. Какую формулу можно написать.
Здравствуйте, Анна!
Если такая формула и возможна, то она будет очень большая и сложная, так как в ней необходимо прописать все варианты ошибок и способы их исправления.
Здравствуйте, подскажите, мне нужно из ФИО Иванов Иван Иванович превратить в ИвановИИ.
=ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1)&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)— эта формула превращает в Иванов ИИ ,а мне нужно без пробела, уж всю голову разбил, не могу найти способ, а функция заменить пробел на ничего работает некорректно, остается после неё Ива.Здравствуйте, Иван!
Используйте такую формулу:
=ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)-1)&ПСТР(B1;НАЙТИ(" ";B1;1)+1;1)&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)Большое спасибо
я нашёл еще такой способ, но ваш мне нравится больше
=ЗАМЕНИТЬ(ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1)&""&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)&"";НАЙТИ(" ";B1);1;"")Отличное решение. Если убрать 6 лишних символов, то длина формулы станет, как у моей:
=ЗАМЕНИТЬ(ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1)&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1);НАЙТИ(" ";B1);1;"")подскажите а есть ли способ для такой задачи. в таблице список и там имя и фамилия, но написаны по разному кто то имя фамилия кто то фамилия и имя. можно ли автоматически сделать что бы они стали начинаться все с имени.
Здравствуйте, Ольга!
Я не вижу решения такой задачи с помощью формул.
спасибо большое за ответ!
Обсуждение закрыто.