Arkusze kalkulacyjne programu Excel często zawierają rozwijane komórki w celu uproszczenia i / lub standaryzacji wprowadzania danych. Listy rozwijane są tworzone przy użyciu funkcji sprawdzania poprawności danych w celu określenia listy dozwolonych wpisów.
Aby skonfigurować prostą listę rozwijaną, wybierz komórkę, w której zostaną wprowadzone dane, a następnie kliknij opcję Sprawdzanie danych (na karcie Dane ), wybierz opcję Sprawdzanie danych, wybierz opcję Lista (w polu Zezwalaj :), a następnie wprowadź elementy listy (oddzielone przecinkami). ) w polu Source : (patrz rysunek 1).
W tym typowym menu rozwijanym lista dozwolonych wpisów jest określana w ramach samego sprawdzania poprawności danych; dlatego, aby dokonać zmian na liście, użytkownik musi otworzyć i edytować sprawdzanie poprawności danych. Może to być jednak trudne dla niedoświadczonych użytkowników lub w przypadkach, gdy lista opcji jest długa.
Inną opcją jest umieszczenie listy w nazwanym zakresie w arkuszu kalkulacyjnym, a następnie określenie tej nazwy zakresu (poprzedzona znakiem równości) w polu Źródło : sprawdzanie poprawności danych (jak pokazano na Rysunku 2).
Ta druga metoda ułatwia edycję opcji na liście, ale dodawanie lub usuwanie elementów może być problematyczne. Ponieważ podany zakres (FruitChoices, w naszym przykładzie) odnosi się do ustalonego zakresu komórek ($ H $ 3: $ H $ 10, jak pokazano), jeśli więcej opcji zostanie dodanych do komórek H11 lub poniżej, nie pojawią się one w menu rozwijanym (ponieważ te komórki nie należą do zakresu FruitChoices).
Podobnie, jeśli na przykład usunięto wpisy Gruszki i Truskawki, nie będą one już wyświetlane w menu rozwijanym, ale zamiast tego lista zawiera dwa "puste" opcje, ponieważ menu rozwijane nadal odnosi się do całego zakresu FruitChoices, w tym pustych komórek H9 i H10.
Z tych powodów, gdy używasz zwykłego nazwanego zakresu jako źródła listy dla listy rozwijanej, sam zakres nazw musi być edytowany, aby dodać więcej lub mniej komórek, jeśli wpisy zostaną dodane lub usunięte z listy.
Rozwiązaniem tego problemu jest użycie nazwy zakresu dynamicznego jako źródła dla opcji rozwijanych. Nazwa zakresu dynamicznego to taka, która automatycznie rozwija (lub kontraktuje), aby dokładnie dopasować rozmiar bloku danych w miarę dodawania lub usuwania pozycji. Aby to zrobić, należy użyć formuły, a nie ustalonego zakresu adresów komórek, aby zdefiniować nazwany zakres.
Jak skonfigurować zakres dynamiczny 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 (zobacz poniżej, zaczerpnięty z oddzielnego arkusza kalkulacyjnego, który używa nazw zakresu dynamicznego):
Zanim zaczniemy, pobierz plik przykładowy Excel (makra sortowania zostały wyłączone).
Przeanalizujmy szczegółowo tę formułę. Wybory dla Fruits znajdują się w bloku komórek bezpośrednio pod nagłówkiem ( FRUITS ). Do tego nagłówka przypisana jest również nazwa: FruitsHeading :
Cała formuła używana do definiowania zakresu dynamicznego dla wyboru owoców to:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (PRAWDA, INDEKS (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading odnosi się do nagłówka, który znajduje się w jednym wierszu nad pierwszym wpisem na liście. Liczba 20 (używana dwukrotnie w formule) to maksymalna wielkość (liczba wierszy) dla listy (można ją dostosować według potrzeb).
Zauważ, że w tym przykładzie na liście jest tylko 8 pozycji, ale poniżej znajdują się 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 każdego kawałka), aby zrozumieć, jak to działa:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (PRAWDA, INDEKS (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
Najbardziej "najgłębszym" jest OFFSET (FruitsHeading, 1, 0, 20, 1) . Odwołuje się do bloku 20 komórek (poniżej komórki FruitsHeading), w którym można wprowadzić wybory. Ta funkcja OFFSET zasadniczo mówi: Zacznij w komórce FruitsHeading, zejdź w dół o jeden wiersz i ponad 0 kolumn, a następnie wybierz obszar o długości 20 wierszy i szerokości 1 kolumny. Tak więc daje nam 20-wierszowy blok, w którym wprowadzane są wybory Owoców.
Następnym elementem formuły jest funkcja ISBLANK :
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (PRAWDA, INDEKS ( ISBLANK (powyższe), 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-rzędowym zakresie komórek zdefiniowanych przez funkcję PRZESUNIĘCIE.
ISBLANK tworzy zestaw 20 wartości TRUE i FALSE, wskazując, czy każda z komórek w 20-rzędowym zakresie, do którego odwołuje się funkcja OFFSET, jest pusta (pusta), czy nie. W tym przykładzie pierwsze 8 wartości w zbiorze będzie FALSE, ponieważ pierwsze 8 komórek nie jest pustych, a ostatnie 12 wartości będzie PRAWDA.
Następną częścią formuły jest funkcja INDEKS:
= PRZESUNIĘCIE (FruitsHeading, 1, 0, IFERROR (MATCH (PRAWDA, INDEKS (powyższe, 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 TRUE / FALSE utworzonych przez funkcję ISBLANK.
INDEX jest zwykle używany do wybierania określonej wartości (lub zakresu wartości) z bloku danych, poprzez określenie określonego wiersza i kolumny (w tym bloku). Ale ustawienie wejść do wiersza i kolumny na zero (tak jak to jest tutaj zrobione) powoduje, że INDEX zwraca tablicę zawierającą cały blok danych.
Następnym elementem formuły jest funkcja MATCH:
= PRZESUNIĘCIE (FruitsHeading, 1, 0, IFERROR ( MATCH (PRAWDA, powyższe, 0) -1, 20), 1)
Funkcja MATCH zwraca pozycję pierwszej wartości PRAWDA w tablicy, która jest zwracana przez funkcję INDEX. Ponieważ pierwsze 8 pozycji na liście nie jest pustych, pierwsze 8 wartości w tablicy będzie FALSE, a dziewiąta będzie PRAWDA (ponieważ dziewiąty wiersz w zakresie jest pusty).
Tak więc funkcja MATCH 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 MATCH (która podaje pozycję ostatniego wpisu). Tak więc ostatecznie, MECZ (PRAWDA, powyższe, 0) -1 zwraca wartość 8 .
Następną częścią formuły jest funkcja IFERROR:
= OFFSET (FruitsHeading, 1, 0, IFERROR (powyższe, 20), 1)
Funkcja IFERROR zwraca alternatywną wartość, jeśli pierwsza podana wartość powoduje błąd. Ta funkcja jest włączona, ponieważ jeśli cały blok komórek (wszystkie 20 wierszy) zostanie wypełniony wpisami, funkcja MATCH zwróci błąd.
Dzieje się tak, ponieważ mówimy funkcji MATCH, aby szukała pierwszej wartości PRAWDA (w tablicy wartości z funkcji ISBLANK), ale jeśli ŻADNA z komórek jest pusta, cała tablica zostanie wypełniona wartościami FAŁSZ. Jeśli MATCH nie może znaleźć wartości docelowej (TRUE) w tablicy, która szuka, zwraca błąd.
Tak więc, jeśli cała lista jest pełna (i dlatego MATCH zwraca błąd), funkcja IFERROR zamiast tego zwróci wartość 20 (wiedząc, że na liście musi być 20 pozycji).
Na koniec: OFFSET (FruitsHeading, 1, 0, powyższy, 1) zwraca zakres, którego faktycznie szukamy: Rozpocznij w komórce FruitsHeading, przejdź w dół o jeden wiersz i ponad 0 kolumn, a następnie wybierz obszar o długości wielu wierszy są pozycje na liście (i 1 kolumna szeroka). Tak więc cała formuła razem zwróci zakres, który zawiera tylko faktyczne wpisy (do pierwszej pustej komórki).
Użycie tej formuły do zdefiniowania zakresu będącego źródłem rozwijanego menu oznacza, że możesz swobodnie edytować listę (dodawać lub usuwać wpisy, o ile pozostałe wpisy zaczynają się od górnej komórki i są przyległe), a lista rozwijana zawsze odzwierciedla bieżące listę (patrz rys. 6).
Plik przykładowy (dynamiczne listy), który został tutaj użyty, jest dołączony i można go pobrać z tej witryny. Makra nie działają jednak, ponieważ WordPress nie lubi książek Excel zawierających makra.
Jako alternatywę do określania liczby wierszy w bloku listy, blokowi listy można przypisać własną nazwę zakresu, którą następnie można wykorzystać w zmodyfikowanej formule. W pliku przykładowym druga lista (nazwy) używa tej metody. Tutaj cały blok listy (pod nagłówkiem "NAZWY", 40 wierszy w pliku przykładowym) ma przypisaną nazwę zakresu NameBlock . Alternatywną formułą do zdefiniowania NamesList jest:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (PRAWDA, INDEKS (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
gdzie NamesBlock zastępuje OFFSET (FruitsHeading, 1, 0, 20, 1) i ROWS (NamesBlock) zastępuje 20 (liczba wierszy) we wcześniejszej formule.
Tak więc w przypadku list rozwijanych, które można łatwo edytować (w tym przez innych użytkowników, którzy mogą być niedoświadczeni), spróbuj użyć nazw zakresu dynamicznego! Pamiętaj, że chociaż artykuł ten koncentruje się na listach rozwijanych, nazwy zakresów dynamicznych mogą być używane wszędzie tam, gdzie powinieneś odwoływać się do zakresu lub listy o różnych rozmiarach. Cieszyć się!
![Czy wiesz, jak dobry jest Google w Poezji [Google Poems]](https://gadget-info.com/img/entertainment/122/do-you-know-how-good-google-is-poetry.jpg)
