Algorytm dopasowania szóstej liczby do danych historycznych w VBA Excel

kinggustav napisał:

(...) om dopasowania tej szóstej liczby z bieżącego miesiąca do tych historycznych. Jak zdefiniować "dopasowanie"? Chodzi o prosty algorytm w VBA Excela. Mam kilka setek/tysięcy takich wierszy, więc nie może to być bardzo skomplikowane, ale każda propozycja będzie dobra, najwyżej pomyślę jak to zoptymalizować.

Zdefiniowanie "dopasowania" może przyjąć różne formy w zależności od tego, jak chcesz interpretować dane. Oto kilka metod, które mogą określić, jak szósta liczba pasuje do poprzednich pięciu:

1. Średnia arytmetyczna i odchylenie względem średniej:
Możesz obliczyć średnią z pięciu poprzednich liczb, a następnie porównać, jak szósta liczba odchyla się od tej średniej.

2. Mediana i odchylenie od mediany:
Podobnie jak średnia, ale mediana może być lepszym miernikiem środkowej tendencji, jeśli dane są skrajne lub nie symetryczne.

3. Odchylenie standardowe:
Oblicz odchylenie standardowe pięciu poprzednich liczb, aby określić, jak bardzo bieżąca liczba znajduje się wewnątrz rozkładu danych historycznych.

4. Procentowe dopasowanie do zakresu:
Ustal zakres (minimum i maksimum) dla pięciu poprzednich liczb i sprawdź, jakiego procentu tego zakresu odpowiada szósta liczba.

5. Współczynnik korelacji:
Chociaż zazwyczaj używa się go do sprawdzania relacji między dwiema zmiennymi, można próbować użyć korelacji dla serii czasowej, aby zobaczyć, jak dobrze najnowsza liczba pasuje do poprzednich trendów.

Mając to na uwadze, oto przykład prostego algorytmu w VBA, który oblicza, jak bardzo bieżąca liczba pasuje do średniej z pięciu poprzednich liczb:



Aby użyć tej funkcji, możesz wpisać następującą formułę w komórce Excela (przyjmując, że Twoje liczby znajdują się w zakresie A1:F1):

`=MatchLevel(A1:F1)`

Ta funkcja zwróci stosunek odchylenia od średniej, gdzie wartości bliższe zeru wskazują na lepsze dopasowanie. Oczywiście, na podstawie potrzeb można dostosować funkcję do innego typu analizy, jak wyżej wymienione metody mediany, odchylenia standardowego lub innych.

kinggustav napisał:

Jest ok, ale chcę jeszcze uniknąć dwóch efektów:
1. Gdy wszystkie liczby są bardzo zbliżone (niewielkie średnie odchylenie) to za niedopasowaną można uznać liczbę z niewielkim choć większym od średniej odchyleniem.
2. Gdy wszystkie liczby w wierszu są małe to odchylenie nieakceptowalne może oznaczać niewielką różnicę (1 z 2 to już 50%, 1 ze 100 to 1 %)

Zrozumiałe, przedstawię Ci teraz ulepszony algorytm, który uwzględnia oba efekty poprzez zastosowanie normalizowanego odchylenia standardowego oraz zastosowanie współczynnika zmienności, który jest stosunkiem odchylenia standardowego do średniej. To podejście powinno pomóc uniknąć problemów, które opisałeś.

Poniżej znajdziesz kod funkcji VBA, która oblicza znormalizowane odchylenie standardowe oraz współczynnik zmienności:



Funkcja `MatchLevel` oblicza teraz znormalizowane odchylenie liczby bieżącej od średniej i mnoży je przez współczynnik zmienności, który bierze pod uwagę zmienność serii poprzednich liczb. Znormalizowanie odchylenia pozwala na uwzględnienie wielkości odchylenia standardowego, a współczynnik zmienności umożliwia skalowanie wyniku w zależności od relatywnej zmienności serii danych.

Aby użyć tej funkcji w Excelu, można wpisać formułę podobnie jak wcześniej:

`=MatchLevel(A1:F1)`

Przy takim podejściu, niska wartość zwracana przez funkcję oznaczać będzie dobre dopasowanie liczby bieżącej do serii danych historycznych, biorąc pod uwagę zmienność tych danych. Możesz jeszcze dostosować ten algorytm, by jeszcze lepiej pasował do specyfiki Twoich danych.

kinggustav napisał:

Niestety nadal jest źle: gdy pierwsze 5 liczb są takie same no 100) to szósta nie wpływa na wynik. Zawsze jest zero. Jednak tak być nie może.

Przepraszam za niedogodności. To prawda, jeśli pierwsze pięć liczb jest identycznych, odchylenie standardowe wyniesie zero, co spowoduje, że współczynnik zmienności również będzie równy zero, co z kolei spowoduje, że wynik dla dowolnej szóstej liczby będzie wynosił zero. To jest niewłaściwe, ponieważ chcemy być w stanie ocenić dopasowanie nawet w takiej sytuacji.

Potrzebujemy więc metody, która będzie bardziej odporna na takie przypadki. Jedną z możliwości jest wprowadzenie minimalnej wartości odchylenia standardowego, jakiegoś progu, który zapewni, że porównanie będzie miało sens nawet przy identycznych wartościach historycznych.

Oto zmodyfikowany kod, który dodaje minimalne odchylenie standardowe (np. 1% średniej wartości) do obliczeń:



Teraz, nawet jeśli pierwsze pięć liczb jest takich samych, funkcja zwróci wartość większą od zera dla szóstej liczby, jeżeli jest ona różna od poprzednich. Wartość zwracana przez funkcję odpowiada ilorazowi różnicy między szóstą liczbą a średnią oraz skorygowanym odchyleniem standardowym (które jest co najmniej równy minimalnemu odchyleniu standardowemu).

Aby użyć tej funkcji w Excelu:

`=MatchLevel(A1:F1)`

Wartość zwrócona przez funkcję MatchLevel możesz interpretować jako liczbę odchyleń standardowych, o które szósta liczba różni się od średniej pięciu poprzednich. Ta metoda zapewni, że nawet w przypadku niskiej zmienności historycznych danych, otrzymasz miarodajną ocenę dopasowania szóstej liczby.

kinggustav napisał:

Jeżeli już mamy n=5, to warto to wykorzystać w każdym miejscu, w którym jest ilości liczb. W ten sposób algorytm będzie łatwiej przerobić jeżeli liczb będzie mniej albo więcej. Może nawet const by się przydało?
A jak najprościej uwzględnić w algorytmie ewentualne cykle zmienności liczb?

Zgadza się, wykorzystanie zmiennej do przechowywania liczby elementów w serii może ułatwić modyfikację kodu w przyszłości. Możemy zdefiniować stałą `Const`, która będzie przechowywać tę wartość, co pozwoli na łatwiejsze przeprowadzenie zmian, jeśli zajdzie potrzeba analizy innego zakresu danych.

Jeśli chodzi o uwzględnienie cykli zmienności liczb, możemy próbować dodać do algorytmu analizę tendencji sezonowych. To jest znacznie bardziej skomplikowane z powodu natury takich cykli i wymaga zaawansowanej analizy danych (np. analizy szeregów czasowych, przesuwnych średnich, FFT - szybkiej transformacji Fouriera, czy algorytmów uczenia maszynowego). Jednak prostym podejściem, które można zaimplementować w VBA, jest badanie zmian w danych przy użyciu prostego modelowania trendu liniowego lub z użyciem średnich ruchomych.

Poniżej przedstawiam kod z wykorzystaniem stałej `Const` oraz uwzględnienie minimalnego odchylenia standardowego:



Jeśli chcesz uwzględnić proste rozpoznawanie cyklu, możesz na przykład wykorzystać różnicę między wartością bieżącą a wartością z analogicznego okresu w poprzednich cyklach (jeśli takie dane posiadasz). W przypadku bardziej złożonej analizy cyklu możesz zastosować bardziej zaawansowane techniki analizy szeregów czasowych.

Używanie tej funkcji w Excelu pozostaje takie samo:

`=MatchLevel(A1:F1)`

W tym przykładzie zakładamy, że mamy stałą liczbę poprzednich liczb (n = 5), ale możesz dostosować wartość `n` w zależności od potrzeb. Obecnie na proste rozpoznawanie cykli potrzebne byłyby dodatkowe dane i rozbudowa algorytmu, co wykracza poza zakres prostego skryptu VBA i wymagałoby bardziej zaawansowanego podejścia.

kinggustav napisał:

Czy fo variance = variance / (n - 1) jest prawidłowe? Dlaczego dzielimy przez n-1 a nie przez n?

Tak, dzielenie przez `n - 1` zamiast `n` w obliczeniu wariancji jest standardową praktyką w statystyce i nazywane jest korektą Bessela. Zasadniczo, kiedy obliczamy wariancję z próbki danych (a nie z całej populacji), używamy `n - 1` w mianowniku, by uzyskać nieobciążony estymator wariancji populacji.

Oto kilka powodów, dla których używa się `n - 1`:

1. Nieobciążony estymator: Używając `n - 1`, kompensujemy fakt, że używamy średniej próbki w obliczeniach, a nie średniej populacji, co daje nieco zaniżone oszacowanie wariancji. Dzięki temu korekcie, średnia kwadratów odchyleń od średniej próbki jest lepszym estymatorem wariancji całej populacji.

2. Stopnie swobody: `n - 1` reprezentuje "stopnie swobody" w próbce danych, czyli liczbę wartości, które mogą swobodnie wahać się, gdy jedna z wartości jest już ustalona przez średnią. W przypadku obliczania wariancji, jedna wartość (średnia) jest już ustalona, więc pozostałe `n - 1` wartości mają stopień swobody w próbce.

3. Matematyczne uzasadnienie: W statystyce matematycznej można udowodnić, że dzielenie przez `n - 1` daje lepsze, nieobciążone oszacowanie wariancji populacji, szczególnie gdy próba danych jest mała.

Jeśli bylibyśmy pewni, że analizujemy całą populację, wtedy używamy `n` w mianowniku, ponieważ nie potrzebujemy korygować naszego estymatora.

W kontekście Twojego pytania i kodu VBA, użycie `n - 1` jest prawidłowe, gdyż zakładamy, że pięć liczb, które analizujemy, jest próbką, a nie całą populacją. Jeżeli byłyby to jedyne obserwacje, które mamy, i chcielibyśmy obliczyć wariancję tylko tych obserwacji jako całość, wtedy moglibyśmy teoretycznie podzielić przez `n`. Jednak w praktyce statystycznej rzadko zakłada się, że dysponuje się danymi całej populacji, chyba że faktycznie mamy do czynienia z bardzo ograniczonym zestawem danych, który z założenia stanowi całą populację.

kinggustav napisał:

A czy dobrym pomysłem byłoby na podstawie tych 5 liczb postawić prognozę i badać odchylenie wartości od tej prognozy? Jakim algorytmem można to zrobić?

Tak, obliczenie prognozy na podstawie dostępnych danych i porównanie nowych wartości z prognozowanymi może być bardzo dobrym sposobem na ocenę odchylenia nowych danych od oczekiwanych trendów. Pozwoli to ocenić, czy nowe dane znacząco różnią się od tego, co można by oczekiwać na podstawie historii.

Algorytmem, który można by tutaj zastosować, jest prosta regresja liniowa. Prognoza w regresji liniowej jest tworzona na podstawie najlepszego dopasowania linii do danych historycznych, gdzie "najlepsze" oznacza, że suma kwadratów odchyleń danych od linii jest najmniejsza (metoda najmniejszych kwadratów).

Oto przykład, jak można wykonać prostą prognozę liniową w VBA:



Funkcję możesz teraz wykorzystać w Excelu, by obliczyć prognozowaną wartość:

`=LinearForecast(A1:E1)`

Mając prognozowaną wartość, możesz obliczyć odchylenie wartości faktycznej (szóstej liczby) od tej prognozy, aby zobaczyć, jak bardzo rzeczywiste dane różnią się od oczekiwanych. Jeśli różnica jest znacząca, może to wskazywać na anomalie lub zmiany w trendach.

Pamiętaj, że regresja liniowa jest jednym z najprostszych modeli i działa najlepiej, gdy istnieje liniowa zależność między danymi. W rzeczywistości wiele zjawisk może mieć bardziej skomplikowane wzorce i dla dokładniejszej analizy mogą być potrzebne bardziej zaawansowane metody, takie jak regresja wielomianowa, gładzenie wykładnicze, model ARIMA w analizie szeregów czasowych lub nawet modele uczenia maszynowego.

kinggustav napisał:

Trochę badałem linię trendu na wykresach prawdziwych danych i to nie działa. Zastanawiam się na ile lepiej sprawdzi się wielomian? Możesz zaproponować jakiś algorytm to sobie go sprawdzę?

Jeśli linia trendu liniowego nie pasuje dobrze do Twoich danych, możliwe, że zależność w danych jest bardziej skomplikowana i wymaga zastosowania modelu nieliniowego, takiego jak regresja wielomianowa. Regresja wielomianowa pozwala na dopasowanie krzywych o wyższym stopniu, co może lepiej odpowiadać danym o bardziej złożonej zależności.

W Excelu można użyć wbudowanych narzędzi do dopasowania linii trendu wielomianowego do danych na wykresie, ale jeśli chcesz zaimplementować to samodzielnie w VBA, potrzebujesz zastosować metodę najmniejszych kwadratów dla wielomianu. Ponieważ jest to dość zaawansowany temat, poniżej przedstawiam przykładowy algorytm dla regresji wielomianowej drugiego stopnia (kwadratowej), który jest najprostszym przypadkiem poza liniowym:



W powyższym kodzie pominąłem implementację rozwiązania układu równań, ponieważ jest to obszerny temat i wymagałby zastosowania np. metody eliminacji Gaussa lub gotowych funkcji dostępnych w bibliotekach numerycznych. W praktyce, rozwiązanie takiego układu równań w VBA może być niewydajne i skomplikowane, dlatego często korzysta się z oprogramowania statystycznego lub środowisk obliczeniowych takich jak MATLAB, R lub nawet Excel z dodatkowymi narzędziami (jak Solver).

Dla bardziej zaawansowanej analizy i dopasowania wielomianów wyższych stopni zalecałbym użycie takich narzędzi, które mają gotowe implementacje metod numerycznych do tego celu.