Vispārēja formula
=SUMPRODUCT(weights,values)/SUM(weights)
Kopsavilkums
Lai aprēķinātu vidējo svērto vērtību, varat izmantot funkciju SUMPRODUCT kopā ar funkciju SUM. Parādītajā piemērā G5 formula, kas nokopēta uz leju, ir:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
kur svars ir nosauktais diapazons I5: K5.
Paskaidrojums
Vidējais svērtais, saukts arī par vidējo svērto, ir vidējais rādītājs, kurā dažas vērtības skaitās vairāk nekā citas. Citiem vārdiem sakot, dažām vērtībām ir lielāks "svars". Mēs varam aprēķināt vidējo svērto vērtību, reizinot vērtības ar vidējo ar atbilstošajiem svariem, pēc tam rezultātu summu dalot ar svaru summu. Programmā Excel to var attēlot ar tālāk norādīto vispārīgo formulu, kur svars un vērtības ir šūnu diapazoni:
=SUMPRODUCT(weights,values)/SUM(weights)
Parādītajā darblapā 3 testu rezultāti parādās kolonnās C līdz E, un svari ir nosauktajā diapazona svarā (I5: K5). G5 šūnas formula ir:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Strādājot no iekšpuses uz āru, vispirms izmantojam funkciju SUMPRODUCT, lai reizinātu svaru ar atbilstošajiem rādītājiem un summētu rezultātu:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT vispirms reizina abu masīvu atbilstošos elementus kopā, pēc tam atgriež produkta summu:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Rezultāts tiek dalīts ar svaru summu:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Tā kā formula tiek kopēta uz leju G kolonnā, nosauktie diapazona svari I5: K5 nemainās, jo tā darbojas kā absolūta atsauce. Tomēr rādītāji C5: E5, kas ievadīti kā relatīva atsauce, tiek atjaunināti katrā jaunajā rindā. Rezultāts ir katra saraksta nosaukuma vidējā svērtā vērtība, kā parādīts. F kolonnas vidējo vērtību aprēķina tikai kā atsauci ar funkciju AVERAGE:
=AVERAGE(C5:E5)
Svari, kas nesasniedz 1
Šajā piemērā svari ir konfigurēti, lai tie būtu līdz 1, tāpēc dalītājs vienmēr ir 1, un rezultāts ir SUMPRODUCT atgrieztā vērtība. Tomēr jauka formulas iezīme ir tā, ka svariem nav jāpievieno 1.
Piemēram, pirmajos divos testos mēs varētu izmantot svaru 1 un finālā 2 (jo fināls ir divreiz svarīgāks), un vidējais svērtais lielums būs vienāds:
Šūnā G5 formula tiek atrisināta šādi:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Piezīme. Iepriekš minētās cirtainās breketes () vērtības ir diapazonos, kas izteikti kā masīvi.
Svaru transponēšana
Funkcijai SUMPRODUCT ir nepieciešams, lai masīva izmēri būtu saderīgi. Ja izmēri nav saderīgi, SUMPRODUCT parādīs kļūdu #VALUE. Tālāk sniegtajā piemērā svari ir tādi paši kā sākotnējā piemērā, taču tie ir norādīti vertikālā diapazonā:
Lai aprēķinātu vidējo svērto vērtību ar to pašu formulu, mums svari ir "jāpārvērš" horizontālā masīvā ar funkciju TRANSPOSE šādi:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Pēc TRANSPOSE palaišanas vertikālais masīvs:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
kļūst:
=(0.25,0.25,0.5) // horizontal array
No šī brīža formula darbojas tāpat kā iepriekš.
Lasīt vairāk: vertikālie un horizontālie bloki.
