Aprēķinot vienkāršu noteiktas vērtību kopas vidējo, tiek pieņemts, ka visām vērtībām ir vienāds svars vai nozīme.
Piemēram, ja jūs ieradāties eksāmenos un visiem eksāmeniem ir līdzīgs svars, tad jūsu kopējo atzīmju vidējais rādītājs būs arī jūsu rezultātu vidējais svērtais.
Tomēr reālajā dzīvē tas diez vai notiek.
Daži uzdevumi vienmēr ir svarīgāki par citiem. Daži eksāmeni ir svarīgāki par citiem.
Un tieši tur Svērtais vidējais nonāk attēlā.
Šeit ir mācību grāmatas vidējā svērtā definīcija:
Tagad redzēsim, kā Excel aprēķināt vidējo svērto vērtību.
Vidējā svērtā aprēķināšana programmā Excel
Šajā apmācībā jūs uzzināsit, kā programmā Excel aprēķināt vidējo svērto.
- Funkcijas SUMPRODUCT izmantošana.
- Funkcijas SUM izmantošana.
Tātad sāksim darbu.
Svērtā vidējā aprēķināšana programmā Excel - funkcija SUMPRODUCT
Var būt dažādi scenāriji, kuros jāaprēķina vidējais svērtais. Tālāk ir norādītas trīs dažādas situācijas, kurās varat izmantot funkciju SUMPRODUCT, lai Excel aprēķinātu vidējo svērto vērtību
Tālāk ir norādītas trīs dažādas situācijas, kurās varat izmantot funkciju SUMPRODUCT, lai Excel aprēķinātu vidējo svērto vērtību
1. piemērs. Kad svars palielinās līdz 100%
Pieņemsim, ka jums ir datu kopa ar atzīmēm, ko students ieguvis dažādos eksāmenos, kā arī svaru procentos (kā parādīts zemāk):
Iepriekš minētajos datos students iegūst atzīmes dažādos vērtējumos, bet galu galā viņam ir jāpiešķir gala rezultāts vai atzīme. Šeit nevar aprēķināt vienkāršu vidējo rādītāju, jo dažādu novērtējumu nozīme ir atšķirīga.
Piemēram, viktorīna, kuras svars ir 10%, ir divreiz lielāka nekā uzdevums, bet ceturtā daļa no svara, salīdzinot ar eksāmenu.
Šādā gadījumā varat izmantot funkciju SUMPRODUCT, lai iegūtu vidējo svērto punktu skaitu.
Šeit ir formula, kas parādīs vidējo svērto vērtību programmā Excel:
= SUMPRODUCT (B2: B8, C2: C8)
Lūk, kā darbojas šī formula: Excel SUMPRODUCT funkcija reizina pirmā masīva pirmo elementu ar otrā masīva pirmo elementu. Tad tas reizina pirmā masīva otro elementu ar otrā masīva otro elementu. Un tā tālāk…
Un visbeidzot, tas pievieno visas šīs vērtības.
Šeit ir ilustrācija, lai tas būtu skaidrs.
2. piemērs - ja svars nepievienojas līdz 100%
Iepriekš minētajā gadījumā svari tika piešķirti tā, ka kopējais apjoms sasniedza 100%. Bet reālās dzīves scenārijos tas ne vienmēr var būt.
Apskatīsim vienu un to pašu piemēru ar dažādiem svariem.
Iepriekš minētajā gadījumā svars veido līdz 200%.
Ja es izmantoju to pašu SUMPRODUCT formulu, tas man sniegs nepareizu rezultātu.
Iepriekš minētajā rezultātā esmu dubultojis visus svarus, un tā atgriež vidējo svērto vērtību kā 153,2. Tagad mēs zinām, ka students nevar iegūt vairāk par 100 no 100, lai arī cik viņš/viņa būtu izcils.
Iemesls tam ir tas, ka svari nesasniedz līdz 100%.
Šeit ir formula, kas to sakārtos:
= SUMPRODUCT (B2: B8, C2: C8)/SUM (C2: C8)
Iepriekš minētajā formulā SUMPRODUCT rezultāts tiek dalīts ar visu svaru summu. Tādējādi, neatkarīgi no tā, svari vienmēr sasniegs 100%.
3. piemērs - kad jāaprēķina svars
Līdz šim apskatītajā piemērā tika norādīti svari. Tomēr var būt gadījumi, kad svari nav tieši pieejami, un vispirms ir jāaprēķina svari un pēc tam jāaprēķina vidējais svērtais.
Pieņemsim, ka jūs pārdodat trīs dažādu veidu produktus, kā minēts tālāk:
Jūs varat aprēķināt vidējo svērto produkta cenu, izmantojot funkciju SUMPRODUCT. Šeit ir formula, kuru varat izmantot:
= SUMPRODUCT (B2: B4, C2: C4)/SUM (B2: B4)
SUMPRODUCT rezultātu dalot ar daudzumu SUM, tiek nodrošināts, ka svari (šajā gadījumā daudzumi) ir līdz 100%.
Svērtā vidējā aprēķināšana programmā Excel - SUM funkcija
Lai gan funkcija SUMPRODUCT ir labākais veids, kā programmā Excel aprēķināt vidējo svērto vērtību, varat izmantot arī funkciju SUM.
Lai aprēķinātu vidējo svērto vērtību, izmantojot funkciju SUM, jums jāreizina katrs elements ar procentuālo nozīmi.
Izmantojot to pašu datu kopu:
Šeit ir formula, kas sniegs pareizo rezultātu:
= SUM (B2*C2, B3*C3, B4*C4, B5*C5, B6*C6, B7*C7, B8*C8)
Šo metodi var izmantot, ja jums ir pāris priekšmeti. Bet, ja jums ir daudz priekšmetu un svaru, šī metode varētu būt apgrūtinoša un pakļauta kļūdām. Ir īsāks un labāks veids, kā to izdarīt, izmantojot funkciju SUM.
Turpinot to pašu datu kopu, šeit ir īsa formula, kas sniegs jums vidējo svērto vērtību, izmantojot funkciju SUM:
= SUM (B2: B8*C2: C8)
Šīs formulas izmantošanas triks ir izmantot taustiņu kombināciju Control + Shift + Enter, nevis tikai ievadīšanas taustiņu. Tā kā funkcija SUM nevar apstrādāt masīvus, jums jāizmanto Control + Shift + Enter.
Nospiežot taustiņu kombināciju Control + Shift + Enter, formulas sākumā un beigās tiek parādītas cirtainās iekavas (skatiet formulas joslu iepriekš redzamajā attēlā).
Vēlreiz pārliecinieties, ka svars ir līdz 100%. Ja tā nav, rezultāts jāsadala ar svaru summu (kā parādīts zemāk, ņemot vērā produkta piemēru):
