Kontrola wprowadzanych danych
7 lip 2008
Tworząc arkusze, z których mają korzystać inne osoby, często zakładamy, że do pewnych komórek będą wprowadzane odpowiednie dane, które są dalej wykorzystywane do obliczeń. Ważne jest zatem, aby dane te były prawidłowe. W tym artykule przedstawię jedną z możliwości wykorzystania wbudowanych funkcji Excela do zagwarantowania, że w arkuszu znajdą się poprawne dane.
Jednym z najwygodniejszych sposobów zapewnienia, że użytkownik wprowadzi dane spośród pewnej dozwolonej grupy danych, jest wykorzystanie listy rozwijanej, która zawiera tylko przewidziane przez nas do wprowadzenia elementy. Korzystanie z tego typu list jest prostsze niż mogłoby się na pierwszy rzut oka wydawać.
Jak to zrobić
Na początek prosty przykład demonstrujący zasadę działania list rozwijanych. Załóżmy, że robimy arkusz, który będzie nam służył do generowania zamówień w pizzerii. W ofercie mamy kilka rodzajów pizzy i tworząc zamówienie użytkownik musi wybrać jedną z nich. Zaczynamy zatem od stworzenia gdzieś w arkuszu odpowiedniej listy:
Przy okazji posortowałem tę listę, bo jej elementy będą widoczne w takiej kolejności, w jakiej są wpisane w arkuszu (a ja chcę mieć je w liście w kolejności alfabetycznej). Po prawej stronie mam siedem pól i w każdym z nich będę mógł wpisać jeden rodzaj pizzy. Wpisać lub wybrać ją z listy rozwijanej – będziemy mieli obie możliwości. Zaznaczam teraz zakres E3:E9 i przechodzę na kartę Dane. Tam klikam na przycisk Poprawność danych w grupie Narzędzia danych:
Teraz jeszcze musimy odpowiednio ustawić opcje w okienku. Ważne jest, aby w polu Dozwolone ustawić Lista, na pewno zaznaczyć Rozwinięcia w komórce (to jest nasza lista rozwijana) i podać źródło dla danych, które znajdą się w tej liście. W naszym przypadku jest to dokładnie zakres zawierający rodzaje pizzy. Jeśli źródło będzie się znajdowało w innym arkuszu naszego skoroszytu (żeby nie było widoczne i nie przeszkadzało), to źródło oczywiście uzupełniamy o nazwę tego arkusza, a więc przykładowo =dane!$A$2:$A$10). Włączona opcja Ignoruj puste spowoduje, że Excel nie będzie zgłaszał błędu, jeśli komórki, do których będziemy wprowadzać dane, pozostaną puste.
Zatwierdzamy ustawienia i już możemy korzystać z list rozwijanych. Wystarczy postawić kursor w jednej z "zielonych" komórek, a obok pojawi się gotowy do kliknięcia symbol listy rozwijanej. Oczywiście cały czas możemy też ręcznie wpisywać rodzaj pizzy, ale i wtedy Excel będzie sprawdzał, czy został wpisany jeden z elementów zdefiniowanych w kolumnie A.
Dla nadgorliwych
Nasze listy rozwijane mamy już omówione i zmienimy nieco temat, bo aż korci, żeby nasz arkusz wzbogacić na przykład o ceny pizzy, które automatycznie będą się pojawiały z prawej strony zielonego bloku. W tym celu najpierw dodajmy w kolumnie B ceny dla każdej wersji pizzy. Żeby uzyskać ceny, skorzystam teraz z funkcji WYSZUKAJ.PIONOWO, którą dokładniej omawiałem w jednym z wcześniejszych wpisów . Do komórki F3 wpisujemy zatem następującą formułę =WYSZUKAJ.PIONOWO(E3;$A$2:$B$10;2;FAŁSZ) i po wyborze pizzy pojawia nam się obok jej cena. Jeśli teraz skopiujemy tę formułę w dół do pozostałych komórek, to niestety pokaże się w nich błąd #N/D!, bo do poszukiwań ceny brakuje jednego z argumentów (nazwy pizzy). Niby ten błąd nie przeszkadza, ale lepiej by było, gdyby go nie było (widać). Możemy go ukryć definiując odpowiedni warunek umieszczający pustą wartość w komórce z ceną, jeśli komórka z nazwą pizzy jest pusta. Dopiero kiedy nazwa zostanie podana, to po prawej powinna pojawić się cena. Wprowadzamy więc następującą formułę do komórki F3:
=JEŻELI(CZY.PUSTA(E3);"";WYSZUKAJ.PIONOWO(E3;$A$2:$B$10;2;FAŁSZ)).
Tą formułą sprawdzamy, czy komórka z nazwą pizzy jest pusta. Jeśli tak, to w komórce z ceną nie pojawia się nic (czyli wpisujemy dwa cudzysłowy obok siebie), a jeśli nie jest pusta, to wyszukujemy cenę dla danej pizzy. Teraz wszystko wygląda dobrze i działa poprawnie, a więc ze spokojnym sumieniem można zakończyć pracę. Nasz arkusz wygląda teraz tak:
