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

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ść REGON | Wagi (dla kolejnych cyfr) |
|---|---|
| 7 cyfr | 2, 3, 4, 5, 6, 7 |
| 9 cyfr | 8, 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 urodzenia | Przesunię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):
- Rozbij liczbę na grupy trzycyfrowe (jednostki, tysiące, miliony), korzystając z
MODiINTalbo zTEXTz odpowiednim formatem i dalszym dzieleniem tekstu. - Dla każdej grupy trzycyfrowej zbuduj osobno słowny zapis setek, dziesiątek i jedności, korzystając z
CHOOSEalboINDEXna z góry przygotowanej liście słów ("sto", "dwieście", "trzysta" itd., analogicznie dla dziesiątek i jedności). - 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.
- 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. - 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
- Zawsze sprawdzaj długość numeru (
LEN) przed liczeniem sumy kontrolnej – formuła z niewłaściwą liczbą cyfr da błędny wynik bez ostrzeżenia. - Numer PESEL i REGON wpisywany do arkusza powinien być formatowany jako tekst, nie liczba – inaczej wiodące zera (częste w numerach REGON) zostaną automatycznie obcięte.
- Przy wyciąganiu daty urodzenia z PESEL pamiętaj o przesunięciu miesiąca zależnym od stulecia – sama wartość
MID(F4;3;2)bez korekty da błędną datę dla osób urodzonych po 1999 roku. - Formułę "kwota słownie" buduj etapami w komórkach pomocniczych – scalenie w jedną linię zrób na samym końcu, dla czytelności i łatwiejszego debugowania.