Jak w Excelu zrobić hierarchiczne listy wyboru?

Excel Akademia Logo

Hierarchiczne listy wyboru czyli zależne listy wyboru z wykorzystaniem sprawdzania poprawności danych w Excelu

W tym odcinku przedstawię jak zrobić w Excelu hierachiczne listy wyboru (listy zależne).

Załóżmy, że mamy do przygotowania w Excelu formularz, który będą dla nas uzupełniać inne osoby. Może to być np. obszerne badanie ankietowe, może to być formularz rejestracji zamówień lub cokolwiek innego.

Pracę mogą usprawnić hierarchiczne listy wyboru (listy zależne) ponieważ danych, które zostaną zebrane będzie dość dużo i mają nam potem posłużyć do przeprowadzenia analizy. Zależy nam na tym, żeby dane można było w dość łatwy sposób podsumować. Dlatego ważne jest dla nas, aby uniknąć sytuacji, w której w przykładzie z ankietą każda odpowiedź jest inna, albo w formularzu zamówień mamy wpisane raz skarpety, innym razem skarpetki i jeszcze różne inne kombinacje z błędami i literówkami.

Co można zrobić? Można ograniczyć użytkownikom wybór w formularzu do wartości z listy nie dopuszczając do wpisywania wartości ręcznie („z palca”) poprzez wprowadzenie hierarchicznych list wyboru (list zależnych).

W Excelu przychodzi nam z pomocą narzędzie Poprawność Danych, dostępne w Narzędziach danych na karcie Dane na wstążce Excela. Poniższa ilustracja ułatwi Ci zlokalizowanie narzędzia na wstążce.

Excel narzędzie poprawność danych

 

Przygotowanie prostej rozwijanej listy wyboru omówiłem w jednym poprzednich odcinków do którego możesz przejść klikając ten link.

W tym odcinku zajmiemy się przygotowaniem zależnych list wyboru tzn. list powiązanych hierarchicznie ze sobą w taki sposób, że wybór opcji na liście pierwszej określa możliwości wyboru dostępne na liście drugiej, która określa opcje dostępne na liście trzeciej itd. Jako przykłady wykorzystania takich zależnych list wyboru mogą posłużyć: systematyka organizmów (podział na królestwa, typy, gromady, rzędy i gatunki); typy i podtypy produktów; podział muzyki na gatunki, wykonawców, albumy i piosenki itp.

Hierachiczne listy wyboru - pierwszy krok - określenie  struktury opcji dostępnych do wyboru 

Aby przygotować hierarchiczne listy wyboru w Excelu w pierwszej kolejności należy wypisać jakie opcje mają być dostępne na pierwszej liście najwyższego poziomu. Następnie dla każdej z nich należy wypisać opcje do wyboru na liście drugiej (nad każdym spisem wpisując nazwę odpowiednią dla wyboru z listy wyższego rzędu). Tak samo należy postąpić na każdym kolejnym poziomie szczegółowości. Dla przejrzystości rozwiązania każdy ze spisów możliwości do wyboru warto umieścić w osobnej kolumnie.

W moim przykładzie zależnych list wyboru widocznym na poniższej ilustracji na pierwszej liście wybieramy kontynent, na drugiej liście mamy do wyboru państwo spośród leżących na danym kontynencie, na liście trzeciej możemy wybrać miasto leżące w danym państwie. Spis kontynentów zamieściłem w komórkach B14:B16, obok zamieściłem spisy państw dostępnych dla poszczególnych kontynentów, potem tak samo postąpiłem z miastami.

Ten etap jest najbardziej pracochłonny - jeśli określimy i rozpiszemy już strukturę możliwych wyborów na zależnych listach to przynajmniej połowę pracy mamy za sobą.

zależne_listy_wyboru

Hierachiczne listy wyboru - drugi krok – nadanie nazw zakresom ze spisanymi opcjami

W celu nadania zakresom, w których przechowujemy spisy opcji na nasze listy wyboru najwygodniej jest posłużyć się poleceniem Utwórz z zaznaczenia (Ctrl+Shift+F3) dostępnym na karcie Formuły. Żeby polecenie sprawnie zadziałało potrzebujemy, aby nad naszymi spisami były podane ich nazwy. Dla czytelności przykładu na wcześniejszej ilustracji nazwy poszczególnych spisów wyróżniłem pogrubioną czcionką

utwórz_z_zaznaczenia

 

Hierachiczne listy wyboru - trzeci i ostatni krok - zdefiniowanie reguły poprawności danych w oparciu o funkcję ADR.POŚR

Ostatnim już krokiem opracowania hierarchicznych list wyboru jest przypisanie reguł sprawdzania poprawności danych poszczególnym zakresom w naszej tabeli. Najpierw oznaczamy zakres, w którym wprowadzane będą nazwy kontynentów - w moim przykładzie będą to komórki B3:B10. Następnie klikamy na narzędzie poprawność danych i w oknie dialogowym Sprawdzanie Poprawności Danych jako  dozwolone wartości wybieramy/wpisujemy Lista. W polu źródło wpisujemy znak równości i nazwę zakresu, w którym przechowujemy listę opcji wyboru na liście najwyższego rzędu. W moim przykładzie będą dopuszczone trzy wartości przechowywane w zakresie B14:B16, który nazwałem Kontynenty, w związku z tym prawidłowy wpis powinien wyglądać tak jak na poniższej ilustracji.

poprawność lista kontynentów

Następnie wprowadzamy regułę poprawności danych dla zakresu C3:C10, w którym będą wpisywane państwa. Zaznaczamy zakres C3:C10 i wybieramy poprawność danych i wprowadzamy ograniczenie dopuszczalnych wartości do wartości z listy. Tym razem jednak jako źródło wpisujemy znak równości i funkcję ADR.POŚR jako argument funkcji wpisujemy odwołanie względne do komórki B3.

poprawność_danych - listy zależne

Ponieważ jest to odwołanie względne to dla komórki C4 reguła poprawności będzie się odnosić do wartości z B4 i odpowiednio dla pozostałych wierszy zakresu.

Ponadto dzieki temu poprzez kopiuj i wklej specjalnie regułę sprawdzania poprawności możemy przekopiować do kolumny z miastami.

Jak działają hierachiczne listy wyboru w Excelu oparte o sprawdzenia poprawności?

Działanie całego rozwiązania hierachicznych list wyboru opiera się na funkcji ADR.POŚR (adresowanie pośrednie) oraz nazwach nadanych zakresom komórek. Dzięki temu wartość wpisana w kolumnie wyższego rzędu (nazwa kontynentu) określa z której listy (spisu państw na danym kontynencie) mają być zaczerpnięte dopuszczalne wartości dla listy rzędu niższego (kolumny z państwami).

Klikając ten link możecie pobrać plik z przykładem opisanym powyżej.

Share and Enjoy

  • Facebook
  • LinkedIn
  • Add to favorites
  • Email
  • RSS

Dodaj komentarz