Определение пола по имени
Классическая задача, с которой периодически сталкивается почти любой пользователь Microsoft Excel: нужно определить пол для каждого человека в списке. Давайте рассмотрим несколько решений для такой задачи.
Вариант 1. Полные ФИО, только "наши"
Начнем, для разогрева, с самого простого случая, когда у нас есть правильно записанные полные ФИО для всех людей в списке. Как легко сообразить, для большинства жителей бывшего СНГ тут сработает простой принцип: "если отчество оканчивается на Ч, то это мужчина, в противном случае - женщина". Реализовать эту логику можно простой формулой:
Функция ПРАВСИМВ (RIGHT) извлекает из ФИО один символ справа (последнюю букву отчества), а функция ЕСЛИ (IF) проверяет извлеченный символ и выводит "ж" или "м", в зависимости от результата проверки.
Вариант 2. Полные ФИО, есть "экспаты"
Если в списке есть имена не только русского типа (назовем их "экспаты"), то к приведенной ранее формуле можно добавить еще одну проверку, чтобы отлавливать их тоже:
То есть "м" будет выводиться только если отчество заканчивается на Ч, "ж" - если заканчивается на А. Во всех же остальных случаях ("экспаты") наша формула выдаст три вопросительных знака.
Вариант 3. Неполные или переставленные ФИО, только "наши"
Если в нашем списке отчества есть не у всех (или их нет совсем) или ФИО идет в другом порядке (ИФО, ИФ, ФИ), то придется использовать принципиально другой подход. Создадим таблицу-справочника со всеми женскими именами (я использовал для этого википедию):
Созданную таблицу я преобразовал в "умную" (выделить ее и нажать Ctrl+T), чтобы потом не думать про ее размеры и дополнять справочник новыми именами в любое время. На появившейся вкладке Конструктор (Design) умной таблице лучше дать отдельное имя (например жен), чтобы потом использовать его в формулах:
Нужная нам формула для определения пола будет выглядеть так:
Давайте разберем ее по шагам на примере первого человека:
Функция ПОИСК (SEARCH) ищет вхождения по очереди каждого женского имени из умной таблицы жен в строку "Храброва Алла Сергеевна" и выдает на выходе либо ошибку #ЗНАЧ (если не нашла), либо порядковый номер символа, начиная с которого имя входит в ФИО. На выходе мы получаем массив:
{#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:10:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}
Число 10 на седьмой позиции в этом массиве фактически означает, что седьмое женское имя Алла из умной таблицы-справочника входит в первое ФИО Храброва Алла Сергеевна начиная с 10 символа.
{0:0:0:0:0:0:10:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0}
Функция СУММ (SUM) суммирует все числа в получившемся массиве и если получается число больше нуля, то функция ЕСЛИ (IF) выводит "ж", в противном случае "м".
Не забудьте после ввода формулы нажать сочетание клавиш Ctrl+Shift+Enter, т.к. ее нужно ввести как формулу массива.
Вариант 4. Неполные ФИО, есть "экспаты"
Если в списке могут встречаться экспаты или нестандартные имена, которых нет в справочнике, то предыдущая формула будет автоматом относить человека к мужчинам, что не есть хорошо. Поэтому для полной универсальности можно добавить справочник мужских имен и еще одну проверку, как мы уже делали в варианте-2:
Теперь гораздо лучше :)
Ссылки по теме
- Как склеить текст из нескольких ячеек в одну
- Поиск ближайшего похожего текста
- Разделение ФИО на отдельные столбцы
Можно попробовать искать "Алла" с пробелом, но это тоже не 100% гарантия
Спасибо Вам огромное за Вашу работу. В тех не редких случаях, когда чего-то не знаю - лезу разбираться именно на Ваш ресурс.
Есть одна загвоздка с аналогичной задачей: все работает, впринципе, вот только Руслану оно считаем мужиком... И я могу понять почему, ведь в списке женских имен нет имени Руслана, а в списке мужских есть Руслан, но, сами понимаете, это не совсем корректная, так сказать, работа скрипта. Добавлять новые имена - это хорошо и правильно, но как бы применить к данной формуле точное соответствие? То ли я заработался и азы не могу вспомнить, то ли реально не знаю подобных тонкостей. Очень прошу помочь.
Заранее благодарен.
Искренне Ваш, Никита.
P.S. Впрочем, решение (номного еще подумав) нашел, при чем куда более простое, чем использование данной формулы. Конкретнее - через формулу ВПР.
В данном конкретном случае она выглядела бы так:
=ЕСЛИ(ЕНД(ВПР(C2;муж;1;0));ЕСЛИ(ЕНД(ВПР(C2;жен;1;0));"???";"ж");"м")
Изучение Excel все-же интересно, так что с вопросом решения бага все-же прошу помочь)))
как правило, программно пол проверяют по русским отчествам и азербайджанским (оглы и кызы).