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