Как получить Ф. И. О. из полных фамилии, имени и отчества
Приветствую всех сотрудников «Б & 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)» вернет результат «вано». Комбинируя эти две функции, мы и решим задачу. Приступим:1) в ячейку «
B1» запишите формулу «=Найти(" ";A1;1)». Первым параметром в формуле записан пробел! Вручную печатать ничего не нужно. Для создания формулы используйте Мастер функций. Второй параметр — полное имя (фамилия, имя, отчество). Формула находит в полном имени (в данном случае это строка «Иванов Вадим Петрович») первый пробел. Он находится между текстом «Иванов» и «Вадим». Позиция этого пробела в исходном тексте равна «7». Чтобы выделить из текста фамилию «Иванов», нужно вырезать из исходной строки символы с первого по седьмой. То есть до позиции, которую определила формула «=Найти(" ";A1;1)». Сделаем это;2) в ячейку «
С1» запишем формулу «=Пстр(A1;1;Найти(" ";A1;1))». Результат работы этой формулы — текст «Иванов » (рис. 1). Завершающий пробел в этом слове я оставил специально. Теперь нужно вырезать из исходной строки имя и отчество. То есть из полного имени нужно взять часть, начиная с позиции «8» и до конца строки. Позиция «8» — это не что иное, как местоположение первого пробела плюс сдвиг на один символ вправо. То есть формула для определения первого символа имени будет такой: «=Найти(" ";A1;1)+1». Длину строки нам поможет определить функция «ДлСтр()». Все составляющие формулы понятны, можно продолжить;
3) в ячейку «
D1» пишем «=Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1))». Результат ее работы — текст «Вадим Петрович». Из этого текста нужно вырезать первый символ и добавить к этому символу знак «.» (точка). Для этого делаем так;4) в ячейку «
E1» вводим формулу «=Пстр(D1;1;1)&"."». В ней функция «Пстр» вырезает первый символ из текста «Вадим Петрович». Это не что иное, как буква «В». Операция «&» сцепляет символ «В» и точку. В результате получается «В.»;5) теперь разберемся с отчеством. Последовательность действий будет похожа на предыдущую. Вначале выделяем из текста «
Вадим Петрович» слово «Петрович». Признак начала слова «Петрович» очевиден — это пробел. Поэтому нам достаточно определить местоположение этого пробела и затем вырезать соответствующую часть строки. Чтобы определить начало слова «Петрович», в ячейку «F1» вводим формулу: «=Найти (" ";D1;1)». Результатом ее работы будет число «6». Все верно. В строке «Вадим Петрович» слово «Петрович» начинается с седьмой позиции (т. е. с позиции «6+1»);6) в «
G1» вводим формулу «=Пстр(D1;F1+1;Длстр(D1))». Она вернет отчество — «Петрович»;7) в ячейку «
H1» вводим формулу «=Пстр(G1;1;1)&"."». Она вырезает из слова «Петрович» первый символ и добавляет к нему точку. Результат работы формулы — текст «П.»;8) в ячейке «
I1» собираем результат в одно целое. Для этого в «I1» пишем: «=C1&E1&H1». Результат ее работы — текст «Иванов В.П.». Что и требовалось сделать.А теперь переходим к самой интересной части. Попытаемся объединить наши усилия в одной-единственной формуле. Она получится большая. Но получить ее несложно. Нужно только делать все внимательно. Напомню, что
формула — это обычный текст. И оперировать с ней можно как текстовой строкой: выполнять замену символов, копировать, переносить и удалять фрагменты и т. п. Все составляющие для обобщающей формулы у нас есть. Только сейчас они находятся в разных ячейках. Но, задействовав буфер обмена, мы объединим их в считанные минуты.Итак,
наша цель. Берем за основу конечную формулу «=C1&E1&H1». В нее последовательно подставляем выражения из ячеек «C1», «E1», «H1». В результате получим некоторый промежуточный вариант, в котором появятся ссылки на ячейки «D1», «G1». Вместо этих ссылок снова подставим тексты формул из соответствующих ячеек. Так продолжаем до тех пор, пока в формуле не останутся только ссылки на ячейку «A1» (полное имя сотрудника). Приступим:1) переходим к ячейке «
C1». Нажимаем «F2» (режим редактирования содержимого). Станет доступно окошко с содержимым ячейки;2) выделяем
текст «Пстр(A1;1;Найти (" ";A1;1))». Символ «=» выделять не нужно!3) через «
Ctrl+C» копируем текст в буфер обмена;4) нажимаем «
Esc», чтобы вернуться на рабочий лист;5) переходим к «
J1», нажимаем «F2». В «J1» мы построим окончательный вариант формулы;6) в формуле «
=C1 & E1 & H1» удаляем текст «С1» и вставляем вместо него содержимое буфера обмена. В результате получаем « Пстр(A1;1;Найти (" ";A1;1))&E1&H1 ». Изменения в исходной формуле показаны жирным шрифтом с подчеркива-нием;7) переходим к ячейке «
E1». Нажимаем «F2»;8) выделяем текст «
Пстр(D1;1;1)&"."». Символ «=» выделять не нужно;9) через «
Ctrl+C» копируем текст в буфер обмена;10) нажимаем «
Esc», чтобы вернуться на рабочий лист;11) переходим к «
J1», нажимаем «F2»;12) в формуле «
Пстр(A1;1;Найти (" ";A1;1))&E1&H1» удаляем текст «E1» и вставляем вместо него содержимое буфера обмена. В результате получаем «=Пстр(A1;1;Найти (" ";A1;1))& Пстр(D1;1;1)&"."&H1»;13) переходим к ячейке «
H1», нажимаем «F2»;14) выделяем текст «
Пстр(G1;1;1)&"."». Символ «=» в формуле выделять не нужно!15) через «
Ctrl+C» копируем текст в буфер обмена;16) нажимаем «
Esc», чтобы вернуться на рабочий лист;17) переходим к «
J1», нажимаем «F2»;18) в формуле «
=Пстр(A1;1;Найти (" ";A1;1))&Пстр(D1;1;1)&"."&H1 » удаляем текст «H1» и вставляем содержимое из буфера обмена. Получаем «=Пстр(A1;1;Найти(" ";A1;1)) & Пстр(D1;1;1) & "."&Пстр(G1;1;1)&"." ». Осталось убрать в этой формуле ссылки на «D1», «G1»;19) переходим к ячейке «
D1», нажимаем «F2»;20) выделяем из формулы текст «
Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1))» (без символа «=»);21) через «
Ctrl+C» копируем его в буфер об-мена;22) нажимаем «
Esc», возвращаемся на рабочий лист;23) переходим к «
J1», нажимаем «F2»;24) в формуле «
=Пстр(A1;1;Найти(" ";A1;1))&Пстр(D1;1;1)&"."&Пстр(G1;1;1)&"."» удаляем текст «D1» и вставляем содержимое из буфера обмена. Получаем: «=Пстр(A1;1;Найти(" ";A1;1)) & Пстр(Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1));1;1)&"."& Пстр(G1;1;1)&"." »;25) переходим к ячейке «
G1». Нажимаем «F2»;26) выделяем текст «
Пстр(D1;F1+1; Длстр(D1))»;27) через «
Ctrl+C» копируем текст в буфер обмена;28) нажимаем «
Esc»;29) переходим к «
J1», нажимаем «F2»;30) в формуле «
=Пстр(A1;1;НайтИ (" ";A1;1)) & Пстр(Пстр(A1;Найти (" ";A1;1)+1; Длстр(A1));1;1) & "."& Пстр (G1;1;1)&"."» удаляем текст «G1» и вставляем вместо него содержимое из буфера обмена. Получаем формулу: «=Пстр(A1;1;Найти(" ";A1;1)) & Пстр(Пстр(A1;Найти("";A1;1)+1;Длстр(A1));1;1) & "." & Пстр (Пстр(D1;F1+1; Длстр(D1));1;1)&"."». В формуле снова появились ссылки на «D1» и «F1». Заменим их;31) становимся на «
D1», нажимаем «F2»;32) копируем в буфер текст «
Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1))»;33) в формуле «
=Пстр(A1;1;Найти(" ";A1;1)) & Пстр(Пстр(A1;Найти(" ";A1;1)+1; Длстр(A1));1;1) & "." & Пстр(ПСТР(D1;F1+1;Длстр(D1));1;1)&"."» последовательно выделяем две ссылки на «D1» (показаны жирным) и каждый раз заменяем их содержимым из буфера обмена. В результате получим формулу: «=Пстр(A1;1;Найти(" ";A1;1))&Пстр(Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1));1;1)&"."&Пстр(Пстр(Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1));F1+1; Длстр(Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1))));1;1)&"."»;34) становимся на ячейку «
F1», нажимаем «F2»;35) копируем в буфер обмена текст «
Найти (" ";D1;1)»;36) переходим к «
J1», нажимаем «F2»;37) в формуле «
=Пстр(A1;1;Найти(" ";A1;1)) & Пстр(Пстр(A1;Найти(" ";A1;1)+1; Длстр(A1));1;1) & "." & Пстр(Пстр(Пстр(A1;Найти(" ";A1;1)+1; Длстр(A1)); F1+1; Длстр(Пстр(A1;Найти(" ";A1;1)+1;Длстр(A1))));1;1)&"."» вместо ссылки «F1» вставляем содержимое из буфера обмена. Получаем формулу: «=Пстр(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»;38) становимся на ячейку «
D1», нажимаем «F2»;39) копируем в буфер обмена текст «
Пстр(A1;НАЙТИ(" ";A1;1)+1;Длстр(A1))»;40) переходим к «
J1», нажимаем «F2»;41) в формуле «
=Пстр(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) выделяем ячейку с формулой. Можно выделить колонку «
B»;3) нажимаем «
Ctrl+H» (меню «Правка → Найти и заменить»);4) в поле «
Найти:» пишем «A1», в поле «Заменить на:» вводим «Сжпробелы(A1)»;5) нажимаем «
Заменить все». Формула откорректирована. Вот ее новый вид: «=Пстр(Сжпробелы(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)&"."»;Важно!
Запомните этот прием. Мы еще им воспользуемся.
6) в ячейке «
С1» Мастером функций строим функцию «Если(A1="";"";1)». В этой формуле «1» — фиктивный параметр. Он нужен только для того, чтобы Мастер функций закрыл формулу;7) в формуле «
Если(A1="";"";1)» вместо «A1» пишем «Сжпробелы(A1)»;8) становимся в ячейку «
B1», нажимаем «F2»;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»;4) нажимаем «
Ctrl+H» (меню «Правка → Найти и заменить»);5) в поле «
Найти:» пишем «A1», в поле «Заменить на:» вводим адрес ячейки с полным именем сотрудника. В примере — это «(A2)»;6) нажимаем «
Заменить все». Готово.То есть вы можете хранить книгу с формулой в папке с образцами файлов Excel. В любой момент открываете файл, копируете формулу, вставляете в другой документ. Средствами замены исправляете адресацию. Причем буквально одним движением мыши! И остается только скопировать формулу на всю высоту базы данных. Ну а те, кто не желает создавать формулу самостоятельно, смогут найти ее на форуме редакции:
http://bk.factor.ua/forum/viewtopic.php?p=2503#2503 или скачать прикрепленный к статье файл на сайте www.bk.factor.ua.
Жду ваших вопросов, замечаний и предложений на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Удачной работы!
