Excel фамилия и инициалы из полного фио
Преобразование имени и отчества в инициалы с помощью формул Excel и присоединение их к фамилии. Разделение фамилий, имен и отчеств, расположенных в одной ячейке, по разным ячейкам и наоборот.
Фамилия, имя и отчество в разных ячейках
Когда фамилия, имя и отчество записаны в разные ячейки, работать с ними легче, чем, когда они расположены в одной ячейке. Перечислим преимущества:
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:
Фамилия, имя и отчество в одной ячейке
Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:
*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.
Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:
Для этого преобразования используются следующие формулы в соответствующих ячейках:
Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».
Необходимость следующего преобразования возникает чаще предыдущего, используется для заполнения различных документов наряду с полным именем:
Для этого преобразования используется следующая формула в ячейке B1 – =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(» «;A1;1));» «;ПСТР(A1;НАЙТИ(» «;A1;1)+1;1);».»;ПСТР(A1;НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)+1;1);».»)
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Работа с фамилией, именем, отчеством и инициалами в Excel : 2 комментария
Фамилия Имя Отчество в Фамилию и инициалы.
Если используется оператор Option Explicit, можно так:
Пусть в A1 полное ФИО. Введите в B1 такую формулу и наслаждайтесь
=ЛЕВСИМВ (A1; ПОИСК (» «;A1)) & ПСТР (A1; ПОИСК (» «;A1)+1; 1) &».» & ПСТР (A1; ПОИСК (» «; A1; ПОИСК (» «; A1)+1)+1; 1) & «.»
Превращаем полное ФИО в Фамилию + Инициалы.
Если Фамилия, Имя и Отчество (ФИО) хранятся в одной ячейке (например, в 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
Преобразование имени и отчества в инициалы с помощью формул Excel и присоединение их к фамилии. Разделение фамилий, имен и отчеств, расположенных в одной ячейке, по разным ячейкам и наоборот.
Фамилия, имя и отчество в разных ячейках
Если фамилия, имя и отчество записываются в разные ячейки, работать с ними легче, чем при размещении их в одной ячейке. Перечислим преимущества:
Итак, объединяем фамилию, имя и отчество полностью из разных ячеек в одну:
В следующем примере мы также объединим фамилию, имя и отчество из разных ячеек в одну, при этом имя и отчество заменив на инициалы:
Фамилия, имя и отчество в одной ячейке
Использование фамилии, имени и отчества в одной ячейке имеет тоже свои преимущества:
*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.
Лично мне в работе не приходилось преобразовывать фамилию, имя и отчество из одной ячейки в разные, но могу предположить, что для кого-то это бывает необходимо:
Для этого преобразования используются следующие формулы в соответствующих ячейках:
Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».
Необходимость следующего преобразования возникает чаще предыдущего, используется для заполнения различных документов наряду с полным именем:
Для этого преобразования используется следующая формула в ячейке B1 — =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(» «;A1;1));» «;ПСТР(A1;НАЙТИ(» «;A1;1)+1;1);».»;ПСТР(A1;НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)+1;1);».»)
Вы можете копировать эти формулы в свои файлы, не забывая изменять адреса ячеек на фактические. Часто инициалы пишут перед фамилией, изменить формулы для такого отображения ФИО несложно.
Компьютерная грамотность с Надеждой
Заполняем пробелы — расширяем горизонты!
Извлечение фамилии из ФИО в Excel: способ перевода на язык цифр
Иногда кажется, что компьютеры и другие устройства (телефоны, смартфоны, планшеты и прочее) понимают нас с полуслова, с одного жеста, или даже читают мысли. Действительно, современные компьютеры и их «младшие братья и сестры» выполнены классно! Но кто и как сделал их такими понятливыми? Для наглядности рассмотрим этот вопрос на примере, как компьютер может извлечь фамилию из ФИО в Excel.
Умеют ли думать компьютеры
Процессоры внутри современных компьютеров и иных устройств остались почти такими, как они были на заре автоматизации. Нет, они, конечно, лучше: уменьшился размер, повысилась скорость работы, возросла разрядность – число двоичных знаков, которые процессор может обрабатывать за один такт работы. Быстродействие процессоров стало колоссальным по сравнению с тем, что было когда-то в начале пути.
И все-таки процессоры внутри устройств остались почти такими же. Современные процессоры по-прежнему работают с ограниченным набором команд. Они понимают лишь нескольких сотен команд. Причем команды так и остались из разряда «сложить», «умножить», «сравнить больше или меньше», «перейти к следующей команде» и тому подобное.
Процессоры умеют считать. Они не умеют думать, и никогда этому не научатся, в том виде, в той конфигурации, как они есть. Их умения сводятся к незамысловатому списку: складывать, умножать, сравнивать. И только программисты могут добиться, чтобы устройство делало вид, что оно думает почти как человек. Почти!
Как научить компьютеры думать
Чтобы компьютер или другое устройство могло уверенно общаться с человеком (пользователем), его нужно запрограммировать. Программисты умеют переводить наши человеческие запросы, чаяния, желания на язык бездушного (но удивительно быстрого!) процессора. Именно программисты делают так, чтобы компьютер превращался в умного помощника.
Перевод нашего человеческого мышления на язык цифр, который понимает компьютер, – это дело рук программистов. Только программисты знают, как оцифровать наше во многом непредсказуемое и потому непрограммируемое человеческое мышление.
Программирование – непростое занятие. Хотя для современных программистов сделано немало полезных приложений, языков программирования, эмуляторов, имитаторов и прочее. Им значительно проще, чем тем, кто начинал подобную работу лет 30-50 тому назад. Все равно современным программистам сложно. Но они – справляются!
Давайте на простом примере покажем отличия нашего человеческого мышления от тех возможностей, которыми на самом деле располагают программисты. Это поможет нам лучше понять, как мыслят и действуют программисты при решении поставленных задач. Понять, как программисты оцифровывают задачи для компьютеров.
Фамилия из ФИО в Excel: как ее извлечь
Пример возьмем следующий. Допустим, перед нами фамилия, имя и отчество некоего человека: Завьялова Анна Петровна. Никто конкретно не имеется в виду, просто случайно выбранная ФИО (фамилия, имя, отчество). Задача для программиста будет следующая: выделить с помощью программного кода из ФИО одну лишь фамилию.
Казалось бы, в чем проблема? Что, разве не видно, что фамилия указанного человека – Завьялова? Да, видно тем, кто умеет читать. Но компьютер не умеет читать. Он не может смотреть как мы и не может думать, как мы. Он умеет только считать. Как «сосчитать» из ФИО одну только фамилию? В этом-то и будет заключаться задача, которую мы ставим перед гипотетическим программистом.
Для решения задачи давайте использовать табличный редактор Microsoft Excel, у которого также есть разные аналоги, например, таблицы Liber Office и др. Итак, допустим, мы – программисты. Сейчас мы попробуем с помощью программного кода извлечь фамилию из ФИО в Excel.
На новом чистом листе табличного редактора Excel в ячейку B2 вводим ФИО (Завьялова Анна Петровна) и выделяем ее желтым цветом для наглядности. Также размечаем таблицу наших последующих действий (рис. 1).
Рис. 1. Ввод ФИО в ячейку B1 и предварительная разметка листа табличного редактора Microsoft Excel.
В столбце с заголовком № п/п мы будем записывать цифры 1, 2, 3 и так далее – это предстоящие попытки извлечения фамилии из ФИО.
Далее в столбце Фамилия будут размещаться результаты наших попыток.
В столбце Вычисления мы покажем, с помощью каких расчетов удается извлечь требуемую фамилию из ФИО.
Извлечение подстроки из строки текста в Excel
Начнем первую попытку. Давайте посмотрим, как можно из строки текста извлечь некоторую его часть. Перед нами строка: Завьялова Анна Петровна. С точки зрения человека, мы видим тут осмысленный текст. Нет никаких проблем что-либо их этого текста извлечь. Вам фамилию? Извольте: Завьялова!
Однако, с точки зрения компьютера, который умеет только считать, перед его «глазами» расположена лишь последовательность символов: З, а, в, ь, я, л, о, в, а, [пробел] (это тоже символ, равный по значению любому другому символу текста), А, н, н… И так далее. Получается, что компьютеру нужно из всей строки взять только несколько первых символов, начиная с символа З, и заканчивая символом а.
Так пусть возьмет! А как? У компьютера нет глаз, рук и прочего. Компьютер умеет только выполнять заложенные в него программы. Программу (код) надо кому-то написать. Вот мы и начнем писать.
Так как процессор компьютера умеет только считать, придется написать код так, чтобы он мог быть выполнен с помощью каких-либо расчетов. Поэтому нам придется «сказать компьютеру» (иными словами, запрограммировать) примерно следующее: «Из строки Завьялова Анна Петровна, пожалуйста, извлеки подстроку, начиная с символа с порядковым номером 1, и заканчивая символом с порядковым номером 9».
В строке Завьялова Анна Петровна первый символ – это З. Девятый символ – это а. А все, что находится между первым и девятым символами строки – это и есть фамилия Завьялова. Согласны? Теперь попробуем нашу мысль, сформулированную по-русски, перевести на язык программирования, понятный компьютеру.
Функция ПСТР в Excel для выделения фамилии из ФИО
Каким языком программирования мы воспользуемся? Формулы Excel вполне для этого подойдут, раз уж мы выбрали Эксель для нашего примера. В Excel есть функция ПСТР (сокращение от «подстрока»). В формуле ПСТР нужно:
Таким образом мы пишем следующую расчетную формулу:
– вот так это выглядит в исполнении для компьютера, для Excel.
Вводим указанную формулу в ячейку B4, и получаем результат: фамилию Завьялова в ячейке B4 (рис. 2):
Рис. 2. Результат применения формулы ПСТР в табличном редакторе Excel для извлечения Фамилии из ФИО.
Единичку слева мы приписали вручную – это наша первая (удачная!) попытка извлечь фамилию. Формулу справа мы записали лишь для наглядности нашего примера. В реальной рабочей программе дополнительное размещение формулы в соседней ячейке справа не нужно.
Итак, извлечь Фамилию из ФИО в Excel получилось! Мы имеем то, что хотели с помощью программы на компьютере. Мы смогли из ФИО извлечь первые 9 символов – это и есть искомая фамилия. Главное: нам удалось оцифровать задачу.
Мы сумели цифрами объяснить программе, компьютеру, процессору, какие данные он должен откуда взять и как их обработать. Главные цифры нашей небольшой успешной программы – это 1 и 9, начало и конец подстроки текста. Компьютер в цифрах знает толк, понимает их, делает то, что нам нужно. Как видим, ни о каких буквах (а ФИО – это буквы!) тут речь не идет. Вместо букв мы оперируем их порядковыми номерами в строке. Вот вам и оцифровка: по номерам позиций букв в строке!
Но на этом, к сожалению, работа с кодом (программой) не заканчивается. Будем продолжать.
Функция СЖПРОБЕЛЫ в Excel для удаления лишних пробелов в тексте
ФИО мы вводили вручную в ячейку B1. При ручном вводе не исключены ошибки. Допустим, в начале ФИО мы поставили бы пару-тройку лишних пробелов. Какой бы мы получили результат вычислений? Давайте посмотрим (рис. 3):
Рис. 3. Результат работы функции ПСТР в Excel, если в начале исходной строке случайно поставлены лишние пробелы.
Прекрасно видно, что первые три лишних пробела остались в тексте Фамилии в ячейке B4, поэтому фамилия сократилась ровно на 3 символа и стала Завьял. Результат – неверный! Подобные ошибки ввода не исключены, и нам (программистам) надо их предусмотреть.
Для удаления лишних пробелов в тексте в Excel применяется функция «сжать пробелы», которая выглядит так: СЖПРОБЕЛЫ. В качестве аргумента этой функции надо подставить исходную строку.
Функция СЖПРОБЕЛЫ удаляет все пробелы, которые стоят в начале исходной строки. Также она удаляет все пробелы в самом конце исходной строки, и оставляет строго по одному пробелу, если пробелы дублируются в середине строки.
В нашем примере исходный текст ФИО находится в ячейке B1, поэтому мы так и запишем: СЖПРОБЕЛЫ (B1).
Этот новый текст СЖПРОБЕЛЫ (B1) надо подставить вместо ячейки B1. В окончательном виде формула расчета, которую мы запишем в ячейку B5, будет выглядеть так:
Результат налицо: получаем требуемую фамилию Завьялова теперь уже в ячейке B5 (рис. 4).
Рис. 4. Исключение лишних пробелов в исходной строке текста Excel с помощью функции СЖПРОБЕЛЫ.
Слева добавим вручную «двойку» 2, а справа повторим нашу формулу для наглядности. Теперь всё? – спросит нетерпеливый читатель. Ответим, увы, нет! Потому что могут быть еще проблемные ситуации. Следовательно, продолжаем.
Как извлечь фамилию любой длины из ФИО в Excel
Как поведет себя наша программа (код), если мы вместо прежней ФИО подставим в ячейку B1 совсем другую строку? Например, Завгороднев Игорь Петрович (рис. 5).
Рис. 5. Проверяем, как поведет себя написанная программа в Excel после подстановки в нее другой исходной строки ФИО.
Совершенно очевидно, что программа (код) не сработала так, как нужно. Мы получили какой-то кусочек фамилии – Завгородн. Почему так вышло? Потому что мы извлекаем из исходной строки ФИО подстроку строго определенной длины, с первого до девятого символа. Но ведь далеко не все возможные существующие фамилии имеют длину 9 символов!
Значит, придется нам программу (код) изменить так, чтобы компьютер мог извлечь фамилию любой длины. Как это сделать программисту? Встаем в его положение. Он думает, думает, и… понимает, что любая фамилия в ФИО всегда заканчивается пробелом. Пробел отделяет фамилию от имени.
Значит, понимает программист, надо попытаться найти номер позиции в ФИО, в которой стоит пробел. В фамилии Завьялова – это 10-я позиция. В фамилии Завгороднев – это уже12-я позиция. Видите, программист пытается оцифровать задачу, чтобы потом эти цифры донести до компьютера. Иначе – никак, компьютер понимает лишь язык цифр, он умеет только считать!
Функция НАЙТИ в Excel для поиска нужного символа в строке текста
В Excel есть функция, которая позволяет определить номер интересующего символа в строке текста. Это функция НАЙТИ. В ней указывают сначала, что нужно найти в строке, а затем саму строку.
Значит, записываем: НАЙТИ (“ “ ; B1). Что это означает? Ищем пробел, который обрамлен в кавычки – так записывают в Excel отдельные символы. И ищем пробел в строке, которая записана в ячейке B1 – там у нас размещена ФИО. А еще в приведенной функции НАЙТИ вместо B1 нам придется записать функцию СЖПРОБЕЛЫ (B1) для исключения лишних пробелов:
В результате функция выдаст номер позиции (искомую цифру, именно цифру. ) в ФИО, где стоит пробел. Мы этот номер уменьшим на единицу (ведь фамилия короче на 1 символ как раз из-за этого разделяющего пробела). И затем подставим функцию НАЙТИ вместо цифры 9 в прежнюю функция ПСТР.
В итоге цифра 9 будет заменена вычислением. Расчет позволяет динамически изменить цифру – это номер последнего символа в фамилии. Значит, теперь при подстановке любой фамилии в ячейку B1, наша формула будет автоматически и правильно находить последний символ фамилии.
Совместное применение функций Excel: ПСТР, СЖПРОБЕЛЫ и НАЙТИ
Итак, пишем формулу расчета для выделения фамилии любой длины из ФИО:
Переведем на русский язык, что мы только что записали:
Итог работы такого теперь уже достаточно сложного и неочевидного расчета – это фамилия Завгороднев в ячейке B6, куда мы поместили наш расчет (рис. 6). Ура, сработало!
Рис. 6. Пример совместного использования функций Excel ПСТР и НАЙТИ для извлечения фамилии произвольной длины из строки ФИО.
Извлечение фамилии из ФИО в Excel: пример мини программы
Все ли под силу программистам
Вот так, с третьей попытки нам (программистам) удалось извлечь ЛЮБУЮ фамилию из ЛЮБОЙ ФИО. Тогда как вручную любой человек эту работу сделал бы без таких проблем, связанных с оцифровкой задачи для компьютера. Правда, если бы таких ФИО было, скажем, миллион, то человек уже не справится. А компьютер с его сумасшедшей производительностью не то что миллион, и миллиард, пожалуй, сможет потянуть.
В этом-то и заключается суть. Чтобы сделать что-то один раз, для этого компьютер не нужен. А вот выполнять повторяющиеся действия человеку сложно или невозможно. Требуется компьютер для повторяющихся множественных действий.
Но все ли возможные проблемы решены выше? Конечно, нет. В реальной жизни может быть такое, что сложно даже представить, не то, чтобы перечислить. Например, вместо буквы «З» в начале фамилии будет поставлена цифра 3. Внешне символы выглядят одинаково.
Наша только что написанная программа спокойно сработает с «тройкой». Правда, если потом придется фамилии расположить в алфавитном порядке, «тройка» встанет перед «А», а не после «Ж». Непорядок! Значит, надо научить компьютер распознавать «тройки» и автоматически заменять их на буквы «З». Можно также перепутать и другие пары цифры-буквы, например, 0 и «О», а также 4 и «Ч».
При ручном вводе можно спутать верхний и нижний регистры. Исходную фамилию можно ввести с маленькой буквы. Вместо маленьких букв не исключено ошибочное использование больших заглавных букв.
Снова встает задача для программиста: предусмотреть автоматическое распознавание неправильно выбранного регистра и замену больших букв на маленькие, либо маленьких на большие там, где нужно. Оцифровка такой задачи потребует определенных сил и времени, не так это просто, как кажется на первый взгляд.
Возьмем еще более сложную задачу. Что делать компьютеру, если ФИО напечатана наоборот: имя, отчество, фамилия? Придется искать фамилию не с первой позиции до первого пробела (как мы это сделали выше), а со второго пробела и до конца строки. Это намного сложнее сформулировать программисту.
Какие задачи могут быть неразрешимыми для программистов
И совсем неразрешимая задача, если отдельные ФИО будут введены «правильно» (фамилия, имя, отчество), а другие «неправильно» (имя, отчество, фамилия). Как обучить компьютер находить фамилию в тексте, если фамилия не будет стоять на своем строго закрепленном месте?
Фамилия – первая или третья в строке? Глазами-то мы увидим фамилию сразу. Но как оцифровать данную задачу?! Ведь для компьютера, как мы немного убедились выше, нужно однозначно указать, с какого символа по какой символ располагается фамилия в тексте ФИО. Однозначно указать не получится – это может быть с первого символа до первого пробела, либо со второго пробела и до конца. Что значит «либо»? С первого символа или со второго пробела?!
Вообще, самое сложное в программировании, когда исходные данные совсем не структурированы. Если программистам ставят задачи, в которых нет хотя бы элементарного порядка, оцифровать алгоритмы обработки таких текстов крайне сложно. В нашем случае с перепутанным расположением фамилий могут потребоваться огромные дополнительные таблицы, содержащие все известные фамилии. Программист должен будет написать код, который сравнивает строку ФИО с таблицей фамилий с целью поиска в ней подходящей фамилии.
Либо придется писать так называемые эвристические алгоритмы и программы, наделяющие компьютер определенными элементами искусственного интеллекта. Так работают, например, системы распознавания визуальных образов для поиска людей по их изображению с камер видеонаблюдений.
В общем, иногда проще предварительно навести некоторый порядок в работе, подлежащей автоматизации. И только после этого приступать к программированию рабочих действий. Так проще, чем (часто безуспешно) создавать эвристические алгоритмы и программы.
Об искусстве программирования
Список возможных задач для программирования или возникающих проблем при кодировании можно продолжать до бесконечности. С большинством известных задач программисты справляются, иначе бы у нас не было любимых и полезных компьютеров и иных устройств.
Вручную человек легко решает задачи подобного рода, например, извлечение фамилии из ФИО. Для этого у человека есть разум, который помогает ему в поиске и выборе. У компьютера и его основного «работника» процессора разума НЕТ! Процессор умеет только считать. Цифры для компьютера должны быть окончательными и однозначными.
Программистам приходится превращать в цифры, в манипуляции с цифрами всё, что мы обычно делаем силой своего разума. Например, мы видим ФИО не как набор символов, а как четкий образ. Мы можем любой образ преобразовать во что угодно, хоть нарисовать от руки предполагаемый портрет персонажа, скрывающегося под ФИО.
Компьютер же может только считать, повторю это в который раз! Складывать, умножать, сравнивать… Он даже вычитает путем сложения с отрицательными числами, и делит не совсем так, как мы могли бы сделать, скажем, «столбиком».
Программисты – это те, кто умеет, практически, любые человеческие действия превратить в движение чисел. Тем самым они создают для нас незаменимых помощников: компьютеры, ноутбуки, планшеты, смартфоны, телефоны и многое другое, где применяют процессоры для вычислений.
Программирование (иными словами кодирование) – это искусство перевода человеческого мышления на язык цифр! Это именно искусство, поскольку невозможно заранее и для всех мыслимых и немыслимых случаев сделать четкие инструкции. Были бы инструкции – работа программистов напоминала бы ремесло. Сам же процесс составления инструкций выглядел бы в некотором смысле прототипом науки. Но тут не ремесло и не наука, а именно искусство!
В каждом конкретном случае программист должен искать и находить пути оцифровки задачи, действуя творчески. Мы хотим многого, а программисту требуется придумать цифровые алгоритмы исполнения наших желаний. Оцифровка потребовалась даже в простеньком продемонстрированном примере: выделение фамилии из более длинной строки с текстом фамилии, имени и отчества произвольного человека.