=PRZESUNIĘCIE(A1;3;2) (po angielsku OFFSET) zwraca komórkę 3 wiersze niżej i 2 kolumny dalej niż A1, czyli C4. Podaj jej też wysokość i szerokość, a zwróci cały zakres; do tego PRZESUNIĘCIE służy najczęściej: do sum i średnich z zakresu, który się przesuwa albo rośnie. Tabele pokazują formuły po angielsku, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=PRZESUNIĘCIE(A1;E2;F2)3 wiersze w dół i 2 w prawo od A1 to C4, cena Carrot, $0.80. Ustaw Cols na 0, aby dostać nazwę Carrot, albo Rows na 5, aby trafić na wiersz Milk. Wiersze i kolumny mogą być ujemne, aby przesunąć się w górę albo w lewo, a wyjście poza górną krawędź lub brzeg arkusza daje #ADR! (po angielsku #REF!).
Składnia funkcji PRZESUNIĘCIE
=OFFSET(reference, rows, cols, [height], [width])
reference(odw): komórka startowa (albo zakres).rows,cols(wiersze, kolumny): o ile się przesunąć. 0 oznacza pozostanie w miejscu.height,width(wysokość, szerokość): rozmiar zwracanego zakresu, liczony od komórki, do której się przesunięto. Pominięte przyjmują rozmiarreference.
Sama w komórce PRZESUNIĘCIE zwracająca kilka komórek rozlewa się w Excelu 365; starsze wersje zwykle pokazują #ARG! (#VALUE!). Tabele na tej stronie pokazują angielskie nazwy błędów. W SUMA, ŚREDNIA, ILE.LICZB albo MAX działa jako zakres.
Suma ostatnich N wierszy
Klasyczne zadanie dla PRZESUNIĘCIE: suma, która zawsze obejmuje najnowsze wiersze, bez względu na to, ile ich dopisano. ILE.LICZB ustala, ile jest wartości, PRZESUNIĘCIE przesuwa się w dół do pierwszej z ostatnich N, a wysokość bierze N wierszy.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=SUMA(PRZESUNIĘCIE(B1;ILE.LICZB(B2:B13)-E2+1;0;E2;1))Wartości jest 7, więc PRZESUNIĘCIE zaczyna 7-3+1, czyli 5 wierszy pod B1, w B6, i bierze 3 wiersze: od May do Jul, 14,900. W polskim Excelu formuła to =SUMA(PRZESUNIĘCIE(B1;ILE.LICZB(B2:B13)-E2+1;0;E2;1)). Wpisz 4900 w B9 (sierpień), a suma przesunie się na Jun, Jul i Aug, bo ILE.LICZB znajdzie teraz 8. Zakres B2:B13 zostawia miejsce na resztę roku. Kolumna nie może mieć pustych komórek w środku, inaczej ILE.LICZB policzy za mało, a okno trafi w złe miejsce.
Średnia krocząca
PRZESUNIĘCIE z ujemnym przesunięciem wierszy, skopiowana w dół kolumny, daje każdemu wierszowi okno z wierszy nad nim: tutaj średnią z bieżącego miesiąca i dwóch poprzednich.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=ŚREDNIA(PRZESUNIĘCIE(B4;-2;0;3;1))C4 liczy średnią z B2:B4 (od Jan do Mar), 4,300. W polskim Excelu formuła to =ŚREDNIA(PRZESUNIĘCIE(B4;-2;0;3;1)). Każdy wiersz niżej przesuwa okno o jeden. Zmień 3 na 6, a -2 na -5, aby dostać średnią sześciomiesięczną (wtedy zacznij formułę w wierszu 7). Akurat ten przypadek nie wymaga PRZESUNIĘCIE: =ŚREDNIA(B2:B4) skopiowane w dół od C4 robi to samo, bo odwołania względne i tak się przesuwają. PRZESUNIĘCIE przydaje się, gdy rozmiar okna pochodzi z komórki.
Dlaczego INDEKS bywa lepszym wyborem
PRZESUNIĘCIE jest nietrwała: Excel przelicza każdą funkcję PRZESUNIĘCIE po każdej edycji w dowolnym miejscu skoroszytu, bo nie wie z góry, na które komórki wskaże. Arkusz z tysiącami takich formuł zaczyna zwalniać. INDEKS też zwraca odwołanie, a zakres zapisany jako start:INDEKS(...) rośnie tak samo, nie będąc nietrwałym:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
W polskim Excelu: =SUMA(PRZESUNIĘCIE(B2;0;0;E2;1)) i =SUMA(B2:INDEKS(B2:B13;E2)). Obie czytają pierwsze E2 wierszy kolumny. PRZESUNIĘCIE trudniej też sprawdzić: Śledź poprzedniki i kolorowe obrysy, które Excel rysuje podczas edycji formuły, pokazują komórkę startową i argumenty, a nie zakres, który PRZESUNIĘCIE ostatecznie zwraca. Używaj PRZESUNIĘCIE w szybkim modelu albo w zakresie wykresu; w dużych skoroszytach wybieraj INDEKS. Strona o INDEKS mówi więcej o zwracaniu zakresów, a ADR.POŚR to druga nietrwała funkcja odwołań.
Ćwiczenie: suma pierwszych N miesięcy
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Twoja kolej: W F2 użyj PRZESUNIĘCIE wewnątrz SUMA, aby zsumować pierwsze N miesięcy, gdzie N jest w E2.
Najczęściej zadawane pytania
Co robi funkcja PRZESUNIĘCIE w Excelu?
Zwraca odwołanie oddalone o podaną liczbę wierszy i kolumn od komórki startowej, opcjonalnie o zmienionym rozmiarze. =PRZESUNIĘCIE(A1;3;2) to komórka 3 wiersze niżej i 2 kolumny w prawo od A1, czyli C4.
Jak zsumować ostatnie N wierszy w Excelu?
Zacznij od nagłówka i przesuń się w dół do pierwszej z ostatnich N wartości: =SUMA(PRZESUNIĘCIE(B1;ILE.LICZB(B2:B100)-N+1;0;N;1)). ILE.LICZB ustala, ile jest wartości, a wysokość N bierze tyle wierszy. Działa to tylko wtedy, gdy kolumna nie ma przerw.
Dlaczego PRZESUNIĘCIE jest funkcją nietrwałą?
Excel przelicza każdą funkcję PRZESUNIĘCIE po każdej zmianie w skoroszycie, bo komórki, na które wskazuje, znane są dopiero po jej wykonaniu. W dużych skoroszytach to spowalnia pracę. Zakres zbudowany z INDEKS, na przykład B2:INDEKS(B2:B100;N), robi to samo bez bycia nietrwałym.
Jakie argumenty ma PRZESUNIĘCIE?
PRZESUNIĘCIE(odw; wiersze; kolumny; [wysokość]; [szerokość]): komórka startowa, ile wierszy w dół (wartość ujemna to w górę), ile kolumn w prawo (ujemna to w lewo) i opcjonalnie rozmiar zwracanego zakresu.
