Teoria jest prosta: pierwsze sześć cyfr numeru PESEL wskazuje na datę urodzenia, kolejne cztery na płeć (zwykle wystarczy dziesiąta cyfra), a jedenasta cyfra jest cyfrą kontrolującą poprawność numeru PESEL. W tekście poniżej pokazuję, jak za pomocą Excela wyciągnąć te informacje z PESEL-a.
PESEL przed rokiem 2000
Zaczynamy od „rozebrania” numeru PESEL na części składowe. Zakładam, że w Excelu ten numer jest wpisany w komórce A1.
Pierwsze dwie cyfry, czyli rok urodzenia, można wyodrębnić funkcją =lewy(A1;2), która wybiera pierwsze dwa znaki od lewej ze wskazanego tekstu. Wynikiem będzie dwucyfrowy rok urodzenia.
Numer miesiąca, który jest „w środku” PESELa, można wyodrębnić funkcją =fragment.tekstu(A1;3;2), która z ciągu znaków w komórce A1 wybierze dwa znaki, zaczynając od znaku numer 3. Wynikiem będzie dwucyfrowy numer miesiąca.
To samo robimy, by uzyskać numer dnia =fragment.tekstu(A1;5;2), która wyciąga od piątego znaku w PESELU dwa kolejne znaki.
Teraz trzeba to poskładać w pełną datę urodzenia używając funkcji data, której kolejnymi argumentami są: rok, miesiąc i dzień
=data(lewy(A1;2);fragment.tekstu(A1;3;2);fragment.tekstu(A1;5;2))
PESEL po roku 2000
To wszystko działało bezproblemowo aż nadszedł rok 2000 i pojawiły się numery PESEL z zerami na początku i dziwnymi numerami miesięcy. Ludzie urodzeni np 10 sierpnia 1981 roku mają pierwsze cyfry PESEL-a następujące: 810810 – czyli rok miesiąc dzień. Natomiast ludziom urodzonym po roku 2000 do numeru miesiąca dodaje się liczbę 20. Czyli zapis 092718 wskazuje na osobę urodzoną w roku 2009 (09), w lipcu (27-20=7 czyli lipiec), osiemnastego. W takim PESEL-u Excel potrafi zniszczyć zero znajdujące się na początku zapisu. Dlatego ,dla bezpieczeństwa, warto komórki przeznaczone na PESEL formatować jako tekstowe.
Wcześniejszą funkcję trzeba dodatkowo skomplikować, żeby uwzględniała te zmiany. Wyjdzie nam teraz wielopiętrowo zagnieżdżona funkcja jeżeli. Bo to tą funkcja trzeba będzie sprawdzić, czy numer miesiąca jest większy niż 20.
Zacznę od testu logicznego =wartość(fragment.tekstu(A1;3;2))>20
Funkcja fragment.tekstu zwraca numer miesiąca jako tekst, więc konieczna jest jeszcze funkcja wartość, która ten tekst przekowertuje na liczbę, którą już łatwo sprawdzić, czy jest większa od 20.
Takie wyrażenie da wartość PRAWDA dla wszystkich miesięcy, do których dodana została liczba 20. FAŁSZ będzie dla osób sprzed roku 2000, które mają numer miesiąca wpisany normalnie (czyli 05 to maj).
Teraz zapakowuję to wyrażenie do funkcji JEŻELI, która po natrafieniu na duży numer miesiąca wyświetli poprawny rok i miesiąc.
=JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(A1;3;2))>20; DATA("20"&LEWY(A1;2);FRAGMENT.TEKSTU(A1;3;2)-20;FRAGMENT.TEKSTU(A1;5;2)); DATA(LEWY(A1;2);FRAGMENT.TEKSTU(A1;3;2);FRAGMENT.TEKSTU(A1;5;2)))
Pierwszy segment po teście logicznym (który jest wykonywany, jeżeli test daje wartość PRAWDA): DATA("20"&LEWY(A1;2);FRAGMENT.TEKSTU(A1;3;2)-20;FRAGMENT.TEKSTU(A1;5;2)) dodaje do dwucyfrowego roku dwudziestkę, żeby z 07 zrobić 2007, a od numeru miesiąca odejmuje liczbę 20. Tylko numer dnia nie wymaga żadnych zabiegów.
Drugi segment segment po teście logicznym (który jest wykonywany jeżeli test da wartość FAŁSZ): DATA(LEWY(A1;2);FRAGMENT.TEKSTU(A1;3;2);FRAGMENT.TEKSTU(A1;5;2)) daje datę urodzenia osoby urodzonej przed rokiem 2000. Jest to ta sama funkcja, która była na początku tekstu.
Dla utrudnienia dodam, że osoby, które urodzą się po roku 2100, będą miały do numeru miesiąca dodawaną liczbę 40, co będzie wymagać kolejnych poziomów zagnieżdżania funkcji jeżeli. Ale to już raczej nie nasz problem 😉
Płeć w PESEL
Teraz płeć. Najczęściej w PESEL płeć jest określana przez przedostatnią cyfrę. Jeżeli jest nieparzysta – wskazuje na mężczyznę, jeżeli jest parzysta bądź równa zero – wskazuje na kobietę.
Najpierw trzeba wydobyć przedostatnią cyfrę z numeru. Czyli cyfrę numer 10. Robię to funkcją =fragment.tekstu(A1;10;1)
Następnie sprawdzam, czy jest to cyfra nieparzysta =czy.nieparzyste(fragment.tekstu(A1;10;1))
Przy cyfrach nieparzystych ta funkcja da komunikat PRAWDA, przy parzystych FAŁSZ. Teraz pozostaje tylko zapakować to do funkcji jeżeli.
=jeżeli(czy.nieparzyste(fragment.tekstu(A1;10;1));"Mężczyzna";"Kobieta")
🙂
Sprawdzanie poprawności PESEL-a za pomocą cyfry kontrolnej zostawię sobie już na inną okazję 🙂
Pozdrawiam
Andrzej