Podczas pracy jako trener IT miałem okazję zaznajomić się z różnymi dokumentami dotyczącymi szkoleń, które był wypełniane przez Panie w biurze. Jednym z takich dokumentów jest "zbiorówka". Owa zbiorówka to arkusz Excela w którym są wypisane nazwiska kursantów oraz różne ich dane (np. telefon kontaktowy, PESEL, płeć, miejsce zatrudnienia itp.)
I tutaj pojawia się pewien paradoks: otóż taka zbiorówka często liczy np. 100 osób. Za każdym razem trzeba ręcznie wpisać płeć kursanta. Nie jest to trudne, ale można ten proces zautomatyzować.
Otóż wystarczy sprawdzić przedostatnią (10-ą) cyfrę numeru PESEL. Jeśli jest ona parzysta to mamy do czynienia z kobietą, jeśli nieparzysta to z mężczyzną. Napiszmy prostą formułę, która automatycznie wypisze płeć danej osoby na podstawie jej numeru PESEL.
Zanim podam gotową formułę, omówię pokrótce kilka przydatnych funkcji.
Funkcja Dł(tekst) zwraca długość tekstu np. =Dł(A2) zwróci z ilu znaków składa się tekst będący w komórce A2.
Funkcja FRAGMENT.TEKSTU(tekst; od_którego_znaku; ile znaków) wydobywa z ciągu tekstowego odpowiednią liczbę znaków od znaku, który podaliśmy w drugim parametrze. Przykładowo =FRAGMENT.TEKSTU(A2; 10; 1) zwróci nam 10-ty znak (a dokładniej jeden znak licząc od 10-ego z tekstu, który znajduje się w komórce A2.
Funkcja MOD(liczba; dzielnik) zwraca resztę z dzielenia danej liczby przez pewien dzielnik. Przykładowo =MOD(10; 2) zwróci 0, gdyż reszta z dzielenia dla liczby 10 przez 2 to 0. Z kolei =MOD(9; 2) zwróci 1 gdyż reszta z dzielenia 9 przez 2 to 1. Czyli należy zapamiętać, że jeśli reszta z dzielenia danej liczby przez 2 wynosi 0 to mamy do czynienia z liczbą parzystą, w przeciwnym przypadku z liczbą nieparzystą.
Funkcja JEŻELI(test_logiczny; wartość_jeśli_prawda; wartość_jeśli_fałsz) zwraca jedną z dwóch wartości z zależności od tego, czy test logiczny został spełniony (czyli czy odpowiedź na dane pytanie jest prawdziwa, lub fałszywa). Przykładowo: =JEŻELI(MOD(A2; 2)=0; "parzysta"; "nieparzysta"). W zależności od tego, czy liczba w komórce A2 jest parzysta, czy nieparzysta funkcja ta zwróci odpowiednią wartość.
Znając już te funkcje przystąpmy do zbudowania formuły. W komórce A1 napisz słowo PESEL. W komórce B1 napisz słowo Płeć. Następnie w komórce A2 wpisz dowolny numer PESEL (np. swój). Teraz małymi kroczkami zbudujmy funkcję, która zwróci płeć.
Na początek wydobędziemy 10-ty znak z numeru PESEL. W tym celu w komórce B2 wpisz formułę
=FRAGMENT.TEKSTU(A2; 10; 1)Jeśli wszystko poszło jak należy czyli pojawiła się w tej komórce przedostatnia cyfra numeru PESEL, to przejdźmy dalej. Zmodyfikuj funkcję na
=MOD(FRAGMENT.TEKSTU(A2; 10; 1); 2)Ta formuła z kolei zwróci nam liczbę 0 lub 1 w zależności od tego, czy przedostatnia cyfra numeru PESEL jest parzysta, lub nieparzysta. Teraz możemy przejść dalej. Dodajmy do tego funkcję JEŻELI, która pozwoli nam zwrócić bardziej czytelny wynik. Oto nasza formuła:
=JEŻELI(MOD(FRAGMENT.TEKSTU(A2;10;1);2)=1;"mężczyzna";"kobieta").Owa formuła ma drobną wadę: jeśli komórka A2 będzie pusta, lub tekst znajdujący się w niej nie będzie numerem lub będzie krótszy niż 10 znaków, to pojawi się błąd #ARG! Można dodać prostą obsługę błędów. Można to rozwiązać na kilka sposobów. Jednym z nich jest sprawdzenie, czy tekst w komórce A2 składa się dokładnie z 11 znaków i jeśli nie jest to prawda, to pojawi się komunikat o błędzie. Tutaj użyjemy zagnieżdżonej funkcji JEŻELI. Oto nasz formułowy "potworek":
=JEŻELI(DŁ(A2)<>11; "wprowadź prawidłowy PESEL";JEŻELI(MOD(FRAGMENT.TEKSTU(A2;10;1);2)=1;"mężczyzna";"kobieta"))Zewnętrzna funkcja jeżeli sprawdza, czy długość ciągu znaków jest różna od 11 znaków. Jeśli to prawda, zwróci komunikat z prośbą o wprowadzenie poprawnego numeru PESEL. W przeciwnym przypadku (czyli jeśli PESEL składa się z 11 znaków) zostanie wywołany trzeci parametr funkcji JEŻELI czyli będzie to funkcja JEŻELI podana w poprzednim przykładzie. Oczywiście to rozwiązanie nie jest pozbawione wad: jeśli wpiszemy do komórki A2 jakiś ciąg znaków nie będący numerem PESEL ale składający się z dokładnie 11 znaków, to zostanie wywołana druga funkcja jeżeli, która pewnie zwróci błąd #ARG! ale to proste zabezpieczenie i tak eliminuje większość niepoprawnie wprowadzonych wartości.
UWAGA: czasami może się zdarzyć, że pewna osoba ma błędnie wystawiony numer PESEL. Podczas prowadzenia zajęć spotkałem się z pewną Panią, która jako 10-ą cyfrę numer PESEL miała nieparzystą (czyli według zasad wystawiania numeru PESEL "teoretycznie była mężczyzną"). Opowiadała mi ona, że podczas ubiegania się o emeryturę miała perypetie z ZUSem, bo system komputerowy ZUSu twierdził, że jest ona mężczyzną. Tak więc w tym przypadku nie można w 100% ufać tej gotowej funkcji i należy sprawdzać, czy nie ma "kwiatków".
