Średnia ważona w Excelu

Najpierw wyjaśnimy sobie czym jest średnia ważona, a potem policzymy ją na różnych przykładach już konkretnie w Excelu.

SPIS TREŚCI

Czym jest średnia ważona

Jak się liczy zwykłą średnią, to dla większości osób jest jasne. Dodajemy do siebie pewną ilość wartości, a potem dzielimy to przez ilość wartości. W takim przypadku, każda wartość jest tak samo ważna – jeśli policzymy średnią dla liczb 1 oraz 99, to wyniesie ona 50, bo:

(1+99)/2 = 50

Zanim zaczniemy działać w Excelu, przenieśmy obliczenia do jakiejś historii biznesowej. Wyobraź sobie, że jest pracownik:

  • który zajmuje się dwoma projektami (nie jest ważne w tym momencie na czym jego praca polega)
  • każdy z projektów jest tak samo duży i ważny
  • pracownik jest on oceniany przez swojego przełożonego (aby zdecydować, jaką premię pracownik otrzyma na koniec roku) w skali od 0 do 100
  • pracownik otrzymuje za jeden z projektów 1 punkt, a za drugi z projektów 99 punktów.
  • Jego średnia ocena wynosi więc 50 punktów (bo w sumie dostał 100 punktów za dwa projekty, więc na jeden projekt przypada 50 punktów).

Rysunek 1. Przykład obliczania zwykłej średniej (historia 1).

Myślę, że powyższa historia jest dość przejrzysta. Obrazuje ona liczenie zwykłej średniej (każdy projekt był tak samo ważny). Poniżej historia, gdzie projekty mają różną wagę (niedługo policzymy taką średnią ważoną w Excelu):

    • pracownik zajmuje się dwoma projektami
    • Pierwszy z projektów jest kluczowy dla firmy i zajmuje większość czasu pracownikowi (ok. 90% czasu). Drugi projekt jest poboczny (zajmuje ok. 10% czasu).
    • pracownik jest on oceniany przez swojego przełożonego (aby zdecydować, jaką premię pracownik otrzyma na koniec roku) w skali od 0 do 100
    • pracownik otrzymuje za kluczowy projekt 99 punktów, a za poboczny projekt 1 punkt.
    • Czy przełożony powinien ocenić pracownika na 50 punktów, tak jak we historii pierwszej? Raczej nie, bo najważniejsze jest to, jak spisał się w kluczowym projekcie.

Rysunek 2. Przykład obliczania średniej ważonej (historia 2).

Widzisz teraz, że liczby mogą mieć czasami różną wagę, co wpływa na ostateczny wynik (ostateczną średnią, nazywaną wówczas średnią ważoną).

Jak policzyć średnią ważoną z powyższego przykładu, pokazuję w następnej sekcji.

Przykład obliczania średniej ważonej

Jeśli chcemy obliczyć średnią ważoną z drugiej historii (z powyższej sekcji), to trzeba określić wagi dla poszczególnych liczb:

    • 99 (punktów) – ma wagę 0,9 (bo 90% czasu pracownik poświęcał temu projektowi)
    • 1 (punkt) ma wagę 0,1 (bo 10% czasu pracownik poświęcał temu projektowi)

Matematyczne działanie będzie wyglądać tak:

99*0,9 + 1*0,1 = 89,1 + 0,1 = 89,2

Ostateczny wynik tego pracownika wyniesie 89,2 punktu (na 100 możliwych punktów). Jeślibyśmy liczyli zwykłą średnią, to jego wynik wyniósłby 50 punktów. Widać, że różnica jest ogromna.

No dobrze, ale skąd wzięły się liczby 0,9 oraz 0,1? W ten sposób wskazujemy, jak duży udział w ostatecznym wyniku (ostatecznej średniej) mają mieć poszczególne liczby. Są to wagi tych liczb. Powinny one sumować się do 1. Można je przedstawić także jako procenty, sumujące się do 100%, czyli powyższe równanie mogłoby wyglądać także tak:

99*90% + 1*10% = 89,1 + 0,1 = 89,2

Wagi wszystkich elementów zazwyczaj sumują się do 1, czyli do 100%. W związku z tym, jeśli ten pracownik dbałby o 3 projekty, to wtedy suma wagi tych trzech projektów powinna dać 100%. Przykładowo:

    • Projekt 1 – 85 punktów – ma wagę 50%
    • Projekt 2 – 90 punktów ma wagę 30%
    • Projekt 3 – 48 punktów ma wagę 20%

Widzisz, że po zsumowaniu wszystkich wag, daję one 100% (50% + 30% + 20%). Obliczenia wyglądałyby tak:

85*50% + 90*30% + 48*20% = 42,5 + 27 + 9,6 = 79,1

Rysunek 3. Średnia ważona z trzech projektów. Suma wag wynosi 100%.

Przykład średniej ważonej w Excelu

Przeniesiemy teraz powyższe przykłady do arkusza Excela. Przeanalizujmy do jeden po drugim.

Zwykła średnia w Excelu

Policzmy przykład ze zwykłą średnią, aby móc go potem porównać ze średnią ważoną.

Rysunek 4. Wynik obliczeń zwykłej średniej w Excelu.

Widać, że formuła naśladuje obliczenia, które przedstawiłem we wcześniejszej sekcji. Liczba punktów pierwszego projektu (B2) jest mnożona przez wagę tego projektu (jako, że liczymy zwykłą średnią, to każdy projekt jest tak samo ważny – czyli ma wagę 50%), czyli komórkę B3. Dodajemy do tego liczbę punktów drugiego projektu (C2), przemnożoną przez jego wagę (C3). Daje to w sumie 50 punktów.

Rysunek 5. Formuła obliczająca zwykłą średnią w Excelu.

Średnia ważona w Excelu dla dwóch projektów

Działamy podobnie jak do tej pory, czyli tworzymy w Excelu formułę, która mnoży liczbę punktów przez wagę projektu. Czyli 1 punkt przemnożony przez 10% i 99 punktów przemnożone przez 90%. Wynik to 89,2.

Rysunek 6. Formuła w arkuszu Excela, która oblicza średnią ważoną.

Średnia ważona w Excelu dla trzech projektów

Sposób działania jest analogiczny jak wcześniej. Formuła Excela mnoży tym razem przez siebie 3 pary wartości (punkty mnoży przez wagę). Jeśli projektów byłoby więcej, to należałoby w Excelu dodać więcej kolumn i zawsze mnożyć punkty przez wagę.

Rysunek 7. Formuła Excelowa, obliczająca średnią ważoną dla 3 projektów.

Pamiętaj jednak, że suma wag powinna równać się 100%.

Średnia ważona w Excelu, przy ocenie przetargów

Bardzo często średnią ważoną stosuje się przy ocenianiu oferta składanych do przetargu. Bardzo łatwo taką średnią ważoną jest policzyć właśnie w Excelu. Poniżej omawiam przykład oceny przetargu, gdzie sposób działania będzie opierał o to wszystko, o czym pisałem powyżej.

Pamiętaj, że jest to jedynie przykład oceny przetargu. Każdy może ocenia oferty przetargowe według własnego klucza, a ja pokazuje ten przykład, żeby pokazać w praktyce jak można używać w Excelu średniej ważonej.

Kryteria ocen zapisane w Excelu

Wymyślmy kilka kryteriów, które będą brane pod uwagę, podczas oceniania składanych w przetargu ofert:

    • Cena – oczywiście jest istotna
    • Doświadczenie – jak wiele lat firma, składająca ofertę, działa na rynku
    • Jakość – jakość usług oferowanych przez firmę, która będzie sprawdzana za pomocą referencji/poleceń, składanych przez firmę.

Możesz poszczególne nazwy kryteriów zapisać w kolejnych kolumnach Excela. Oczywiście może być ich dowolnie dużo, w moim przykładzie jest ich trzy.

Rysunek 8. Zapisanie kryteriów dla średniej ważonej.

Ustalenie skali ocen kryteriów

Jeśli chcemy wziąć pod uwagę te 3 kryteria, to nie możemy porównywać zwyczajnie ich wartości. Bo np. cena może wynieś 15.000 (podana w zł), a doświadczenie 5 (podane w latach), a jakość 10 (podane w ilości listów referencyjnych). Widzimy, że jednostki tych kryteriów są zupełnie różne i nijak nie można ceny w liczbie 15.000 porównać bezpośrednio z doświadczeniem w liczbie 5. Należy ustanowić skale dla tych kryteriów.

Zastanówmy się, jakie wartości poszczególnych kryteriów są realne (np. czy jest możliwe jest, żeby firma miała 1000 lat doświadczenia na rynku) i jakie wartości (spośród tych realnych) są dla nas istotne (bo czy ma dla nas realne znaczenie to że jakaś firma ma doświadczenia 15 lat, a nie 16? Chyba nie). Oto moja propozycja skali, gdzie dla każdego kryterium maksymalna ilość punktów to 100:

    • Cena:
      • 10.000 zł i mniej – 100 punktów.
      • Każdy 1.000 zł więcej, to 10 punktów mniej, czyli 10.001 do 11.000 – 90 punktów, 11.001 do 12.000 – 80 punktów…19.001 do 20.000 – 10 punktów
      • Powyżej 20.000 zł – 0 punktów. Jest to wtedy na tyle droga oferta, że jest dla mnie zdecydowanie za droga i już nie ma to znaczenia, czy kosztuje 21.000, czy 40.000 – jest po prostu poza moim budżetem.
    • Doświadczenie:
      • za każdy 1 rok na rynku oferent otrzymuje 10 punktów.
      • W przypadku doświadczenia powyżej 10 lat, oferent dostaje zawsze maksymalną ilość punktów, czyli 100. Nie ma dla mnie znaczenia czy ma 10, czy 20 lat doświadczenia, bo jest to dla mnie czas wystarczająco długi, aby stać się specjalistą.
    • Jakość:
      • za każdy list refencyjny/list polecający oferent otrzymuje 5 punktów.
      • W przypadku 20 i więcej listów oferent otrzymuje maksymalną liczbę punktów, czyli 100.

Nie będziemy powyższych założeń wpisywać do formuł Excela, żeby sobie niepotrzebnie nie komplikować życia, ale można traktować to jako instrukcję zapisaną w jakimś pliku tekstowym (do którego można zajrzeć w razie potrzeby).

Ustalenie wagi kryteriów i zapisanie ich w Excelu

Powiedziałem, że za każde kryteriów można dostać od 0 do 100 punktów. Należy teraz im przypisać odpowiednią wagę (można by od razu na poprzednim etapie zróżnicować maksymalną liczbę punktów, określając w ten sposób wagi, ale tego nie zrobiłem, że ułatwić zrozumienie sposobu obliczania średniej ważonej w Excelu). Powiedzmy, że:

    • Cena ma wagę 60%.
    • Doświadczenie ma wagę 10%.
    • Jakość ma wagę 30%.

Zapiszmy to w arkuszu Excela.

Rysunek 9. Wagi poszczególnych kryteriów zapisane w komórkach Excela.

Wpisanie do Excela punktów za kryteria

Teraz należy wpisać do komórek Excela liczbę punktów, które poszczególni oferenci otrzymali za każde z kryteriów. Tutaj zakładam, że w myślach przeliczam ilość punktów – np widzę, że ktoś ma 5 lat doświadczenia, to nie wpisuje do Excela 5, tylko od razu 50 (punktów). Oczywiście można by stworzyć dodatkowe formuły, które z tych 5 lat same wyliczą 50 punktów, ale w takim przykładzie jak nasz, uważam to za zbędne.

Rysunek 10. Wpisani oferenci i liczba punktów za kryteria.

Stworzenie formuły Excelowej, która policzy średnią ważoną

Formułę tworzymy podobnie jak wcześniej, czyli wartość wagi mnożymy przez ilość punktów. Zwróć jedna uwagę na to, że będziemy tę formułę przeciągać do kolejnych komórek Excela (do kolejnych oferentów). W związku z tym, trzeba zablokować dolarami adresy komórek, w których są wagi, czyli B3, C3, D3. Widać to na rysunku poniżej.

Rysunek 11. Formuła w Excelu, która oblicza średnią ważoną.

Gdy formuła zostanie przeciągnięta do kolejnych oferentów, to cały czas przemnaża te sami wagi, ale tym razem przez inne wartości punktowe (odpowiednie dla kolejnych oferentów), co widać na rysunku poniżej:

Rysunek 12. Średnia ważona liczona dla oferenta nr 3.

Funkcja Excela do liczenia średniej ważonej

Obliczenia średniej ważonej możemy sobie w Excelu nieco ułatwić, korzystając z funkcji SUMA.ILOCZYNÓW.

Weźmy przykład z przetargiem. Mamy tam 3 kryteria, więc formuła nie jest bardzo długa ani skomplikowana, ale gdyby kryteriów było więcej, to stanie się ona odpowiednia dłuższa i bardziej skomplikowana.

Użyjmy w tym przykładzie funkcji SUMA.ILOCZYNÓW. Będzie ona miała tylko dwa argumenty, niezależnie od tego, jak dużo jest kryteriów:

    • Argument 1 – zaznaczenie wiersza z wagami. Jeśli kryteriów jest dużo, to po prostu zaznaczasz więcej komórek i tyle.
    • Argument 2 – zaznaczenie wiersza z punktami. Jeśli kryteriów jest dużo, to po prostu zaznaczasz więcej komórek i tyle.

Pierwszy argument musi być zablokowany dolarami.

Rysunek 13. Funkcja SUMA.ILOCZYNÓW do liczenia średniej ważonej.

Powiązane wpisy poradnikowe