PRZESUNIĘCIE w Excelu

Funkcja PRZESUNIĘCIE Zwraca zawartość wskazanej komórki lub adres zakresu wielu komórek Excela. Aby wskazać komórkę (komórki), należy podać tej funkcji kilka parametrów. Jest to jedna z bardziej zaawansowanych funkcji Excela.

SPIS TREŚCI

Do czego służy funkcja PRZESUNIĘCIE w Excelu?

Jeśli tworzysz bardziej rozbudowane arkusze w Excelu, to nieraz zachodzi potrzeba zaprezentowania w jednej komórce wartości z innej komórki, której adresu nie znasz. Brak znajomości adresu nie wynika, z tego, że nie możesz go odczytać, ale z tego, że wartość, którą chcesz zaprezentować przesuwa się, i za każdym razem może znajdować się w innej komórce Excela. Z tego względu, chcesz użyć mechanizmu (funkcji PRZESUNIĘCIE), który pozwoli automatycznie określić, w jakiej komórce znajduje się poszukiwana wartość.

W przykładzie poniżej, należy znaleźć wartość przypisaną do Szwajcarii i do miesiąca, które numer został podany w komórce B2. Idea jest taka, że ten numer miesiąca można zmieniać i formuła powinna na bieżąco aktualizować się i pokazywać poprawny wynik.

Rysunek 1. Przykład zastosowania funkcji PRZESUNIĘCIE w Excelu.

Wiemy, że miesiąc może się zmieniać, ale nazwa państwa jest stała w tym zadaniu (Szwajcaria). Można więc powiedzieć, że funkcja PRZESUNIĘCIE, przesuwa się od nagłówka kolumny szwajcarskiej (komórka I2) o dwie komórki w dół – a właściwie, przesuwa się w dół o tyle komórek, ile wynosi numer miesiąca.

To pierwsze zadanie może wydawać się na siłę komplikowane, bo można by ten problem rozwiązać w Excelu inaczej, ale na tym prostym zadaniu, chce Ci wytłumaczyć sam mechanizm działania tej funkcji. Zrobię to bardziej szczegółowo w poniższej sekcji.

Budowa funkcji PRZESUNIĘCIE w Excelu

Funkcja PRZESUNIĘCIE ma dwa tryby, w których może działać:

    • Składający się z 3 argumentów.
    • Składający się z 5 argumentów.

Na razie omówię tryb z trzema argumentami, który jest prostszy.

Tryb pierwszy, zwraca zawartość pojedynczej komórki. Czyli robi dość pospolitą rzecz – mówi jaka jest wartość np. w A5, albo C8, albo dowolnej innej komórce. To co jest nietypowe, to że ta funkcja nie wie z góry, o którą komórkę chodzi, ale oblicza jej adres. Poniżej budowa funkcji:

    1. Odwołanie – podaj komórkę początkową, od której Excel powinien przesunąć się określoną liczbę wierszy i kolumn, aby trafić na komórkę, której szukasz.
    2. Wiersze – podaj liczbę, oznaczająca ile wierszy od komórki początkowej Excel powinien się przesunąć. Dodatnia wartość powoduje przesunięcie w dół, ujemna wartość powoduje przesunięcie do góry.
    3. Kolumny – podaj liczbę, oznaczająca ile kolumn od komórki początkowej Excel powinien się przesunąć. Dodatnia wartość powoduje przesunięcie w prawo, ujemna wartość powoduje przesunięcie w lewo.

Oto jak może wyglądać formuła w zadaniu przedstawionym wyżej (wprowadzona do komórki C2):

=PRZESUNIĘCIE(I1;B2;0)

Objaśnię teraz kolejne argumenty na powyższym przykładzie:

    1. W tym zadaniu wiemy, że chcemy pokazać wartość z kolumny „Szwajcaria”, dlatego możesz, jako komórkę początkową, wybrać komórkę z kolumny I (tam gdzie jest Szwajcaria). Zazwyczaj wskazuje się komórkę w nagłówku tabeli (bo jest to jakiś „początek”, a dobrze zaczynać jest od „początku”, a nie od „środka”), więc będzie to komórka w wierszu pierwszym, czyli „I1”.
    2. Drugi argument, to liczba wierszy, o jakie Excel ma się przesunąć od „I1” – liczba wierszy równa się numerowi miesiąca – tego typu analogii należy szukać w swoich arkuszach Excela, aby funkcja mogła prawidłowo wyszukiwać. Oczywiście, nie zawsze takie analogie można znaleźć – wówczas często trzeba posiłkować się dodatkowymi funkcjami, jak np. PODAJ.POZYCJĘ. W tym przypadku, zaznaczam więc liczbę miesięcy, czyli komórkę „B2”.
    3. Ostatni argument to liczba „0”, bo „I1”, czyli komórka początkowa, jest już w prawidłowej kolumnie (Excel nie powinien przesuwać się o żadne dodatkowe kolumny).

Kolejny przykład na PRZESUNIĘCIE z 3 argumentami

Wiem, że jest to dość skomplikowana funkcja, dlatego przedstawię kolejny przykład, który jest podobny do poprzedniego, czyli używać będziemy 3 argumentów.

Czasem tworzymy interaktywne arkusze Excela, które pozwalają użytkownikowi w łatwy sposób sterować wyświetlanymi informacjami. Może on wtedy wskazać, której informacji potrzebuje, a ona mu się w odpowiedni sposób wyświetli. Funkcja PRZESUNIĘCIE może w tym pomóc – pomoże wyświetlać wartość nie zawsze tej samej komórki Excela, ale tej, która zostanie w jakiś sposób wskazana.

W zadaniu widać informacje o tym, ile kilometrów pojazd przejechał w ciągu kolejnych dni. U góry, należy zapisać taką formułę, która będzie wyświetlała liczbę kilometrów dla podanego numeru dnia. Jeśli użytkownik poda nr 5, to Excel, za pomocą funkcji PRZESUNIĘCIE, przesunie się o 5 wierszy w dół tabeli i wyświetli odpowiednią ilość kilometrów. W funkcji użyto trzech argumentów:

Rysunek 2. Przykład PRZESUNIĘCIE z 3 argumentami.

=PRZESUNIĘCIE(F2;B3;0)
    • Argument 1 – komórka początkowa, czyli nagłówek tabeli. Jest to nagłówek kolumny „Ilość km”, bo właśnie z tej kolumny chcemy wyświetlać wartości.
    • Argument 2 – ilość wierszy, którą Excel ma się przesunąć od komórki początkowej jest równa numerowi dnia wybranego przez użytkownika w komórce u góry. Wynika to z tego, że argument nr 1 został świadomie wybrany – jeśli w argumencie nr 1 wybrano by komórkę z innego wiersza, to argument nr 2 też powinien zostać odpowiednio zmieniony. Widać więc, że argument nr 1 i nr 2 są ze sobą sprzężone.
    • Argument 3 – Excel nie powinien przesuwać się o żadną ilość kolumn, ponieważ komórka w argumencie nr 1 już została wybrana w odpowiedniej kolumnie. Argument nr 3 przyjmuje w takiej sytuacji wartość zero. Jeśli w argumencie nr 1 wybrano by komórkę z innej kolumny, to argument nr 3 też powinien zostać odpowiednio zmieniony. Widać więc, że argumenty nr 1 i nr 3 również są ze sobą sprzężone.

Pamiętaj, że wszystkie trzy argumenty tej funkcji są ze sobą połączone. Staraj się więc tak wybierać komórkę początkową (argument nr 1), aby argumenty nr 2 i nr 3 były proste do wyliczenia i intuicyjne. Najczęściej komórką początkową jest nagłówek (kolumny, wiersza, tabeli), więc spróbuj zacząć od tego, ale pamiętaj, że nie jest to rygorystyczna zasada i zawsze możesz to zmienić na inną komórkę.

PRZESUNIĘCIE w EXCELU i obliczanie numeru wiersza

Tworzenie interaktywnych arkuszy jest bardzo przydatne, ale nie zawsze jesteśmy w stanie powiedzieć, o ile wierszy lub kolumn Excela funkcja PRZESUNIĘCIE powinna się przesunąć aby znaleźć wartość. Często trzeba to obliczyć – w jaki sposób? To o ile wierszy/kolumn się przesunąć nie jest zazwyczaj przypadkowe, ale zależy od tego, w którym wierszu/kolumnie znajduje się jakaś ważna dla nas informacja opisana pewną nazwą lub nagłówkiem. Można więc znaleźć pozycję tego nagłówka (nazwy), tym samym znajdując pozycje szukanej wartości. Do znalezienia pozycji użyta zostanie inna excelowa funkcja – PODAJ.POZYCJĘ.

W zadaniu poniżej musisz dowiedzieć się ile przesyłek dostarczył kurier o wskazanym wcześniej numerze. Należy więc użyć PODAJ.POZYCJĘ na kolumnie „Nr kuriera”, aby zobaczyć, w którym wierszu się znajduje, a następnie przesunąć się za pomocą PRZESUNIĘCIA, o tyle właśnie wierszy:

Rysunek 3. Szukanie numeru kuriera, aby znaleźć liczbę dostarczonych przesyłek.

=PRZESUNIĘCIE(D2;PODAJ.POZYCJĘ(I2;F3:F49;0);0)
    • Argument 1 – komórka początkowa, czyli nagłówek kolumny „Liczba dostarczonych przesyłek”. Z tej właśnie kolumny chcemy ostatecznie otrzymać wartość, a nie z kolumny „Nr kuriera”, dlatego na niej ustawiamy komórkę początkową.
    • Argument 2 – ilość wierszy, którą Excel ma się przesunąć od komórki początkowej jest równa pozycji szukanego kuriera na liście. Pozycja ta, jest obliczana za pomocą PODAJ.POZYCJĘ.
    • Argument 3 – nie należy się przesuwać o żadną ilość kolumn, ponieważ komórka w argumencie nr 1 już została wybrana w odpowiedniej kolumnie. Argument nr 3 przyjmuje w takiej sytuacji wartość zero.

PRZESUNIĘCIE z 5 argumentami

No to przechodzimy do gwoździa programu, czyli funkcji Excela o nazwie PRZESUNIĘCIE, z 5-cioma argumentami.

Do czego służy w Excelu PRZESUNIĘCIE z 5-cioma argumentami

Pozwala ona wówczas na wskazanie zakresu wielu komórek. Prawie żadna inna funkcja Excela tego nie potrafi!:

    • WYSZUKAJ.PIONOWO – świetnie szuka, ale wyświetla zawartość tylko jednej komórki
    • INDEKS – także szuka i wyświetla zawartość jednej komórki
    • PODAJ.POZYCJĘ – szuka pozycji, ale tylko dla jednej wartości

Funkcja PRZESUNIĘCIE potrafi znaleźć zakres wielu komórek w arkuszu Excela. Tylko po co? Nie można za jej pomocą wyświetlić wszystkich wartości tych komórek (no bo jak wiele wartości wyświetlić w pojedynczej komórce, w której mamy PRZESUNIĘCIE?). Ale możemy np. policzyć sumę tych wartości i ją wyświetlić, prawda? A no tak, możemy. Więc PRZESUNIĘCIE szuka nam komórek, my potem możemy zrobić z nimi co tylko chcemy:

    • możemy je zsumować
    • możemy w nich wyszukiwać za pomocą WYSZUKAJ.PIONOWO
    • możemy wyliczać z nich wartość maksymalną
    • możemy z nich rysować dynamiczny wykres w Excelu
    • możemy z nich tworzyć dynamiczną listę rozwijaną
    • możemy z nich tworzyć dynamiczną tabelę przestawną
    • możemy robić cokolwiek, co opiera się na jakimś zakresie komórek i chcemy żeby było dynamiczne (zmienne)

Budowa PRZESUNIĘCIA z 5 argumentami

Tryb drugi zwraca odwołanie do zakresu (wielu) komórek. Samy odwołanie do wielu komórek zazwyczaj powoduje błąd, dlatego ta funkcja stanowi „wsparcie” dla innych funkcji lub narzędzi (np. wskazuje komórki, które SUMA ma sumować, ale komórki, z tych ma być narysowany wykres). Poniżej budowa funkcji, gdzie 3 pierwsze argumenty są takie same jak wcześniej, ale dla porządku zapisuje również je:

    1. Odwołanie – podaj komórkę początkową, od której Excel powinien przesunąć się określoną liczbę wierszy i kolumn, aby trafić na komórkę, której szukasz.
    2. Wiersze – podaj liczbę, oznaczająca ile wierszy od komórki początkowej Excel powinien się przesunąć. Dodatnia wartość powoduje przesunięcie w dół, ujemna wartość powoduje przesunięcie do góry.
    3. Kolumny – podaj liczbę, oznaczająca ile kolumn od komórki początkowej Excel powinien się przesunąć. Dodatnia wartość powoduje przesunięcie w prawo, ujemna wartość powoduje przesunięcie w lewo.
    4. Wysokość – podaj liczbę wierszy, jaką ma mieć zwracany przez funkcję zakres. Zakres ten będzie zaczynał się w komórce wskazanej przez trzy pierwsze argumenty. Gdy ten argument ma wartość dodatnią, wówczas zakres rozciąga się w dół, jeśli ma wartość ujemną, wówczas rozciąga się w górę.
    5. Szerokość – podaj liczbę kolumn, jaką ma mieć zwracany przez funkcję zakres. Zakres ten będzie zaczynał się w komórce wskazanej przez trzy pierwsze argumenty. Gdy ten argument ma wartość dodatnią, wówczas zakres rozciąga się w prawo, jeśli ma wartość ujemną, wówczas rozciąga się w lewo.

Na rysunku widać kolejne argumenty:

    1. A1 to komórka startowa
    2. Przesuwamy się w dół o 3 wiersze, lądując w A4
    3. Przesuwamy się w prawo o 2 kolumny, lądując w C4
    4. Rozciągamy się (zaczynając w C4) na 5 wierszy (od C4 do C8)
    5. Rozciągamy się (zaczynając w C4:C8) na 3 kolumny (od C4 do E8).

Formuła w tym przypadku wyglądałaby tak:

=PRZESUNIĘCIE(A1;3;2;5;3)

Rysunek 4. Rozrysowanie budowy PRZESUNIĘCIA z 5 argumentami.

Przykład obliczeń na zakresie komórek

Przechodzę teraz do konkretnego przykładu zastosowania 5 argumentów w PRZESUNIĘCIU. W zadaniu należy obliczyć sumę wartości dla transakcji od pierwszej do tej, którą wybierze użytkownik:

    • Argument 1 – komórka początkowa. Gdy używasz argumentów nr 4 i nr 5, to często komórką początkową nie będzie nagłówek, ale pierwsza komórka, która ma należeć do szukanego zakresu. Często ta komórka jest z góry znana, więc najłatwiej jest ją wskazać w tym argumencie. Tym, co jest nieznane, to rozmiar tego zakresu (argument nr 4 i nr 5).
    • Argument 2– jeśli pierwszy argument wskazuje już na prawidłową komórkę, od której zaczynać się będzie zakres, to nie trzeba (wręcz nie należy) przesuwać się o żadne wiersze.
    • Argument 3 – jeśli pierwszy argument wskazuje już na prawidłową komórkę, od której zaczynać się będzie zakres, to nie trzeba (wręcz nie należy) przesuwać się o żadne kolumny.
    • Argument 4 – najtrudniejszym elementem tego zadania, jest obliczenie ile wierszy ma zostać zsumowane. Tyle, ile użytkownik wskaże w komórce obok – czyli PRZESUNIĘCIE zwróci odwołanie do zakresu rozpoczynającego się w komórce podanej w argumencie nr 3, rozciągającego się na tę właśnie liczbę wierszy.
    • Argument 5 – wiadomo z góry, że zakres ma być jednokolumnowy, więc można spokojnie wpisać tutaj liczbę 1.

=SUMA(PRZESUNIĘCIE(D3;0;0;G2;1))

Rysunek 5. Przykład sumowania wartości od początku, do wskazanego miejsca w tabeli.

Obliczenia na zakresie komórek Excela, który trzeba rozpoznać

Czasami, gdy potrzebujemy uzyskać odwołanie do zakresu wielu komórek (tak jak w poprzednim przykładzie), musimy jeszcze obliczyć jak duży będzie to zakres. Często nie jest podane to wprost, tylko trzeba użyć do tego funkcji PODAJ.POZYCJĘ.

W zadaniu widać kwoty sprzedaży w kolejnych miesiącach. Należy zapisać formułę, która będzie liczyła sumę sprzedaży od początku roku do wskazanego miesiąca. Należy więc obliczyć, który na liście jest wskazany miesiąc (np. marzec jest trzeci) i tak duży zakres komórek sumować.

=SUMA(PRZESUNIĘCIE(C3;0;0;PODAJ.POZYCJĘ(E3;B3:B14;0);1))

Rysunek 6. Wyszukanie pozycji miesiąca za pomocą PODAJ.POZYCJĘ.

    • Argument 1 – komórka początkowa. Gdy używasz argumentów nr 4 i nr 5, to często komórką początkową nie będzie nagłówek, ale pierwsza komórka, która ma należeć do szukanego zakresu. Często ta komórka jest z góry znana, więc najłatwiej jest ją wskazać w tym argumencie. W tym przypadku będzie to sprzedaż za styczeń, bo zawsze sumowane są wartości od początku roku.
    • Argument 2 – jeśli pierwszy argument wskazuje już na prawidłową komórkę, od której zaczynać się będzie zakres, to nie trzeba (wręcz nie należy) przesuwać się o żadne wiersze.
    • Argument 3 – jeśli pierwszy argument wskazuje już na prawidłową komórkę, od której zaczynać się będzie zakres, to nie trzeba (wręcz nie należy) przesuwać się o żadne kolumny.
    • Argument 4 – najtrudniejszym elementem tego zadania, jest obliczenie, ile wierszy ma zostać zsumowane. Tyle, ile miesięcy należy podsumować. To, ile tych miesięcy będzie, zależy od wskazanego miesiąca – od obliczenia, którą pozycję zajmuje na liście. Do tego została użyta funkcja PODAJ.POZYCJĘ.
    • Argument 5 – wiadomo z góry, że zakres ma być jednokolumnowy, więc można spokojnie wpisać tutaj liczbę 1.

Często oprócz argumentu nr 4, argument nr 5 też jest obliczany za pomocą PODAJ.POZYCJĘ. Czasem dodatkowym utrudnieniem jest jeszcze wyliczenie argumentu nr 2 i/lub nr 3.

PRZESUNIĘCIE użyte z narzędziami Excela

Funkcja PRZESUNIĘCIE w wersji z pięcioma argumentami zwraca odwołanie do zakresu komórek. Ten zakres może być wykorzystany nie tylko w formułach, ale we wszelkich narzędziach, które oczekują podania zakresu komórek. Narzędziem takim są np.:

    • Wykresy – rysowane są na bazie wartości w podanym zakresie komórek.
    • Listy rozwijane – prezentują najczęściej dane, znajdujące się we wskazanym zakresie komórek.
    • Tabele przestawne – budowane są na bazie tabeli źródłowej (znajdującej się w zakresie komórek).
    • Zakresy filtru zaawansowanego – jego kryteria zapisywane są w komórkach.

Budowanie takiej funkcji PRZESUNIĘCIE działa tak samo, jak w przypadku użycia jej w formułach.

W zadaniu, znajdują się nazwy produktów, zapisane w kolejnych komórkach. Należy stworzyć listę rozwijaną, która będzie prezentowała wszystkie z tych produktów. Istnieje jednak możliwość, że produktów będzie przybywać lub ubywać – wówczas lista ma automatycznie uwzględniać te zmiany. Wykorzystano tutaj połączenie funkcji PRZESUNIĘCIE i ILE.NIEPUSTYCH. ILE.NIEPUSTYCH zlicza jak dużo jest produktów, czyli jak dużo pozycji ma być na rozwijanej liście pokazywanych, a PRZESUNIĘCIE zwraca odwołanie do tych produktów. Na końcu, taka formuła musi być „wrzucona” do listy rozwijanej.

Oto formuła, którą należy wkleić do listy rozwijanej:

=PRZESUNIĘCIE($D$5;0;0;ILE.NIEPUSTYCH($D$5:$D$18);1)

Rysunek 7. Lista rozwijana w Excelu, oparta o funkcję PRZESUNIĘCIE.

    • Argument 1 – komórka początkowa. Gdy używasz argumentów nr 4 i nr 5, to często komórką początkową nie będzie nagłówek, ale pierwsza komórka, która ma należeć do szukanego zakresu. Często ta komórka jest z góry znana, więc najłatwiej jest ją wskazać w tym argumencie. W tym przypadku będzie to nazwa pierwszego produktu.
    • Argument 2 – jeśli pierwszy argument wskazuje już na prawidłową komórkę, od której zaczynać się będzie zakres, to nie trzeba (wręcz nie należy) przesuwać się o żadne wiersze.
    • Argument 3 – jeśli pierwszy argument wskazuje już na prawidłową komórkę, od której zaczynać się będzie zakres, to nie trzeba (wręcz nie należy) przesuwać się o żadne kolumny.
    • Argument 4 – najtrudniejszym elementem tego zadania, jest obliczenie ile produktów znajduje się na liście. Obliczone to zostało za pomocą ILE.NIEPUSTYCH. W tym przypadku założono, że produktów będzie nie więcej niż 20 i tyle komórek zaznaczono – zamiast zaznaczać całą kolumnę arkusza. Dzięki temu minimalizuje się ryzyko błędów w przyszłości – gdyby ktoś poniżej tej listy coś kiedyś dopisał, to mogłoby to być zliczone przez ILE.NIEPUSTYCH. Innymi słowy – jeśli wiesz, że elementów nie będzie przybywało bardzo dużo, to lepiej zaznaczyć ograniczony obszar, a nie całą kolumnę.
    • Argument 5 – wiadomo z góry, że zakres ma być jednokolumnowy, więc można spokojnie wpisać tutaj liczbę 1.

Na końcu, trzeba jeszcze tę formułę skopiować i wkleić do narzędzia listy rozwijanej. Z tego powodu, wszystkie adresy w formule są zablokowane dolarami. Lista rozwijana nie koniecznie będzie używana w tej samej komórce, w której zapisano najpierw formułę, więc „przesuwając” formułę do innej komórki, adresy by się też przesunęły, gdyby nie były zablokowane.

Powiązane wpisy poradnikowe