Numer PESEL zawiera zaszyfrowane informacje o dacie urodzenia osoby. W tym szczegółowym przewodniku pokażę, jak krok po kroku wyodrębnić te dane przy użyciu funkcji Excela, tworząc automatyczny system konwersji numeru PESEL na pełną datę urodzenia.
Struktura numeru PESEL
Zanim przejdziemy do tworzenia formuł, musimy zrozumieć budowę numeru PESEL. Składa się on z 11 cyfr o następującej strukturze:
RR MM DD SSSS K
gdzie:
- RR – dwie ostatnie cyfry roku urodzenia
- MM – miesiąc urodzenia (z modyfikacją)
- DD – dzień urodzenia
- SSSS – numer seryjny
- K – cyfra kontrolna
Kluczowa informacja o miesiącu
Pierwsze sześć cyfr PESEL określa datę urodzenia. Aby odróżnić osoby urodzone w różnych stuleciach, do miesiąca urodzenia dodaje się określoną wartość:
- Lata 1900-1999 – miesiąc bez zmian (01-12)
- Lata 2000-2099 – miesiąc + 20 (21-32)
- Lata 2100-2199 – miesiąc + 40 (41-52)
- Lata 2200-2299 – miesiąc + 60 (61-72)
- Lata 1800-1899 – miesiąc + 80 (81-92)
Przykład – Osoba urodzona 9 czerwca 2011 roku będzie miała PESEL zaczynający się od 112609 – gdzie 11 to rok, 26 to czerwiec (6+20), a 09 to dzień.
Przygotowanie arkusza
Zacznijmy od przygotowania podstawowej struktury. Załóżmy, że:
- W kolumnie A znajdują się numery PESEL
- W komórce A2 mamy pierwszy numer PESEL do analizy
- Wynik (datę urodzenia) umieścimy w kolumnie B
Metoda 1 – Formuła krok po kroku dla początkujących
Ta metoda polega na stopniowym wyodrębnianiu poszczególnych elementów daty w osobnych kolumnach, co ułatwia zrozumienie procesu.
Krok 1 – Wyodrębnienie podstawowych elementów
Utwórzmy nagłówki w kolumnach C, D, E, F, G:
- Kolumna C – Pierwszy i drugi znak (RR)
- Kolumna D – Trzeci i czwarty znak (MM)
- Kolumna E – Piąty i szósty znak (DD)
- Kolumna F – Rok urodzenia
- Kolumna G – Miesiąc urodzenia
Krok 2 – Formuły do wyodrębnienia znaków
W komórce C2 wpisz:
=LEWY(A2;2) W komórce D2 –
=FRAGMENT.TEKSTU(A2;3;2) W komórce E2 –
=FRAGMENT.TEKSTU(A2;5;2) Funkcja LEWY pobiera określoną liczbę znaków od lewej strony tekstu, a FRAGMENT.TEKSTU wyodrębnia znaki z określonej pozycji.
Krok 3 – Określenie roku urodzenia
Tutaj wykorzystamy trzecią cyfrę PESEL (pierwszą cyfrę miesiąca) do ustalenia stulecia. W komórce F2 wpisz:
=JEŻELI(LEWY(D2;1)>=2;2000+WARTOŚĆ(C2);1900+WARTOŚĆ(C2)) Ta formuła sprawdza, czy pierwsza cyfra miesiąca jest większa lub równa 2. Jeśli tak, dodajemy 2000 do dwucyfrowego roku, w przeciwnym razie 1900.
Krok 4 – Korekta miesiąca
W komórce G2 zastosuj formułę korygującą wartość miesiąca:
=JEŻELI(WARTOŚĆ(D2)>20;WARTOŚĆ(D2)-20;WARTOŚĆ(D2)) To odejmie 20 od miesiąca dla osób urodzonych po 1999 roku.
Krok 5 – Utworzenie pełnej daty
W komórce B2 (lub innej docelowej) użyj funkcji DATA:
=DATA(F2;G2;WARTOŚĆ(E2)) Funkcja DATA tworzy prawidłową datę z trzech składowych: roku, miesiąca i dnia.
Metoda 2 – Uniwersalna formuła jednolinijkowa
Dla bardziej zaawansowanych użytkowników przedstawiam kompletną formułę, która wykonuje wszystkie operacje w jednej komórce. W komórce B2 wpisz:
=DATA( JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(A2;3;1))>=2; 2000+WARTOŚĆ(LEWY(A2;2)); 1900+WARTOŚĆ(LEWY(A2;2)) ); JEŻELI(WARTOŚĆ(FRAGMENT.TEKSTU(A2;3;2))>20; WARTOŚĆ(FRAGMENT.TEKSTU(A2;3;2))-20; WARTOŚĆ(FRAGMENT.TEKSTU(A2;3;2)) ); WARTOŚĆ(FRAGMENT.TEKSTU(A2;5;2)) ) Ta formuła:
- Analizuje trzecią cyfrę PESEL, aby określić stulecie
- Buduje pełny rok urodzenia (1900+ lub 2000+)
- Koryguje wartość miesiąca, odejmując 20 jeśli to konieczne
- Wyodrębnia dzień urodzenia
- Łączy wszystko w prawidłową datę
Metoda 3 – Funkcja LET dla Microsoft 365
Użytkownicy Microsoft 365 mogą skorzystać z funkcji LET, która pozwala na nazywanie fragmentów formuły i ich wielokrotne wykorzystanie bez powtarzania:
=LET( pesel;A2; rok;WARTOŚĆ(LEWY(pesel;2)); miesiac;WARTOŚĆ(FRAGMENT.TEKSTU(pesel;3;2)); dzien;WARTOŚĆ(FRAGMENT.TEKSTU(pesel;5;2)); pierwsza_cyfra_miesiaca;WARTOŚĆ(FRAGMENT.TEKSTU(pesel;3;1)); pelny_rok;JEŻELI(pierwsza_cyfra_miesiaca>=2;2000+rok;1900+rok); poprawny_miesiac;JEŻELI(miesiac>20;miesiac-20;miesiac); DATA(pelny_rok;poprawny_miesiac;dzien) ) Funkcja LET znacznie poprawia czytelność formuły, nadając nazwy poszczególnym elementom.
Rozszerzenie funkcjonalności
Obliczanie wieku na podstawie daty urodzenia
Po wyodrębnieniu daty urodzenia możesz łatwo obliczyć wiek osoby. W komórce obok daty wpisz:
=DATA.RÓŻNICA(B2;DZIŚ();"Y") Funkcja DATA.RÓŻNICA oblicza różnicę między datą urodzenia a dzisiejszą datą w pełnych latach („Y” oznacza lata).
Formatowanie numeru PESEL
Możesz sformatować numer PESEL dla lepszej czytelności, stosując niestandardowy format komórki lub funkcję TEKST.
Najczęstsze problemy i rozwiązania
Problem: Excel traktuje PESEL jako liczbę i obcina zera
Rozwiązanie – Upewnij się, że kolumna z numerami PESEL jest sformatowana jako tekst. Możesz to zrobić:
- Formatując kolumnę jako tekst przed wprowadzeniem danych
- Poprzedzając numer apostrofem:
'01234567890 - Importując dane z opcją „tekst” dla kolumny PESEL
Problem: Formuła zwraca błąd dla niektórych numerów
Rozwiązanie – Sprawdź, czy:
- Numer PESEL ma dokładnie 11 cyfr
- Nie ma spacji ani innych znaków w numerze
- Numer jest przechowywany jako tekst
Problem: Data wyświetla się jako liczba
Rozwiązanie – Sformatuj komórkę wynikową jako datę poprzez Format komórek → Data lub użyj skrótu Ctrl+1.
Walidacja poprawności numeru PESEL
Dla pełnego rozwiązania warto dodać sprawdzanie cyfry kontrolnej (ostatniej cyfry PESEL). Jest to bardziej zaawansowane zagadnienie wymagające zastosowania wag i modulo 10.
Praktyczne zastosowania
Automatyzacja wyodrębniania dat urodzenia z numerów PESEL jest szczególnie przydatna w:
- Działach HR przy zarządzaniu bazą pracowników
- Weryfikacji wieku klientów
- Automatycznym generowaniu raportów
- Przygotowywaniu dokumentacji wymagającej daty urodzenia
Podsumowanie
Wyodrębnienie daty urodzenia z numeru PESEL w Excelu to proces, który wymaga zrozumienia struktury tego numeru oraz umiejętnego zastosowania funkcji tekstowych (LEWY, FRAGMENT.TEKSTU), logicznych (JEŻELI) i daty (DATA). Wybór metody zależy od poziomu zaawansowania użytkownika i tego, czy preferuje przejrzystość (metoda krokowa) czy zwięzłość (formuła jednolinijkowa).
Kluczowym elementem jest prawidłowe rozpoznanie stulecia urodzenia na podstawie trzeciej cyfry PESEL oraz odpowiednia korekta wartości miesiąca. Po opanowaniu tych technik możesz łatwo rozszerzyć funkcjonalność o obliczanie wieku czy automatyczne generowanie innych informacji zawartych w numerze PESEL.
