Jak obliczyć średnią ważoną w programie Excel za pomocą procentów

Obliczanie średniej ważonej w programie Excel za pomocą wartości procentowych umożliwia radzenie sobie z danymi o różnym stopniu ważności.

Średnia ważona jest podobna do średniej standardowej, ale podkreśla określone wartości na podstawie przypisanych im wag. Kiedy różne wagi są wyrażone w procentach, wyniki są często bardziej znaczące.

W tym artykule dowiesz się, jak obliczyć średnią ważoną przy użyciu procentów w Excelu. Omówimy niezbędne formuły, funkcje i wskazówki programu Excel, które musisz znać.

Wejdźmy w to.

Spis treści

• Co to jest średnia ważona?
  • Korzystanie z procentów lub jednostek
• Konfigurowanie arkusza programu Excel
  • Formatowanie komórek dla wartości procentowych
• Jak obliczyć średnią ważoną w programie Excel
  • Korzystanie z funkcji Excel SUMPRODUCT
  • Dzielenie przez całkowitą wagę
  • Instrukcje dla Excela
  • Rozbicie formuły
• Scenariusze ze świata rzeczywistego
  • Systemy oceniania
  • Analiza finansowa
• 4 Typowe błędy i rozwiązywanie problemów
  • 1. Błędne dane i wagi
  • 2. Nie uwzględnianie procentów
  • 3. Nieprawidłowe odwołania do komórek
  • 4. Używanie niewłaściwej funkcji
• Nasze ostatnie zdanie

Co to jest średnia ważona?

Średnie ważone i procenty to podstawowe pojęcia w wielu dziedzinach, w tym w finansach, statystyce i analizie danych.

Średnia ważona to rodzaj średniej, która uwzględnia względne znaczenie zestawu wartości.

Porównajmy to z prostą średnią, której prawdopodobnie używałeś wiele razy. Prosta średnia arytmetyczna traktuje wszystkie wartości jednakowo podczas obliczania wyniku.

W przypadku średniej ważonej nie wszystkie wartości mają jednakową wagę. Wagi są przypisywane wartościom zgodnie z ich ważnością lub przydatnością.

Z matematycznego punktu widzenia średnią ważoną oblicza się w następujący sposób:

1. mnożąc każdą wartość przez odpowiadającą jej wagę
2. sumując produkty, aby uzyskać sumę
3. dzieląc sumę przez sumę wag

Formułę matematyczną można przedstawić jako:

Średnia ważona = (?(wartość × waga)) / ?(waga)

Możesz napisać formułę matematyczną podczas pracy z makrami i VBA.

Jednak w tym samouczku wykorzystano kilka przydatnych funkcji programu Microsoft Excel , aby uprościć obliczenia. Przekonasz się, że są to łatwe w użyciu skróty.

Korzystanie z procentów lub jednostek

Niektóre obliczenia ważone wykorzystują wartości procentowe, a inne jednostki.

Na przykład, jeśli uśredniasz ceny w sklepie, będziesz używać jednostek monetarnych reprezentujących cenę przedmiotu.

Jeśli ołówek kosztuje pięćdziesiąt centów, a długopis dolara, to wagi wynoszą odpowiednio 0,5 i 1,0.

Oceny z college'u częściej opierają się na procentach. Na przykład za egzamin śródsemestralny można przypisać 40% oceny ogólnej, a za egzamin końcowy 60%.

Ten artykuł koncentruje się na obliczaniu i wyświetlaniu średnich przy użyciu wartości procentowych.

Konfigurowanie arkusza programu Excel

Zanim obliczysz średnią ważoną w Excelu, musisz skonfigurować arkusz z niezbędnymi danymi. Wykonaj następujące kroki:

1. Wprowadź wartości danych w jednej kolumnie (np. w kolumnie B).
2. Przypisz wagi w procentach w oddzielnej kolumnie (np. Kolumna C).
3. Sprawdź, czy wagi sumują się do 100%.

Ten samouczek rozpoczyna się od przykładu ze świata sportu. Trenerzy oceniają swoich zawodników według różnych atrybutów, a niektóre atrybuty są bardziej wartościowe niż inne.

Trener decyduje o procentowej wadze dla każdego atrybutu, a następnie punktuje zawodnika. Średnia ważona daje pełniejszy obraz tego, jak ocenia się zawodnik.

Skopiuj tę tabelę do szablonu programu Excel:

Powyższe wagi wyrażone są w procentach, a ich suma wynosi 100%.

Formatowanie komórek dla wartości procentowych

Dla przejrzystości konieczne jest sformatowanie komórek zawierających wartości wagowe jako wartości procentowe. Oto zadania krok po kroku:

1. Wybierz komórki wagowe, które mają być sformatowane (np. Kolumna B).
2. Kliknij kartę „Strona główna” na pasku narzędzi programu Excel.
3. Znajdź grupę „Liczba” i kliknij przycisk „Styl procentowy” (%). Spowoduje to sformatowanie wybranych komórek jako wartości procentowych i wyświetlenie ich ze znakiem %.

Po wykonaniu tych kroków arkusz programu Excel powinien być teraz skonfigurowany i gotowy do obliczenia średniej ważonej. Upewnij się, że dane i wartości wagi zostały wprowadzone poprawnie, a komórki zawierające wagi są sformatowane jako wartości procentowe w celu zwiększenia czytelności.

Jak obliczyć średnią ważoną w programie Excel

Obliczenie średniej ważonej obejmuje szereg prostych kroków z wykorzystaniem funkcji SUMA ILOCZYNÓW i SUMA.

Funkcje te można łączyć, aby znaleźć ostateczną średnią wartość, która uwzględnia różne znaczenie lub wagi przypisane różnym wartościom.

Korzystanie z funkcji Excel SUMPRODUCT

Formuła programu Excel SUMPRODUCT służy do mnożenia dwóch lub więcej tablic wartości i zwracania sumy ich iloczynów.

Tablica odnosi się do listy wartości. Zakres komórek zawierających ilości to jedna tablica. Tablica zawierająca cenę jest drugą tablicą.

Dzielenie przez całkowitą wagę

Po użyciu funkcji SUMA ILOCZYNÓW do pomnożenia i zsumowania ilości towarów i cen należy podzielić to przez sumę wag, aby obliczyć średnią ważoną.

Załóżmy, że masa całkowita reprezentuje sumę ilości w kolumnie B.

Korzystając z funkcji SUMA, takiej jak ta: SUMA(B2:B4), otrzymasz całkowitą wagę.

Ta liczba służy do dzielenia na wynik wyniku SUMA ILOCZYNÓW.

Instrukcje dla Excela

Wykonaj następujące kroki w arkuszu programu Excel :

1. Kliknij na pustą komórkę, w której zostanie wyświetlona średnia ważona.
2. Wprowadź następującą formułę: =SUMA.ILOCZYN(B2:B4; C2:C4)/SUMA(B2:B4)
3. Naciśnij enter'.

Po wykonaniu tych kroków program Excel wyświetli średnią ważoną cenę w wybranej komórce.

Jeśli postępujesz zgodnie z tym przykładem, w komórce wynikowej może pojawić się długa liczba z wieloma miejscami po przecinku.

W tym momencie powinieneś uporządkować wyświetlacz, formatując komórkę wyniku tak, aby wyświetlała dwa miejsca po przecinku.

Rozbicie formuły

Oto podział formuły z poprzedniej sekcji:

• SUMPRODUCT(B2:B4, C2:C4) otrzymuje sumę iloczynów ilości pozycji pomnożonych przez ich ceny
• SUMA(B2:B4) oblicza całkowitą wagę (suma ilości pozycji)
• SUMPRODUCT(B2:B4, C2:C4)/SUMA(B2:B4) dzieli sumę produktów przez całkowitą wagę, dając średnią ważoną

Scenariusze ze świata rzeczywistego

Dwa typowe zastosowania tej formuły obejmują systemy ocen i analizę finansową.

Te przykłady pokazują, jak stosować średnie ważone w programie Excel przy użyciu procentów jako wag.

Systemy oceniania

Kiedy nauczyciele i profesorowie oceniają wyniki uczniów na kursie, często używają średnich ważonych do określenia końcowej oceny. W takim przypadku każde zadanie, quiz lub egzamin ma określoną wagę lub procent składający się na ocenę ogólną.

Załóżmy na przykład następujący podział ocen:

• Zadania: 40% (łącznie 4, każde warte 10%)
• Quizy: 20% (łącznie 2, każdy wart 10%)
• Egzamin śródsemestralny: 20%
• Egzamin końcowy: 20%

Idąc za przykładem, możesz skopiować dane z tej tabeli do swojego arkusza kalkulacyjnego. Umieść pierwszą kolumnę tabeli w kolumnie A.

Aby obliczyć średnią ważoną w programie Excel, możesz użyć funkcji SUMA ILOCZYNÓW.

Zakładając, że oceny znajdują się w kolumnie B (od B2 do B9), a odpowiadające im wagi wyrażone w procentach w kolumnie C (od C2 do C9), wzór będzie wyglądał następująco:

=SUMA.ILOCZYN(B2:B9; C2:C9)/SUMA(C2:C9)

Ta formuła programu Excel oblicza średnią ważoną przy użyciu podanych ocen i wartości procentowych.

Analiza finansowa

Średnie ważone odgrywają również istotną rolę w analizie finansowej, zwłaszcza przy ocenie wyników portfela akcji . W takich przypadkach waga przypisana do każdego stada może opierać się na procentowej przydzielonej inwestycji.

Załóżmy, że w swoim portfelu posiadasz następujące akcje:

Zakładając, że ceny akcji znajdują się w kolumnie B (od B2 do B4), akcje w kolumnie C (od C2 do C4), a procent inwestycji w kolumnie D (od D2 do D4), wzór na średnią ważoną będzie następujący:

=SUMA.ILOCZYN(B2:B4; D2:D4)/SUMA(D2:D4)

Powyższy wzór oblicza średnią ważoną cenę akcji na podstawie procentu inwestycji dla każdej akcji. Ta wartość pomaga w ocenie ogólnej wydajności portfela.

Ten zrzut ekranu pokazuje obliczenia i wyniki:

Możesz jeszcze bardziej ulepszyć swoje prezentacje, oznaczając kolorami wartości, które znajdują się powyżej lub poniżej średniej ważonej. Na przykład wyższe wartości mogą być podświetlone na zielono.

Aby to zrobić, użyj formatowania warunkowego, aby dodać regułę „wyróżnij komórkę” z warunkiem „większe niż”. Pozwala to określić próg i wybrać kolor podświetlenia.

Analiza cen akcji często łączy moc średnich ważonych z częstotliwością zamykania cen w określonych przedziałach cenowych. Analitycy przypisują wagi do różnych przedziałów cenowych i używają programu Excel do tworzenia tabeli częstości, która zlicza przypadki, gdy cena akcji spadła do przedziału cenowego w ciągu trzydziestu dni.

Pozwala to na obliczenie średnich ważonych dziennych kursów zamknięcia w oparciu o częstotliwość. Ten pomocny film pokazuje, jak tworzyć tabele częstotliwości w programie Excel:

4 Typowe błędy i rozwiązywanie problemów

Oto cztery najczęstsze błędy, które można napotkać podczas obliczania średniej ważonej w programie Excel.

1. Błędne dane i wagi

Błędne dane i wagi mogą również prowadzić do niedokładnych średnich ważonych wyników. Dokładność zależy od prawidłowego wyrównania danych i wag w Excelu.

Dane należy wymienić w sąsiednich kolumnach, przy czym każda wartość odpowiada odpowiedniej wadze:

Jeśli zauważysz, że dane i wagi są źle wyrównane, dostosuj kolumny w arkuszu kalkulacyjnym, aby zapewnić prawidłowe dopasowanie wartości i odpowiadających im wag.

2. Nie uwzględnianie procentów

Gdy traktujesz procenty jako wagi, upewnij się, że wagi są wyrażone jako wartości dziesiętne. Na przykład, jeśli waga jest reprezentowana jako 20%, odpowiednik dziesiętny powinien wynosić 0,20.

Aby zamienić procent na ułamek dziesiętny, podziel procent przez 100:

=C2 / 100

Przy prawidłowym uwzględnieniu wartości procentowych obliczenie średniej ważonej da dokładne wyniki.

3. Nieprawidłowe odwołania do komórek

Należy upewnić się, że odwołania do komórek są dokładne. Nieprawidłowe odwołania do komórek mogą prowadzić do błędów lub niedokładnych wyników w obliczeniach.

Sprawdź, czy formuły zawierają odpowiednie zakresy komórek zarówno dla wartości, jak i wag.

4. Używanie niewłaściwej funkcji

Prawdopodobnie wiele razy korzystałeś z normalnej funkcji średniej programu Excel. Ta funkcja nie jest używana w przypadku średnich ważonych.

Odchylenie standardowe i średnia ruchoma to sposoby wychwytywania zmian w średnich danych. Obliczenia te wykorzystują różne formuły.

Funkcja ŚREDNIA.JEŻELI umożliwia odfiltrowanie wartości, które nie spełniają określonych kryteriów. Jednak lepiej jest przypisać wartościom niski procent podczas obliczania średniej ważonej.

Nasze ostatnie zdanie

W tym artykule zademonstrowaliśmy proces obliczania średnich ważonych w Excelu za pomocą procentów. Średnie ważone zapewniają cenny wgląd w zestawy danych, podkreślając względne znaczenie wielu wartości.

Za pomocą funkcji SUMPRODUCT program Excel może łatwo obliczyć średnią ważoną, mnożąc odpowiednie punkty danych i dzieląc wynik przez całkowitą sumę wag. Formuła jest następująca:

=SUMA.ILOCZYN(zakres_danych; zakres_wag) / SUMA(zakres_wag)

Ta metoda jest szczególnie przydatna w przypadku ocen, wyników ankiet lub danych finansowych, gdzie niektóre wartości mają większe znaczenie niż inne.

Opanowanie tej techniki może zwiększyć możliwości analizy danych, umożliwiając użytkownikom podejmowanie bardziej świadomych decyzji w oparciu o dokładne i odpowiednie dane.

Gotowy, aby zacząć budować silne umiejętności obsługi programu Excel? Sprawdź nasz