Formuła programu Excel: Dynamiczny nazwany zakres z PRZESUNIĘCIEM -

Formuła ogólna

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

Podsumowanie

Jednym ze sposobów tworzenia dynamicznego nazwanego zakresu z formułą jest użycie funkcji PRZESUNIĘCIE razem z funkcją ILOŚĆ. Zakresy dynamiczne są również znane jako zakresy rozszerzające - automatycznie rozszerzają się i kurczą, aby pomieścić nowe lub usunięte dane.

Uwaga: PRZESUNIĘCIE jest funkcją zmienną, co oznacza, że przelicza ją przy każdej zmianie arkusza. W przypadku nowoczesnej maszyny i mniejszego zestawu danych nie powinno to powodować problemu, ale może wystąpić wolniejsza wydajność w przypadku dużych zestawów danych. W takim przypadku rozważ zamiast tego zbudowanie dynamicznego nazwanego zakresu z funkcją INDEKS.

W przedstawionym przykładzie formuła użyta do określenia zakresu dynamicznego to:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Wyjaśnienie

Ta formuła używa funkcji PRZESUNIĘCIE w celu wygenerowania zakresu, który rozszerza się i zawęża, dostosowując wysokość i szerokość na podstawie liczby niepustych komórek.

Pierwszy argument funkcji PRZESUNIĘCIE reprezentuje pierwszą komórkę w danych (źródło), którą w tym przypadku jest komórka B5. Kolejne dwa argumenty to przesunięcia dla wierszy i kolumn i są podawane jako zero.

Ostatnie dwa argumenty reprezentują wysokość i szerokość. Wysokość i szerokość są generowane w locie za pomocą funkcji COUNTA, co powoduje, że wynikowe odniesienie jest dynamiczne.

W przypadku wysokości używamy funkcji COUNTA, aby policzyć niepuste wartości z zakresu B5: B100. Zakłada to brak pustych wartości w danych i żadnych wartości poza B100. COUNTA zwraca 6.

W przypadku szerokości używamy funkcji COUNTA, aby policzyć niepuste wartości w zakresie B5: Z5. Zakłada to brak komórek nagłówka i żadnych nagłówków poza Z5. COUNTA zwraca 6.

W tym momencie formuła wygląda następująco:

=OFFSET(B5,0,0,6,6)

Dzięki tym informacjom funkcja OFFSET zwraca odniesienie do B5: G10, co odpowiada zakresowi wysokości 6 wierszy i 6 kolumn w poprzek.

Uwaga: zakresy wysokości i szerokości należy dostosować, aby pasowały do układu arkusza.

Odmiana z pełnymi odwołaniami do kolumn / wierszy

Możesz również użyć pełnych odwołań do kolumn i wierszy dla wysokości i szerokości, na przykład:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Zwróć uwagę, że wysokość jest dostosowywana za pomocą -2, aby uwzględnić wartości nagłówka i tytułu w komórkach B4 i B2. Zaletą tego podejścia jest prostota zakresów wewnątrz COUNTA. Wadą jest ogromna wielkość pełnych kolumn i wierszy - należy uważać, aby nie dopuścić do błędnych wartości poza zakresem, ponieważ mogą one łatwo zmienić wynik.

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: