Formuły ·

Liczby słownie i walidacja numerów PESEL/REGON bez VBA

Arkusz Excela z formułą walidującą płeć z numeru PESEL

Dlaczego te dwa zadania łączy to samo podejście

Zamiana kwoty na zapis słowny i weryfikacja numeru PESEL czy REGON na pierwszy rzut oka to zupełnie różne zadania. Łączy je jednak jedna, wspólna zasada: oba problemy sprowadzają się do rozłożenia liczby na pojedyncze cyfry (albo grupy cyfr) i wykonania na nich osobnych, powtarzalnych operacji – a to można zrobić wyłącznie formułami Excela, bez pisania żadnego makra VBA, jeśli jesteś w środowisku, w którym makra są zablokowane z przyczyn bezpieczeństwa (częsty przypadek w firmach i urzędach).

Suma kontrolna numeru REGON

Numer REGON (7- albo 9-cyfrowy) ma w ostatniej cyfrze zapisaną sumę kontrolną, wyliczaną na podstawie wszystkich poprzednich cyfr. Algorytm: każdą cyfrę numeru (bez ostatniej) mnożysz przez przypisaną jej wagę, sumujesz wszystkie iloczyny, a resztę z dzielenia tej sumy przez 11 traktujesz jako sumę kontrolną (z zastrzeżeniem, że reszta równa 10 jest traktowana jako 0).

Wagi różnią się w zależności od długości numeru:

Długość REGONWagi (dla kolejnych cyfr)
7 cyfr2, 3, 4, 5, 6, 7
9 cyfr8, 9, 2, 3, 4, 5, 6, 7

Formuła tablicowa licząca sumę kontrolną (zakładając, że numer REGON jako tekst znajduje się w komórce F4):

{=RIGHT(MOD(SUM(MID(F4;ROW(INDIRECT("1:"&LEN(F4)-1));1)*1*IF(LEN(F4)=9;{8;9;2;3;4;5;6;7};{2;3;4;5;6;7}));11);1)}

Formuła musi zostać zatwierdzona kombinacją Ctrl+Shift+Enter. Działa następująco: MID(F4;ROW(...);1) rozbija numer na pojedyncze cyfry (bez ostatniej, kontrolnej), IF(LEN(F4)=9;...) dobiera odpowiedni zestaw wag zależnie od długości numeru, iloczyn obu tablic i SUM dają sumę wagowaną, a MOD(...;11) liczy resztę z dzielenia przez 11. RIGHT(...;1) na końcu zabezpiecza przed sytuacją, w której wynik MOD wynosi 10 – w notacji dziesiątkowej cyfra jedności liczby 10 to 0, co akurat zgadza się z regułą "reszta 10 = suma kontrolna 0".

Aby sprawdzić, czy numer jest poprawny, porównujesz wyliczoną sumę kontrolną z ostatnią cyfrą numeru:

{=RIGHT(MOD(SUM(MID(F4;ROW(INDIRECT("1:"&LEN(F4)-1));1)*1*IF(LEN(F4)=9;{8;9;2;3;4;5;6;7};{2;3;4;5;6;7}));11);1)=RIGHT(F4;1)}

Wynik PRAWDA oznacza numer poprawny. Ponieważ formatowanie warunkowe nie przyjmuje formuł tablicowych bezpośrednio, zanim podłączysz ten warunek jako regułę formatowania warunkowego (np. kolorowanie poprawnych numerów na zielono), wylicz najpierw wynik w osobnej, pomocniczej komórce (możesz ją później ukryć), a w regule formatowania warunkowego odwołaj się tylko do tej gotowej wartości PRAWDA/FAŁSZ.

Suma kontrolna numeru PESEL

PESEL ma inny algorytm wag i inny moduł dzielenia niż REGON. Jedenasta, ostatnia cyfra PESEL-u jest sumą kontrolną, wyliczaną z wag 1, 3, 7, 9, 1, 3, 7, 9, 1, 3 dla pierwszych dziesięciu cyfr, a wynik sumowania bierze się modulo 10 (nie 11, jak w REGON):

{=MOD(10-MOD(SUM(MID(F4;ROW(INDIRECT("1:10"));1)*1*{1;3;7;9;1;3;7;9;1;3});10);10)}

Porównanie z ostatnią, jedenastą cyfrą numeru, działa analogicznie jak w przypadku REGON:

{=MOD(10-MOD(SUM(MID(F4;ROW(INDIRECT("1:10"));1)*1*{1;3;7;9;1;3;7;9;1;3});10);10)=VALUE(RIGHT(F4;1))}

Wyciąganie daty urodzenia i płci z numeru PESEL

Struktura numeru PESEL koduje dodatkowe informacje, które można wydobyć bez żadnej bazy danych – samymi formułami tekstowymi. Pierwsze sześć cyfr to data urodzenia w formacie RRMMDD, ale z zastrzeżeniem: miesiąc jest zakodowany z przesunięciem zależnym od wieku, żeby odróżnić wiek XX od XXI i innych stuleci.

Zakres lat urodzeniaPrzesunięcie miesiąca
1900-1999+0
2000-2099+20
1800-1899+80
2100-2199+40
2200-2299+60

Formuła wyciągająca pełną datę urodzenia z numeru PESEL w komórce F4, obsługująca przesunięcia dla lat 1900-2099 (najczęstszy, praktyczny przypadek):

=DATE(IF(VALUE(MID(F4;3;2))>20;1900+VALUE(LEFT(F4;2));2000+VALUE(LEFT(F4;2)));IF(VALUE(MID(F4;3;2))>20;VALUE(MID(F4;3;2))-20;VALUE(MID(F4;3;2)));VALUE(MID(F4;5;2)))

Płeć kodowana jest w dziesiątej cyfrze numeru – liczba nieparzysta oznacza mężczyznę, parzysta kobietę:

=IF(ISODD(VALUE(MID(F4;10;1)));"Mężczyzna";"Kobieta")

Kwota słownie bez VBA – dlaczego formuła jest tak długa

Zamiana liczby na jej słowny zapis (np. „1 234,56” na „jeden tysiąc dwieście trzydzieści cztery złote 56/100”) jest jednym z tych zadań, które w VBA rozwiązuje się jedną, uporządkowaną funkcją z kilkoma tablicami słów i pętlą. Bez VBA trzeba odtworzyć tę samą logikę używając tylko formuł arkusza – i to jest dokładnie powód, dla którego rozwiązania "kwota słownie bez makr" wyglądają na pierwszy rzut oka przytłaczająco.

Zasada budowy takiej formuły, rozłożona na kroki (każdy w osobnej komórce pomocniczej, co jest tu zdecydowanie zalecane – próba spakowania wszystkiego w jedną formułę kończy się ciągiem znaków zajmującym kilka ekranów):

  1. Rozbij liczbę na grupy trzycyfrowe (jednostki, tysiące, miliony), korzystając z MOD i INT albo z TEXT z odpowiednim formatem i dalszym dzieleniem tekstu.
  2. Dla każdej grupy trzycyfrowej zbuduj osobno słowny zapis setek, dziesiątek i jedności, korzystając z CHOOSE albo INDEX na z góry przygotowanej liście słów ("sto", "dwieście", "trzysta" itd., analogicznie dla dziesiątek i jedności).
  3. Uwzględnij nieregularności języka polskiego – liczby 11-19 nie budują się z "dziesięć" + jedność, tak jak np. 21 buduje się z "dwadzieścia jeden". To wymaga odrębnej listy słów na przedział 10-19, nie samego mechanicznego składania dziesiątek z jednościami.
  4. Dodaj poprawną odmianę jednostki walutowej w zależności od liczby (1 złoty, 2-4 złote, 5 i więcej złotych, z dodatkowym wyjątkiem dla liczb 12-14, które mimo końcówki "2-4" używają formy "złotych") – to osobna logika CHOOSE/IF, niezależna od samej zamiany cyfr na słowa.
  5. Na końcu sklej wszystkie grupy (miliony, tysiące, jednostki) razem, pomijając te, które są równe zero (np. „1 000 000 złotych”, a nie „jeden milion zero tysięcy zero złotych”).

Każdy z tych pięciu kroków osobno jest prostą formułą tekstową opartą na CHOOSE, MOD, INT i IF – problemem jest tylko ich liczba i wzajemne powiązania, nie pojedyncza trudność żadnego z kroków. Jeśli budujesz to po raz pierwszy, zdecydowanie warto zacząć od komórek pomocniczych dla każdego kroku, a scalenie w jedną, finalną formułę zostawić na koniec, kiedy już wszystkie etapy działają poprawnie pojedynczo.

W praktyce, jeśli masz dostęp do VBA, prostsza i łatwiejsza w utrzymaniu jest funkcja użytkownika napisana w kodzie – z pętlą obsługującą każdą grupę trzycyfrową tym samym fragmentem kodu, bez powtarzania logiki pięć razy w formule arkusza. Rozwiązanie bez VBA ma sens głównie wtedy, gdy faktycznie nie możesz uruchamiać makr w docelowym środowisku (np. plik ma krążyć między komputerami z różnymi, nieznanymi Ci ustawieniami zabezpieczeń).

Checklist przy walidacji numerów identyfikacyjnych