Jak utworzyć dynamicznie zdefiniowany zakres w programie Excel

Dane programu Excel często się zmieniają, dlatego warto utworzyć dynamicznie zdefiniowany zakres, który automatycznie rozszerza się i zmniejsza do rozmiaru zakresu danych. Zobaczmy, jak to zrobić.

Korzystając z dynamicznie zdefiniowanego zakresu, nie musisz ręcznie edytować zakresów formuł, wykresów i tabel przestawnych w przypadku zmiany danych. Stanie się to automatycznie.

Do tworzenia dynamicznych zakresów służą dwie formuły: PRZESUNIĘCIE i INDEKS. W tym artykule skupimy się na używaniu funkcji INDEKS, ponieważ jest to bardziej wydajne podejście. OFFSET to niestabilna funkcja, która może spowolnić duże arkusze kalkulacyjne.

Utwórz dynamicznie zdefiniowany zakres w programie Excel

W naszym pierwszym przykładzie mamy listę danych z jedną kolumną widoczną poniżej.

Zakres danych, który ma być dynamiczny

Musimy to być dynamiczne, aby w przypadku dodania lub usunięcia większej liczby krajów zakres był automatycznie aktualizowany.

W tym przykładzie chcemy uniknąć komórki nagłówka. W związku z tym chcemy, aby zakres 2 $ A: 6 $ A, ale dynamiczny. Zrób to, klikając Formuły> Zdefiniuj nazwę.

Utwórz zdefiniowaną nazwę w programie Excel

Wpisz „kraje” w polu „Nazwa”, a następnie wprowadź wzór poniżej w polu „Odnosi się do”.

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

Wpisanie tego równania w komórce arkusza kalkulacyjnego, a następnie skopiowanie go do pola Nowa nazwa jest czasami szybsze i łatwiejsze.

Używanie formuły w zdefiniowanej nazwie

Jak to działa?

Pierwsza część formuły określa komórkę początkową zakresu (w naszym przypadku A2), a następnie operator zakresu (:).

=$A$2:

Użycie operatora zakresu wymusza na funkcji INDEKS zwrócenie zakresu zamiast wartości komórki. Funkcja INDEKS jest następnie używana z funkcją ILOŚĆ. COUNTA zlicza liczbę niepustych komórek w kolumnie A (sześć w naszym przypadku).

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

Ta formuła żąda od funkcji INDEKS zwrócenia zakresu ostatniej niepustej komórki w kolumnie A (6 $ A $).

Ostateczny wynik to $ A $ 2: $ A $ 6, a dzięki funkcji COUNTA jest dynamiczny, ponieważ znajdzie ostatni wiersz. Możesz teraz używać tej zdefiniowanej nazwy „krajów” w regule sprawdzania poprawności danych, formule, wykresie lub wszędzie tam, gdzie musimy odwołać się do nazw wszystkich krajów.

Utwórz dwukierunkowy dynamiczny zakres zdefiniowany

Pierwszy przykład miał tylko dynamiczną wysokość. Jednak dzięki niewielkiej modyfikacji i innej funkcji COUNTA można utworzyć zakres dynamiczny zarówno pod względem wysokości, jak i szerokości.

W tym przykładzie będziemy używać danych przedstawionych poniżej.

Dane dla dwukierunkowego zakresu dynamiki

Tym razem utworzymy dynamicznie zdefiniowany zakres, który obejmuje nagłówki. Kliknij opcję Formuły> Zdefiniuj nazwę.

Utwórz zdefiniowaną nazwę w programie Excel

Wpisz „” sprzedaż ”w polu„ Nazwa ”i wprowadź poniższy wzór w polu„ Odnosi się do ”.

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

Dwukierunkowa dynamiczna formuła zdefiniowanego zakresu

Ta formuła używa $ A $ 1 jako komórki początkowej. Następnie funkcja INDEKS używa zakresu całego arkusza roboczego (1 USD: 1048576 USD) do przeszukiwania i zwracania.

Jedna z funkcji COUNTA służy do zliczania niepustych wierszy, a inna do niepustych kolumn, dzięki czemu jest dynamiczna w obu kierunkach. Chociaż ta formuła zaczęła się od A1, można było określić dowolną komórkę początkową.

Możesz teraz użyć tej zdefiniowanej nazwy (sprzedaży) w formule lub jako serii danych wykresu, aby uczynić je dynamicznymi.