Sprawdzanie numerów PESEl i REGON

by Marcin

Dzisiaj o formułach obliczających sumy kontrolne numerów PESEL i REGON. Wspomniane formuły umożliwiają sprawdzenie czy wpisane w komórkę numery identyfikacyjne są poprawne. Pokażę także jeden ze sposobów na „wyłuskanie” kilku dodatkowych informacji z numeru PESEL.

Nie będę dokładnie opisywał algorytmów. Sporo informacji znajdziesz tutaj. Przygotowując ten wpis opierałem się właśnie na tych informacjach (weryfikując to na kilku innych stronach, ale okazuje się, że w większości przypadków jako źródło i tak podaje się stronę wspomnianą przeze mnie). Poza tym Google twoim przyjacielem.

Numer REGON

Ostatnia cyfra numeru REGON jest sumą kontrolną. Obliczanie sumy kontrolnej zaczynamy zsumowaniem iloczynu każdej cyfry numeru z odpowiednio dobranym tzw. współczynnikiem wagowym. Suma kontrolna to reszta z dzielenia tej sumy przez 11. W przypadku, kiedy wynik dzielenia przez 11 wynosi 10, jako sumę kontrolną przyjmujemy 0.

Identyfikator REGON może być 7-mio (dawniej) lub 9-cio cyfrowy. Dla numeru 7-mio cyfrowego wagi wynoszą kolejno: 2,3,4,5,6,7, dla 9-cio cyfrowego: 8,9,2,3,4,5,6,7.
Ostatnia cyfra numeru, jak już wiesz, jest sumą kontrolną i oczywiście nie uwzględniamy jej w obliczeniach. Formuła, którą wymyśliłem na obliczenie sumy kontrolnej numeru REGON wygląda następująco:

=PRAWY(MOD(SUMA((FRAGMENT.TEKSTU(F4;WIERSZ(ADR.POŚR(„1:”& DŁ(F4)-1));1))*JEŻELI(DŁ(F4)=9;{8\9\2\3\4\5\6\7};{2\3\4\5\6\7}));11);1)

W komórce F4 znajduje się numer REGON. Jest to formuła tablicowa, więc po wprowadzeniu do komórki musisz zatwierdzić ją kombinacją klawiszy Ctr+Shift +Enter. Formuła zwraca sumę kontrolną jako wartość tekstową. Jeżeli bardzo chcesz, w każdej chwili, możesz przerobić to sobie na liczbę przy pomocy funkcji WARTOŚĆ.

Oczywiście sama suma kontrolna to nie wszystko. Dobrze byłoby jakoś wyróżnić poprawnie i niepoprawnie wprowadzony numer REGON. Możesz do tego celu wykorzystać formatowanie warunkowe. Przygotowując warunki nie możesz niestety skorzystać z formuł tablicowych, dlatego konieczne jest skorzystanie z komórki pomocniczej (możesz ją później ukryć).

W komórkę pomocniczą wprowadziłem funkcję sprawdzająca czy suma kontrolna równa jest ostatniej cyfrze numeru REGON i zwracająca wartość PRAWDA lub FAŁSZ:

=PRAWY(MOD(SUMA((FRAGMENT.TEKSTU(F4;WIERSZ(ADR.POŚR(„1:”& DŁ(F4)-1));1))*JEŻELI(DŁ(F4)=9;{8\9\2\3\4\5\6\7};{2\3\4\5\6\7}));11);1)=PRAWY(F4;1)

Ostatecznie wygląda to, u mnie w skoroszycie, mniej więcej tak:

Sprawdzanie numerów PESEL i REGON

W kolumnach D i E zastosowałem formatowanie warunkowe. Warunek wygląda następująco:
„formuła jest” – ” =$G4″.

Na rysunku widać wyraźnie, że numer REGON jest poprawny, kiedy wyliczona suma kontrolna równa jest ostatniemu znakowi numeru. Jeżeli warunek jest spełniony (REGON poprawny) kolor czcionki zmieniam na zielony. W przeciwnym wypadku mam domyślny kolor czerwony. Znak kropki oraz „ptaszka” otrzymałem wykorzystując odpowiednie symbole dostępne w zestawie czcionek Wingdings i Wingdings 2. Pomocnicze kolumny G i H można ukryć i końcowy rezultat może wyglądać tak:

Sprawdzanie numerów PESEl i REGON

Numer PESEL

Zasada weryfikacji numeru PESEL jest mniej więcej taka sama jak w przypadku numeru REGON. Obliczając sumę kontrolną, każdą cyfrę numeru (oprócz ostatniej, która jest sumą kontrolną) trzeba pomnożyć przez odpowiednią wagę i zsumować wyniki mnożenia. Inaczej niże w przypadku REGON, otrzymaną sumę dzielimy przez 10 i otrzymaną resztę z dzielenia odejmujemy od 10. Wagi dla numeru PESEL wynoszą odpowiednio: 1, 3, 7, 9, 1, 3, 7, 9, 1, 3.

Moja propozycja formuły obliczającej sumę kontrolna jest następująca:

=PRAWY(10-MOD(SUMA((FRAGMENT.TEKSTU(F12;WIERSZ(ADR.POŚR(„1:”& DŁ(F12)-1));1))*{1\3\7\9\1\3\7\9\1\3});10);1)

Podobnie jak w przypadku numeru REGON jest to formuła tablicowa.

Jeżeli nie potrzebujesz sumy kontrolnej, a wszystko, co chcesz zrobić to sprawdzić poprawność numeru możesz skorzystać z prostszej formuły (o szczegółach czytaj na wspomnianej, przeze mnie na początku, stronie internetowej) :

=WARTOŚĆ(PRAWY(SUMA((FRAGMENT.TEKSTU(F12;WIERSZ(ADR.POŚR(„1:”& DŁ(F12)));1))*{1\3\7\9\1\3\7\9\1\3\1});1))=0

Jeżeli numer PESEL jest poprawny formuła zwraca wartość PRAWDA.

Jak zapewne wiesz numer PESEL zawiera w sobie pewne dodatkowe informacje. Na pewno zwróciłeś uwagę, że pierwsze 6 cyfr to po prostu data urodzenia. Przedostatnia cyfra określa płeć (nieparzysta dla mężczyzn, parzysta dla kobiet).

Swój skoroszyt postanowiłem rozbudować wiec o możliwość „wyciągnięcia” tych dodatkowych informacji z numeru PESEL.

Sprawdzanie numerów PESEl i REGON

Formuła dla określenia płci jest stosunkowo prosta:

=JEŻELI(MOD(FRAGMENT.TEKSTU(F18;10;1);2)=0;”K”;”M”)

Nieco kłopotów jest z datą urodzenia. Wszystko przez to, że PESEL swoją skalą obejmuje …kilka stuleci. Dlatego też wprowadzono kilka zasad w zakresie numeru miesiąca, które umożliwiają wkodowanie w numer tak szerokiego zakresu dat.

Dla osób, które urodziły się w latach 1900-1999 miesiąc zapisywany jest bez żadnych zmian. W przypadku osób urodzonych w innych latach (lub jeszcze nieurodzonych), w zależności od stulecia, do miesiąca dodaje się odpowiednio:

dla lat 1800-1899 – 80
dla lat 2000-2099 – 20
dla lat 2100-2199 – 40
dla lat 2200-2299 – 60

W związku z tym moje propozycje formuł na „wyciagnięcie” z numeru PESEL roku, miesiąca i dnia urodzenia wyglądają następująco:

Rok

=INDEKS({1900\2000\2100\2200\1800};PODAJ.POZYCJĘ(WARTOŚĆ(FRAGMENT.TEKSTU(F18;3;2));
{0\20\40\60\80};1))+LEWY(F18;2)

Miesiąc

=FRAGMENT.TEKSTU(F18;3;2)-INDEKS({0\20\40\60\80};PODAJ.POZYCJĘ(WARTOŚĆ(FRAGMENT.TEKSTU(F18;3;2));
{0\20\40\60\80};1))

Powyższe formuły są formułami tablicowymi (Ctr+Shift+Enter).

Dzień

=FRAGMENT.TEKSTU(F18;5;2)

Oczywiście w praktyce, w każdym razie póki co, rok urodzenia większości żyjących osób (poza nielicznymi wyjątkami urodzonymi przed 1900 rokiem) mieści się w jednym z dwóch zakresów: 1900-1999 oraz 2000-2099. Dlatego jeżeli chcesz możesz użyć nieco krótszych funkcji dla roku

=INDEKS({1900\2000};PODAJ.POZYCJĘ(WARTOŚĆ(FRAGMENT.TEKSTU(F23;3;2));
{0\20};1))+LEWY(F23;2)

oraz miesiąca

=MOD(WARTOŚĆ(FRAGMENT.TEKSTU(F23;3;2));20)

Na koniec dodam, że w kwestii omawianych numerów identyfikacyjnych nie jestem w żadnym razie specjalistą. Jeżeli popełniłem gdzieś błąd, czy to w zakresie interpretacji algorytmu, czy też formuły Excela bardzo liczę na pomoc. Jeżeli znasz sposób na prostszą formułę Excela – proszę o komentarz.

{ 21 comments… read them below or add one }

Antek

A mógłbyś powiedzieć jak zrobić te zielone i czerwone kółeczka (tak by zmieniały sie warunkowo) ? przez formatowanie warunkowe ?

Z góry dziękuje….

POZDRAWIAM

PS. Świetny blog – nie raz mi pomogłeś…

Marcin

Tak, te czerwone/zielone kółeczka powstały przy zastosowaniu formatowania warunkowego. Poza tym zastosowałem inną czcionkę w komórce z kółeczkiem. NIe pamiętam w tej chwili jaka, ale na pewno taką, która w zestawie symboli ma kółeczko i „ptaszka”. Na pewno gdzieś tam znajdziesz.

Marcin

Antek

Dzięki wielkie za pomoc. Znaki o które mi chodziło znalazłem w czcionce „Marlet”…
i tak:
kółko – literka „N”
haczyk – literka „A”

POZDRAWIAM.

BoSZ

Nie moge zrozumieć istoty ADR.POŚR w tej funcji…a do tego wywala mi w niej błąd nawet przy bezpośrednim kopiuj wklej

jakieś pomysły czemu?

Marcin

>Nie moge zrozumieć istoty ADR.POŚR w tej funcji…

Pokombinuj … :) Poczytaj ….. na pewno zrozumiesz

>a do tego wywala mi w niej błąd nawet przy bezpośrednim kopiuj wklej

To chyba wina Wordpressa, ktory mi jakos dziwnie znaki zamienia (cudzyslowy na przyklad). Bardzo wiec czesto opcja skopiuj-wklej po prostu nie dziala. Pozostaje przepisac formulke, lub wkleic i pozamieniac recznie wszystkie znaki wygladajace „dziwnie”.

Marcin

pel

Witam
Nie wiem dlaczego ale u mnie dla wszystkich peseli wyświetla FAŁSZ i zły numer sumy kontrolnej
Czy ktoś też miał taki problem?

Marcin

@ pel

Gdybys podal nieco wiecej szczegolow na pewno bym Ci pomogl. Jaki numer pesel wspisujesz ? Moze po prostu podeslij mi na email arkusz excela gdzie Ci to nie dziala. Wtedy odszukam blad i poprawie – takze dla dobra innych.

Marcin

pel

Witaj Marcin

Bardzo chętnie tylko gdzie mogę znaleźć twój adres?

Pozdrawiam

Marcin

@ pel

Moj adres email znajdziesz na stronie glownej, po prawo, nieco na dole :)

Marcin

pel

Slepota
rzeczywiście
zaraz wysyłam
Pozdrawiam

dar

mi też zawsze fałsz wyskakuje i zła suma kontrolna:/

Marcin

Do mniej wiecej 10 grudnia jestem niestety „uwiazany” (sporo roznych obowiazkow, plus wm iare wazne egzaminy), ale obiecuje wszystkim, ze po 10 grudnia wszystkie problemy posprawdzam i wyjasnie. Poki co dziekuje za cierpliwosc.

Marcin

czacha

Odnośnie błędów z źle wyliczana sumą kontrolną to podejrzewam , że złe wprowadzacie formułę. Aby ją zatwierdzić należy wcisnąć Ctrl+Shift+Enter tak jak to jest napisane w aktykule

Adam

Ja osobiście datę z peselu wyciągam taką formułką:
JEŻELI(WARTOŚĆ(H17) >0;ZŁĄCZ.TEKSTY(ZŁĄCZ.TEKSTY(JEŻELI(FRAGMENT.TEKSTU(H17;3;2)=”80″;18;20));FRAGMENT.TEKSTU(H17;1;2));”-”;JEŻELI(FRAGMENT.TEKSTU(H17;3;2)=”80″;TEKST((FRAGMENT.TEKSTU(H17;3;2)-80);”00″);TEKST(FRAGMENT.TEKSTU(H17;3;2)-20;”00″)));”-”;FRAGMENT.TEKSTU(H17;5;2));””)
jak narazie działa dobrze i wyświetla mi całą datę w jednej komórce

pepo44

Wszystko jest super, ale czy w miarę prosty sposób można sprawdzić, czy w kolumnie w której znajduje się kilka tysięcy numerów PESEL nie znalazły się przypadkiem 2 lub więcej identycznych numerów (bez użycia sortowania).
Wydawało mi się, że problem jest prosty do rozwiązania, niestety chyba przekroczył mój mizerny poziom wiedzy w temacie Excel.

Piotrek

w sumie to trochę nie widzę sensu takiego rozwiązania. Z czysto teoretycznego punktu widzenia jeśli nie chcesz sortować to musisz porównywać numery parami, a wtedy masz złożoność tej zabawy O(n^2). Algorytmy sortowania osiągają O(nlogn), więc nawet jeślibyś potem miał to przeszukać, to i tak przy „kilku tysiącach numerów” będziesz na plusie…
pozdr

Artik

Macieju

Ta formuła:
=WARTOŚĆ(PRAWY(SUMA((FRAGMENT.TEKSTU(F12;WIERSZ(ADR.POŚR(“1:”& DŁ(F12)));1))*{1\3\7\9\1\3\7\9\1\3\1});1))=0
nie liczy do końca poprawnie.

Proponuję prześledzenie wątku na forum:
http://www.excelforum.pl/viewtopic.php?t=16270

Artik

Adam

w części formuły jest bład: ADR.POŚR(„1:”& DŁ(F12)));1))*{1\3\7\9\1\3\7\9\1\3\1})
sumę sprawdzać należy na 10 cyfrach, a więc prościej i poprawnie jest:
ADR.POŚR(„1:”& 10))*{1\3\7\9\1\3\7\9\1\3})
Oczywiście jesli nie wchodzi formuła po skopiowaniu to z powodu cudzysłowu, którego jest kilka wersji. Powinien być górny prosty cudzysłów ” z obu stron tekstu 1:

Piotr

Mam ważne pytanko, potrzebne mi by było w pracy takie coś, tylko że w user form
nazwa okienko do wpisywania sprawdzanego ciągu liczb i jakiś znaczek ,że wszystko gra. Da się to zrobić?

Joanna Schneider

Mam pytanie? czy istnieje jakas mozliwosc na odszukanie aktualnego adresu osoby po numerze Pesel? Pozdrawiam serdecznie Bardzo prosze o odpowiedz

Refael72

Coś nie tak z tym walidatorem. Niektóre PESELki są poprawne a to coś udające walidator wyrzuca FALSE

Leave a Comment

Previous post:

Next post: