Jak vypočítat vážený průměr v Excelu s procenty

Výpočet váženého průměru v Excelu s procenty vám dává možnost pracovat s daty, která mají různý stupeň důležitosti.

Vážený průměr je podobný standardnímu průměru, ale zdůrazňuje konkrétní hodnoty na základě jim přiřazených vah. Když jsou různé váhy vyjádřeny v procentech, výsledky jsou často smysluplnější.

V tomto článku se dozvíte, jak vypočítat vážený průměr pomocí procent v Excelu. Pokrýváme nezbytné vzorce, funkce a tipy pro Excel, které potřebujete znát.

Pojďme do toho.

Obsah

• Co je vážený průměr?
  • Pomocí procent nebo jednotek
• Nastavení vašeho excelového listu
  • Formátování buněk na procenta
• Jak vypočítat vážený průměr v Excelu
  • Použití funkce Excel SUMPRODUCT
  • Dělení celkovou hmotností
  • Návod pro Excel
  • Rozbití vzorce
• Scénáře skutečného světa
  • Systémy klasifikace
  • Finanční analýza
• 4 Běžné chyby a odstraňování problémů
  • 1. Nesprávně zarovnaná data a váhy
  • 2. Neúčtování v procentech
  • 3. Nesprávné odkazy na buňky
  • 4. Použití nesprávné funkce
• Naše závěrečné slovo

Co je vážený průměr?

Vážené průměry a procenta jsou základními pojmy v mnoha oblastech, včetně financí, statistiky a analýzy dat.

Vážený průměr je typ průměru, který zohledňuje relativní významnost souboru hodnot.

Porovnejme to s jednoduchým průměrem, který jste pravděpodobně použili mnohokrát. Jednoduchý aritmetický průměr zachází při výpočtu výsledku se všemi hodnotami stejně.

U váženého průměru nemají všechny hodnoty stejnou váhu. Váhy jsou přiřazeny hodnotám podle jejich důležitosti nebo relevance.

Z matematického hlediska se vážený průměr vypočítá jako:

1. vynásobením každé hodnoty její odpovídající váhou
2. sečtením produktů získáte součet
3. vydělením součtu součtem vah

Matematický vzorec může být reprezentován jako:

Vážený průměr = (?(hodnota × hmotnost)) / ?(hmotnost)

Matematický vzorec můžete napsat při práci s makry a VBA.

Tento tutoriál však používá několik užitečných funkcí pro zjednodušení výpočtu. Zjistíte, že jde o snadno použitelné zkratky.

Pomocí procent nebo jednotek

Některé vážené výpočty používají procenta a jiné jednotky.

Pokud například průměrujete ceny v obchodě, budete používat peněžní jednotky, které představují cenu položky.

Pokud je tužka padesát centů a pero dolar, pak jsou váhy 0,5 a 1,0.

Známky na vysoké škole budou spíše založeny na procentech. Například půlsemestrální zkoušce může být přiděleno 40 % z celkové známky, zatímco 60 % závěrečné zkoušce.

Tento článek se zaměřuje na výpočet a zobrazování průměrů pomocí procent.

Nastavení vašeho excelového listu

Než v Excelu vypočítáte vážený průměr, musíte si sestavit list s potřebnými údaji. Následuj tyto kroky:

1. Zadejte hodnoty dat do jednoho sloupce (např. sloupec B).
2. Váhy přiřaďte v procentech v samostatném sloupci (např. ve sloupci C).
3. Zkontrolujte, zda se součet hmotnosti rovná 100 %.

Tento tutoriál začíná příkladem ze světa sportu. Trenéři hodnotí své hráče podle různých atributů a některé atributy jsou cennější než jiné.

Trenér rozhodne o procentuální váze pro každý atribut a poté hráče boduje. Vážený průměr poskytuje úplnější obrázek o tom, jak hráč měří.

Zkopírujte tuto tabulku do své šablony Excel:

Výše uvedené váhy jsou uvedeny v procentech a jejich součet je 100 %.

Formátování buněk na procenta

Pro přehlednost je nezbytné formátovat buňky obsahující hodnoty hmotnosti v procentech. Zde jsou úkoly krok za krokem:

1. Vyberte buňky hmotnosti, které je třeba naformátovat (např. sloupec B).
2. Klikněte na kartu „Domů“ na panelu nástrojů Excel.
3. Vyhledejte skupinu „Číslo“ a klikněte na tlačítko „Styl procent“ (%). To zformátuje vybrané buňky jako procenta a zobrazí je se znakem %.

Po dokončení těchto kroků by měl být váš excelový list nyní nastaven a připraven k výpočtu váženého průměru. Ujistěte se, že jsou vaše data a hodnoty hmotnosti zadány správně a že buňky obsahující hmotnosti jsou pro lepší čitelnost formátovány jako procenta.

Jak vypočítat vážený průměr v Excelu

Výpočet váženého průměru zahrnuje řadu jednoduchých kroků pomocí funkcí SUMPRODUCT a SUM.

Tyto funkce lze zkombinovat za účelem nalezení konečné průměrné hodnoty, která zohledňuje různou důležitost nebo váhy dané různým hodnotám.

Použití funkce Excel SUMPRODUCT

Vzorec SUMPRODUCT aplikace Excel se používá k vynásobení dvou nebo více polí hodnot a vrácení součtu jejich součinů.

Pole odkazuje na seznam hodnot. Rozsah buněk, které obsahují množství, je jedno pole. Pole, které obsahuje cenu, je druhé pole.

Dělení celkovou hmotností

Po použití funkce SOUČETNÝ PRODUKT k vynásobení a sečtení množství a cen položky je musíte vydělit součtem vah, abyste vypočítali vážený průměr.

Předpokládejme, že celková hmotnost představuje součet množství ve sloupci B.

Použitím funkce SUM, jako je tato: SUM(B2:B4), získáte celkovou hmotnost.

Tento údaj se používá k rozdělení na výsledek výsledku SUMPRODUCT.

Návod pro Excel

V pracovním listu postupujte takto:

1. Klikněte na prázdnou buňku, kde se zobrazí vážený průměr.
2. Zadejte následující vzorec: =SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4)
3. Stiskněte Enter'.

Po dokončení těchto kroků Excel zobrazí vážený průměr ceny ve vybrané buňce.

Pokud postupujete podle tohoto příkladu, můžete ve výsledné buňce vidět dlouhé číslo s mnoha desetinnými místy.

V tomto okamžiku byste měli udělat pořádek na displeji tak, že zformátujete výslednou buňku tak, aby zobrazovala dvě desetinná místa.

Rozbití vzorce

Zde je rozpis vzorce z předchozí části:

• SUMPRODUCT(B2:B4, C2:C4) získá součet produktů množství položek vynásobený jejich příslušnými cenami
• SUM(B2:B4) vypočítá celkovou hmotnost (součet množství položek)
• SUMPRODUCT(B2:B4, C2:C4)/SUMA(B2:B4) vydělí součet produktů celkovou hmotností a poskytne vážený průměr

Scénáře skutečného světa

Dvě běžné aplikace tohoto vzorce zahrnují systémy hodnocení a finanční analýzu.

Tyto příklady ukazují, jak použít vážené průměry při použití procent jako vah.

Systémy klasifikace

Když učitelé a profesoři hodnotí výkon studentů v kurzu, často používají k určení konečné známky vážené průměry. V tomto případě má každý úkol, kvíz nebo zkouška určitou váhu nebo procento přispívající k celkové známce.

Předpokládejme například následující rozdělení tříd:

• Úkoly: 40 % (celkem 4, každý má hodnotu 10 %)
• Kvízy: 20 % (celkem 2, každý v hodnotě 10 %)
• Půlsemestrální zkouška: 20 %
• Závěrečná zkouška: 20 %

Chcete-li následovat příklad, můžete zkopírovat data z této tabulky do tabulky. Vložte první sloupec v tabulce do sloupce A.

Pro výpočet váženého průměru v Excelu můžete použít funkci SUMPRODUCT.

Za předpokladu, že stupně jsou ve sloupci B (od B2 do B9) a odpovídající váhy vyjádřené v procentech jsou ve sloupci C (od C2 do C9), vzorec bude vypadat takto:

=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)

Tento vzorec Excel vypočítá vážený průměr pomocí poskytnutých stupňů a procent.

Finanční analýza

Vážené průměry také hrají zásadní roli ve finanční analýze, zejména při hodnocení výkonnosti akciového portfolia . V takových případech může být váha přidělená každé akcii založena na procentu přidělené investice.

Předpokládejme, že ve svém portfoliu vlastníte následující akcie:

Za předpokladu, že ceny akcií jsou ve sloupci B (od B2 do B4), akcie ve sloupci C (od C2 do C4) a procenta investic ve sloupci D (od D2 do D4), vzorec váženého průměru bude následující:

=SUMPRODUCT(B2:B4, D2:D4)/SUM(D2:D4)

Výše uvedený vzorec vypočítává váženou průměrnou cenu akcií na základě procenta investice pro každou akcii. Tato hodnota pomáhá při hodnocení celkové výkonnosti portfolia.

Tento snímek obrazovky ukazuje výpočet a výsledky:

Své prezentace můžete ještě vylepšit barevným kódováním hodnot, které spadají nad nebo pod vážený průměr. Vyšší hodnoty mohou být například zvýrazněny zeleně.

Chcete-li to provést, použijte podmíněné formátování a přidejte pravidlo „zvýraznění buňky“ s podmínkou „větší než“. To vám umožní určit práh a vybrat barvu zvýraznění.

Analýza cen akcií často kombinuje sílu vážených průměrů s frekvencí uzavíracích cen v určitých cenových rozpětích. Analytici přiřazují váhy různým cenovým rozsahům a používají Excel k vytvoření frekvenční tabulky, která počítá časy, kdy cena akcií klesla do cenového rozsahu za třicet dní.

To umožňuje výpočet vážených průměrných denních závěrečných cen na základě frekvence. Toto užitečné video vám ukáže, jak vytvořit frekvenční tabulky v Excelu:

4 Běžné chyby a odstraňování problémů

Zde jsou čtyři nejčastější chyby, se kterými se při výpočtu váženého průměru v Excelu setkáte.

1. Nesprávně zarovnaná data a váhy

Nesprávně zarovnaná data a váhy mohou také vést k nepřesným výsledkům váženého průměru. Přesnost je podmíněna správným zarovnáním dat a vah v Excelu.

Údaje by měly být uvedeny v sousedních sloupcích, přičemž každá hodnota odpovídá příslušné váze:

Pokud si všimnete, že data a váhy nejsou zarovnané, upravte sloupce v tabulce, abyste zajistili správnou shodu mezi hodnotami a jejich odpovídajícími váhami.

2. Neúčtování v procentech

Při práci s procenty jako s váhami se ujistěte, že jsou váhy vyjádřeny jako desetinné hodnoty. Pokud je například váha reprezentována jako 20 %, desetinný ekvivalent by měl být 0,20.

Chcete-li převést procento na desetinné číslo, vydělte procento 100:

= C2/100

Správným započtením procent poskytne výpočet váženého průměru přesné výsledky.

3. Nesprávné odkazy na buňky

Je důležité zajistit, aby odkazy na buňky byly přesné. Nesprávné odkazy na buňky mohou vést k chybám nebo nepřesným výsledkům ve výpočtu.

Zkontrolujte, zda vzorce zahrnují příslušné rozsahy buněk pro hodnoty i hmotnosti.

4. Použití nesprávné funkce

Pravděpodobně jste mnohokrát použili normální průměrnou funkci Excelu. Tato funkce se nepoužívá pro vážené průměry.

Standardní odchylka a klouzavý průměr jsou způsoby, jak zachytit změny v průměrech dat. Tyto výpočty používají různé vzorce.

Funkce AVERAGEIF umožňuje odfiltrovat hodnoty, které nesplňují konkrétní kritéria. Při výpočtu váženého průměru je však lepší přiřadit hodnotám nízké procento.

Naše závěrečné slovo

V tomto článku jsme demonstrovali postup výpočtu vážených průměrů v Excelu s procenty. Vážené průměry poskytují cenné informace o souborech dat a zdůrazňují relativní důležitost řady hodnot.

Pomocí funkce SUMPRODUCT může Excel snadno vypočítat vážený průměr vynásobením odpovídajících datových bodů a vydělením výsledku celkovým součtem vah. Vzorec je následující:

=SUMPRODUCT(rozsah_dat, rozsah_vah) / SUM(rozsah_vah)

Tato metoda je užitečná zejména při práci se známkami, výsledky průzkumu nebo finančními údaji, kde některé hodnoty mají větší význam než jiné.

Zvládnutí této techniky může zlepšit schopnosti analýzy dat a umožnit uživatelům činit informovanější rozhodnutí na základě přesných a relevantních dat.

Jste připraveni začít budovat silné dovednosti Excelu? Podívejte se na naše