Formuła programu Excel: Dynamiczny nazwany zakres z INDEKSEM -

Spisie treści

Formuła ogólna

=$A$1:INDEX($A:$A,lastrow)

Podsumowanie

Jednym ze sposobów tworzenia dynamicznego nazwanego zakresu w programie Excel jest użycie funkcji INDEKS. W przedstawionym przykładzie nazwany zakres „dane” jest zdefiniowany za pomocą następującego wzoru:

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

co przekłada się na zakres $ A $ 2: $ A $ 10.

Uwaga: ta formuła ma na celu zdefiniowanie nazwanego zakresu, którego można używać w innych formułach.

Wyjaśnienie

Ta strona przedstawia przykład dynamicznego nazwanego zakresu utworzonego za pomocą funkcji INDEKS wraz z funkcją ILOŚĆ. Dynamiczne nazwane zakresy automatycznie rozszerzają się i kurczą po dodaniu lub usunięciu danych. Są alternatywą dla korzystania z tabeli programu Excel, która również zmienia rozmiar w miarę dodawania lub usuwania danych.

Funkcja INDEKS zwraca wartość z podanej pozycji w zakresie lub tablicy. Możesz użyć INDEKSU, aby pobrać pojedyncze wartości lub całe wiersze i kolumny w zakresie. To, co sprawia, że INDEKS jest szczególnie przydatny w przypadku dynamicznych nazwanych zakresów, to fakt, że zwraca on odwołanie. Oznacza to, że możesz użyć INDEKSU do skonstruowania mieszanego odniesienia, takiego jak $ A $ 1: A100.

W przedstawionym przykładzie nazwany zakres „dane” jest zdefiniowany za pomocą następującego wzoru:

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

co przekłada się na zakres $ A $ 2: $ A $ 10.

Jak działają te formuły

Zauważ najpierw, że ta formuła składa się z dwóch części, które znajdują się po obu stronach operatora zakresu (:). Po lewej stronie mamy początkowe odniesienie do zakresu, zakodowane na stałe jako:

$A$2

Po prawej stronie znajduje się końcowe odniesienie do zakresu, utworzone za pomocą INDEKSU w następujący sposób:

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

Tutaj wprowadzamy INDEKS do całej kolumny A dla tablicy, a następnie używamy funkcji ILOŚĆ, aby obliczyć „ostatni wiersz” w zakresie. COUNTA działa dobrze w tym przypadku, ponieważ w kolumnie A znajduje się 10 wartości, w tym wiersz nagłówka. COUNTA zwraca zatem 10, które trafia bezpośrednio do INDEKSU jako numer wiersza. INDEX zwraca następnie odniesienie do $ A 10, ostatniego używanego wiersza w zakresie:

INDEX($A:$A,10) // resolves to $A$10

Tak więc ostateczny wynik wzoru to ten zakres:

$A$2:$A$10

Zakres dwuwymiarowy

Powyższy przykład działa dla zakresu jednowymiarowego. Aby utworzyć dwuwymiarowy zakres dynamiczny, w którym liczba kolumn jest również dynamiczna, możesz zastosować to samo podejście, rozszerzone w następujący sposób:

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

Jak poprzednio, COUNTA jest używane do obliczenia „ostatniego rzędu”, a my ponownie używamy COUNTA, aby uzyskać „ostatnią kolumnę”. Są one dostarczane do indeksowania odpowiednio jako numer_wiersza i numer_kolumny.

Jednak w przypadku tablicy dostarczamy pełny arkusz roboczy, wprowadzony jako wszystkie 1048576 wierszy, co umożliwia INDEKSOWI zwrócenie odwołania w przestrzeni 2D.

Uwaga: Excel 2003 obsługuje tylko 65535 wierszy.

Określenie ostatniego rzędu

Istnieje kilka sposobów określenia ostatniego wiersza (ostatniej pozycji względnej) w zestawie danych, w zależności od struktury i zawartości danych w arkuszu: