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.
Spis treści:
Utwórz dynamicznie zdefiniowany zakres w programie Excel
W naszym pierwszym przykładzie mamy listę danych z jedną kolumną widoczną poniżej.
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ę.
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.
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.
Tym razem utworzymy dynamicznie zdefiniowany zakres, który obejmuje nagłówki. Kliknij opcję Formuły> Zdefiniuj nazwę.
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))
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.