Linia trendu i ograniczenia Excela

Ostatnio znajomy poprosił mnie o pomoc przy linii trendu i wykresie. Domyślnie Excel daje możliwość zastosowania jej na n okresów do przodu lub wstecz.

Przy danych dziennych aktualizowanych codziennie aż do końca miesiąca i założeniu, że linia trendu ma wskazywać trend do końca miesiąca, rodzi to problemy:

• po dodaniu danych dla kolejnego dnia należy zmniejszać liczbę okresów o 1, tak aby suma okresów z danymi istniejącymi była równa ilości dni danego miesiąca, w przeciwnym wypadku liczba okresów będzie przekraczać liczbę dni miesiąca,
• po dodaniu danych należy zmieniać zakres dla wartości serii na wykresie, aby uwzględniany był kolejny dzień (nie możemy uwzględniac na wykresie od razu całego miesiąca).

Dodatkowo znajomy prosił, aby obliczyć wartość trendu dla danego okresu, czego Excel również nie udostępnia standardowo. Co prawda jest opcja pokazywania wzoru, ale ten zmienia się wraz ze zmianą danych.

W przykładzie posłużę się wykresem obrazującym dane dla 31 dniowego miesiąca oraz dane w postaci narastającej (linia trendu będzie właśnie dla danych narastających). 

Najpierw rozwiążę sprawę płynnej aktualizacji linii trendu. Nie ukrywam - tutaj - zamiast wertowania manuali - pomogłem sobie nagrywaniem makra, krótko mówiąc nagrałem dodanie linii trendu oraz zmianę ilości okresów. Dodatkową zaletą jest to, że wiemy jaki indeks mają serie oraz linia trendu. Kod zmieniający linię trendu po drobnej zmianie wygląda tak:

Sub linia_trendu()
   
    Dim arkusz As Worksheet
    Dim wykres As ChartObject
    Dim zakres As Integer
   
    Set arkusz = Worksheets("Arkusz1")
    Set wykres = arkusz.ChartObjects("Wykres 2")
   
    zakres = Range("AI4") - Range("AI3")
    wykres.Chart.SeriesCollection(2).Trendlines(1).Forward = zakres
   
End Sub

Jak widać nic skomplikowanego, na dobrą sprawę można było napisać jednolinijkowca ;)

Za ilość okresów "do przodu" odpowiada właściwość Forward, która zmienia się w moim przypadku za pomocą wbudowanych funkcji w komórkach AI4 oraz AI3. Obliczona jest w nich ilość dni danego miesiąca oraz dany dzień:

AI3 =DZIEŃ(DZIŚ()) 
AI4 =DZIEŃ(DATA(2012;MIESIĄC(DZIŚ())+1;1)-1)

Ten drugi zestaw funkcji oblicza nam ostatni dzień danego miesiąca poprzez odjęcie jednego dnia od pierwszego dnia następnego miesiąca.

Pozostały jeszcze dwa problemy - ruchomy zakres wykresu oraz wartość linii trendu, ale to w następnym poście.