TOP

Połączone listy rozwijane

YouLibreCalc for Excel logo

Opis

W Excel możesz to zrobić szybko i łatwo utwórz listę rozwijaną , ale czy kiedykolwiek próbowałeś utworzyć zależną listę rozwijaną? Istnieje kilka sposobów na utworzenie takiej listy, więc przyjrzyjmy się im.


Metoda 1. Funkcja INDIRECT

Technika ta opiera się na zastosowaniu funkcji INDIRECT, która może wykonać jedną prostą rzecz - przekonwertować zawartość dowolnej określonej komórki na adres zakresu zrozumiały dla Excel. Oznacza to, że jeśli komórka zawiera tekst „ А1 ", wówczas funkcja w rezultacie poda odwołanie do komórki А1 . Jeśli komórka zawiera słowo „ Auto ", wówczas funkcja wyświetli odwołanie do nazwanego zakresu z nazwą Auto itp.

Weźmy na przykład tę listę modeli samochodów Toyota, Ford I Nissan :

Wybierz całą listę modeli Toyoty (z komórki А2 i w dół do końca listy) i nadaj temu zakresowi nazwę Toyota w menu Wstaw - Nazwa - Przypisz (Insert - Name - Define) . Następnie powtórzymy to samo z listami Ford I Nissan , podając odpowiednio nazwy zakresów Ford I Nissan .

Przypisując nazwy należy pamiętać, że nazwy zakresów w Excel nie mogą zawierać spacji, znaków interpunkcyjnych i muszą zaczynać się od litery. Dlatego jeśli w jednej z marek samochodów pojawiła się luka (np Ssang Yong), wówczas należałoby go zastąpić w komórce i nazwie zakresu znakiem podkreślenia (tj Ssang_Yong).

Stwórzmy teraz pierwszą listę rozwijaną służącą do wyboru marki samochodu. Wybierz pustą komórkę i otwórz menu Dane - sprawdź (Data - Validation) , a następnie z listy rozwijanej Typ danych Wybierz opcję Lista i w terenie Źródło - zaznacz komórki z nazwami marek (w naszym przykładzie żółte komórki). Po kliknięciu ОК pierwsza lista rozwijana jest gotowa:

Stwórzmy teraz drugą listę rozwijaną, która wyświetli modele marki wybranej na pierwszej liście. Podobnie jak w poprzednim przypadku wybierz pustą komórkę i otwórz menu Dane - sprawdź - dalej Lista . Na polu Źródło będziesz musiał wprowadzić następującą formułę:

=INDIRECT(F3)

Gdzie:

Wszystko. Po kliknięciu OK zawartość drugiej listy zostanie wybrana nazwą zakresu wybranego na pierwszej liście.

Wady tej metody:

Metoda 2. Lista dopasowań (OFFSET i MATCH)

Ta metoda wymaga posortowanej listy dopasowań typu make-model następującego typu:

Aby utworzyć podstawową listę rozwijaną marek, możesz skorzystać ze zwykłej metody opisanej powyżej, czyli:

Ale w przypadku zależnej listy modeli będziesz musiał utworzyć nazwany zakres za pomocą funkcji OFFSET, która będzie dynamicznie odnosić się tylko do komórek modeli określonej marki. Dla tego:

=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$GG$7) ; 1 )

Odniesienia muszą być bezwzględne (ze znakami $ ). Po naciśnięciu Enter nazwy arkuszy zostaną automatycznie dodane do formuły.

Funkcja OFFSET jest w stanie wystawić referencję do zakresu o żądanej wielkości, przesuniętego względem komórki początkowej o podaną liczbę wierszy i kolumn. W bardziej zrozumiałej wersji składnia tej funkcji jest następująca:

=OFFSET( połęka_początkowa ; Spływać ; przesuniecie_w prawo ; zakres_rozmiar_w_wierszach ; zakres_rozmiar_w_kolumnach )

Więc:

Wynik powinien być mniej więcej taki:

Pozostaje dodać do komórki listę rozwijaną na podstawie utworzonej formuły G8 . Dla tego:

Artykuły na ten temat: