Inny wymiar

W poprzednim poście przybliżyłem Wam podstawy tworzenia tabel. Poznaliśmy użyteczny skrót klawiszowy CTRL+L, który tworzy tabelę z zakresu komórek. Przekonaliście się, jak kolumny obliczeniowe pomagają zachować porządek w formułach, a odwołania do tabel podnoszą ich czytelność.
Pora zaprząc tabele do czegoś bardziej pożytecznego. Dodajmy im dodatkowy wymiar, używając sprawdzania poprawności danych.

3. Wsparcie dla walidacji wprowadzanych danych

Wróćmy do przykładowego pliku, który zaczęliśmy budować w ostatnim tygodniu. Uważny obserwator zauważył już pewnie, że tabela zawiera przynajmniej 2 kolumny, w których warto by ograniczyć możliwe do wyboru wartości. Używa się do tego funkcji Dane > Poprawność danych.
Pierwsza kolumna, dość oczywista, to Vat%. Zazwyczaj w każdym kraju używanych jest zaledwie kilka dopuszczalnych stawek procentowych. Przyjmijmy, że taka lista ogranicza się do: 23%, 8%, 5% oraz 0% (dla uproszczenia pomijam celowo stawki specjalne typu zwolniony).

• Aby nie zaśmiecać widoku w arkuszu z główną tabelą faktur, dodajmy osobny arkusz w tym pliku.
  
• Nazwijmy go "Wymiary" (dwukrotne kliknięcie na nazwie arkusza pozwala ją zmienić). Umieścimy w nim wszelkie potrzebne nam słowniki wymiarów, które potem możemy zastosować przy sprawdzaniu poprawności.
  
• Zaczynając od komórki A1 arkusza Wymiary wypełnij kolumnę komórek wartościami jak poniżej:

  Stawka
  23%
  8%

  
• Kliknij w komórkę A1 i wciśnij CTRL+L. Zweryfikuj czy zakres jest prawidłowy i zatwierdź okno dialogowe OK
• Nazwij naszą tabelę "tblStawkiVAT"
  
• Wróć do poprzedniego arkusza z listą faktur
• Aby zaznaczyć całą kolumnę w tabeli, nie musisz ręcznie zaznaczać wszystkich komórek. Możesz skorzystać z gorących punktów tabeli. Przesuń kursor myszki na górną krawędź nagłówka kolumny [Vat%]. Ikona kursora zmieni się na czarną strzałkę skierowaną w dół:
  
• Kliknij raz w tym miejscu. Cały obszar danych w kolumnie zostanie zaznaczony.
• Na wstążce odszukaj zakładkę Dane i wybierz Narzędzia danych > Poprawność danych > Poprawność danych
• W oknie dialogowym [Sprawdzanie poprawności danych] wybierz w kryteriach poprawności Dozwolone = Lista
• W polu Źródło wpisz odwołanie do naszej tabeli

  =tblStawkiVAT[Stawka]

  i spróbuj zatwierdzić przyciskiem [OK]

Ups... coś nie wyszło? Błąd "Ta formuła jest nieprawidłowa (bla, bla, bla...)".

Co jest nie w porządku? Hmmmm... Pomyślmy. Jedną z rzeczy, o których należy wiedzieć przy ustawianiu poprawności danych, są dozwolone w tym polu wartości. Otóż sprawdzanie poprawności z listy, pozwala na podanie dwóch rodzajów list. Pierwszy to lista wpisana bezpośrednio, bez użycia znaku równości. Na przykład lista stawek VAT:

23%;8%;5%;0%

Ważna uwaga: Znak rozdzielający elementy listy, jest zależny m.in. od ustawień regionalnych języka. Jeśli pracujesz na angielskiej wersji systemu operacyjnego, zazwyczaj znak oddzielający elementy list ręcznych to "," (przecinek). Przy polskich ustawieniach - zawzyczaj jest to ";" (średnik). Możesz to zweryfikować dodając ręczną listę sprawdzania poprawności i obserwując, czy lista rozwijalna będzie zawierała osobne elementy. Jeśli nie spróbuj użyć innego separatora. Domyślny separator jest również widoczny w Ustawieniach regionalnych w Panelu sterowania. Należy odszukać okno Ustawień formatu daty, czasu i liczb, a potem przejść do ustawień dodatkowych. Będzie tam m.in. informacja, jaki znak służy jako separator listy.

Druga opcja to wskazany zakres komórek, które posłużą jako lista wartości do wyboru. Może być on wskazany bezpośrednio lub za pomocą nazwanego zakresu. Jeśli chcemy skorzystać z odwołania do kolumny naszej tabeli, musimy zatem nazwać ten zakres osobną nazwą albo zamienić odwołanie na bezpośrednie. Skorzystajmy z tej drugiej możliwości. Użyjemy do tego celu funkcji Excela ADR.POŚR. Zamienia ona tekstowe odwołanie na bezpośredni zakres komórek.

• Zmień formułę wpisaną w polu źródło na poniższą:

  =ADR.POŚR("tblStawkiVAT[Stawka]")

• Od tej chwili użytkownik Twojego arkusza będzie mógł wpisać w kolumnie stawki tylko jedną z wartości umieszczonych w tabeli tblStawkiVAT. Sprawdź to klikając w komórkę zawierającą stawki (kolumna F w tabeli faktur). Kiedy jest aktywne sprawdzanie poprawności z listy - obok prawej krawędzi komórki wyświetli się przycisk listy rozwijalnej. Po kliknięciu zobaczysz stawki z naszej tabeli pomocniczej: 23% i 8%
  
• Sprawdźmy, co się stanie jeśli dodamy to listy stawek kolejne wartości. Przejdź do zakładki Wymiary i dopisz poniżej tabeli kolejne 2 warotści: 5% oraz 0%
• W miarę wpisywania tabela będzie samoczynnie zwiększać swój obszar.
  
• Wróć do tabeli faktur i sprawdź jakie wartości są teraz dozwolone do wyboru.
  

No a teraz najpiękniejsza chwila: w miarę dodawania wierszy, sprawdzanie poprawności będzie również obejmowało nowe pozycje dodawane do tabeli. Sprawdzenie tego pozostawiam Wam: dopiszcie jeszcze kilka faktur do tabeli i zobaczcie jak wygląda pole podatku w nowych wierszach.

Dla dociekliwych: Używanie funkcji ADR.POŚR może wymagać czujności, jeśli Wasz budowany model ulega jeszcze istotnym zmianom. Należy pamiętać, że każda zmiana nazwy tabeli źródłowej, a także zmiana nazwy kolumny użytej jako lista wartości, będzie wymagać zaktualizowania nazwy użytej w sprawdzaniu poprawności. Można tego uniknąć, zastępując tą formułę nazwanym zakresem. Używa się do tego Menedżera nazw dostępnego na karcie Formuły (lub przez skrót CTRL+F3). Po utworzeniu nazwy zakresu, można jej już bezpośrednio użyć w Sprawdzaniu poprawności.

Następnym razem pomówimy nieco o tabelach w połączeniu z funkcją WYSZUKAJ.PIONOWO. Stworzymy tabelę dostawców i spróbujemy scalić informacje z niej z główną tabelą faktur. Zapraszam już wkrótce.