Sumy pośrednie a funkcje statystyczne

[Quasi]

Witam :)

Jak wiadomo Sumy pośrednie to funkcja matematyczna znajdująca się w
Excelu. Często z niej korzystam (w połączeniu z autofiltrem) bo moim
zdaniem daje bardzo duże możliwości. Bardzo duże z jednej strony
mozliwości, ale też i spore (przynajmniej na pozór) ograniczenia z
drugiej strony. W prosty sposób można bowiem skorzystać z 11
wbudowanych funkcji, problem zaczyna się gdy chcemy obliczyć medianę,
wartość najczęściej występującą w zbiorze danych czy np. percentyle.

I teraz zadanie. Załóżmy, że chcemy porównać ze sobą dwóch koszykarzy
pod względem tego ile punktów rzucają na mecz- niech to będzie Kobe
Bryant i Allen Iverson. Prowadzimy w Excelu arkusz, w których
zamieszczamy statystyki od początku sezonu (W kolumnie A mamy nazwiska
tych koszykarzy- w kolumnie B sumę punktów jaką uzyskali w każdym
meczu, w kolumnie C datę rozegrania meczu, w kolumnie D nazwę
przeciwnika). I teraz po wpisaniu danych (np. 50 meczów) filtrujemy
kolumnę A i zostawiamy tylko Kobe Bryanta. Bez problemu mozemy
obliczyć średnią, sumę wszystkich punktów, minimalną i maksymalną
liczbę punktów w meczu czy ilość meczów w ogóle, ale dodatkowo chcemy
obliczyć:

a) medianę
b) kwartyle (dolny i górny) i percentyl (np. 0,9)
c) wartość występującą najczęściej
d) chcemy zeby excel zwrócił nam wiersz (datę i przeciwnika) z
największa ilością rzuconych punktów
e) częstość

Proszę zaproponować formuły :)

Pozdrawiam

[Tajan]

Post by Quasi
Witam :)
Jak wiadomo Sumy pośrednie to funkcja matematyczna znajdująca się w
Excelu. Często z niej korzystam (w połączeniu z autofiltrem) bo moim
zdaniem daje bardzo duże możliwości. Bardzo duże z jednej strony
mozliwości, ale też i spore (przynajmniej na pozór) ograniczenia z
drugiej strony. W prosty sposób można bowiem skorzystać z 11
wbudowanych funkcji, problem zaczyna się gdy chcemy obliczyć medianę,
wartość najczęściej występującą w zbiorze danych czy np. percentyle.
I teraz zadanie. Załóżmy, że chcemy porównać ze sobą dwóch koszykarzy
pod względem tego ile punktów rzucają na mecz- niech to będzie Kobe
Bryant i Allen Iverson. Prowadzimy w Excelu arkusz, w których
zamieszczamy statystyki od początku sezonu (W kolumnie A mamy nazwiska
tych koszykarzy- w kolumnie B sumę punktów jaką uzyskali w każdym
meczu, w kolumnie C datę rozegrania meczu, w kolumnie D nazwę
przeciwnika). I teraz po wpisaniu danych (np. 50 meczów) filtrujemy
kolumnę A i zostawiamy tylko Kobe Bryanta. Bez problemu mozemy
obliczyć średnią, sumę wszystkich punktów, minimalną i maksymalną
liczbę punktów w meczu czy ilość meczów w ogóle, ale dodatkowo chcemy
a) medianę
b) kwartyle (dolny i górny) i percentyl (np. 0,9)
c) wartość występującą najczęściej
d) chcemy zeby excel zwrócił nam wiersz (datę i przeciwnika) z
największa ilością rzuconych punktów
e) częstość
Proszę zaproponować formuły :)

Witam!

Ja w takich przypadkach nie "bawię się" i piszę własną funkcję użytkownika w
VBA, która z zadanego obszaru zwraca mi tylko wartości z widocznych wierszy.
Przykładowo:

Function Widoczne(zakr As Range)
Dim kom As Range
Dim wiersz As Range
Dim tempArr() As Variant
Dim ileW As Long
Dim w As Long, k As Long

For Each wiersz In zakr.Rows
If Not wiersz.EntireRow.Hidden Then
ileW = ileW + 1
ReDim Preserve tempArr(1 To ileW)
With Application.WorksheetFunction
tempArr(ileW) = .Transpose(.Transpose(wiersz.Value))
End With
End If
Next

Widoczne = tempArr

End Function

i stosuję tak:

=WYST.NAJCZĘŚCIEJ(WIDOCZNE(B2:C10))

Tajan

[Quasi]

Wszystko gra świetnie :) - zastanawiam się jeszcze czy tym sposobem
dałoby się uaktywnić funkcje: Licz.Jeżeli i Suma.Jeżeli.
Formuła:
=LICZ.JEŻELI(WIDOCZNE($B$2:$B$10);"Iverson")
zwraca mi błąd #ARG!

[Tajan]

Witam!

Post by Quasi
Wszystko gra świetnie :) - zastanawiam się jeszcze czy tym sposobem
dałoby się uaktywnić funkcje: Licz.Jeżeli i Suma.Jeżeli.
=LICZ.JEŻELI(WIDOCZNE($B$2:$B$10);"Iverson")
zwraca mi błąd #ARG!

Funkcja WIDOCZNE zwraca tablicę wartości a nie wszystkie funkcje Excela to
akceptują. Niektóre, jak LICZ.JEŻELI, wymagają podania zakresu komórek. Ale
w tym przypadku można sobie poradzić za pomocą formuły tablicowej,
wykorzystującej funkcję SUMY.POŚREDNIE:

=SUMA(SUMY.POŚREDNIE(3;ADR.POŚR("B"&WIERSZ(2:10)))*(B2:B10="Iverson"))

Powyższa formuła zastąpi LICZ.JEŻELI. Aby zastąpić funkcję SUMA.JEŻELI,
nalezy dodać jeszcze obszar zlicznych wartości (np: C2:10):

=SUMA(SUMY.POŚREDNIE(3;ADR.POŚR("B"&WIERSZ(2:10)))*(B2:B10="Iverson")*C2:C10)

Tajan

PS. Dla przypomnienia :-) Formuły tablicowe zatwierdzamy Enterem, trzymając
wciśnięte klawisze Ctrl+Shift.

[Quasi]

Dziękuję za formuły :). Mam jeszcze jedną sprawę dotyczącą funkcji
CZĘSTOŚĆ. Przypuśćmy, że w kolumnie A mamy te punkty zdobyte przez
dwóch badanych przez nas koszykarzy. Przy pomocy ww. funkcji możemy
ustalić ile razy Ci zawodnicy przekroczyli np. liczbę 50 punktów
(niech to będzie najwyższy przedział). Ale jak policzyć sumę
wszystkich punktów zawartych w tym przedziale? (załóżmy, że zawodnicy
Ci trzykrotnie przekroczyli tą granicę - zdobyli odpowiednio - 54; 57
i 63 punkty) Funkcja CZĘSTOŚĆ zwróci nam wartość 3 a jak zrobić żeby w
komórce wyskoczyło 174? :)

[Tajan]

Witam!

Post by Quasi
Dziękuję za formuły :). Mam jeszcze jedną sprawę dotyczącą funkcji
CZĘSTOŚĆ. Przypuśćmy, że w kolumnie A mamy te punkty zdobyte przez
dwóch badanych przez nas koszykarzy. Przy pomocy ww. funkcji możemy
ustalić ile razy Ci zawodnicy przekroczyli np. liczbę 50 punktów
(niech to będzie najwyższy przedział). Ale jak policzyć sumę
wszystkich punktów zawartych w tym przedziale? (załóżmy, że zawodnicy
Ci trzykrotnie przekroczyli tą granicę - zdobyli odpowiednio - 54; 57
i 63 punkty) Funkcja CZĘSTOŚĆ zwróci nam wartość 3 a jak zrobić żeby w
komórce wyskoczyło 174? :)

No ale, dlaczego to ma byc funkcja CZĘSTOŚĆ, skoro to typowe zadanie dla
SUMA.JEŻELI?

Tajan

[Quasi]

Też myślałem o tej formule tylko nie wiem jak wstawić koniunkcje
warunków np. >20 i <=30. Chodzi oczywiście o formułę tablicową :).

[pxd74]

Post by Quasi
Też myślałem o tej formule tylko nie wiem jak wstawić koniunkcje
warunków np. >20 i <=30. Chodzi oczywiście o formułę tablicową :).

Nie wnikałem w poprzednie posty, ale koniunkcje sumy.jeżeli dla jednej
kolumny dla wyżej wymienionych warunków robi się w ten sposób:

=SUMA.JEŻELI(A1:A20;">20")-SUMA.JEŻELI(A1:A20;">30")

[Tajan]

Witam!

Post by Quasi
Też myślałem o tej formule tylko nie wiem jak wstawić koniunkcje
warunków np. >20 i <=30. Chodzi oczywiście o formułę tablicową :).

Obliczenie przedziału z jednym warunkiem, to nie problem:
=SUMA.JEŻELI(B2:B10;">20")-SUMA.JEŻELI(B2:B10;">31")
Sprawa się komplikuje, gdy trzeba użyć dodatkowego warunku. W takim
przypadku faktycznie konieczne jest użycie formuły tablicowej. Ale jest to
dość proste:
=SUMA((A2:A10="Coby Bryant")*(B2:B10>20)*(B2:B10<=30)*B2:B10)

Tajan

[Quasi]

pxd74 --> zgadza się tylko w tym przypadku chodzi o podsumowanie tylko
widocznych komórek (sfiltrowanych). Formuła oczywiście jest poprawna
ale nie będzie się zmieniać wartości dynamicznie.

Zobaczcie na poniższy rysunek :)

Loading Image...

Dane w kolumnie F będą się zmieniać dynamicznie według tego jakiego
zawodnika zaznaczę w kolumnie B ponieważ arkuszowi temu przypisałem
makro zaproponowane przez Tajana i formuła tablicowa w komórce F23
wygląda tak:

=CZĘSTOŚĆ(WIDOCZNE(A2:A10);E23:E27)

Komórka F29 to formuła =SUMY.POŚREDNIE(3;B2:B10) - jeżeli zaznaczam w
kolumnie B tylko Bryanta wówczas zmienia mi się rzecz jasna wartość
F29 z 9 na 5, zmieniają się dane w zakresie F23:F27, zmieniają się
procenty w kolumnie G (w G23 mam formułę =F23/$F$29) skopiowaną na dół
- i przed wszystkim zmienia mi się wykres co jest świetną opcją :).
Nie zmieniają mi się tylko dane w kolumnie H i tu tkwi szkopuł :)

[Tajan]

Quasi wrote:
(...)

Post by Quasi
Nie zmieniają mi się tylko dane w kolumnie H i tu tkwi szkopuł :)

W sumie wszystkie informacje do tego, aby się zmieniały - otrzymałeś :-)
Trzeba je tylko połączyć w całość.
Wpisz np. do komórki H23 formułę:
=SUMA(SUMY.POŚREDNIE(3;ADR.POŚR("B"&WIERSZ($2:$10)))*(A$2:A$10>=D23)*(A$2:A$10<=E23)*A$2:A$10)
zatwierdzając ją jako tablicową a następnie skopiuj ją w dół, do pozostałych
komórek.

Tajan

[Quasi]

<piwo> :)

[Quasi]

Tajan mam jeszcze jedno pytanie odnośnie tego makra. Otóż, jeżeli mam
wpisane wszystkie dane to medianę jest policzyć bardzo łatwo, ale
pojawia się problem gdy mam taką kolumnę, która jest cały czas
aktualizowana. Excel traktuje puste komórki w tej kolumnie jako 0 i
potem w wyniku zwraca mi medianę i kwartyle jako 0. Nie dałoby rady
tego obejść tak żeby Excel całkowicie ignorował te puste komórki z
kolumny B? (tak żeby formuła =MEDIANA(WIDOCZNE(B2:B1000)) uwzględniała
tylko wartości liczbowe?)

Pozdrawiam :)

[Quasi]

I jeszcze podobna sprawa z tym, że dotycząca wykresów. Chciałbym
zrobić wykres liniowy dla całej kolumny (jest tam wpisana formuła) ale
niestety Excel tratuje mi dane w pustych komórkach jako 0. Wiem, ze
można utworzyć listę ale byłoby to zbyt kłopotliwe bo musiałbym
utworzyć ich aż 5 w jednym arkuszu. Czy dałoby radę zrobić tak żeby
Excel uwzględniał mi przy wykresie tylko komórki z wartościami
liczbowymi, ewentualnie traktował dalsze pola jako puste (nie jako
zera)?

[Tajan]

Witam!

Post by Quasi
I jeszcze podobna sprawa z tym, że dotycząca wykresów. Chciałbym
zrobić wykres liniowy dla całej kolumny (jest tam wpisana formuła) ale
niestety Excel tratuje mi dane w pustych komórkach jako 0. Wiem, ze
można utworzyć listę ale byłoby to zbyt kłopotliwe bo musiałbym
utworzyć ich aż 5 w jednym arkuszu. Czy dałoby radę zrobić tak żeby
Excel uwzględniał mi przy wykresie tylko komórki z wartościami
liczbowymi, ewentualnie traktował dalsze pola jako puste (nie jako
zera)?

Aby Excel zignorował puste komórki, to powinien sie pojawić w nich kod błędu
"brak danych" ("#N/D!"). W związku z tym musisz tak zmienic formułę, aby
zamiast pustych wartości zwracała ten bład (przy pomocy funkcji BRAK()),
ewentualnie wykres oprzeć na obszarze pomocniczym, do którego bedziesz pobierał
dane z właściwego obszaru, uzywając np. funkcji: =JEŻELI(A1="";BRAK();A1)

Tajan

[Quasi]

Coś sie dalej nie zgadza :). Po skopiowaniu nowego makra wyskakuje mi
błąd NAZWA. Poniżej znajduje się plik ze starym makrem :)

http://www.sendspace.com/file/q78mgz

Teraz tak:

Chodzi o arkusz DANE. W kolumnie C (począwszy od C7) będzie wpisywany
ręcznie zysk z danego tygodnia. Wszystkich tygodni jest 52 więc
obszar, który mnie interesuje tutaj to C7:C58, który cały czas będzie
aktualizowany (zgodnie z założeniami zadania raz na tydzień chociaż w
innych okolicznościach może być nawet aktualizowany co godzinę). Teraz
chciałbym znaleźć medianę dla danych zawartych w tym obszarze ale
tylko dla tych wartości, które są tam wpisane przeze mnie. Mediana
cały czas wychodzi mi 0 - tak samo kwartyle, dlatego, że właśnie
komórki puste, których jest najwięcej, traktowane są jako zero :)
Oczywiście w grę wchodzi filtrowanie i taka formuła =
MEDIANA(WIDOCZNE(C7:C58))

Co do wykresu - chciałbym go zrobić dla danych z kolumny D lub E,
najlepiej tak żeby uwzględniane były tylko liczby, które sa wynikiem
formuły, bez komórek pustych, ewentualnie mogą być zawarte komórki
puste ale żeby Excel nie przypisywał im wartości 0 bo wtedy wykres
liniowy (o taki mi chodzi :))) nagle się załamuje i linia wykresu
spada na oś x :)

Pozdrawiam i proszę o rady :)

[Tajan]

Witam!

Post by Quasi
Coś sie dalej nie zgadza :). Po skopiowaniu nowego makra wyskakuje mi
błąd NAZWA. Poniżej znajduje się plik ze starym makrem :)
http://www.sendspace.com/file/q78mgz
Chodzi o arkusz DANE. W kolumnie C (począwszy od C7) będzie wpisywany
ręcznie zysk z danego tygodnia. Wszystkich tygodni jest 52 więc
obszar, który mnie interesuje tutaj to C7:C58, który cały czas będzie
aktualizowany (zgodnie z założeniami zadania raz na tydzień chociaż w
innych okolicznościach może być nawet aktualizowany co godzinę). Teraz
chciałbym znaleźć medianę dla danych zawartych w tym obszarze ale
tylko dla tych wartości, które są tam wpisane przeze mnie. Mediana
cały czas wychodzi mi 0 - tak samo kwartyle, dlatego, że właśnie
komórki puste, których jest najwięcej, traktowane są jako zero :)
Oczywiście w grę wchodzi filtrowanie i taka formuła =
MEDIANA(WIDOCZNE(C7:C58))
Co do wykresu - chciałbym go zrobić dla danych z kolumny D lub E,
najlepiej tak żeby uwzględniane były tylko liczby, które sa wynikiem
formuły, bez komórek pustych, ewentualnie mogą być zawarte komórki
puste ale żeby Excel nie przypisywał im wartości 0 bo wtedy wykres
liniowy (o taki mi chodzi :))) nagle się załamuje i linia wykresu
spada na oś x :)

Błąd nazwy otrzymujesz, bowiem, nie zwróciłeś uwagi, że nowa funkcja nazywa
się Widoczne1 i nie dostosowałeś do tego formuł :-) Oczywiście, możesz
również zmienić jej nazwę, ale w tym przypadku, nie zapomnij o zmianie nazwy
również w kodzie funkcji, w instrukcji przypisywania jej wartości.
Co do problemów z poprzednią funkcją, to w tej chwili nie za bardzo mama
czas aby sprawdzać przyczynę, dlaczego dla pustych komórek jest zwracana
wartość 0, ale mam nadzieję, że nowa funkcja będzie działała prawidłowo.

Co do wykresu to, jak już Ci pisałem, jako źródło danych wykresu zastosuj
tabelę pomocniczą z funkcją BRAK, do której dane pobierzesz formułą:
=JEŻELI(Dane!C7="";BRAK();Dane!D7)
Jeżeli zamiast liniowego, wybierzesz wykres XY, to będziesz mógł również
dynamicznie dostosowywać oś X, pobierając numery tygoni za pomocą formuły:
=JEŻELI(Dane!C7="";BRAK();Dane!B7)

Tajan

[Quasi]

Dzięki Wielkie <brawo> <brawo> <brawo>

Wszystko działa świetnie. Z ta medianą to chciałem odpuścić i policzyć
ja w zwykły sposób ale jednak wersja z makro to super opcja :).
Wykresy też teraz działają super - właśnie o taki efekt mi chodziło :)

[Quasi]

Zadam jeszcze jedno pytanie z ciekawości dotyczące wykresów. Załóżmy,
że oś Y to minimum 0 a maksimum 100. Załóżmy, że dane mieszczą się w
przedziale 20-30. Wczoraj jakoś przypadkiem udało mi się zrobić tak
żeby Excel pokazywał na wykresie automatycznie tylko ten obszar z
danymi czyli od 20 do 30 - w ten sposób zdecydowanie wyraźniej mogłem
zobaczyć różnice. Czy jest jakaś opcja, która automatycznie
dostosowałaby wykres tak żeby najmniejsza wartość była na samym dole a
największa na samej górze? (oczywiście można zmienić min i max ale nie
o to mi chodzi :))).

[Quasi]

Ok - poprzednie pytanie już nieaktualne :)

[Tajan]

Witam!

Post by Quasi
Tajan mam jeszcze jedno pytanie odnośnie tego makra. Otóż, jeżeli mam
wpisane wszystkie dane to medianę jest policzyć bardzo łatwo, ale
pojawia się problem gdy mam taką kolumnę, która jest cały czas
aktualizowana. Excel traktuje puste komórki w tej kolumnie jako 0 i
potem w wyniku zwraca mi medianę i kwartyle jako 0. Nie dałoby rady
tego obejść tak żeby Excel całkowicie ignorował te puste komórki z
kolumny B? (tak żeby formuła =MEDIANA(WIDOCZNE(B2:B1000)) uwzględniała
tylko wartości liczbowe?)

Tak, prawdę mówiąc, to trudno jest mi cokolwiek napisac nie widząc arkusza. Nie
wiem z jakiego powodu tak a nie inaczej sa interpretowane komórki. Jeżeli
komórka jest całkowicie pusta, to nie powinna byc traktowana jako 0. Również,
tablica wartości zwracana przez funkcję WIDOCZNE, wszędzie gdzie są komórki
puste zwraca wartość Empty, czyli pustą a nie 0. Zrobiłem kilka eksperymentów z
funkcja MEDIANA i zawsze puste komórki były przez nią ignorowane. Zreszą, w
helpie do niej można przeczytać:

"Jeśli argument tablicowy lub odwołaniowy zawiera tekst, wartości logiczne lub
puste komórki, to wartości takie są ignorowane."

W związku z tym, wydaje mi się, że te komórki chyba nie są puste.
Wypróbuj jednak taką funkcję:

Function Widoczne1(zakr As Range)
Dim kom As Range
Dim tempArr() As Variant
Dim ileW As Long

For Each kom In zakr.Cells
If Not kom.EntireRow.Hidden Then
If kom.Value <> "" Then
ileW = ileW + 1
ReDim Preserve tempArr(1 To ileW)
tempArr(ileW) = kom.Value
End If
End If
Next

Widoczne1 = tempArr

End Function

która całkowicie pomija puste komórki.

Tajan