W tym artykule dowiemy się, jak korzystać z funkcji PRZESUNIĘCIE w programie Excel.
Co jest Funkcja przesunięcia?
Funkcja offset to funkcja wyszukiwania informacji. Jeśli masz dużą tabelę zawierającą określone dane i chcesz z niej pobrać dane, najlepszym sposobem na to jest wstawienie formuły przesunięcia dla tabeli w tym arkuszu lub innym arkuszu w tym samym skoroszycie. Tak więc, po utworzeniu tabeli wyszukiwania offsetów, wystarczy wprowadzić dane, na przykład „miesiąc”, a otrzymasz „sprzedaż” lub „dochód” z tego miesiąca z tej tabeli.
Przechodzenie przez tabelę to kolejna opcja, ale czy rozważyłbyś tę opcję, jeśli masz arkusze i arkusze danych zawierające tabelę danych o 1000 kolumnach? W tym miejscu offset łączy się z innymi funkcjami.
Koncepcja tabeli przestawnej pochodzi z offsetu. Tabela przestawna jest tabelą ogólną i z tej konkretnej kombinacji danych i wykresów uzyskuje się w razie potrzeby. W ten sam sposób można również tworzyć tabele dynamiczne. W przypadku tych tabel będziesz musiał utworzyć formułę przesunięcia programu Excel
Składnia PRZESUNIĘCIA
| =PRZESUNIĘCIE(odniesienie;wiersze;kolumny;[wysokość];[szerokość]) |
Wydziwianie - prosisz Excela o przesunięcie liczby wierszy, aby uzyskać informacje. W tym przypadku musi poruszać się po całym stole, więc po prostu zostaw puste miejsce oznaczone przecinkiem (,). W przeciwnym razie, aby przesunąć kolumnę do przodu, podaj wartość 1, a aby przenieść kolumnę wcześniej, podaj wartość -1.
Kolumny - jest to doprowadzenie funkcji do liczby kolumn. System wartości jest taki sam jak w przypadku wiersza.
Wzrost - wysokość stołu, w tym przypadku A11.
Szerokość - szerokość stołu, w tym przypadku D11.
Po skonfigurowaniu funkcji przesunięcia ważne jest, aby przeprowadzić próbę, aby wyeliminować wszelkie błędy.
Weźmy tabelę zawierającą imię i nazwisko, identyfikator e-mail, datę urodzenia i wiek. Tabela zaczyna się od komórki A1, zawierającej nagłówek „Nazwa”. Dane biegną dla powiedzmy A11. A rzędy biegną do D1. Cały stół biegnie A1:D11. Potrzebujesz systemu pobierania, który pobiera nazwę po wprowadzeniu identyfikatora e-mail lub identyfikator e-mail po wprowadzeniu nazwy wraz z innymi szczegółami. Tworzysz formułę, spełniając 5 argumentów.
Punktem odniesienia jest komórka, od której powinno rozpocząć się wyszukiwanie. W tym przypadku powinno to być A2. To jest ogólny standard.
Przykład:
Na przykład podasz odniesienie do B4 i przesunięcia wiersza 0 i przesunięcia kolumny 1, a następnie zostanie zwrócona wartość z C4. Zagmatwane? Miejmy więcej przykładów. Ostatni przykład funkcji OFFSET mówi jak dynamicznie SUM.
Funkcja OFFSET do pobierania wartości z prawej i lewej strony komórki
Mamy ten stół.
Teraz, jeśli chcę uzyskać wartość z 2 komórek w prawo do B2 (co oznacza D2). Napiszę tę formułę OFFSET:
| =PRZESUNIĘCIE(B2,0,2) |
Funkcja OFFSET przesunie 2 komórki na prawo od komórki B4 i zwróci jej wartość. Zobacz obrazek poniżej
Jeśli chcę uzyskać wartość z 1 komórki pozostawionej do B2 (co oznacza A2). Napiszę tę formułę OFFSET:
| =PRZESUNIĘCIE(B2,0;-1) |
Znak minus (-) wskazuje przesunięcie do ruchu wstecznego.
Funkcja OFFSET, aby uzyskać wartość z dołu i z góry komórki
Więc ponownie w powyższej tabeli, jeśli chcę uzyskać wartość z 2 komórek powyżej do B3 (co oznacza B5). Napiszę tę formułę OFFSET:
| =PRZESUNIĘCIE(B2,2,0) |
Jeśli chcę uzyskać wartość z 1 komórki pozostawionej do B2 (co oznacza A2). Napiszę tę formułę OFFSET:
| =PRZESUNIĘCIE(B2,-2,0) |
- Wskazówka dla profesjonalistów: Traktuj PRZESUNIĘCIE jako wskaźnik wykresu, gdzie odniesienie jest początkiem, a wiersz i kolumna są osiami x i y.
Funkcja OFFSET do dynamicznego sumowania zakresu
Zobaczmy ten przykład.
W powyższej tabeli Suma wiersz na końcu tabeli. Z biegiem czasu będziesz dodawać wiersze do tej tabeli. Następnie musisz zmienić zakres sumy w formule. Tutaj możemy skorzystać z funkcji OFFSET do dynamicznego sumowania. Obecnie w komórce C11 mamy =SUMA(C2:C10). Zastąp to poniższym wzorem.
| =SUMA(C2:PRZESUNIĘCIE(C11;-1,0)) |
Ta formuła pobiera tylko pierwszy wiersz danych, a następnie sumuje zakres powyżej wiersza sumy.
Przesunięcie w celu uzyskania szyku
Funkcja OFFSET ma dwa opcjonalne argumenty, [wysokość] i [szerokość]. Nie bierz ich za pewnik. Gdy funkcja PRZESUNIĘCIE ma argumenty [wysokość] i [szerokość], zwraca dwuwymiarową tablicę. Jeśli tylko jeden z nich zostanie przekazany jako argument, zwraca on wymiarową tablicę wartości.
Jeśli wpiszesz tę formułę w dowolnej komórce:
| =SUMA(ODSUNIĘCIE(C1,1,0,9,3)) |
Zsumuje wartości z zakresu od C2 do 9 wierszy poniżej i 3 kolumny po prawej.
PRZESUNIĘCIE(C1,1,0,9,3) : Przełoży się to na {8,8,7;6,1,2;8,5,8;5,1,5;8,3,5;8,3,9;8,9,2;3,5 ,4;6,6,5}.
Podsumowując, offset jest bardzo przydatną funkcją programu Excel, która może pomóc w rozwiązaniu wielu zadufanych wątków. Daj mi znać, w jaki sposób używasz funkcji PRZESUNIĘCIE w swojej formule i gdzie najbardziej Ci pomogła.
Funkcja wyszukiwania jest taka sama jak offset, ale wykorzystuje funkcje takie jak Match, Counta i IF do pracy z dynamiczną tabelą.
Korzystanie z offsetu w Excelu wymaga umiejętności i dobrej znajomości funkcji Excela i tego, jak one ze sobą współpracują. Wyciągnięcie go bez żadnego błędu jest największą przeszkodą.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Ta formuła działa zarówno z tekstem, jak i liczbami.
- Istnieje pięć argumentów offsetu, które muszą zostać spełnione, aby uzyskać informacje bez błędów. Jeśli napiszesz niepoprawną formułę, wystąpi błąd Ref.
Mam nadzieję, że zrozumiałeś, co to jest funkcja przesunięcia programu Excel i jak z niej korzystać w programie Excel. Zapoznaj się z innymi artykułami na temat wyszukiwania w programie Excel i dopasuj wartości za pomocą formuł tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Funkcja WYSZUKAJ.PIONOWO do obliczania oceny w programie Excel : Aby obliczyć oceny IF i IFS nie są jedynymi funkcjami, których możesz użyć. Funkcja WYSZUKAJ.PIONOWO jest bardziej wydajna i dynamiczna dla takich obliczeń warunkowych.Aby obliczyć oceny za pomocą funkcji WYSZUKAJ.PIONOWO, możemy użyć tego wzoru.
17 rzeczy o Excelu WYSZUKAJ.PIONOWO : WYSZUKAJ.PIONOWO jest najczęściej używany do pobierania dopasowanych wartości, ale WYSZUKAJ.PIONOWO może zrobić o wiele więcej. Oto 17 rzeczy dotyczących funkcji WYSZUKAJ.PIONOWO, o których powinieneś wiedzieć, aby skutecznie używać.
WYSZUKAJ pierwszy tekst z listy w programie Excel : Funkcja WYSZUKAJ.PIONOWO działa dobrze ze znakami wieloznacznymi. Możemy to wykorzystać do wyodrębnienia pierwszej wartości tekstowej z podanej listy w programie Excel. Oto ogólna formuła.
WYSZUKAJ datę z ostatnią wartością na liście : Aby pobrać datę, która zawiera ostatnią wartość, używamy funkcji WYSZUKAJ. Ta funkcja sprawdza komórkę, która zawiera ostatnią wartość w wektorze, a następnie używa tego odwołania do zwrócenia daty.
Jak pobrać najnowszą cenę w Excelu? : Aktualizacja cen w każdej firmie jest powszechna, a używanie najnowszych cen przy każdym zakupie lub sprzedaży jest koniecznością. Aby pobrać najnowszą cenę z listy w Excelu, korzystamy z funkcji WYSZUKAJ. Funkcja WYSZUKAJ pobiera najnowszą cenę.
Popularne artykuły:
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.