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.

Na start - zdarzenie lewego przycisku myszy

Od kilku dni chodziło za mną, żeby zacząć zapisywać różne makra i inne pożyteczne przykłady z użytkownia Excela i VBA (głównie VBA), zanim wylecą z głowy bądź ulegną samozniszczeniu w przypadku awarii komputera.

Zastrzegam: nie jestem specjalistą w dziedzinie VBA. Programować lubię i robię to głównie hobbystycznie, a VBA zacząłem się uczyć na potrzeby aktualnej pracy - wiedzę czerpię na bieżąco w miarę potrzeb - z kursów, tutoriali i manuala.

Nie spodziewajcie się tutaj kursów czy poradników, raczej będą to zapiski dotyczące różnych problemów, które napotkałem podczas pisania makr, ewentualnie jakieś ciekawostki. Niemniej, w odnośnikach umieściłem kilka pozycji, na które warto wg mnie rzucić okiem.

Na początek, jako pierwszy problem - zdarzenie (event) kliknięcia lewego przycisku myszy na obiekcie arkusza.. no właśnie - nie ma takiego, i w tym jest problem. Jest BeforeRightClick, jest BeforeDoubleClick, ale nie ma tego nieszczęsnego lewego..

Na problem trafiłem, kiedy pisałem z nudów (ah praca ;) mini-grę w Excelu i chciałem, aby gracz sterował tylko klawiszami, a nie myszką (czyli chciałem uniknąć klikania lewym/prawym przyciskiem po komórkach). O ile prawy nie był problemem, tak z lewym musiałem pogoogle'ować. I znalazłem taki sposób oparty na zdarzeniu SelectionChange oraz funkcji GetAsyncKeyState z biblioteki user32. Przykładowy kod:

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer


Sub Worksheet_SelectionChange(ByVal Target As Range)


If GetAsyncKeyState(1) Then
MsgBox ("Naciśnięto lewy przycisk myszy.")
End If


End Sub

Ot - cała filozofia. Funkcja zwraca wartość true lub false, w zależności, czy naciśnięto klawisz odpowiadający argumentowi. Jako argument podaje się kod klawisza (nie jest to kod ASCII klawiatury) lub odpowiadającą jemu stałą. W przypadku lewego przycisku jest to 1 lub vbKeyLButton, w przypadku prawego: 2 lub vbKeyRButton.

Pozostałe wartości można zobaczyć w Object Browser (naciskając F2 w edytorze VBA), w module KeyCodeConstants.

..i tak oto dokończyłem wpis sprzed prawie roku.


Trochę wstyd, mam nadzieję, że poprawię się i będę bardziej regularny ;)