Pracując z danymi osobowymi w Excelu często spotykamy się z numerami PESEL, które potrafią sprawiać trochę problemów, ale też dostarczają informacji, których nie musimy osobno pozyskiwać. W dzisiejszym wpisie pokażę jak z numeru PESEL w Excelu wyciągnąć datę urodzenia, płeć, jak pracować z numerami PESEL oraz jak sprawdzić czy wprowadzony numer jest poprawny.
UWAGA: wszystkie numery PESEL w przedstawionych przykładach zostały wygenerowane w sposób losowy.
Jak wpisać numer PESEL w Excelu
Problemy w pracy z numerami PESEL najczęściej wynikają z tego, że mogą zaczynać się od zer, a takie wartości zazwyczaj przez Excela zamieniane są na zwykłe liczby. Jeśli numery PESEL kopiujemy z jakiegoś źródła to ich format może zostać zachowany i do Excela trafią jako wartości tekstowe. Natomiast numery wpisywane bezpośrednio zamienione zostaną na zwykłe liczby. Najważniejsze w tym momencie jest dla nas zrozumienie, że numer PESEL powinien być przez nas traktowany jako tekst a nie liczba. A dokładnie tekst, który składa się z dokładnie 11 znaków, są to liczby od 0 do 9 a każda z nich ma swoje znaczenie. Pierwsze o co trzeba zadbać to, aby nie zniknęły nam zera wiodące (na początku numeru). Są na to trzy sposoby:
Sposób 1 – Użycie apostrofu ‘ przed numerem – potraktowanie przez Excel liczby jako tekst
Najprostsza metoda, gdy po prostu musimy wprowadzić numer PESEL do Excela to wpisanie go zaczynając od znaku apostrofa ‘. To znak, który wskazuje Excelowi, że ma tę liczbę traktować jako tekst, więc nie pozbywa się zer wiodących. Co ważne apostrof nie jest wyświetlany ani brany pod uwagę przez formuły działające na tekście. Jedenastoznakowy numer z apostrofem na początku dla Excela nadal ma 11 znaków. Jest to najlepsza metoda, gdy musimy ręcznie wpisać kilka numerów PESEL.
Sposób 2 – Użycie funkcji TEKST
Gdy już pracujemy numerami PESEL w Excelu to zazwyczaj są ich duże ilości i wpisywanie ręcznie z apostrofem lub dodawanie go oraz brakujących zer na pewno nie jest dobrym pomysłem. Jedną z dwóch formuł, które najczęściej używam do sformatowania numerów PESEL to funkcja TEKST(). Jej pierwszym argumentem jest wartość, która ma być zamieniona na tekst, a drugi argument to ustawienie formatu. W tym przypadku format powinien być określony jako „00000000000” (11 zer), co oznacza, że każda liczba będzie zamieniona na tekst o długości 11 znaków, a brakujące znaki na początku zostaną zamienione na zera. Jest to najprostszy sposób na znikające zera wiodące.
Sposób 3 – użycie funkcji PRAWY
Druga formuła, którą również często stosuję to funkcja PRAWY(), która pobiera określoną liczbę znaków z tekstu licząc od prawej strony. Jeżeli jako pierwszy argument podamy liczbę, to automatycznie zostanie potraktowana jako tekst. Problem z tym, że musimy tu do tej liczby dodać na początku zera, jeżeli ich brakuje. Dlatego łączymy 3-4 zera z liczbą, a następnie bierzemy 11 znaków z prawej strony. Efekt jest ten sam co w przypadku formuły TEKST tak, więc możemy ja używać jak nam wygodniej.
Jak określić płeć po numerze PESEL w Excelu?
Płeć w numerze PESEL określona jest w bardzo prosty sposób. Jeśli przedostatnia cyfra jest parzysta to jest to PESEL kobiety, jeśli nieparzysta to mężczyzny. Tu również pokażę różne sposoby jak formułami określić płeć osoby.
Sposób 1 – Formuła CZY.PARZYSTE
Z numeru PESEL wyciągamy przedostatnią cyfrę za pomocą funkcji FRAGMENT.TEKSTU, czyli bierzemy dziesiąty znak, długość tekstu to jeden. Następnie umieszczamy to w formule CZY.PARZYSTE. Jest to nasz warunek logiczny w funkcji JEŻELI. Gdy warunek jest spełniony to wynikiem formuły ma być tekst „Kobieta”, w przeciwnym wypadku „Mężczyzna”.
Sposób 2 – Reszta z dzielenia, funkcja MOD
Podobnie jak wcześniej wyciągamy przedostatni znak i dajemy go do funkcji MOD, która liczy resztę z dzielenia. Dzielnikiem jest liczba 2. Gdy reszta z dzielenia jest równa zero, czyli liczba jest parzysta to znaczy, że PESEL jest kobiety, jeśli nie to mężczyzny.
Jak wyciągnąć datę urodzenia z numeru PESEL w Excelu?
Datę urodzenia w numerze PESEL określa sześć pierwszych cyfr. Dwie pierwsze to rok, kolejne dwie określają miesiąc a kolejne dni. Dla osób urodzonych w ubiegłym stuleciu sprawa jest prosta, ponieważ numery miesiąca nie są w żaden sposób przekształcane. Dla osób urodzonych od 2000 roku numer miesiąca jest powiększony o 20, a w przypadku osób urodzonych w XIX wieku dodane jest 80. Numer miesiąca to element, który najbardziej komplikuje określenie daty urodzenia. Poradzić sobie z tym możemy na dwa sposoby.
Sposób 1 – data urodzenia z numeru PESEL użyciem tylko formuł
Pierwsze co musimy uwzględnić to stulecie, w którym urodziła się osoba. To określa nam numer miesiąca, dlatego naszą formułę dzielimy na 3 części. Jeżeli numer miesiąca jest większy od 80 to mamy stulecie 1800, jeśli większy od 20 to 2000, a w pozostałych przypadkach 1900. Numer miesiąca wyciągamy formułą FRAGMENT.TEKSTU. Pierwszy parametr to numer PESEL, drugi to od którego znaku chcemy pobrać fragment tekstu, a trzeci określa długość pobieranego tekstu. W tym przypadku chcemy wyciągnąć trzecią i czwartą cyfrę, więc drugi parametr to 3, a trzeci to 2. Aby porównać te z liczbą 80 i 20 musimy zamienić tekst na wartość. Najprościej zrobić to mnożąc tekst razy jeden. Tak też będziemy robić z pozostałymi liczbami.
Gdy już mamy określone stulecie to korzystamy z funkcji DATA, która tworzy datę z podanych wartości. Rok to stulecie plus dwie pierwsze liczby z numeru PESEL. Miesiąc to trzeci i czwarty znak, który w dwóch przypadkach trzeba zmniejszyć o 20 lub 80. Dzień to po prostu piąty i szósty znak numeru PESEL.
W ten sposób otrzymujemy jedną długą formułę, która działa dla osób urodzonych od 1800 do 2099. Jeśli w naszych danych nie ma osób z przed 1900 roku to można formułę uprościć i usunąć całą pierwszą linijkę z przedstawionej formuły.
Sposób 2 – użycie pomocniczej tabeli
W tym sposobie wykorzystujemy pomocniczą tabelę, która dla określonego numeru miesiąca w numerze PESEL ma przypisaną wartość stulecia oraz miesiąca kalendarzowego. Podobnie jak wcześniej wykorzystujemy funkcję DATA. Stulecie wyszukujemy z pomocniczej tabeli szukając trzeciego i czwartego znaku z numeru PESEL oraz dodając wartość dwóch pierwszych znaków (funkcja lewy). Miesiąc podobnie wyszukujemy z tabeli pomocniczej z trzeciej kolumny, a dzień to piąty i szósty znak. Użycie w tabeli pomocniczej w pierwszej kolumnie tekstu dla trzeciego i czwartego znaku PESEL ogranicza ilość mnożenia przez 1 w całej formule. W tym przypadku formuła jest krótsza i łatwo ją rozbudować o kolejne stulecia. Wadą jest konieczność przygotowania tabeli pomocniczej. Dlatego ten sposób jest wygodny, gdy taką tabelę mamy już wcześniej przygotowaną.
Jak sprawdzić w Excelu czy numer PESEL jest poprawny?
Numery PESEL mają zaszyte w sobie sprawdzenie czy dany numer jest na pewno poprawny. Sposób ten jest stosowany w wielu systemach nadawania numerów. Polega on na tym że każdą z pierwszych dziesięciu cyfr należy przemnożyć przez określoną liczbę, a następnie wszystkie dodać. Pierwszą mnożymy przez 1, drugą przez 3, trzecią przez 7 itd. według poniższego wzoru
1*a + 3*b + 7*c + 9*d + 1*e + 3*f + 7*g + 9*h + 1*i + 3*j
Z sumy tych wszystkich iloczynów bierzemy tylko ostatnią cyfrę. Możemy ją łatwo wyciągnąć biorąc po prostu resztę z dzielenia przez 10, czyli używamy funkcję MOD. Następnie musimy odjąć tę liczbę od liczby 10. Jeżeli wynik tych działań matematycznych jest równy wartości ostatniej cyfry w numerze PESEL to numer jest poprawny.
Podsumowanie
Jeśli pracujesz z danymi osobowymi to warto znać te sposoby na wyciąganie informacji z numeru PESEL. Ma to swoje zastosowanie w sytuacji, gdy:
- Nie masz kompletnych danych o płci oraz dacie urodzenia
- Gdy chcesz zweryfikować poprawność danych – np. w wypełnianych formularzach
- Gdy chcesz, aby część formularza wypełniała się sama po wprowadzeniu do niego numeru PESEL
Zachęcam do pobrania pliku z zaprezentowanymi przykładami. Gotowe formuły na pewno będą pomocne.
Zobacz również
