Temat: średnia z części komórek

Witam serdecznie!

Mam pewien problem.

Mam arkusz w Excelu, w którym w kolumnach mam odpowiedzi respondentów z badania, a w wierszach przyznane oceny, które wartościowały daną cechę od 0 do 10. Do tego pod ocenami mam metryczkę, która ujmuje respondentów czy są z miasta czy wsi.

Jak obliczyć jaka jest średnia dla respondentów oddzielnie z miasta i oddzielnie ze wsi?
To samo tyczy się odchylenia standardowego.

Ktoś może mi pomóc?

plik poglądowy: http://www.parr.com.pl/pub/pogladowy_plik.xlsx

Temat: średnia z części komórek

Do obliczenia średniej z części komórek można użyć formuły do obliczania średniej warunkowej. Jednocześnie żeby sobie uprościć życie, trochę przekształciłam wygląd zbioru danych.

Czyli po kolei:

1) Kopiuję dane z arkusza 1 i wklejam całość do Arkusza2 używając polecenia Transpozycja (jest pod klawiszem Wklej) - w ten sposób zamieniam miejscami wiersze i kolumny
2) Usuwam zbędne wiersze dzięki czemu dostaję "normalnie" wyglądający zbiór danych. Teraz w kolumnach od A do O mam kolejno nazwy zmiennych: odpowiedź, złość, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, wieś, miasto, a w wierszach poszczególne odpowiedzi respondentów (nr od 1 do 300)
3) Zaznaczam mój zbiór danych i wybieram z menu Wstawianie polecenie Tabela (zaznaczam opcję, że tabela ma nagłówki) - dzięki temu mój zbiór staje się tabelą o nazwie Tabela1, a zmienne są nagłówkami tej tabeli.
4) Teraz pozostaje użycie funkcji średniej warunków:
dla wsi ma ona następującą postać:
=ŚREDNIA.WARUNKÓW(Tabela1[złość];Tabela1[Wieś];1)
dla miasta ma ona następującą postać:
=ŚREDNIA.WARUNKÓW(Tabela1[złość];Tabela1[Miasto];1)

gdzie:
Tabela1[złość] - kolumna z której będą brane dane do liczenia średniej
Tabela1[Wieś];1 - oznacza, że używane będą dane tylko dla odpowiedzi (wierszy) spełniających warunek, że w kolumnie Wieś jest wpisana liczba 1.

Jeśli chodzi o odchylenie standardowe to postanowiłam je policzyć wg wzoru na odchylenie standardowe, czyli jako pierwiastek z sumy kwadratów odchyleń od średniej, tj. po kolei:

1) w nowej kolumnie w wierszu nr 2 wpisuję formułę na policzenie sobie kwadratu "warunkowego" odchylenia od średniej warunkowej dla każdej odpowiedzi. Formuła ma postać:
=JEŻELI([Wieś]=1;(liczba-Tabela2[[#Ten wiersz];[złość]])^2;0)
gdzie
[Wieś]=1 - oznacza: "jeśli w kolumnie Wieś jest 1"
(liczba-Tabela1[[#Ten wiersz];[złość]])^2 - oznacza "policz kwadrat odchylenia od średniej komórki Tabela1[[#Ten wiersz];[złość]] (czyli komórki B2)"
liczba - tu trzeba wpisać średnią warunkową, jaka wyszła dla mieszkańców wsi
0 - oznacza "a jeśli nie to wpisz 0"

Ponieważ pracuję w tabeli to wiersze uzupełnią się same.

2) Analogicznie w kolejnej kolumnie tworzę formułę dla mieszkańców miasta ("wieś" zastępuję "miasto" i wpisuję średnią warunkową dla mieszkańców miasta)

Zamiast wpisywać liczbę z palca można oczywiście dać odwołanie do odpowiedniej komórki (koniecznie ze znakiem dolara) - tak pewnie nawet będzie lepiej.

3) Teraz podsumowuję obie kolumny - mam policzoną wariancję

4) Przy pomocy funkcji pierwiastek wyciągam odchylenie standardowe.

Mam nadzieję, że niczego po drodze nie pokręciłam ;)

Edit: mała zmiana w jednej formule.Agnieszka O. edytował(a) ten post dnia 19.02.13 o godzinie 09:19

Temat: średnia z części komórek

Witam!

Agnieszko bardzo dziękuję za pomoc! Co do średniej wszystko jest super i działa bez problemu! Mam trochę wątpliwości co do odchylenia.

Otrzymałem następujące wyniki:
średnia wieś: 0,147
średnia miasto: 0,227
odchylenie wieś: 4,865 odchylenie miasto: 10,009

Czy Tobie też tak wyszło?

Temat: średnia z części komórek

Odchylenia standardowego dla miasta już nie liczyłam, ale pozostałe wyniki się zgadzają z moimi.

Natomiast po czasie nabrałam pewnych wątpliwości co do średniej warunkowej. Bo jak policzyłam ją sama według wzoru (łatwo to zrobić - wystarczy posortować kolumnę Wieś lub Miasto), to wyniki wyszły mi delikatnie inne (bardzo niewielka różnica paru setnych, ale jednak). I nie umiem tego wyjaśnić, hmm. Nie wiem, skąd ta różnica. :(
Jestem ciekawa, czy u Ciebie Piotrze, też tak wychodzi?

Temat: średnia z części komórek

Średnia warunkowa wydaje się ok. Bo liczyłem też tym wzorem ogólną średnia i wychodzi identyczna jak średnia arytmetyczna liczona z samej bazy danych odpowiedzi. Więc to jest ok.

Natomiast ja mam sporo wątpliwości co do odchylenia standardowego. Dla miasta wychodzi 10,009 podczas gdy oceny są następujące:
Wieś : 89,0% = 0
Miasto: 86,7 = 0
a w przedziale ocen: 5-10, tylko 1 osoba udzieliła odpowiedzi 6, a na pozostałe oceny nie padła ani jedna odpowiedź.

Średnia ogólna wychodzi 0,197.

Skąd zatem tak wielkie odchylenia standardowe? Gdzieś jest błąd.

Licząc odchylenie standardowe za pomocą formuła Excela z kolumny D [złość] wychodzi: 0,654 i to jest prawdopodobne. Ale nie wiem jak przełożyć to na miasto i wieś.

Temat: średnia z części komórek

Już wiem o co chodzi. Moje przeoczenie - wariancja to średnia kwadratów odchyleń od średniej, a według podanej przeze mnie formuły obliczona zostaje suma kwadratów odchyleń od średniej. A zatem trzeba tę sumę podzielić przez liczbę obserwacji, a dopiero później pierwiastkować.

Alternatywnie można to policzyć z właściwej formuły Excela - co będzie proste jeśli posortuje się wyniki rosnąco lub malejąco w kolumnie Miasto lub Wieś (wtedy łatwo będzie ograniczyć zakres danych do formuły).

Temat: średnia z części komórek

Lepiej liczyć formułą Excela przez posortowanie danych.

Bo zauważyłem, że pomimo braku odpowiedzi przez respondenta to formuła =JEŻELI([Wieś]=1;(liczba-Tabela2[[#Ten wiersz];[złość]])^2;0)
wyrzuca wartość np.: 23,992.

Dziękuję serdecznie za pomoc!