Jak utworzyć dynamicznie zdefiniowany zakres w programie Excel

Dane w programie Excel często się zmieniają, dlatego warto stworzyć dynamicznie zdefiniowany zakres, który automatycznie dostosowuje się do aktualnych danych. Przyjrzyjmy się, jak to zrobić.

Dzięki zastosowaniu dynamicznych zakresów, nie musisz ręcznie aktualizować zakresów formuł, wykresów czy tabel przestawnych po każdej zmianie danych – wszystko odbywa się automatycznie.

Do tworzenia dynamicznych zakresów wykorzystuje się dwie funkcje: PRZESUNIĘCIE oraz INDEKS. W tym artykule skoncentrujemy się na funkcji INDEKS, gdyż jest to bardziej efektywne podejście. Warto zauważyć, że OFFSET może być niestabilny i spowalniać działanie większych arkuszy kalkulacyjnych.

Jak utworzyć dynamicznie zdefiniowany zakres w Excelu

W naszym pierwszym przykładzie posługujemy się listą danych zawierającą jedną kolumnę, którą prezentujemy poniżej.

Naszym celem jest uczynienie tego zakresu dynamicznym, tak aby w przypadku dodania lub usunięcia krajów, zakres aktualizował się automatycznie.

W tym przypadku chcemy zdefiniować zakres od A2 do A6, pomijając komórkę nagłówka. Aby to zrobić, kliknij na zakładkę Formuły, a następnie wybierz opcję Zdefiniuj nazwę.

W polu „Nazwa” wpisz „kraje”, a następnie w polu „Odnosi się do” wprowadź poniższy wzór:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

Wprowadzenie tej formuły bezpośrednio w arkuszu kalkulacyjnym i skopiowanie jej do pola Nowa nazwa może być szybsze i prostsze.

Jak to działa?

Pierwsza część formuły wskazuje na komórkę początkową zakresu (w naszym przypadku A2), a następnie używamy operatora zakresu (:).

=$A$2:

Operator zakresu zmusza funkcję INDEKS do zwrócenia zakresu, a nie pojedynczej wartości. Funkcja INDEKS współpracuje z funkcją COUNTA, która liczy niepuste komórki w kolumnie A (szesnaście w naszym przypadku).

INDEX($A:$A,COUNTA($A:$A))

Ta formuła poleca funkcji INDEKS, aby zwróciła zakres ostatniej niepustej komórki w kolumnie A (6 $ A $).

Ostateczny wynik to $ A $ 2: $ A $ 6, a dzięki użyciu funkcji COUNTA zakres staje się dynamiczny i automatycznie znajduje ostatni wiersz. Teraz możesz używać zdefiniowanej nazwy „kraje” w regułach walidacji danych, formułach, wykresach lub wszędzie tam, gdzie potrzebujesz odwołać się do listy wszystkich krajów.

Tworzenie dwukierunkowego dynamicznego zakresu

Pierwszy przykład dotyczył jedynie dynamicznej wysokości. Dzięki niewielkiej modyfikacji oraz dodatkowej funkcji COUNTA możemy stworzyć dynamiczny zakres, który dostosowuje się zarówno w pionie, jak i w poziomie.

W tym przykładzie wykorzystamy dane przedstawione poniżej.

Tym razem utworzymy dynamicznie zdefiniowany zakres, który również obejmuje nagłówki. Aby to zrobić, kliknij na zakładkę Formuły, a następnie wybierz Zdefiniuj nazwę.

W polu „Nazwa” wpisz „sprzedaż” i wprowadź poniższy wzór w polu „Odnosi się do”:

=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

W tej formule używamy $ A $ 1 jako komórki początkowej. Funkcja INDEKS przeszukuje cały arkusz roboczy (od 1 do 1048576) w celu zwrócenia odpowiednich wartości.

Jedna z funkcji COUNTA zlicza niepuste wiersze, podczas gdy druga zlicza niepuste kolumny, co czyni zakres dynamicznym w obu kierunkach. Mimo że formuła zaczyna się od A1, można wskazać dowolną komórkę początkową.

Od teraz możesz używać tej zdefiniowanej nazwy (sprzedaż) w formułach lub jako serii danych wykresu, aby uczynić je dynamicznymi.