TOP
Połączone listy rozwijane
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:
- F3 - adres komórki z pierwszą listą rozwijaną - zastąp ją swoim.
Wszystko. Po kliknięciu OK zawartość drugiej listy zostanie wybrana nazwą zakresu wybranego na pierwszej liście.
Wady tej metody:
- Zakresy dynamiczne określone formułami typu OFFSET nie mogą pełnić roli zakresów wtórnych (zależnych). Można ich używać dla listy podstawowej (niezależnej), ale lista dodatkowa musi być zdefiniowana sztywno, bez formuł.
- Nazwy zakresów dodatkowych muszą odpowiadać elementom głównej listy rozwijanej. Oznacza to, że jeśli zawiera tekst ze lukami, należy je zastąpić podkreśleniami itp.
- Musisz ręcznie utworzyć wiele nazwanych zakresów.
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:
- Podaj nazwę zakresu D1:D3 (np Marki )
- Wybierz na karcie Dane (Data) zespół Weryfikacja danych (Data validation)
- Wybierz opcję zaznaczenia z listy rozwijanej Lista (List) i określ jako jakość Źródła (Source) = Marki lub po prostu zaznacz komórki D1:D3 (jeśli znajdują się na tym samym arkuszu co lista).
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:
- Naciśnij Ctrl + F3 lub użyj przycisku Menedżer nazw (Name manager) na karcie Formuły (Formulas) . W wersjach sprzed 2003 roku było to polecenie menu Wstaw - Nazwa - Przypisz (Insert - Name - Define)
- Utwórz nowy nazwany zakres o dowolnej nazwie (np Modele ) i w terenie Połączyć (Reference) w dolnej części okna wprowadź ręcznie następującą formułę:
=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:
- połęka_początkowa - bierzemy pierwszą komórkę naszej listy, tj A1 ;
- Spływać - liczymy funkcję MATCH, która najprościej mówiąc wypisuje numer seryjny ogniwa o wybranej marce (G7) w zadanym zakresie (kolumny I );
- przesuniecie_w prawo =1 , dlatego chcemy odnieść się do modeli z sąsiedniej kolumny ( W );
- zakres_rozmiar_w_wierszach - obliczamy za pomocą funkcji COUNTIF, która jest w stanie policzyć ilość wystąpień na liście (kolumna A) potrzebnych nam wartości - marki samochodów ( G7 );
- zakres_rozmiar_w_kolumnach =1 , więc potrzebujemy jednej kolumny z modelami.
Wynik powinien być mniej więcej taki:
Pozostaje dodać do komórki listę rozwijaną na podstawie utworzonej formuły G8 . Dla tego:
- Wybierz komórkę G8
- Wybierz na karcie Dane (Data) zespół Weryfikacja danych (Data validation) lub w menu Dane - sprawdź (Data - Validation)
- Z listy rozwijanej wybierz opcję zaznaczenia Lista (List) i wpisz jako Źródła (Source) znak jest równy nazwie naszego asortymentu, tj = Modele .
Artykuły na ten temat: