Pobierz przykładowy skoroszyt
Ten samouczek pokazuje, jak używać Funkcja przesunięcia Excel w programie Excel, aby utworzyć przesunięcie odniesienia z początkowej komórki.
PRZESUNIĘCIE Przegląd funkcji
Funkcja PRZESUNIĘCIE Rozpoczyna się od zdefiniowanego odwołania do komórki i zwraca odwołanie do komórki o określoną liczbę wierszy i kolumn przesuniętych względem pierwotnego odwołania. Odwołania mogą być jedną komórką lub zakresem komórek. Przesunięcie umożliwia również zmianę rozmiaru odniesienia o określoną liczbę wierszy/kolumn.
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
IFERROR Składnia i wejścia funkcji:
| 1 | =PRZESUNIĘCIE(odniesienie;wiersze;kolumny;wysokość;szerokość) |
referencja – początkowe odwołanie do komórki, od którego chcesz dokonać przesunięcia.
wydziwianie – liczba wierszy do przesunięcia.
kol – liczba kolumn do przesunięcia.
wzrost - OPCJONALNIE: Dostosuj liczbę wierszy w referencji.
szerokość - OPCJONALNIE: Dostosuj liczbę kolumn w odwołaniu.
Co to jest funkcja PRZESUNIĘCIE?
Funkcja PRZESUNIĘCIE jest jedną z potężniejszych funkcji arkusza kalkulacyjnego, ponieważ może być dość wszechstronna w tym, co tworzy. Daje użytkownikowi możliwość zdefiniowania komórki lub zakresu w różnych pozycjach i rozmiarach.
UWAGA: Funkcja OFFSET jest jedną z funkcji ulotnych. W większości przypadków, gdy pracujesz w arkuszu kalkulacyjnym, komputer ponownie obliczy formułę tylko wtedy, gdy dane wejściowe zmieniły swoje wartości. Jednak funkcja lotna przelicza każdy kiedy dokonasz zmiany w dowolnej komórce. Należy zachować ostrożność, aby upewnić się, że nie spowodujesz długiego czasu ponownego obliczenia z powodu nadmiernego użycia funkcji lotnej lub uzależnienia wielu komórek od wyniku funkcji ulotnej.
Podstawowe przykłady wierszy
Przy każdym użyciu funkcji OFFSET należy podać punkt początkowy, czyli kotwicę. Spójrzmy na tę tabelę, aby pomóc to zrozumieć:
Użyjemy "Bob" w komórce B3 jako naszego punktu zakotwiczenia. Gdybyśmy chcieli pobrać wartość tuż poniżej (Charlie), powiedzielibyśmy, że chcemy przesunąć wiersz o 1. Nasza formuła wyglądałaby tak
| 1 | =PRZESUNIĘCIE(B3; 1) |
Gdybyśmy chcieli przenieść się w górę, byłaby to zmiana ujemna. Możesz myśleć o tym, jak liczba wierszy maleje, więc musimy odjąć. Tak więc, aby uzyskać powyższą wartość (Adam), napisalibyśmy
| 1 | =PRZESUNIĘCIE(B2, -1) |
Podstawowe przykłady kolumn
Kontynuując pomysł z poprzedniego przykładu, dodamy kolejną kolumnę do naszej tabeli.
Gdybyśmy chcieli złapać nauczyciela dla Boba, moglibyśmy użyć formuły
| 1 | =PRZESUNIĘCIE(B2, 0, 1) |
W tym przypadku powiedzieliśmy, że chcemy przesunąć zero wierszy (czyli pozostać w tym samym wierszu), ale chcemy przesunąć 1 kolumnę. W przypadku kolumn liczba dodatnia oznacza przesunięcie w prawo, a liczby ujemne oznaczają przesunięcie w lewo.
PRZESUNIĘCIE i DOPASOWANIE
Załóżmy, że masz kilka kolumn danych i chcesz dać użytkownikowi możliwość wyboru kolumny, z której ma pobierać wyniki. Możesz użyć funkcji INDEKS lub możesz użyć PRZESUNIĘCIA. Ponieważ MATCH zwróci względną pozycję wartości, musimy upewnić się, że punkt zakotwiczenia znajduje się po lewej stronie naszej pierwszej możliwej wartości. Rozważ następujący układ:
W B2 napiszemy taką formułę:
| 1 | = PRZESUNIĘCIE(B2, 0; PODAJ.POZYCJĘ(A2;$C$1:$F$1, 0)) |
MECZ będzie wyglądał „luty” w zakresie C1:F1 i znajdzie go w 2NS komórka. PRZESUNIĘCIE następnie przesunie 1 kolumnę na prawo od B2 i przyjmie żądaną wartość 9. Należy zauważyć, że PRZESUNIĘCIE nie ma problemu z użyciem tej samej komórki, która zawiera formułę jako punkt kontrolny.
UWAGA: Ta technika może być użyta jako zamiennik funkcji WYSZUKAJ.PIONOWO lub WYSZUKAJ.POZIOMO, gdy chcesz zwrócić wartość z lewej/powyżej zakresu wyszukiwania. Dzieje się tak, ponieważ PRZESUNIĘCIE może wykonywać ujemne przesunięcia.
PRZESUNIĘCIE, aby uzyskać zasięg
Możesz użyć 4NS i 5NS argumenty w funkcji PRZESUNIĘCIE, aby zwrócić zakres, a nie tylko pojedynczą komórkę. Załóżmy, że chcesz zsumować 3 kolumny w tej tabeli.
| 1 | =ŚREDNIA(PRZESUNIĘCIE(A1,DOPASUJ(F2,A2:A5,0);1,1,3)) |
W F2 wybraliśmy imię ucznia, dla którego chcemy pobrać jego średnie wyniki testu. Aby to zrobić, użyjemy wzoru
| 1 | =ŚREDNIA(PRZESUNIĘCIE(A1,DOPASUJ(F2,A2:A5,0);1,1,3)) |
Funkcja MATCH przeszuka kolumnę A pod kątem naszego imienia i zwróci pozycję względną, która w naszym przykładzie wynosi 3. Zobaczmy, jak zostanie to ocenione. Po pierwsze, OFFSET pójdzie w dół 3 rzędy od A1 i 1 kolumna do Prawidłowy od A1. To umieszcza nas w komórce B3.
| 1 | =ŚREDNIA(ODSUNIĘCIE(A1, 3, 1, 1, 3)) |
Następnie zmienimy rozmiar zakresu. Nowy zakres będzie miał B3 jako górną lewą komórkę. Będzie miał wysokość 1 wiersza i 3 kolumny, co da nam zakres B4:D4.
| 1 | =ŚREDNIA(PRZESUNIĘCIE(A1,3;1,1;3)) |
Zauważ, że chociaż możesz legalnie umieścić wartości ujemne w argumentach przesunięcia, w argumentach określania rozmiaru możesz używać tylko wartości nieujemnych.
Na koniec nasza funkcja ŚREDNIA widzi:
| 1 | =ŚREDNIA(B4:D4) |
W ten sposób otrzymujemy nasze rozwiązanie 86,67
OFFSET z dynamiczną SUMA
Ponieważ PRZESUNIĘCIE służy do znajdowania odwołania, a nie do bezpośredniego wskazywania komórki, jest najbardziej przydatne, gdy mamy do czynienia z danymi, do których dodano lub usunięto wiersze. Rozważ poniższą tabelę z sumą na dole
| 1 | =SUMA(B2:B4) |
Gdybyśmy użyli tutaj podstawowej formuły SUMA „= SUMA (B2: B4)”, a następnie wstawili nowy wiersz, aby dodać rekord dla Billa, otrzymalibyśmy złą odpowiedź
Zamiast tego zastanówmy się, jak rozwiązać ten problem z punktu widzenia Total. Naprawdę chcemy pobrać wszystko, od komórki B2 po komórkę tuż powyżej naszej sumy. Sposób, w jaki możemy to zapisać w formule, to przesunięcie wiersza o wartości -1. Dlatego używamy tego jako wzoru na naszą sumę w komórce B5:
| 1 | =SUMA(B2:PRZESUNIĘCIE(B5;-1,0)) |
Ta formuła robi to, co właśnie opisaliśmy: zacznij od B2 i przejdź do komórki o 1 komórkę powyżej naszej całkowitej komórki. Możesz zobaczyć, jak po dodaniu danych Billa nasza suma jest poprawnie aktualizowana.
PRZESUNIĘCIE, aby uzyskać ostatnie N przedmiotów
Załóżmy, że odnotowujesz miesięczną sprzedaż, ale chcesz mieć możliwość przyjrzenia się ostatnim 3 miesiącom. Zamiast ręcznie aktualizować formuły, aby dostosowywać je w miarę dodawania nowych danych, możesz użyć funkcji PRZESUNIĘCIE z funkcją LICZBA.
Pokazaliśmy już, jak możesz użyć PRZESUNIĘCIA, aby pobrać zakres komórek. Aby określić, ile komórek musimy przesunąć, użyjemy funkcji LICZBA, aby znaleźć ile liczby są w kolumnie B. Spójrzmy na naszą przykładową tabelę.
| 1 | =SUMA(PRZESUNIĘCIE($B$1,LICZBA(B:B)-$E$1+1,0,$E$1,1)) |
Gdybyśmy zaczęli od B1 i przesunęli 4 wiersze (liczba liczb w kolumnie B), skończylibyśmy na dole naszego zakresu, B5. Ponieważ jednak PRZESUNIĘCIE nie może zmienić rozmiaru z wartością ujemną, musimy wprowadzić pewne poprawki, abyśmy wylądowali w B3. Ogólne równanie to zrobi
| 1 | LICZBA(…) - N + 1 |
Bierzemy liczbę całej kolumny, odejmujemy tyle, ile chcemy zwrócić (ponieważ zmienimy rozmiar, aby je pobrać), a następnie dodajemy 1 (ponieważ zasadniczo zaczynamy nasze przesunięcie od pozycji zerowej).
Tutaj możesz zobaczyć, że ustawiliśmy zakres, aby uzyskać sumę, średnią i maksymalną z ostatnich N miesięcy. W E1 wprowadziliśmy wartość 3. W E2 nasz wzór to
| 1 | =SUMA(PRZESUNIĘCIE($B$1,LICZBA(B:B)-$E$1+1,0,$E$1,1)) |
Podświetlona sekcja to nasze ogólne równanie, które właśnie omówiliśmy. Nie musimy przesuwać żadnych kolumn. Następnie zmienimy rozmiar zakresu na 3 komórki (określone przez wartość w E1) i szerokość 1 kolumny. Nasza SUM następnie przyjmuje ten zakres i daje nam wynik 1850 USD. Pokazaliśmy również, że możesz obliczyć średnią max tego samego zakresu, po prostu przełączając funkcję zewnętrzną z SUM na dowolną wymaganą sytuację.
Listy dynamicznej walidacji OFFSET
Korzystając z techniki pokazanej w poprzednim przykładzie, możemy również zbudować nazwane zakresy, które można wykorzystać w walidacji danych lub wykresach. Może to być przydatne, gdy chcesz skonfigurować arkusz kalkulacyjny, ale oczekujesz zmiany rozmiaru naszych list/danych. Załóżmy, że nasz sklep zaczyna sprzedawać owoce, a obecnie mamy 3 możliwości.
Aby utworzyć listę rozwijaną sprawdzania poprawności danych, której możemy użyć w innym miejscu, zdefiniujemy nazwany zakres MyFruit jako
| 1 | = $ A $ 2: PRZESUNIĘCIE ($ A $ 1, COUNT A ($ A: $ A) -1, 0) |
Zamiast COUNT, używamy COUNTA, ponieważ mamy do czynienia z wartościami tekstowymi. Z tego powodu nasze COUNTA będzie o jeden wyższe, ponieważ zliczy komórkę nagłówka w komórce A1 i da wartość 4. Jeśli jednak przesuniemy o 4 wiersze, skończymy w komórce A5, która jest pusta. Aby to skorygować, odejmujemy 1.
Teraz, gdy mamy już konfigurację nazwanego zakresu, możemy skonfigurować niektóre sprawdzanie poprawności danych w komórce C4 przy użyciu typu listy ze źródłem:
| 1 | =MojeOwoc |
Zauważ, że lista rozwijana pokazuje tylko nasze trzy aktualne pozycje. Jeśli następnie dodamy więcej elementów do naszej listy i wrócimy do listy rozwijanej, lista pokaże wszystkie nowe elementy bez konieczności zmiany jakiejkolwiek formuły.
Ostrzeżenia dotyczące korzystania z OFFSET
Jak wspomniano na początku tego artykułu, OFFSET jest funkcją ulotną. Nie zauważysz tego, jeśli używasz go w zaledwie kilku komórkach, ale jeśli zaczniesz go angażować w setki obliczeń i szybko zauważysz, że komputer spędza znaczną ilość czasu na ponownym obliczaniu za każdym razem, gdy wprowadzasz jakiekolwiek zmiany .
Dodatkowo, ponieważ PRZESUNIĘCIE nie nazywa bezpośrednio komórek, na które patrzy, innym użytkownikom trudniej jest później przyjść i zmienić formuły, jeśli zajdzie taka potrzeba.
Zamiast tego wskazane byłoby użycie tabel (wprowadzonych w Office 2007), które umożliwiają odwołania strukturalne. Pomogło to użytkownikom w uzyskaniu pojedynczego odniesienia, którego rozmiar automatycznie dopasowywał się w miarę dodawania lub usuwania nowych danych.
Inną opcją, której można użyć zamiast OFFSET, jest potężna funkcja INDEX. INDEKS pozwala budować wszystkie zakresy dynamiczne, które widzieliśmy w tym artykule, bez problemu bycia funkcją ulotną.
Dodatkowe uwagi
Użyj funkcji PRZESUNIĘCIE, aby zwrócić wartość komórki (lub zakres komórek) przez przesunięcie określonej liczby wierszy i kolumn od odniesienia początkowego. Podczas wyszukiwania tylko pojedynczej komórki formuły PRZESUNIĘCIE osiągają ten sam cel, co formuły INDEKS, przy użyciu nieco innej techniki. Prawdziwa moc funkcji OFFSET leży w jej zdolności do wybrania zakresu komórek, które mają być użyte w innej formule.
Korzystając z funkcji PRZESUNIĘCIE, definiujesz początkową komórkę początkową lub zakres komórek. Następnie wskazujesz liczbę wierszy i kolumn do przesunięcia od tej początkowej komórki. Możesz także zmienić rozmiar zakresu; dodawać lub odejmować wiersze lub kolumny.
Wróć do listy wszystkich funkcji w Excelu
PRZESUNIĘCIE w Arkuszach Google
Funkcja PRZESUNIĘCIA działa dokładnie tak samo w Arkuszach Google jak w Excelu:
