как определить пол в эксель

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

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

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

Пример 1

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

Пример 2

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

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

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

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

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

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

<#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!: 10 :#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!: #ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!: #ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!: #ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!: #ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!: #ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:
#ЗНАЧ!: #ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!:#ЗНАЧ!>

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

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

Функция СУММ (SUM) суммирует все числа в получившемся массиве и если получается число больше нуля, то функция ЕСЛИ (IF) выводит «ж», в противном случае «м».

Не забудьте после ввода формулы нажать сочетание клавиш Ctrl+Shift+Enter, т.к. ее нужно ввести как формулу массива.

Вариант 4. Неполные ФИО, есть «экспаты»

Если в списке могут встречаться экспаты или нестандартные имена, которых нет в справочнике, то предыдущая формула будет автоматом относить человека к мужчинам, что не есть хорошо. Поэтому для полной универсальности можно добавить справочник мужских имен и еще одну проверку, как мы уже делали в варианте-2:

Источник

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

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

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

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

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

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

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

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

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

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

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

Источник

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, которые нужны при анализе данных. Жду ваши комментарии.

Источник

Как определить пол подписчика для email-рассылки

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

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

Первый метод: определение пола по ФИО

Ситуация идеальная, но все-таки встречается. Речь идет о базе, где есть фамилия, имя, отчество и они записаны в правильном порядке. В нашем случае информацию о ФИО нужно разместить в одной колонке. Традиционно все славянские отчества женщин заканчиваются на “а”, а мужские – на “ч”.

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

Получится следующая формула: =ЕСЛИ(ПРАВСИМВ(A2)=»ч»;»М»;»Ж»):

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

Определение пола для базы с некорректными или нетипичными именами/отсутствующим отчеством

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

Откорректируйте формулу, и укажите, что если последняя буква “ч”, то определяем контакт как мужчину, если “а” – как женщину, а если окончание ФИО не “а” и не “ч”, то выводим знаки вопроса “. ”, чтобы потом поработать с этими данными.

Функция будет выглядеть так: =ЕСЛИ(ПРАВСИМВ(A2)=«ч»;«М»;ЕСЛИ(ПРАВСИМВ(A2)=«а»;«Ж»;«. «))

Протяните формулу до конца списка.

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

Откройте вкладку “Данные” и выберите пункт “Создать фильтр”:

При помощи фильтра выведите на странице только строки с неопределенным полом. Для этого нужно снять галочки с других значений:

В списке будут видны:

Проставьте, где знаете, пол вручную.

Второй метод: определение пола подписчика с опорой на базу имен

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

Предварительно создайте 2 столбца: один – с женскими именами, второй – с мужскими. Список можно найти в любой доступной базе в интернете. Для автоматизации последующей работы сделайте списки с именами отдельными умными таблицами (функция доступна в Microsoft Excel после выделения ячеек и нажатия сочетания клавиш Ctrl+T). Это позволит вносить новые имена в конец таблицы и использовать их в формулах, не выделяя каждый раз нужный диапазон и не фиксируя ссылки.

Во вкладке “Конструктор” переименуйте таблицы в “жен” и “муж” для удобства дальнейшей работы и сокращения формулы.

Пропишите функцию: =ЕСЛИ(СУММ(ЕСЛИОШИБКА(ПОИСК(жен;E2);0))>0;»ж»;ЕСЛИ(СУММ(ЕСЛИОШИБКА(ПОИСК(муж;E2);0))>0;»м»;». «))

Формула включает в себя проверку на наличие имени в обоих списках имен. E2 – первая ячейка столбца с проверяемыми контактами.

Так как в данном случае мы работаем с массивом, то вместо Enter нажмите сочетание клавиш Ctrl+Shift+Enter. Протяните значение вниз.
Получаем наиболее точный результат:

Отфильтруйте поля со знаками вопросов и проставьте им пол вручную.

Фильтрация по полу и исправление данных в системе eSputnik

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

Вы можете автоматизировать работу с именами и создавать группы с учетом гендерной принадлежности подписчика прямо в системе eSputnik. Она будет анализировать ошибки в полном имени и определять пол. В вашу рассылку не попадут Колян, Саня, тем самым защитив вас от казуса. Для этого в “Лаборатории” (настройки аккаунта) нажмите кнопку “Добавить пол” и “Добавить точное имя”.

Определить пол подписчиков в eSputnik

Теперь, когда в каждой карточке контакте указан пол, создайте две условные группы: отдельно с мужчинами, отдельно с женщинами. Сегментированные рассылки дадут вам больше конверсий и сэкономят затраты за счет исключения отправки сообщений нецелевой аудитории. Отправляйте мужскую коллекцию мужчинам и женскую – женщинам, помогайте мужчинам сделать приятные подарки своим дамам к празднику.

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

Источник