пол по фамилии excel

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

Описание функции

Функция =ПОЛ(ФИО) возвращает значение М если ФИО содержит мужское имя, или значение Ж, если имя женское. Функция имеет только 1 аргумент:

Пример 1

Начнем с простого примера, когда для каждого человека из списка необходимо определить его пол.

Пример 2

Использование функции ПОЛ для автоматизации договоров. Допустим необходимо автоматизировать обращение к клиенту и в зависимости от пола писать Уважаемый или Уважаемая. Тут нам и поможет наша функция.

Помимо функции ПОЛ тут для удобства использовались функции ИМЯ и ОТЧЕСТВО. Они также входят в состав надстройки.

Принцип работы функции

Конечно, для определения пола необходимо, чтобы определяемое имя было в нашей базе. Сейчас мы создали перечень из основных популярных имен, однако допускаем, что в базе перечислены не все имена.

Так как для определения пола используется наш сервер, то для корректной работы функции ПОЛ необходимо наличие интернета.

Если Вы нашли имя, пол по которому не определяется (функция возвращает значение Нет данных), напишите пожалуйста нам об это используя кнопку Есть предложение на ленте меню надстройки VBA-Excel, мы ее оперативно добавим и функция станет еще лучше.

Источник

Как определить пол по отчеству (ПРАВСИМВ)

Этот пример я решил разобрать, чтобы разобрать комбинацию из формул ЕСЛИ и ПРАВСИМВ.
Разберём тот случай, когда после применения не самой замудрённой формулы в Microsoft Excel, у обычного зрителя возникает ощущение «ВАУ».

Перед нами таблица с ФИО, и наша задача — определить с помощью формулы пол по отчеству.
По традиции можете посмотреть видео, пример для скачивания доступен выше.

Для начала немного теории…
Все мужские отчества заканчиваются на букву «-ч», женские — на «-на».
Нам остаётся просто воспользоваться логической функцией: если отчество заканчивается на букву «-ч», значит пол мужской.
В противном случае, пол — женский.

В ячейку C2 запишем формулу:

«=ЕСЛИ(ПРАВСИМВ(B2;1)=»ч»;»м»;»ж»)»

Растянем нашу формулу на всю таблицу, чтобы определить пол во всех ячейках.

Согласитесь, красивое решение.
Просто, одной формулой, которая состоит из функций ЕСЛИ и ПРАВСИМВ, мы так красиво решили задачу.

В этом смысле, в Excel есть креативная составляющая.
Если решать эту задачу в лоб, то такого элегантного решения мы бы не добились.
В то же время понаблюдав, что общего у отчества всех мужчин или женщин, мы нашли решение, по какому признаку программа могла бы тоже понимать это.

Вам так же доступно видео, возможно кому-то будет так удобнее.

Больше полезных видеоуроков на нашем YouTube канале.
Если у Вас возникли вопросы или просто хотите поделиться мнением, напишите в комментариях к записи.

Источник

Практичный email маркетинг

Узнаём пол подписчика по имени (№96)

В прошлый раз мы приводили в порядок разрозненные данные о подписчиках. Сегодня поговорим о сегментации по полу. Причём разговор в общих чертах — что это и для чего нужно — в своё время уже состоялся (см. №30 Мальчик или девочка?). И сейчас хочется сосредоточиться на более прикладных вещах: откуда взять данные для такой сегментации при минимально доступных средствах и как определить пол подписчика по имени.

Предположим, у нас есть база email+имя, которая насчитывает 10 000 подписчиков. Информация о поле заранее не собиралась, а сейчас вдруг понадобилась для какой-то сегментированной рассылки — например, для поздравления с «гендерными» праздниками: 23 февраля / 8 марта.

Программиста, который может обработать базу автоматически, в нашей команде нет. Проставить отметку о поле каждого контакта вручную, конечно же, нереально — это и трудоёмко, и долго. Остаётся прибегнуть к старому-доброму Эксель:-)

Прежде чем приступить к решению самой задачи, важно выполнить 2 условия:

• Провалидировать email-адреса
(т.е. проверить их подлинность, например, с помощью Mailvalidator).

Это делается в том случае, если по ним ещё никогда не отправлялись рассылки. Если мы берём уже «приработанную» базу из сервиса рассылок, то валидация не требуется.

• Привести в порядок некорректные имена
(Саня → Александр, Tatiana → Татьяна, dfsdfsf → X ).

Далее мы предполагаем, что данные у нас отредактированы, и переходим к последующей обработке.

Пол подписчиков по имени

1. Открываем нашу базу в Экселе — в первом столбце email, во втором имена:

2. Создаём в одном из соседних столбцов (не вплотную) базу использующихся у нас имён:

→ копируем столбец с именами полностью,
→ используем опцию Экселя «Данные / Удалить дубликаты», чтобы отсеять повторяющиеся значения,
→ выполняем сортировку по алфавиту (не включая соседние столбцы).

Скорее всего из 10 000 записей у нас получится несколько сотен имён — всё ещё довольно много, но объём уже не запредельный.

3. Ставим пометку о поле вручную напротив каждого имени из получившегося «короткого списка»:

Иногда могут попадаться сложные имена, пол которых так сходу не определить. Для начала ставим им нейтральную пометку (скажем, «н») и, не останавливаясь, идём дальше.

Позже, когда мы дойдём до конца списка, все имена с пометкой «н» можно отсортировать:

И пройти по ним повторно, разбираясь с каждым таким именем отдельно. Скорее всего, их наберётся несколько десятков. Можно их просто погуглить, а если у нас в базе есть фамилии пользователей, то и эта информация пригодится:

Возможно, будут тяжёлые случаи, в которых придётся оставить пометку «н», но из нескольких десятков записей мы сведём их к единицам.

4. Добавляем пометку о поле всем нашим подписчикам:

→ вбиваем в третий столбец, рядом с email+именами, экселевскую формулу

=ЕСЛИОШИБКА(ВПР(B2;F:G;2;0);“н”)

Буквально она означает следующее:

Если значение в ячейке B2 (наше первое имя) совпадает с каким-либо значением из столбца F, где у нас полный список имён, то в третий столбец подставится соответствующее значение из столбца G.

Как отдельный случай: если имя у контакта отсутствует, в ячейку третьего столбца подставится нейтральная пометка «н».

Подробнее об используемой формуле в справке Support.office.com:

→ копируем формулу во все ячейки напротив наших контактов:

→ получаем пометку с полом м/ж (или «н» при отсутствии информации) в каждой из 10 000 строк. Готово!

Результат можно скопировать в отдельный файл (копируем только значения — чтобы не «тащить» за собой формулы из ячеек):

И далее этот файл загрузить в сервис рассылок / обновить в нём данные о подписчиках, если они там уже были.

Так где-то за полчаса-час мы проработаем всю нашу базу и получим возможность сегментировать её по полу в рассылках.

Например, накануне 23 февраля отправим мужчинам поздравление (держите подарок — скидку 10% на всё), женщинам — предложение поздравить мужчин (держите скидку 10% на подарки), подписчикам без информации о поле — общее письмо, где просто поздравляем с праздником в нейтральных тонах:

Как правило, рассылки с использованием данных о поле достаточно «редкие птицы». У среднестатического проекта они случаются раз-два в год — как раз под те самые праздники.

Если позднее рассылки с учётом пола снова понадобятся — можно выгрузить только новых подписчиков, у которых ещё не добавлена такая информация (возможно, их наберётся несколько сотен), и определить их пол по имени аналогичным способом, но уже быстрее.

Если же нас интересуют рассылки с учётом пола на постоянной основе — скажем, мы интернет-магазин женской и мужской одежды — то стоит приложить усилия по автоматизации сбора данных. Например, добавлять выбор пола уже на этапе подписки:

Или с помощью программиста создать скрипт, определяющий пол подписчика по имени после подписки.

Вручную в таких случаях можно действовать только на первых порах — пока автоматизация ещё не заработала — чтобы не терять времени, а заодно и обкатать методику сегментированных рассылок.

В сети встречаются готовые решения, чтобы определить пол подписчиков по имени, также обработать прочие данные (как бесплатные — см. статью в блоге Юнисендер выше, так и платные — например, Dadata.ru).

Однако, на мой взгляд, для максимальной прозрачности и точности стоит попробовать «собрать» собственное решение по работе с такими задачами.

Разбираясь с ним, можно в принципе глубже прокачать своё умение обрабатывать данные и, соответственно, получить возможность использовать его для решения других аналогичных или даже более сложных задач.

Знание Эксель — хороший навык для email маркетолога. Ведь далеко не всегда есть возможность привлечь на проект технического специалиста, который решит все проблемы с данными за нас.

[В следующий раз нас ждёт кейс по анкетированию: как разослать 8000 писем и получить с этого 600 заполненных анкет].

P.S. Ещё больше информации и механик работы с данными для рассылок есть в 4-5 уроках «Email маркетинга под ключ». Если вам интересно глубже исследовать этот вопрос — добро пожаловать на курс! Тем более его основные материалы совершенно бесплатны.

Если вы ещё не подписались на мою рассылку — самое время это сделать 😉

Источник

Определение пола по ФИО – когда точность действительно важна

Некоторое время назад меня заинтересовала задача определения пола человека по его ФИО. В тот момент я работал в области медицинского страхования, где эта проблема была действительно актуальна – расходы на одного застрахованного, а значит и тарифы, по которым людей принимали на страхование, в зависимости от пола клиента, могли отличаться в несколько раз. Большая часть договоров – корпоративные, застрахованные являются сотрудниками работодателя.

Мы никогда не видели большинство из них в глаза, все, что мы имели – списки застрахованных, где пол иногда был указан (с большим количеством ошибок), но чаще – не указан вообще. Большинство компаний имеют свою специфику работы и профессиональные традиции, в силу чего, в их коллективах преобладают люди одного пола. Даже небольшая ошибка могла сделать потенциально прибыльный договор убыточным (или наоборот, но на такое, по странному стечению обстоятельств, случалось с нашими клиентами гораздо реже). В целом, при объеме портфеля договоров в несколько миллиардов, и характерном количестве ошибок около процента, цена корректного определения пола по ФИО была в районе нескольких десятков миллионов.

В рунете тема определения пола по ФИО поднималась уже неоднократно, но, в большинстве случаев, все сводилось к рекомендации внимательно смотреть на окончание отчества («вич»/«вна») или использовать какие-то подобные найденные вручную закономерности. К сожалению, в моей ситуации этот метод не подходил – среди застрахованных было много, действительно много, иностранцев. Правильное написание их отчества не содержало никаких признаков искомого окончания (а в некоторых случаях отсутствовало и само отчество).

Как ни странно, подобный, очень простой алгоритм, позволил добиться потрясающей точности – на выборке в несколько сотен тысяч человек (при базе для обучения в полтора миллиона) было допущено всего 6 ошибок (которые будут описаны ниже и каждую из которых, вполне вероятно, живой человек тоже совершил бы).

Некоторые подробности предварительной подготовки обучающей выборки:

Поскольку каждый раз, когда требуется определить пол человека, пробегаться по таблице контрагентов со всеми записями долго, то агрегированную информацию о частотах появления определенной части ФИО в хранимых данных я разместил во вспомогательной таблице. Выглядит это как четверка параметров – [часть ФИО – тип (Ф, И или О)– пол – число записей в базе]. Статистика обновляется еженедельно, автоматически.

Для определения того, какое соотношение мужчин и женщин для одной части ФИО считать достаточным основанием для причисления его к тому или иному полу, я использовал следующую функцию:

Ошибки алгоритма. Я обнаружил три ситуаций, когда алгоритм может выдать некорректный результат:

К сожалению, данный метод не является серебряной пулей, он просто лучше всех других, которые я встречал. Я тестировал метод на нескольких базах данных в разных компаниях. К минусам можно отнести то, что для части людей из-за недостаточной статистики определить пол по ФИО нельзя: на базе в 1,5 млн человек таких людей чуть больше 1%, на базе в 300 тыс человек таких около 3%, на базе в 6 млн человек не удалось определить пол для 0,8%. У меня есть предположение, что процент людей, для которых пол не может быть определен, обратно пропорционален корню из размера обучающей выборки, но никакого объяснения — почему именно так происходит, у меня нет. Конечно, процент людей, для которых пол может быть определен можно увеличить (и даже довести почти до 100%) установив более мягкие условия на то, когда человеку можно присвоить какой-либо пол, но для задач, с которыми я работал, была важнее точность, чем 100%-ное отнесение к одному из двух классов.

Другой недостаток, которому подвержен данный метод – плохая работа с опечатками. Несмотря на то, что некоторые из них довольно стандартны (имя «Олга» встречается чаще, чем, например, вполне правильное «Октябрина»), для большинства опечаток статистика будет отсутствовать => определить пол по такому имени будет не всегда возможно. К сожалению, обратное утверждение (если искомое имя еще ни разу не встречалось в базе, значит оно написано не правильно) не верно – людей с уникальными именами не меньше, чем опечаток в именах обычных людей.

Как и у любого инструмента, у этого есть особенности, о которых не задумываешься при создании.

Источник

data_client

10 наиболее полезных функций при анализе данных в Excel

Excel содержит огромное количество самых разнообразных функций, однако не все они нужны при анализе данных. В этой статье вы узнаете о 10 наиболее популярных функций, которые будут нужны при работе с информацией. Эти функции позволяют выполнить большинство задач, которые появляются при анализе данных.

1. ВПР

Эта функция является одной из самых популярных и часто используемых в Excel. Если вам необходимо найти данные в одном столбце в таблице и получить значение из другого столбца таблицы, то эта функция вам поможет. Ее синтаксис:

ВПР (искомое значение; таблица; номер столбца; интервальный просмотр)

Если хотите изучить более подробно, как работает функция ВПР, прочитайте нашу статью «Функция ВПР в Excel».

2. ГПР

Функция ГПР выполняет туже задачу, что и ВПР, только она просматривает первую строку в поиске искомого значения и для получения результата сдвигается на указанное количество строк вниз.

ГПР(искомое значение;таблица;номер строки;интервальный просмотр)

3. ЕСЛИ

Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.

Функция ЕСЛИ выполняет проверку логического выражения и если выражение истинно, то поставляется одно значение и альтернативное, если ложь. Синтаксис следующий:

ЕСЛИ(логическое выражение; значение если истина; значение если ложь)

Обратите внимание, что значения при истине или лжи могут быть не только текстовые, числовые, но также и функции(в том числе и ЕСЛИ), что позволяет реализовать достаточно сложные логические конструкции.

4. ЕСЛИОШИБКА

При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: «Как исправить ошибку #Н/Д в Excel»)

Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:

ЕСЛИОШИБКА(значение; значение если ошибка)

В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.

5. СУММЕСЛИМН

Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.

У функции СУММЕСЛИМН следующий синтаксис:

СУММЕСЛИМН(Диапазон суммирования; Диапазон условия 1; Условие 1;. )

Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».

6. СЧЁТЕСЛИМН

СЧЁТЕСЛИМН очень похожа на функцию СУММЕСЛИМН, только в отличии от нее, она не суммируется значения, а только считает количество ячеек, которые соответствуют определенным условиям. Как и в случае с СУММЕСЛИМН, у СЧЁТЕСЛИМН есть упрощенная форма СЧЁТЕСЛИ, который считает количество ячеек только по одному критерию, но лучше используйте более общий вариант.

Синтаксис у функции следующий:

СЧЁТЕСЛИМН(диапазон условия 1; условие 1;. )

7. СЖПРОБЕЛЫ

При работе с данными в Excel, мы можем получать их из разных источников, что может привести к тому, что получаемые значения имеют «мусорную» информацию, очень часто это лишние пробелы, которые надо удалить. Можно удалять вручную, но это долго и муторно. На выручку нам приходит функция СЖПРОБЕЛЫ, которая удаляет лишние пробелы, в случае если их больше одного подряд. Синтаксис у функции очень простой:

8. ЛЕВСИМВ и ПРАВСИМВ

Функции ЛЕВСИМВ и ПРАВСИМВ возвращают определенное количество знаков с начала (ЛЕВСИМВ) либо с конца (ПРАВСИМВ) строки. Эти функции нужны для получения части строки. Синтаксис у функций однотипный:

ЛЕВСИМВ(текст; количество знаков)
ПРАВСИМВ(текст; количество знаков)

В примере выше мы из текста «Пример текста» извлекаем 6 символов слева и получаем текст «Пример».

9. СЦЕПИТЬ

Функция СПЕПИТЬ позволяет объединить значения из нескольких ячеек. Синтаксис у функции достаточно простой:

10.ЗНАЧЕН

Часто данные, которые мы получаем из внешних источников, имеют текстовый формат и мы не можем производить с ними математических действий (складывать, вычитать и т.п.). Нам требуется сначала преобразовать текст в число, для этого используйте функцию ЗНАЧЕН. Синтаксис у функции следующий:

Как видно в примере выше, у нас есть число 12522, которое представлено в виде текста, при помощи функции ЗНАЧЕН мы преобразовали его в число 12 522, с которым в дальнейшем можем работать, как с любыми другими числами.

Спасибо, что дочитали статью. Я постарался выбрать 10 наиболее полезных функций в Excel, которые нужны при анализе данных. Жду ваши комментарии.

Источник