Tag Archives: Przesunięcie

Loading
loading..

Tutaj możecie ściągnąć plik ze stworzoną dwuetapową listą wybieralną.

Lista jest stworzona tak, że w 7 wierszu (zaznaczony na jasno zielony kolor) możemy dodawać kolejne regiony, a w polu zaznaczonym szarym kolorem możemy dodawać kolejne wydziały w tych regionach.
W zależności od tego ile dodamy pól, to lista wybieralna będzie odpowiednio dostosowana.

Lista wybieralna opiera się na poprawności danych. Klikamy na komórkę E2, wchodzimy w Dane/Poprawność danych i na karcie Ustawienia wybieramy opcję Lista. Różnica między standardową listą wybieralną polega na tym, że jako źródło nie podajemy zwykłej listy a formułę.

karta developer

Jako źródło listy wybieralnej regionu nie jest podany po prostu zakres $A$7:$D$7 tylko odpowiednia formuła z wykorzystaniem funkcji PRZESUNIĘCIE – kliknij tutaj aby przejść do wpisu gdzie opisujemy funkcję.

W komórce E2 (gdzie wybieramy region), w poprawności danych jako źródło listy wybieralnej mamy wpisaną formułę:

=PRZESUNIĘCIE($A$7:$I$7;0;0;;ILE.NIEPUSTYCH($A$7:$I$7))



karta developer

Taka formuła powoduje, że gdy dopiszemy kolejne regiony w zakresie A7:I7 to pojawią się one na liście wybieralnej. Gdyby jako źródło był podany bezpośrednio zakres $A$7:$I$7, to na liście pojawiłyby się również puste pozycje. Co więcej, dzięki powyższej formule zakres ma tyle kolumn ile jest niepustych komórek w zakresie $A$7:$I$7. W funkcji PRZESUNIĘCIE jako piąty argument jest wpisana formuła ILE.NIEPUSTYCH($A$7:$I$7), dzięki czemu szerokość zakresu jest zmieniona w naszym przykładzie ma on 4 kolumny (mamy wpisane 4 regiony). Czyli zwrócony przez funkcję zakres to: $A$7:$D$7 i takie jest źródło naszej listy wybieralnej.

W kolejnej komórce, gdzie mamy wydziały (komórka E3) jako źródło listy wybieralnej wpisaliśmy formułę:

=PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1;ILE.NIEPUSTYCH(PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1)))



karta developer

Funkcja PODAJ.POZYCJĘ(E2;$A$7:$I$7;0) zwraca pozycję wybranego przez nas wyżej regionu w zakresie a7:I7. I tak np. jeśli wybierzemy Południe, to podaj pozycje zwróci nam wartość 4. Dzięki temu wiemy, że interesująca nas lista wydziałów znajduje się w kolumnie 4, czyli musimy przesunąć zakres $A$8:$A$102 o 3 kolumny (czyli pozycja Południe – 1) w prawo. Właśnie w tym celu zastosowaliśmy funkcję PRZESUNIĘCIE. Powoduje ona, że zakres $A$8:$A$102 jest przesunięty o 0 wierszy w dół i odpowiednią ilość kolumn w prawo (w przypadku południe 3 kolumny). Znów formuła ILE.NIEPUSTYCH zapewnia nam, że na liście nie będą widoczne puste elementy, czyli wielkość zakresu będzie miała tyle wierszy ile jest niepustych komórek. Ostatecznie funkcja PRZESUNIĘCIE powoduje, że w źródle listy wybieralnej odwołujemy się do zakresu $D$8:$D$11.

Mam nadzieję, że to Wam pomogło. Zapraszamy za tydzień po kolejny wpis. 🙂

Plik Excela, w którym zastosowano funkcję PRZESUNIĘCIE znajdziecie tutaj:

Funkcja PRZESUNIĘCIE zwraca odwołanie do zakresu, który jest podaną liczbą wierszy lub kolumn począwszy od komórki lub zakresu komórek. Zwrócone obszar może być zarówno komórką jak i zakresem komórek.

Funkcja PRZESUNIĘCIE potrzebuje następujących parametrów:

odwołanie – komórka lub zakres od którego zaczynamy (od którego będziemy się przesuwać)
wiersze – o ile wierszy chcemy się przesunąć od komórki początkowej (lub odwołania)
kolumny – o ile kolumn chcemy się przesunąć od komórki początkowej (lub odwołania)
wysokość – jaka ma być wysokość zwróconego obszaru w ilości wierszy (możemy zmniejszyć lub zwiększyć zakres podany w odwołaniu) – argument opcjonalny
szerokość – analogicznie jak wysokość – jaka ma być szerokość zwróconego obszaru w ilości wierszy – argument opcjonalny

PRZYKŁAD 1:

Arkusz Przesuniecie1 w pliku do ściągnięcia.

karta developer

W prezentowanym powyżej przykładzie w komórce B6 wpisaliśmy formułę:

= PRZESUNIĘCIE(A2;1;1)

Jako pierwszy argument podajemy odwołanie do komórki A2, jako drugi i trzeci wpisujemy 1, czyli przesuwamy podany w odwołaniu zakres o jeden wiersz w dół i jedną kolumnę w prawo. W konsekwencji formuła zwróci wynik Nowak, czyli odwoła się do wartości znajdującej się w komórce B3. Dzieje się tak ponieważ komórka B3 znajduje się o jeden wiersz w dół i jedną kolumnę w prawo od komórki A2.

PRZYKŁAD 2

W arkuszu Przesuniecie2 tym razem bardziej zaawansowany przykład wykorzystania funkcji PRZESUNIĘCIE, który może być przydatny podczas tworzenia raportów. Za pomocą funkcji PRZESUNIĘCIE możemy stworzyć dynamiczny wykres.

W komórce D2 stworzyliśmy listę wybieralną (DANE/POPRAWNOŚĆ DANYCH), gdzie jako źródło podajemy zakres poszczególnych miesięcy podanych w tabeli.

W D2 wybieramy odpowiedni miesiąc, dla którego chcemy zobaczyć dane i odpowiedni wykres.

karta developer

Jednak najważniejsza w naszym przykładzie jest funkcja PRZESUNIĘCIE, która jest wykorzystana w zakresie B5:B11.

=PRZESUNIĘCIE($D$31:$D$37;0;MIESIĄC($D$2))

W pierwszym argumencie podaliśmy zakres $D$31:$D$37, czyli listę nazwisk (pierwsza kolumna w tabeli danych), drugi argument jest równy 0, ponieważ nie chcemy przesuwać zakresu w dół a jedynie w prawo, aby znaleźć dane na odpowiedni miesiąc. Jako argument 3 podaliśmy funkcję Miesiąc, która zwraca numer miesiąca, który wybraliśmy z listy wybieralnej znajdującej się w komórce D2. Jeżeli wybierzemy styczeń 15, to funkcja miesiąc zwraca 1, jak marzec 15 to funkcja miesiąc zwraca 3 itd. W związku z tym w trzecim argumencie funkcji PRZESUNIĘCIE mamy wartość zależną od wybranego przez nas miesiąca, i tak jeśli wybierzemy styczeń to przesuniemy się o 1 kolumnę w prawo i odwołamy się do zakresu $E$31:$E$37, czyli do wartości ze stycznia.

Co ważne formuła jest formuła tablicową. W praktyce od zwykłej formuły różni się to tylko tym, że przed wpisaniem formuły zaznaczamy cały zakres D5:D11 a nie jedną komórkę, a przy zatwierdzaniu funkcji wciskamy Ctrl + Shift + Enter zamiast samego Entera.

W ten sposób w komórkach D5:D11 uzyskujemy odpowiednie wartości dla wybranego przez nas miesiąca.

Stworzony przez nas wykres odwołuje się do danych z zakresu C5:D11, dzięki czemu osiągnęliśmy dynamiczny wykres, który zmienia się gdy zmienimy miesiąc w komórce D2

Dla zainteresowanych w arkuszu Przesuniecie + Sortowanie znajduje się przykład gdzie dane są automatycznie sortowane

Layout mode
Predefined Skins
Custom Colors
Choose your skin color
Patterns Background
Images Background

Zapisz się na nasz newsletter dotyczący nauki Excela

Wysyłaj mi newsletter (możesz się wypisać w każdym dowolnym momencie).

FreshMail e-mail marketing free online marketing software