Trudność: 🔴 (trudne)
Jak sprawdzić numer PESEL?
Jeśli często rejestrujesz numery PESEL w arkuszu, to zapewne chcesz sprawdzić czy wpisywane numery są prawidłowe. Jest na to sposób… a nawet kilka.
Formuła dość łatwa
Metoda łatwa ale zawiła.
Krok 1:
zmieniamy wpisany numer PESEL na wartość tekstową (jedną z metod jest rozpoczęcie numeru PESEL od apostrofu)
Krok 2:
rozbijamy tekst (nr PESEL) na pojedyncze znaki
- przygotuj tabelkę jak na obrazku obok
- wpisz w C6 formułę =FRAGMENT.TEKSTU($C$2;B6;1)
- skopiuj ją w dół (oczywiście dwuklikiem)
Krok 3:
Zmieniamy pozyskane znaki tekstowe na wartości liczbowe
- dopisz w komórce D5 nagłówek Wartość ze znaku
- wpisz formułę =WARTOŚĆ(C6)
Krok 4:
Dopisujemy kolumnę z mnożnikami. Dlaczego takie, a nie inne? Tu znajdziesz wyjaśnienie.
- dopisz w komórce E5 nagłówek Mnożnik
- wypełnij kolumnę cyframi: 1, 3, 7, 9, 1, 3, 7, 9, 1, 3, 1
Krok 5:
Teraz wyliczamy (w dowolnej komórce, np. E18 iloczyn kolumny D i E) formułą =SUMA.ILOCZYNÓW(D6:D16;E6:E16)
Krok 6:
W komórce E19 wyciągamy z uzyskanej liczby ostatnią cyfrę przy pomocy formuły =TEKST(E18;”0″)
Krok 7:
Musimy sprawdzić czy ostatnia cyfra numeru PESEL jest równa zero. Ale zauważ, że wynik z kroku 6 jest wartością tekstową. Dlatego porównujemy uzyskany znak ze znakiem tekstowym “0” przy pomocy formuły =JEŻELI(E19=”0″;”poprawny”;”zły”)
Kroki 5, 6 i 7 da się połączyć w jedną formułę:
=JEŻELI(PRAWY(SUMA.ILOCZYNÓW(D6:D16;E6:E16))=”0″;”poprawny”;”zły”)
W jednej formule
W poniższym przykładzie wykorzystujemy powyższe obliczenia (skomasowane do jednej formuły) oraz stałe tablicowe (te pisane w nawiasach klamrowych). Jeśli stałe tablicowe są Ci obce… musisz przyjąć ten przykład “na wiarę i autorytet” 😉
W dowolnej komórce piszemy formułę:
=JEŻELI(WARTOŚĆ(PRAWY(TEKST(SUMA.ILOCZYNÓW(WARTOŚĆ(FRAGMENT.TEKSTU($C$5;{1;2;3;4;5;6;7;8;9;10;11};1));{1;3;7;9;1;3;7;9;1;3;1});”0″)))=0;”poprawny”;”zły”)
Jeśli numer PESEL znajduje się w komórce C5, zostanie oceniony czy jest poprawny czy nie.
To nie są wszystkie sposoby na ocenę poprawności nr. PESEL, ale mam nadzieję, że się przydadzą.
W nazwie
No to idziemy teraz po bandzie… Wymagania to zrozumienia tego przykładu są spore. Jeśli któryś z wymienionych poniżej tematów sprawia Ci kłopot… nie czytaj dalej 😉
Musisz dobrze znać i rozumieć:
- Formatowanie warunkowe
- użycie funkcji LAMBDA()
- weryfikacja poprawności nr. PESEL
Jeśli powyższe tematy nie sprawiają Ci kłopotu, to idziemy dalej. Plan jest taki:
- utworzymy nazwę obliczeniową
- wykorzystamy ją w Formatowaniu warunkowym
Krok 1:
Tworzymy nazwę obliczeniową. To trudne… ale możliwe. Więcej na temat nazw obliczeniowych znajdziesz w artykule: “MS Excel – nazwa obliczeniowa”. W dużym skrócie tworzymy nazwę obliczeniową sprawdzającą zadaną komórkę pod kątem poprawności numeru PESEL. Zmienną lokalną w LAMBDA() będzie PESEL.
=LAMBDA(PESEL; JEŻELI(WARTOŚĆ(PRAWY(TEKST(SUMA.ILOCZYNÓW(WARTOŚĆ(FRAGMENT.TEKSTU(PESEL;{1;2;3;4;5;6;7;8;9;10;11};1));{1;3;7;9;1;3;7;9;1;3;1});”0″)))=0;”poprawny”;”zły”) )
Krok 2:
Tworzymy Format warunkowy.
Jeśli we komórce z tym formatowaniem warunkowym jest poprawny nr PESEL, komórka zmieni kolor na zielony.