Excel yaita kuverenga avhareji yemasero akawanda basa riri nyore - ingoshandisa basa racho AVERAGE (AVERAGE). Asi zvakadini kana mamwe maitiro akatakura uremu kupfuura mamwe? Semuenzaniso, mumakosi mazhinji, bvunzo dzinotakura huremu hwakawanda kupfuura mabasa. Nokuda kwezviitiko zvakadaro, zvakakosha kuverenga kuremerwa pakati.
Excel haina basa rekuverenga chiyero chakayerwa, asi pane basa rinokuitira rakawanda rebasa: SUMPRODUCT (SUM PRODUCT). Uye kunyangwe kana usati wamboshandisa chimiro ichi, pakupera kwechinyorwa ichi unenge uchiishandisa senge pro. Nzira yatinoshandisa inoshanda mune chero shanduro yeExcel pamwe nemamwe maspredishiti akadai seGoogle Sheets.
Isu tinogadzirira tafura
Kana iwe uchizoverenga avhareji yakayerwa, iwe uchada anokwana makoramu maviri. Koramu yekutanga ( koramu B mumuenzaniso wedu) ine zvibodzwa zvebasa rega rega kana bvunzo. Koramu yechipiri (chikamu C) ine huremu. Huremu hwakawanda hunoreva kuwanda kwesimba rebasa kana bvunzo pagiredhi rekupedzisira.
Kuti unzwisise kuti uremu chii, unogona kufunga nezvayo sechikamu chegiredhi rako rekupedzisira. Muchokwadi, izvi hazvisizvo, sezvo muchiitiko ichi uremu hunofanira kuwedzera kusvika ku100%. Iyo formula yatichaongorora muchidzidzo ichi ichaverenga zvese nemazvo uye hazvinei nehuwandu hunowedzera huremu.
Tinopinda mufomu
Iye zvino kuti tafura yedu yakagadzirira, tinowedzera chimiro kune sero B10 (chero sero isina chinhu ichaita). Sezvakaita chero imwe fomula muExcel, tinotanga nechiratidzo chakaenzana (=).
Chikamu chekutanga cheformula yedu ibasa SUMPRODUCT (SUM PRODUCT). Nharo dzinofanirwa kuvharirwa mumabhuraketi, saka tinovhura:
=СУММПРОИЗВ(
=SUMPRODUCT(
Zvadaro, wedzera basa nharo. SUMPRODUCT (SUMPRODUCT) inogona kuva nemakakatanwa akawanda, asi kazhinji maviri anoshandiswa. Mumuenzaniso wedu, nharo yekutanga ichave yakasiyana-siyana yemasero. B2:B9A ine zvibodzwa.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Nharo yechipiri ichava huwandu hwemasero C2:C9, iyo ine uremu. Nharo idzi dzinofanirwa kuparadzaniswa nesemicolon (comma). Kana zvese zvagadzirwa, vhara mabhureki:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Zvino ngatiwedzerei chikamu chechipiri chefomula yedu, inozopatsanura mhedzisiro yakaverengerwa nebasa SUMPRODUCT (SUMPRODUCT) nehuwandu hwehuremu. Tichazokurukura gare gare kuti nei izvi zvichikosha.
Kuti tiite division operation, tinoenderera mberi neforoma yakatopinda nechiratidzo / (slash yakatwasuka), wobva wanyora basa racho SUM (SUM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Zvebasa SUM (SUM) tichatsanangura nharo imwe chete - huwandu hwemasero C2:C9. Usakanganwa kuvhara maparentheses mushure mekupinda nharo:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Ready! Mushure mekudzvanya kiyi pinda, Excel ichaverenga huremu hwepakati. Mumuenzaniso wedu, mugumisiro wekupedzisira uchava 83,6.
How akunoita
Ngatiparadze chikamu chimwe nechimwe chefomula, tichitanga nebasa SUMPRODUCT (SUMPRODUCT) kuti unzwisise kuti inoshanda sei. Function SUMPRODUCT (SUMPRODUCT) inoverengera chigadzirwa chechinhu chimwe nechimwe chibodzwa uye uremu hwacho, uyezve inounganidza zvese zvinenge zvabuda. Mune mamwe mazwi, basa rinowana huwandu hwezvigadzirwa, saka zita. Saka for Mabasa 1 wedzera 85 ne5, uye nokuda Muedzo wedzera 83 ne25.
Kana iwe uchinetseka kuti sei isu tichida kuwedzera kukosha muchikamu chekutanga, fungidzira kuti kukura kwehuremu hwebasa, nguva zhinji dzatinofanira kufunga nezvegiredhi racho. Semuyenzaniso, Basa 2 akaverenga 5 nguva uye Yekupedzisira bvunzo - 45 nguva. Ndosaka Yekupedzisira bvunzo ine simba guru pagiredhi rekupedzisira.
Kuenzanisa, kana uchiverenga iyo yakajairwa arithmetic zvinoreva, kukosha kwega kwega kunotariswa kamwe chete, ndiko kuti, zvese zvakakosha zvine huremu hwakaenzana.
Kana iwe waigona kutarisa pasi pehodhi yebasa SUMPRODUCT (SUMPRODUCT), takaona kuti chokwadi anotenda izvi:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Sezvineiwo, hatifanire kunyora fomula refu kudaro nekuti SUMPRODUCT (SUMPRODUCT) inoita zvese izvi otomatiki.
Basa pacharo SUMPRODUCT (SUMPRODUCT) inotidzosera nhamba huru - 10450. Panguva ino, chikamu chechipiri chefomula chinotanga kushanda: /SUM(C2:C9) or /SUM(C2:C9), iyo inodzorera chigumisiro kuhuwandu hwehuwandu hwezvibodzwa, ichipa mhinduro 83,6.
Chikamu chechipiri cheformula chakanyanya kukosha nekuti chinokutendera kuti ugadzirise maverengero otomatiki. Rangarira kuti uremu haufanirwe kuwedzera kusvika ku100%? Zvese izvi nekuda kwechikamu chechipiri chefomula. Semuenzaniso, kana tikawedzera huremu humwechete kana kupfuura, chikamu chechipiri chechimiro chinongopatsanurwa nehukuru hukuru, zvakare zvichikonzera mhinduro chaiyo. Kana kuti isu tinogona kuita kuti huremu huve hudiki, semuenzaniso nekutsanangura kukosha senge 0,5, 2,5, 3 or 4,5, uye fomula icharamba ichishanda nemazvo. Zvakanaka, handizvo?