Ograniczanie danych wejściowych za pomocą sprawdzania poprawności danych

Sprawdzanie poprawności danych określa reguły dotyczące wartości, które można wprowadzić do komórki. Zamiast polegać na tym, że użytkownicy wpiszą właściwą wartość, arkusz kalkulacyjny wymusza jej poprawność.

Można wymagać, aby komórka zawierała wyłącznie liczby całkowite, wyłącznie daty z określonego zakresu albo wyłącznie wartości z zatwierdzonej listy.

• Wychwytuje literówki, zanim zaburzą dane.
• Pomaga użytkownikom za pomocą podpowiedzi i komunikatów ostrzegawczych.
• Działa zarówno w programie Excel, jak i w Google Sheets.

Funkcja znajduje się w nieco różnych miejscach w każdym z tych programów.

• Excel: zaznacz komórki, przejdź do karty Dane i kliknij Sprawdzanie poprawności danych.
• Google Sheets: zaznacz komórki, a następnie wybierz Dane > Sprawdzanie poprawności danych i kliknij Dodaj regułę.

W obu programach otworzy się panel, w którym można wybrać typ reguły, ustawić jej ograniczenia i określić, co ma się stać po wprowadzeniu nieprawidłowych danych.

Typowa reguła ogranicza zawartość komórki do liczb. W oknie dialogowym programu Excel wybierz Liczba całkowita lub Liczba dziesiętna, a następnie ustaw warunki, na przykład między 1 a 100.

Jeśli ktoś wpisze teraz abc lub 150, wpis zostanie odrzucony.

• Używaj opcji Liczba całkowita do liczników i ilości.
• Używaj opcji Liczba dziesiętna do cen lub wartości procentowych.
• Dostępne operatory to między, większe niż i równe.

Można również ograniczyć zawartość komórki do prawidłowych dat w określonym przedziale. Wybierz regułę Data i ustaw na przykład daty między dzisiaj a końcem roku.

To idealne rozwiązanie w arkuszach rezerwacji lub przy terminach, w przypadku których daty z przeszłości nie mają sensu.

• Odrzucaj daty wcześniejsze niż =TODAY(), aby blokować wprowadzanie dat wstecznych.
• Połącz datę początkową i końcową, aby wymusić zakres trwania projektu.

Arkusz kalkulacyjny automatycznie sprawdza każdy wpis pod kątem reguły.

Sprawdzanie poprawności może dotyczyć również tekstu. Excel oferuje regułę Długość tekstu, przydatną w przypadku pól takich jak dwuliterowy kod kraju lub identyfikator o stałej długości.

• Wymagaj długości równej 2 w przypadku kodów stanów.
• Wymagaj długości mniejszej lub równej 50 w przypadku krótkich notatek.

Wszystko, co wykracza poza limit, zostaje odrzucone, dzięki czemu kolumny pozostają jednolite, a późniejsze formuły działają w przewidywalny sposób.

W przypadku warunków, których nie da się wyrazić za pomocą wbudowanych reguł, użyj reguły Niestandardowa (Excel) lub Formuła niestandardowa (Sheets). Wpisz formułę, która musi zwrócić wartość TRUE, aby wpis został zaakceptowany.

Na przykład, aby wymusić wielkie litery w komórce A2:

=EXACT(A2,UPPER(A2))

Jeśli wpisana wartość nie składa się wyłącznie z wielkich liter, formuła zwraca FALSE, a wpis zostaje zablokowany.

Jedna z najczęściej używanych reguł niestandardowych zapobiega duplikowaniu wpisów w kolumnie. Zaznacz zakres A2:A100 i użyj niestandardowej formuły:

=COUNTIF($A$2:$A$100,A2)=1

Oznacza to, że właśnie wpisana wartość może wystąpić w zakresie tylko raz. Jeśli już w nim istnieje, liczba wystąpień wynosi 2, więc reguła nie zostaje spełniona.

• Świetnie sprawdza się w przypadku unikatowych identyfikatorów, adresów e-mail lub numerów faktur.
• Zwróć uwagę na zakres bezwzględny i względne odwołanie A2.

Excel pozwala wyświetlać Komunikat wprowadzania — małą podpowiedź pojawiającą się po zaznaczeniu komórki, zanim ktokolwiek zacznie pisać.

Można za jego pomocą wyjaśnić regułę prostym językiem, na przykład Wpisz liczbę całkowitą od 1 do 100.

• Zawczasu pomaga użytkownikom.
• Zmniejsza liczbę odrzuconych wpisów i związaną z nimi frustrację.

Google Sheets wyświetla podobne wskazówki w tekście pomocy dotyczącym sprawdzania poprawności danych w regule.

Gdy dane naruszają regułę, karta Alert o błędzie w programie Excel określa poziom restrykcyjności:

• Zatrzymaj — całkowicie odrzuca wpis.
• Ostrzeżenie — oznacza wpis, ale pozwala użytkownikowi kontynuować.
• Informacje — tylko powiadamia użytkownika.

Google Sheets oferuje podobny wybór: Odrzuć dane wejściowe lub Pokaż ostrzeżenie. Wybierz opcję Zatrzymaj, gdy nieprawidłowe dane są niedopuszczalne, a Ostrzeżenie, gdy można pozostawić użytkownikowi decyzję.

Reguły można w dowolnym momencie przejrzeć lub usunąć.

• W programie Excel zaznacz komórki i ponownie otwórz funkcję Sprawdzanie poprawności danych; kliknij Wyczyść wszystko, aby usunąć regułę.
• Narzędzie programu Excel Zakreśl nieprawidłowe dane wyróżnia istniejące wartości, które naruszają nowo dodaną regułę.
• W Sheets otwórz funkcję Sprawdzanie poprawności danych i usuń regułę z panelu.

Pamiętaj, że sprawdzanie poprawności danych dotyczy tylko nowych wpisów, chyba że skontrolujesz również stare.

Warto wspomnieć o jeszcze jednym typie reguły, ponieważ prowadzi on do następnej lekcji: regule Lista.

Zamiast akceptować dowolną liczbę lub datę reguła Lista ogranicza zawartość komórki do określonego zestawu opcji, takich jak Open, Closed lub Pending.

• To podstawa menu rozwijanego.
• Opcje można wpisać ręcznie albo wskazać zakres komórek.

W następnej lekcji przekształcisz tę funkcję w dopracowaną listę rozwijaną.

Chcesz, aby kolumna odrzucała wartość, która już istnieje gdzie indziej w tej kolumnie. Która niestandardowa formuła sprawdzania poprawności danych zapewnia to dla komórki A2 w zakresie A2:A100?

Nauczyli się Państwo kontrolować dane wejściowe za pomocą funkcji sprawdzania poprawności danych:

• Otwiera się ją za pomocą karty Dane (Excel) lub polecenia Dane > Sprawdzanie poprawności danych (Sheets).
• Można ograniczyć dane do liczb całkowitych, liczb dziesiętnych, dat lub określonej długości tekstu.
• Można tworzyć niestandardowe formuły, takie jak =COUNTIF($A$2:$A$100,A2)=1, aby definiować zaawansowane reguły.
• Można dodawać komunikaty wprowadzania i wybierać opcję Zatrzymaj lub Ostrzeżenie dla błędów.

W następnej lekcji utworzą Państwo przyjazne dla użytkownika listy rozwijane oparte na nazwanym zakresie.