Yestok.pl

Opracowanie powstało w oparciu o wersję programu istniejącą w trakcie jego pisania, Libre Office: 4.2.6, Apache OpenOffice: 4.1.1.

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 programie Calc za wprowadzenie zasad weryfikowania poprawności danych odpowiada polecenie „Dane – Poprawność danych...”. Jego wywołanie spowoduje wyświetlenie okna dialogowe pokazanego na rysunku 1.

Jak już wspomniałem, jeśli podczas wywołania tego polecenia zaznaczony był obszar komórek lub kilka obszarów to określone zasady weryfikacji danych dotyczyć będą każdej komórki w tych zakresach. Okno dialogowe składa się z trzech kart:

Pierwsza – „Kryteria” – pozwala zdefiniować zależności, jakie zostaną sprawdzone aby wpis mógł być zaakceptowany jako poprawny.

Druga – „Pomoc przy wprowadzaniu” – pozwala zdefiniować tekst pomocniczy, jaki zobaczy użytkownik po uaktywnieniu komórki zawierającej mechanizm kontroli poprawności danych.

Trzeci – „Komunikat o błędzie” – pozwala określić oprócz komunikatu także działanie podejmowane przez program w sytuacji, gdy wprowadzone dane nie spełniają kryteriów poprawności.

Zajmę się teraz zakładkami okna dialogowego.

Na karcie „Kryteria”, na pozycji „Zezwalaj”, znajduje się pole z rozwijaną listą dopuszczalnych rodzajów danych wprowadzanych do komórki. Gdy okienko dialogowe otwarte jest w odniesieniu do komórki, która nie miała przypisanego mechanizmu weryfikacji, widoczny jest wpis „Wszystkie wartości”, a pozostałe opcje na tej karcie są niedostępne. Ten wpis oznacza, że komórka nie ma przypisanego żadnego mechanizmu sprawdzania poprawności. Ten wpis należy wybrać wtedy, gdy komórce lub zakresom wycofujemy wcześniej przypisane kryteria weryfikacji. Wybór innej pozycji z listy „Zezwalaj” uaktywnia możliwości dokonywania zmian w pozostałych opcjach widocznych na tej karcie a te z kolei mogą zmieniać wygląd karty i sposób ustawiania dalszych parametrów i właściwości.

W Calc, oprócz wspomnianej opcji „Wszystkie wartości” można wybrać także jedną z następujących możliwości:

• Liczba całkowita – pozwala wprowadzać tylko liczby całkowite.
• Dziesiętny – można wprowadzać dowolne liczby.
• Data – dopuszcza wprowadzanie daty wpisywanej w postaci ciągu znaków DD.MM.[RR]RR albo [RR]RR-MM-DD, ciąg musi odpowiadać poprawnej dacie. Format przedstawienia wartości w komórce, jeśli nie został wcześniej ustalony przez autora, zostanie przypisany automatycznie.
  (Tym, którzy po raz pierwszy spotykają się z zapisem używającym nawiasów kwadratowych wyjaśniam, że obejmują one te elementy jakie można opuścić podczas wpisywania wartości. Zapis [RR]RR oznacza że rok np. 2014 można wpisać jako liczbę 2014 ale także jako 14)
• Czas – dopuszcza wprowadzanie czasu w postaci ciągu znaków gg:[mm[:ss]], musi to być poprawna godzina, minuta i sekunda.
• Zakres komórek – dopuszcza wartości znajdujące się na skończonej liście znajdującej się w innej części arkusza.
• Lista – dopuszcza wartości znajdujące się na skończonej liście stanowiacej część kryterium wyboru.
• Długość tekstu – dopuszcza wartości o określonej ilości znaków. Ten sposób kontroli dotyczy zarówno wprowadzanych tekstów jak i liczb.

Oprócz „Zakresu komórek” i „Listy” we wszystkich pozostałych możliwościach pozycji „Zezwalaj” można na pozycji „Dane” określić rodzaj relacji, jaki ma zajść dla wpisywanej do komórki wartości, aby została ona uznana za poprawną. Do wyboru są następujące relacje (w dwóch przypadkach relacje te inaczej są nazwane w Apache OpenOffice i LibreOffice):

• „równy” – wówczas w polu „Wartość” należy podać tę wartość.
• „mniejsze niż” – wówczas nazwa pola „Wartość” zostanie zmieniona na „Maksimum” a w polu należy wpisać tę maksymalną wartość.
• „większy niż” – nazwa pola „Wartość” zmieni się tym razem na „Minimum” i odpowiednią wartość należy wpisać.
• „mniejsze lub równe” – działa analogicznie do „mniejsze niż”, ale jako prawidłową dopuszcza także wartość maksymalną.
• „większa lub równa” – działa analogicznie do „większy niż”, ale jako prawidłową dopuszcza także wartość minimalną.
• „nie równy” – w polu „Wartość” należy podać tę wartość.
• „między” (w Apache OpenOffice) albo „prawidłowy zakres” (w LibreOffice) – w tym rodzaju relacji pojawią się dwa pola do określenia wartości: „Maksimum” i „Minimum” a wartość poprawna znajdować się musi wewnątrz zakresu z wartościami granicznymi włącznie.
• „nie między” (w Apache OpenOffice) albo „nieprawidłowy zakres” (w LibreOffice) – tu także pojawią się dwa pola, jak powyżej, lecz poprawną będzie wartość leżąca poza zakresem danych, bez wartości granicznych.

Jeżeli więc w wybranej komórce dopuszczamy wprowadzenie wartości liczbowej między 7,8 a 12,65, kryteria poprawności powinny zostać zdefiniowane następująco:

Chciałbym sprecyzować opis wartości granicznych związanych z wyborem „Czas”. Jeśli wpisać chcemy tylko godzinę wpisujemy ją z symbolem dwukropka, np. godzina siódma zapisywana jest tak „7:”, jeśli wpisujemy godzinę z minutami po liczbie minut nie musimy już stawiać dwukropka chociaż jego postawienie nie jest błędem. Zatem 15 po siódmej można zapisać tak „7:15”. Tak więc jeżeli wpisywany czas ma zawierać się między siódmą a piętnastą w polu „Minimum” wpisujemy „7:” a w polu „Maksimum” – „15:”. Właściwa, pełna forma czasu pojawi się w polach po ponownym otwarciu okna „Poprawność danych”.Przy takim zapisie do komórki wprowadzać można tylko czas.

Jeżeli jako wartości graniczne czasu zostaną podane daty i czas łącznie, to do komórki należy także wprowadzać datę i czas. Dopuszczalne wówczas będą wszystkie godziny od godziny w dacie początkowej do godziny przy dacie końcowej. Przykładowy zapis wartości minimalnej „1.12.14 7:” i wartości maksymalnej „10.12.14 15:30” oznacza, że do komórki wprowadzić można dowolną godzinę od siódmej pierwszego grudnia 2014 roku do piętnastej trzydzieści dziesiątego grudnia 2014. Wpisywany ciąg znaków musi zawierać także datę i mieć postać „DD.MM.[RR]RR gg:[mm[:ss]]” albo „[RR]RR-MM-DD gg:[mm[:ss]]”.

Dodam tu od razu, że zamiast konkretnych wartości do pól mogą być wpisane adresy komórek, a nawet całe formuły. Zawartość komórki lub obliczona wartość formuły stanie się wartością graniczną. Gdyby więc w arkuszu należało wprowadzić np. datę, ale nie wcześniejszą niż wczorajszą to karta kryteriów mogłaby wyglądać tak:

W tym przykładzie formuła DZIŚ()-1 wyznacza bieżącą datę pomniejszoną o jeden, a więc datę wczorajszą. Zatem dzisiaj mogę do komórki wpisać dowolną datę przyszłą, dzisiejszą lub wczorajszą. Ponieważ weryfikacja danych odbywa się tylko podczas fizycznego wpisywania wartości do komórki, Calc w stosunku do wpisanych już wartości nie reaguje na ich poprawność. Wczorajsza data, mimo że w następnych dniach nie spełnia już kryterium poprawności jest zaakceptowana w arkuszu.

Tutaj dygresja: procedury weryfikacyjne nie zadziałają także przy skopiowaniu lub przeniesieniu wartości z innej komórki. Należy przy tym liczyć się z tym, że komórka przyjmie właściwości komórki kopiowanej lub przenoszonej i może utracić właściwości sprawdzania poprawności.

Wpisywanych adresów lub formuł nie trzeba poprzedzać znakiem „=”. Zawartość komórki lub wynik formuły wyznaczy odpowiednią wartość graniczną. Wpisywane adresy komórek nie podlegają w tych polach zasadom używania skrótu klawiaturowego F4 (a w edycji Apache OpenOffice SHIFT+F4) , czyli zamianie adresu komórki na adres bezwzględny. Adresy bezwzględne należy zatem wpisać samodzielnie wykorzystując znak „$”. Korzystanie z adresów bezwzględnych jest tu ważne, bo jeśli kryterium poprawności tworzone jest jednocześnie dla wielu komórek i wartości graniczne wykorzystują adresy komórek, to adresy te są relatywnie przekształcane w treści kryterium każdej komórki. Gdy więc dziesięciu zaznaczonym kolejnym komórkom znajdującym się w kolumnie przypisze się kryterium w rodzaju „Zezwalaj – Liczba całkowita”, „Dane – mniejsze niż” a w polu „Maksimum” podany zostanie adres np. A1, to w drugiej komórce tego obszaru pole „Maksimum” będzie miało wpisane A2 w kolejnym A3 itd. Jeśli wartości tych komórek miałyby być mniejsze zawsze od wartości wpisanej do komórki A1, to podczas tworzenia kryterium ten adres należałoby zapisać tak: $A$1.

Inaczej prezentuje się karta „Kryteria”, jeśli w pozycji „Zezwalaj” wybrano „Lista” albo „Zakres komórek”. Te opcje oznaczają, że do komórki wpisać można jedną z wartości znajdującą się na liście wyboru wpisanej bezpośrednio do kryterium („Lista”) albo umieszczonej w innym miejscu arkusza („Zakres komórek”).

Poniżej na ilustracji przedstawiam wariant „Lista”, w którym dopuszczalne wartości – nazwy miesięcy – umieszczono bezpośrednio na liście znajdującej się w polu „Wpisy”. Każda pozycja tej listy kończona jest naciśnięciem klawisza ENTER

Wśród nowych pól opcji, jakie pojawiają się przy wyborze wariantów „Lista” lub „Zakres komórek” pojawia się opcja „Pokaż listę wyboru”, która – jeśli będzie zaznaczona – spowoduje, że na krawędzi uaktywnionego pola pojawi się przycisk rozwijający listę, pozwalając tym samym wskazać i wybrać wymagany wpis. Widoczna na ilustracji kolejna opcja, tutaj niezaznaczona, „Sortuj wpisy rosnąco”, sprawi, że wyświetlona lista będzie uporządkowana w kolejności wzrastających wartości. Źródłowa kolejność listy nie będzie zmieniona. Przykład komórki z tego typu sposobem weryfikacji przedstawia rys. 5.

Zaznaczony czerwonym okręgiem przycisk rozwijania listy, znika po uaktywnieniu innej komórki w arkuszu. Jeśli opcja „Pokaż listę wyboru” nie zostanie zaznaczona to i tak należy wprowadzać wartości zgodne z listą, tyle że trzeba znać te wartości.

Gdy wartości listy znajdują się w komórkach arkusza – a musi to wówczas być ciągły obszar jednokolumnowy lub jednowierszowy – weryfikację można zrealizować wykorzystując wariant „Zakres komórek”. Przykład takiego odwołania znajduje się poniżej. Na ilustracji pokazałem też odpowiadający fragment arkusza zawierający listę.

We wszystkich wariantach karty „Kryteria” znajduje się opcja „Zezwalaj na puste komórki”. Opcja ta nie ma wpływu na weryfikowanie danych podczas wprowadzania. Inaczej mówiąc, zawartość komórki kontrolowanej pod względem poprawności może zostać usunięta klawiszem DELETE i nie spowoduje to żadnej sygnalizacji braku poprawności. Jednak zaznaczenie tej opcji ma wpływ na inne działanie, na wyszukiwanie i oznakowanie komórek zawierających niepoprawne dane. Jeśli zatem opcja ta będzie wyłączona, to komórki puste będą wykazywane jako zawierające dane spoza dopuszczalnego zakresu, ale jest to realizowane w innym trybie pracy programu. Wspomnę o tym w dalszej części, po omówieniu pozostałych kart.

Karta „Pomoc przy wprowadzaniu” pozwala określić treść pomocy, jaką uzyska użytkownik po przejściu do kontrolowanej komórki. Jej przygotowanie i rolę przedstawia rys. 7.

Na ilustracji widać wyświetloną pomoc pojawiającą się po ustawieniu aktywności w komórce A1. Na tej samej ilustracji pokazuję też wygląd karty pomocy utworzonej dla tego przykładu. Pomoc będzie widoczna tylko wówczas, gdy zaznaczona zostanie opcja „Pokaż pomoc po zaznaczeniu komórki”. Ilustracja wyjaśnia wystarczająco znaczenie pól tej karty.

Ostatnia karta to „Komunikat o błędzie”. W sposób podobny do przedstawionego na karcie poprzedzającej można podać objaśnienie, dlaczego wartości nie zostały zakwalifikowane jako poprawne. Jednak aby taki komunikat się pojawił i tutaj zaznaczona musi być stosowna opcja „Pokaż informację o błędzie przy wprowadzaniu niepoprawnych wartości”.

Kartę i jej ustawienia pokazuje rys. 8.

Na tej karcie można dodatkowo wybrać jedną z czterech akcji, jaka zostanie podjęta przez oprogramowanie. Akcja „Zatrzymaj” wyświetli komunikat o błędzie, a jeśli sami nie przygotujemy komunikatu, będzie to komunikat wystawiony przez system arkusza, jak na rysunku poniżej.

Akcja „Zatrzymaj” generuje komunikat z przyciskiem „OK”. Jego użycie zamyka komunikat i przywraca poprzednią zawartość komórki. Przy tej akcji nie można zatem wprowadzić niepoprawnych wartości do komórki.

Akcje „Ostrzeżenie” i „Informacja” wyświetlają komunikat z dwoma przyciskami: „OK”, który spowoduje zaakceptowanie wartości niepoprawnej i wstawienie jej do komórki oraz przycisku „Anuluj”, który odrzuci wpisaną wartość.

Przykład takiego komunikatu, tym razem zredagowanego we własny sposób – poniżej.

Ilustracja pokazuje wszystkie aspekty zastosowania kart pomocy i błędów. Widoczny jest zarówno tekst pomocy wyświetlony po ustawieniu aktywności w komórce oraz zredagowany komunikat informujący o błędzie gdy podejmowana akcją jest „Informacja”. Calc w komunikacie rozróżnia rodzaj akcji przez to, że w komunikacie „Ostrzeżenie” domyślnie ustawionym przyciskiem komunikatu jest „Anuluj” a w akcji „Informacja” – „OK”.

Z możliwości wyboru akcji i reakcji na komunikat wynika, że sprawdzając poprawność danych można zezwolić na umieszczenie w arkuszu wartości, które weryfikator uznał za błędne. A ponieważ tak jest, to może nas zainteresować czy a jeśli tak, to gdzie w arkuszu znajdują się takie wartości.

W Calcu osiągniemy to za pomocą polecenia „Narzędzia – Detektyw – Zaznacz kółkiem nieprawidłowe dane”. Polecenie to oznaczy kółkiem każdą komórkę zawierającą niepoprawne dane. Wywołanie tego polecenia uruchomi sprawdzenie poprawności we wszystkich komórkach, którym takie zasady przypisano w odniesieniu do aktualnej zawartości komórek. Żadne dane nie są usuwane ani poprawiane lecz wartości sprzeczne z przypisanym kryterium poprawności są oznaczane kółkiem. Ilustruje to kolejny rysunek.

Na tej ilustracji, w drugim wierszu arkusza, w komórkach od A2 do D2 umieściłem pewne wartości, ich suma znajduje się w komórce E2. Komórki w kolumnach A, B i D są sprawdzane pod kątem poprawności danych. Nad każdą komórką symbolicznie zaznaczyłem, jakie kryteria powinna spełnić wartość w nich zapisana aby została zaliczona do prawidłowych. W komórce A2 powinna to być wartość większa od 7. Wywołanie omawianego polecenia „Zaznacz kółkiem...”, zaznaczyło tę właśnie komórkę, bo wpisana do niej liczba 4 nie spełnia wymogu poprawności danych. To właśnie w tej sytuacji będzie miało znaczenie, czy na karcie „Kryteria” zaznaczona była opcja „Zezwalaj na puste komórki”. Jeśli opcja ta była wyłączona to puste komórki, objęte weryfikacją wartości, zawsze zostaną oznakowane jako zawierające dane nieprawidłowe.

Aby wyłączyć zaznaczanie kółkiem, należy w poleceniu „Detektyw” wybrać opcję „Usuń wszystkie strzałki”.

Ostatnią akcją, jaką można zadysponować na karcie „Komunikat o błędzie” to „Makro”. Jej wybór wymaga jednocześnie wskazania makra, jakie zostanie wywołane wtedy, gdy proces weryfikacji zakończy się niepowodzeniem. Wyboru właściwego makra dokonuje się przy pomocy przycisku „Przeglądaj”, który staje się dostępny tylko dla tej akcji. Jeśli makro nie zostanie wskazane pojawi się komunikat informujący o braku makra o podanej nazwie.

Aby powielić zdefiniowane kryteria w innych komórkach można użyć polecenia „Malarz formatów” (w Apache OpenOffice) albo „Format pędzla” (w LibreOffice). Ikony odpowiadające za te polecenia znajdują się w pasku narzędzi standardowych.

Arkusze kalkulacyjne Calc w żaden sposób nie wizualizują komórek zawierających kryteria sprawdzania danych. Chcąc poprawić lub zmienić kryteria poprawności trzeba znać zakresy komórek, w których takie kryteria zostały ustalone i najpierw zaznaczyć odpowiednie zakresy. Rozsądnym rozwiązaniem jest użycie odmiennego tła dla takich komórek aby móc łatwo je identyfikować. Ostateczne rozwiązanie należy jednak zawsze do autora.