Класична задача, з якою періодично стикається майже будь-який користувач Microsoft Excel: потрібно визначити стать для кожної людини в списку. Давайте розглянемо кілька рішень для такого завдання.
Варіант 1. Повні ПІБ, тільки "наші"
Почнемо, для розігріву, з найпростішого випадку, коли у нас є правильно записані повні ПІБ для всіх людей в списку. Як легко здогадатися, для більшості жителів колишнього СНД тут спрацює простий принцип: "якщо по батькові закінчується на Ч, то це чоловік, в іншому випадку - жінка". Реалізувати цю логіку можна простою формулою:
Функція ПРАВСИМВ (RIGHT) витягує з ПІБ один символ праворуч (останню букву по батькові), а функція ЯКЩО (IF) перевіряє витягнутий символ і виводить "ж" або "м", в залежності від результату перевірки.
Варіант 2. Повні ПІБ, є "експати"
Якщо в списку є імена не тільки українського типу (назвемо їх "експати"), то до наведеної раніше формулою можна додати ще одну перевірку, щоб відловлювати їх теж:
Тобто "м" буде виводитися тільки якщо батькові закінчується на Ч, "ж" - якщо закінчується на А. У всіх же інших випадках ( "експати") наша формула видасть три знаки оклику.
Варіант 3. Неповні або переставлені ПІБ, тільки "наші"
Якщо в нашому списку по батькові є не у всіх (або їх немає зовсім) або ПІБ йде в іншому порядку (ИФО, ІФ, ФМ), то доведеться використовувати принципово інший підхід. Створимо таблицю-довідника з усіма жіночими іменами (я використовував для цього википедию):
Створену таблицю я перетворив в "розумну" (виділити її і натиснути Ctrl + T), щоб потім не думати про її розміри і доповнювати довідник новими іменами в будь-який час. На вкладці Конструктор (Design) розумної таблиці краще дати окреме ім'я (наприклад дружин), щоб потім використовувати його в формулах:
Потрібна нам формула для визначення статі буде виглядати так:
Давайте розберемо її по кроках на прикладі першої людини:
Функція ПОШУК (SEARCH) шукає входження по черзі кожного жіночого імені з розумною таблиці дружин в рядок "Храброва Алла Сергіївна" і видає на виході або помилку # значить (якщо не знайшла), або порядковий номер символу, починаючи з якого ім'я входить в ПІБ. На виході ми отримуємо масив:
10: # значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить. # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить !:
# Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить. # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить !:
# Значить. # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить !: # значить. # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить !:
# Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить. # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить!: # Значить !:
# Значить. # Значить!: # Значить!: # Значить!: # Значить!>
Число 10 на сьомій позиції в цьому масиві фактично означає, що сьомий жіноче ім'я Алла з розумною таблиці-довідника входить у першу ПІБ Храброва Алла Сергіївна починаючи з 10 символу.
Потім функція ЕСЛІОШІБКА (IFERROR) замінює помилки # значить! на нулі. В результаті отримуємо:
Функція СУММ (SUM) підсумовує всі числа в отриманому масиві і якщо виходить число більше нуля, то функція ЯКЩО (IF) виводить "ж", в іншому випадку "м".
Не забудьте після введення формули натиснути клавіші Ctrl + Shift + Enter. тому її потрібно ввести як формулу масиву.
Варіант 4. Неповні ПІБ, є "експати"
Якщо в списку можуть зустрічатися експати або нестандартні імена, яких немає в довіднику, то попередня формула буде автоматично відносити людини до чоловіків, що не є добре. Тому для повної універсальності можна додати довідник чоловічих імен і ще одну перевірку, як ми вже робили в варіанті-2:
Тепер набагато краще :)