Jak odczytać informacje z numeru PESEL formułami w Excelu?

W tej miniserii trzech filmów omawiam strukturę PESELu i buduję formuły wydobywające z niego wszystkie informacje: datę urodzenia, płeć, sumę kontrolną. Więcej informacji o strukturze Peselu można znaleźć tu:
https://www.gov.pl/web/gov/czym-jest-numer-pesel

Wydobywanie informacji o płci

Płeć kodowana jest na przedostatniej, 10 cyfrze. Nieparzysta oznacza mężczyznę, a parzysta kobietę. W fimie użyłem formuły:

=JEŻELI(  CZY.PARZYSTE( FRAGMENT.TEKSTU(A2;10;1) ); "K"; "M" )

Wydobywanie daty urodzenia

W tym filmie wydobywam po kolei dzień, miesiąc, rok urodzania, i finalnie składam je w jedną formułę zwracającą kompletną datę urodzenia. Pokazuję też jak rozróżnić „stare” i „nowe” Pesele.

Sam dzień urodzenia: 
=FRAGMENT.TEKSTU(A2;5;2)*1
Sam miesiąc urodzenia: 
=LET(
            mcur; FRAGMENT.TEKSTU(A2;3;2)*1;
            JEŻELI(mcur>20; mcur-20; mcur )
)
Sam rok urodzenia: 
=LET(
            mcur; FRAGMENT.TEKSTU(A2;3;2)*1;
             rur;     FRAGMENT.TEKSTU(A2;1;2)*1;

            JEŻELI(mcur>20;  rur+2000; rur+1900 )
)
A to formuła, którą zbudowałem w filmie, wydobywająca pełną datę urodzania. Wystarczy tylko zmienić „A2” na nowy adres, i nadać komórce z datą formatowanie daty. 
=LET(
        kom; A2;

        dur;  FRAGMENT.TEKSTU(kom;5;2)*1;
        mcur; FRAGMENT.TEKSTU(kom;3;2)*1;
        rur;  FRAGMENT.TEKSTU(kom;1;2)*1;

        mmm; JEŻELI(mcur>20; mcur-20; mcur );
        rrr; JEŻELI(mcur>20;  rur+2000; rur+1900 );

        DATA(rrr; mmm; dur)
)

Obliczanie sumy kontrolnej

Suma kontrolna to ostania, 11 cyfra Peselu. Oblicza się ją wykonując kilka prostych kroków obliczeniowych, które omawiam w filmie obok. Jej celem jest sprawdzenie poprawności Peselu, np. w celu uniknięcia błędów przy dyktowaniu: każda zmiana cyfr od 1 do 10 spowoduje inne obliczenie sumy kontrolnej.

W filmie EPXPL zbudowałem tę oto ofrmułe, która zwraca obliczoną sumę kontrolną jako cyfrę: 
 10-MOD( SUMA( MOD( FRAGMENT.TEKSTU(A2;{1\2\3\4\5\6\7\8\9\10};1) * {1\3\7\9\1\3\7\9\1\3}; 10 ) ); 10)
To samo wyrażenie można też zawinąć w funkcję LET, która pozwala na czytelne rozpisanie co robimy, krok po kroku: 
=LET(
    pesel; A2;

    tablica_z_peselu; FRAGMENT.TEKSTU(pesel;{1\2\3\4\5\6\7\8\9\10};1);
    tablica_wag;  {1\3\7\9\1\3\7\9\1\3};

    krok1; tablica_z_peselu*tablica_wag;
    krok2; MOD(krok1;10);
    krok3; SUMA(krok2);
    krok4; MOD(krok3;10);

    10-krok4
)

Walidacja (zwraca PRAWDA / FAŁSZ)

A oto ta sama formuła po drobnej modyfikacji. Tak jak na filnmie EPXPL, dodałem na początku porównanie. Wyciągam ostatnią, 11 cyfrę i porównuję ją do obliczonej moją formułą: 
=  PRAWY(A2)*1=    10-MOD( SUMA( MOD( FRAGMENT.TEKSTU(A2;{1\2\3\4\5\6\7\8\9\10};1) * {1\3\7\9\1\3\7\9\1\3}; 10 ) ); 10)
Poprzednie wyrażenie można też zapisać jak poniżej, używając szkieletu funkcji LET. Uzytkownik musi zmienić tylko pierwszą linię, wskazując na adres komórki zawierającej PESEL do walidacji. 
=LET(
    pesel; A2;

    tablica_z_peselu; FRAGMENT.TEKSTU(pesel;{1\2\3\4\5\6\7\8\9\10};1);
    tablica_wag;  {1\3\7\9\1\3\7\9\1\3};
    ostatnia_z_peselu; PRAWY(pesel)*1;

    krok1; tablica_z_peselu*tablica_wag;
    krok2; MOD(krok1;10);
    krok3; SUMA(krok2);
    krok4; MOD(krok3;10);

    ostatnia_z_peselu=10-krok4
)
Czy wiesz jak łatwo ukrywać i zaznaczać kształty zasłaniające arkusz?
Kalendarz Pionowy (ep.CYZPL)
Switch The Language

    Etiam magna arcu, ullamcorper ut pulvinar et, ornare sit amet ligula. Aliquam vitae bibendum lorem. Cras id dui lectus. Pellentesque nec felis tristique urna lacinia sollicitudin ac ac ex. Maecenas mattis faucibus condimentum. Curabitur imperdiet felis at est posuere bibendum. Sed quis nulla tellus.

    ADDRESS

    63739 street lorem ipsum City, Country

    PHONE

    +12 (0) 345 678 9

    EMAIL

    info@company.com