Użyj nazw dynamicznych w programie Excel do elastycznych rozwijanych rozwijanych

Arkusze kalkulacyjne Excel często obejmują menu rozwijane w celu uproszczenia i/lub standaryzacji wprowadzania danych.  Te rozwijane są tworzone za pomocą funkcji sprawdzania poprawności danych w celu określenia listy dopuszczalnych wpisów.

Aby skonfigurować prostą listę rozwijaną, wybierz komórkę, w której dane zostaną wprowadzone, a następnie kliknij Walidacji danych (na Dane Tab), wybierz sprawdzanie poprawności danych, wybierz Lista (Under zezwolenie :), a następnie wprowadź elementy listy (oddzielone przecinkami) w Źródło: pole (patrz rysunek 1).

Ta druga metoda ułatwia edycję opcji na liście, ale dodawanie lub usuwanie elementów może być problematyczne.  Ponieważ nazwany zakres (w naszym przykładzie Fruitchoices) odnosi się do stałego zakresu komórek ($ h 3: $ h 10 $, jak pokazano), jeśli do komórek jest dodane do komórek H11 lub poniżej, nie pojawią się one w rozwijaniu (Ponieważ komórki te nie są częścią zakresu Fruitchoices).

Podobnie, jeśli na przykład wpisy gruszek i truskawek zostaną usunięte, nie będą już pojawiać się w rozwijaniu, ale zamiast tego rozwijanie będzie zawierać dwa „puste” wybory, ponieważ rozwijanie nadal odnosi się do całego zakresu Fruitchoices, w tym pustych komórek H9 i H10.

Z tych powodów, przy użyciu normalnego wymienionego zakresu jako źródła listy rozwijanego, sam wymieniony zakres musi zostać edytowany tak, aby zawierał więcej lub mniej komórek, jeśli wpisy są dodawane lub usuwane z listy.

Rozwiązaniem tego problemu jest użycie dynamiczny Nazwa zasięgu jako źródło wyborów rozwijanych.  Nazwa zakresu dynamicznego to taka, która automatycznie rozszerza się (lub kontrakty), aby dokładnie dopasować rozmiar bloku danych w miarę dodawania lub usuwania wpisów.  Aby to zrobić, używasz formuła, zamiast stałego zakresu adresów komórkowych, aby zdefiniować nazwany zakres.

Jak skonfigurować zakres dynamiczny w programie Excel

Normalna nazwa zasięgu (statycznego) odnosi się do określonego zakresu komórek ($ h 3 $: $ h 10 $ w naszym przykładzie, patrz poniżej):

Ale zakres dynamiczny jest zdefiniowany przy użyciu formuły (patrz poniżej, pobrany z oddzielnego arkusza kalkulacyjnego, który używa nazwy zakresu zakresu dynamicznego):

Zanim zaczniemy, pamiętaj, aby pobrać nasz przykładowy plik Excel (makra sortowania zostały wyłączone).

Przeanalizujmy tę formułę szczegółowo.  Wybory owoców znajdują się w bloku komórek bezpośrednio poniżej nagłówka (OWOCE).  Ten nagłówek jest również przypisywany nazwa: Osheading:

Cała formuła używana do zdefiniowania zakresu dynamicznego dla opcji owoców jest:

= Offset (Sheading Fruits, 1,0, IFERROR (MATHE (True, indeks (isblank (Offset (Sheading Fruits, 1,0,20,1)), 0,0), 0) -1,20), 1)

Osheading odnosi się do nagłówka, który jest o jeden rząd powyżej pierwszego wpisu na liście.  Liczba 20 (używana dwa razy w wzorze) to maksymalny rozmiar (liczba wierszy) dla listy (można to dostosować zgodnie z żądaniem).

Zauważ, że w tym przykładzie na liście znajduje się tylko 8 wpisów, ale poniżej są puste komórki, w których można dodać dodatkowe wpisy.  Liczba 20 odnosi się do całego bloku, w którym można dokonywać wpisów, a nie do rzeczywistej liczby wpisów.

Teraz rozbijmy formułę na kawałki (kodowanie kolorów każdy kawałek), aby zrozumieć, jak to działa:

= Offset (Sheading Fruits, 1,0, IFERROR (dopasowanie (prawda, indeks (isblank (Offset (Sheading Fruits, 1,0,20,1)), 0,0), 0) -1,20), 1)

„Najbardziej wewnętrzny” kawałek to Offset (Sheading Fruits, 1,0,20,1).  Odwołuje się to do bloku 20 komórek (pod komórką owocową), w której można wprowadzić wybory.  Ta funkcja przesunięcia w zasadzie mówi: zacznij od Osheading komórka, zejdź w dół 1 wiersz i ponad 0 kolumn, a następnie wybierz obszar o długości 20 rzędów i szerokości 1 kolumny.  To daje nam 20-rzędowy blok, w którym wprowadzane są opcje owoców.

Następnym elementem formuły jest JEST PUSTY funkcjonować:

= Offset (Sheading Fruits, 1,0, IFERROR (dopasowanie (prawda, indeks (Isblank (powyższe),0,0), 0) -1,20), 1)

Tutaj funkcja przesunięcia (wyjaśniona powyżej) została zastąpiona „powyższym” (aby ułatwić odczytanie).  Ale funkcja ISBLANK działa na 20-rzędowym zakresie komórek, które definiuje funkcja przesunięcia.

Isblank następnie tworzy zestaw 20 prawdziwych i fałszywych wartości, co wskazuje, czy każda z poszczególnych komórek w zakresie 20 rzędu, do którego odwołuje się funkcja przesunięcia, jest pusta (pusta), czy nie.  W tym przykładzie pierwsze 8 wartości w zestawie będzie fałszywe, ponieważ pierwsze 8 komórek nie jest pustych, a ostatnie 12 wartości będzie prawdziwe.

Następnym elementem formuły jest funkcja indeksu:

= Offset (sheading owoców, 1,0, iferror (dopasowanie (prawda,Indeks (powyższy, 0,0),0) -1,20), 1)

Ponownie „powyższe” odnosi się do funkcji ISBLANK i przesunięcia opisanych powyżej.  Funkcja indeksu zwraca tablicę zawierającą 20 wartości prawdziwych / fałszywych utworzonych przez funkcję ISBLANK.

INDEKS jest zwykle używany do wybierania określonej wartości (lub zakresu wartości) z bloku danych, określając określony wiersz i kolumnę (w tym bloku).  Ale ustawienie wejść wierszy i kolumny na zero (jak to się dzieje) powoduje, że indeks zwraca tablicę zawierającą cały blok danych.

Następnym elementem formuły jest funkcja dopasowania:

= Offset (smażenie owoców, 1,0, iferror (Dopasowanie (prawda, powyższe, 0) -1,20), 1)

MECZ Funkcja zwraca pozycję pierwszej wartości prawdziwej, w tablicy zwróconej przez funkcję indeksu.  Ponieważ pierwsze 8 wpisów na liście nie jest puste, pierwsze 8 wartości w tablicy będzie fałszywe, a dziewiąta wartość będzie prawdziwa (ponieważ 9^th Rząd w zakresie jest pusty).

Więc funkcja dopasowania zwróci wartość 9.  W tym przypadku jednak naprawdę chcemy wiedzieć, ile wpisów znajduje się na liście, więc formuła odejmuje 1 od wartości dopasowania (co daje pozycję ostatniego wpisu).  Więc ostatecznie dopasuj (prawda, powyższe, 0) -1 Zwraca wartość 8.

Kolejnym elementem formuły jest funkcja iferror:

= Offset (osobie owoców, 1,0,Iferror (powyższe, 20),1)

Funkcja IFERROR zwraca wartość alternatywną, jeśli pierwsza określona wartość powoduje błąd.  Ta funkcja jest uwzględniona, ponieważ jeśli cały blok komórek (wszystkie 20 wierszy) jest wypełniony wpisami, funkcja dopasowania zwróci błąd.

Jest tak, ponieważ mówimy funkcję dopasowania, aby poszukiwał pierwszej prawdziwej wartości (w tablicy wartości z funkcji Isblank), ale jeśli żadna z komórek nie jest pusta, cała tablica zostanie wypełniona fałszywymi wartościami.  Jeśli mecz nie może znaleźć wartości docelowej (prawdziwy) w tablicy, w której wyszukuje, zwraca błąd.

Jeśli więc cała lista jest pełna (a zatem dopasowanie zwraca błąd), funkcja IFERROR zwróci wartość 20 (wiedząc, że na liście musi być 20 wpisów).

Wreszcie, Offset (Sheading Fruits, 1,0, powyższe, 1) Zwraca zakres, którego faktycznie szukamy: Zacznij od komórki owocowej, zejdź w dół 1 rzędu i ponad 0 kolumn, a następnie wybierz obszar, który jest wiele wierszy długi, ponieważ na liście jest wpisy (i 1 kolumna).  Tak więc cała formuła razem zwróci zakres, który zawiera tylko rzeczywiste wpisy (do pierwszej pustej komórki).

Korzystanie z tej formuły do ​​zdefiniowania zakresu, które jest źródłem menu, oznacza, że ​​możesz swobodnie edytować listę (dodawanie lub usuwanie wpisów, o ile pozostałe wpisy uruchomione w górnej komórce i są ciągłe), a rozwijanie zawsze odzwierciedla bieżące Lista (patrz rysunek 6).

Przykładowy plik (listy dynamiczne), który został tu używany, jest uwzględniony i można je pobrać z tej strony. Makra nie działają jednak, ponieważ WordPress nie lubi książek Excel z makrami.

Jako alternatywę dla określenia liczby wierszy w bloku listy, blok listy można przypisać własnej nazwie zakresu, które można następnie użyć w zmodyfikowanej formule.  W pliku przykładowym druga lista (nazwy) używa tej metody.  Tutaj całą listę listy (pod nagłówkiem „nazwy”, 40 wierszy w pliku przykładowym) przypisuje się nazwę zasięgu NameBlock.  Następnie jest alternatywna formuła definiowania listy nazw:

= Przesunięcie (nazwa, 1,0, IFERROR (dopasowanie (prawda, indeks (isblank (Namesblock), 0,0), 0) -1,Rząd (nazwa BLOCK)), 1)

Gdzie Namesblock zastępuje przesunięcie (Sheading Fruits, 1,0,20,1) i Rząd (nazwa BLOCK) zastępuje 20 (liczbę wierszy) we wcześniejszej formule.

Tak więc, w przypadku list rozwijanych, które można łatwo edytować (w tym innych użytkowników, którzy mogą być niedoświadczeni), spróbuj użyć nazwy zakresu zakresu dynamicznego!  I pamiętaj, że chociaż ten artykuł koncentrował się na listach rozwijanych, nazwy zakresu dynamicznego mogą być używane w dowolnym miejscu, aby odwołać się do zakresu lub listy, które mogą różnić się rozmiarem. Cieszyć się!