Ten odcinek poświęcimy uproszczeniu interfejsu odbiorcy naszych danych tak aby mógł wybierać szukane przez siebie wartości za pomocą kolejnych rozwijanych menu których zawartość zależy od dokonanego wcześniej wyboru. Jako przykład posłuży nam lista produktów marketu budowlanego zebrana w tabeli poniżej.
Jak widać kolumna „Obszar” zawiera tylko 2 różne wartości , kolumna „Grupa” ma ich 4, pozycja „Rodzaj” 11 a pozycja „PN” aż 27. Chcielibyśmy ograniczyć użytkownikowi wybór tych 27 pozycji za pomocą kolejnych rozwijanych menu zgodnych z Obszarami, Grupami i Rodzajami naszego asortymentu.
Oczywiście w przypadku 27 elementów wystarczyłoby jedno menu oparte na bazie kolumny Rodzaj ale wyobraźmy sobie; że mamy tych elementów tysiące jak to ma miejsce w wiodących na rynku marketach budowlanych.
Do tworzenia naszego menu użyjemy funkcji Poprawność danych która umożliwia ograniczenie wprowadzanych do komórki wartości. Jednak bezpośrednie jej użycie z wykorzystaniem kolumn z naszej tabeli nie da nam zadawalającego efektu gdyż nie chcemy wybierać z ogromnej listy powtarzających się elementów .
My chcielibyśmy w Menu 1 dostać tylko 2 wartości w kolejnym też tylko 2 wartości (zależne od wyboru menu 1) w kolejnym menu znowu ilość zależną od wyboru Menu 2 i tak dalej.
Jak to osiągnąć ?
Wybierzmy sobie dwa obszary, pierwszy w którym zlokalizujemy nasze Menu 1,2,3 i 4 poziomu oraz drugi roboczy w którym zdefiniujemy wartości które będą się pojawiać w naszych menu. W moim przypadku dla prostoty prezentacji znajdują się one obok siebie ale obszar techniczny należy oczywiście ukryć poza obszarem widoczności odbiorcy danych.
Rozpocznijmy od Menu 1. W tym przypadku potrzebujemy tylko uzyskać unikatowe wartości z kolumny „Obszar” naszej tabeli. W związku z tym w komórce D2 poniżej „Menu Dane 1” wpiszemy funkcję =Unikatowe( zaznaczmy w naszej tabeli kolumnę „Obszar” i zamkniemy nawias funkcji ).
W moim przypadku komórka D2 ma następującą składnię =UNIKATOWE(Tabela002[Obszar])
Co daje następujący efekt:
Nadajmy nazwę komórce D2 np. Menu01
A teraz ograniczmy wartości w komórce B1 (naszym Menu pierwszego poziomu) do wartości z listy którą nazwaliśmy Menu01. W tym celu stajemy na komórce B1 przechodzimy do Menu Dane, sekcja Narzędzia danych i wybieramy ikonę Poprawność Danych
A następnie w menu wybieramy w polu „Dozwolone” opcję „Lista” w polu „Źródło” wskazujemy nadaną komórce D2 nazwę =Menu01# zgodnie z poniższym obrazkiem
Uzyskujemy rozwijane menu pierwszego poziomu adekwatne do naszej listy z danymi
Teraz trudniejszy element bo Menu 2 musi być zależne od Menu 1 czyli inne dla „Elektronarzędzia” i inne dla „Instalacja elektryczna”.
W komórce E2 wpiszemy funkcję =Unikatowe( Jeżeli( wskażemy komórkę z Menu wyboru 1 którą przed chwilą udało nam się zdefiniować = zaznaczmy w naszej tabeli kolumnę „Obszar”; zaznaczymy w naszej tabeli kolumnę „Grupa” ;X.Wyszukaj ( i analogicznie jak poprzednio wskażemy komórkę z Menu wyboru 1 ; zaznaczmy w naszej tabeli kolumnę „Obszar”; zaznaczymy w naszej tabeli kolumnę „Grupa”) ) )
Co sumarycznie da nam następującą funkcję
=UNIKATOWE(JEŻELI(B1=Tabela002[Obszar];Tabela002[Grupa];X.WYSZUKAJ(B1;Tabela002[Obszar];Tabela002[Grupa])))
A w efekcie uzyskamy kolejny obszar unikatowych pozycji dla Menu 2 które będą zależne od wybranej przez nas w Menu 1 pozycji
Teraz analogicznie jak dla Menu 1.
Nazywamy sobie pole E2 jako Menu02
A następnie wybieramy pole B4 które będzie naszym drugim wybieralnym menu i ograniczamy mu możliwość wpisywania danych do naszej listy poprzez „Poprawność danych”
Oraz uzupełnienie jako „Źródło” =Menu02#
I otrzymamy nasze Menu 2 gotowe do działania
Gdy znamy już metodę analogicznie postępujemy dla kolejnych menu jako punkt odniesienia zawsze wskazując menu jeden poziom niżej oraz biorąc kolejne kolumny tabeli czyli :
w komórce F2 wpisujemy
=UNIKATOWE(JEŻELI(B4=Tabela002[Grupa];Tabela002[Rodzaj];X.WYSZUKAJ(B4;Tabela002[Grupa];Tabela002[Rodzaj])))
Nazywamy ją Menu03 i w komórce B7 reprezentującej Menu 3 ograniczamy wpisywane dane do =Menu03# uzyskując działające Menu 3
I dla ostatniego menu w komórce G2 wpisujemy
=UNIKATOWE(JEŻELI(B7=Tabela002[Rodzaj];Tabela002[PN];X.WYSZUKAJ(B7;Tabela002[Rodzaj];Tabela002[PN])))
Nazywamy ją Menu04 i w komórce B10 reprezentującej Menu 4 ograniczamy wpisywane dane do =Menu04#
uzyskując następujący efekt
Udało się ! Na podstawie danych z tabeli stworzyliśmy wielopoziomowe kontekstowe rozwijane menu. Zawartość każdego kolejnego menu zależy od wybranych wcześniej wartości
Jeżeli uważacie powyższy opis za przydatny i chcielibyście podyskutować o szczegółach lub macie inne potrzeby w zakresie zaawansowanych funkcji Excela bądź chcielibyście po prostu za jego pomocą uprościć sobie życie zapraszam do kontaktów na adres konrad.pogodz@itch.pl .
Dzięki darmowym materiałom szkoleniowym będziesz mógł przećwiczyć nabytą wiedzę i sprawdzić ją w praktyce. Pobierz i ćwicz natychmiast.
