Problem z porównywaniem liczb wyciąganych z kodu (np. PESELa) za pomocą funkcji tekstowych

Jak zapewne wiesz pierwszych 6 cyfr w numerze PESEL to data urodzenia. Dwie pierwsze cyfry numeru PESEL to rok urodzenia. Dwie kolejne to miesiąc urodzenia, a dwie kolejne to dzień urodzenia. Czyli widząc taki PESEL: 90 11 22 33 445 wiemy, że mamy do czynienia z osobą urodzoną 22.11.1990.

No dobra, ale załóżmy, że mamy inny PESEL, który zaczyna się np. od liczby 10. Skąd mamy pewność czy osoba urodziła się w roku 1910 czy może w 2010? Aby nie dochodziło do takich niejednoznacznych sytuacji wymyślono, że jeśli dana osoba urodziła się w XXI wieku (czyli po roku 1999) to numer miesiąca jest zwiększany o 20. Więc PESEL 10 31 22 33 445 należy do osoby urodzonej 22.11.2010 (ponieważ miesiąc to 31 więc jest to osoba urodzona po roku 1999, odejmując liczbę 20 od liczby 31 wychodzi nam 11 miesiąc czyli listopad).

Będąc uzbrojeni w tę wiedzę zaprojektujmy prosty arkusz, który nam powie w którym wieku urodziła się dana osoba.

Pobierz skoroszyt do ćwiczeń

W kolumnie A wpisałem dwa przykładowe numery PESEL. W komórce B2 wpisałem prostą funkcję wyciągającą miesiąc z numeru PESEL:

=FRAGMENT.TEKSTU(A2;3;2)

W komórce C2 użyłem funkcji JEŻELI:

=JEŻELI(B2>20;"XX w.";"XXI w.")

Niestety, ale przy takim porównaniu zawsze jest zwracany XX wiek. Jeśli bym „zadał pytanie” odwrotnie tj.

=JEŻELI(B2<20;"XXI w.";"XX w.")

To też będzie zwrócony XX wiek.

Skąd to się wzięło? Otóż funkcja FRAGMENT.TEKSTU, która z numeru PESEL wyciągnęła miesiąc urodzenia jest funkcją tekstową. Zresztą widzimy, że liczby w kolumnie B są wyrównane do lewego marginesu komórki. I funkcja JEŻELI tak jakby myśli, że ma do czynienia z tekstem a nie z liczbą, dlatego nie działa w prawidłowy sposób.

Zróbmy jeszcze inny eksperyment: w komórce E2 wpisałem prostą formułę:

=B2+1

Efekt? Excel wartość z komórki B2 traktuje jako liczbę (jeśli ta wartość byłaby traktowana jako tekst to wtedy w komórce E2 pojawiłby się błąd #ARG!).

Funkcja WARTOŚĆ

Jeśli znasz język programowania VBA (lub jakiś inny) to pewnie świta Tobie w głowie, że w VBA jest funkcja o nazwie VAL, która zamienia tekst na liczbę. W dość podobny sposób działa Excelowa funkcja WARTOŚĆ (z tym, że jeśli nie mamy do czynienia z liczbą to funkcja WARTOŚĆ zwraca błąd a funkcja VAL w VBA zwracała 0). W wielu przypadkach funkcja WARTOŚĆ nie jest potrzebna, gdyż większość funkcji oczekujących wartości liczbowej fragment tekstu zwrócony przez funkcje tekstowe (LEWY, PRAWY, FRAGMENT.TEKSTU) traktuje jako liczbę, ale jak widzimy funkcja JEŻELI płata nam psikusa.

Oto jak działa funkcja wartość:

Widzimy, że jeśli w danej komórce jest choć jeden znak nie będący cyfrą to funkcja zwraca błąd (nawet jeśli dany ciąg znaków zaczyna się od cyfr jak to mamy w przypadku tekstu 20 kg). Jedyny wyjątek to komórka z wartością 20 zł, ale tak naprawdę w tej komórce jest liczba 20 a to zł to ustawione formatowanie komórki jako księgowa. Ponieważ Data i czas to tak naprawdę dla Excela są to liczby to również tutaj funkcja wartość nie zwróciła błędu.

Jeśli nie chcesz, aby funkcja WARTOŚĆ zwracała błąd, gdy dany ciąg znaków nie jest liczbą możesz „opakować” funkcję WARTOŚĆ w funkcję JEŻELI.BŁĄD.

Jak naprawić funkcję JEŻELI?

Istnieje kilka sposobów na rozwiązanie tego problemu:

• Użycie funkcji WARTOŚĆ
• Pomnożenie miesiąca przez 1 (cokolwiek razy 1 daje nam cokolwiek)
• Dodanie do miesiąca liczby 0 (cokolwiek plus 0 daje nam cokolwiek)
• Użycie podwójnej negacji (czyli tak jakbyśmy dwukrotnie pomnożyli daną liczbę przez -1)

W komórce B8 wpisałem funkcję:

=JEŻELI(B2>20;"wieksze";"mniejsze")

I jak widać, zwraca ona błędną wartość. Natomiast wszystkie pozostałe funkcje w kolumnach C, D, E i F zwracają poprawne wyniki. W komórce C8 użyłem w porównaniu funkcji WARTOŚĆ:

=JEŻELI(WARTOŚĆ(B2)>20;"wieksze";"mniejsze")

W komórce D8 postanowiłem pomnożyć zawartość komórki B2 przez 1

=JEŻELI(B2*1>20;"wieksze";"mniejsze")

W komórce E8 postanowiłem dodać do zawartości komórki B2 liczbę 0

=JEŻELI(B2+0>20;"wieksze";"mniejsze")

A w komórce E8 użyłem podwójnej negacji (zwróć uwagę na te dwa minusy przed B2)

=JEŻELI(--B2>20;"wieksze";"mniejsze")

Podsumowując: jeśli w funkcji JEŻELI porównujemy jakąś liczbę, którą uzyskujemy poprzez wyciągnięcie jej z jakiegoś innego ciągu znaków za pomocą funkcji Tekstowych to należy Excela "poinformować", aby ten ciąg znaków przy porównaniu taktował jako liczbę.

Ten wpis jest niejako rozwinięciem wcześniej popełnionej notki: Jak z numeru PESEL wydobyć datę urodzenia?