fbpx

Wyodrębnianie daty urodzenia z nowego PESEL-u

07.09.2020 | Daty i czas, ECP2, Microsoft365, Nazwy, Operacje na tekście

Czyli jak działa funkcja LET

Nowa funkcja w Excelu, dostępna w subskrypcji (Microsoft365), świetnie się nadaje, gdy w jednej formule potrzebujemy wielokrotnie użyć tego samego fragmentu formuły (albo kilku tych samych). Funkcja ta nazywa ów fragment i pozwala od razu użyć powstałej nazwy. Dzięki temu nie wypisujemy wciąż tego samego.

„LET” omówię na przykładzie wyodrębniania daty urodzenia z PESEL-u – zarówno „nowego” (2000-2099), jak i „starego” (1900-1999).

Tak wygląda formatka z wynikiem:

MalinowyExcel Wyodrębniania daty urodzenia z PESELu funkcja LET - Wynik

Jak odczytać datę urodzenia z PESEL-u?

O dacie urodzenia mówi 6 pierwszych cyfr PESEL-u. Dwie pierwsze to końcówka roku, dwie kolejne – miesiąc, dwie kolejne – dzień. Aby jednak odróżnić osoby urodzone w latach 2000-2099 od osób z lat 1900-1999, wprowadzono pewną modyfikację w zapisie miesiąca. W PESEL-ach osób urodzonych w latach 2000-2099, dodaje się liczbę 20 do miesiąca. Czyli np. styczeń to 01+20=21, a listopad to 11 + 20 = 31 (TUTAJ możesz doczytać więcej o PESEL-u).

Technicznie najważniejsza jest dla nas trzecia cyfra PESEL-u, czyli pierwsza cyfra miesiąca. Jeśli jest ona większa lub równa 2 – mamy do czynienia z osobą urodzoną w latach 2000-2099. Ta liczba będzie nam więc potrzebna do ustalenia roku i miesiąca urodzenia. Wyodrębnianie jej „zamkniemy” w nazwie, właśnie dzięki funkcji LET.

Jak użyć funkcji LET?

Aby zademonstrować ci jak działa LET, sprawdzimy, czy trzecia liczba PESEL-u jest większa lub równa 2. Na początek tyle. Potem to zmodyfikujemy.

Ważne, abyś wiedział, że cała formuła musi być zawarta w funkcji LET, jeśli planujemy skorzystać z nazywania poszczególnych jej elementów. Potrzebujemy określić:

  1. nazwę, którą planujemy używać,
  2. formułę, którą ta nazwa będzie reprezentować,
  3. formułę, w której użyjemy nazwy.

Jeśli zatem chcemy, aby trzecia cyfra PESEL-u nazywała się Trzecia i była wyodrębniana za pomocą funkcji FRAGMENT.TEKSTU, a następnie porównana do 2, funkcja przybierze następującą postać:

=LET(Trzecia;--FRAGMENT.TEKSTU(B5;3;1);Trzecia>=2)

Dwa minusy (–) przed FRAGMENTEM.TEKSTU służą do konwersji tekstu na liczbę. Więcej o tym dowiesz się tutaj.

Wynikiem powyższej formuły będzie PRAWDA. Jest to wynik porównania trzeciej liczby (Trzecia) do dwójki. Oczywiście ten sam efekt jesteśmy w stanie osiągnąć bez funkcji LET. Chciałam ci jednak pokazać jej działanie na prostym przykładzie.

Ustalanie daty urodzenia

Teraz zajmiemy się już konkretami, czyli powoli będziemy ustalać datę urodzenia. Do tworzenia dat świetnie nadaje się funkcja DATA, która potrzebuje od nas 3 informacji:

  1. rok,
  2. miesiąc,
  3. dzień.

Na ich podstawie stworzy datę. Dzień najprościej jest ustalić, bo są to po prostu 5. i 6. znak PESEL-u. Z rokiem i miesiącem jest trudniej. Do roku potrzebujemy dodać liczbę 1900 lub 2000 (w zależności od naszej Trzeciej). Od miesiąca zaś będziemy odejmować (lub nie) liczbę 20. Znów: w zależności od Trzeciej. Do dzieła!

Z pewnością potrzebujemy ustalić Trzecią cyfrę PESEL-u, aby dwukrotnie ją wykorzystać (do liczenia roku i miesiąca). Możemy od razu sprawdzić, czy Trzecia jest większa lub równa 2, czyli w nazwie od razu zawrzeć porównanie.

Tak to wygląda w funkcji LET:

=LET(Trzecia;--FRAGMENT.TEKSTU(B5;3;1)>=2;...)

W jej trzecim argumencie jest miejsce na właściwą formułę. W tej z kolei na pewno znajdzie się funkcja DATA, czyli na razie tak:

=LET(Trzecia;--FRAGMENT.TEKSTU(B5;3;1)>=2;DATA(rok;miesiąc;dzień))

Zacznijmy od najprostszego – od dnia.

Ustalanie dnia urodzenia

Dzień to po prostu 5. i 6. cyfra PESEL-u, czyli:

FRAGMENT.TEKSTU(B5;5;2)

Powyższe wrzucone do funkcji LET i DATA wygląda tak:

=LET(Trzecia;--FRAGMENT.TEKSTU(B5;3;1)>=2;DATA(rok;miesiąc;FRAGMENT.TEKSTU(B5;5;2)))

DATA umie działać na liczbach przechowywanych w formie tekstu, więc z dniem nic już nie musimy robić. Jedźmy więc do roku.

Ustalanie miesiąca urodzenia

Jak wspomniałam, miesiąc będą to 3. i 4. cyfra PESEL-u ewentualnie trzeba będzie odjąć 20. Zmniejszamy, jeśli Trzecia jest większa lub równa 2. Możemy to zapisać w ten sposób (drugi argument funkcji DATA):

FRAGMENT.TEKSTU(B5;3;2)-20*Trzecia

W powyższej formule wykorzystałam fakt, że Trzecia jest wartością logiczną, czyli jeśli będzie wartością FAŁSZ, więc w mnożeniu przyjmie wartość zero (0). Jeśli zaś będzie PRAWDĄ – przyjmie wartość 1. Więcej o takim wykorzystaniu logiki dowiesz się tutaj.

Po wrzuceniu tego do funkcji LET i DATA otrzymujemy to:

=LET(Trzecia;--FRAGMENT.TEKSTU(B5;3;1)>=2;DATA(rok;FRAGMENT.TEKSTU(B5;3;2)-20*Trzecia;FRAGMENT.TEKSTU(B5;5;2)))

Podobnie zadziałamy z rokiem.

Ustalanie roku urodzenia

Rok to 1. i 2. cyfra PESEL-u powiększone o 1900 lub 2000, w zależności od Trzeciej. Jeśli Trzecia >=2, to dodajemy 2000, w przeciwnym razie 1900:

LEWY(B5;2)+JEŻELI(Trzecia;2000;1900)

Całość wygląda tak:

=LET(Trzecia;--FRAGMENT.TEKSTU(B5;3;1)>=2;DATA(LEWY(B5;2)+JEŻELI(Trzecia;2000;1900);FRAGMENT.TEKSTU(B5;3;2)-20*Trzecia;FRAGMENT.TEKSTU(B5;5;2)))

Czyli wszystkie daty urodzenia, po sformatowaniu ich na daty oczywiście, wyglądają tak:

MalinowyExcel Wyodrębniania daty urodzenia z PESELu funkcja LET - Wynik

Wynik

Przydatne? 🙂

Na koniec mam do ciebie prośbę: pomóż mi dotrzeć z tym artykułem do szerszego grona odbiorców. Jeśli zechcesz udostępnić go na Facebooku, będę ci bardzo wdzięczna! Bardzo się starałam, aby wart był wzmianki. 😉 Oczywiście, śmiało podeślij go znajomym mailem lub Messengerem. Będzie mi bardzo miło. 🙂

Powiązane produkty

  • Zagnieżdżanie funkcji – podczas tej lekcji nauczysz się, jak zagnieżdżać funkcje w sposób łatwy i przyjemny. I przede wszystkim – zrozumiały!
  • Logika bez funkcji logicznych – podczas tej lekcji nauczysz się korzystać z logiki bez użycia funkcji logicznych (takich jak ORAZ czy LUB). Okazuje się, że często bardzo ułatwia i przyspiesza to naszą pracę. Dodatkowo dowiesz się, jak stosować logikę w narzędziach Excela takich jak sprawdzanie poprawności czy formatowanie warunkowe.
  • Funkcje dat – Excelowy niezbędnik – lekcja omawiająca najważniejsze funkcje dat, które pozwolą Ci w prosty sposób dokonywać obliczeń na datach, bez kombinowania jak koń pod górę.

Plik do pobrania:

MalinowyExcel Wyodrębnianie daty urodzenia z PESEL-u LET DW.xlsx

 

I film na YouTubie, gdzie pokazuję powyższe rozwiązanie:

 

Redakcja językowa: Aleksandra Wasiak
www.kulturajezyka.pl

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

3 komentarze

  1. Fajny filmy bardzo polecam.

    Reply
    • Cieszę się, że Ci się podobał i dziękuję za polecenie!

      Reply
  2. W excelu jestem dość biegły, ale nadal się uczę, na Let trafiłem dziś po raz pierwszy. Jeszcze przed chwilą chciałem napisać, że nie widzę zastosowania, ale nie byłbym sobą gdyby nie spróbował przetestować najpierw tego rozwiązania. Zacząłem od przykładu z Peselem, który napisałem za pomocą zwykłego jeżeli, żeby zobaczyć gdzie może być przewaga Let. Okazało się, że jeszcze niedawno zagnieżdżdzałem wszystkie funkcje w tak skomplikowany sposób, że ich poprawienie graniczyło z cudem. Let to fantastyczne rozwiązanie, które pozwala ten sam fragment użyć wiele razy, a zadeklarować go tylko 1 raz w funkcji. Ewentualna zmiana, będzie zajmowała sekundę, a nie godzinę głowienia się. Brakuje mi tylko jednego elementu, chyba że coś jeszcze pominąłem. Tak by komórki nie deklarować już w pierwszej formule, ale dopiero w drugiej.

    Można zrobić =Let(PowiekszonaO55;5+A1;PowiekszonaO5*2), co da taki sam wynik co 2*(A1+5), ale chciałbym używać tej formuły dynamicznie w zależności od potrzeb. Widziałbym to raczej jako coś w stylu:
    =Let(PowiekszonaO55;5+”Zmienna”;PowiekszonaO5(Zmienna)*2), tak by w głównej funkcji można było przekazać jeszcze parametr do tej dodatkowej funkcji.

    Reply

Leave a Reply to funkcja let Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest