Jak tworzyć wiele zależnych list rozwijanych w programie Excel?

Możesz wiedzieć, jak utworzyć menu rozwijane w programie Excel, ale możesz nie wiedzieć, jak utworzyć wiele zależnych menu rozwijanych.

Istnieją różne podejścia do tworzenia wielu menu rozwijanych, z których niektóre są łatwe do wykonania, a inne trudne. W tym artykule wyjaśniono, jak zrobić to stosunkowo szybko za pomocą jednej formuły przesunięcia.

Przykład tworzenia wielu zależnych list rozwijanych

Rzućmy okiem na poniższe dane, dla których zamierzasz utworzyć zależną listę rozwijaną za pomocą formuły przesunięcia.

Tutaj możesz zobaczyć trzy różne ligi, każda ze swoją listą drużyn. Aby uprościć koncepcję, każda liga może składać się tylko z niewielkiej liczby drużyn, zamiast przedstawiać pełną listę.

Po lewej stronie masz dwie możliwości wyboru ligi i odpowiadającej jej drużyny. Twój wybór drużyny będzie zależał od wybranej ligi, ponieważ wybór drugi zależy od pierwszego wyboru.

jak dodawać słowa na tiktok

Naszym celem jest stworzenie prostego menu rozwijanego z nazwami lig oraz zależnego menu rozwijanego dla każdej listy lig.

1. Przejdź do Zakładka Dane i kliknij Walidacji danych .

2. Wybierz Lista w Zezwól opcja w kryteriach walidacji.

3. Wybierz komórki E4 do G4 jako źródło.

Cztery. Kliknij ok zastosować zmiany.

W trzech prostych krokach możesz utworzyć prostą listę rozwijaną. Następnie kopiuj i wklej formułę do pozostałych komórek w wierszu.

Związane z: Jak utworzyć listę rozwijaną w programie Microsoft Excel

Tworzenie zależnej listy rozwijanej w programie Excel

Menu rozwijane drużyny piłkarskiej opiera się na prostej liście rozwijanej, którą właśnie utworzyłeś. Jeśli wybierzesz konkretną ligę, powinieneś mieć możliwość wybrania drużyny z menu rozwijanego Liga piłkarska zawierającego tylko drużyny z tej ligi.

Używanie wzoru przesunięcia do tworzenia listy rozwijanej

Utwórzmy formułę, aby upewnić się, że działa idealnie, zanim bezpośrednio wstawimy ją do pola sprawdzania poprawności danych. Następnie możesz przystąpić do wdrożenia go w całym zbiorze danych.

Przyjrzyjmy się składni funkcji Offset.

Funkcja offset ma pięć argumentów. Omówmy je krótko tutaj:

1. Odniesienie: Odnosi się do punktu wyjścia danych. Funkcja Offset podaje zakres, który jest bliski punktu odniesienia. Dlatego punkt odniesienia musi znajdować się blisko zbioru danych.

2. Wydziwianie: Argument wiersze odnosi się do liczby wierszy, które chcesz przenieść w dół od punktu odniesienia.

3. Kolumna: Podobnie jak wiersze, ten argument opisuje liczbę miejsc, które chcesz przenieść w kolumnach zestawu danych. Ponieważ pozycja kolumny zależy od ligi piłkarskiej zawartej w naszym prostym menu rozwijanym, może być konieczne użycie funkcji match jako argumentu kolumny.

4. Wysokość i szerokość: Te dwa argumenty odnoszą się do pozycji komórek, w których aktualnie się znajdujesz, na podstawie wybranych wcześniej argumentów wierszy i kolumn. Musisz to policzyć ręcznie, więc zachowaj ostrożność podczas dodawania wartości. Sprawdź również dwukrotnie, aby potwierdzić.

Zaimplementujmy funkcję offset w tym zbiorze danych, aby lepiej zrozumieć tę koncepcję.

Implementacja funkcji przesunięcia

W tym przypadku komórka E4 jest odwołaniem, ponieważ jest punktem początkowym zestawu danych. Plan jest również taki, aby skopiować tę samą formułę do innych komórek w wierszu, aby można było uczynić ją bezwzględnym odwołaniem do komórki, dodając $ znak.

Ponieważ nazwa zespołu zaczyna się poniżej punktu odniesienia, argumentem wiersza będzie 1.

Jednak argument wysokości może zmieniać się między 0, 1 i 2 i nie można go dodać ręcznie do każdej komórki. Aby wypełnić inne karty formułą, możesz użyć funkcji dopasowania, która prawidłowo przypisuje numer kolumny. Omówmy to krótko, nie wchodząc w szczegóły.

Składnia funkcji dopasowania

Wyszukaj_wartość , lookup_array , oraz typ_dopasowania to trzy argumenty w funkcji dopasowania.

jak monitorować ruch w sieci domowej

W tym przykładzie wartość w komórce B5 jest wartością wyszukiwania, podczas gdy lista nazw lig w komórkach od E4 do G4 jest tablicą wyszukiwania. Z match_type wybierzmy dokładne dopasowanie.

Wybierz całą funkcję dopasowania i naciśnij F9 aby sprawdzić, czy wybrał właściwą pozycję kolumny dla wybranej ligi piłkarskiej w prostym menu rozwijanym. Funkcja dopasowania rozpoczyna odliczanie od pierwszej kolumny i uwzględnia komórkę E4 na pozycji jeden, która jest punktem odniesienia.

Z drugiej strony przesunięcie zaczyna liczyć od 0. Funkcja dopasowania musi również przyjąć kolumnę odniesienia w pozycji zero, aby była spójna z funkcją przesunięcia. Aby to zmienić, odejmij jeden od całej formuły.

Następnie ustaw wysokość na maksymalną liczbę wartości, które chcesz wyświetlić w menu, a szerokość na jeden. Odpowiada to pozycji wiersza i kolumny w formule.

naciskać wejść aby sprawdzić, czy formuła wybrała właściwy zespół.

Teraz, gdy formuła jest gotowa, dodajmy ją do sprawdzania poprawności danych.

Dodawanie formuły do walidacji danych

1. Naciskając CTRL + C , możesz skopiować formułę z wybranej komórki.

2. Iść do Walidacji danych .

3. Umieść skopiowaną formułę jako źródło po wybraniu Lista jako pierwsza opcja.

Po wykonaniu formuła wygeneruje wiele zależnych menu rozwijanych dla drużyn piłkarskich.

Kopiuj i wklej formuła w dół rzędu dla drużyn piłkarskich, aby wdrożyć ją w całym rzędzie.

Oto, w jaki sposób można użyć kombinacji formuły Przesunięcie i funkcji dopasowania, aby utworzyć listę rozwijaną zależną od wielu. Formuła offsetu może początkowo wydawać się myląca, ale przyzwyczaisz się do niej po kilkukrotnym wdrożeniu.

Związane z: Jak pracować z zakładkami arkusza roboczego w programie Microsoft Excel

Ułatw tworzenie rozwijanych menu dzięki formule offsetowej

Wiele arkuszy w miejscu pracy wymaga utworzenia list rozwijanych. Podejście z formułą offsetową jest najszybszą i najprostszą metodą tworzenia całego menu rozwijanego przy użyciu tylko jednej formuły.

Ponadto możliwe jest ręczne wybieranie komórek do sprawdzania poprawności danych w celu tworzenia list rozwijanych. Jednak ręczne tworzenie list rozwijanych dla dużego zestawu danych jest czasochłonne i istnieje większe prawdopodobieństwo popełnienia błędów. Podobnie jak Excel, możesz również utworzyć menu rozwijane dla Arkuszy Google.

Udział Udział Ćwierkać E-mail Jak utworzyć listę rozwijaną w Arkuszach Google

Jeśli chcesz mieć pewność, że do komórki można dodać tylko określone dane, zacznij ograniczać wpisy za pomocą list rozwijanych w Arkuszach Google.

Czytaj dalej Powiązane tematy

Wydajność
Wskazówki dotyczące arkuszy kalkulacyjnych
Microsoft Excel
Porady dotyczące pakietu Microsoft Office
Analiza danych

O autorze Szan Abdul |(46 opublikowanych artykułów)

Shan Abdul jest absolwentem inżynierii mechanicznej. Po ukończeniu edukacji rozpoczął karierę jako niezależny pisarz. Pisze o używaniu różnych narzędzi i oprogramowania, aby pomóc ludziom być bardziej produktywnymi jako student lub profesjonalista. W wolnym czasie uwielbia oglądać filmy na Youtube na temat produktywności.

Więcej od Shana Abdula

Zapisz się do naszego newslettera

Dołącz do naszego newslettera, aby otrzymywać porady techniczne, recenzje, bezpłatne e-booki i ekskluzywne oferty!

Kliknij tutaj, aby zasubskrybować