Programma Excel ir padarījusi vairāku šūnu vidējās vērtības aprēķināšanu par ļoti vienkāršu uzdevumu — vienkārši izmantojiet funkciju AVERAGE (VIDĒJĀ). Bet ko darīt, ja dažām vērtībām ir lielāks svars nekā citām? Piemēram, daudzos kursos kontroldarbiem ir lielāka nozīme nekā uzdevumiem. Šādiem gadījumiem ir jāaprēķina vidējais svērtais.
Programmā Excel nav funkcijas vidējā svērtā lieluma aprēķināšanai, taču ir funkcija, kas lielāko daļu darba paveiks jūsu vietā: SUMPRODUCT (SUMMAS PRODUKTS). Un pat ja jūs nekad iepriekš neesat izmantojis šo funkciju, šī raksta beigās jūs to izmantosit kā profesionālis. Mūsu izmantotā metode darbojas jebkurā Excel versijā, kā arī citās izklājlapās, piemēram, Google izklājlapās.
Gatavojam galdu
Ja plānojat aprēķināt vidējo svērto vērtību, jums būs nepieciešamas vismaz divas kolonnas. Pirmajā kolonnā (mūsu piemērā B kolonna) ir norādīti katra uzdevuma vai pārbaudes rezultāti. Otrajā kolonnā (C ailē) ir norādīti svari. Lielāks svars nozīmē lielāku uzdevuma vai testa ietekmi uz gala atzīmi.
Lai saprastu, kas ir svars, varat to uzskatīt par procentuālo daļu no galīgās atzīmes. Faktiski tas tā nav, jo šajā gadījumā svaru summai vajadzētu būt līdz 100%. Formula, kuru mēs analizēsim šajā nodarbībā, visu aprēķinās pareizi un nav atkarīga no svara summas.
Mēs ievadām formulu
Tagad, kad mūsu tabula ir gatava, mēs pievienojam formulu šūnai B10 (derēs jebkura tukša šūna). Tāpat kā ar jebkuru citu Excel formulu, mēs sākam ar vienādības zīmi (=).
Mūsu formulas pirmā daļa ir funkcija SUMPRODUCT (SUMMAS PRODUKTS). Argumenti jāliek iekavās, tāpēc mēs tos atveram:
=СУММПРОИЗВ(
=SUMPRODUCT(
Pēc tam pievienojiet funkcijas argumentus. SUMPRODUCT (SUMPRODUCT) var būt vairāki argumenti, bet parasti tiek izmantoti divi. Mūsu piemērā pirmais arguments būs šūnu diapazons. B2:B9A, kas satur punktu skaitu.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Otrais arguments būs šūnu diapazons C2:C9, kurā ir ietverti svari. Šie argumenti ir jāatdala ar semikolu (komatu). Kad viss ir gatavs, aizveriet iekavas:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Tagad pievienosim mūsu formulas otro daļu, kas sadalīs aprēķināto rezultātu ar funkciju SUMPRODUCT (SUMPRODUCT) pēc svaru summas. Kāpēc tas ir svarīgi, mēs apspriedīsim vēlāk.
Lai veiktu dalīšanas darbību, turpinām jau ievadīto formulu ar simbolu / (taisna slīpsvītra) un pēc tam ierakstiet funkciju SUM (SUM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Funkcijai SUM (SUM) norādīsim tikai vienu argumentu – šūnu diapazonu C2:C9. Pēc argumenta ievadīšanas neaizmirstiet aizvērt iekavas:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Gatavs! Pēc taustiņa nospiešanas ienākt, Excel aprēķinās vidējo svērto. Mūsu piemērā gala rezultāts būs 83,6.
Kā tas darbojas
Sadalīsim katru formulas daļu, sākot ar funkciju SUMPRODUCT (SUMPRODUCT), lai saprastu, kā tas darbojas. Funkcija SUMPRODUCT (SUMPRODUCT) aprēķina katras preces rezultāta un svara reizinājumu un pēc tam summē visus iegūtos produktus. Citiem vārdiem sakot, funkcija atrod produktu summu, līdz ar to nosaukumu. Tātad priekš Uzdevumi 1 reiziniet 85 ar 5 un par Pārbaude reiziniet 83 ar 25.
Ja jums rodas jautājums, kāpēc mums ir jāreizina vērtības pirmajā daļā, iedomājieties, jo lielāks ir uzdevuma svars, jo vairāk reižu mums ir jāņem vērā tā vērtējums. Piemēram, Uzdevums 2 skaitītas 5 reizes un Gala eksāmens - 45 reizes. Tāpēc Gala eksāmens ir lielāka ietekme uz gala atzīmi.
Salīdzinājumam, aprēķinot parasto vidējo aritmētisko, katra vērtība tiek ņemta vērā tikai vienu reizi, tas ir, visām vērtībām ir vienāds svars.
Ja varētu paskatīties zem kādas funkcijas pārsega SUMPRODUCT (SUMPRODUCT), mēs redzējām, ka patiesībā viņa uzskata:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Par laimi mums nav jāraksta tik gara formula, jo SUMPRODUCT (SUMPRODUCT) to visu dara automātiski.
Funkcija pati par sevi SUMPRODUCT (SUMPRODUCT) atgriež mums milzīgu skaitu − 10450. Šajā brīdī tiek izmantota formulas otrā daļa: /SUM(C2:C9) or /SUM(C2:C9), kas atgriež rezultātu normālā punktu diapazonā, sniedzot atbildi 83,6.
Formulas otrā daļa ir ļoti svarīga, jo ļauj automātiski labot aprēķinus. Atcerieties, ka svariem nav jāsaskaita 100%? Tas viss, pateicoties formulas otrajai daļai. Piemēram, ja mēs palielinām vienu vai vairākas svara vērtības, formulas otrā daļa vienkārši tiks dalīta ar lielāko vērtību, kā rezultātā atkal tiks iegūta pareizā atbilde. Vai arī mēs varam padarīt svarus daudz mazākus, piemēram, norādot tādas vērtības kā 0,5, 2,5, 3 or 4,5, un formula joprojām darbosies pareizi. Tas ir lieliski, vai ne?