Funkcje statystyczne - Excel
Ryszard
W logistycznych zagadnieniach zarządzania zapasami występuje tzw. zapas
bezpieczeństwa.
Wzór na w/w zapas zawiera tzw współczynnik bezpieczeństwa oznaczany omega, który
jest funkcją poziomu obsługi klienta POK. Wartość tego współczynnika zależy od
przyjętego POK-u ale również od przyjętego rozkładu sttystycznego dla profilu
poputu (zapotrzebowania).
Dka rozkładu normalnego zależności te liczy funkcja Rozkład.Normalny.s oraz
Rozkład.Normalny.s.odw.
Rozkład.Normalny.s(0,95)=0,828944
Rozkład.Normalny.s.odw(0,828944)=0,95
Jak policzyć tego typu zależności dla rozkładów exponencjalnego lub Poissona ?
Pozdrawiam
Ryszard
WuKa
news:gph976$ogb$1...@news.onet.pl...
>
> Rozkład.Normalny.s(0,95)=0,828944
> Rozkład.Normalny.s.odw(0,828944)=0,95
>
> Jak policzyć tego typu zależności dla rozkładów exponencjalnego lub
> Poissona ?
> Pozdrawiam
>
> Ryszard
Ponieważ są funkcje ROZKŁAD.EXP(..) i ROZKŁAD.POISSON(..) które z parametrem
PRAWDA dają dystrybuanty (czyli pp. skumulowane) a brakuje funkcji
odwrotnych ODW, musisz skorzystać choćby z opcji Narzędzia->Szukaj wyniku i
zmusić Excel do znalezienia pary liczb bez używania funkcji odwrotnej. Jedną
z nich ustawiasz na założoną wartość, a drugą Excel sam znajdzie, by
realizowała tę pierwszą wartość. Nie wspominając o Solverze, który taki
proces uogólnia.
WuKa
SDD
Użytkownik "Ryszard" <ryszar...@neostrada.pl> napisał w wiadomości
news:gph976$ogb$1...@news.onet.pl...
> Jak policzyć tego typu zależności dla rozkładów exponencjalnego lub
> Poissona ?
Bezposrednio ze wzoru na gestosc, z uzyciem Funkcji EXP() tudziez LN().
Do Poissona jeszcze silnia sie przyda :)
Pozdrawiam
SDD
Ryszard
Próbowałem z tymi funkcjami ale coś mi nie wychodzi.
Pozwolę sobie wkleić fragment tabelki, w której są wartości, które chciałbym
obliczyć dla danych z kolumny pierwszej. Drugą kulumnę liczy funkcja
ROZKŁAD.NORMALNY.S()a pozostałe ?
Poziom obsługi POK jako funkcja wsp.bezpieczeństwa omega dla rozkładów:
normalnego,
wykładniczego i Poissona
omega Rozkład Rozkład Rozkład Poissona dla wartości średniej P=
normalny wykładniczy 1 2 3 4 5
,,, ,,, ,,, ,,, ,,, ,,, ,,, ,,,
1,85 0,9678 0,9422 0,9197 0,9473 0,9665 0,9489 0,9682
1,90 0,9713 0,9450 0,9197 0,9473 0,9665 0,9489 0,9682
1,95 0,9744 0,9477 0,9197 0,9473 0,9665 0,9489 0,9682
2,00 0,9773 0,9502 0,9810 0,9473 0,9665 0,9786 0,9682
2,05 0,9798 0,9526 0,9810 0,9473 0,9665 0,9786 0,9682
2,10 0,9821 0,9550 0,9810 0,9473 0,9665 0,9786 0,9682
2,15 0,9842 0,9571 0,9810 0,9843 0,9665 0,9786 0,9682
2,20 0,9861 0,9592 0,9810 0,9843 0,9665 0,9786 0,9682
2,25 0,9878 0,9612 0,9810 0,9843 0,9665 0,9786 0,9863
,,, ,,, ,,, ,,, ,,, ,,, ,,, ,,,
Pozdrawiam
Ryszard
Użytkownik "WuKa" <wlk...@op.pl> napisał w wiadomości
news:gphcle$206v$2...@news2.ipartners.pl...
WuKa
> Dzięki.
> Próbowałem z tymi funkcjami ale coś mi nie wychodzi.
>
> Pozwolę sobie wkleić fragment tabelki, w której są wartości, które
> chciałbym obliczyć dla danych z kolumny pierwszej. Drugą kulumnę liczy
> funkcja ROZKŁAD.NORMALNY.S()a pozostałe ?
>
zapisane jako (zapis symboliczny, nie excelowy):
kol(2) ROZKŁAD.NORMALNY.S(kol(1))
kol(3) ROZKŁAD.EXP(kol(1);Lambda;PRAWDA)
kol(4) ROZKŁAD.POISSON(kol(1);ŚR_POISS_1;PRAWDA)
kol(5) ROZKŁAD.POISSON(kol(1);ŚR_POISS_2;PRAWDA)
kol(6) ROZKŁAD.POISSON(kol(1);ŚR_POISS_3;PRAWDA)
kol(7) ROZKŁAD.POISSON(kol(1);ŚR_POISS_4;PRAWDA)
kol(8) ROZKŁAD.POISSON(kol(1);ŚR_POISS_5;PRAWDA)
i teraz moje uwagi/pytania dotyczące istoty problemu, a ściślej, czego tu
szukamy? Czy jest to zadanie z potrzebą identyfikacji parametrów rozkładu na
podstawie wyników? Proszę bowiem spojrzeć:
w kol(3) z rozkładem EXP brak jest danej a priori wartości Lambda, zatem
metodą Szukaj_wyniku z żądaniem zgodności liczb 0.9422 i
ROZKŁAD.EXP(1.85;Lambda,PRAWDA) ustalam, że Lambda=1.4967698. Niestety,
jeśli zapuścić tę formułę w dół kolumny z omegą, wartości różnią się od
liczb z kolumny (3). Wniosek: dane nie pochodzą od żadnego rozkładu EXP, bo
nie istnieje wspólna Lambda realizująca identyczne z podanymi wyniki. Czyżby
to były dane z próby losowej i była zweryfikowana hipoteza o rozkładzie
wykładniczym?
w kulmnach (4)-(8) sytuacja wygląda jeszcze dziwniej. Jeśli bowiem podane
liczby 1,2,3,4,5 uznać, za kolejne średnie, to podane wyniki są kompletnie
niezgodne z rzeczywistymi a wywołanymi przez formuły
ROZKŁAD.POISSON(kol(1);1;PRAWDA), ROZKŁAD.POISSON(kol(1);2;PRAWDA) itp.
Jeśli szukać własnych średnich w/g metody Szukaj_wyniku, to znów pojawi się
niegodność w wynikach ale już nie tak rażąca i stąd wniosek: podane kolumny
(4)-(8) nie są możliwe do uzyskania przez żaden rozkład Poissona w sposób
zadany analitycznie. Można jedynie uważać, że będą przybliżone rozkładem
Poissona.
WuKa
Ryszard
Doszedłem do tego samego.
Buduję arkusz do zarządzania zapasami i we wzorze na zapas informacyjny
występuje parametr omega
Zap.Inf=P*T+omega*sgrt(P^2*sigmaT^2+T*sigmaP^2)
Parametr ten jest zależny od profilu popytu (u mnie - normalny,exponencjalny,
Poissona)
i od poziomu obsługi np. 0,95.
Chciałem to zautomatyzować ...
Chyba, że w tablicach są błędy ...
Pozdrawiam
Ryszard
Użytkownik "WuKa" <wlk...@op.pl> napisał w wiadomości
news:gpjba2$30cs$1...@news2.ipartners.pl...
WuKa
> Chciałem to zautomatyzować ...
> Chyba, że w tablicach są błędy ...
>
> Pozdrawiam
> Ryszard
automatyzowane i z czym ma się do czynienia. Wzór na omegę nie ma tu
znaczenia, bo to jakiś parametr przecież, ponadto nie jest jasne, czym są P
i T i jaki mają związek z rozkładami.
Tym razem ja zacytuję "moje" liczby, jakie teoretycznie powinny były być w
kolumnach
(liczby pod numerami kolumn, to Lambda dla EXP oraz 5 kolejnych średnich dla
POISSON'a)
(1) (2) (3) (4) (5) (6) (7) (8)
1.4968 0.4687 0.3664 0.2856 0.3601 0.2778
1.85 0.9678 0.9373 0.9191 0.9472 0.9662 0.9488 0.9679
1.90 0.9713 0.9418 0.9191 0.9472 0.9662 0.9488 0.9679
1.95 0.9744 0.9460 0.9191 0.9472 0.9662 0.9488 0.9679
2.00 0.9772 0.9499 0.9879 0.9938 0.9969 0.9940 0.9971
2.05 0.9798 0.9535 0.9879 0.9938 0.9969 0.9940 0.9971
2.10 0.9821 0.9569 0.9879 0.9938 0.9969 0.9940 0.9971
2.15 0.9842 0.9600 0.9879 0.9938 0.9969 0.9940 0.9971
2.20 0.9861 0.9629 0.9879 0.9938 0.9969 0.9940 0.9971
2.25 0.9878 0.9655 0.9879 0.9938 0.9969 0.9940 0.9971
WuKa
Ryszard
P to popyt w okresie t.
P są liczbami dużo wiekszymi od jedności.
Trudno żeby w takim przypadku średnia była mniejsza od 1.
Dla średnich rzędu kilkunastu wartości te zerowe.
Z teorii zarządzania zapasami ...
Mamy popyt (prognozę) sprzedaży na następny rok. Powiedzmy dane tygodniowe.
t 1 2 3 4 5 6 ....
P 56 34 23 78 42 12 .....
Budujemy histogram i doposowujemy do rozkładu teoretycznego - otrzymujemy jakis
rozkład ... np. exponencjalny.
Obliczamy średnią, odchylenie standardowe ...
Pewien zapas - zwany informacyjnym (potrzebny do sytemu zamawiania) składa sie z
popytu średniego w czasie cyklu uzupełnienia zapasu T i tzw. zapasu
bezpieczeństwa (z uwagi na odchylenia, niespodziewany zwiększony popyt,
opóźnienie w dostawach ...). Zapas bezpieczeństwa zależy m.in. od współczynnika
bezpieczeństwa - omega - który to jest funkcją rozkładu i założonego poziomu
obsługi klienta. Np. zgodnie z tabelą (i akurat tutaj w zgodzie z fukcją
Rozkład.Normalny.s.odw())dla POK 95% wynosi on 1,6448.
Funkcja Rozkłąd.Normalny.s() i ........odw()liczy dla rozkładu N(0,1). Czyli
jeżeli chcemy policzyć dystrybuantę dla N(średnia,odchylenie st.)to nie możemy
wstawić wartość bezpośrednio do tego wzoru tylko wpierw dokonać normalizacji
tzn. wstawić Z=(z-średnia)/odchylenie st.
Może w przypadku pozostałych rozkładów też tak trzeba. Ale czy normalizuje się
analogicznie - tego (jeszcze) nie wiem ...
Szukam dalej ...
Kazda pomoc mile widziana ...
Pozdrawiam
Ryszard
Użytkownik "WuKa" <wlk...@op.pl> napisał w wiadomości
news:gpjusv$9c2$1...@news2.ipartners.pl...
Czaq
Użytkownik "Ryszard" <ryszar...@neostrada.pl> napisał
> Jak policzyć tego typu zależności dla rozkładów exponencjalnego lub
> Poissona ?
z tego co pamiętam (a jestem już dawno po egzaminie zestatystyki) to dla exp
możesz użyć:
(-ln(1-szukany kwantyl)) / lambda
albo też metodą chybił trafił szukasz takiej wartości skumulowanego rozkąłdu
exp, żeby w wyniku wyszedł Ci szukany kwantyl.
Pozdrawiam
czaq
WuKa
news:gpsts3$p21$1...@atlantis.news.neostrada.pl...
>
> z tego co pamiętam (a jestem już dawno po egzaminie zestatystyki) to dla
> exp możesz użyć:
>
> (-ln(1-szukany kwantyl)) / lambda
>
> albo też metodą chybił trafił szukasz takiej wartości skumulowanego
> rozkąłdu exp, żeby w wyniku wyszedł Ci szukany kwantyl.
>
> Pozdrawiam
> czaq
Zamiast metody na chybił trafił, powinieneś ogólniej powiedzieć, że
należałoby rozwiązać równanie F(q)=p, czyli q=F^(-1)(p), gdzie F(..) -
dystrybuanta, p - poziom prawdopodobieństwa (tzw. rząd kwantyla w %) i q -
szukany kwantyl. Tak się dzieje dla rozkładów ciągłych, dla których
poszukiwanie funkcji odwrotnej jest możliwe i nie sprawia trudności
analitycznych. Jeśli była mowa o Excelu jako narzędziu, który ma te funkcje
(F i F^(-1)) dla wielu rozkładów gotowe do użycia, nie ma wyraźnej potrzeby
sięgania po własne pomysły, choć nie są przecież zabronione.
WuKa