Работа с фамилией, именем, отчеством и инициалами в 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)&"."

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

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

    1. Наталья

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

      1. Евгений

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

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

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

    2. Наталья

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

      1. Евгений

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

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

      1. Евгений

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

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

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

    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. Николай

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

    Добавить комментарий

    Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора.