Sprawdzanie numerów PESEl i REGON

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:

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:

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.

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.

Tagged as: Formuła tablicowa, Pesel, Regon, Sprawdzanie sumy kontrolnej