Jak korzystać z funkcji PRZESUNIĘCIE programu Excel -

Podsumowanie

Funkcja Excel OFFSET zwraca odwołanie do zakresu zbudowanego z pięciu danych wejściowych: (1) punkt początkowy, (2) przesunięcie wiersza, (3) przesunięcie kolumny, (4) wysokość w wierszach, (5) szerokość w kolumny. OFFSET jest przydatny w formułach wymagających zakresu dynamicznego.

Cel, powód

Utwórz odsunięcie odniesienia od podanego punktu początkowego

Wartość zwracana

Odwołanie do komórki.

Składnia

= OFFSET (odwołanie, wiersze, kolumny, (wysokość), (szerokość))

Argumenty

  • odwołanie - punkt początkowy podany jako odwołanie do komórki lub zakres.
  • wiersze - liczba wierszy do przesunięcia poniżej początkowego odniesienia.
  • cols - liczba kolumn do przesunięcia w prawo od początkowego odwołania.
  • wysokość - (opcjonalnie) wysokość w wierszach zwróconego odwołania.
  • width - (opcjonalne) Szerokość w kolumnach zwróconego odwołania.

Wersja

Excel 2003

Uwagi dotyczące użytkowania

Funkcja Excel OFFSET zwraca zakres dynamiczny zbudowany z pięciu danych wejściowych: (1) punkt początkowy, (2) przesunięcie wiersza, (3) przesunięcie kolumny, (4) wysokość w wierszach, (5) szerokość w kolumnach.

Punktem początkowym ( argumentem odwołania ) może być jedna komórka lub zakres komórek. Argumenty wierszy i kolumn to liczba komórek do „przesunięcia” od punktu początkowego. Argumenty wysokości i szerokości są opcjonalne i określają rozmiar tworzonego zakresu. Gdy wysokość i szerokość są pominięte, domyślnie przyjmują wysokość i szerokość odniesienia .

Na przykład odwołanie do C5 zaczynające się od A1, odwołanie to A1, wiersze to 4, a kolumny to 2:

=OFFSET(A1,4,2) // returns reference to C5

Aby odwołać się do C1: C5 z A1, odwołanie to A1, wiersze to 0, kolumny to 2, wysokość to 5, a szerokość to 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Uwaga: szerokość można pominąć, ponieważ domyślnie wynosi 1.

ODSETEK jest często zawijany w innej funkcji, która oczekuje zakresu. Na przykład do SUMA C1: C5, zaczynając od A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Głównym celem OFFSET jest umożliwienie formułom dynamicznego dostosowywania się do dostępnych danych lub do danych wprowadzanych przez użytkownika. Funkcji PRZESUNIĘCIE można użyć do utworzenia dynamicznego nazwanego zakresu dla wykresów lub tabel przestawnych, aby zapewnić, że dane źródłowe są zawsze aktualne.

Uwaga: dokumentacja programu Excel stwierdza, że wysokość i szerokość nie mogą być ujemne, ale wydaje się, że wartości ujemne działają dobrze od wczesnych lat 90-tych. Funkcja PRZESUNIĘCIE w Arkuszach Google nie zezwala na wartości ujemne argumentów wysokości ani szerokości.

Przykłady

Poniższe przykłady pokazują, jak można skonfigurować OFFSET, aby zwracać różne rodzaje zakresów. Te ekrany zostały zrobione za pomocą programu Excel 365, więc PRZESUNIĘCIE zwraca tablicę dynamiczną, gdy wynik obejmuje więcej niż jedną komórkę. W starszych wersjach programu Excel można użyć klawisza F9, aby sprawdzić wyniki zwrócone przez PRZESUNIĘCIE.

Przykład 1

Na poniższym ekranie używamy OFFSET, aby zwrócić trzecią wartość (marzec) w drugiej kolumnie (zachód). Wzór w H4 to:

=OFFSET(B3,3,2) // returns D6

Przykład nr 2

Na poniższym ekranie używamy OFFSET, aby zwrócić ostatnią wartość (czerwiec) w trzeciej kolumnie (północ). Wzór w H4 to:

=OFFSET(B3,6,3) // returns E9

Przykład nr 3

Poniżej używamy OFFSET, aby zwrócić wszystkie wartości z trzeciej kolumny (północ). Wzór w H4 to:

=OFFSET(B3,1,3,6) // returns E4:E9

Przykład 4

Poniżej używamy OFFSET, aby zwrócić wszystkie wartości dla maja (piąty wiersz). Wzór w H4 to:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Przykład nr 5

Poniżej używamy OFFSET, aby zwrócić wartość z kwietnia, maja i czerwca dla regionu zachodniego. Wzór w H4 to:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Przykład nr 6

Poniżej używamy opcji OFFSET, aby zwrócić wartości z kwietnia, maja i czerwca dla zachodu i północy. Wzór w H4 to:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Uwagi

  • OFFSET zwraca tylko odwołanie, żadne komórki nie są przenoszone.
  • Zarówno wiersze, jak i kolumny można podać jako liczby ujemne, aby odwrócić ich normalny kierunek przesunięcia - ujemne kolumny przesunięte w lewo i ujemne przesunięcie wierszy powyżej.
  • PRZESUNIĘCIE jest „funkcją nietrwałą” - będzie obliczana ponownie przy każdej zmianie arkusza. Funkcje ulotne mogą sprawić, że większe i bardziej złożone skoroszyty będą działać wolniej.
  • OFFSET wyświetli #REF! wartość błędu, jeśli odsunięcie znajduje się poza krawędzią arkusza.
  • Jeśli pominięto wysokość lub szerokość, używana jest wysokość i szerokość odniesienia .
  • PRZESUNIĘCIE można używać z dowolną inną funkcją, która oczekuje na odniesienie.
  • Dokumentacja programu Excel mówi, że wysokość i szerokość nie mogą być ujemne, ale wartości ujemne działają.

Powiązane wideo

Jak utworzyć dynamiczny nazwany zakres za pomocą PRZESUNIĘCIA W tym nagraniu wideo przyjrzymy się, jak utworzyć dynamiczny nazwany zakres za pomocą funkcji PRZESUNIĘCIE, najczęstszego sposobu tworzenia dynamicznego zakresu nazw za pomocą formuły.

Interesujące artykuły...