Arkusze kalkulacyjne Excel często zawierają menu rozwijane komórek w celu uproszczenia i / lub standaryzacji wprowadzania danych. Te listy rozwijane są tworzone za pomocą funkcji sprawdzania poprawności danych w celu określenia listy dozwolonych wpisów.
Aby skonfigurować prostą listę rozwijaną, wybierz komórkę, w której dane zostaną wprowadzone, a następnie kliknij Sprawdzanie poprawności danych(na karcie Dane) wybierz opcję Sprawdzanie danych, wybierz Lista(w obszarze Zezwalaj :), a następnie wprowadź elementy listy (oddzielone przecinkami) w Źródło: pole (patrz rysunek 1).
W tego rodzaju podstawowym menu rozwijanym określa się listę dozwolonych wpisów w ramach samej weryfikacji danych; dlatego, aby wprowadzić zmiany na liście, użytkownik musi otworzyć i edytować sprawdzanie poprawności danych. Może to być trudne dla niedoświadczonych użytkowników lub w przypadkach, gdy lista opcji jest długa.
Inną opcją jest umieszczenie listy w nazwany zakres w arkuszu kalkulacyjnym, a następnie określenie nazwa zakresu (poprzedzona znakiem równości) w polu Źródło: pole sprawdzania poprawności danych (jak pokazano na rysunku 2).
Ta druga metoda ułatwia edytowanie wyborów na liście, ale dodawanie lub usuwanie elementów może być problematyczne. Ponieważ nazwany zakres (FruitChoices, w naszym przykładzie) odnosi się do stałego zakresu komórek ($ H $ 3: $ H $ 10, jak pokazano), jeśli więcej opcji zostanie dodanych do komórek H11 lub niższych, nie pojawią się w menu rozwijanym (ponieważ te komórki nie są częścią zakresu FruitChoices).
Podobnie, jeśli na przykład wpisy w Gruszkach i Truskawkach zostaną usunięte, nie pojawią się już w menu rozwijanym, ale zamiast tego będą zawierać dwa „Puste” opcje, ponieważ menu rozwijane nadal odnosi się do całego zakresu FruitChoices, w tym do pustych komórek H9 i H10.
Z tych powodów, gdy używa się normalnego nazwanego zakresu jako źródła listy dla rozwijanego menu, nazwany zakres sam należy edytować, aby zawierał więcej lub mniej komórek, jeśli wpisy są dodawane lub usuwane z listy.
Rozwiązaniem tego problemu jest użycie dynamicznejnazwa zakresu jako źródło rozwijanych opcji. Nazwa zakresu dynamicznego to taka, która automatycznie rozszerza się (lub kurczy) tak, aby dokładnie odpowiadała rozmiarowi bloku danych podczas dodawania lub usuwania wpisów. Aby to zrobić, należy użyć formula, a nie stałego zakresu adresów komórek, aby zdefiniować nazwany zakres.
Jak skonfigurować dynamiczny Zakres w programie Excel
Normalna (statyczna) nazwa zakresu odnosi się do określonego zakresu komórek ($ H $ 3: $ H $ 10 w naszym przykładzie, patrz poniżej):
Ale zakres dynamiczny jest definiowany za pomocą formuły (patrz poniżej, zaczerpnięty z osobnego arkusza kalkulacyjnego, który używa nazw zakresu dynamicznego):
Zanim zaczniemy, pobierz Plik przykładowy Excel (makra sortowania zostały wyłączone).
Przyjrzyjmy się szczegółowo tej formule. Opcje dotyczące owoców znajdują się w bloku komórek bezpośrednio pod nagłówkiem (OWOCE). Nagłówek ten otrzymuje również nazwę: OwoceOsoba:
Cała formuła użyta do zdefiniowania zakresu dynamicznego dla Wybrane owoce to:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeadingodnosi się do nagłówka znajdującego się o jeden wiersz nad pierwszym wpisem na liście. Liczba 20 (użyta dwa razy w formule) jest maksymalnym rozmiarem (liczbą wierszy) listy (można ją dowolnie dostosować).
Uwaga: w tym przykładzie jest tylko 8 wpisów na liście, ale pod nimi są również puste komórki, w których można dodać dodatkowe wpisy. Liczba 20 odnosi się do całego bloku, w którym można wprowadzać wpisy, a nie do faktycznej liczby wpisów.
Teraz podzielmy formułę na części (kodowanie kolorami każdego elementu), aby zrozumieć, jak to działa :
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
„Najbardziej wewnętrzny” kawałek to PRZESUNIĘCIE (FruitsHeading, 1,0,20,1). Odnosi się to do bloku 20 komórek (pod komórką FruitsHeading), w którym można wprowadzić wybory. Ta funkcja PRZESUNIĘCIA w zasadzie mówi: Zacznij od komórki FruitsHeading, przejdź w dół 1 wiersz i ponad 0 kolumn, a następnie wybierz obszar o długości 20 wierszy i szerokości 1 kolumny. To daje nam 20-wierszowy blok, w którym wprowadzane są wybory Fruits.
Następnym elementem formuły jest funkcja ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Tutaj funkcja OFFSET (wyjaśniona powyżej) została zastąpiona przez „powyższe” (aby ułatwić czytanie). Ale funkcja ISBLANK działa na 20-wierszowym zakresie komórek, które definiuje funkcja OFFSET.
ISBLANK tworzy następnie zestaw 20 wartości PRAWDA i FAŁSZ, wskazując, czy każda z poszczególnych komórek w 20- zakres wierszy, do którego odwołuje się funkcja PRZESUNIĘCIE, jest pusty (pusty) lub nie. W tym przykładzie pierwsze 8 wartości w zestawie będzie miało wartość FAŁSZ, ponieważ pierwsze 8 komórek nie jest puste, a ostatnie 12 wartości będą PRAWDA.
Następnym elementem formuły jest funkcja INDEKS:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Ponownie, „powyższe” odnosi się do opisanych powyżej funkcji ISBLANK i OFFSET. Funkcja INDEKS zwraca tablicę zawierającą 20 wartości PRAWDA / FAŁSZ utworzonych przez funkcję ISBLANK.
INDEKSjest zwykle używany do wybierania określonej wartości (lub zakresu wartości) spośród blok danych, określając określony wiersz i kolumnę (w tym bloku). Ale ustawienie wartości wejściowych wierszy i kolumn na zero (jak to tutaj zrobiono) powoduje, że INDEX zwraca tablicę zawierającą cały blok danych.
Następnym elementem formuły jest funkcja MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
Funkcja PODAJzwraca pozycję pierwszej PRAWDZIWEJ wartości w tablicy zwracanej przez funkcję INDEKS. Ponieważ pierwszych 8 wpisów na liście nie jest pustych, pierwszych 8 wartości w tablicy będzie FAŁSZ, a dziewiąta wartość będzie PRAWDA (ponieważ 9 thw zakresie jest pusty).
Tak więc funkcja PODAJNIK zwróci wartość 9. W tym przypadku jednak naprawdę chcemy wiedzieć, ile wpisów jest na liście, więc formuła odejmuje 1 od wartości PODAJ.POZYCJĘ (co daje pozycję ostatniego wpisu). Tak więc ostatecznie PODAJNIK (PRAWDA, powyższe, 0) -1 zwraca wartość 8.
Następnym fragmentem formuły jest funkcja IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Funkcja IFERROR zwraca wartość alternatywną, jeśli pierwsza podana wartość powoduje błąd. Ta funkcja jest uwzględniona, ponieważ jeśli cały blok komórek (wszystkie 20 wierszy) zostanie wypełniony wpisami, funkcja PODAJNIK zwróci błąd.
To dlatego, że mówimy funkcji PODAJ. pierwsza PRAWDA wartość (w tablicy wartości z funkcji ISBLANK), ale jeśli ŻADNA z komórek nie będzie pusta, wówczas cała tablica zostanie wypełniona FAŁSZ. Jeśli MATCH nie może znaleźć wartości docelowej (PRAWDA) w wyszukiwanej tablicy, zwraca błąd.
Tak więc, jeśli cała lista jest pełna (a zatem MATCH zwraca błąd), funkcja IFERROR zamiast tego zwraca wartość 20 (wiedząc, że na liście musi być 20 wpisów).
Wreszcie PRZESUNIĘCIE (FruitsHeading, 1,0, powyżej, 1)zwraca zakres, którego tak naprawdę szukamy: Zacznij od komórki FruitsHeading, przejdź w dół o 1 wiersz i ponad 0 kolumn, a następnie wybierz obszar, który ma jednak wiele wierszy, o ile na liście znajdują się wpisy (i szerokość 1 kolumny). Tak więc cała formuła razem zwróci zakres zawierający tylko rzeczywiste wpisy (aż do pierwszej pustej komórki).
Użycie tej formuły do zdefiniowania zakresu, który jest źródłem listy rozwijanej, oznacza, że możesz swobodnie edytować lista (dodawanie lub usuwanie wpisów, o ile pozostałe wpisy zaczynają się w górnej komórce i są ciągłe), a lista rozwijana zawsze będzie odzwierciedlać bieżącą listę (patrz rysunek 6).
Użyto tu przykładowy plik (listy dynamiczne) i można ją pobrać z tej witryny. Makra nie działają jednak, ponieważ WordPress nie lubi książek Excela z makrami.
Jako alternatywę dla określenia liczby wierszy w bloku listy, blokowi listy można przypisać jego własna nazwa zakresu, którą można następnie zastosować w zmodyfikowanej formule. W pliku przykładowym druga lista (Nazwy) korzysta z tej metody. Tutaj cały blok listy (pod nagłówkiem „NAMES”, 40 wierszy w przykładowym pliku) ma przypisaną nazwę zakresu NameBlock. Alternatywna formuła definiowania NamesList to:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
gdzie NamesBlockzastępuje OFFSET (FruitsHeading, 1,0,20,1) i ROWS (NamesBlock)zastępuje 20 (liczbę wierszy) we wcześniejszej formule.
Tak więc w przypadku list rozwijanych, które można łatwo edytować (w tym przez innych niedoświadczonych użytkowników), spróbuj użyć nazw zakresu dynamicznego! I zauważ, że chociaż ten artykuł koncentruje się na listach rozwijanych, nazw zakresów dynamicznych można używać wszędzie tam, gdzie trzeba odwoływać się do zakresu lub listy, które mogą różnić się rozmiarem. Ciesz się!