Wyobraź sobie, że mamy dane dotyczące sprzedaży dla firmy:
I że chcemy znaleźć sumy liczbowe za rok do chwili obecnej. Możemy dodać listę rozwijaną w następujący sposób:
Abyśmy mogli określić bieżący miesiąc. Dlatego teraz chcemy obliczyć bieżący rok na marzec. Najprostszym formatem byłoby posiadanie formuł, które rozszerzyłyby się w całym zakresie:
A potem zmienialiśmy formuły co miesiąc.
Jednak Excel pozwala na inne podejście. Moglibyśmy ustawić zakres dynamiczny, którego rozmiar zmieniał się w zależności od miesiąca, w którym się znajdujemy. Gdy zmieniamy miesiąc w menu rozwijanym, zmienia się rozmiar zakresu.
Tak więc dla miesiąca marca zakres ma długość 3 kolumn, a dla miesiąca czerwca będzie to 6 miesięcy.
Wielkość zakresu zależy od miesiąca. Jednym ze sposobów sformułowania tego jest użycie funkcji Miesiąc:
=Miesiąc(c8)
Gdzie c8 to adres komórki naszego menu rozwijanego. Jednak preferowaną metodą jest użycie funkcji PODAJ.POZYCJĘ do określenia pozycji bieżących miesięcy we wszystkich miesiącach w naszym raporcie:
PODAJ.POZYCJĘ(c8,$c$3:$j$3,0)
Gdzie:
• c8 to adres komórki z bieżącego miesiąca
• C3:J3 to adres wszystkich naszych miesięcy
• 0 ma zapewnić dokładne dopasowanie
Teraz możemy określić rozmiar naszego zakresu dynamicznego za pomocą funkcji PRZESUNIĘCIE, która ma 5 argumentów:
=PRZESUNIĘCIE(odniesienie;wiersze;kolumny;wysokość;szerokość)
Gdzie:
• Odwołanie to lewy górny róg naszego zakresu dynamicznego - komórka C5 - pierwsza komórka, którą chcemy zsumować
• Rzędy - liczba wierszy w dół od naszej komórki bazowej - to jest 0
• Cols – liczba kolumn w stosunku do naszego połączenia podstawowego – to jest 0
• Szerokość naszego zakresu dynamicznego - która w tym przypadku wynosi 3. Ponieważ jednak chcemy, aby zakres różnił się w zależności od miesiąca, umieścimy tutaj nasze formuły DOPASUJ
• Jest to wysokość naszego zakresu dynamicznego, który wynosi 1
Więc nasze formuły OFFSET to:
= PRZESUNIĘCIE(c5,0,0,PODZIEL(c8,$c$3:$j$3,0),1)
Na koniec musimy powiedzieć programowi Excel, aby SUM to podał pełne formuły jako:
= SUMA(PRZESUNIĘCIE(c5,0,0,PODZIEL(c8,$c$3:$j$3,0);1))
Mamy:
Teraz, jeśli zmienimy miesiąc w menu, poprawna liczba od początku roku przechodzi przez:
Ponieważ jest to automatyczna aktualizacja, to podejście ma następujące zalety:
• Nie ma potrzeby comiesięcznej zmiany receptur
• Ponieważ jest mniej zmian formuł, mniej miejsca na błędy
• Arkusz kalkulacyjny może być używany przez kogoś, kto ma ograniczoną wiedzę na temat Excela - może po prostu zmienić menu rozwijane i nie zawracać sobie głowy formułami
