Funkcja PRZESUNIĘCIA Excel (przykład + wideo)
Kiedy używać funkcji PRZESUNIĘCIA Excel?
Funkcja Excel OFFSET może być użyta, gdy chcesz uzyskać odwołanie, które przesunie określoną liczbę wierszy i kolumn od punktu początkowego.
Co zwraca
Zwraca odwołanie, na które wskazuje funkcja OFFSET.
Składnia
=OFFSET(odniesienie;wiersze;kolumny;[wysokość];[szerokość])
Argumenty wejściowe
- odniesienie - Odniesienie, od którego chcesz dokonać przesunięcia. Może to być odwołanie do komórki lub zakres sąsiednich komórek.
- wiersze - liczba wierszy do przesunięcia. Jeśli użyjesz liczby dodatniej, zostanie ona przesunięta do wierszy poniżej, a jeśli zostanie użyta liczba ujemna, to przesunie się do wierszy powyżej.
- kol. - liczba kolumn do przesunięcia. Jeśli użyjesz liczby dodatniej, zostanie ona przesunięta do kolumn po prawej stronie, a jeśli zostanie użyta liczba ujemna, to przesunie się do kolumn po lewej stronie.
- [wzrost] - jest to liczba reprezentująca liczbę wierszy w zwróconym odwołaniu.
- [szerokość] - jest to liczba reprezentująca liczbę kolumn w zwróconym odwołaniu.
Zrozumienie podstaw funkcji PRZESUNIĘCIE programu Excel
Funkcja PRZESUNIĘCIE Excela jest prawdopodobnie jedną z najbardziej mylących funkcji w Excelu.
Weźmy prosty przykład gry w szachy. W szachach jest pion zwany wieżą (znany również jako wieża, markiz lub rektor).
[Zdjęcie dzięki uprzejmości: Wspaniała Wikipedia]
Teraz, jak wszystkie inne figury szachowe, wieża ma ustaloną ścieżkę, po której może poruszać się po planszy. Może iść prosto (w prawo/w lewo lub w górę/w dół planszy), ale nie może iść po przekątnej.
Załóżmy na przykład, że mamy szachownicę w Excelu (jak pokazano poniżej), w danym polu (w tym przypadku D5) wieża może poruszać się tylko w czterech podświetlonych kierunkach.
Teraz, jeśli poproszę cię o przeniesienie wieży z jej aktualnej pozycji na tę podświetloną na żółto, co powiesz wieży?
Poprosisz go, aby zrobił dwa kroki w dół i dwa kroki w prawo… prawda?
I to jest bliskie przybliżenie działania funkcji OFFSET.
Zobaczmy teraz, co to oznacza w programie Excel. Chcę zacząć od komórki D5 (w której znajduje się wieża), a następnie przejść dwa wiersze w dół i dwie kolumny w prawo i pobrać wartość z komórki.
Formuła byłaby:
= PRZESUNIĘCIE(od czego zacząć, ile wierszy w dół, ile kolumn w prawo)
Jak widać, formuła w powyższym przykładzie w komórce J1 to = PRZESUNIĘCIE (D5,2,2).
Zaczęło się od D5, a następnie przeszło dwa rzędy w dół i dwie kolumny w prawo i dotarło do komórki F7. Następnie zwrócił wartość w komórce F7.
W powyższym przykładzie przyjrzeliśmy się funkcji PRZESUNIĘCIE z 3 argumentami. Ale są jeszcze dwa opcjonalne argumenty, których można użyć.
Spójrzmy na prosty przykład:
Załóżmy, że chcesz użyć odwołania do komórki A1 (na żółto) i chcesz odwołać się do całego zakresu podświetlonego na niebiesko (C2:E4) w formule.
Jak byś to zrobił używając klawiatury? Najpierw przejdź do komórki C2, a następnie zaznacz wszystkie komórki w C2:E4.
Zobaczmy teraz, jak to zrobić za pomocą formuły PRZESUNIĘCIE:
=PRZESUNIĘCIE(A1,1,2,3,3)
Jeśli użyjesz tej formuły w komórce, zwróci ona #ARG! błąd, ale jeśli przejdziesz do trybu edycji i wybierzesz formułę i naciśniesz F9, zobaczysz, że zwraca wszystkie wartości podświetlone na niebiesko.
Zobaczmy teraz, jak działa ta formuła:
=PRZESUNIĘCIE(A1,1,2,3,3)
- Pierwszym argumentem jest komórka, od której powinien się zaczynać.
- Drugi argument to 1, co oznacza, że program Excel ma zwrócić odwołanie, które zostało PRZESUNIĘTE o 1 wiersz.
- Trzeci argument to 2, co oznacza, że program Excel ma zwrócić odwołanie, które zostało PRZESUNIĘTE o 2 kolumny.
- Czwartym argumentem jest 3. Oznacza to, że odwołanie powinno obejmować 3 wiersze. Nazywa się to argumentem wysokości.
- Piątym argumentem jest 3. Oznacza to, że odwołanie powinno obejmować 3 kolumny. Nazywa się to argumentem szerokości.
Teraz, gdy masz odwołanie do zakresu komórek (C2:E4), możesz go użyć w innych funkcjach (takich jak SUMA, LICZBA, MAKS, ŚREDNIA).
Mamy nadzieję, że dobrze rozumiesz korzystanie z funkcji PRZESUNIĘCIE programu Excel. Przyjrzyjmy się teraz kilku praktycznym przykładom wykorzystania funkcji PRZESUNIĘCIE.
Funkcja przesunięcia Excela - przykłady
Oto dwa przykłady użycia funkcji PRZESUNIĘCIE Excela.
Przykład 1 - znajdowanie ostatniej wypełnionej komórki w kolumnie
Załóżmy, że masz jakieś dane w kolumnie, jak pokazano poniżej:
Aby znaleźć ostatnią komórkę w kolumnie, użyj następującej formuły:
=PRZESUNIĘCIE(A1;LICZBA(A:A)-1,0)
Ta formuła zakłada, że poza pokazanymi nie ma żadnych wartości, a komórki te nie zawierają pustej komórki. Działa poprzez zliczanie całkowitej liczby wypełnionych komórek i odpowiednio przesuwa komórkę A1.
Na przykład w tym przypadku jest 8 wartości, więc LICZBA(A:A) zwraca 8. Przesuwamy komórkę A1 o 7, aby uzyskać ostatnią wartość.
Przykład 2 - Tworzenie dynamicznego menu rozwijanego
Można rozszerzyć koncepcję pokazów w Przykładzie 1, aby utworzyć dynamiczne nazwane zakresy. Mogą być przydatne, jeśli używasz nazwanych zakresów w formułach lub podczas tworzenia list rozwijanych i prawdopodobnie będziesz dodawać/usuwać dane z odwołania, które tworzy nazwany zakres.
Oto przykład:
Pamiętaj, że lista rozwijana dostosowuje się automatycznie po dodaniu lub usunięciu roku.
Dzieje się tak, ponieważ formuła używana do tworzenia listy rozwijanej jest dynamiczna i identyfikuje każde dodanie lub usunięcie oraz odpowiednio dostosowuje zakres.
Oto jak to zrobić:
- Wybierz komórkę, w której chcesz wstawić listę rozwijaną.
- Przejdź do Dane -> Narzędzia danych -> Walidacja danych.
- W oknie dialogowym Sprawdzanie poprawności danych na karcie Ustawienia wybierz z listy rozwijanej opcję Lista.
- W źródle wprowadź następującą formułę: =OFFSET(A1,0,0,COUNT(A:A),1)
- Kliknij OK.
Zobaczmy, jak działa ta formuła:
- Pierwsze trzy argumenty funkcji PRZESUNIĘCIE to A1, 0 i 0. Zasadniczo oznacza to, że zwróci tę samą komórkę odniesienia (czyli A1).
- Czwarty argument dotyczy wysokości, a tutaj funkcja ILE.LICZB zwraca całkowitą liczbę pozycji na liście. Zakłada, że na liście nie ma pustych miejsc.
- Piątym argumentem jest 1, co oznacza, że szerokość kolumny powinna wynosić jeden.
Dodatkowe uwagi:
- OFFSET to funkcja ulotna (należy zachować ostrożność).
- Oblicza się ponownie za każdym razem, gdy skoroszyt programu Excel jest otwarty lub za każdym razem, gdy obliczenia są uruchamiane w arkuszu. Może to wydłużyć czas przetwarzania i spowolnić skoroszyt.
- Jeśli wartość wysokości lub szerokości zostanie pominięta, jest traktowana jako wartość odniesienia.
- Jeśli wydziwianie oraz kol są liczbami ujemnymi, to kierunek przesunięcia jest odwrócony.
Alternatywy funkcji PRZESUNIĘCIE Excel
Ze względu na pewne ograniczenia funkcji PRZESUNIĘCIE Excela, wielu chce rozważyć alternatywy dla niej:
- Funkcja INDEKS: Funkcja INDEKS może również służyć do zwracania odwołania do komórki. Kliknij tutaj, aby zobaczyć przykład tworzenia dynamicznego nazwanego zakresu za pomocą funkcji INDEX.
- Tabela Excel: Jeśli używasz odwołań strukturalnych w tabeli Excel, nie musisz się martwić o dodawanie nowych danych i konieczność dostosowania formuł.
Funkcja przesunięcia Excel - samouczek wideo
- Funkcja WYSZUKAJ.PIONOWO Excela.
- Funkcja WYSZUKAJ.POZIOMO Excela.
- Funkcja INDEKS Excela.
- Excel ADR.Funkcja.
- Funkcja PODAJ.POZYCJĘ Excela.
