Czasami chcemy w Excelu zablokować możliwość wpisywania do pewnych komórek liczb spoza akceptowalnego przez nas zakresu. Przykładowo mamy prosty arkusz do wpisywania ocen. Jak wiemy, w szkole możemy otrzymać ocenę z zakresu od 1 do 6 (dla uproszczenia załóżmy, że są to liczby całkowite).
Na powyższym rysunku widzimy, że jeden z uczniów otrzymał ocenę spoza zakresu czyli 9. W jaki sposób można by było temu zapobiec?
Otóż w pierwszej kolejności należy zaznaczyć zakres komórek, do którego się wpisuje oceny (w moim przypadku D2:M16). Następnie na karcie Dane należy wybrać polecenie Poprawność danych znajdujące się w grupie poleceń Narzędzia danych.
Naszym oczom pojawi się okno dialogowe Sprawdzanie poprawności danych.
Domyślnie w polu Dozwolone będzie wybrana opcja Dowolne (co oznacza, że do danej komórki można wpisać cokolwiek tj. dowolną liczbę, datę, godzinę, tekst, formułę itp.) Możemy tę opcję zmienić na:
- Pełna liczba – od teraz będzie można wprowadzać tylko liczby całkowite z określonego przez nas zakresu
- Dziesiętne – j.w. z tym, że liczba może być również ułamkowa (np. na studiach można mieć oceny typu 3,5).
- Lista – można wprowadzać tylko wartości z rozwijanej listy (temu zagadnieniu poświęciłem osobny wpis)
- Data – tutaj będziemy mogli wprowadzać tylko daty
- Godzina – j.w. ale tyczy się czasu
- Długość tekstu – można wprowadzać tylko tekst o określonej długości (świetna opcja np. w przypadku numeru PESEL – możemy nałożyć ograniczenie, że długość tekstu musi mieć dokładnie 11 znaków długości)
- Niestandardowe – temat na odrębny wpis – możemy zdefiniować własną niestandardową formułę, która waliduje poprawność danych (np. tylko liczba parzysta, data nie mniejsza niż dziś minus określona liczba dni itp.)
W polu wartości danych możemy określić dodatkowe kryteria takie jak: między, nie między, równa, nie równa, większa niż, mniejsza niż, większa lub równa niż, mniejsza lub równa niż. Ja najczęściej wybieram kryterium między.
I to wszystko. Po wprowadzeniu ograniczeń należy kliknąć w przycisk OK. Od teraz, gdy jakiemuś uczniowi zechcesz wpisać ocenę spoza akceptowalnego zakresu (np. 7) to pojawi się komunikat z błędem: Ta wartość jest niezgodna z ograniczeniami sprawdzania poprawności danych, które są zdefiniowane dla tej komórki.
Komunikat ten niestety ale jest nieprecyzyjny, gdyż nam za wiele nie mówi (w przypadku ocen można się domyśleć, że będą to wartości od 1 do 6 lub w przypadku studentów od 2 do 5 ale przykładowo w arkuszu wyceny mogę nie wiedzieć jaki jest zakres poprawnych wartości dla np. współczynnika przenikania ciepła). Dlatego też warto wprowadzić własny komunikat błędu. W tym celu ponownie na karcie Dane należy wybrać polecenie Poprawność danych znajdujące się w grupie poleceń Narzędzia danych. Gdy pojawi się okno dialogowe Sprawdzanie poprawności danych należy przejść na kartę Alert o błędzie.
W tym oknie dialogowym w polu Tytuł wpisujemy krótki opis błędu (optymalnie 2-4 wyrazy). W polu Komunikat o błędzie możemy się nieco bardziej rozpisać.
Z kolei w polu Styl możemy wybrać rodzaj komunikatu o błędzie
- Stop – po wprowadzeniu wartości spoza zakresu musimy poprawić błędną wartość albo anulować edycję. Nie ma możliwości wprowadzenia wartości spoza zakresu.
- Ostrzeżenie – po wprowadzeniu wartości spoza zakresu co prawda zobaczymy ostrzeżenie, ale mamy możliwość wprowadzenia wartości spoza zakresu (tak jakby nie wolno, ale za zgodą kierownika da się zrobić)
- Informacje – wyskoczy komunikat z informacją, że owa wartość jest spoza zakresu, ale domyślnie zostanie ona wprowadzona.
Najczęściej jako Styl zostawiam domyślnie wybraną opcję Stop gdyż zazwyczaj nie chcę, aby ktokolwiek jednak miał możliwość wprowadzania wartości spoza zakresu.
Wskazówka: jeśli ktoś zna dobrze program Excel, to może zaznaczyć „kłopotliwe” komórki i wyłączyć owe sprawdzanie poprawności danych (ustawiając z powrotem w polu Dozwolone opcję Dowolne). Dlatego oprócz tego warto jest włączyć opcję ochrony arkusza.
A co z błędnymi komórkami?
Sprawdzanie poprawności danych działa dopiero od momentu nałożenia ochrony. Oznacza to, że jeśli jakiś uczeń wcześniej miał wpisaną liczbę spoza zakresu to nałożenie reguł sprawdzania poprawności danych nie spowoduje, że ta błędna ocena nagle mu zniknie (co ma sens, bo nikt by nie chciał, aby ukradkiem mu znikły jakieś dane).
Tak więc jeśli Sprawdzanie poprawności danych zostało włączone „w trakcie roku szkolnego” to warto jest sprawdzić, czy jakiś uczeń nie ma nieprawidłowej oceny. W tym celu na karcie Dane w grupie poleceń Narzędzia danych należy kliknąć w strzałeczkę przy poleceniu Poprawność danych i wybrać polecenie Zakreśl nieprawidłowe dane.
Excel zakreśli czerwonym kółeczkiem komórki z błędnymi wartościami. Możemy wtedy albo ręcznie poprawić owe komórki (zmienić oceny) albo wybrać polecenie Wyczyść zakreślenia nieprawidłowych danych, aby owe zakreślenia nas nie denerwowały.
