W internecie znajdziesz wiele stron pozwalających wygenerować losowy PESEL. Ponieważ skupiam się na Excelu, dlatego postanowiłem zrobić taki generator w Excelu (przy okazji również generatory NIP i REGON).
Żeby wygenerować losowy PESEL musimy znać jego strukturę. Większość z nas wie, że pierwsze 6 cyfr odpowiada za datę urodzenia. Już nie wszyscy wiedzą, że numery miesiąca zmieniają się w zależności od stulecia. Nas interesuje tylko XX wiek – lata 1900-1999 oraz XXI wiek – lata 2000-2099. XX wiek nie zmienia nic w numerowaniu miesięcy, ale XXI wiek dodaje liczbę 20 do numeru miesiąca w roku. Czyli osoba urodzona w 1910-05-23 i 2010-05-23 będzie miała początek PESEL odpowiednio 100523 i 102523.

Jak to zrobić w Excelu? Przede wszystkim potrzebujemy wylosować datę urodzenia. Świetnie się tu sprawdza funkcja LOS.ZAKR. Której wpisujemy w cudzysłowach daty minimalną i maksymalną pomiędzy którymi chcemy losować:

=LOS.ZAKR(„1900-01-01″;”2010-01-01”)

Porada 298 - Generator PESEL 01

Zanim zaczniemy przekształcać odpowiednio tą datę wylosujmy jeszcze płeć naszego posiadacza PESEL (od tego zależy 10 cyfra PESEL). Posłużymy się tutaj funkcją INDEKS, której na stałe wpiszemy tablicę liter ‘k’ i ‘m’ i będziemy losować albo 1 albo 2 pozycję tablicy (funkcja LOS.ZAKR):

=INDEKS({„k”;”m”};LOS.ZAKR(1;2))

Porada 298 - Generator PESEL 02

Teraz zacznijmy przekształcać datę. Będziemy musieli ją podzielić na część odpowiedzialną za rok, miesiąc i dzień. Rok będzie najprostszy, bo wystarczy odpowiednio sformatować wylosowaną datę jako rok za pomocą funkcji TEKST:

=TEKST([Data];”rr”)

Porada 298 - Generator PESEL 03

Powyższy zapis wstawia tylko 2 ostatnie cyfry z roku. Teraz przyszła pora na miesiąc. Z nim jest trudniej – możemy wyciągnąć miesiąc z daty za pomocą funkcji MIESIĄC, ale musimy dołożyć mechanizm, który dodawałby 20 dla XXI wieku (inne stulecia poza dwoma wcześniej ustalonymi nas nie interesują). W taki razie wystarczy sprawdzić, czy ROK z daty jest większy od 1999 i jeżeli tak to zwrócić 20, a jeśli nie to 0 za pomocą funkcji JEŻELI. Potrzeba do tego dodać jeszcze numer miesiąca z daty:

JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data])

Niestety to jeszcze nie wystarczy ponieważ dla miesięcy z XX wieku mogą nam wyjść wyniki 1 cyfrowe, a my potrzebujemy mieć zawsze 2 cydry, dlatego powyższą formułę musimy wstawić do funkcji TEKST, która będzie wymuszała wiodące 0. Tak uzyskaną formułę łączymy z cyframi roku:

=TEKST([Data];”rr”)&TEKST(JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data]);”00″)

Porada 298 - Generator PESEL 04

Potrzebujemy jeszcze dodać 2 cyfry jako dni. Robimy to analogicznie jak rok tylko wpisujemy do funkcji TEKST kod odpowiedzialny za formatowanie dni:

TEKST([Data];”dd”)

W ten sposób mamy pełną formułę budującą 6 cyfr numeru PESEL.

=TEKST([Data];”rr”)&TEKST(JEŻELI(ROK([Data])>1999;20;0)+MIESIĄC([Data]);”00″)&TEKST([Data];”dd”)

Porada 298 - Generator PESEL 05

Teraz zajmijmy się wyznaczeniem 4 kolejnych cyfr. Powiedzieliśmy sobie, że 10 cyfra oznacza płeć. Dokładnie liczby {0;2;4;6;8} oznaczają kobietę, a {1;3;5;7;9} mężczyznę. 3 wcześniejsze cyfry to kolejność urodzenia w danym dniu, czyli wystarczy funkcja LOS.ZAKR z odpowiednimi granicami. A musimy pamiętać, że to zawsze muszą być 3 cyfry, więc dodajemy funkcję TEKST, która zapewni ewentualne wiodące zera.

=TEKST(LOS.ZAKR(1;999);”000″)

Jak jednak ustalić numer odpowiedzialny za płeć.

Jak się mu przyjrzymy to możemy zobaczyć, że jeśli będziemy losować liczbę z przedziału od 0 do 4 i przemnożymy ją przez 2 to zawsze wyjdzie nam kobieta. W takim razie wystarczy, że sprawdzimy za pomocą funkcji JEŻELI, czy wylosowaliśmy wcześniej kobietę czy mężczyznę i odpowiednio zwracali 0 lub 1 i tą wartość dodali do naszego losowania.

LOS.ZAKR(0;4)*2+JEŻELI([@Płeć]=”k”;0;1)

Łącząc powyższe dwie formuły mamy 4 kolejne cyfry numeru PESEL:

=TEKST(LOS.ZAKR(1;999);”000″)&LOS.ZAKR(0;4)*2+JEŻELI([@Płeć]=”k”;0;1)

Porada 298 - Generator PESEL 06

Teraz czeka nas najtrudniejsze zadanie – musimy obliczyć cyfrę kontrolną. Zasada jej obliczania wymaga przemnożenia każdej cyfry przez przypisaną jej wagę, a następnie ich zsumowanie. Wagi możemy zapisać w tablicy {1;3;7;9;1;3;7;9;1;3} ważne, że ta tablica ma w Excelu orientację pionową (każdy średnik oznacza nowy wiersz).

Już kilka razy wyciągaliśmy pojedyncze znaki z tekstu (np.: porada 295 i 296) z numerem PESEL jest analogicznie, a nawet prościej, bo zawsze mamy 10 cyfr (musimy je tylko połączyć z wcześniejszych obliczeń) i możemy zapisać na stałe tablicę {1;2;3;4;5;6;7;8;9;10} (ta sama orientacja co tablica z wagami, żeby mieć pewność poprawnych wyników). Czyli nasze mnożenie i sumowanie wygląda tak:

=SUMA(FRAGMENT.TEKSTU([@DataP]&[@PłećP];{1;2;3;4;5;6;7;8;9;10};1)*{1;3;7;9;1;3;7;9;1;3})

To jeszcze nie koniec naszych obliczeń, bo z takiego mnożenia i sumowania (swoją drogą dzięki wpisaniu tablic na stałe możemy zatwierdzać tą formułę normalnie, a nie jako formułę tablicową) daje wynik większy od 1 cyfry. W obliczeniach liczby kontrolnej musimy wyciągnąć resztę dzielenia przez 10 (funkcja MOD) i odjąć ją od 10. Ponieważ czasami wynik może wyjść 10, to jeszcze raz musimy go wstawić do funkcji MOD:

=MOD(10-MOD(SUMA(FRAGMENT.TEKSTU([@DataP]&[@PłećP];{1;2;3;4;5;6;7;8;9;10};1)*{1;3;7;9;1;3;7;9;1;3});10);10)

Porada 298 - Generator PESEL 07

Uff. Mamy wszystkie elementy teraz musimy tylko połączyć je razem:

=[@DataP]&[@PłećP]&[@Kontrolna]

Porada 298 - Generator PESEL 08

W załączonym pliku znajdziesz jeszcze mechanizmy losujący numery NIP i REGON.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP