Yestok.pl

Opracowanie powstało w oparciu o wersję programu nazwanego przez twórców Nowe Arkusze Google.

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ń.

Sprawdzanie poprawności danych w arkuszach Google realizowane jest poprzez wywołanie polecenia „Dane – Sprawdzanie poprawności...”. W wyniku wywołania tego polecenia pojawi się okno dialogowe o wyglądzie pokazanym poniżej.

Wywołanie tego polecenia nie wymaga wskazania komórki, której sprawdzanie będzie dotyczyło ani zaznaczania obszaru komórek. Pierwsza definiowana pozycja tego okna: „Zakres komórek” pozwala wskazać komórki, które mają być sprawdzane. Po otwarciu okna w tej pozycji wpisany jest adres komórki, w jakiej znajdował się kursor tablicowy, a jeżeli zaznaczony był już jakiś obszar to adres tego obszaru. Inaczej jest, gdy w arkuszu zaznaczonych jest kilka obszarów, wówczas bowiem w tej pozycji znajduje się opis tylko jednego obszaru, tego, który został zaznaczony jako ostatni. Ten wybrany zakres komórek można zaakceptować, ale symboliczny przycisk znajdujący się po prawej stronie pola pozwala wybrać inną komórkę lub obszar komórek, które będą podlegały sprawdzaniu. Po jego naciśnięciu pojawi się okno dialogowe jak na rys. 2.

Teraz wystarczy kliknąć inną wybraną komórkę lub zaznaczyć obszar komórek w arkuszu aby definicje sprawdzania zostały przypisane do wybranego miejsca.

Kolejnym krokiem jest wybór zasad, według jakich wprowadzane dane będą weryfikowane. Za to ustawienie odpowiada pozycja „Kryteria”.

Zawiera ona co najmniej dwa pola. Pierwsze decyduje o rodzaju weryfikowanych danych. Jest to rozwijana lista zawierająca następujące pozycje:

• Lista z zakresu
• Lista elementów
• Liczba
• Tekst
• Data
• Niestandardowa formuła to

Drugie i ewentualnie następne pola w tej pozycji zależą od wyboru dokonanego w polu pierwszym.

Kolejny wybór, jakiego należy dokonać to decyzja co ma się wydarzyć, jeżeli wprowadzone dane nie spełniają kryteriów wyboru. Pozycja ta opisana jest jako „Jeśli dane są nieprawidłowe:” i umożliwia wybór jednej z dwóch opcji:

• „Pokaz ostrzeżenie” – ten wybór oznacza, że wartość, mimo że jest nieprawidłowa w stosunku do zasad sprawdzania poprawności, zostanie zaakceptowana. Komórka zawierająca taką wartość zostanie oznaczona symbolem czerwonego trójkąta umieszczonego w prawym górnym rogu komórki, ponadto po naprowadzeniu wskaźnika myszki na taką komórkę pojawi się komunikat ostrzegający. Komunikat ma standardową treść „Nieprawidłowa zawartość komórki”. Jeżeli w dalszych ustawieniach okna dialogowego zaznaczona zostanie opcja „Pokaz pomoc” treść komunikatu będzie uzupełniona o tekst zdefiniowany przez autora opracowania. Przykład tej sytuacji przedstawia rys. 3.



• „Odrzuć wprowadzone dane” – ten wybór oznacza, że dane niespełniające zasad poprawności dla danej komórki nie zostaną zaakceptowane. Program wyświetli stosowny komunikat w oddzielnym okienku. Standardowy komunikat generowany przez program pokazany jest poniżej.



Komunikat ma tylko jeden przycisk „OK” i jego wybór zamknie to okienko a w komórce pozostanie zawartość znajdująca się w niej przed próbą wprowadzenia niepoprawnej danej. Jeśli zaznaczona będzie wspomniana już powyżej opcja „Pokaż pomoc” po tekście „Wystąpił problem” zamiast widocznego tekstu standardowego pojawi się treść znajdująca się w polu „Pokaż pomoc”.

Pozycja „Wygląd” zawiera jedną opcję „Wyświetl przycisk w komórce by pokazać listę”. Opcją tą można manipulować tylko wtedy, gdy wybranym typem sprawdzania poprawności jest „Lista z zakresu” albo „Lista elementów”, wspomnę o niej przy omawianiu tych typów weryfikacji.

Ostatnim ustawieniem jest opcja „Pokaż pomoc” i związana z nią treść tej pomocy. W arkuszach Google po wybraniu rodzaju kryterium zastosowanego do sprawdzania wprowadzanych wartości i zdefiniowaniu dalszych ustawień automatycznie przygotowany zostaje tekst pomocy. Na rysunku 1. widoczny jest wpis „Kliknij i wprowadź wartość z zakres” (przez pomyłkę zapewne opuszczono literę „u” w ostatnim słowie tego tekstu). Wpis ten został przygotowany automatycznie dla pola kryteriów zawierającego „Lista z zakresu” i zostanie uzupełniony o zawartość, jaką użytkownik wprowadzi do pola leżącego obok. Utworzony tekst można zmienić na dowolny własny po zaznaczeniu pola „Pokaż pomoc”. Zaznaczenie tego pola poza tym, że pozwoli edytować własną treść pomocy sprawi, że to właśnie ten tekst pojawi się jako uzupełnienie komunikatu pokazanego na rys. 3. oraz zamieni tekst po słowach „Wystąpił problem” w oknie komunikatu pokazanego na rys. 4. Ponadto, gdy zostanie uaktywniona komórka, w której zdefiniowano zasady sprawdzania, i naprowadzi się na nią wskaźnik myszki włączona opcja „Pokaż pomoc” spowoduje wyświetlenie dodatkowego komunikatu informacyjnego, zawierającego tekst znajdujący się w tym polu. Postać takiego komunikatu rozpoczynającego się słowem „Weryfikacja” pokazuje rys. 5.

Użycie widocznego na rys. 1. linku „Resetuj” zastępuje tekst użytkownika, wprowadzony do pola pomocy, tekstem wygenerowanym przez program, zależnym od wybranego kryterium.

Ustalenie zasad poprawności dla komórek w arkuszu, które zawierają już jakieś wpisy nie wpływa na ich zawartość. Oprogramowanie analizuje jednak wpisane wartości i jeśli jakieś z nich nie spełniają kryteriów weryfikacji to komórki takie zostają oznaczone czerwonym trójkątnym znacznikiem w prawym, górnym narożniku. Naprowadzenie wskaźnika myszki na tak oznakowaną komórkę wyświetli komunikat o niepoprawności danych. Dane, które będą wprowadzane ponownie będą już sprawdzane i traktowane zgodnie z zadekretowaną zasadą „Pokaz ostrzeżenie” albo „Odrzuć wprowadzone dane”. Jeżeli jednak do komórki ponownie zostaną wprowadzone te same niepoprawne dane, jakie się w niej już znajdowały, to zostaną one przyjęte i nadal oznakowane jako niepoprawne, nawet wówczas, gdy działaniem ma być odrzucenie danych.

Ustalone zasady sprawdzania poprawności przypisuje się komórkom wybierając przycisk „Zapisz”. Jeżeli w zaznaczonym obszarze chcemy usunąć sprawdzanie poprawności danych należy skorzystać z przycisku „Usuń sprawdzanie poprawności”.

Zajmę się teraz kryteriami sprawdzania wprowadzanych danych. Kryteria określane są przez co najmniej dwa a co najwyżej cztery pola ustalane przez autora projektu. Pierwsze pole wybiera główny rodzaj sprawdzanych danych i decyduje liczbie dalszych pól.

• Kryterium „Lista z zakresu” umożliwia wprowadzenie do komórki wartości zgodnej z pewną listą. Taka lista musi znajdować się w arkuszu w pewnym obszarze. Może to być obszar dwuwymiarowy zawierający wszystkie dopuszczalne wartości, jakie można wprowadzić do komórki. Obok pola kryterium znajduje się tylko jedno dodatkowe pole, w którym należy podać zakres komórek zawierających taką listę. W polu tym znajduje się podpowiedź jak taki wpis powinien wyglądać (widać to na rys. 1). Listę a właściwie obszar zawierający taka listę można też wskazać po wybraniu symbolu znajdującego się w prawej części tego pola. Z tym kryterium związana jest opcja umieszczona na pozycji „Wygląd”. Jeśli zostanie zaznaczona, w komórce z takim kryterium pojawi się symbol szarego trójkąta z wierzchołkiem skierowanym w dół, widać go na rysunku 3. W komórkach pustych albo takich, w których zawartość jest wycentrowana lub dosunięta do lewej krawędzi, ten symbol wyświetlony jest po prawej stronie komórki. W pozostałych przypadkach, po lewej stronie. Wartości do komórki można wprowadzać z klawiatury (ten sposób jest jedynym możliwym, gdy wyłączono opcję „Wyświetl przycisk w komórce, by pokazać listę”) albo po kliknięciu wskaźnikiem myszki we wspomniany symbol wybrać z rozwiniętej listy dostępnych wartości.

Poniżej przykład takiej rozwiniętej listy w komórce która powinna zawierać stawki podatku VAT. W Polsce w 2014 roku obowiązują stawki: zw (zwolniony), 0%, 8% i 23%. Zakresem podanym w polu zakresu listy jest obszar $B$1:$E$1.



• Kryterium „Lista elementów” jest w gruncie rzeczy tym samym kryterium jak omówiona powyżej „Lista z zakresu”. Różnica polega na tym, że dla tego kryterium listę wartości należy wpisać bezpośrednio do pola znajdującego się obok pola kryterium. Lista składa się z pozycji oddzielonych przecinkami. Zastosowanie przecinka jako separatora pozycji listy nie pozwala na utworzenie listy zawierającej liczby dziesiętne, gdyż przecinki są traktowane jako separatory. To jest dla krajów, w których przecinek jest znakiem oddzielającym część całkowitą liczby od części ułamkowej niewątpliwie błędem w tym produkcie. Wysłałem w tej sprawie maila do Google sugerując aby separator listy był taki sam jak separator argumentów w funkcjach. Wszystkie uwagi dotyczące „Listy z zakresu” obowiązują także przy wyborze tego kryterium.
• Kryterium „Liczba”. To kryterium narzuca wprowadzenie do komórki liczby. Generuje ono kolejne pola dodatkowe, z których pierwsze określa relacje, jakie liczba musi spełnić a trzecie i ewentualnie czwarte – graniczne wartości tych liczb. Przykład tego okna poniżej.



W przedstawionym przykładzie sposób sprawdzania liczby („pomiędzy”) polega na zweryfikowaniu, czy wprowadzona wartość znajduje się przedziale, którego granice użytkownik musi sam określić w trzecim i czwartym dodatkowym polu.

Relacjami jakie można wybrać są:

• pomiędzy
• spoza zakresu
• mniejsze niż
• mniejsze lub równe
• większe niż
• większe lub równe
• równa się
• nie równa się

W zależności od wybranej relacji należy wypełnić jedno albo dwa pola określające wymagane wartości graniczne. Wybrany sposób sprawdzania determinuje także treść pomocy generowanej automatycznie przez program. Powyżej, na ilustracji, treść pomocy zawiera już tekst „Wprowadź liczbę pomiędzy” jednak dokończenie tego tekstu nastąpi dopiero po wprowadzeniu wartości granicznych.

• Kryterium „Tekst”. To kryterium pozwala określić poprawność danych według innych sposobów sprawdzania. Tymi sposobami są:
• „zawiera” – w dodatkowym polu należy podać, jakie znaki musi zawierać wprowadzana wartość aby została zaakceptowana. W przypadku tego sposobu wielkość liter jest ignorowana. Wpisany tekst musi znajdować się dokładnie w podanej postaci we wprowadzanych danych. Zatem wpisanie np. ciągu liter „abc” oznacza, że poprawnymi danymi będą np. „babcia”, „Abchazja” albo „żabcia”, a nie takie dane, które w ogóle zawierają litery „a”, „b” i „c”.
• „nie zawiera” – ten sposób to odwrócenie sposobu powyższego.
• „równa się” – wprowadzona wartość musi być zgodna z podanym tekstem. Przypominam, że wielkość liter nie odgrywa roli, czyli „babcia”, „BABCIA” lub „bAbCiA” to takie same teksty.
• „jest prawidłowym adresem e-mail” – ten interesujący sposób weryfikacji dostępny jest tylko w arkuszach Google i pozwala sprawdzać, czy wprowadzony wpis odpowiada swoją strukturą adresowi poczty elektronicznej. Oczywiście nie sprawdza, czy taki adres pocztowy istnieje, sprawdza tylko czy tak podany wpis mógłby być takim adresem.
• „jest prawidłowym adresem URL” – Ten sposób sprawdzania kontroluje czy wpisana zawartość odpowiada adresowi URL, czyli w najczęstszym rozumieniu – adresowi strony www (URL – Uniform Resource Locator – oznacza ujednolicony format adresowania stosowany w Internecie i w sieciach lokalnych).
• Kryterium „Data”. Przy pomocy tego kryterium można weryfikować proces wprowadzania dat do komórki. Sam sposób weryfikowania jest podobny do weryfikowania liczb. Pierwszy na liście jest jednak sposób o nazwie „jest prawidłową datą”. Pozwala on na sprawdzenie, czy wprowadzona wartość jest prawidłową datą. Datę można wprowadzać w postaci „DsMsRRRR”, „RRRRsMsD” albo „RRsMsD”. Litera „D” odpowiada liczbie dnia, który może być podany w postaci dwucyfrowej (np. 03) jak i jednocyfrowej (np. 3), litera „M” odpowiada numerowi miesiąca podanemu także w jednej z tych dwu postaci, litery „RRRR” odpowiadają numerowi roku podawanemu jako liczba czterocyfrowa natomiast litery „RR” numerowi roku podającemu dwie ostatnie cyfry roku (na dzień, w którym to opracowanie powstaje, liczby od 0 do 29 odpowiadają latom od 2000 do 2029 natomiast liczby od 30 do 99 latom od 1930 do 1999). Litera „s” oznacza w tym zapisie separator poszczególnych elementów daty. Może nim być znak spacji, kropki, myślnika czy ukośnika. Datę zatem można do komórki wpisać nawet takim ciągiem „4.12/2014” (4 grudnia 2014 r.) albo takim „14 11-24” (24 listopada 2014 r.). Weryfikator odrzuci wartości, które nie są prawidłową datą, a więc np. 29 lutego w roku nieprzestępnym, numer dnia albo numer roku spoza dopuszczalnego przedziału.

Pozostałe sposoby weryfikowania daty są analogiczne do tych zastosowanych przy weryfikowaniu liczb. Oto one:

• równa się
• wcześniejsza niż
• jest równa lub wcześniejsza niż
• jest późniejsza niż
• jest równa lub późniejsza niż
• pomiędzy
• spoza zakresu
• Ostatnie kryterium nosi nazwę „Niestandardowa formuła to”. Wymaga ono wpisania do dodatkowego pola dowolnej formuły logicznej. Wartości wprowadzane do komórki na podstawie tego kryterium zostaną uznane za prawidłowe, jeśli wynikiem formuły jest wartość TRUE. Jednak działanie tego kryterium nie wydaje się do końca dobrze przemyślane. Formuła jest bowiem sprawdzana przed wprowadzeniem wartości do komórki, a więc „nie wie” czy ta wartość wpłynie na wynik formuły. Jeśli wpłynie i zmieni wynik formuły logicznej na FALSE, uniemożliwi to dokonanie poprawki (oczywiście tylko wówczas, gdy wybraną akcją będzie odrzucenie danych niepoprawnych).

Wyjaśnię to na przykładzie, który przedstawia rys. 8.



W przedstawionym przykładzie do komórek opisanych tekstem „Zakup” należy wprowadzać wartości kolejnych zakupów. We wszystkich komórkach tego zakresu wprowadziłem sprawdzanie poprawności danych, polegające na tym, że wprowadzać można wartości zakupów, pod warunkiem, że łączna kwota zakupów jest mniejsza od 1200. Widać opis tej zasady w okienku dialogowym wyświetlonym dla komórki aktualnie aktywnej, czyli B4. W momencie wprowadzania wartości do tej komórki formuła logiczna ma wartość TRUE (naliczona kwota „Razem” jest mniejsza od 1200). Wprowadzę zatem wartość 700. Wartość ta zostanie zaakceptowana, bo wynik formuły logicznej wynosi ciągle TRUE. Jak można przewidzieć, wprowadzenie tej wartości spowoduje, że łączna kwota zakupów osiągnie wartość 1300 i będzie większa od dopuszczalnej 1200. W tej sytuacji formuła logiczna weryfikująca dane będzie miała wartość FALSE. To spowoduje, że wszystkie komórki objęte tą zasadą sprawdzania zostaną oznaczone jako zawierające niepoprawne dane. Tę sytuację pokazuję na ilustracji poniżej.



Teraz próba poprawienia jakiegokolwiek wyniku poprzez wprowadzenie mniejszej wartości skończy się komunikatem odrzucającym tę wartość, ponieważ w tym momencie formuła weryfikacyjna ma już wartość FALSE. Oczywiście nie oznacza to, że niczego nie można już zrobić. Wystarczy usunąć zawartość komórki klawiszem DELETE, a następnie wpisać nową wartość.

Arkusz Google w odróżnieniu od Excela i Calca od razu sygnalizuje fakt niepoprawności danych zawartych w komórkach. Zmienia się wówczas, jak już wspomniałem, prawy górny narożnik komórki, a naprowadzenie wskaźnika myszki na taka komórkę wyświetla odpowiedni komunikat. Znaczniki te nie pojawiają się oczywiście na wydrukach.