Opublikowano

Listy zależne w Excelu – prosty sposób

W Excelu pomocą narzędzia Sprawdzanie Poprawności można w prosty sposób wstawić do komórki listę rozwijaną, z której użytkownik może wybierać interesujące go pozycje. Co jednak jeżeli elementy na liście są uzależnione od wartości wpisanej w innej komórce. Na przykład przy uzupełnianiu formularza zamówienia z pierwszej listy wybieram kategorię produktową, a z drugiej listy wybieram konkrety produkt. W zależności od wybranej kategorii z pierwszej listy, na drugiej liście prezetowane są tylko produkty wchodzące w skład wybranej kategorii.

W internecie można zaleźć wiele sposobów na tworzenie list zależnych. Niektóre rozwiązania sugerują wykorzystanie w tym celu formuł index i przesunięcie, inne sugerują wykorzystanie makr.
W tym artykule została opisana chyba najprostsza metoda bazująca na nazwach zakresów i formule WARUNKI (użytkownicy starszych wersji Excela zamiast formuły WARUNKI mogą ten sam efekt osiągnąć zagnieżdżając formułę JEŻELI).

Krok pierwszy

W omawianym przykładzie wykorzystane zostaną dwie listy rozwijane. Lista główna zawiera nazwy kategorii produktowej oraz listy zależne zawierające nazwy poszczególnych produktów. Przykładowe listy prezentuje poniższy rysunek.

W arkuszu Formularz do komórki B3 należy wstawić listę pozwalającą na wybór kategorii produktowej. W tym celu na wstążce programu przejdź do karty Dane, a następnie kliknij ikonkę Poprawności danych.

Na ekranie pojawi się okienko Sprawdzania poprawności danych. W okienku tym z pośród wszystkich dozwolonych opcji wybierz Listę, a następnie w polu źródło zaznacz zakres komórek zawierających nazwy kategorii produktowych, tak jak pokazano to na poniższym rysunku.

Po kliknięciu przycisku Ok, w komórce pojawi się rozwijana lista, z której można wybrać nazwę kategorii produktowej.

Krok druginazwanie zakresów

Kolejnym krokiem jest nadanie nazw zakresom zawierającym listy produktów. W tym celu należy zaznaczyć listę wraz z jej tytułem, a następnie na wstążce w karcie Formuły kliknąć ikonkę Utwórz z zaznaczenia. Na ekranie pojawi się okienko Tworzenia nazwy z zaznaczenia. Jeżeli tytuł kolumny znajduje się w pierwszej komórce zaznaczonego zakresu, to upewnij się, że zaznaczona jest opcja Górny wiersz. Zatwierdź wybór klikając przycisk Ok.

Od tej chwili zaznaczonemu zakresowi komórek została przyporządkowana nazwa nabiał. Po zaznaczeniu komórek od C2 do C36 nazwa ta będzie widoczna w polu nazwy nad arkuszem.

Te same kroki powtarzamy w celu nadania nazw zakresom pozostałym listą z nazwami produktów.
Nazwy można później sprawdzić i edytować w okienku menadżera nazw.

Krok trzeci – formuła

Następnym etapem w tworzeniu listy zależnej jest napisanie formuły działającej na następującej zasadzie: jeżeli w komórce B3 wybrana została kategoria nabiał, wtedy wyświetl listę produktów wchodzących w skład tej kategorii, jeżeli natomiast została wybrana pieczywo, wtedy wyświetl listę produktów wchodzącą w skald tej kategorii i tak dalej aż w formule zostaną uwzględnione wszystkie kategorie produktowe i listy.
Użytkownicy Excela 365 i Excela 2019 mogą wykorzystać formułę WARUNKI (IFS), lub tak jak użytkownicy wcześniejszych wersji programu Excel mogą zagnieździć odpowiednią ilość razy formułę JEŻELI (IF).

Ustaw się w dowolnej pustej komórce w arkuszu i wprowadź do niej formułę. Zapis przykładowych formuł podano poniżej.

Formuła WARUNKI

=WARUNKI($B$3=”nabiał”;nabiał;$B$3=”pieczywo”;pieczywo;$B$3=”makaron”;makaron;$B$3=”produkty zbożowe”;produkty_zbożowe;$B$3=”przetwory”;przetwory;$B$3=”sosy i pasty”;sosy_i_pasty;$B$3=”zboża”;zboża;$B$3=”zioła i przyprawy”;zioła_i_przyprawy)

Formuła JEŻELI

=JEŻELI($B$3=”nabiał”;nabiał;JEŻELI($B$3=”pieczywo”;pieczywo;JEŻELI($B$3=”makaron”;makaron;JEŻELI($B$3=”produkty zbożowe”;produkty_zbożowe;JEŻELI($B$3=”przetwory”;przetwory;JEŻELI($B$3=”sosy i pasty”;sosy_i_pasty;JEŻELI($B$3=”zboża”;zboża;zioła_i_przyprawy)))))))

Krok czwarty – wstawienie listy zależnej

Skopiuj z paska formuły wpisaną wcześniej funkcję. Kliknij na komórkę C3 lub na inną komórkę, do której chcesz wstawić listę zależną.
Na wstążce programu przejdź do karty Dane i kliknij ikonkę Poprawność Danych.

W okienku sprawdzania poprawności danych z dozwolonych opcji wybierz listę, a następnie do pola Źródło wklej skopiowaną wcześniej formułę, pamiętaj, żeby formuła zaczynała się od znaku równości. Zatwierdź zmiany klikając przycisk OK.

Uwaga
Pole Źródło ma ograniczenie co do ilości wpisywanych znaków. Ograniczenie to wynosi 255 znaków.

Od tej pory po wybraniu w komórce B3 nazwy kategorii produktowej, w komórce C3 będzie wyświetlała się lista z nazwami produktów należącymi do wybranej kategorii.