Как получить Ф. И. О. из полных фамилии, имени и отчества
Приветствую всех сотрудников «Б & K»! Я ваш постоянный читатель. Выписываю газету уже более двух лет. В любом разделе нахожу для себя массу полезной информации. Очень помогли освоить компьютер материалы по «1С», Excel, живые рубрики типа вопрос-ответ. Благодаря «Б & K» я стал постоянным жителем Интернета. Но сейчас у меня сугубо практический вопрос. Есть большая база в Excel. В одной колонке этой базы находятся фамилии, имена и отчества сотрудников (полностью). Например, «Иванов Иван Иванович». Мне нужно получить их фамилии и инициалы. То есть вместо «Иванов Иван Иванович» написать «Иванов И. И.». Подскажите, как быстро справиться с такой задачей? Корректировать несколько тысяч фамилий очень не хочется! Заранее благодарю.
В. А. Волков, г. Харьков
Николай КАРПЕНКО , канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства
У этой задачи, как минимум, два решения. Можно написать небольшую программу на языке VBA (Visual Basic for Application). Но этот путь не самый удачный. Для работы с текстами, для записи результатов в ячейки придется оперировать
объектами, свойствами, методами. Для программиста такая работа проще пареной репы. А вот бухгалтеру она непривычна. Мы пойдем другим путем. Решим задачу с помощью формул. Они получатся длинными. Но если использовать правильную технику и строить формулы по частям, то никакие сложности нам не страшны. Подробно о работе с формулами можно прочитать в наших статьях, посвященных Excel: «Формулы — душа таблицы» (см. «Б & К», 2006, № 1) и «Excel 2007: формулы без проблем» (см. «Б & К», 2009, № 9). Тем не менее я постараюсь описать процесс создания формул подробно, насколько позволит объем этой статьи. Приступим.
На рис. 1 показан рабочий лист нашего примера. В ячейке «
A1» этого листа записан текст «Иванов Вадим Петрович». На первом этапе мы в отдельных колонках рабочего листа последовательно получим формулы для определения фамилии «Иванов», а также инициалов «В.» и «П.», а потом объединим полученные результаты в одну длинную формулу.
Нам понадобятся встроенные функции для работы с текстом. Их много, я остановил свой выбор на следующих.
«Найти(Текст1;Текст2;НачПоз)» находит заданную строку в тексте и возвращает положение искомой строки относительно крайнего левого символа в этом тексте. Синтаксис функции такой: «Найти(Текст1;Текст2;НачПоз)», где «Текст1» — это то, что мы ищем, «Текст2» — это строка, в которой мы ищем. Параметр «НачПоз» — это номер символа в «Текст2», с которого начинается поиск. По умолчанию параметр «НачПоз» равен «1». Пример: формула «=Найти(«о»;»Иванов»;1)» возвратит значение «5». Это означает, что в слове «Иванов» символ «о» находится на пятой позиции.
Для поиска вхождений одной строки в другую можно использовать и функцию «Поиск». Между этими функциями есть отличия. Так, «Найти» учитывает регистр символов и не допускает использования подстановочных знаков (или так называемых шаблонов). Функция «Поиск» не учитывает регистр, зато в ней можно пользоваться подставочными знаками. Их вводят вместо строки «Текст1». Например, знак вопроса («?») в шаблоне означает, что на этом месте может стоять любой символ. Знак звездочка («*») соответствует любой последовательности символов. Если нужно найти один из подстановочных знаков, перед ними в шаблоне нужно поставить знак тильда («~»).
Для наших целей достаточно функции «
Найти()». При работе с текстом нам придется вырезать из него отдельные куски, чтобы получить из них инициалы. Для таких действий я предлагаю использовать функцию «ПСтр()». У нее три параметра: «Текст», «НачПоз» и «КолЗн». Функция возвращает указанное число знаков «КолЗн» из строки «Текст», начиная с позиции «НачПоз». Например, формула «=ПСтр(«Иванов»;2;4)» вернет результат «вано». Комбинируя эти две функции, мы и решим задачу. Приступим:
B1» запишите формулу «=Найти(» «;A1;1)». Первым параметром в формуле записан пробел! Вручную печатать ничего не нужно. Для создания формулы используйте Мастер функций. Второй параметр — полное имя (фамилия, имя, отчество). Формула находит в полном имени (в данном случае это строка «Иванов Вадим Петрович») первый пробел. Он находится между текстом «Иванов» и «Вадим». Позиция этого пробела в исходном тексте равна «7». Чтобы выделить из текста фамилию «Иванов», нужно вырезать из исходной строки символы с первого по седьмой. То есть до позиции, которую определила формула «=Найти(» «;A1;1)». Сделаем это;
С1» запишем формулу «=Пстр(A1;1;Найти(» «;A1;1))». Результат работы этой формулы — текст «Иванов » (рис. 1). Завершающий пробел в этом слове я оставил специально. Теперь нужно вырезать из исходной строки имя и отчество. То есть из полного имени нужно взять часть, начиная с позиции «8» и до конца строки. Позиция «8» — это не что иное, как местоположение первого пробела плюс сдвиг на один символ вправо. То есть формула для определения первого символа имени будет такой: «=Найти(» «;A1;1)+1». Длину строки нам поможет определить функция «ДлСтр()». Все составляющие формулы понятны, можно продолжить;
D1» пишем «=Пстр(A1;Найти(» «;A1;1)+1;Длстр(A1))». Результат ее работы — текст «Вадим Петрович». Из этого текста нужно вырезать первый символ и добавить к этому символу знак «.» (точка). Для этого делаем так;
5) теперь разберемся с отчеством. Последовательность действий будет похожа на предыдущую. Вначале выделяем из текста «
Вадим Петрович» слово «Петрович». Признак начала слова «Петрович» очевиден — это пробел. Поэтому нам достаточно определить местоположение этого пробела и затем вырезать соответствующую часть строки. Чтобы определить начало слова «Петрович», в ячейку «F1» вводим формулу: «=Найти (» «;D1;1)». Результатом ее работы будет число «6». Все верно. В строке «Вадим Петрович» слово «Петрович» начинается с седьмой позиции (т. е. с позиции «6+1»);
G1» вводим формулу «=Пстр(D1;F1+1;Длстр(D1))». Она вернет отчество — «Петрович»;
I1» собираем результат в одно целое. Для этого в «I1» пишем: «=C1&E1&H1». Результат ее работы — текст «Иванов В.П.». Что и требовалось сделать.
А теперь переходим к самой интересной части. Попытаемся объединить наши усилия в одной-единственной формуле. Она получится большая. Но получить ее несложно. Нужно только делать все внимательно. Напомню, что
формула — это обычный текст. И оперировать с ней можно как текстовой строкой: выполнять замену символов, копировать, переносить и удалять фрагменты и т. п. Все составляющие для обобщающей формулы у нас есть. Только сейчас они находятся в разных ячейках. Но, задействовав буфер обмена, мы объединим их в считанные минуты.
наша цель. Берем за основу конечную формулу «=C1&E1&H1». В нее последовательно подставляем выражения из ячеек «C1», «E1», «H1». В результате получим некоторый промежуточный вариант, в котором появятся ссылки на ячейки «D1», «G1». Вместо этих ссылок снова подставим тексты формул из соответствующих ячеек. Так продолжаем до тех пор, пока в формуле не останутся только ссылки на ячейку «A1» (полное имя сотрудника). Приступим:
1) переходим к ячейке «
C1». Нажимаем «F2» (режим редактирования содержимого). Станет доступно окошко с содержимым ячейки;
текст «Пстр(A1;1;Найти (» «;A1;1))». Символ «=» выделять не нужно!
Ctrl+C» копируем текст в буфер обмена;
Esc», чтобы вернуться на рабочий лист;
J1», нажимаем «F2». В «J1» мы построим окончательный вариант формулы;
7) переходим к ячейке «
8) выделяем текст «
Ctrl+C» копируем текст в буфер обмена;
Esc», чтобы вернуться на рабочий лист;
13) переходим к ячейке «
14) выделяем текст «
Пстр(G1;1;1)&».»». Символ «=» в формуле выделять не нужно!
Ctrl+C» копируем текст в буфер обмена;
Esc», чтобы вернуться на рабочий лист;
19) переходим к ячейке «
20) выделяем из формулы текст «
Пстр(A1;Найти(» «;A1;1)+1;Длстр(A1))» (без символа «=»);
Ctrl+C» копируем его в буфер об-мена;
Esc», возвращаемся на рабочий лист;
25) переходим к ячейке «
26) выделяем текст «
Ctrl+C» копируем текст в буфер обмена;
31) становимся на «
32) копируем в буфер текст «
34) становимся на ячейку «
35) копируем в буфер обмена текст «
38) становимся на ячейку «
39) копируем в буфер обмена текст «
=Пстр(A1;1;Найти(» «;A1;1))&Пстр(Пстр(A1;Найти(» «;A1;1)+1; Длстр(A1));1;1)&».»&Пстр(Пстр(Пстр(A1;Найти(» «;A1;1)+1;Длстр(A1));Найти(» «;D1;1)+1; Длстр(Пстр(A1;Найти(» «;A1;1)+1;Длстр(A1))));1;1)&».»» заменяем ссылку «D1» на содержимое буфера обмена.
Окончательный вариант формулы выглядит так: «
=Пстр(A1;1;Найти(» «;A1;1)) & Пстр(Пстр(A1;Найти(» «;A1;1)+1;Длстр(A1));1;1)&».»&Пстр(Пстр(Пстр(A1;Найти(» «;A1;1)+1; Длстр(A1)); Найти (» «; Пстр(A1;Найти(» «;A1;1)+1;Длстр(A1)) ;1 )+1;Длстр(Пстр(A1;Найти(» «; A1;1)+1;Длстр(A1))));1;1)&».»».
Вид формулы может показаться вам устрашающим. Смею заверить, что при описанной технике (работа с Мастером функций и буфером обмена) на создание такой формулы уходит минут 5, не более. А для тех, кто неуверенно работает с формулами в Excel, процесс ее создания будет прекрасным упражнением для совершенствования своих навыков.
Процесс совершенствования бесконечен. Это неоспоримый факт. Поэтому формулу в принципе можно оставить как есть. Работать она будет нормально и в большинстве случаев успешно справится с поставленной задачей. Но определенные недостатки все же есть. Например, если полное имя начинается с пробела, результат работы формулы будет неверным. Если между фамилией, именем или отчеством находятся не один, а
несколько пробелов, формула тоже ошибется (ведь функцией «Найти()» мы ищем всегда один пробел!). Также возникнет проблема, если формулой обработать пустую ячейку. Все эти вопросы решаются элементарно. Главное, определить алгоритм наших действий, а менно:
— чтобы формула уверенно справилась с лишними пробелами, нужно везде вместо ссылки «
A1» поставить выражение «Сжпробелы(A1)». Эта функция уберет все лишнее и оставит только одиночные пробелы между словами;
— в ситуации, когда вместо полного имени стоит пустая строка, выручит функция «
Если()». Выглядеть она будет так: «Если(A1=»»;»»;формула)», где вместо «формула» мы поставим полученное нами выражение. Делаем так:
1) вначале преобразуем файл. Сейчас наша формула находится в ячейке «
J1». Ее составляющие записаны в промежуточных ячейках. Поскольку в «J1» остались ссылки только на «A1», все лишнее можно удалить. Поэтому выделяем колонки с «B» по «I» и удаляем их. В нашей таблице остались всего две ячейки, как показано на рис. 2. В «A1» записано полное имя. В «B1» находится формула. Приступим к ее корректировке;
2) выделяем ячейку с формулой. Можно выделить колонку «
Ctrl+H» (меню «Правка → Найти и заменить»);
Найти:» пишем «A1», в поле «Заменить на:» вводим «Сжпробелы(A1)»;
Заменить все». Формула откорректирована. Вот ее новый вид: «=Пстр(Сжпробелы(A1);1;Найти(» «;Сжпробелы(A1);1))&Пстр(Пстр(Сжпробелы(A1);Найти(» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)));1;1)&».»&Пстр(Пстр(Пстр(Сжпробелы(A1);Найти(» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)));Найти(» «;Пстр(Сжпробелы(A1);Найти(» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)));1)+1;Длстр(Пстр(Сжпробелы(A1);Найти(» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)))));1;1)&».»»;
Запомните этот прием. Мы еще им воспользуемся.
С1» Мастером функций строим функцию «Если(A1=»»;»»;1)». В этой формуле «1» — фиктивный параметр. Он нужен только для того, чтобы Мастер функций закрыл формулу;
Если(A1=»»;»»;1)» вместо «A1» пишем « Сжпробелы(A1) »;
8) становимся в ячейку «
9) копируем текст функции без символа «
10) переходим на ячейку «
С1», нажимаем «F2». В тексте формулы стираем «1» и вставляем содержимое из буфера обмена. Получим такой результат: « =Если(Сжпробелы(A1)=»»;»»;Пстр(Сжпробелы(A1);1;Найти(» «;Сжпробелы(A1);1))&Пстр(Пстр(Сжпробелы(A1);Найти(» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)));1;1)&».»&Пстр(Пстр(Пстр(Сжпробелы(A1);Найти (» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)));Найти(» «;Пстр(Сжпробелы(A1); Найти(» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)));1)+1;Длстр(Пстр(Сжпробелы (A1);Найти(» «;Сжпробелы(A1);1)+1;Длстр(Сжпробелы(A1)))));1;1)&».») »;
11) окончательный вариант формулы находится в «
С1». Удаляем колонку «B».
Несколько конфликтных ситуаций мы обработали. Думаю, дальше улучшать нет смысла, результат будет незначительный. Лучше остановимся на таком вопросе, как работа с формулой.
Как пользоваться формулой
Итак, у нас есть таблица, как на рис. 2. Результирующая формула создана в ячейке «
B1». Все ее части ссылаются на ячейку «A1». Допустим, что я хочу применить эту формулу для заполнения базы, как на рис. 3. Единственная проблема может быть в том, что придется корректировать адрес ячейки, которая ссылается на исходные данные. В принципе это можно сделать вручную, но ссылок в формуле много. Напомню еще раз, что формула — это обычный текст. А это значит, что корректировать его можно любыми доступными средствами, в том числе и средством поиска и замены. И это самый быстрый способ решения проблемы. Делаем так:
1) открываем файл с примером. Клавишей «
F2» выделяем текст формулы и копируем его в буфер обмена;
2) переходим в документ с базой данных. Вставляем в нужную ячейку содержимое буфера. Для примера на рис. 3 будет «
B2». Не удивляйтесь, что вместо результата в ячейке появилось значение «#ЗНАЧ!» — это следствие неправильной адресации. Сейчас мы это исправим;
3) выделяем ячейку «
B2» или всю колонку «B»;
Ctrl+H» (меню «Правка → Найти и заменить»);
Найти:» пишем «A1», в поле «Заменить на:» вводим адрес ячейки с полным именем сотрудника. В примере — это «(A2)»;
Заменить все». Готово.
То есть вы можете хранить книгу с формулой в папке с образцами файлов Excel. В любой момент открываете файл, копируете формулу, вставляете в другой документ. Средствами замены исправляете адресацию. Причем буквально одним движением мыши! И остается только скопировать формулу на всю высоту базы данных. Ну а те, кто не желает создавать формулу самостоятельно, смогут найти ее на форуме редакции:
http://bk.factor.ua/forum/viewtopic.php?p=2503#2503 или скачать прикрепленный к статье файл на сайте www.bk.factor.ua .
Жду ваших вопросов, замечаний и предложений на
Как в EXCEL «Иванов Иван Иванович» превратить в «Иванов И.И.»
Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в B1 ) и отсутствуют лишние пробелы, то превратить полное ФИО в Фамилию + инициалы можно с помощью этой формулы (см. файл примера ): =ЛЕВСИМВ(B1;НАЙТИ(» «;B1;1)+1)&».»&ПСТР(B1;НАЙТИ(» «;B1;НАЙТИ(» «;B1;1)+1)+1;1)&».»
Формула определяет позицию начала Имени и Отчества по пробелам (код символа пробел =32). Очевидно, что порядок следования фамилии и имени с отчеством важен для работы формулы.
Формула НАЙТИ(» «;B1;1) в выражении Иванов Иван Иванович . Соответственно, выражение ЛЕВСИМВ(B1;НАЙТИ(» «;B1;1)+1) выводит фамилию и первый инициал (первую букву имени).
Гарантировать, что в ячейке отсутствуют лишние пробелы можно предварительно применив формулу СЖПРОБЕЛЫ() .
Чтобы превратить Иванов Иван Иванович (в ячейке B2 ) в И.И. Иванов (инициалы перед фамилией) используйте формулу = ПСТР(B2;НАЙТИ(СИМВОЛ(32);B2;1)+1;1)&».»&ПСТР(B2;ПОИСК(СИМВОЛ(32);B2;НАЙТИ(СИМВОЛ(32);B2;1)+1)+1;1)&». «&ЛЕВСИМВ(B2;НАЙТИ(СИМВОЛ(32);B2;1)-1)
Совет : Если бы фамилия, имя и отчество хранились в отдельных ячейках , то формула была бы гораздо проще =ЛЕВСИМВ(B1;1)&».» То же относится и к другим строковым значениям: Адрес (индекс, страна, город, улица); Название книги (автор, название), банковские реквизиты (номер счета, банк, БИК) и др. Формулы становятся значительно проще, если разные по смыслу данные хранятся в отдельных ячейках.
Объединение имени и фамилии
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов с Android Excel Mobile Excel Starter 2010 Еще. Меньше
Предположим, что вы хотите создать один столбец «Полное имя», объединив два других столбца: «Имя» и «Фамилия». Для объединения имени и фамилии используйте функцию С ФУНКЦИИ СЕКАНД или оператор амперсанд (&).
Важно: В Excel 2016, Excel Mobile и Excel в Интернете эта функция заменена функцией СОВМЕСТИМ. Функция СЦЕПИТЬ пока еще доступна для обратной совместимости, однако рекомендуем использовать вместо нее функцию СЦЕП. В последующих версиях Excel функция СЦЕПИТЬ может быть недоступна.
Пример
Скопируйте следующий пример на пустой лист.
Изменение имени пользователя и инициалов
Excel для Microsoft 365 Word для Microsoft 365 Outlook для Microsoft 365 PowerPoint для Microsoft 365 Access для Microsoft 365 Классический клиент Project Online Publisher для Microsoft 365 Visio, план 2 Excel 2021 Word 2021 Outlook 2021 PowerPoint 2021 Access 2021 Project профессиональный 2021 Project стандартный 2021 Publisher 2021 Visio профессиональный 2021 Visio стандартный 2021 Excel 2019 Word 2019 Outlook 2019 PowerPoint 2019 Access 2019 Project профессиональный 2019 Project стандартный 2019 Publisher 2019 Visio профессиональный 2019 Visio стандартный 2019 Excel 2016 Word 2016 Outlook 2016 PowerPoint 2016 Access 2016 OneNote 2016 Project профессиональный 2016 Project стандартный 2016 Publisher 2016 Visio профессиональный 2016 Visio стандартный 2016 Excel 2013 Word 2013 Outlook 2013 PowerPoint 2013 Access 2013 OneNote 2013 Project профессиональный 2013 Project стандартный 2013 Publisher 2013 Visio профессиональный 2013 Visio 2013 Excel 2010 Word 2010 Outlook 2010 PowerPoint 2010 Access 2010 OneNote 2010 Project 2010 Project стандартный 2010 Publisher 2010 Visio 2010 Visio стандартный 2010 Excel 2007 Word 2007 Outlook 2007 PowerPoint 2007 Access 2007 OneNote 2007 Project стандартный 2007 Publisher 2007 Visio 2007 Visio стандартный 2007 Excel Starter 2010 InfoPath 2010 InfoPath 2013 InfoPath Edit Mode 2007 InfoPath Edit Mode 2010 Word Starter 2010 Еще. Меньше
После установки и открытия приложения Office вам будет предложено указать имя пользователя и инициалы в диалоговом окне, которое выглядит примерно так:
Ваше имя и инициалы будут связаны со всеми документами Office, которые вы создаете, и выводиться в примечаниях, исправлениях и свойствах документа. Вы можете ввести любое имя, но учитывайте то, с кем вы будете делиться документами и для кого их рецензировать. Большинство людей просто указывают свое имя и фамилию.
Если диалоговое окно «Имя пользователя» выводится каждый раз при запуске приложения Office, см. раздел Предотвращение запроса имени пользователя и инициалов.
Изменение имени пользователя и инициалов
При создании документов Office, которые будут использоваться совместно с другими людьми, важно правильно указывать данные об авторах. Имя пользователя и инициалы можно изменить в любое время.
Важно: При их изменении в одной из программ Office они также будут изменены во всех программах Office, установленных на компьютере.
- Выберите Файл >Параметры.
- В диалоговом окне Параметры измените имя пользователя и инициалы в разделе Личная настройка Microsoft Office.
Примечание: В Excel 2010 используется только имя пользователя, а не инициалы.
- В открытом документе нажмите Office кнопку , а затем нажмите кнопку Параметры в нижней части меню.
- В OneNote откройте меню Сервис и выберите Параметры.
- В Project и Visio в меню Сервис выберите команду Параметры и откройте вкладку Общие.
Примечание: В Excel 2007 используется только имя пользователя, а не инициалы.
Предотвращение запроса имени пользователя и инициалов
Диалоговое окно «Имя пользователя» должно выводиться только один раз — при первом открытии установленной программы Office. Если вы видите его каждый раз при запуске программы, нужно изменить разрешения в реестре.
Предупреждение: В этой процедуре содержатся действия, которые содержат сведения о том, как изменить реестр компьютера. Ошибки при изменении реестра могут привести к серьезным проблемам. Внимательно следуйте этим шагам. В качестве дополнительной меры предосторожности перед внесением изменений создайте резервную копию реестра. Тогда в случае возникновения проблемы вы сможете восстановить реестр. Чтобы узнать, как создать и восстановить реестр, см. в этой Windows.
- На начальном экране Windows 10, Windows 8, Windows 7 или Windows Vista введите regedit.exe в поле поиска, а затем нажмите клавишу ВВОД.
Примечание: В Windows XP нажмите кнопку Пуск, выберите Выполнить, введите regedit.exe и нажмите клавишу ВВОД.