Im bardziej poznaję Excela, tym bardziej zdaję sobie sprawę z tego, jak wiele jeszcze nie umiem. Kiedyś wypomniałem, że w można użyć własnej formuły w formatowaniu warunkowym. Aby było ciekawiej, okno dialogowe Sprawdzanie poprawności danych również pozwala na wpisanie własnej formuły, która waliduje (sprawdza) poprawność danych.
Pobierz przykładowy plik do ćwiczeń
Daty
Rozpatrzmy taki przykład. Interesują nas tylko daty z zakresu od 2020-01-01 do 2030-01-01. W tym celu zaznaczmy wskazane komórki a następnie na karcie Dane w grupie poleceń Narzędzia danych wybierzmy polecenie Poprawność danych. Pojawi się okno dialogowe Sprawdzanie poprawności danych. W polu dozwolone wybierzmy pozycję Data. Jako wartości danych wskażmy między. W polu Data początkowa wpisz 2020-01-01 a w polu Data końcowa wpisz 2030-01-01.
To akurat było dość proste. No to teraz skomplikujmy sytuację. Pozwólmy na wpisywanie dat z zakresu 7 dni przed dniem dzisiejszym do 7 dni po dniu dzisiejszym. Ot takie 15 dniowe okienko. Dla przypomnienia funkcja DZIŚ() zwraca aktualną datę (wykorzystując tę funkcję nasza reguła będzie dynamiczna i nie będziemy musieli codziennie edytować reguły sprawdzania poprawności danych). Oto jak może wyglądać nasza formuła:
=DZIŚ()-7=DZIŚ()+7No dobra to teraz nieco bardziej skomplikujmy sytuację: pozwólmy na wprowadzanie daty, która zawiera dowolny dzień, dowolny rok, ale miesiąc musi być nie większy niż marzec.
Tutaj w polu dozwolone wybierzemy Niestandardowe a w polu formuła wpiszemy wyrażenie
=MIESIĄC(E4)<=3Jak to działa? Otóż jeśli wyrażenie MIESIĄC(E4)<=3 zwróci wartość PRAWDA to będziemy mogli wprowadzić ową datę. Gdyby to wyrażenie zwróciło wartość FAŁSZ to Excel nie pozwoliłby na wpisanie takiej daty.
Ważna rzecz: zaznaczamy zakres komórek koniecznie „od góry do dołu” a jako komórka do której się odwołujemy wpisujemy formułę zawierającą adres pierwszej komórki z zakresu. W naszym przypadku E4 – Excel dynamicznie zmienić dla pozostałych komórek w regule odpowiednio zmieni numer wiersza. Zresztą łatwo się o tym przekonać przykładowo gdy zaznaczę samą komórkę E6 i wybiorę z karty Dane polecenie Poprawność danych, to zobaczę taką oto regułę:
=MIESIĄC(E6)<=3
Inne ciekawe formuły
Załóżmy, że chcemy pozwolić jedynie na wprowadzanie liczb parzystych. W tym celu możemy wpisać taką oto formułę:
=MOD(A4;2)=0Dla nieparzystych byłoby podobnie z tym, że wtedy reszta z dzielenia wynosi 1:
=MOD(C4;2)=1Jeśli chcemy pozwolić na wprowadzanie damskich imion możemy wpisać takie wyrażenie (tutaj wykorzystujemy właściwość, że polskojęzyczne damskie imiona kończą się na literę a):
=PRAWY(E4)="a"Bardziej poprawna formuła może uwzględniać, że owym imieniem nie może być Kuba (jest to jeden z nielicznych wyjątków – męskie imię zakończone na literę a)
=ORAZ(E4<>"Kuba"; PRAWY(E4)="a")A co jeśli chcemy wprowadzać kody EAN-8 lub EAN-13? Standardowo wybranie pozycji Długość tekstu w polu Dozwolone pozwala wybierać tylko większe, mniejsze, między, równe itp. Nie można wybrać opcji równe X lub równe Y. Tutaj możemy wpisać taką oto formułę:
=LUB(DŁ(G4)=8; DŁ(G4)=13)No to teraz coś nieco bardziej skomplikowanego. Możemy wprowadzać tylko 8 znakowe kody produktów, które zaczynają się ciągiem znaków PL-E oraz są zakończone 4 cyframi. Tutaj użyjemy funkcji ORAZ a wewnątrz niej 3 warunki:
- Sprawdzimy czy wprowadzona wartość składa się z 8 znaków (4 pierwsze znaki to stały ciąg PL-E a 4 ostatnie to cyfry co oznacza, że kod produktu zawsze ma dokładnie 8 znaków)
- Sprawdzimy, czy 4 pierwsze znaki to PL-E (dla uproszczenia nie bierzemy pod uwagę wielkości liter)
- Sprawdzimy, czy 4 ostatnie znaki to cyfry
W efekcie otrzymamy takiego potworka:
=ORAZ(DŁ(I4)=8; LEWY(I4;4)="PL-E"; CZY.LICZBA(--PRAWY(I4;4)))Innym ciekawym przykładem może być sprawdzenie, czy w kodzie produktu znajdują się dokładnie dwa znaki / (ukośniki). Dla uproszczenia nasza reguła nie będzie sprawdzała takich błędów jak ukośnik jako pierwszy lub ostatni znak, bądź dwa ukośniki jeden po drugim. Jak sprawdzić ile razy dany znak występuje z innym ciągu znaków opisałem w notce Jak policzyć ile razy występuje dany znak w komórce?
=(DŁ(K4)-2)=(DŁ(PODSTAW(K4;"/";"")))Uwaga: jeśli nie wiesz co robi dana funkcja to zerknij na moim blogu na spis opisanych funkcji i uzupełnij braki w wiedzy 😉
Poprawność danych i własne funkcje napisane w VBA
Na koniec coś ciekawszego: walidacja numeru PESEL. Tutaj oprócz sprawdzenia czy PESEL ma 11 znaków możemy sprawdzić, czy DATA zakodowana w numerze PESEL istnieje (więc nie pozwolimy wpisać numeru PESEL mówiącego, że ktoś się urodził dnia 30 lutego) a także numer ten musi zawierać poprawną sumę kontrolną. I tutaj mamy 2 niespodzianki:
- W polu formuły w okienku sprawdzania poprawności danych możemy wpisać tylko 255 znaków (więc nie napiszemy jakiejś bardzo złożonej formuły)
- Jako formuły nie można (bezpośrednio) korzystać z własnych funkcji VBA (UDF).
Tak więc dla komórek z zakresu M4:M16 nie mogę skorzystać z mojej funkcji:
=CzyPoprawnyPESEL(M4)Ale mogę zrobić proste obejście otóż np. w pomocniczej kolumnie O w komórce O4 mogę wpisać formułę (kolumnę O mogę potem ukryć):
=CzyPoprawnyPESEL(M4)A następnie dla komórek z zakresu M4:M16 mogę wpisać prostą regułę poprawności danych:
=O4Tutaj mamy takie ciekawe „zakleszczenie” otóż jak wpiszę w komórce M4 jakiś numer PESEL to w komórce O4 pojawi się wartość PRAWDA lub FAŁSZ. Natomiast reguła sprawdzania poprawności danych pozwoli dla komórki M4 na wpisanie numeru PESEL jeśli w komórce O4 znajduje się wartość PRAWDA. Spróbuj wykonać to ćwiczenie wpisując najpierw swój numer PESEL a potem zmień w tym numerze jakąś dowolną cyfrę.
Wskazówka: jeśli funkcja =CzyPoprawnyPESEL(M4) zwraca wartość FAŁSZ a masz wątpliwości odnośnie poprawności numeru PESEL to możesz „na boku” dla danego numeru PESEL użyć bliźniaczej funkcji =strCzyPoprawnyPESEL(M4), która zwróci tekstowy komunikat mówiący dlaczego dany numer PESEL jest niepoprawny.
Uwaga: Ponieważ numer PESEL może zaczynać się od 0 a Excel z liczb zaczynających się od 0 ucina początkowe zera to dla komórek do których wpisuje się numer PESEL należy ustawić format tekstowy.
Wygodne konstruowanie formuł
Jeśli piszemy nieco bardziej skomplikowane (zagnieżdżone) formuły do walidacji poprawności danych to pewnie niezbyt wygodnym podejściem jest następujący proces:
- wpisywanie formuły w okienku sprawdzania poprawności danych,
- klikanie w OK,
- wpisywanie do komórek przykładowych danych aby sprawdzić czy Excel przepuści,
- wchodzenie w okienko sprawdzania poprawności danych, aby poprawić formułę
- I tak „w koło Macieju” skaczę tam i z powrotem.
Dlatego ja polecam taki trik: w dowolnej komórce pod zakresem danych (np. A20) wpisuję formułę, która odwołuje się do pierwszej komórki z owego zakresu (np. A4). Następnie w komórce A4 wprowadzam przykładową wartość i sprawdzam czy w komórce A20 pojawi się wartość PRAWDA. Następnie w komórce A4 wprowadzam inną wartość i jeśli stwierdzę, że w komórce A20 zgodnie z moimi oczekiwaniami pojawi się zawsze to co chcę (PRAWDA lub FAŁSZ) to wtedy kopiuję formułę z komórki A20 z paska formuły i wklejam ją w okienku sprawdzania poprawności danych.
