Arkusze kalkulacyjne Excel^(Excel) często zawierają listy rozwijane komórek, aby uprościć i/lub ujednolicić wprowadzanie danych. Te 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 Sprawdzanie poprawności danych^{(Data Validation)} (na karcie Dane^(Data) ), wybierz Sprawdzanie poprawności danych^{(Data Validation)} , wybierz Lista^(List) (w obszarze Zezwalaj^(Allow) :), a następnie wprowadź elementy listy (oddzielone przecinkami ) w polu Źródło^(Source) : (patrz rysunek 1).

W tym typie podstawowego menu rozwijanego lista dozwolonych wpisów jest określona w samej walidacji danych; dlatego, aby wprowadzić zmiany na liście, użytkownik musi otworzyć i edytować walidację danych. Może to być jednak trudne dla niedoświadczonych użytkowników lub w przypadkach, gdy lista wyborów jest długa.

Inną opcją jest umieszczenie listy w nazwanym zakresie w arkuszu kalkulacyjnym^{(named range within the spreadsheet)} , a następnie określenie tej nazwy zakresu (poprzedzonej znakiem równości) w polu Źródło^(Source) : sprawdzania poprawności danych (jak pokazano na Rysunku 2^{(Figure 2)} ).

Ta druga metoda ułatwia edycję 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 ustalonego zakresu komórek ($ H $ 3: $ H $ 10, jak pokazano), jeśli do komórek H11 lub niższych zostanie dodanych więcej opcji, nie pojawią się one na liście rozwijanej (ponieważ te komórki nie należą do zakresu FruitChoices ).

Podobnie, jeśli, na przykład, wpisy Gruszki^(Pears) i Truskawki^{(Strawberries)} zostaną usunięte, nie będą się już pojawiać w menu rozwijanym, ale zamiast tego lista będzie zawierała dwie „puste” opcje, ponieważ lista ta nadal odwołuje się do całego zakresu FruitChoices, w tym pustych komórek H9 i H10 .

Z tych powodów, gdy jako źródło listy rozwijanej używany jest normalny nazwany zakres, sam nazwany zakres 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 nazwy zakresu dynamicznego^(dynamic) jako źródła opcji rozwijanych. Nazwa zakresu dynamicznego to taka, która automatycznie rozszerza się (lub kurczy), aby dokładnie dopasować rozmiar bloku danych w miarę dodawania lub usuwania wpisów. Aby to zrobić, użyj formuły^(formula) zamiast stałego zakresu adresów komórek, aby zdefiniować nazwany zakres.

Jak skonfigurować zakres dynamiczny^{(Dynamic Range)} w programie Excel^(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ą wzoru (patrz poniżej, zaczerpniętego z oddzielnego arkusza kalkulacyjnego, który używa nazw zakresów dynamicznych):

Zanim zaczniemy, pobierz nasz przykładowy plik Excel  (makra sortowania zostały wyłączone).

Przyjrzyjmy się szczegółowo tej formule. Wybory dla owoców znajdują się w bloku komórek bezpośrednio pod nagłówkiem ( OWCE^(FRUITS) ). Nagłówek ten ma również przypisaną nazwę: FruitsHeading :

Cała formuła użyta do zdefiniowania zakresu dynamicznego dla wyborów Fruits to:^(Fruits)

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading odnosi się do nagłówka znajdującego się o jeden wiersz powyżej pierwszego wpisu na liście. Liczba 20 (użyta w formule dwa razy) to maksymalny rozmiar (liczba wierszy) listy (można to dostosować według potrzeb).

Zauważ, że w tym przykładzie na liście jest tylko 8 wpisów, ale poniżej znajdują się 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 dokonywać wpisów, a nie do rzeczywistej liczby wpisów.

Teraz podzielmy formułę na części (oznaczając każdy kawałek kolorami), 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)

Elementem „najbardziej wewnętrznym” jest PRZESUNIĘCIE(FruitsHeading,1,0,20,1)^{(OFFSET(FruitsHeading,1,0,20,1))} . Odwołuje się to do bloku 20 komórek (pod komórką FruitsHeading ), w którym można wprowadzać opcje. Ta funkcja PRZESUNIĘCIA^(OFFSET) zasadniczo mówi: Zacznij od komórki FruitsHeading , przejdź w dół o 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-rzędowy blok, w którym wprowadzane są wybory owoców .^(Fruits)

Kolejnym 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żej” (aby ułatwić czytanie). Ale funkcja ISBLANK działa na 20-wierszowym zakresie komórek zdefiniowanym przez funkcję PRZESUNIĘCIE^(OFFSET) .

ISBLANK tworzy następnie zestaw 20 wartości PRAWDA^(TRUE) i FAŁSZ^(FALSE) , wskazując, czy każda z poszczególnych komórek w zakresie 20 wierszy, do którego odwołuje się funkcja PRZESUNIĘCIE^(OFFSET) , jest pusta (pusta), czy nie. W tym przykładzie pierwsze 8 wartości w zestawie będzie miało wartość FAŁSZ^(FALSE) , ponieważ pierwsze 8 komórek nie jest pustych, a ostatnie 12 wartości to PRAWDA^(TRUE) .

Kolejnym elementem formuły jest funkcja INDEKS^(INDEX) :

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Ponownie, „powyżej” odnosi się do opisanych powyżej funkcji ISBLANK i OFFSET . Funkcja INDEX zwraca tablicę zawierającą 20 wartości TRUE / FALSE utworzonych przez funkcję ISBLANK .

INDEKS^(INDEX) jest zwykle używany do wybrania określonej wartości (lub zakresu wartości) z bloku danych poprzez określenie określonego wiersza i kolumny (w ramach tego bloku). Jednak ustawienie wartości wejściowych wierszy i kolumn na zero (tak jak to zrobiono tutaj) powoduje, że INDEKS^(INDEX) zwraca tablicę zawierającą cały blok danych.

Kolejnym elementem formuły jest funkcja PODAJ.POZYCJĘ^(MATCH) :

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

Funkcja PODAJ.POZYCJĘ^(MATCH) zwraca pozycję pierwszej wartości PRAWDA^(TRUE) w tablicy, która jest zwracana przez funkcję INDEKS^(INDEX) . Ponieważ pierwszych 8 wpisów na liście nie jest pustych, pierwsze 8 wartości w tablicy będzie FALSE , a dziewiąta będzie TRUE (ponieważ dziewiąty ^wiersz w zakresie jest pusty).

Tak więc funkcja PODAJ.POZYCJĘ^(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 PODAJ.POZYCJĘ^(MATCH) (co daje pozycję ostatniego wpisu). Tak więc ostatecznie MATCH ( PRAWDA^(TRUE) ,powyższy,0)-1 zwraca wartość 8 .

Następnym elementem formuły jest funkcja JEŻELI.BŁĄD^(IFERROR) :

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

Funkcja JEŻELI.BŁĄD^(IFERROR) zwraca wartość alternatywną, jeśli pierwsza określona wartość powoduje błąd. Ta funkcja jest zawarta, ponieważ jeśli cały blok komórek (wszystkie 20 wierszy) jest wypełniony wpisami, funkcja PODAJ.POZYCJĘ^(MATCH) zwróci błąd.

Dzieje się tak dlatego, że mówimy funkcji PODAJ.POZYCJĘ^(MATCH) , aby szukała pierwszej wartości PRAWDA^(TRUE) (w tablicy wartości z funkcji CZY.PUSTE^(ISBLANK) ), ale jeśli ŻADNA^(NONE) z komórek nie jest pusta, cała tablica zostanie wypełniona wartościami FAŁSZ^(FALSE) . Jeśli MATCH nie może znaleźć wartości docelowej ( TRUE ) w przeszukiwanej tablicy, zwraca błąd.

Jeśli więc cała lista jest pełna (a zatem funkcja PODAJ.POZYCJĘ^(MATCH) zwróci błąd), funkcja JEŻELI.BŁĄD^(IFERROR) zwróci wartość 20 (wiedząc, że na liście musi być 20 wpisów).

Na koniec PRZESUNIĘCIE(FruitsHeading,1,0,powyższy,1)^{(OFFSET(FruitsHeading,1,0,the above,1))} zwraca zakres, którego faktycznie szukamy: Zacznij od komórki FruitsHeading , przejdź w dół o 1 wiersz i ponad 0 kolumn, a następnie wybierz obszar, który ma tyle wierszy, ile wynosi na liście znajdują się wpisy (i szerokości 1 kolumny). Tak więc cała formuła razem zwróci zakres, który zawiera tylko rzeczywiste wpisy (do pierwszej pustej komórki).

Użycie tej formuły do ​​zdefiniowania zakresu będącego źródłem listy rozwijanej oznacza, że ​​można swobodnie edytować listę (dodawać lub usuwać wpisy, o ile pozostałe wpisy zaczynają się od górnej komórki i są ciągłe), a lista rozwijana zawsze będzie odzwierciedlać bieżącą lista (patrz Rysunek 6^{(Figure 6)} ).

Przykładowy plik (Dynamic Lists) , który został tutaj użyty, jest dołączony i można go pobrać z tej witryny. Makra jednak nie działają, ponieważ WordPress nie lubi książek Excela z makrami w nich.^(Excel)

Jako alternatywę do określania liczby wierszy w bloku listy, blokowi listy można przypisać własną nazwę zakresu, której można następnie użyć w zmodyfikowanej formule. W przykładowym pliku druga lista ( Names ) używa tej metody. Tutaj całemu blokowi listy (pod nagłówkiem „NAZWY”, 40 wierszy w przykładowym pliku) przypisywana jest nazwa 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 NamesBlock zastępuje PRZESUNIĘCIE^(OFFSET) ( FruitsHeading,1,0,20,1 ), a 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 użytkowników, którzy mogą być niedoświadczeni), spróbuj użyć nazw zakresów dynamicznych! Pamiętaj, że chociaż ten artykuł koncentrował się na listach rozwijanych, nazwy zakresów dynamicznych mogą być używane wszędzie tam, gdzie potrzebujesz odwołać się do zakresu lub listy, które mogą różnić się rozmiarem. Cieszyć się!

Use Dynamic Range Names in Excel for Flexible Dropdowns

Excel spreadsheets often inсlude cell dropdowns to simplify and/or standardize data entry.  These dropdowns are created using the data validation feature to specify a list of allowable entries.

To set up a simple dropdown list, select the cell where data will be entered, then click Data Validation (on the Data tab), select Data Validation, choose List (under Allow:), and then enter the list items (separated by commas) in the Source: field (see Figure 1).

In this type of basic dropdown, the list of allowable entries is specified within the data validation itself; therefore, to make changes to the list, the user must open and edit the data validation.  This may be difficult, however, for inexperienced users, or in cases where the list of choices is lengthy.

Another option is to place the list in a named range within the spreadsheet, and then specify that range name (prefaced with an equal sign) in the Source: field of the data validation (as shown in Figure 2).

This second method makes it easier to edit the choices in the list, but adding or removing items can be problematic.  Since the named range (FruitChoices, in our example) refers to a fixed range of cells ($H$3:$H$10 as shown), if more choices are added to the cells H11 or below, they will not show up in the dropdown (since those cells are not part of the FruitChoices range).

Likewise if, for example, the Pears and Strawberries entries are erased, they will no longer appear in the dropdown, but instead the dropdown will include two “empty” choices since the dropdown still references the entire FruitChoices range, including the empty cells H9 and H10.

For these reasons, when using a normal named range as the list source for a dropdown, the named range itself must be edited to include more or fewer cells if entries are added or deleted from the list.

A solution to this problem is to use a dynamic range name as the source for the dropdown choices.  A dynamic range name is one that automatically expands (or contracts) to exactly match the size of a block of data as entries are added or removed.  To do this, you use a formula, rather than a fixed range of cell addresses, to define the named range.

How to Setup a Dynamic Range in Excel

A normal (static) range name refers to a specified range of cells ($H$3:$H$10 in our example, see below):

But a dynamic range is defined using a formula (see below, taken from a separate spreadsheet which uses dynamic range names):

Before we get started, make sure you download our Excel example file (sort macros have been disabled).

Let’s examine this formula in detail.  The choices for Fruits are in a block of cells directly below a heading (FRUITS).  That heading is also assigned a name: FruitsHeading:

The entire formula used to define the dynamic range for the Fruits choices is:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading refers to the heading that is one row above the first entry in the list.  The number 20 (used two times in the formula) is the maximum size (number of rows) for the list (this can be adjusted as desired).

Note that in this example, there are only 8 entries in the list, but there are also empty cells below these where additional entries could be added.  The number 20 refers to the entire block where entries can be made, not to the actual number of entries.

Now let’s break down the formula into pieces (color-coding each piece), to understand how it works:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

The “innermost” piece is OFFSET(FruitsHeading,1,0,20,1).  This references the block of 20 cells (underneath the FruitsHeading cell) where choices may be entered.  This OFFSET function basically says: Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is 20 rows long and 1 column wide.  So that gives us the 20-row block where the Fruits choices are entered.

The next piece of the formula is the ISBLANK function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

Here, the OFFSET function (explained above) has been replaced with “the above” (to make things easier to read).  But the ISBLANK function is operating on the 20-row range of cells that the OFFSET function defines.

ISBLANK then creates a set of 20 TRUE and FALSE values, indicating whether each of the individual cells in the 20-row range referenced by the OFFSET function is blank (empty) or not.  In this example, the first 8 values in the set will be FALSE since the first 8 cells are not empty and the last 12 values will be TRUE.

The next piece of the formula is the INDEX function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

Again, “the above” refers to the ISBLANK and OFFSET functions described above.  The INDEX function returns an array containing the 20 TRUE / FALSE values created by the ISBLANK function.

INDEX is normally used to pick a certain value (or range of values) out of a block of data, by specifying a certain row and column (within that block).  But setting the row and column inputs to zero (as is done here) causes INDEX to return an array containing the entire block of data.

The next piece of the formula is the MATCH function:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

The MATCH function returns the position of the first TRUE value, within the array that’s returned by the INDEX function.  Since the first 8 entries in the list are not blank, the first 8 values in the array will be FALSE, and the ninth value will be TRUE (since the 9^th row in the range is empty).

So the MATCH function will return the value of 9.  In this case, however, we really want to know how many entries are in the list, so the formula subtracts 1 from the MATCH value (which gives the position of the last entry).  So ultimately, MATCH(TRUE,the above,0)-1 returns the value of 8.

The next piece of the formula is the IFERROR function:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

The IFERROR function returns an alternate value, if the first value specified results in an error.  This function is included since, if the entire block of cells (all 20 rows) are filled with entries, the MATCH function will return an error.

This is because we’re telling the MATCH function to look for the first TRUE value (in the array of values from the ISBLANK function), but if NONE of the cells are empty, then the entire array will be filled with FALSE values.  If MATCH cannot find the target value (TRUE) in array it is searching, it returns an error.

So, if the entire list is full (and therefore, MATCH returns an error), the IFERROR function will instead return the value of 20 (knowing that there must be 20 entries in the list).

Finally, OFFSET(FruitsHeading,1,0,the above,1) returns the range we are actually looking for:  Start at the FruitsHeading cell, go down 1 row and over 0 columns, then select an area that is however many rows long as there are entries in the list (and 1 column wide).  So the entire formula together will return the range that contains only the actual entries (down to the first empty cell).

Using this formula to define the range that is the source for the dropdown means you can freely edit the list (adding or removing entries, as long as the remaining entries start at the top cell and are contiguous) and the dropdown will always reflect the current list (see Figure 6).

The example file (Dynamic Lists) that’s been used here is included and is downloadable from this website. The macros don’t work, however, because WordPress doesn’t like Excel books with macros in them.

As an alternative to specifying the number of rows in the list block, the list block can be assigned its own range name, which can then be used in a modified formula.  In the example file, a second list (Names) uses this method.  Here, the entire list block (underneath the “NAMES” heading, 40 rows in the example file) is assigned the range name of NameBlock.  The alternate formula for defining the NamesList is then:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

where NamesBlock replaces OFFSET(FruitsHeading,1,0,20,1) and ROWS(NamesBlock) replaces the 20 (number of rows) in the earlier formula.

So, for dropdown lists which can be easily edited (including by other users who may be inexperienced), try using dynamic range names!  And note that, although this article has been focused on dropdown lists, dynamic range names can be used anywhere you need to reference a range or list that can vary in size. Enjoy!

Related posts

• Jak usunąć puste linie w Excelu?

• Jak korzystać z funkcji Speak Cells programu Excel

• Jak wstawić arkusz programu Excel do dokumentu programu Word?

• Jak korzystać z analizy warunkowej programu Excel

• Jak naprawić wiersz w programie Excel

• Jak stworzyć prostą tabelę przestawną w programie Excel

• Jak wstawić CSV lub TSV do arkusza programu Excel?

• Jak napisać formułę/oświadczenie IF w programie Excel?

• Kiedy używać dopasowania indeksu zamiast funkcji WYSZUKAJ.PIONOWO w programie Excel

• Jak scalić komórki, kolumny i wiersze w programie Excel

• Korzystanie z narzędzia wyszukiwania celów analizy warunkowej w programie Excel

• Jak naprawić błędy #N/A w formułach programu Excel, takich jak WYSZUKAJ.PIONOWO

• Wyśrodkuj dane arkusza roboczego w programie Excel do drukowania

• Jak obliczyć Z-Score w Excelu

• Użyj klawiatury, aby zmienić wysokość wiersza i szerokość kolumny w programie Excel

• Jak udostępnić plik Excel w celu łatwej współpracy?

• Podstawowe jedno- i wielokolumnowe sortowanie danych w arkuszach kalkulacyjnych Excel

• 5 sposobów na konwersję tekstu na liczby w programie Excel

• Jak usunąć linie siatki w programie Excel

• Jak utworzyć makro lub skrypt VBA w programie Excel?