Uwaga: przeglądasz tę stronę na urządzeniu o niewielkim ekranie (szerokość < 640px). Niektóre zamieszczone w artykule ilustracje i animacje mogą stać się nieczytelne po dopasowaniu ich do rozdzielczości tego ekranu.

Yestok.pl

Jerzy Moruś

© Wszystkie prawa zastrzeżone. Wykorzystanie całości serwisu lub jego fragmentów bez pisemnej zgody autora zabronione.

Weryfikacja poprawności danych w arkuszu kalkulacyjnym Excel.



Opracowanie powstało w oparciu o Excel 2007.


Większość projektów powstaje z myślą o wielokrotnym wykorzystywaniu obliczeń z różnymi zestawami danych, często wykonywanymi przez różne osoby. Czasami takie przygotowane w jednej z komórek firmy opracowanie wykorzystywane jest do obliczeń i analiz w innych strukturach przedsiębiorstwa. Użytkownicy takiego projektu wprowadzają niezbędne dane, analizują wyniki, drukują je lub przesyłają dalej. Dane obliczeniowe wprowadzane są przez człowieka a ten – jak mówi porzekadło – jest istotą omylną. Dlatego arkusze kalkulacyjne udostępniają mechanizmy pozwalające na weryfikację wprowadzanych danych jeszcze przed umieszczeniem ich w komórkach. Dzięki temu już na etapie ich wpisywania można wyeliminować dane niepoprawne. Niektóre z takich danych można bezspornie zidentyfikować jako błędne, np. gdy do komórek wprowadzane mają być elementy daty: rok, miesiąc i dzień, to miesiąc musi być liczbą z zakresu od 1 do 12 a dzień liczbą z zakresu od 1 do ostatniego dopuszczalnego dnia w konkretnym miesiącu. Wprowadzanie innych wartości można kontrolować na podstawie kryteriów związanych z merytorycznym zakresem zbieranych danych. Zgodzić się też trzeba z tym, że pewnych pomyłek nie da się uniknąć. Gdy ktoś wprowadzając liczbę odpowiadającą np. za ilość osób wprowadzi 27 zamiast 17 to takiej sytuacji nie zapobiegniemy, ale gdy zechce wpisać 18,5 to na pewno popełniony został błąd i to możemy zasygnalizować.



Zasady sprawdzania poprawności danych można przypisać jednej albo wielu komórkom. Jeśli takie same zasady mają być przypisane wielu komórkom to należy zaznaczyć obszar lub obszary, w których te zasady będą obowiązywały.

Opracowanie to napisałem z myślą o arkuszach kalkulacyjnych Excel, Calc i Google Docs dlatego nie dziw się czytelniku, że wstęp, który właśnie przeczytałeś jest wspólny dla każdego z tych opracowań.

W arkuszu kalkulacyjnym Excel 2007 za sprawdzanie poprawności danych – i związane z tą funkcją inne działania – odpowiada ikonka narzędziowa „Poprawność danych”.

Zakładka "Dane" grupa "Narzedzia danych".
Rys. 1: Excel 2007 – wywoływanie narzędzia weryfikacji danych.

Ikonka udostępnia trzy polecenia, z których – „Poprawność danych...” – zostanie omówione jako pierwsze. Aby wymusić sprawdzanie poprawności danych w wybranej komórce należy tę komórkę uaktywnić. Jeśli sprawdzanie poprawności ma dotyczyć obszaru lub obszarów komórek należy taki obszar, lub obszary, najpierw zaznaczyć. Przy pierwszym użyciu polecenia „Poprawność danych” pojawi się okno dialogowe pokazane na rys. 2. Okno w tej postaci ukaże się także wówczas, gdy polecenie „Poprawność danych...” zostanie wydane w stosunku do komórki albo komórek, które nie mają przypisanego żadnego systemu weryfikowania poprawności.

Okno dialogowe "Sprawdzanie poprawności danych".
Rys. 2: Okno dialogowe „Sprawdzanie poprawności danych” w Excelu 2007.

Okno zawiera trzy karty służące ustawianiu konkretnych zasad sprawdzania.

Karta „Ustawienia” – definiuje zasady weryfikacji danych

Karta „Komunikat wejściowy” – pozwala określić komunikat, jaki zobaczy użytkownik po uaktywnieniu kontrolowanej komórki.

Karta „Alert o błędzie” – pozwala wskazać sposób postępowania programu po wykryciu danych niespełniających kryteriów określonych na karcie „Ustawienia” oraz określić treść komunikatu wyjaśniającego przyczynę zakwestionowania danych.



Podstawową kartą procedury sprawdzania poprawności danych jest karta „Ustawienia”. W rozwijalnym polu „Dozwolone” należy określić rodzaj dozwolonych danych. Widoczny na ilustracji wpis „Dowolna wartość” jest równoznaczny z brakiem weryfikowania danych i dlatego przy tym wyborze nieaktywne są inne opcje na tej karcie. Tę opcję należy wybrać wtedy, gdy komórkom, które mają przypisane reguły sprawdzania poprawności – wycofujemy je.

Na liście dozwolonych rodzajów danych znajdują się:

Ze wszystkimi – poza „Listą” i „Niestandardowe” – rodzajami danych związane jest kolejne rozwijane pole zatytułowane „Wartości danych”. Pozwala ono określić relacje, jakie musi spełnić wprowadzona wartość aby została uznana za poprawną. Wybrana relacja powoduje dodatkowo pojawienie się jednego lub dwóch pól wartości granicznych uściślających relację. Relacje te to:

Rodzaj wybranej relacji w powiązaniu z rodzajem danych zmienia nazewnictwo kolejnych pól na karcie oraz ich liczbę. Przykład takiego wyboru przedstawia rys. 3.

Wybór wartości typu "Data" z relacją "między".
Rys. 3: Określanie poprawności dat dla komórki danych w Excelu 2007.

Relacje „między” i „nie między” wymagają wypełnienia dwóch pól wartości. Pola „Minimum” określającego wartość minimalną oraz pola „Maksimum” określającego wartość maksymalną, pomiędzy którymi albo poza którymi musi znajdować się poprawna wartość danej. Wartości graniczne w tego typu relacji traktowane są jako poprawne w relacji „między” i niepoprawne w relacji „nie między”. Pozostałe rodzaje relacji wymagają podanie jednej tylko wartości, która, w zależności od relacji jest wartością wymaganą albo niedopuszczalną (relacje: „równa”, „nierówna”), bądź minimalną lub maksymalną. Nazewnictwo tych pól zmienia się zależnie od wybranego typu danych i dla typu „Data” pokazanego na rysunku powyżej, wartość minimalna opisana jest jako „Data początkowa” a wartość maksymalna jako „Data końcowa”.

Pola przeznaczone na podanie wartości mają na swojej prawej krawędzi przycisk pozwalający odwołać się do komórki w arkuszu. Można więc, zamiast wpisywania konkretnych wartości wskazać adresy komórek, które takie wartości zawierają. Należy pamiętać o relatywizacji adresów komórek w arkuszu kalkulacyjnym. Jeśli jest to niezbędne należy wprowadzić adresy bezwzględne. W Excelu, po wskazaniu adresu komórki, można cyklicznie zmieniać sposób jej adresowania poprzez naciskanie klawisza F4.

Inaczej ma się sprawa z rodzajem „Lista”. Ten typ danych dopuszcza jako poprawne tylko takie dane, które znajdują się na liście dopuszczalnych wartości. W tym przypadku lista zawierać może każdy rodzaj wartości, a więc liczby, teksty i wartości logiczne. Przykład takiego sprawdzania poprawności prezentuje kolejna ilustracja.

Sprawdzanie poprawności danych typu "Lista".
Rys. 4: Rodzaj danych „Lista” w Excelu 2007.

Elementy listy należy wpisać do pola nazwanego „Źródło” rozdzielając je separatorem listy. W polskiej wersji Excela jest to znak średnika. Na przedstawionej ilustracji lista składa się z sześciu elementów. Pierwszy to liczba 7, drugi to tekst „TRUE”, trzeci to tekst „oko”, czwarty to liczba 5,6, piąty to formuła logiczna „=E1<20” a ostatni wpis to „prawda”. Ten wpis nie zostanie potraktowany jako zwykły tekst. W Excelu wpisanie do komórki słów „prawda” lub „fałsz” jest bowiem traktowane jak wpisanie wartości logicznej i tak także zostanie potraktowany na liście. W angielskojęzycznej wersji Excela odpowiednikami tych dwóch słów są „true” i „false” i w tamtej wersji to drugi parametr na tej liście zostałby potraktowany jako wartość logiczna. Jeżeli na liście powinny pojawić się teksty (a nie wartości logiczne) „prawda” lub „fałsz” to należy taki tekst poprzedzić znakiem apostrofu, a zatem wpisać je na listę jako „'prawda” albo „'fałsz”.

Wśród dostępnych do zaznaczenia opcji pojawiła się opcja „Rozwinięcia w komórce”. Jej zaznaczenie oznacza, że po wybraniu komórki z tego typu sprawdzaniem danych, obok niej pojawi się przycisk pozwalający rozwinąć całą listę po to aby użytkownik mógł wybrać jedną z pozycji. Przykład wykorzystania tej możliwości przedstawia ilustracja poniżej.

Przykład rozwiniętej listy dozwolonych wartości.
Rys. 5: Rozwijana lista wartości.

W tym przykładzie zmieniłem w formule adres komórki na C1 aby pokazać całą sytuację na rysunku. Po wybraniu z listy pozycji zapisanej jako „=c1<20”, w komórce wyświetli się wartość logiczna FAŁSZ, bo w komórce C1 znajduje się obecnie wartość 56 a ta nie jest mniejsza od 20.



Lista wartości dopuszczalnych do wprowadzenia do komórki może też znajdować się w innym miejscu arkusza, wpisana w kolejnych komórkach wiersza lub kolumny. Jeśli tak jest to w polu „Źródło” można wpisać adres tego obszaru albo wskazać ten obszar wykorzystując przycisk znajdujący się na prawej krawędzi pola „Źródło”.

Wyświetlana rozwijana lista przyjmuje szerokość komórki, z którą jest „powiązana” powiększoną o szerokość przycisku rozwijania listy, jeżeli zatem wpis na liście będzie za długi, to podczas wyświetlania listy zostanie obcięty do tylu znaków ile mieści się w przyjętej szerokości. Jeśli autor projektu nie włączy opcji „Rozwinięcia w komórce” użytkownik arkusza musi wpisać samodzielnie dopuszczalną wartość znajdującą się na liście.

Ostatni możliwy typ sprawdzania danych to „Niestandardowe”. Ten wybór oznacza, że na karcie „Ustawienia” pojawia się pole „Formuła”, w którym należy wpisać formułę lub wskazać odpowiednią formułę znajdującą się w jakieś komórce. Musi to być formuła logiczna, której wynikiem jest PRAWDA albo FAŁSZ. Po wprowadzeniu nowej wartości do tak kontrolowanej komórki sprawdzany jest wynik formuły. Jeśli wynikiem formuły jest prawda to wprowadzana wartość zostanie zaakceptowana, jeśli wynikiem formuły jest FAŁSZ, wprowadzana wartość zostanie potraktowana jako niepoprawna.

Poniżej znajduje się ilustracja obrazująca zastosowanie sprawdzania tego rodzaju.

Niestandardowe sprawdzanie poprawności danych.
Rys. 6: Arkusz z niestandardowym typem sprawdzania poprawności.

W tym projekcie założono, że podany zostanie dopuszczalny budżet wydatków, oraz że budżetu tego nie można przekroczyć w łącznych zakupach. Zaznaczyłem obszar komórek od B3 do B12 i zastosowałem wobec nich niestandardowy sposób sprawdzania poprawności. Polega on na dopuszczeniu jako poprawnej tylko takich wartości, które zsumowane są co najwyżej równe budżetowi. Na ilustracji widać wprowadzone już wartości i przygotowaną do wypełnienia pozycję „Zakup 4”. Gdy komórka ta została zaznaczona wywołałem ponownie okienko dialogowe „Sprawdzanie poprawności danych” i ten stan jest widoczny na ilustracji. Jak widać formuła logiczna będąca podstawą weryfikacji danych ma postać „=$B$13<=$B$1” i sprawdza każdorazowo, czy łączna kwota zakupów jest mniejsza od dopuszczalnego budżetu. Okienko sprawdzania poprawności wywołałem w tym momencie po to aby pokazać znaczenie jeszcze jednej opcji dostępnej na karcie „Ustawienia”, opcji „Zastosuj te zmiany we wszystkich komórkach z tymi samymi ustawieniami”. Jej zaznaczenie spowoduje, że Excel zaznaczy specjalnym wyróżnieniem wszystkie komórki sprawdzane tą sama regułą i ewentualne zmiany w formule zastosuje do wszystkich tych komórek.

Uwaga dotycząca tworzenia formuł: w formułach tworzonych w okienku sprawdzania poprawności danych także obowiązują zasady adresowania względnego. Wpisanie formuły w postaci „=B13<=B1”, gdy zaznaczony był obszar komórek, spowoduje że w każdej z nich ustalony zostanie adres relatywny dla reguły sprawdzającej. Dlatego, jeśli to jest niezbędne, należy wykorzystać adresy bezwzględne tak jak w przypadku przedstawionego przykładu.

Nieomówiona ostatnia opcja tej karty to: „Ignoruj puste”. Jej zaznaczenie nie wpływa bezpośrednio na przyjęcie lub odrzucenie danej. Tak naprawdę zmiana zawartości komórki na pustą (użycie klawisza DELETE) nie powoduje uruchomienia procedury sprawdzania i pusta komórka jest akceptowana. Opcja ta wpływa jednak na wynik prezentowania komórek, których zawartość nie spełnia kryteriów poprawności, realizowany w innym procesie.

Reguły sprawdzania poprawności danych można definiować w dowolnym momencie tworzenia arkusza. Jeśli komórki zawierały już jakieś wstawione wartości, to mimo wprowadzenia do komórek reguł sprawdzania poprawności, wartości te nie będą weryfikowane. Weryfikacja następuje tylko podczas fizycznego wprowadzania nowych wartości z klawiatury.

Są jeszcze dwie bardzo interesujące właściwości procesu tworzenia zasad sprawdzania poprawności wynikające z tego, że Excel „wie” gdzie znajdują się komórki w jakiś sposób weryfikowane. Jeżeli w arkuszu zostanie zaznaczony obszar, w którym znajdują się choćby dwie komórki o różnych zasadach weryfikacji, zostanie wyświetlony komunikat jak poniżej.

Komunikat informujący, że w zaznaczonym obszarze znajdują się różne zasady sprawdzania poprawności.
Rys. 7: Komunikat o różnych zasadach weryfikacji.

Wybór „OK” usunie dotychczasowe zasady sprawdzania i pozwoli utworzyć nowe, dla całego zaznaczonego obszaru.



Podobnie jest, gdy w zaznaczonym obszarze oprócz komórek o jednakowym sposobie weryfikacji znajdują się komórki bez zasad weryfikacji. Wówczas Excel wyświetli komunikat innego rodzaju.

Komunikat informujący o tym, że w zaznaczonym obszarze znajdują się komórki bez zasad sprawdzania poprwaności.
Rys. 8: Komunikat o występowaniu komórek bez ustawień poprawności.

Odpowiedź twierdząca w tym przypadku rozszerzy zasady weryfikacji na dodatkowe komórki.

Druga karta okienka dialogowego „Sprawdzanie poprawności danych” to „Komunikat wejściowy”. Pozwala ona na zadeklarowanie, czy taki komunikat ma być w ogóle pokazany (to opcja „Pokazuj komunikat wejściowy...”) oraz określenie tekstu tytułowego komunikatu i jego treści. Rys. 9. pokazuje przykład wypełnienia tej karty.

Przykład utworzenia komunikatu wejściowego.
Rys. 9: Karta „Komunikat wejściowy”.

Trzecia karta tego okienka to „Alert o błędzie”. Tutaj także należy wybrać opcję decydującą, czy komunikat o niepoprawnych danych ma się pokazywać. Jednak oprócz tytułu i treści komunikatu użytkownik może dodatkowo wskazać jedną z trzech akcji, jakie podejmie Excel w przypadku wartości niepoprawnych. Akcjami tymi są: „Zatrzymaj” oznaczająca, że wartość niepoprawna nie zostanie dopuszczona do wprowadzenia, „Ostrzeżenie” i „Informacja”, obie te akcje wyświetlają odpowiedni komunikat, lecz dopuszczają na wprowadzenie danych niespełniających kryteriów poprawności. Jeżeli użytkownik nie określi treści komunikatu, pojawi się komunikat standardowy wygenerowany przez program. Przykład wypełnionej karty „Alert o błędzie” dla akcji „Zatrzymaj” przedstawia rys. 10.

Przykład utworzenia komunikatu o błędzie danych.
Rys. 10: Karta „Alert o błędzie” z akcją „Zatrzymaj”.

Kolejne dwie ilustracje przedstawiają stan arkusza, w którym wprowadzono wartość 4000 do komórki „Zakup 4”. Widoczny jest komunikat wejściowy zdefiniowany dla komórki oraz w tym przypadku Komunikat o błędzie dla akcji „Ostrzeżenie”.

Przykład komunikatu o błędzie przy akcji "Ostrzeżenie".
Rys. 11: Komunikat o błędzie przy akcji „Ostrzeżenie”.

Odpowiedź „Tak” – akceptuje wprowadzoną wartość, odpowiedź „Nie” – pozwoli poprawić wartość w komórce, odpowiedź „Anuluj” – przywróci poprzednią zawartość komórki. Dla akcji „Informacja” dostępna jest tylko możliwość zaakceptowania wartości (przycisk „OK”) albo jej odrzucenia (przycisk „Anuluj”).

Gdy treść komunikatu o błędzie nie zostanie zdefiniowana, wyświetlony zostanie komunikat standardowy. Na rysunku poniżej komunikat taki został wyświetlony dla akcji „Zatrzymaj”.

Przykłąd standardowego komunikatu o błędzie generowanego przez akcję "Zatrzymaj".
Rys. 12: Standardowy komunikat o błędzie przy akcji „Zatrzymaj”.

Komunikat związany z tą akcją pozwala jedynie ponowić próbę albo anulować proces wprowadzania danej. Przy wyborze „Anuluj” w komórce zostanie przywrócona poprzednia wartość.

Ponieważ wynikiem akcji „Ostrzeżenie” oraz „Informacja” może być zaakceptowanie niepoprawnych danych użytkownik może chcieć, zobaczyć gdzie znajdują się takie wartości. Należy wówczas skorzystać z drugiego polecenia widocznego na rys. 1. „Zakreśl nieprawidłowe dane”. Wybór tego polecenia spowoduje, że komórki, w których wartości nie spełniają kryteriów poprawności zostaną zakreślone czerwonym owalem. Przy zakreślaniu komórek ma znaczenie czy w zasadach weryfikacji była zaznaczona opcja „Ignoruj puste”.

Przykład wykorzystania takiej wizualizacji przedstawia rys. 13.

Wizualizacja niepoprawnych danych w arkuszu.
Rys. 13: Wizualizacja niepoprawnych danych w arkuszu Excel.

W tym przykładowym arkuszu dla komórek od B2 do B11 określono zasady poprawności mówiące, że poprawne są tylko liczby całkowite w zakresie od 1 do 5 i nie będą ignorowane komórki puste. Po wybraniu polecenia „Zakreśl nieprawidłowe dane” owalami objęte zostały wszystkie te komórki, w których wartość względem tych wymagań jest niepoprawna. Ponieważ wyłączona była opcja „Ignoruj puste”, komórka B4, jako pusta, także została zaznaczona jako niepoprawna.



Usuwanie zakreśleń realizuje polecenie „Wyczyść zakreślenia nieprawidłowych danych”. Zakreślenia zostaną także usunięte po wykonaniu niektórych operacji na arkuszu, np. po dodaniu nowego wiersza.