Formuła ogólna
=SUM(OFFSET(start,0,0,N,1))
Podsumowanie
Aby podsumować zestaw danych miesięcznych przez n liczbę miesięcy, można użyć formuły opartej na funkcjach SUMA i PRZESUNIĘCIE. W przedstawionym przykładzie formuła w G6 to:
=SUM(OFFSET(C5,0,0,G5,1))
Wyjaśnienie
W pokazanym przykładzie mamy dane miesięczne za lata 2017 i 2018. Celem jest dynamiczne sumowanie wartości przez określoną liczbę miesięcy, zakodowanych na stałe jako 6 w komórce G5. Odbywa się to poprzez podanie odniesienia zbudowanego za pomocą funkcji OFFSET do funkcji sumy.
Funkcja PRZESUNIĘCIE umożliwia utworzenie odniesienia przy użyciu punktu początkowego, przesunięcia wiersza i kolumny oraz wysokości i szerokości. PRZESUNIĘCIE jest przydatne w formułach, które dynamicznie uśredniają lub sumują „ostatnie n wartości”, „pierwsze n wartości” i tak dalej. W komórce G6 OFFSET jest skonfigurowany w następujący sposób:
OFFSET(C5,0,0,G5,1)
Przetłumaczone: z pozycji początkowej C5 utwórz odniesienie 6 rzędów x 1 kolumna bez przesunięcia. Z liczbą 6 w G5, OFFSET zwraca odniesienie C5: C10 do SUMA:
=OFFSET(C5,0,0,G5,1) // returns C5:C10 =SUM(C5:C10)) // returns 5775
Formuła w G7 jest skonfigurowana w ten sam sposób. Jedyną różnicą jest punkt wyjścia:
=OFFSET(D5,0,0,G5,1) // returns D5:D10 =SUM(D5:D10)) // returns 6380
Gdy liczba w G5 zostanie zmieniona, obie formuły dynamicznie aktualizują się i zwracają nowy wynik.
Różnica od początku roku
Zamiast zakodować wartość na stałe w formule, możesz użyć funkcji LICZBA, aby policzyć istniejące wpisy w danej kolumnie i zwrócić tę liczbę do PRZESUNIĘCIE. Ogólna wersja tej formuły wyglądałaby następująco:
=SUM(OFFSET(start,0,0,COUNT(rng),1))
gdzie rng jest odniesieniem do zakresu zawierającego wartości od początku roku.
