Poprawność Danych

Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Sprawdzanie Poprawności.xlsx tylko ich samodzielne przerobienie daje gwarancję zapamiętania tej lekcji

Przykłady dla wszystkich lekcji szkolenia Excel 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

Przykład 1.

(Arkusz: ‘Sprawdzanie Poprawności 1’)

W poniższym przykładzie wiele osób będzie uzupełniać tą samą tabele, w tabeli używana jest funkcja WYSZUKAJ.PIONOWO, która działa wyłącznie, jeśli nazwy wprowadzone są idealnie tak samo jak w tabeli, z której dane są wyszukiwane.

W grupie wielu osób zawsze znajdą się osoby kreatywne i nie możemy liczyć na to, że nawet po dokładnym wytłumaczeniu, nazwy projektów zostaną wprowadzone precyzyjnie tak samo.

Poniżej 2 nazwy wprowadzono dokładnie tak jak w kolumnie H a 3 kolejne z błędami:

projekt2 – brak spacji

projekt 3 – dodatkowa spacja za 3

porjekt 8 – przestawione literki

projekt 10 – podwójna spacja zamiast pojedynczej

Postanowiono użyć ‘Poprawność danych’, aby zapewnić, że funkcja wyszukaj będzie działać poprawnie.

Wprowadzenie sprawdzania poprawności danych rozpoczniemy od wybrania obszaru, którego ma ono dotyczyć czyli komórek B6-B11.

Po czym wybierzemy z Menu: ‘DANE’ → ‘Poprawność danych’.

W oknie ‘Sprawdzanie poprawności danych’ na karcie ‘Ustawienia’ wybieramy opcję Lista, która umożliwia wskazanie listy danych, które mogą być wprowadzone do wcześniej zaznaczonych komórek.

Następnie wskazujemy listę gdzie znajdują się dozwolone do wprowadzenia wartości.

Po kliknięciu OK, jeśli wejdziemy do komórki, dla której zostało ustalone sprawdzanie poprawności wyświetli się lista danych które mogą być wprowadzone. Osoba uzupełniająca tabele wybiera z tej listy właściwą nazwę projektu.

Można też wpisać nazwę projektu w komórce z klawiatury i o ile wpis będzie się zgadzać z listą, wpisany tekst zostanie zaakceptowany.

W przypadku gdyby wpisano nazwę, która nie występuje na naszej liście, wyświetli się komunikat informujący że: ‘Wprowadzona wartość jest nieprawidłowa’.

Komunikat o błędzie możemy zmienić, wprowadzając komunikat, który chcielibyśmy aby się pokazywał w oknie: ‘Sprawdzanie poprawności danych’ na karcie ‘Alert o błędzie’.

Na poniższym rysunku widać jak będzie wyświetlany nasz komunikat o błędzie.

Można także wprowadzić komunikat wejściowy, który będzie wyświetlany po wejściu do komórki, dla której ustawiono sprawdzanie poprawności.

Komunikat wejściowy wprowadzamy także w oknie ‘Sprawdzanie poprawności danych’ na karcie ‘Komunikat wejściowy.

Osobiście odradzam to rozwiązanie, ponieważ przy dłuższym korzystaniu z pliku jest bardzo irytujące dla użytkowników.

Jeżeli chcemy o czymś poinformować użytkowników to najlepiej, aby taki komunikat wyświetlał się tylko raz przy otwieraniu pliku Excela. Więcej o tym jak przygotować taki komunikat powitalny w Części poświęconej VBA.

Przykład 2.

(Arkusz: ‘Sprawdzanie Poprawności 2’)

Przećwiczmy sprawdzanie poprawności na kolejnym autentycznym przykładzie.

Pewna osoba rozsyłała do ponad 100 dostawców pliki z prośbą o uzupełnienie terminów dostaw. Pomimo wielokrotnych próśb nie udało się jej wyegzekwować jednolitego formatu dat od wszystkich dostawców.

Wprowadzenie sprawdzania poprawności tak jak to zostało pokazane na poniższym rysunku załatwiło sprawę raz na zawsze.

Przykład 3.

(Arkusz: ‘Sprawdzanie Poprawności 3’)

Poprawność danych może także odnosić się nie bezpośrednio do komórki w którą dane są wprowadzane ale także do komórki na którą dane te mają wpływ.

W tym przykładzie planując produkcję nowych krzeseł chcielibyśmy mieć pewność że suma kosztów materiałów będzie poniżej 100zł.

Koszty wprowadzamy w żółte pola. Zaznaczamy je i przechodzimy do Poprawności Danych.

Chcielibyśmy aby suma będąca w F8 była mniejsza niż 100.

Na karcie ‘Alert o błędzie’ dodamy jeszcze komunikat dla użytkowników.

Gdy wprowadzimy wartość 66zł dla drewna i suma przekroczy 100zł komunikat zostanie wyświetlony i dana nie będzie mogła być wprowadzona.

Jeśli wcześniej obniżymy inne koszty, wartość 66 będzie mogła być bez problemu wpisana w komórkę F5.

Niestety tak wprowadzone ‘Sprawdzanie Poprawności’ można oszukać.

Wprowadzamy za dużą kwotę i zamiast Enter, klikamy inną komórkę.

Excel przyjmuje wartość niezgodną ze regułą ‘Sprawdzania Poprawności’.

Jeżeli zamiast odwoływać się do sumy w F8 wprowadzimy sumę tych 3 komórek w oknie Formuła.

Sprawdzanie poprawności nie da się już tak łatwo oszukać.

Nowa wartość dla której suma nie spełnia zadanego warunku nie będzie wprowadzona przy kliknięciu innej komórki.

Także bardziej zaawansowane obliczenia mogą być wprowadzone do ‘Sprawdzania poprawności danych’.

Poniżej oprócz kosztów uwzględniono także koszt robocizny obliczany jako narzut na koszty, dodano także marżę i koszt dystrybucji obliczane w zależności od sumy kosztów.

Podobnie zamiast odwoływać się do komórki K13 dla lepszego zabezpieczenia wprowadzono obliczenia w okienko Formuła.

Wprowadzenie danych skutkujących zbyt dużą ceną blokuje komórkę i informuje że wartość jest niepoprawna.

Poniżej standardowy komunikat, który jest wyświetlany gdy nie wprowadzimy własnego komunikatu.

Wprowadzanie formuły to znacznie lepsze zabezpieczenie, ale niestety i na tą metodę jest sposób.

Wystarczy wybrać 2 komórki np. G5 i F5 (aktywna jest G5), wprowadzić do G5 np. 999 i zatwierdzić Ctrl+Enter, a Excel zaakceptuje niepoprawną wartość w F5.

Wiedzę tą ma mało kto i chyba nie należy spodziewać się po użytkownikach aż takiej przebiegłości aby zrobić coś inaczej niż są o to proszeni.