przewyższać OFFSET Funkcjonować

Excel Funkcja OFFSET zwraca odwołanie do komórki lub zakresu komórek odsuniętych od określonej komórki o określoną liczbę wierszy i kolumn.

Składnia

=OFFSET (reference, rows, cols, [height], [width])

Argumenty

Wartość zwracana

Funkcja PRZESUNIĘCIE zwróci przesunięcie odwołania do komórki od podanego punktu początkowego.

Uwagi dotyczące funkcji

1. # WARTOŚĆ! wartość błędu zostanie zwrócona, gdy odwołanie odwołuje się do zakresu nieciągłych komórek.
2. #REF! wartość błędu zostanie zwrócona, gdy wiersze i kolumny przesuną odniesienie poza krawędź arkusza.

Przykłady

Przykład 1: Podstawowe użycie funkcji PRZESUNIĘCIE

Zwróć odwołanie do komórki za pomocą poniższej formuły:
=OFFSET(B2,3,1)

W tym przypadku B2 jest punktem początkowym, cyfry 3 i 1 oznaczają, że należy przenieść 3 wiersze w dół i 1 kolumnę w prawo od komórki B2, a ostatecznie zwrócić wartość w C5. Zobacz zrzut ekranu:

Zwróć odwołanie do zakresu komórek za pomocą poniższej formuły:
=OFFSET(B2,3,1,2,2)

W takim przypadku otrzymasz wyniki z zakresu 2 x 2, czyli 3 wiersze poniżej i 1 kolumna po prawej stronie komórki B2.

Uwagi: # WARTOŚĆ! Błąd wystąpi, gdy wybierzesz tylko jedną komórkę, aby zastosować funkcję PRZESUNIĘCIE w celu zwrócenia zakresu komórek. Musisz wybrać zakres 2 x 2 (mówi 4 puste komórki), wprowadzić formułę i nacisnąć Ctrl + Shift + Wchodzę klucze, aby uzyskać wyniki.

Przykład 2: Użyj funkcji PRZESUNIĘCIE, aby zsumować zakres wartości

Jak wspomnieliśmy powyżej, jeśli spróbujesz użyć funkcji OFFSET, takiej jak =OFFSET(B2,3,1,2,2) samodzielnie w jednej komórce, zwróci #ARG! Błąd. Jeśli jednak połączysz funkcję SUMA i PRZESUNIĘCIE, jak pokazano na poniższym zrzucie ekranu, zwróci ona bezpośrednio sumę wartości z zakresu C5: D6.

1. Wybierz pustą komórkę, skopiuj do niej poniższą formułę i naciśnij Wchodzę aby uzyskać wynik.
=SUM(OFFSET(B2,3,1,2,2)))

Przykład 3: Suma kolumn na podstawie określonych kryteriów

Jak pokazano na poniższym zrzucie ekranu, jak uzyskać całkowitą sprzedaż Mango od słońca do wtorek? Spróbuj jak poniżej.

1. Wybierz pustą komórkę, skopiuj do niej poniższą formułę i naciśnij Wchodzę aby uzyskać wynik.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Uwagi:

1. W powyższym wzorze MATCH(G4,B3:B8) szuka Mango i wraca do swojego zasięgu B3: B8. Zobaczmy, Mango lokalizuje 5 wierszy poniżej komórki B2, więc zwraca liczbę 5;
2. Ponieważ wynik MATCH to 5, a ostatni przecinek reprezentuje 0, funkcja OFFSET jest teraz wyświetlana jako OFFSET(C2:E2,5,0), co oznacza, że ​​przesunięcie zaczyna się od zakresu C2: E2, przesuń 5 wierszy w dół i 0 kolumnę w prawo, aby uzyskać odniesienie do zakresu C7: E7;
3. Następnie funkcja SUMA wyświetla się jako SUM(C7:E7)i na koniec zwróć sumę wartości z zakresu C7: E7.

Więcej przykładów

Jak zsumować co n wierszy w programie Excel?

Jak utworzyć dynamiczny nazwany zakres w programie Excel?

Jak zwrócić wartość komórki co pięć lub n-ty wiersz w programie Excel?