Powiązanie ze sobą 2 rozwijanych list
Autor: Piotr Dynia
Tworzę zamówienia towarów z wykorzystaniem danych znajdujących się w drugim arkuszu. Potrafię zastosować listę rozwijaną w komórce, aby wybierać z niej nazwy, zamiast wpisywać je ręcznie. Baza towarów jest bardzo obszerna, dlatego chciałbym przygotować dwie listy rozwijane. Z pierwszej wybierałbym kategorie, a w drugiej konkretny towar należący do tej grupy. W jaki sposób osiągnąć taki efekt?
Arkusz Zamówienie, do którego będą wprowadzane szczegóły zamówienia, wygląda jak na rysunku 1.
Rysunek 1. Arkusz zamówienia
Drugi arkusz noszący nazwę Kategorie zawiera bazę danych, w której towary zostały przyporządkowane do odpowiednich grup. Nazwa każdej kategorii znajduje się w nagłówku kolumny (rysunek 2).
Rysunek 2. Baza danych towarów pogrupowanych w kategorie
Aby na podstawie tak zgromadzonych danych utworzyć powiązane ze sobą listy rozwijane, proponujemy skorzystać z nazwanych zakresów.
W tym celu:
- W arkuszu Zamówienie na karcie Formuły, w grupie poleceń Nazwy zdefiniowane wybierzmy Menedżer nazw i naciśnijmy Nowy.
- W oknie dialogowym, w polu Nazwa wpiszmy: Kategorie, a w polu Odwołuje się do wprowadźmy formułę:
=PRZESUNIĘCIE(Kategorie!$A$1;;;;ILE.NIEPUSTYCH(Kategorie!$1:$1))
- Naciśnijmy OK, a potem ponownie przycisk Nowy.
Rysunek 3. Dodawanie nazwanego zakresu komórek
Jeśli po prawej stronie arkusza z bazą danych towarów dodamy kolejną kategorię, to zostanie również zawarta w tym nazwanym zakresie komórek. Formuła sprawdza ilość wypełnionych komórek i za pomocą funkcji PRZESUNIĘCIE dopasowuje liczbę komórek w tym zakresie. Pamiętajmy, aby w 1. wierszu nie stosować pustych komórek w obrębie zestawienia.
Podobnie jak w 2. kroku dodajmy drugi zakres o nazwie: Towary i w polu Odwołuje się do przyporządkujmy mu następującą formułę:
=PRZESUNIĘCIE(Kategorie!$A$1;1;PODAJMY.POZYCJĘ(Zamówienie!$B5; Kategorie!$1:$1;0)-1; ILE.NIEPUSTYCH(PRZESUNIĘCIE(Kategorie!$A:$A;; PODAJMY.POZYCJĘ(Zamówienie!$B5; Kategorie!$1:$1;0)-1))-1)
Jej wynikiem jest lista towarów z kategorii wpisanej w komórce B5. Lista ta podobnie jak poprzednia także automatycznie dopasowuje się do liczby wpisanych towarów w danej kategorii. Naciśnijmy przycisk Zamknij, aby zamknąć okno dialogowe do tworzenia nazwanych zakresów.
Przejdźmy teraz do utworzenie rozwijanych list:
- Mając komórkę B5 zaznaczoną, wybierzmy na karcie Dane, w grupie poleceń Narzędzia danych wybierzmy Poprawność danych. W oknie, które się pojawi, w polu Dozwolone wybierzmy Lista, a w polu Źródło wpiszmy:
=Kategorie
i naciśnijmy przycisk OK.
- Zaznaczmy komórkę C5 i podobnie jak w poprzednim kroku ustawmy listę rozwijaną w komórce za pomocą okna Sprawdzanie poprawności danych. W polu Źródło zastosujmy formułę:
=Towary
- Zaznaczmy komórki B5 i C5, a następnie na karcie Narzędzia główne, w grupie poleceń Schowek wybierzmy Kopiuj).
- Następnie na karcie Narzędzia główne, w grupie poleceń Schowek wybierzmy Wklej.
Rysunek 4. Ustawienie listy rozwijanej w komórce
Teraz już wypełnimy zamówienie za pomocą powiązanych list. W komórce kolumny B określa się odpowiednią kategorię, a następnie w tym samym wierszu w komórce kolumny C wskazuje nazwę towaru należącego do określonej wcześniej grupy. Listy rozwijane znajdujące się w kolumnach B i C są powiązane ze sobą w obrębie każdego wiersza.
Rysunek 5. Wypełnianie zamówienia za pomocą list rozwijanych
Zobacz także:
Tagi: excel
Zaloguj się, aby dodać komentarz
Nie masz konta? Zarejestruj się »
Opinie czytelników
~katoslaw
data: 2019-07-29 22:43:11Czy ta formuła na pewno jest poprawna? nie zwraca mi poprawnej wartości =PRZESUNIĘCIE(Kategorie!$A$1;1;PODAJMY.POZYCJĘ(Zamówienie!$B5; Kategorie!$1:$1;0)-1; ILE.NIEPUSTYCH(PRZESUNIĘCIE(Kategorie!$A:$A;; PODAJMY.POZYCJĘ(Zamówienie!$B5; Kategorie!$1:$1;0)-1))-1)
Ocena użytkownika:
Zgłoś naruszenie regulaminu
