Istnieje w Excelu funkcja o nazwie ŚREDNIA, która liczy średnią arytmetyczną wszystkich liczb. Jednak czasami zachodzi potrzeba obliczenia średniej ważonej. Przykładowo w szkole nauczyciel wystawiając oceny na koniec roku przykładał większą wagę do ocen ze sprawdzianów w mniejszą do ocen z aktywności czy zadań domowych.
Rozpatrzmy następujący przykład:
Pobierz plik z przykładowymi danymi
Jak widzimy ocena ze sprawdzianu ma wagę 3, oceny z kartkówek mają wagę 2 a pozostałe oceny mają wagę 1. Aby policzyć średnią ważoną ocen należy pomnożyć wszystkie oceny przez odpowiadające im wagi a następnie zsumować owe iloczyny i podzielić je przez sumę wag. Czyli mamy taki oto wzór:
W naszym przypadku pierwsza osoba ma średnią 4,00 gdyż:
Mając już wzór na średnią ważoną możemy spróbować wpisać odpowiednią formułę w Excelu. Tutaj mała podpowiedź: istnieje bardzo wygodna funkcja o nazwie SUMA.ILOCZYNÓW, która jako argumenty przyjmuje dowolną liczbę zakresów (a dokładniej maksymalnie 253). W naszym przypadku mamy 2 zakresy danych: oceny (B2:F2) i ich wagi (B18:F18) . Funkcja ta mnoży ze sobą kolejne liczby z owych zakresów a nastepnie zwraca sumę obliczonych iloczynów. Pamiętajmy, że wagi musimy podać jako wartości bezwzględne (zadolarowane), gdyż będziemy kopiować formułę dla pozostałych wierszy. Nie zapomnijmy o podzieleniu sumy iloczynów przez sumę wag ocen. Finalnie nasza formuła będzie wyglądała nastepująco:
=SUMA.ILOCZYNÓW(B2:F2; $B$18:$F$18) / SUMA($B$18:$F$18)No a co z osobami, które nie mają kompletu ocen?
Powyższa formuła jest bardzo prosta, ale ma pewną wadę: otóż raczej nigdy się nie zdaża, że wszyscy uczniowie mają taką samą liczbę ocen (no chyba, że na studiach gdzie otrzymuje się ocenę na każdych laborkach a warunkiem zaliczenia zajęć jest komplet obecności). Wiadomo: jeden uczeń zostanie wybrany do odpowiedzi raz w ciągu semestru a ten co rozrabia może mieć 5 ocen z odpowiedzi. Jak jakiś uczeń zachoruje, to nie będzie pisał jakiejś niezapowiedzianej kartkówki itp.
Tutaj już sprawa się komplikuje. Otóż w przypadku braku oceny (pusta komórka) Excel traktuje daną komórkę jakby było tam zero. Następnie w mianowniku jest suma wag wszystkich możliwych do uzyskania ocen. I tutaj trzeba powiedzieć Excelowi, że jeśli dana komórka jest pusta, to w mianowniku dana waga nie powinna być sumowana. Pierwsza połowa formuły będzie taka sama (gdyż brak oceny to pusta komórka więc Excel ją traktuje jakby tam było wpisane zero, a cokolwiek razy zero daje zero, więc brak oceny nam nie zmienia licznika) a z kolei w mianowniku można skorzystać z funkcji SUMA.JEŻELI (sumujemy daną wagę tylko wtedy, gdy uczeń posiada ocenę). Nasza formuła może wyglądać następująco:
=SUMA.ILOCZYNÓW(B2:F2;$B$18:$F$18) / SUMA.JEŻELI(B2:F2; "<>"; $B$18:$F$18)Z kolei fani formuł tablicowych mogliby wykombinować taką oto formułę:
=SUMA(JEŻELI(B2:F2<>"";B2:F2*B$18:F$18;0) / SUMA(JEŻELI(B2:F2<>"";B$18:F$18;0)))(W przypadku formuł tablicowych pamiętaj, że taką formułę powinno się zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter).
