Formuła Excel OFFSET (spis treści)

  • Co to jest formuła OFFSET w programie Excel?
  • Jak korzystać z formuły OFFSET w programie Excel?

Co to jest formuła OFFSET w programie Excel?

Formuła PRZESUNIĘCIE w programie Excel daje komórkę lub zakres dynamicznych prostokątnych komórek jako wynik, który jest podzbiorem całego podanego zakresu z określoną liczbą wierszy, kolumn, wysokości i szerokości.

Składnia formuły OFFSET w programie Excel

Objaśnienie funkcji PRZESUNIĘCIE w programie Excel:

  • odniesienie: Punktem wyjścia dla OFFSET lub możemy powiedzieć zakres komórek, które można uznać za podstawę dla OFFSET.
  • wiersze : liczba wierszy do PRZESUNIĘCIA poniżej lub powyżej odniesienia podstawowego.
  • cols: liczba kolumn do PRZESUNIĘCIA po prawej lub lewej stronie odniesienia podstawowego.
  • wysokość: liczba wierszy w zwróconym odwołaniu.
  • szerokość: liczba kolumn w zwróconym odwołaniu.

Wysokość i szerokość są parametrami opcjonalnymi dla tej funkcji i mogą być użyte do określenia wielkości utworzonego odwołania.

Jak korzystać z formuły OFFSET w programie Excel?

Nauczymy się teraz pisać formułę przesunięcia, aby uzyskać wartość wyszukiwania. Przyjrzyjmy się formule przesunięcia z kilkoma przykładami w Excelu.

Możesz pobrać szablon OFFSET Formula Excel tutaj - OFFSET Formula Excel

Formuła Excel OFFSET - przykład nr 1

Załóżmy, że mamy dane podane poniżej.

  • W komórce E2 wprowadź następującą formułę:

= PRZESUNIĘCIE (A1, 11, 1)

  • Po zakończeniu z formułą naciśnij klawisz Enter i zobacz wynik.

To, co zrobiła ta formuła, wzięło odwołanie z komórki A1 (Rok) i przesunęło się w dół o 11 wierszy po A1 (tj. Wiersz nr 12), a następnie uzyskało wartość, która jest współrzędną 11. rzędu po A1 i pierwszej kolumnie ( kolumna A = 0 w tym przypadku kolumna B = 1. Jak wiemy, możemy przejść zarówno w prawo, jak i w lewo). To jest 141, 27 miliona dolarów.

Formuła Excel OFFSET - przykład nr 2

Teraz zobaczymy inny przykład komórki referencyjnej na określony rok. Umieść następującą formułę w komórce E3:

= PRZESUNIĘCIE (A1, 11, 0)

  • Po zakończeniu z formułą naciśnij klawisz Enter i zobacz wynik.

Jeśli widziałeś formułę, jest ona taka sama jak poprzednia, jedyną zmianą jest odwołanie do kolumny. Odniesienie do kolumny 0 (zero) oznacza, że ​​wartość zostanie przechwycona z kolumny A dla 11. rzędu po A1 (czyli 2010).

Formuła Excel OFFSET - przykład nr 3

W tym przykładzie obliczymy sumę ostatnich N wierszy za pomocą funkcji Excel OFFSET. Ogólna formuła sumowania ostatnich N liczb to:

= SUMA (PRZESUNIĘCIE (A1, LICZBA (A: A), 0, -N))

Aby podsumować ostatnie N obserwacji z danych, możesz użyć funkcji SUMA wraz z funkcją PRZESUNIĘCIA i LICZENIA jednocześnie. W tym przykładzie zsumujemy ostatnie 5 punktów danych. Pamiętaj, że gdy mówimy N, jest to uogólnienie dowolnej wartości całkowitej. Możesz więc obliczyć dla ostatnich 3, ostatnich 4, ostatnich 5 itd. W zależności od zestawu danych oraz wymagań.

Zróbmy to dla naszego zestawu danych:

  • Wprowadź następującą formułę w komórce E2 pliku programu Excel.

= SUMA (PRZESUNIĘCIE (B1, LICZBA (B: B), 0, -5))

  • Kliknij Enter, aby zobaczyć wynik.

Jak już widzieliśmy, funkcja Excel OFFSET generuje prostokątne zakresy dynamiczne, gdy podano początkowe odniesienie.

  • W tym przypadku odniesienie początkowe podano w kategoriach B1 (jako pierwszy argument funkcji OFFSET).
  • Użyliśmy funkcji LICZNIK, ponieważ potrzebujemy sumy obserwacji od ostatniej wartości. Funkcja COUNT pomaga PRZESUNIĘCIOWI określić liczbę obserwacji (wierszy) w kolumnie B. Zlicza wszystkie wartości liczbowe w kolumnie B (19 to liczba).
  • Przy liczbie 19 formuła OFFSET staje się:

= PRZESUNIĘCIE (B1, 19, 0, -5)

  • Ta formuła rozpoczyna kompensację w B1 z 19 wierszami i tą samą kolumną (0). Następnie parametr wysokości -5 pomaga tej funkcji rozszerzyć zakres do tyłu o 5 rzędów (patrz znak ujemny związany z 5).
  • Zakres dynamiczny ostatnich pięciu obserwacji podano jako dane wejściowe do funkcji sumowania. Który daje wynik sumowania tych z kolumny B.

Formuła Excel OFFSET - przykład 4

W tym przykładzie obliczymy medianę sprzedaży za ostatnie 3 lata. Funkcja OFFSET może być użyta jako argument do obliczenia tego samego.

  • Umieść następującą formułę w komórce E2:

= MEDIAN (PRZESUNIĘCIE (B1, LICZBA (B: B), 0, -3))

  • Naciśnij Enter, aby zobaczyć wynik.

Ta formuła działa na liniach podobnych do poprzedniego.

  • PRZESUNIĘCIE Tworzy zakres dynamiczny ostatnich trzech obserwacji z kolumny B za pomocą funkcji LICZNIK (= PRZESUNIĘCIE (A1, 19, 0, -3)).
  • Można to podać jako wynik funkcji MEDIAN. Który następnie sortuje obserwacje w kolejności rosnącej lub malejącej, a następnie wybiera środkową najwyższą wartość, jak widać na wyjściu (212, 07 USD).

Formuła Excel OFFSET - przykład # 5

Tutaj obliczymy medianę roku za pomocą funkcji MEDIAN i OFFSET.

  • Użyj tej formuły dla tego samego w komórce E3:

= MEDIAN (PRZESUNIĘCIE (A1, LICZBA (A: A), 0, -3))

  • Kliknij Enter, aby zobaczyć wynik.

To samo stało się tutaj. Formuła przyjęła zakres dynamiczny utworzony przez funkcję PRZESUNIĘCIA przy pomocy COUNT. I użył go jako argumentu dla funkcji MEDIAN, która następnie posortowała wartości roku w porządku rosnącym lub malejącym, a następnie dała wynik jako 2017.

Zastosowanie formuły OFFSET polega na tworzeniu zakresów dynamicznych. Zawsze lepiej jest pracować na zakresach dynamicznych niż statycznych. Ponieważ możesz mieć dane, które są dodawane co jakiś czas. Na przykład Mistrz pracownika. Twoje Dane podstawowe pracownika zawsze będą się zwiększać od czasu do czasu, ponieważ musi dołączać kilku nowych pracowników. Za każdym razem stosowanie tej samej formuły nie ma żadnego sensu. Zamiast tego utwórz zakres dynamiczny za pomocą funkcji OFFSET i zobacz magię. Z pewnością pozwoli ci to zaoszczędzić czas.

Rzeczy do zapamiętania

  • Funkcja PRZESUNIĘCIE zwraca tylko odwołanie do określonego zakresu komórek w programie Excel. Nie przenosi komórek z ich pierwotnych pozycji.
  • Domyślnie ta funkcja działa w górnej części (pod względem wierszy) i po prawej stronie (pod względem kolumn). Można jednak podać argumenty negatywne, aby odwrócić domyślną funkcjonalność. Podobnie jak -negatywny argument wiersza pomaga przesunąć dane od dołu do góry, a ujemny argument col pomaga przesunąć dane w lewo.
  • Formuła OFFSET może spowolnić funkcjonalność programu Excel, ponieważ jest to formuła, która jest ponownie obliczana za każdym razem, gdy następuje zmiana w arkuszu.
  • Błąd #REF zostanie zgłoszony, jeśli twój zakres jest poza zakresem skoroszytu.
  • Każda inna funkcja, która oczekuje odniesienia, może w nim korzystać z funkcji PRZESUNIĘCIA.

Polecane artykuły

Jest to przewodnik po formule OFFSET w programie Excel. Tutaj omawiamy, jak korzystać z funkcji OFFSET w Excelu, wraz z praktycznymi przykładami i szablonem Excel do pobrania. Możesz także przejrzeć nasze inne sugerowane artykuły -

  1. Jak zawijać tekst w programie Excel?
  2. Prosty przewodnik po sortowaniu w programie Excel
  3. Funkcja WYSZUKAJ w Excelu
  4. Jak korzystać z funkcji ZNAJDŹ w Excelu?

Kategoria:

Porady Excel