Szybki kontakt




Funkcja PRZESUNIĘCIE() umożliwia modyfikację odwołania. Możemy utworzyć odwołanie do komórki, przesunięte o podaną liczbę wierszy i kolumn względem komórki stanowiącej odwołanie pierwotne.

W angielskiej wersji Excela funkcja znajduje się pod nazwą OFFSET.

Excel funkcja PRZESUNIĘCIE() argumenty

Argumenty funkcji:

odwołanie – adres pojedynczej komórki lub zakresu sąsiadujących ze sobą komórek, który będzie modyfikowany przez przesunięcie o wskazaną liczbę wierszy i kolumn

wiersze – liczba wierszy, o które zostanie zmienione pierwotne odwołanie. Możliwe są trzy opcje:

  • liczba dodatnia oznacza przesunięcie w dół
  • liczba ujemna oznacza przesunięcie w górę
  • zero oznacza pozostawienie wiersza odwołania bez zmian

kolumny – liczba kolumn, o które zostanie zmienione pierwotne odwołanie. Możliwe są trzy opcje:

  • liczba dodatnia oznacza przesunięcie w prawo
  • liczba ujemna oznacza przesunięcie w lewo
  • zero oznacza pozostawienie kolumny odwołania bez zmian

wysokość – argument opcjonalny. Oznacza liczbę wierszy zmodyfikowanego zakresu. Domyślnie jest równy liczbie wierszy pierwotnego (modyfikowanego) odwołania.

szerokość – argument opcjonalny. Oznacza liczbę kolumn zmodyfikowanego zakresu. Domyślnie jest równy liczbie kolumn pierwotnego (modyfikowanego) odwołania.

Jeśli chcemy sumować dane z określonego zakresu, musimy przesunąć odniesienie na początek zakresu, który będzie sumowany. Excel oblicza formułę od lewej do prawej strony, zgodnie z określoną kolejnością operatorów zastosowanych w formule.

Excel funkcja PRZESUNIĘCIE() przykłady

Przykład 1
„Startujemy” z komórki B300 – jest to nasz punkt odniesienia, który będziemy modyfikować. Jako drugi argument podajemy -1 i przesuwamy się o jeden wiersz w górę. Następnie jako argument wpisujemy 1 i przesuwamy się o jedną kolumnę w prawo. Argumenty opcjonalne „wysokość” i „szerokość” pomijamy. W wyniku działania funkcji otrzymamy wynik 2. Jest to wartość z komórki C299, do której się przesunęliśmy.

Excel funkcja PRZESUNIĘCIE() przykład 1

Przykład 2
„Startujemy” z komórki E301 – jest to nasz punkt odniesienia, który będziemy modyfikować. Jako drugi argument podajemy -2 i przesuwamy się o dwa wiersze w górę. Następnie jako argument wpisujemy -3 i przesuwamy się o trzy kolumny w lewo. Chcemy zsumować komórki znajdujące się w zakresie od B299 do C300. Jako wysokość podajemy więc 2 (dwa wiersze), a jako szerokość 2 (2 kolumny). Dodatkowo zagnieżdżamy funkcję PRZESUNIĘCIE() w funkcji SUMA().

Excel funkcja PRZESUNIĘCIE() przykład 2

Uwaga! Jeśli zmodyfikowane odwołanie wykracza poza arkusz, funkcja PRZESUNIĘCIE() zwraca błąd #ADR!.

Przykład 3
Chcemy przygotować raport, który będzie się aktualizował po wpisaniu daty. W raporcie chcemy podawać wartość sprzedaży z podanego miesiąca oraz narastającą.

Excel funkcja PRZESUNIĘCIE() raport

W komórce D351 wpisuję datę – jest to miesiąc, którego raport dotyczy.

Ustalenie wartości sprzedaży z bieżącego miesiąca
W komórce D354 wpisuję funkcję, za pomocą której „pobiorę” wartość sprzedaży z podanego miesiąca. Jako punkt startowy (pierwotne odniesienie) podaję adres komórki D354. W drugim argumencie wpisuję 0, gdyż jestem we właściwym wierszu, nie muszę zmieniać odniesienia odnośnie wiersza. W trzecim argumencie zagnieżdżam funkcję MIESIĄC(). Funkcja ta, zwróci numer miesiąca. W moim przykładzie będzie to 3. Odniesienie przesunie się zatem o 3 kolumny w prawo. Jako wysokość i szerokość podaję 1, gdyż potrzebuję dane z jednej komórki.

Ustalenie wartości sprzedaży narastająco
W komórce C354 wpisuję funkcję, za pomocą której wyliczę sumę sprzedaży narastająco. Jako „punkt startowy” (pierwotne odwołanie) podaję adres komórki D354. W drugim argumencie wpisuję 0, gdyż jestem we właściwym wierszu, nie muszę zmieniać odniesienia odnośnie wiersza. W trzecim argumencie wpisuję wartość 1, gdyż chcę przesunąć odniesienie o jedną kolumnę w prawo (sumowanie narastająco zaczynam od stycznia). Jako wysokość podaję wartość 1, gdyż sumowane będą wartości z jednego tylko wiersza. Szerokość wyliczam za pomocą funkcji MIESIĄC(). W podanym przykładzie funkcja MIESIĄC() zwróci wartość 3. Szerokość będzie zatem wynosiła 3 i obejmę sumą dane z trzech kolumn.

Funkcję PRZESUNIĘCIE() zagnieżdżam w funkcji SUMA(), aby policzyć sprzedaż narastająco.

Przykład 4
Chcemy odwoływać się do zakresów, których z góry nie znamy, aby np. sumować dopisywane dane.

Excel funkcja PRZESUNIĘCIE() - zakres dynamiczny

Sumowanie ilości sprzedaży
Jako „punkt startowy” (pierwotne odwołanie) wybieram adres komórki D10. Przesuwam odwołanie o 0 wierszy i 0 kolumn, gdyż jestem we właściwej komórce. Komórka D10 stanowi początek zakresu, który chcę sumować. Wysokość, czyli ilość wierszy, które mają być zsumowane określam za pomocą funkcji ILE.NIEPUSTYCH(). Jako argument funkcji ILE.NIEPUSTYCH() podaję zakres od komórki C10 do C1048576 (ostatnia komórka w kolumnie C).

Funkcja ILE.NIEPUSTYCH() zwraca informację, ile w zaznaczonym zakresie danych znajduje się niepustych komórek. W przykładzie zwrócona jest wartość 3, która odpowiada ilości wierszy koniecznych do zsumowania.

W argumencie „szerokość” wpisuję 1, gdyż tylko jedna kolumna ma podlegać sumowaniu.

Sumowanie wartości sprzedaży
Wartość sprzedaży sumują analogicznie. Jedyna różnica to pierwotne odwołanie, które w tym przypadku znajduje się w komórce E10.

Po dopisaniu kolejnego wiersza danych, zostaną one uwzględnione w wyniku sumowania.

Dodaj komentarz

Twój adres email nie zostanie opublikowany.