Перейти к содержимому
Наше приложение «Дешевые авиабилеты» в AppGallery >>

Работа с фамилией, именем, отчеством и инициалами в Excel

    Преобразование имени и отчества в инициалы с помощью формул 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)&"."

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

    95 комментариев для “Работа с фамилией, именем, отчеством и инициалами в Excel”

    1. Наталья

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

      1. Евгений

        Здравствуйте, Наталья!

        A B C
        Иванов Петр Сергеевич Иванов П.С.

        Формула в ячейке C1:
        =A1&" "&ЛЕВСИМВ(B1;1)&"."&ПСТР(B1;НАЙТИ(" ";B1;1)+1;1)&"."

    2. Наталья

      Добрый день. Спасибо большое! Все получилось!
      А вот такой задаче есть решение?
      В одной ячейке записана фамилия, в другой — имя+отчество. Можно ли задать формулы по переводу фамилии и имени+отчества в родительном падеже и в дательном падеже? Запись фамилии — одна ячейка, имя+отчество — другая ячейка.

      1. Евгений

        Добрый день, Наталья!
        Самый простой способ: на отдельном листе в первый столбец записать все встречающиеся у вас фамилии, имена и отчества в именительном падеже, во второй — в родительном, в третий — в дательном, и в документах заменять их с помощью функции ВПР.

    3. Подскажите, пожалуйста, следующее:
      Имеется список сотрудников, Где ФИО указаны в одной строке, как выделить в этом списке сотрудников, у которых у фамилии есть буква «О»?

      1. Евгений

        Здравствуйте, Павел!
        Примените к строке с ФИО условное форматирование. Если нужна первая буква фамилии и фамилия стоит на первом месте, тогда можно так:
        Формула: =(ЛЕВСИМВ(A1)="О")
        Формат: выберите цвет заливки или полужирный шрифт
        Применяется к: =$1:$1 — укажите номер своей строки

        1. Спасибо за ответ!
          Пожалуйста, подскажите, а если требуется не первый символ, а фамилия, которая содержит «О» в любом месте, какую можно использовать формулу?

          1. Евгений

            Формула для «о» и «О» в любом месте ФИО: =(ЕСЛИОШИБКА(ПОИСК("О";A1);0)>0)

            1. Спасибо!
              Вероятно, я не совсем корректно описал задачу.
              Имеется список сотрудников выглядящий примерно так:
              Белова Светлана Валентиновна
              Белокопытов Александр Викторович
              Беляев Михаил Геннадьевич (ФИО написано в одной ячейке)
              Нужно выделить только ту ячейку, в которой фамилия содержит букву «О» в любом месте, будь то начало или середина самой фамилии.
              Ваша формула выше работает, но выделяет также ячейки, в которых имена и отчества, содержащие букву «О», чего делать не нужно.

              1. Евгений

                =ЕСЛИОШИБКА(ПОИСК("О";ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1));0)

    4. Дмитрий

      Здравствуйте!
      Подскажите, пожалуйста, как с помощью формулы поменять инициалы местами, чтобы было так Галанин М.П.

      1. Евгений

        Добрый день, Дмитрий!
        Из какого варианта ФИО должно получиться Галанин М.П.? Из Галанин П.М.?

      2. Евгений

        Будем исходить из предположения, что инициалы всегда состоят из двух символов с точками, иначе, формула будет слишком громоздкой. Преобразование П.М. Галанин (ячейка A1) в Галанин М.П.:

        =СЦЕПИТЬ(ПСТР(A1;6;ДЛСТР(A1)-5);" ";ПСТР(A1;3;2);ЛЕВСИМВ(A1;2))

    5. Аркадий

      Здравствуйте. В ячейках записаны имя отчество фамилия, как сделать фамилия имя отчество?

      1. Евгений

        Здравствуйте, Аркадий!
        =СЦЕПИТЬ(ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1));" ";ПСТР(A1;1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-1))

    6. Доброго времени суток!
      Подскажите пожалуйста, вопрос в следующем в ячейке написано к примеру ИВАНОВ Иван Иванович, как из этого сделать Иванов И.И., чтобы фамилия была с большой а остальной текст был с маленькой буквы?
      Буду крайне признателен

      1. Евгений

        Здравствуйте, Лев!
        Формула для вас:
        =ПРОПНАЧ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)))&" "&ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)&"."&ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1)&"."

    7. Наталья

      Добрый день. Можете помочь? Никак не работает формула по поиску максимального и минимального значения в диапазоне по заданному критерию. Вроде бы задано все правильно, но не считает….
      =МАКС(Лист1!A:A;Лист1!D:D;B35)

      1. Евгений

        Здравствуйте, Наталья!
        Функция МАКС возвращает наибольшее значение из набора значений и не предусматривает использование каких-либо критериев.

    8. ЗДРАВСТВУЙТЕ, а как преобразовать целый список полного ФИО в Фамилия инициалы. Формула =ЛЕВСИМВ(A2;НАЙТИ(" ";A2;1))&" "&ПСТР(A2;НАЙТИ(" ";A2;1)+1;1)&"."&ПСТР(A2;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)+1;1)&"." работает только в строке А2, а таблица большая и менять в формуле А3, А4 и тд долго. Ответьте пожалуйста.

      1. Евгений

        Добрый день, Настя!
        В формуле — относительные ссылки, и при копировании они должны меняться на нужные без ручного редактирования. Протяните ячейку с формулой вниз за черный крестик в правом нижнем углу.

    9. Здравствуйте, подскажите пожалуйста можно ли как то настроить автозаполнения в экселе одинаковых имен и отчеств? Например Иванов Пётр Иванович в одной строчке и Сидоров Пётр Иванович, чтобы имя и отчество он предложил как т9?

      1. Евгений

        Здравствуйте, Мария!
        Мне такая возможность неизвестна. Я для автозаполнения использовал под ФИО три ячейки.

    10. Николай

      Добрый вечер!
      Помогите в решении.
      Имеется список с некорректно заполненными ФИО (например: Николаевна Симонова Ирина)
      Так же имеется список с корректными ФИО.
      Как построить формулу для поиска совпадений некорректной ФИО в корректном списке и вывода правильного варианта?

    11. Надоумьте ещё, пожалуйста, до следующего.
      Вот эта формула превращает в ячейке 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 стоит только «Имя» либо «Фамилия», то формула бы их возвращала? Сейчас в рамках данной формулы, если стоит только Имя либо Фамилия появляется #ЗНАЧ! …

      1. Евгений

        Привет, Antag!
        Для моей формулы это будет выглядеть следующим образом:
        =ЕСЛИОШИБКА(ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1))&" "&ПСТР(B1;НАЙТИ(" ";B1;1)+1;1)&"."&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)&".";B1)

    12. Константин

      Добрый вечер, Евгений!
      Подскажите пожалуйста!
      Как правильно написать формулу извлечения после запятой ФАМИЛИИ И.О.

      В ячейке А1 открывающийся список вот с такими данными
      «Ансамбль народной песни Русские потешки», Новикова Ольга Александровна
      В ячейке В1 должно быть
      Новикова О.А.

      Пробывал формулу
      =ЕСЛИ(ЕОШИБКА(ПСТР(A1;НАЙТИ(",";A1)+1;ДЛСТР(A1)));"";ПСТР(A1;НАЙТИ(",";A1)+1;ДЛСТР(A1)))
      но результат НЕ тот что нужен.
      Помогите пожалуйста исправить, дополнить или изменить на другую формулу.
      Спасибо!

      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)&".";ПСТР(A1;НАЙТИ(",";A1)+2;ДЛСТР(A1)))

    13. Константин

      Доброе утро, Евгений!
      Супер! У Вас получилось!
      Вот только бы к ней добавить маленький момент
      Если в ячейке А1 пусто (ничего не выбрано), то в В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)&".";"")

        1. Константин

          Огромнейшее Вам СПАСИБО!
          Все работает, как и хотели Наши педагоги.
          Вы по ИСТИНЕ делаете доброе дело.
          Спасибо!

    14. Николай

      Добрый вечер. Подскажите пожалуйста ответ. Есть список сотрудников, в котором некоторые сотрудники записаны несколько раз, но с ошибками в имени или фамилии. Иногда имя и фамилия просто поменялись местами (столбцами). Можно ли сделать так, чтобы таблица считала Серова Сергея и Сирова Сергея, как одного и того же человека? И можно ли как-то устранить эти ошибки не прибегая к ручному исправлению каждой позиции. Список пару тысяч сотрудников(((

      1. Евгений

        Здравствуйте, Николай!
        Вашу таблицу можно исправить только вручную.

        1. Николай

          Понял. Грусть, тоска. Спасибо за ответ.

    15. Здравствуйте, подскажите, пожалуйста, мне необходимо привести к единому формату столбец в котором указаны ФИО, которые прописаны с ошибками: Фамилия И.О или Фамилия ИО, Фамилия И.т О., Фамилия И.О.., Фамилия И О. Какую формулу можно написать.

      1. Евгений

        Здравствуйте, Анна!
        Если такая формула и возможна, то она будет очень большая и сложная, так как в ней необходимо прописать все варианты ошибок и способы их исправления.

    16. Здравствуйте, подскажите, мне нужно из ФИО Иванов Иван Иванович превратить в ИвановИИ. =ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1)&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1) — эта формула превращает в Иванов ИИ ,а мне нужно без пробела, уж всю голову разбил, не могу найти способ, а функция заменить пробел на ничего работает некорректно, остается после неё Ива.

      1. Евгений

        Здравствуйте, Иван!
        Используйте такую формулу:
        =ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)-1)&ПСТР(B1;НАЙТИ(" ";B1;1)+1;1)&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)

        1. я нашёл еще такой способ, но ваш мне нравится больше
          =ЗАМЕНИТЬ(ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1)&""&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1)&"";НАЙТИ(" ";B1);1;"")

          1. Евгений

            Отличное решение. Если убрать 6 лишних символов, то длина формулы станет, как у моей:
            =ЗАМЕНИТЬ(ЛЕВСИМВ(B1;НАЙТИ(" ";B1;1)+1)&ПСТР(B1;НАЙТИ(" ";B1;НАЙТИ(" ";B1;1)+1)+1;1);НАЙТИ(" ";B1);1;"")

    17. Ольга Плаксина

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

      1. Евгений

        Здравствуйте, Ольга!
        Я не вижу решения такой задачи с помощью формул.

    Обсуждение закрыто.