Lista rozwijana w programie Excel to bardzo użyteczne narzędzie, szczególnie przydatne jeżeli chcemy aby osoba uzupełniająca dane komórki trzymała się standardu i jednakowo wprowadzała dane. Dla użytkownika też jest to wygodne, gdy zamiast wpisywać długą nazwę może dwoma kliknięciami wybrać ją z listy. Jednak jak to w życiu bywa „co za dużo to niezdrowo” tak i w przypadku listy rozwijanej, jeśli zawiera ona zbyt dużo elementów korzystanie z niej staje uciążliwe. Wyszukiwanie odpowiedniej wartości z listy stu elementów zapewne zajmie więcej niż czasu niż jej ręczne wpisanie. Pół biedy jeśli wartości są w jakiś sensowny sposób posortowane, np. alfabetycznie. Stosowanie listy rozwijane jak na obrazku poniżej nie jest dobrym pomysłem.

Załóżmy, że osoba ma wprowadzić do naszego skoroszytu nazwę klienta, województwo oraz nazwę miasta w jakim znajduje się oddział naszej firmy, który będzie się kontaktował z klientem. Mamy tu dwie kolumny w których warto zastosować listy rozwijane. Jak widać na obrazku zrobienie listy dla wszystkich oddziałów spowoduje, że znalezienie odpowiedniej nazwy będzie czasochłonne a do tego użytkownik może się pomylić i wybrać miasto z drugiego końca Polski. Rozwiązaniem tego problemu jest lista oddziałów dedykowana dla wybranego województwa.

Przygotowanie słownika dla listy warunkowej w arkuszu Excel

Po pierwsze musimy przygotować nasz słownik, w którym ze zwykłej listy miast przechodzimy na listy miast w poszczególnych województwach.

Do wyszukania z której listy miast chcemy korzystać wykorzystamy funkcję PODAJ.POZYCJĘ, która wyszuka nazwę województwa w nagłówkach naszych list i określi jej względne położenie, czyli numer komórki w określonym zakresie. I tak dla województwa dolnośląskiego otrzymujemy wartość 1, a dla lubuskiego 4.

Ustawienie listy warunkowej w Excelu

Przechodzimy do wstawienia warunkowej listy rozwijanej. Najpierw zaznaczamy komórkę D3 i przechodzimy do karty Dane i polecenia Poprawność danych. Następnie wybieramy jako dozwolone opcję Lista i jako źródło wprowadzamy formułę

=PRZESUNIĘCIE(Słownik!$F$3;0;PODAJ.POZYCJĘ (C3;Słownik!$F$2:$U$2;0)-1;24)

Funkcja Przesunięcie zmienia odwołanie do zakresu, o określoną liczbę wierszy i kolumn względem określonej komórki, dodatkowo może zmieniać wymiary tego zakresu, czyli wynikiem tej formuły może być też zakres komórek, a nie tylko pojedyncza komórka. Jako pierwszy parametr podajemy pierwszą komórkę z miastem z pierwszej listy (Głogów) czyli komórkę F3, musimy ją usztywnić aby odwołanie nie zmieniało się przy przeciąganiu komórki z wybieraną listą na kolejne wiersze. Drugi parametr funkcji to liczba wierszy o jaką chcemy przesunąć nasze odwołanie. W naszym przypadku jest to 0 ponieważ już jesteśmy na pierwszym wierszu zakresu miast dla województwa. Trzeci parametr to liczba kolumn o jaką chcemy przesunąć nasze odwołanie. Tu wykorzystujemy wspomnianą funkcję PODAJ.POZYCJĘ. Ważne aby nie usztywniać odwołania do komórki C3, ponieważ chcemy aby wynik tej formuły był zależny od wybranego województwa w poszczególnych wierszach.

Dla dla województwa lubuskiego wartość formuły to 4, ale nie chcemy przesunąć się o 4 kolumny ale o 3, stąd wynik formuły pomniejszamy o 1. Nie musielibyśmy tego gdybyśmy w formule PRZESUNIĘCIE startowali od komórki E3 ale wtedy ryzykujemy, że gdy usuniemy tę komórkę lub całą kolumnę to zepsują się nasze listy wyboru. Czwarty parametr określa z ilu wierszy składa się nasz wynikowy zakres. W tym przykładzie województwo śląskie ma 24 miasta więc tyle chcemy na liście. Piąty parametr dotyczy ilości kolumn w zakresie, ale tu działamy na jednej kolumnie więc możemy ten parametr pominąć. W ten sposób uzyskujemy zakres wartości listy rozwijanej zależny do wybranego województwa.

Pozbycie się pustych pól na liście rozwijanej zależnej

Tak zbudowana lista rozwijana w programie Excel ma jeszcze jedną wadę, zawsze pokazuje 24 pozycje na liście, z czego np. dla województwa lubuskiego 22 będą puste, aby zobaczyć miasta trzeba przewinąć listę do góry, a do tego jeżeli wprowadzimy na którejś liście więcej niż 24 miasta to ostatnie miasta nie będą widoczne. Nie pomaga też tutaj zaznaczona opcja Ignoruj puste. Aby określić jak długa ma być nasza lista użyjemy formuły ILE.NIEPUSTYCH, która podaje liczbę niepustych komórek w określonym zakresie.

Wchodzimy ponownie do edycji poprawności danych i wprowadzamy funkcję

=PRZESUNIĘCIE(Słownik!$F$3;0;PODAJ.POZYCJĘ (C3;Słownik!$F$2:$U$2;0)- 1;ILE.NIEPUSTYCH (PRZESUNIĘCIE(Słownik!$F$3;0;PODAJ.POZYCJĘ (C3;Słownik!$F$2:$U$2;0)-1;200)))

Tak naprawdę dla funkcji ILE.NIEPUSTYCH podajemy nasz wcześniejszy określony zakres ale z dużym zapasem, np. na 200 wierszy. W ten sposób dostajemy listę miast o długości liczby tych miast, bez pustych pól.

Jeżeli chcesz aby na liście było dostępne też puste pole musisz formułę delikatnie zmodyfikować tj.

=PRZESUNIĘCIE(Słownik!$F$3;0;PODAJ.POZYCJĘ (C3;Słownik!$F$2:$U$2;0)- 1;ILE.NIEPUSTYCH (PRZESUNIĘCIE(Słownik!$F$3;0;PODAJ.POZYCJĘ (C3;Słownik!$F$2:$U$2;0)-1;200))+1) a otrzymasz poniższy efekt. W ten sposób wybieranie wartości z list rozwijanych będzie o wiele przyjemniejsze.

Lista rozwijana Excel w wersji warunkowej – pobierz gotowca!

Jak widać warunkowa lista rozwijana w programie Excel nie jest tak trudna do przygotowania jak mogłoby się na początku wydawać. Jeśli nie chcesz budować jej od zera, to skorzystać z gotowego rozwiązania w pliku w poniższym linku Pobierz plik Excel

Jeśli chcesz dowiedzieć się więcej na temat komunikatów wyświetlanych przy wprowadzaniu danych to cenne informacje możesz znaleźć na stronie Microsoft LINK