Jak używać funkcji OFFSET (PRZESUNIĘCIE) w Excelu

Polskie Angielski
PRZESUNIĘCIE OFFSET

Funkcja PRZESUNIĘCIE, dostępna w Microsoft Excel oraz Google Sheets, jest niezwykle potężnym narzędziem umożliwiającym dynamiczne odwoływanie się do różnych segmentów arkusza danych. Pozwala ona na tworzenie formuł, które adaptują zwracane dane w odpowiedzi na określone kryteria, co jest kluczowe w analizach danych, gdzie struktura danych może się zmieniać.

Opis i składnia funkcji

Funkcja PRZESUNIĘCIE zwraca referencję do zakresu, który jest przesunięty o określoną liczbę wierszy i kolumn względem punktu początkowego (podanego zakresu).

  =PRZESUNIĘCIE(referencja, przesunięcie_wierszy, przesunięcie_kolumn, [wysokość], [szerokość])  
  • referencja: Adres początkowego zakresu, od którego ma być przesunięty nowy zakres.
  • przesunięcie_wierszy: Liczba wierszy, o którą zakres jest przesuwany względem punktu początkowego. Może być dodatnia (przesunięcie w dół) lub ujemna (przesunięcie w górę).
  • przesunięcie_kolumn: Liczba kolumn, o którą zakres jest przesuwany względem punktu początkowego. Może być dodatnia (przesunięcie w prawo) lub ujemna (przesunięcie w lewo).
  • wysokość [opcjonalnie]: Opcjonalna liczba wierszy nowego zakresu.
  • szerokość [opcjonalnie]: Opcjonalna liczba kolumn nowego zakresu.

Przykład zastosowania

Przyjmijmy, że dysponujemy tabelą z miesięcznymi wynikami sprzedaży w kolumnie B od B2 do B13 i chcemy obliczyć sumę sprzedaży za drugi kwartał (kwiecień-czerwiec), umieszczając wynik bezpośrednio obok.

  =SUMA(PRZESUNIĘCIE(B2,3,0,3))  

W tym przykładzie:

  • Referencją jest komórka B2.
  • Przesunięcie względem wierszy wynosi 3, co przesuwa zakres do komórki B5, czyli kwietnia.
  • Przesunięcie względem kolumn wynosi 0, co oznacza brak przesunięcia w poziomie.
  • Nowy zakres zawiera 3 wiersze, co obejmuje miesiące kwiecień, maj i czerwiec.

Dynamiczne tworzenie tabeli przestawnej

Posiadając tabelę z danymi osobowymi (imię, nazwisko, data urodzenia) możemy dynamicznie przeglądać dane od pierwszej osoby urodzonej w danym miesiącu.

  =PRZESUNIĘCIE(A2,SZUKAJ.PIONOWO("Jan",A2:A100,0)-1,0,1,3)  

W tym przypadku:

  • Szukamy komórki z osobą o imieniu „Jan”.
  • PRZESUNIĘCIE rozpoczyna się od A2 i przesuwa o liczbę wierszy równą pozycji Jana minus 1.
  • Zakres po przesunięciu obejmuje 1 wiersz i 3 kolumny, odpowiadając pełnym danym o Janie.

Funkcja PRZESUNIĘCIE jest niezwykle elastyczna i pozwala na dostosowanie wyświetlanych danych bez konieczności manualnej zmiany formuł, co jest nieocenione w dynamicznie zmieniających się arkuszach danych.

Więcej informacji: https://support.microsoft.com/pl-pl/office/przesunięcie-funkcja-c8de19ae-dd79-4b9b-a14e-b4d906d11b66

Inne funkcje
Zwraca odwołanie określone przez wartość tekstową
Zwraca odwołanie do jednej komórki w arkuszu jako wartość tekstową
Pobiera dane w czasie rzeczywistym z programu obsługującego automatyzację COM
Filtrowanie zakresu danych na podstawie zdefiniowanych kryteriów
Zwraca formułę w danym odwołaniu jako tekst
Tworzy skrót lub skok, który pozwala otwierać dokument przechowywany na serwerze sieciowym, w sieci intranet lub w Internecie
Zwraca liczbę wierszy dla danego odwołania
Używa indeksu do wybierania wartości z odwołania lub tablicy
Zwraca liczbę kolumn w odwołaniu
Zwraca numer kolumny z odwołania
Zwraca liczbę obszarów występujących w odwołaniu
Wyszukuje wartości w odwołaniu lub w tablicy
Sortowanie zawartości zakresu lub tablicy
Sortuje zawartość zakresu lub tablicy na podstawie wartości z odpowiedniego zakresu lub tablicy
Zwraca transponowaną tablicę
Zwraca listę unikatowych wartości na liście lub w zakresie
Zwraca dane przechowywane w raporcie w formie tabeli przestawnej
Zwraca numer wiersza odwołania
Wybiera wartość z listy wartości
Wyszukuje wartości w wektorze lub tablicy
Przeszukuje pierwszą kolumnę tablicy i przechodzi wzdłuż wiersza, aby zwrócić wartość komórki
Przegląda górny wiersz tablicy i zwraca wartość wskazanej komórki
Umożliwia przeszukanie zakresu lub tablicy i zwrócenie elementu odpowiadającego pierwszej znalezionej wartości Jeśli dopasowanie nie istnieje, XLOOKUP może zwrócić najbliższy (przybliżony) odpowiednik
Zwraca względną pozycję elementu w tablicy lub zakresie komórek