Определение пола по имени

Классическая задача, с которой периодически сталкивается почти любой пользователь Microsoft Excel: нужно определить пол для каждого человека в списке. Давайте рассмотрим несколько решений для такой задачи.

Вариант 1. Полные ФИО, только "наши"

Начнем, для разогрева, с самого простого случая, когда у нас есть правильно записанные полные ФИО для всех людей в списке. Как легко сообразить, для большинства жителей бывшего СНГ тут сработает простой принцип: "если отчество оканчивается на Ч, то это мужчина, в противном случае - женщина". Реализовать эту логику можно простой формулой:

Определение пола по ФИО

Функция ПРАВСИМВ (RIGHT) извлекает из ФИО один символ справа (последнюю букву отчества), а функция ЕСЛИ (IF) проверяет извлеченный символ и выводит "ж" или "м", в зависимости  от результата проверки.

Вариант 2. Полные ФИО, есть "экспаты"

Если в списке есть имена не только русского типа (назовем их "экспаты"), то к приведенной ранее формуле можно добавить еще одну проверку, чтобы отлавливать их тоже:

Определение пола по ФИО если есть экспаты

То есть "м" будет выводиться только если отчество заканчивается на Ч, "ж" - если заканчивается на А. Во всех же остальных случаях ("экспаты") наша формула выдаст три вопросительных знака.

Вариант 3. Неполные или переставленные ФИО, только "наши"

Если в нашем списке отчества есть не у всех (или их нет совсем) или ФИО идет в другом порядке (ИФО, ИФ, ФИ), то придется использовать принципиально другой подход. Создадим таблицу-справочника со всеми женскими именами (я использовал для этого википедию):

Справочник женских имен

Созданную таблицу я преобразовал в "умную" (выделить ее и нажать Ctrl+T), чтобы потом не думать про ее размеры и дополнять справочник новыми именами в любое время. На появившейся вкладке Конструктор (Design) умной таблице лучше дать отдельное имя (например жен), чтобы потом использовать его в формулах:

Имя для умной таблицы

Нужная нам формула для определения пола будет выглядеть так:

Формула массива для определения пола по ФИО

Давайте разберем ее по шагам на примере первого человека:

Функция ПОИСК (SEARCH) ищет вхождения по очереди каждого женского имени из умной таблицы жен в строку "Храброва Алла Сергеевна" и выдает на выходе либо ошибку #ЗНАЧ (если не нашла), либо порядковый номер символа, начиная с которого имя входит в ФИО. На выходе мы получаем массив:

{#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:10:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!}

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

Затем функция ЕСЛИОШИБКА (IFERROR) заменяет ошибки #ЗНАЧ! на нули. В результате получаем:

{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:

Универсальная формула массива

Теперь гораздо лучше :)

Ссылки по теме


03.10.2016 12:46:33
День добрый. А как будет обработано ФИО, содержащее в ф фамилии или отчестве часть женского имени, например, Аллахвердыев Магомед. Может сцепить результаты обеих проверок и в сложных случаях получить МЖ (хотя во всех отчествах будут части мужских имен)
03.10.2016 12:55:33
Да. Или, например, Люси Лью Александровна.
03.10.2016 16:44:13
Ну, стопроцентно работающий вариант не сделать никогда - просто потому, что есть люди с именем, например, Алекс Смит, которые могут быть как женщиной, так и мужчиной.
Можно попробовать искать "Алла" с пробелом, но это тоже не 100% гарантия
03.10.2016 14:54:26
Интересно без формулы массива, четвёртый вариант можно сделать?  Например, если массив от поиска вычислить по F9. То сумма получается без формулы массива.
03.10.2016 17:43:14
Здравствуйте Николай. Спасибо огромное за пример, уже вставил в базу и всё работает:)
22.10.2016 13:20:38
в 4-м случае выход только один, разбивать ФИО по отдельным ячейкам и пол определять по ячейке с именем)
08.01.2017 07:30:20
Доброго времени суток, Николай!
Спасибо Вам огромное за Вашу работу. В тех не редких случаях, когда чего-то не знаю - лезу разбираться именно на Ваш ресурс.
Есть одна загвоздка с аналогичной задачей: все работает, впринципе, вот только Руслану оно считаем мужиком... И я могу понять почему, ведь в списке женских имен нет имени Руслана, а в списке мужских есть Руслан, но, сами понимаете, это не совсем корректная, так сказать, работа скрипта. Добавлять новые имена - это хорошо и правильно, но как бы применить к данной формуле точное соответствие? То ли я заработался и азы не могу вспомнить, то ли реально не знаю подобных тонкостей. Очень прошу помочь.
Заранее благодарен.
Искренне Ваш, Никита.

P.S. Впрочем, решение (номного еще подумав) нашел, при чем куда более простое, чем использование данной формулы. Конкретнее - через формулу ВПР.
В данном конкретном случае она выглядела бы так:
=ЕСЛИ(ЕНД(ВПР(C2;муж;1;0));ЕСЛИ(ЕНД(ВПР(C2;жен;1;0));"???";"ж");"м")

Изучение Excel все-же интересно, так что с вопросом решения бага все-же прошу помочь)))
Николай Павлов,
как правило, программно пол проверяют по русским отчествам и азербайджанским (оглы и кызы).