PRZESUNIĘCIE w Excelu: kierunki, rozmiar zakresu i granice

PRZESUNIĘCIE, czyli angielskie OFFSET, zwraca odwołanie do komórki lub prostokątnego zakresu położonego względem punktu startowego. Ja czytam tę funkcję jako instrukcję: skąd zaczynam, ile wierszy i kolumn zmieniam, jaki rozmiar ma wynik. Żadne dane nie są przy tym przesuwane w arkuszu.

Najpierw poprawmy kierunki: dodatnia liczba wierszy prowadzi w dół, ujemna w górę. Dodatnia liczba kolumn prowadzi w prawo, ujemna w lewo. To reguły z dokumentacji Microsoft. W przykładach używam małej, syntetycznej tabeli.

Pięć argumentów i jeden punkt startowy

=PRZESUNIĘCIE(odwołanie;wiersze;kolumny;[wysokość];[szerokość])

Odwołanie wskazuje komórkę lub przylegający zakres. Kolejne dwa argumenty określają pozycję lewego górnego rogu wyniku. Wysokość i szerokość określają jego rozmiar w wierszach i kolumnach; dokumentacja wymaga dodatnich wymiarów. Jeśli pomijasz wymiar, dziedziczysz go z początkowego odwołania. Zakres dwuwierszowy nie staje się automatycznie jedną komórką.

Formuły używają polskich nazw i średników. W EN odpowiednikami są OFFSET, SUM, AVERAGE i INDEX; separator zależy od ustawień regionalnych. Funkcja działa między innymi w Microsoft 365 oraz Excelu 2016, 2019, 2021 i 2024. Najwygodniej zacząć od wskazywania adresów na kartce, a dopiero potem łączyć przesunięcie z sumowaniem.

Mała tabela pozwala wskazać wynik ręcznie

Wpisz dane w A1:C5. Pierwszy wiersz zawiera nagłówki, a kolejne cztery rekordy dotyczą umownych okresów. Wartości są syntetyczne, w jednej wspólnej jednostce.

=PRZESUNIĘCIE(B2;1;1)

Z B2 idziesz jeden wiersz w dół do B3 i jedną kolumnę w prawo do C3. Odwołanie wskazuje C3, a w pojedynczej komórce formuły otrzymujesz wartość 18. Wynik nie zależy od wartości 10 w B2: ta komórka jest punktem geometrycznym, nie kwotą do dodania.

Dla =PRZESUNIĘCIE(C3;-1;-1) wracasz do B2 i wartości 10. Z kolei =PRZESUNIĘCIE(C3;0;0) zachowuje C3 i daje 18. Zero oznacza brak przesunięcia w danym kierunku. Rozpisanie tych trzech przypadków ułatwia wykrycie odwróconych znaków, zanim pojawią się w większym modelu.

Zakres zwrócony do sumy

=SUMA(PRZESUNIĘCIE($C$2;2;0;2;1))

Początek C2 przesuwa się o dwa wiersze do C4. Wysokość 2 i szerokość 1 tworzą zakres C4:C5. Znajdują się tam 33 oraz 44, więc suma wynosi 77. Funkcja zewnętrzna przyjmuje odwołanie i wykonuje na nim obliczenie. W tym samym układzie średnia tych dwóch wartości wynosi 38,5.

Samo zwrócenie zakresu i wyświetlenie go w siatce to osobna kwestia. Zachowanie tablicowe zależy od wersji i miejsca wpisania formuły. Tutaj opakowuję wielokomórkowy wynik w SUMA, żeby cel był jednoznaczny i nie zależał od wolnego miejsca na rozlanie. Microsoft pokazuje używanie OFFSET jako argumentu funkcji oczekującej odwołania.

Pominięte wymiary zachowują rozmiar źródła

=SUMA(PRZESUNIĘCIE(B2:C3;1;0))

Początkowe odwołanie ma dwa wiersze i dwie kolumny. Po przesunięciu o jeden wiersz wynik nadal ma rozmiar dwa na dwa: B3:C4. Suma to 20 + 18 + 30 + 33, czyli 101. To ważna różnica względem założenia, że brak wysokości i szerokości zawsze oznacza jedną komórkę.

Przy przeglądzie formuły zapisuję więc pełny zakres startowy. Jeśli ktoś zmienił go z B2 na B2:C3, mógł zmienić rozmiar wyniku bez dotykania argumentów wymiarów. Oglądanie wyłącznie dwóch liczb przesunięcia nie wystarcza. Punkt początkowy i jego kształt wspólnie określają działanie.

Ostatnie N okresów przy znanej liczbie rekordów

W F1 wpisz całkowite N od 1 do 4. Przy czterech kolejnych, kompletnych rekordach w C2:C5 można opisać okno tak:

=SUMA(PRZESUNIĘCIE($C$2;4-$F$1;0;$F$1;1))

Dla N równego 2 początek przesuwa się o dwa wiersze, a wysokość wynosi dwa: znowu C4:C5 i 77. Dla N równego 1 otrzymujesz C5 i 44. Dla N równego 4 zakres obejmuje C2:C5 i sumę 107. Ten przykład ma jawne założenie czterech rekordów; liczba 4 nie wykrywa automatycznie dalszych dopisków.

W prawdziwym formularzu ogranicz N do dozwolonych liczb całkowitych i ustal kolejność okresów. „Ostatnie” oznacza tutaj ostatnie wiersze, a nie automatycznie najnowsze daty. Jeśli sortowanie jest inne albo brakuje okresu, trzeba najpierw naprawić źródło i definicję okna. Funkcja przesuwa adres, bez rozumienia kalendarza i kompletności serii.

Nie zastępuję bezmyślnie liczby rekordów przez ILE.NIEPUSTYCH całej kolumny. Nagłówek lub notatka też są niepuste, a liczba niepustych komórek nie wskazuje ostatniego zajętego wiersza przy lukach. Zera, puste komórki i formuły dające pusty tekst również wymagają uzgodnienia. Często wygodniej przechowywać dopisywane dane w tabeli Excela.

Granice arkusza i koszt przeliczania

=PRZESUNIĘCIE(A1;-1;0) próbowałoby wskazać wiersz nad pierwszym wierszem arkusza i daje #ADR!, czyli #REF! w EN. Ten sam rodzaj problemu powstaje, gdy szerokość lub wysokość wyprowadza wynik poza krawędź. Odwołanie do pustej, ale istniejącej komórki samo w sobie nie oznacza wyjścia poza arkusz. Zanim ukryjesz błąd, ustal położenie wyniku.

PRZESUNIĘCIE jest funkcją niestabilną: uczestniczy w każdym przeliczeniu, także gdy jej bezpośrednie argumenty nie uległy zmianie. Nie oznacza to, że pojedyncza formuła musi być wolna. Przy wielu takich odwołaniach rośnie jednak zakres pracy silnika. Microsoft opisuje niestabilność OFFSET i INDIRECT. Nie przedstawiam ADR.POŚR jako automatycznej optymalizacji, bo również jest niestabilna.

Dla pojedynczego pobrania C3 z tej tabeli równoważne wskazanie można zbudować jako =INDEKS(A1:C5;3;3). Zakres ma trzy kolumny, więc trzeci indeks mieści się w jego szerokości. Formuła INDEKS(A:A;3;3) nie byłaby poprawnym zamiennikiem, ponieważ A:A ma tylko jedną kolumnę. Wybór INDEKS, tabeli lub zwykłego odwołania zależy od potrzeb; nie obiecuję przewagi czasowej bez pomiaru konkretnego skoroszytu.

Ćwiczenie z kontrolą adresu

Wyprowadź zakres dla PRZESUNIĘCIE(B2;1;0;2;2): to B3:C4, a suma powinna wynieść 101. Następnie przestaw N w F1 na 3. Okno ostatnich trzech rekordów to C3:C5 i suma 95. Przy każdym wyniku zapisz najpierw adres, dopiero potem wartość. Taki porządek pozwala odróżnić błąd przesunięcia od błędu w danych, do których trafiasz.