Walidacja danych w arkuszach google, czyli jak ograniczyć treść komórek i wymusić format danych

Chodzi o sytuację, gdy dajemy do obsługi arkusz różnym użytkownikom i obawiamy się, czy nie naśmiecą w nim. Często zdarza się, że ktoś wkleja dane z błędnym formatem i dziwi się, że formuły się sypią. Najczęściej zdarza się to gdy używa się Kopiuj / Wklej i zapomina o kropkach czy przecinkach w cenach.

Wstęp

W Excelu ta funkcja nazywa się: walidacja danych. W arkuszach google znajdziemy ją w menu Dane —> Sprawdzanie poprawności danych.

Sprawdzanie poprawności danych – opcje z menu

Możemy sprawić, że pojawi się ostrzeżenie, a możemy również odrzucić wprowadzane dane. W tym celu wybieramy opcję z menu:

Jeśli obawiamy się, że użytkownik nie zrozumie problemu, możemy dodać specjalny komuniat pomocniczy. Wówczas wybieramy “Pokaz tekst pomocy związanej ze sprawdzaniem poprawności danych”.

Jak przyspieszyć wpisywanie danych, jeśli używamy tylko kilku słów (np. ocen słownych czy kolorów).

Możemy to zrobić na dwa sposoby. Albo wykorzystać listę zrobioną w arkuszu i zrobić z niej rozwijane menu,

Można też listę zdefiniować w samym oknie menu Srawdzanie poprawności danych:

Sprawdzanie czy użytkownik wprowadza poprawne liczby

Najprościej jest gdy mamy do czynienia z liczbami. Załóżmy, że użytkownik ma wpisać ocenę od 1 do 10. Wystarczy, że w menu sprawdzania poprawności wpiszemy w działce Kryteria: Liczba pomiędzy 1 i 10.

Jeśli w tym miejscu wprowadzamy np. dane sprzedaży, to warto zastosować sprawdzanie poprawności, żeby wychwycić błędy. Wystarczy bowiem, że ktoś posadzi kropkę zamiast przecinka i liczba będzie traktowana jako tekst.

Głupio jak ktoś wkleja np. 1 000 i w arkuszu wygląda niemal tak samo jak 1000, ale kiedy formuła sumuje kolumnę, to ten tysiak nie jest brany pod uwagę. I całe obliczenia idą się paść.

Możemy to też załatwić używając sprawdzania poprawności danych oraz funkcji ISNUMBER.

Jeśli normalnie wpisujemy tą funkcję w komórki obok przykładowych danych, to wygląda to tak:

Funkcja ta może być wpisana do komórki arkusza, a może być użyta jako Niestandardowa formuła

Sprawdzanie poprawności danych przy pomocy niestandardowych formuł

Jak ograniczyć wpisywanie danych do liczb całkowitych

Tutaj użyjemy niestandardowej formuły. Funkcja INT służy do wydzielenia z liczby jej części całkowitej. Jeśli zrobimy INT(6) to uzyskamy 6, a z INT(7,5665) uzyskamy 7. Zatem jeśli INT z wartości jest równy tej wartości, znaczy że mamy do czynienia z liczbą całkowitą.

Ponieważ zastosowaliśmy sprawdzanie poprawności danych dla zakresu A1:A100, sprawdzane są wszystkie komórki tego zakresu i za każdym razem jest zmieniany adres. A więc dla A2 ten test będzie miał automatycznie postać =INT(A2)=A2 i jeśli to będzie prawda, to dane zostaną przyjęte. Jeśli to fałsz, to pojawi się pomarańczowy trójkąt z informacją o nieprawidłowości lub dane zostaną odrzucone.

Jak ograniczyć pole danych do wpisywania stopni (1-6) z plusami, minusami lub bez nich.

To zadanie z którym mogą się spotkać nauczyciele wpisujący oceny do arkusza. Załóżmy, że chcemy pozwolić na wprowadzanie stopni w formie: 1, 1+, 2=, 2-, 2, 2+, 3=,3-,3, etc. Czyli mamy format gdzie pojawiają się liczby z zakresu 1-6, a po nich pojawia się jeden z trzech znaków +-=. W komórce nie może pojawiać się nic poza tym.

Zatem montujemy formułę, która będzie sprawdzać czy ten warunek jest spełniony. Znów posłużymy się Menu –> Sprawdzanie poprawności danych i jako Niestandardowa formuła to wpiszemy

=regexmatch(to_text(C3);"^[1-6]{1}[+-=]{0,1}$").

Rozbijmy to na czynniki pierwsze.

Funkcję regexmatch omówiłem szerzej we wpisie o wyrażeniach regularnych. Jej zadaniem jest sprawdzenie czy dany ciąg znaków spełnia warunki wyrażenia regularnego.

Ten ciąg to to_text(C3). Zawartość komórki C3 opakowaliśmy w funkcję to_text(), która przerabia wszystko na format tekstowy. Jeśli byśmy tego nie zrobili, funkcja regexmatch nie poradziłaby sobie w sytuacji, gdyby w C3 były liczby (1,2,3,4 etc.). Bo ona generalnie nie działa z liczbami.

Ostatnia część to samo wyrażenie regularne w cudzysłowie:

Jak ustalić format wprowadzania kodu pocztowego

Tu również posłużymy się funkcją regexmatch i wyrażeniem regularnym wpisanym w okienko sprawdzania poprawności danych. Zaznaczamy interesujący nas fragment arkusza i wpisujemy formułę:

Nasza formuła to:

=regexmatch(b14;"^[0-9]{2}-[0-9]{3}$")

Regexmatch ma składnię regexmatch(ciąg znaków do zbadania;wyrażenie regularne). Jeśli ciąg spełnia warunki wyrażenia to formuła przyjmuję wartość PRAWDA (TRUE).

Skupmy się zatem na samym wyrażeniu opisującym kod pocztowy:

Znaczniki ^ i $ są bardzo ważne – bez nich zostanie dopuszczony kod pocztowy na początku którego lub na końcu którego są jeszcze jakieś znaki (np. 33-333444).

Jak sprawdzić poprawność adresu email

Z tym problemem spotykają się zwykle programiści, którzy przygotowują formularze i każą wpisywać adres, a nie chcą żeby ktoś ich zbył jakimś losowym qwertitiyui…

Tu możemy się posłużyć narzędziem wbudowanym w Arkusze Google. To funkcja isemail() sprawdzająca czy ciąg znaków jest poprawnym adresem mailowym.