Excel uczynił obliczanie średniej z wielu komórek bardzo łatwym zadaniem – wystarczy użyć funkcji ŚREDNI (PRZECIĘTNY). Ale co, jeśli niektóre wartości mają większą wagę niż inne? Na przykład na wielu kursach testy mają większą wagę niż zadania. W takich przypadkach konieczne jest obliczenie Średnia ważona.
Excel nie ma funkcji obliczania średniej ważonej, ale jest funkcja, która wykona większość pracy za Ciebie: SUMPRODUCT (SUM PRODUKTU). I nawet jeśli nigdy wcześniej nie korzystałeś z tej funkcji, pod koniec tego artykułu będziesz z niej korzystać jak profesjonalista. Metoda, której używamy, działa w dowolnej wersji programu Excel, a także w innych arkuszach kalkulacyjnych, takich jak Arkusze Google.
Przygotowujemy stół
Jeśli zamierzasz obliczyć średnią ważoną, będziesz potrzebować co najmniej dwóch kolumn. Pierwsza kolumna (w naszym przykładzie kolumna B) zawiera wyniki każdego zadania lub testu. Druga kolumna (kolumna C) zawiera wagi. Większa waga oznacza większy wpływ zadania lub testu na ocenę końcową.
Aby zrozumieć, jaka jest waga, możesz potraktować ją jako procent swojej końcowej oceny. W rzeczywistości tak nie jest, ponieważ w tym przypadku wagi powinny sumować się do 100%. Formuła, którą przeanalizujemy w tej lekcji, obliczy wszystko poprawnie i nie zależy od ilości, do której sumują się wagi.
Wprowadzamy formułę
Teraz, gdy nasza tabela jest gotowa, dodajemy formułę do komórki B10 (zrobi to każda pusta komórka). Podobnie jak w przypadku każdej innej formuły w programie Excel, zaczynamy od znaku równości (=).
Pierwszą częścią naszego wzoru jest funkcja SUMPRODUCT (SUM PRODUKTU). Argumenty muszą być ujęte w nawiasy, więc otwieramy je:
=СУММПРОИЗВ(
=SUMPRODUCT(
Następnie dodaj argumenty funkcji. SUMPRODUCT (SUMPRODUCT) może mieć wiele argumentów, ale zwykle używane są dwa. W naszym przykładzie pierwszym argumentem będzie zakres komórek. B2:B9A, który zawiera wyniki.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Drugim argumentem będzie zakres komórek C2:C9, który zawiera wagi. Te argumenty muszą być oddzielone średnikiem (przecinkiem). Gdy wszystko będzie gotowe, zamknij nawiasy:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Dodajmy teraz drugą część naszego wzoru, która podzieli wynik obliczony przez funkcję SUMPRODUCT (SUMPRODUCT) przez sumę wag. Omówimy później, dlaczego jest to ważne.
Aby wykonać operację dzielenia, kontynuujemy już wprowadzoną formułę z symbolem / (prosty ukośnik), a następnie wpisz funkcję SUMA (SUMA):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Dla funkcji SUMA (SUM) określimy tylko jeden argument – zakres komórek C2:C9. Nie zapomnij zamknąć nawiasów po wpisaniu argumentu:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Gotowy! Po naciśnięciu klawisza Wchodzę, Excel obliczy średnią ważoną. W naszym przykładzie ostatecznym wynikiem będzie 83,6.
Jak to działa?
Rozbijmy każdą część formuły, zaczynając od funkcji SUMPRODUCT (SUMPRODUCT), aby zrozumieć, jak to działa. Funkcjonować SUMPRODUCT (SUMPRODUCT) oblicza iloczyn wyniku każdego elementu i jego wagi, a następnie sumuje wszystkie otrzymane produkty. Innymi słowy, funkcja wyszukuje sumę produktów, stąd nazwa. Więc dla Zadania 1 pomnóż 85 przez 5 i przez Test pomnóż 83 przez 25.
Jeśli zastanawiasz się, dlaczego musimy pomnożyć wartości z pierwszej części, wyobraź sobie, że im większa waga zadania, tym więcej razy musimy się za niego oceniać. Na przykład, Zadanie 2 liczone 5 razy i Egzamin końcowy – 45 razy. Dlatego Egzamin końcowy ma większy wpływ na ocenę końcową.
Dla porównania, przy obliczaniu zwykłej średniej arytmetycznej każda wartość jest brana pod uwagę tylko raz, to znaczy wszystkie wartości mają równą wagę.
Gdybyś mógł zajrzeć pod maskę funkcji SUMPRODUCT (SUMPRODUCT), widzieliśmy, że w rzeczywistości wierzy w to:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Na szczęście nie musimy pisać tak długiej formuły, ponieważ SUMPRODUCT (SUMPRODUCT) robi to wszystko automatycznie.
Funkcja sama w sobie SUMPRODUCT (SUMPRODUCT) zwraca nam ogromną liczbę − 10450. W tym momencie w grę wchodzi druga część formuły: /SUMA(C2:C9) or /SUMA(C2:C9), co przywraca wynik do normalnego zakresu punktacji, podając odpowiedź 83,6.
Druga część wzoru jest bardzo ważna, ponieważ pozwala na automatyczne korygowanie obliczeń. Pamiętaj, że wagi nie muszą się sumować do 100%? Wszystko to dzięki drugiej części formuły. Na przykład, jeśli zwiększymy jedną lub więcej wartości wagi, druga część wzoru po prostu podzieli się przez większą wartość, co ponownie da poprawną odpowiedź. Lub możemy znacznie zmniejszyć wagi, na przykład określając wartości takie jak 0,5, 2,5, 3 or 4,5, a formuła nadal będzie działać poprawnie. To świetnie, prawda?