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

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

Этот пример я решил разобрать, чтобы разобрать комбинацию из формул ЕСЛИ и ПРАВСИМВ.
Разберём тот случай, когда после применения не самой замудрённой формулы в 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)=<"а";"я">));»Ж»;»М»)
У меня возникла проблема:
Дан список контактов

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

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

Источник

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

Сергей, ты про Бендера?

В романе «Золотой телёнок» Бендера именуют Остап Ибрагимович

Сергей, ты про Бендера?

В романе «Золотой телёнок» Бендера именуют Остап Ибрагимович MCH

Сергей, ты про Бендера?

Serge_007

Дата: Понедельник, 21.05.2012, 20:33 | Сообщение № 7

На самом деле он Остап Сулейман Берта-Мария Бендер Бей ЗЫ Майкл С правильно спросил: На самом деле он Остап Сулейман Берта-Мария Бендер Бей ЗЫ Майкл С правильно спросил: На самом деле он Остап Сулейман Берта-Мария Бендер Бей ЗЫ Майкл С правильно спросил: Serge_007 Дата: Понедельник, 21.05.2012, 20:53 | Сообщение № 9 Не-а Так называл его безграмотный Шура, а отчество у турецкоподданого было Сулейман. Не-а Так называл его безграмотный Шура, а отчество у турецкоподданого было Сулейман. Не-а Так называл его безграмотный Шура, а отчество у турецкоподданого было Сулейман. Источник Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов В этом руководстве мы обсудим синтаксис и особенности функции Excel ПСТР (в английской версии – MID), а затем вы узнаете несколько вариантов творческого её использования для выполнения сложных задач. Cинтаксис. Функция ПСТР возвращает указанное количество знаков, начиная с указанной вами позиции. Функция Excel ПСТР имеет следующие аргументы: ПСТР(текст; начальная_позиция; количество_знаков) Все 3 аргумента обязательны. Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу: Результат может выглядеть примерно так: 5 вещей, которые вы должны знать о функции Excel ПСТР Как вы только что убедились, в использовании функции ПСТР в Excel нет ничего страшного. И помня следующие простые факты, вы избежите наиболее распространенных ошибок. При решении реальных задач в Excel вам чаще всего потребуется использовать ПСТР в сочетании с другими функциями, как показано в следующих примерах. Как извлечь имя и фамилию. Если у вас была возможность прочитать наши недавние уроки, вы уже знаете, как вытащить имя с помощью функции ЛЕВСИМВ и получить фамилию с помощью ПРАВСИМВ. Но, как это часто бывает в Excel, одно и то же можно сделать разными способами. Получаем имя. Предполагая, что полное имя находится в ячейке A2, имя и фамилия разделены интервалом, вы можете извлечь имя, используя следующую формулу: ПОИСК используется для сканирования исходного значения на предмет пробела (» «) и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов после имени. Затем вы используете ПСТР, чтобы вернуть подстроку, начинающуюся с первого знака и заканчивая предшествующим пробелу, таким образом извлекая первое имя. Получаем фамилию. Чтобы извлечь фамилию из A2, используйте эту формулу: Как выделить подстроку между двумя разделителями. Продолжим предыдущий пример. А если, помимо имени и фамилии, ячейка A2 также содержит отчество, то как его извлечь? Технически задача сводится к определению позиций двух пробелов в исходном тексте, и вы можете сделать это следующим образом: Чтобы узнать количество извлекаемых знаков, вычтите позицию первого пробела из положения второго. И затем еще вычтите 1 из результата, поскольку вам не нужны лишние интервалы в получившемся результате. Таким образом, у вас есть второй аргумент для формулы: Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами: На следующем скриншоте показан результат: Аналогичным образом вы можете извлечь текст между любыми другими разделителями: Например, чтобы извлечь отрезок, выделенный запятой с пробелом после неё, используйте следующее выражение: На следующем рисунке эта формула используется для извлечения из адреса названия города, и она отлично справляется со своей задачей: Как получить N-е слово из текста. Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей: Общая формула выглядит следующим образом: Например, чтобы вытащить второе слово из A2, используйте это выражение: Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже: Как работает эта формула? По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике: Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР. Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например: =СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР($A$2))); (B2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2))) Следующий рисунок демонстрирует улучшенный вариант (2) в действии: Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки: =СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР(СЖПРОБЕЛЫ ($A$2)))); (B2-1)*ДЛСТР(СЖПРОБЕЛЫ($A$2))+1; ДЛСТР(СЖПРОБЕЛЫ($A$2)))) Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты. Извлекаем слово, содержащее определенный символ. В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места: Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму: =СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)); МАКС(1;НАЙТИ(«$»;ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)))-50);99)) Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее. Теперь разберём пошагово: Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99. С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один. Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500. Как заставить ПСТР возвращать число? Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст. Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число. Например, чтобы извлечь подстроку из 3 символов, начинающуюся с 7- го символа, и преобразовать ее в число, используйте: На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещены в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A: Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН: Вот как можно использовать функцию ПСТР в Excel. Как извлечь текст из ячейки с помощью Ultimate Suite Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами. Вы просто переходите на вкладку Ablebits Data > Текст, выбираете инструмент Split Text и в выпадающем списке нажимаете Извлечь (Extract) : Теперь вы выбираете исходные ячейки, и какие бы сложные строки они ни содержали, извлечение из них подстроки сводится к этим двум простым действиям: Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже. В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно: Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? 😉 Поскольку результаты представляют собой формулы, извлеченные подстроки будут обновляться автоматически, как только в исходные ячейки будут внесены какие-либо изменения. Когда в ваш набор данных добавляются новые записи, вы можете скопировать формулы в другие ячейки как обычно, без необходимости заново запускать инструмент «Извлечь текст». Если вам интересно попробовать это, а также множество других полезных функций, включенных в Ultimate Suite for Excel, вы можете загрузить ознакомительную версию. Если вам нравятся инструменты, вы можете получить лицензию по очень специальной цене, которая доступна только для наших русскоязычных пользователей. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе! Источник ← О чем говорит натоптыш под мизинцем Омез дср что такое дср → Вам также понравится старшая дочь ревнует к младшей сестре что делать 14.10.202326.08.2023 admin 0 обустройство кладовки в квартире 15.10.202314.08.2023 admin 0 проекты одноэтажных с сауной 15.10.202316.08.2023 admin 0 Добавить комментарий Отменить ответ Ваш адрес email не будет опубликован. Обязательные поля помечены * Комментарий * Имя * Email *