Wykrywanie płci za pomocą arkusza kalkulacyjnego

To nie pomyłka. W tym poradniku zamieszczam autorski pomysł na wykrywanie płci osób znajdujących się na listach arkusza. Może się to okazać niezwykle przydatne przy produkcji seryjnej imiennych dyplomów, certyfikatów, identyfikatorów lub innych materiałów, które (w zależności od płci) tytułować będziemy Pan lub Pani.

W moim przykładowym arkuszu wykorzystuję imiona do których dotarłem dzięki Wikipedii. Jest mało prawdopodobne, aby kiedykolwiek przyszło nam pracować z tak wielką grupą różnorodnych imion - doskonale wiemy, że wybór imion (a tym samym ich rozkład w grupach) jest uzależniony od ich popularności. Posługuję się w tym poradniku tak dużym zbiorem w celu przekonania do skuteczności zastosowanej metody. W zwykłych warunkach nie będziemy mieli aż takiej różnorodności.

Wiemy, że w znakomitej większości przypadków imiona żeńskie są zakończone samogłoską „a”, a męskie - spółgłoską. Są od tej zasady wyjątki, które powinniśmy respektować i obsługiwać, np. Miriam (abstrahuję tutaj od znanego reality show z Miriam w roli głównej, męskiej czy żeńskiej - Państwo pewnie wybrali…). Na ponad pół tysiąca imion pochodzenia angielskiego, bałtyckiego, celtyckiego, germańskiego, greckiego, hiszpańskiego, perskiego, słowiańskiego, semickiego i łacińskiego, tylko 11 nie kończy się na „a”. Są to rzadkie ale wymieniane w słownikach: Chloe, Bogudarz, Bogudać, Boguwłość, Dobrowieść, Dobrożyźń, Przybycześć, Świętożyźń, Abigail, Noemi i wspomniana już Miriam.

Krok 1 - Przygotowanie arkusza

Rozdzielamy komórki tak, żeby imiona ułożyły się w jednej kolumnie, a nazwiska w drugiej.

Zalecam, aby w pierwszym wierszu dodać nagłówek a następnie zablokować go przed przewijaniem. Dzięki temu przy dłuższych listach zawsze będziemy wiedzieć co edytujemy w danej kolumnie.

Krok 2 - Przygotowanie formuły

Wiedząc, że większość imion żeńskich kończy się na „a”, wykonamy analizę ostatniej litery w imieniu: jeśli będzie to „a” - oznaczymy płeć kobiecą. W innej sytuacji uznamy że to mężczyzna.

Formuła, która zwraca ostatnią literę z tekstu jest:

=PRAWY([komórka z imieniem];1).

Druga formuła, która nam będzie potrzebna to:

=JEŻELI([warunek];[wartość jeśli prawda];[wartość jeśli fałsz]).

W naszym przypadku:

=JEŻELI(PRAWY([komórka z imieniem];1)="a";"Pani";"Pan")

Mówiąc po ludzku: jeśli ostatnia litera zawartości komórki z imieniem to „a” - wyświetli się „Pani”, w przeciwnym razie „Pan”.

Prawie dobrze, tylko jeszcze nie obsługujemy wyjątków. W tym celu w naszym pliku utworzymy nowy arkusz „Wyjątki” w którym zapiszemy wszystkie odstępstwa od używanej formuły.

Mówiąc po ludzku: jeśli imię znajduje się na liście wyjątków lub jeśli ostatnia litera zawartości komórki z imieniem to „a” - wyświetli się „Pani”, w przeciwnym razie „Pan”.

Zapisując ten warunek w formule otrzymamy:

=JEŻELI(LUB(PRAWY([komórka z imieniem];1)="a";
LICZ.JEŻELI(wyjatki; ([komórka z imieniem]));"Pani";"Pan")

„wyjatki” to etykieta dla kolumny z arkusza „Wyjątki” - etykiety pozwalają odwoływać się do zakresów w komórkach „po ludzku” zamiast zapisu bezwzględnego np. A:A (jeśli wyjątki są zapisane w kolumnie A).

Oczywiście jeśli w komórce z imieniem pojawi się inny ciąg znakowy, nie będący imieniem, formuła również będzie (na podstawie ostatniej litery i wyjątków) działać, choć oczywiście nie będzie podawała logicznego wyniku. Można temu zapobiec poprzez wprowadzenie listy imion i warunku, który jeśli nie rozpozna imienia będzie ignorował napis „Pan”/„Pani”. W naszym przykładzie stosujemy prosty ale sprawny algorytm.

Pliki do pobrania

Choć nie lubię rozdawać gotowych rozwiązań na rzecz przeżywania wiedzy na podstawie inspisacji, w tym przypadku pozwolę zobie odejść od tej zasady, ponieważ dla wielu użytkowników arkuszy kalkulacyjnych kwestia formuł kojarzy się z drogą przez mękę. Dodatkowo uproszczenia, jakich dokonuję zapisując w nawiasie kwadratowym miejsce w którym znajdują się dane, mogą być postrzegane dosłownie, tj. ktoś może się dziwić, że formuła przekopiowana ze strony nie działa.