Funkcja Excel OFFSET (spis treści)

  • PRZESUNIĘCIE w Excelu
  • Formuła OFFSET w Excelu
  • Jak korzystać z funkcji OFFSET w Excelu?

PRZESUNIĘCIE w Excelu

Funkcja PRZESUNIĘCIE w programie Excel pomaga użytkownikowi zwrócić komórkę lub zakres komórek, który jest określony nie. wierszy i kolumn z komórki. Możemy określić nr. rzędów i nie. kolumn do zwrócenia.

Poniżej znajduje się formuła OFFSET w programie Excel:

Funkcja PRZESUNIĘCIA w programie Excel składa się z następujących argumentów:

  • Odniesienie: Jest to argument, na podstawie którego chcemy oprzeć przesunięcie. Może to być odwołanie do komórki lub zakres sąsiednich komórek, jeśli nie, Offset zwraca wartość # WARTOŚĆ! Wartość błędu
  • Rzędy: To jest nie. wierszy do odsunięcia. Jeśli użyjemy liczby dodatniej, spowoduje to przesunięcie do wierszy poniżej, a jeśli zostanie użyta liczba ujemna, to nastąpi przesunięcie wierszy powyżej.
  • Kolumny: To za nie. kolumn do odsunięcia. Tak więc jest to ta sama koncepcja, co w przypadku wierszy, jeśli użyjemy liczby dodatniej, wówczas zostanie przesunięta do kolumn po prawej stronie, a jeśli zostanie użyta liczba ujemna, wówczas jej przesunięcia do kolumn po lewej stronie.
  • Wysokość: ten argument jest opcjonalny. Powinien to być liczba dodatnia. Jest to liczba reprezentująca liczbę wierszy w zwróconym odwołaniu.
  • Szerokość: jest to również argument opcjonalny. To musi być liczba dodatnia. Jest to liczba reprezentująca liczbę kolumn w zwróconym odwołaniu.

Uwagi:

  • Funkcja PRZESUNIĘCIE w programie Excel zwraca wartość #REF! wartość błędu, jeśli wiersze i kolumny odsuną odwołanie na krawędź arkusza.
  • W funkcji PRZESUNIĘCIE powinna mieć tę samą wysokość lub szerokość co wartość odniesienia, jeśli wysokość lub szerokość zostaną pominięte.
  • PRZESUNIĘCIE zwraca odwołanie, w rzeczywistości nie przenosi żadnych komórek ani zakresu komórek ani nie zmienia zaznaczenia. Można go używać z dowolną funkcją oczekującą argumentu referencyjnego.

Jak korzystać z funkcji OFFSET w Excelu?

Funkcja PRZESUNIĘCIA w programie Excel jest bardzo prosta i łatwa w użyciu. Rozumiem działanie funkcji PRZESUNIĘCIE w programie Excel na przykładzie formuły PRZESUNIĘCIE.

Możesz pobrać ten szablon Excel funkcji OFFSET tutaj - Szablon Excel funkcji OFFSET

Przykład 1

Formuła przesunięcia zwraca odwołanie do komórki na podstawie określonego przez nas punktu początkowego, wierszy i kolumn. Widzimy to w podanym poniżej przykładzie:

= PRZESUNIĘCIE (A1, 3, 1)

Formuła mówi Excelowi, aby wziął pod uwagę komórkę A1 jako punkt początkowy (odniesienie), a następnie przesunął 3 rzędy w dół (rzędy) i 1 kolumnę w lewo (argument kolumny). Następnie formuła OFFSET zwraca wartość w komórce B4.

Zdjęcie po lewej pokazuje, że trasa funkcji i zrzut ekranu po prawej pokazują, jak możemy użyć formuły OFFSET na podstawie danych w czasie rzeczywistym. Różnica między dwiema formułami polega na tym, że druga (po prawej) zawiera odwołanie do komórki (D1) w argumencie wiersza. Ale ponieważ komórka D1 zawiera liczbę 1, a dokładnie ta sama liczba pojawia się w argumencie wierszy pierwszej formuły, oba zwrócą identyczny wynik - wartość w B2.

Przykład # 2 Wykorzystanie funkcji OFFSET dla sumy

Jak wiemy, funkcja OFFSET może być również używana z inną funkcją Excela, dlatego w tym przykładzie zobaczymy z funkcją SUMA.

Na powyższym zdjęciu, załóżmy, że musimy znaleźć sumę ocen, to możemy również użyć funkcji OFFSET.

Wzór jest poniżej:

Tak więc w powyższym wzorze D5 jest pierwszą komórką, w której zaczynają się dane, która jest referencją jako komórka początkowa. Następnie wartość wierszy i kolumn wynosi 0, więc możemy ustawić ją jako 0, 0. Następnie są 4, co oznacza 4 wiersze poniżej odniesienia, dla których Excel musi obliczyć sumę, a następnie, w końcu, jest to 1, co oznacza, że ​​jest 1 kolumna jako szerokość.

Możemy odwołać się do powyższego obrazu, aby odnieść się do wyjaśnionego przykładu.

A poniżej jest wynik:

Po prostu musimy nacisnąć Enter po wprowadzeniu formuły w wymaganej komórce, a następnie mamy wynik.

O czym należy pamiętać o funkcji PRZESUNIĘCIE w programie Excel

  • Funkcja PRZESUNIĘCIA w programie Excel po prostu zwraca odwołanie, w rzeczywistości nie przenosi żadnych komórek ani zakresu komórek.
  • Jeśli formuła OFFSET zwraca zakres komórek, wiersze i kolumny zawsze odnoszą się do lewej górnej komórki w zwróconym zakresie.
  • Odwołanie powinno zawierać komórkę lub zakres komórek, w przeciwnym razie formuła zwróci błąd # WARTOŚĆ.
  • Jeśli wiersze i kolumny określone ponad krawędzią arkusza kalkulacyjnego, formuła przesunięcia w programie Excel zwróci wartość #REF! błąd.
  • Z funkcji OFFSET można korzystać w dowolnej innej funkcji Excela, która akceptuje odwołanie do komórki lub zakresu w swoich argumentach.

Na przykład, jeśli używamy formuły = PRZESUNIĘCIE (A1, 3, 1, 1, 3), daje ona # WARTOŚĆ! Błąd, ponieważ zakres zwrotu (1 wiersz, 3 kolumny) nie mieści się w komórce. Jeśli jednak zestawimy go w funkcję SUM, jak poniżej:

= SUMA (PRZESUNIĘCIE (A1, 3, 1, 1, 3))

Formuła Przesunięcie zwraca sumę wartości w 1 rzędzie z zakresu 3 kolumn, czyli 3 wiersze poniżej i 1 kolumna po prawej stronie komórki A1, tj. Suma wartości w komórkach B4: D4.

Jak widzimy, suma 36 + 63 + 82 podświetlonego Mar jest równa 181, co przyniosło wynik w G2 po zastosowaniu wzoru sumy i przesunięcia. (podświetlony na żółto)

Korzystanie z funkcji PRZESUNIĘCIE w programie Excel

Jak widzieliśmy, co właściwie robi funkcja PRZESUNIĘCIE za pomocą powyższego przykładu, możemy mieć pytania, dlaczego zawracać sobie głowę śpiewaną formułą PRZESUNIĘCIE, która jest nieco złożona, dlaczego po prostu nie użyć bezpośredniego odwołania, takiego jak suma lub B4: D4?

Funkcja PRZESUNIĘCIA w programie Excel jest bardzo dobra dla poniższych:

  • Uzyskiwanie zasięgu z komórki początkowej:

Czasami nie znamy dokładnego lub faktycznego adresu zakresu, wiemy tylko, skąd zaczyna się od określonej komórki. W takim przypadku możemy użyć funkcji OFFSET, która jest łatwa w obsłudze.

  • Tworzenie zakresu dynamicznego:

Odniesienia jak w powyższym przykładzie B1: C4 są statyczne, co oznacza, że ​​zawsze odnoszą się do określonego lub stałego zakresu. Ale w niektórych przypadkach zadania są łatwiejsze do wykonania z zakresami dynamicznymi. Jest to szczególnie pomocne, gdy pracujemy ze zmianami danych. Na przykład możemy mieć arkusz kalkulacyjny, w którym nowy wiersz lub kolumna jest wstawiany lub dodawany codziennie / tydzień / miesiąc, w takim przypadku funkcja OFFSET pomoże.

Ograniczenia i alternatywy funkcji PRZESUNIĘCIE w programie Excel

Ponieważ każda formuła w programie Excel ma własne ograniczenia i alternatywy, widzieliśmy, jak i kiedy używać funkcji PRZESUNIĘCIE w programie Excel.

Poniżej znajdują się krytyczne ograniczenia funkcji PRZESUNIĘCIE w programie Excel:

  • Funkcja wymagająca dużej ilości zasobów:

Oznacza to, że ilekroć nastąpi zmiana danych źródłowych, formuła zostanie ponownie obliczona dla całego zestawu, dzięki czemu program Excel będzie zajęty przez dłuższy czas. Jeśli jest mały arkusz kalkulacyjny, nie wpłynie to tak bardzo, ale jeśli istnieje wiele arkuszy kalkulacyjnych, ponowne obliczenie może zająć Excel.

  • Trudność w przeglądzie:

Jak wiemy, referencje zwracane przez funkcję Offset są dynamiczne lub zmieniają się i mogą zawierać dużą formułę, co może być trudne do poprawienia lub edycji zgodnie z wymaganiami.

Alternatywy funkcji OFFSET w Excelu:

  • Funkcja INDEKS:

Funkcji INDEKS w programie Excel można również użyć do utworzenia dynamicznego zakresu odniesień. To nie jest dokładnie to samo co OFFSET. Ale podobnie jak OFFSET, funkcja INDEX nie jest tak bardzo zmienna, dlatego nie spowolni programu Excel, który jest ograniczeniem dla OFFSET.

  • Funkcja POŚREDNIE:

Możemy również użyć funkcji POŚREDNIE, aby utworzyć dynamiczny zakres referencji z wielu źródeł, takich jak wartości komórek , tekst i nazwane zakresy.

Polecane artykuły

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

  1. MS Excel: Funkcja WYSZUKAJ
  2. Najlepszy przykład funkcji Excel XIRR
  3. Niesamowity przewodnik po funkcji NIE w Excelu
  4. IPMT w programie Excel z przykładami

Kategoria:

Porady Excel