Automatyzacja list walidacji danych w programie Excel

Praca w arkuszu kalkulacyjnym Excela może być nieco podobna do pracy na Dzikim Zachodzie – o ile nie zostaną wprowadzone inne przepisy, użytkownicy mogą wprowadzić dowolną wartość w dowolnej komórce. Jednym ze sposobów ograniczenia użytkowników do predefiniowanego zestawu wartości jest funkcja walidacji danych w Excelu. Wyjaśnię, jak można tworzyć listy rozwijane w komórkach, a także jak «sprawdzać» listę Walidacji Danych w przyszłości, aby funkcja ta nie wymagała konserwacji w przypadku dodawania dodatkowych elementów w przyszłości. Technika ta rozwiązuje również irytujący problem w programie Excel 2007, gdzie listy walidacji danych nie mogą być umieszczane na innych arkuszach.

Zanim przyjrzymy się walidacji danych, najpierw ustalmy naszą listę. Powiedzmy, że chcemy, aby użytkownik wybrał rodzaj owoców. Na drugim arkuszu pustego skoroszytu utwórzmy listę, jak pokazano na Rysunku 1. Gdy już to zrobimy, utwórzmy listę w postaci tabeli w programie Excel 2007 i później lub listy w programie Excel 2003 i wcześniej. Po kliknięciu na dowolną komórkę w obrębie listy:

• Excel 2007 i później – Wybierz Wstaw, a następnie Tabela. Upewnij się, że wybrana została opcja Moja tabela z nagłówkami, a następnie kliknij OK.
• Excel:Mac 2011 – W zakładce Tabele na wstążce kliknij strzałkę obok polecenia Nowy, a następnie wybierz opcję Wstaw tabelę z nagłówkami.
• Excel 2003 i wcześniejsze – Wybierz Dane, Lista, a następnie Utwórz listę.

Rysunek 1: Wprowadź kilka pozycji w drugim arkuszu roboczym skoroszytu.

Zaletą tabel (i list w programie Excel 2003 i wcześniejszych) jest to, że jeśli dodasz pozycje na dole listy, tabela zostanie automatycznie rozszerzona o nowe pozycje. Nie możemy jednak używać tej samorozwijającej się tabeli lub listy bezpośrednio z walidacją danych. Aby to zrobić, musimy utworzyć nazwę zakresu, która obejmie wszystkie elementy oprócz pierwszego wiersza tabeli. Wybierz drugi do ostatniego wiersza swojego zakresu, a następnie:

• Excel 2007 i później – W zakładce Formuły wybierz opcję Zdefiniuj nazwę.
• Excel 2003 i wcześniejsze, lub Excel – Mac 2011: Wybierz Insert, Name, a następnie Define.

Gdy pojawi się okno dialogowe Define Name pokazane na Rysunku 2, wprowadź nazwę taką jak Fruit, upewnij się, że pole Refers to odnosi się do drugiego do ostatniego wiersza tabeli, a następnie kliknij OK.

Rysunek 2: Przydziel nazwę do drugiej, ostatniej komórki tabeli lub listy.

Spójrzmy najpierw na funkcję weryfikacji danych. Na pierwszym arkuszu skoroszytu, w którym utworzyłeś swoją listę, kliknij na komórkę A1 i wpisz słowo Owoce. Następnie wybierz komórkę A2 i wybierz Data, a następnie Data Validation. Pojawi się okno dialogowe na Rysunku 3. W zakładce Ustawienia wybierz opcję Lista w polu Pozwól. Gdy pojawi się pole Źródło, wpisz równy znak wraz z nazwą zakresu, który wcześniej przypisałeś, np. =Fruit.

Rysunek 3: Wybierz Lista, a następnie określ swoją nazwę zakresu jako Źródło w zakładce Ustawienia.

W zakładce Komunikat wejściowy należy wpisać tytuł, np. słowo Owoc, aby opisać pole wejściowe wraz z komunikatem, np. «Wybierz element z listy», jak pokazano na rysunku 4. Nie jest konieczne używanie cudzysłowów w polu Opis.

Rysunek 4: Chociaż opcjonalna, zakładka Komunikat wejściowy pozwala na udokumentowanie walidacji.

Na koniec, na karcie Alert błędów, wprowadź tytuł, taki jak «Nieważne wprowadzenie», oraz komunikat o błędzie, taki jak «Musisz dokonać wyboru z listy», jak pokazano na Rysunku 5. Zachowaj styl ustawiony jako Stop, aby uniemożliwić użytkownikowi ominięcie elementów listy. Kliknij OK, aby zamknąć okno dialogowe Walidacja danych.

Rysunek 5: Upewnij się, że wypełniłeś zakładkę Error Alert; w przeciwnym razie, nieprawidłowe wejścia wyzwolą ogólny i kryptyczny komunikat o błędzie.

:

Idąc dalej, po kliknięciu w komórce A2, powinna pojawić się notatka, jak pokazano na Rysunku 6. Dokumentuje ona arkusz kalkulacyjny. Jeśli użytkownik dokona słusznego wyboru, będzie mógł przejść do innej komórki; w przeciwnym razie podpowiedź pokazana na Rysunku 7 zatrzyma użytkownika w jego śladach.

Rysunek 6: Nasze wybory w zakładce Wprowadzanie dają pomocny komunikat, gdy użytkownik kliknie komórkę A2.

Rysunek 7: Użytkownik zobaczy tę zachętę, jeśli spróbuje wprowadzić coś, co nie pojawia się na liście.

Teraz wróć do swojej oryginalnej listy i dodaj jeszcze dwa elementy, jak pokazano na Rysunku 8. Po powrocie do komórki z listą weryfikacji danych, nowe pozycje powinny pojawić się automatycznie. Połączenie funkcji tabeli/listy i nazw zakresów automatyzuje ten aspekt. W oknie dialogowym Data Validation możesz podać konkretny zestaw współrzędnych komórki w polu Source, ale będziesz musiał ręcznie zmienić to ustawienie, jeśli później rozszerzysz listę. Co więcej, program Excel 2007 i wcześniejsze nie pozwalają na odwoływanie się do współrzędnych komórek w innych arkuszach. Najlepsze praktyki w projektowaniu arkuszy kalkulacyjnych wymagają oddzielenia list pomocniczych od rzeczywistych danych na osobnych arkuszach. Każdy, kto korzysta z programu Excel 2007 i nowszego, nie musi już zmagać się z problemem, gdzie bezpiecznie umieścić listy pomocnicze do walidacji danych na tym samym arkuszu, na którym znajduje się rzeczywisty wkład.

Rysunek 8: Ponieważ ostatecznym źródłem dla listy zatwierdzania jest tabela/lista, nowe pozycje pojawiają się automatycznie na liście zatwierdzania danych.