Excel,Triki

Funkcja przesuniecie

9 Sie , 2016  

Jedną z ulubionych moich funkcji w MS Excel jest funkcja PRZESUNIĘCIE. Można ją wykorzystać na wiele sposobów, najpopularniejszy sposób to dynamicznie zmieniający się wykres.

Efekt końcowy widać na poniższym rysunku:
przesuniecie_4

 

Co trzeba wiedzieć o funkcji aby móc z niej skorzystać?

Funkcja PRZESUNIĘCIE potrzebuje 5 parametrów, gdzie 2 są opcjonalne.

Składnia
PRZESUNIĘCIE(odwołanie;wiersze;kolumny;[wysokość];[szerokość])

Odwołanie – Argument wymagany. Konkretna komórka w Excelu, od której będzie wskazywany zakres komórek lub komórka.

Wiersze – Argument wymagany. O ile wierszy należy się przesunąć od komórki, aby dostać się do żądanego zakresu lub komórki.

Kolumny – Argument wymagany. O ile kolumn należy się przesunąć od komórki, aby dostać się do żądanego zakresu lub komórki.

Wysokość – Argument opcjonalny. Liczba wierszy, z których składa się zakres, który chcemy pobrać.

Szerokość – Argument opcjonalny. Liczba kolumn, z których składa się zakres, który chcemy pobrać.

Tyle teorii zaczynamy część praktyczną!

1. Najlepiej poniższą tabele skopiować do excel’a lub pobrać plik dane_przesuniecie.xlsm

Rok Messi Ronaldo
 2015/2106  41 51
 2014/2015  58  61
 2013/2014  41  51
 2012/2013  60  55
 2011/2012  73  60
 2010/2011  53  53
 2009/2010  47  33

 

2. Przechodzimy do menu Developer rozwijamy Wstaw i wybieramy narzędzie Przycisk pokrętła

przesuniecie_1

3. Stojąc kursorem na przycisku pokrętła, prawym klawiszem myszy wybieramy polecenie przesuniecie_2

przesuniecie_1

4. Pojawia się nam nowe okno gdzie w polu Łącze komórki wprowadzamy wartość: = $E$1, oraz ustawiamy wartość minimalną i maksymalną.

przesuniecie_3

Efekt końcowy – klikając na pokrętło zmienia nam się dynamicznie wartość w komórce E1.  Przycisk pokrętła jest już gotowy do wykorzystania go w funkcji przesunięcie.

5. Przechodzimy do funkcji przesunięcie, ustawiamy aktywną komórkę E2, i wprowadzamy w pasku formuł polecenie „=PRZESUNIĘCIE(A2;0;$E$1;1;1)” i naciskamy enter.

przesuniecie_4

 

Wyjaśnienie formuły:

przesuniecie_5

Żeby obrazowo wyjaśnić o co chodzi z tym zakresem przesuniecie_6 skopiujmy formułę z komórki E2 do E3.

 

 

W komórce E1 mamy wartość 1 wiec wyświetlają nam się dane z pierwszej kolumny od kolumny A.

przesuniecie_7

Jeśli zmodyfikujemy formulę w komórce E3 na: =SUMA(PRZESUNIĘCIE(A2;0;$E$1;1;2)), pamiętamy że ostatni parametr steruje szerokością. W tym przypadku podaliśmy 2 czyli 2 kolumny, wówczas efekcie końcowym będziemy mieli zsumowane dwie wartości 41 + 51 = 92

przesuniecie_8

Koniec wyjaśnień na temat formuły.

 

6. Stajemy na komórce E2 i przeciągamy formułę do komórki E9

przesuniecie_2

 

Kręcąc pokrętłem mamy efekt końcowy czyli zmieniają nam się wartości w kolumnie E. Wystarczy teraz zaznaczyć ten zakres plus zakres z kolumny A i wstawić wykres, który będzie zmieniał się dynamicznie.

przesuniecie_3

 

7. Do zmiany zdjęcia został wykorzystany prosty kod vba podpięty do pokrętła:

Gotowy plik do pobrania: przesuniecie.xlsm

 

 

, ,