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

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

Этот пример я решил разобрать, чтобы разобрать комбинацию из формул ЕСЛИ и ПРАВСИМВ.
Разберём тот случай, когда после применения не самой замудрённой формулы в 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 маркетинга под ключ». Если вам интересно глубже исследовать этот вопрос — добро пожаловать на курс! Тем более его основные материалы совершенно бесплатны.

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

Источник

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

Доброго времени суток! Нашел у Вас формулу, которая определяет пол по последней букве =ЕСЛИ(СУММПРОИЗВ(—(ПРАВСИМВ(A2;1)=<"а";"я">));»Ж»;»М»)
У меня возникла проблема:
Дан список контактов

Петрова Оксана Петровна
Шумилова Галина Васильевна
Желнова Алла Владимировна
Караульных Александр Игоревич
Солдатова Елена Евгеньевна
Худолей Александр Сергеевич

Петренко Виталий Александрович
Носырев Николай Николаевич
Петров Антон Валерьевич

В нем присутствует название компании, которая не относится ни к мужчинам ни к женщинам. Нужно чтоб этой ячейке назначалась к примеру «ХЗ».

Доброго времени суток! Нашел у Вас формулу, которая определяет пол по последней букве =ЕСЛИ(СУММПРОИЗВ(—(ПРАВСИМВ(A2;1)=<"а";"я">));»Ж»;»М»)
У меня возникла проблема:
Дан список контактов

Петрова Оксана Петровна
Шумилова Галина Васильевна
Желнова Алла Владимировна
Караульных Александр Игоревич
Солдатова Елена Евгеньевна
Худолей Александр Сергеевич

Петренко Виталий Александрович
Носырев Николай Николаевич
Петров Антон Валерьевич

В нем присутствует название компании, которая не относится ни к мужчинам ни к женщинам. Нужно чтоб этой ячейке назначалась к примеру «ХЗ». Николай

Сообщение Доброго времени суток! Нашел у Вас формулу, которая определяет пол по последней букве =ЕСЛИ(СУММПРОИЗВ(—(ПРАВСИМВ(A2;1)=<"а";"я">));»Ж»;»М»)
У меня возникла проблема:
Дан список контактов

Петрова Оксана Петровна
Шумилова Галина Васильевна
Желнова Алла Владимировна
Караульных Александр Игоревич
Солдатова Елена Евгеньевна
Худолей Александр Сергеевич

Петренко Виталий Александрович
Носырев Николай Николаевич
Петров Антон Валерьевич

Источник

Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах.

Начиная с версии Excel 2007, Microsoft добавила в Excel «старших сестер» функциям выборочного подсчета СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧЕСЛИ – функции СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИМН. В английском варианте эти функции выглядят как SUMIFS, COUNTIFS и AVERAGEIFS, т.е. имеют на конце букву -S, обозначающую в английском языке множественное число. В русской версии эту роль играет -МН.

Их часто путают, поскольку они очень похожи друг на друга и предназначены для подсчета на основе указанных критериев.

Разница в том, что СЧЕТЕСЛИ предназначен для подсчета ячеек с одним условием в одном диапазоне, тогда как СЧЕТЕСЛИМН может оценивать разные критерии в одном и том же или в разных диапазонах.

Как работает функция СЧЕТЕСЛИМН?

Она вычисляет количество соответствий в нескольких диапазонах на основе одного или множества критериев.

Синтаксис функции выглядит следующим образом:

СЧЕТЕСЛИМН(диапазон1;условие1; [диапазон2;условие2]…)

На самом деле, вам не нужно помнить этот синтаксис наизусть. Microsoft Excel отобразит аргументы функции, как только вы начнете печатать; аргумент, который вы вводите в данный момент, будет выделен жирным шрифтом.

Что нужно запомнить?

Считаем с учетом всех критериев (логика И).

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

Предположим, у вас есть список товаров, как показано на скриншоте ниже. Вы хотите узнать количество товаров, которые есть в наличии (у них значение в столбце B больше 0), но еще не были проданы (значение в столбце D равно 0).

Задача может быть выполнена таким образом:

Видим, что 2 товара (крыжовник и ежевика) находятся на складе, но не продаются.

б. Одинаковый критерий для всех диапазонов.

Если вы хотите посчитать элементы с одинаковыми критериями, вам все равно нужно указывать каждую пару диапазон/условие отдельно.

Например, вот правильный подход для подсчета элементов, которые имеют 0 как в столбце B, так и в столбце D:

Получаем 1, потому что только Слива имеет значение «0» в обоих столбцах.

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

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

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:

В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

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

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

В нашей таблице с примерами для подсчета заказов со статусом «Отменено» или «Ожидание» расчет будет выглядеть следующим образом:

Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.

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

Как сосчитать числа в интервале.

1. СЧЕТЕСЛИМН для подсчета ячеек между двумя числами

Чтобы узнать, сколько было получено заказов количеством товара от 10 до 20, сделаем так:

Это выражение будет возвращать то же количество, как показано на рисунке выше.

Как использовать ссылки в формулах СЧЕТЕСЛИМН.

При использовании логических операторов, таких как «>», » =» вместе со ссылками на ячейки, не забудьте заключить оператор в «двойные кавычки» и добавить амперсанд (&) перед ссылкой. Иначе говоря, требование к отбору должно быть представлено в виде текста, заключенного в двойные кавычки.

В приведенном примере посчитаем заказы с количеством более 30 единиц, при том что на складе в наличии было менее 50 единиц товара.

если вы записали значения ограничений в определенные клетки, скажем, в G1 и G2, и ссылаетесь на них.

Как использовать СЧЕТЕСЛИМН со знаками подстановки.

Традиционно можно применять следующие символы подстановки:

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

) перед звездочкой или знаком вопроса в записи параметра поиска.

Теперь давайте посмотрим, как вы можете использовать символ подстановки.

Предположим, у вас есть список заказов, за которыми персонально закреплены менеджеры. Вы хотите знать, сколько заказов уже кому-то назначено и при этом установлен срок их выполнения. Иначе говоря, имеются ли какие-то значения в столбцах B и Е таблицы.

Нам необходимо узнать количество заказов, для которых заполнены столбцы B и Е:

Несколько условий в виде даты.

Правила работы с датами очень похожи на рассмотренные выше вычисления с числами.

1.Подсчет дат в определенном интервале.

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

Следующие выражения подсчитывают в области с D2 по D21 количество дат, приходящихся на период с 1 по 7 февраля 2020 года включительно:

=СЧЁТЕСЛИМН(D2:D21;»>=01.02.2020″;D2:D21;» =»&H3;D2:D21;»

Как обычно, запишем двумя способами: со ссылками и без них:

3. Подсчет дат с различными критериями на основе текущей даты

Вы можете использовать функцию СЕГОДНЯ() для подсчета дат по отношению к сегодняшнему дню.

Эта формула с двумя областями и двумя критериями ответит вам, сколько товаров уже куплено, но еще не доставлено.

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

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

Я надеюсь, что вы найдете эти примеры и советы полезными. В любом случае, я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге ещё не раз.

Источник

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

KOMMENTARE • 32

ВАМ огромние спасиба «Nikolay Pavlov»

Отлично видео, тем более мне как раз этот способ очень подходит. Но при нажатии F9 появляется ошибка: «Слишком длинная формула. Длина формулы не должна превышать 8192 знака» Как это решить? Спасибо

Никак. Это ограничение Excel на длину формул. У вас после нажатия на F9 и подстановки исходных данных из ячеек вместо адресов получается слишком длинная строка 🙁

Николай, ОГРОМНОЕ СПАСИБО.

один трансгендер поставил дизлайк?))

НЕ В ТЕМУ. подскажите, такая ситуация: открываешь книгу а листов не видно, как сделать чтоб внизу книги появились листы. Спасибо

Давно пора F9 и «Ctrl z» использовать.

Прошу прощения, вопрос не по теме видео. Подскажите, где почитать/посмотреть более-менее полную инструкцию по работе с макросами, стоит задача комбинирования слов, которые записаны в разных столбцах, при этом необходимо включить исключения и иметь возможность изменять правила для комбинирования, то есть, простое комбинирование «всех на всех» не подходит. Заранее спасибо за ответ.

А почему сразу Bradd Pitt? 🙂

Ура! Спасибо! Нужное видео.

Спасибо за полезный урок!

Кудесник!))) Не требуется такая функция, но пройти мимо нового урока на ЭТОМ канале не могу!

объясните тупому почему ctrl+shift+enter а не просто enter? что произойдёт если нажать просто enter? он не будет вычислять или в чем не корректность будет?

@Nikolay Pavlov у меня имя Данияр определяется как женское, хотя в таблице имя как мужское указано

Алексей, функции типа ПОИСК изначально работают только с двумя ячейками («где ищем» и «что ищем»). В нашем же случае «что ищем» представлено целым списком-справочником женских/мужских имен, т.е. массивом. Чтобы Excel это понял и перебирал имена из массива по очереди и нужно жать Ctrl+Shift+Enter, т.е. вводить формулу как формулу массива, а не обычную (тогда будет ошибка).

Большое спасибо, Николай, за очередной полезный урок! ))))

Источник