Excel зрабіў вылічэнне сярэдняга значэння некалькіх вочак вельмі лёгкай задачай - проста выкарыстоўвайце гэтую функцыю СЯРЭДНЯЯ (СЯРЭДНЯЯ). Але што, калі некаторыя каштоўнасці маюць большую вагу, чым іншыя? Напрыклад, у многіх курсах тэсты маюць большую вагу, чым заданні. Для такіх выпадкаў трэба разлічваць сярэднеўзважаны.
У Excel няма функцыі для разліку сярэднеўзважанага значэння, але ёсць функцыя, якая зробіць большую частку працы за вас: SUMPRODUCT (СУМА ПРАДЫТУТ). І нават калі вы ніколі раней не карысталіся гэтай функцыяй, да канца гэтага артыкула вы будзеце выкарыстоўваць яе як прафесіянал. Метад, які мы выкарыстоўваем, працуе ў любой версіі Excel, а таксама ў іншых электронных табліцах, такіх як Табліцы Google.
Рыхтуем стол
Калі вы збіраецеся вылічыць сярэднеўзважанае значэнне, вам спатрэбіцца як мінімум два слупка. Першы слупок (у нашым прыкладзе слупок B) змяшчае балы за кожнае заданне або тэст. Другі слупок (слупок C) змяшчае вагавыя паказчыкі. Большая вага азначае большы ўплыў задання або тэста на выніковую адзнаку.
Каб зразумець, што такое вага, вы можаце разглядаць яго як працэнт ад вашай канчатковай адзнакі. На самай справе гэта не так, бо ў гэтым выпадку вага павінна складаць 100%. Формула, якую мы прааналізуем у гэтым уроку, разлічыць усё правільна і не будзе залежаць ад сумы вагаў.
Ўводзім формулу
Цяпер, калі наша табліца гатовая, мы дадаем формулу ў вочка B10 (падыдзе любая пустая клетка). Як і ў любой іншай формуле ў Excel, мы пачынаем са знака роўнасці (=).
Першая частка нашай формулы - гэта функцыя SUMPRODUCT (СУМА ПРАДЫТУТ). Аргументы павінны быць заключаны ў дужкі, таму мы іх адкрываем:
=СУММПРОИЗВ(
=SUMPRODUCT(
Далей дадайце аргументы функцыі. SUMPRODUCT (SUMPRODUCT) можа мець некалькі аргументаў, але звычайна выкарыстоўваюцца два. У нашым прыкладзе першым аргументам будзе дыяпазон вочак. В2: В9A, які змяшчае балы.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Другім аргументам будзе дыяпазон вочак С2: С9, які змяшчае вагі. Гэтыя аргументы павінны быць падзеленыя кропкай з коскай (коскай). Калі ўсё гатова, закрываем дужкі:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Зараз дадамо другую частку нашай формулы, якая будзе дзяліць вынік, вылічаны функцыяй SUMPRODUCT (SUMPRODUCT) сумай вагаў. Мы абмяркуем пазней, чаму гэта важна.
Для выканання аперацыі дзялення працягваем сімвалам ўжо ўведзеную формулу / (прамая касая рыса), а затым напішыце функцыю SUM (СУМ):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Для функцыі SUM (SUM) укажам толькі адзін аргумент – дыяпазон вочак С2: С9. Не забудзьцеся зачыніць круглыя дужкі пасля ўводу аргумента:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
гатовы! Пасля націску клавішы Уводзіць, Excel разлічыць сярэднеўзважанае значэнне. У нашым прыкладзе канчатковы вынік будзе 83,6.
Як гэта працуе?
Разбяром кожную частку формулы, пачынаючы з функцыі SUMPRODUCT (SUMPRODUCT), каб зразумець, як гэта працуе. Функцыя SUMPRODUCT (SUMPRODUCT) разлічвае здабытак балаў кожнага элемента і яго вагу, а затым сумуе ўсе атрыманыя прадукты. Іншымі словамі, функцыя знаходзіць суму здабыткаў, адсюль і назва. Такім чынам, для Заданні 1 памножыць 85 на 5, і за Тэст памножыць 83 на 25.
Калі вам цікава, чаму нам трэба памнажаць значэнні ў першай частцы, уявіце, што чым большая вага задання, тым больш разоў мы павінны ўлічваць адзнаку за яго. Напрыклад, Заданне 2 палічылі 5 разоў і Выніковы экзамен – 45 разоў. Вось чаму Выніковы экзамен мае большы ўплыў на выніковую ацэнку.
Для параўнання, пры разліку звычайнага сярэдняга арыфметычнага кожнае значэнне ўлічваецца толькі адзін раз, гэта значыць усе значэння маюць роўны вага.
Калі б вы маглі зазірнуць пад капот функцыі SUMPRODUCT (SUMPRODUCT), мы ўбачылі, што на самой справе яна верыць у гэта:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
На шчасце, нам не трэба пісаць такую доўгую формулу, таму што SUMPRODUCT (SUMPRODUCT) робіць усё гэта аўтаматычна.
Сама па сабе функцыя SUMPRODUCT (SUMPRODUCT) вяртае нам велізарную лічбу − 10450. У гэты момант у гульню ўваходзіць другая частка формулы: /СУМ(C2:C9) or /СУМ(C2:C9), які вяртае вынік у нармальны дыяпазон балаў, даючы адказ 83,6.
Другая частка формулы вельмі важная, таму што дазваляе аўтаматычна карэктаваць разлікі. Памятаеце, што вага не павінна складаць 100%? Усё гэта дзякуючы другой частцы формулы. Напрыклад, калі мы павялічваем адно або некалькі значэнняў вагі, другая частка формулы будзе проста падзелена на большае значэнне, што зноў прывядзе да правільнага адказу. Ці мы можам зрабіць вагу значна меншай, напрыклад, указаўшы такія значэнні, як 0,5, 2,5, 3 or 4,5, і формула будзе працаваць правільна. Выдатна, праўда?