Aby obliczyć wiek na podstawie numeru PESEL w Excelu, należy najpierw wyodrębnić datę urodzenia z PESEL-u, a następnie na jej podstawie wyliczyć wiek w latach. Poniżej znajdziesz szczegółowy, krok po kroku poradnik, wyjaśnienia dotyczące zasad działania PESEL-u oraz gotowe formuły do zastosowania w Excelu.
1. Zasady kodowania daty w PESEL
Numer PESEL składa się z 11 cyfr:
- RRMMDDPPPPK, gdzie:
- RR — dwie ostatnie cyfry roku urodzenia,
- MM — miesiąc urodzenia (z zakodowaniem stulecia!),
- DD — dzień urodzenia,
- PPPP — kolejne cyfry, m.in. kod płci,
- K — cyfra kontrolna.
Kluczowe są pierwsze 6 cyfr:
- Pierwsze dwie – rok urodzenia.
- Kolejne dwie – miesiąc, ale:
- Osoby urodzone w 1900–1999: MM bez zmian (01–12).
- Osoby urodzone w 2000–2099: do MM dodano 20 (czyli 21–32).
- Osoby urodzone w 1800–1899: do MM dodano 80 (czyli 81–92).
- Osoby urodzone w 2100–2199: do MM dodano 40 (czyli 41–52).
- Osoby urodzone w 2200–2299: do MM dodano 60 (czyli 61–72).
2. Wyodrębnianie daty urodzenia z PESEL w Excelu
Przykład: Komórka A2 zawiera numer PESEL jako tekst: "02270803628".
a) Wyodrębnij rok, miesiąc i dzień:
- Rok –
=LEWY(A2;2) - Miesiąc –
=FRAGMENT.TEKSTU(A2;3;2) - Dzień –
=FRAGMENT.TEKSTU(A2;5;2)
b) Określ stulecie na podstawie miesiąca
W zależności od wartości miesiąca, przyporządkuj odpowiednie stulecie i „odkoduj” właściwy miesiąc:
- Jeśli MM 01–12 → 1900–1999,
- Jeśli MM 21–32 → 2000–2099,
- Jeśli MM 81–92 → 1800–1899,
- Jeśli MM 41–52 → 2100–2199,
- Jeśli MM 61–72 → 2200–2299.
Można to zautomatyzować w formułach Excel.
3. Funkcje Excela – krok po kroku
Poniżej kompletna formuła, która dla PESEL w komórce B8 podaje liczbowo wiek na dzień bieżący –
=DATA.RÓŻNICA( JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>80; DATA(1800+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-80;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>60; DATA(2200+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-60;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>40; DATA(2100+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-40;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>20; DATA(2000+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-20;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); DATA(1900+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))) ) ) ) ); DZIŚ(); "Y" ) Ta formuła:
- Samodzielnie wybiera odpowiednie stulecie,
- Wylicza datę urodzenia,
- Oblicza pełne lata na podstawie dzisiejszej daty.
4. Wyodrębnianie daty urodzenia jako data w Excelu (bez wieku)
Jeśli potrzebujesz osobnej formuły tylko do uzyskania daty urodzenia:
=JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>80; DATA(1800+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-80;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>60; DATA(2200+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-60;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>40; DATA(2100+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-40;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); JEŻELI( WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))>20; DATA(2000+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2))-20;WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))); DATA(1900+WARTOŚĆ(LEWY(B8;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;3;2));WARTOŚĆ(FRAGMENT.TEKSTU(B8;5;2))) ) ) ) ) Zastosowanie tej formuły pozwoli Ci uzyskać datę urodzenia w oddzielnej komórce.
5. Obliczanie wieku – alternatywne sposoby i uwagi
- Do wyliczania wieku możesz użyć także funkcji ROK i TERAZ, ale najlepszą praktyką dla precyzji pełnych lat jest DATA.RÓŻNICA z parametrem „Y”.
- PESEL powinien być traktowany w Excelu jako tekst, by nie „gubić” początkowych zer.
- Formuły zakładają poprawny PESEL; warto rozważyć też walidację numeru za pomocą sumy kontrolnej.
6. Przykład – gotowe zastosowanie
Załóż, że lista PESEL znajduje się w komórkach B2:B100. W kolumnie C uzyskasz wiek wpisując powyższą formułę (dostosuj odwołanie do komórek).
7. Pełny proces (podsumowanie):
- PESEL jako tekst w Excelu.
- Formuły wyciągające rok, miesiąc i dzień z PESEL na podstawie wyjaśnionych zasad.
- Data urodzenia utworzona za pomocą funkcji DATA i warunków dotyczących stulecia.
- Oblicz wiek używając funkcji DATA.RÓŻNICA w stosunku do dzisiejszej daty.
8. Źródła dodatkowej wiedzy
- Szczegółowe opisy z przykładami wykorzystania funkcji tekstowych i daty znajdziesz w poradnikach Excelness.com i Malinowy Excel.
- Instrukcje wideo dostępne są w serwisie YouTube, np. kanał „Excelness” i „Malinowy Excel” pokazują cały proces krok po kroku z komentarzem.
Dzięki powyższym krokom, nawet początkujący użytkownik Excela poradzi sobie z wyciąganiem danych z PESEL i obliczaniem wieku na ich podstawie – zarówno pojedynczo, jak i dla całych list!
