01 ye 01
Excel SUMPRODUCT Function
Weighted vs. Unweighted Average Overview
Kazhinji, paunenge uchiverenga mavhareji kana masvomho zvinorevei, nhamba imwe neimwe inokosha zvakaenzana kana uremu.
Ivhareji inoratidzirwa nokuwedzera nhamba yehuwandu pamwe chete uye kugovera chiyero ichi nehuwandu hwehutsika mumwero .
Muenzaniso unogona kuva (2 + 3 + 4 + 5 + 6) / 5 iyo inopa kuwedzera kusina simba kwe4.
MuExcel, kuverenga kwakadaro kunoshandiswa nyore nyore uchishandisa AVERAGE basa .
Avhareji inorema, kune rumwe rutivi, inofunga nhamba imwe kana yakawanda muhuwandu kuti ive yakakosha, kana kuva nechiremera chikuru kudarika imwe nhamba.
Somuenzaniso, zvimwe zviratidzo kuchikoro, zvakadai sepakati nepakati uye kuongororwa kwekupedzisira, kazhinji zvinobatsira kupfuura kuongororwa nguva dzose kana mabasa.
Kana kuyerwa kunoshandiswa kuverenga yekupedzisira kwevadzidzi kwepakati pemigumo uye kuongorora kwekupedzisira kwaizopiwa kuwedzera kukuru.
MuExcel, zviyero zvinorema zvinogona kuverengwa kuburikidza neSUMPRODUCT basa.
Kuti SUMPRODUCT Function Works
Iko SUMPRODUCT inowedzera humwe hwezvinhu zviviri kana huwandu humwechete uyezve kuwedzera kana kuvhara zvigadzirwa.
Semuenzaniso, mumamiriro ezvinhu apo mabhii maviri ane zvinhu zvina zvakapinda sechikonzero cheSUMPRODUCT basa:
- iyo yekutanga ye array1 yakawanda nekutanga chokutanga mu array2;
- chikamu chechipiri chemazana1 chinowedzera nechipiri chezvinhu zve array2;
- chikamu chechitatu chemazana1 chinowedzera nechinhu chechitatu che array2;
- chechina chemazana1 chinowedzera nechina chechina ye array2.
Zvadaro, zvigadzirwa zvemabasa mana okuwedzera zvinopfupikiswa uye zvinodzorerwa nebasa sechigumisiro.
Excel SUMPRODUCT Function Syntax uye Arguments
Mutsara webasa unoreva kurongeka kwebasa uye rinosanganisira zita rebasa, mabheji, uye nharo.
Mutsara weSUMPRODUCT basa ndewokuti:
= SUMPRODUCT (array1, array2, array3, ... array255)
Nharo dzeSUMPRODUCT basa ndezvekuti:
array1: (inodikanwa) nharo yekutanga yokutanga.
array2, array3, ... array255: (kusarudza) mamwe mabirasi, kusvika ku 255. Nezvikamu zviviri kana kupfuura, basa rinowedzera zvinhu zvese zvakagadzikana pamwe chete uye zvinowedzera mhinduro.
- zvinyorwa zvinogona kuva sero rinotsanangurwa kunzvimbo yedata iri pabheji rekushanda kana nhamba dzinoparadzaniswa nevashandi vei arithmetic - zvakadai sokuwedzera (+) kana kuderedza zviratidzo (-). Kana nhamba dzapinda musina kuparadzaniswa nevashandi, Excel inovatora se data data. Iyi mamiriro akafukidzwa mumuenzaniso uri pasi apa.
Cherechedza :
Zvose zvinopikisana zvinofanira kuva zvakafanana. Kana, mune mamwe mazwi, panofanira kunge kune nhamba yakafanana yezvikamu mumhando imwe neimwe. Kana zvisina kudaro, SUMPRODUCT inodzorera #VALUE! kukanganisa kukosha.
Kana zvimwe zvikamu zvakasiyana hazvizi nhamba - zvakadai semashoko ekunyora - SUMPRODUCT inovabata sezero.
Muenzaniso: Tora Weighted Average mu Excel
Muenzaniso unoratidzwa mumufananidzo wepamusoro unotarisa chiyero chakarehwa chechiratidzo chekupedzisira chemudzidzi achishandisa SUMPRODUCT basa.
Basa racho rinozadzisa izvi ne:
- kuwedzera mavara akasiyana-siyana nehumwe humwe hunorema;
- kuwedzera zvigadzirwa zvekuwedzera kwezviitiko pamwe chete;
- akaparadzanisa chikamu chiri pamusoro apa chiyero chekuyera chikamu 7 (1 + 1 + 2 + 3) yezviongorudzo zvina.
Kupinda muHome Weighting
Kufanana nemamwe mabasa akawanda muExcel, SUMPRODUCT inowanzopinda mune pepa rekushandisa uchishandisa basa rebhokisi rebhokisi . Zvisinei, sezvo chiyero chekusimudza chinoshandisa SUMPRODUCT nenzira isiri yepamutemo-chigumisiro chebasa chinopatsanurwa nehutachiwana chikonzero - chiyero chekurema chinofanira kuiswa mumusero weshizha .
Aya matanho anotevera akashandiswa kupinda muhomwe yekusimudzira mumusero C7:
- Dzvanya pachitokisi C7 kuti uite sero rinoshanda - nzvimbo iyo chiratidzo chekupedzisira chemudzidzi chicharatidzwa
- Tora fomu iyi inotevera muyero:
= SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)
Dhinda pikicha inopinda mubhokisi
- Mhinduro 78.6 inofanira kuonekwa muchitokisi C7 - mhinduro yako inogona kunge ine nzvimbo dzakawanda
Ivhareji isina kuwedzera yezvikamu zvina zvakafanana ichange iri 76.5
Sezvo mudzidzi akava nemigumisiro yakanaka yepakati nepakati uye kuongorora kwekupedzisira, kuyerwa kwemavhareji akabatsira kuvandudza mararamiro ake ose.
Formula Variations
Kuti kusimbise kuti migumisiro yeSUMPRODUCT basa yakakamurwa nehuwandu hwezviyeuchidzo zveboka rimwe nerimwe rekuongorora, mutungamiri - chikamu chekuparadzanisa - chakapinda se (1 + 1 + 2 + 3).
Zvose zvinorema zvinogona kuiswa nyore nekupinda nhamba ye7 (chiyero chezviremba) semudzidzisi. Chirevo chacho chaizotevera:
= SUMPRODUCT (B3: B6, C3: C6) / 7
Chisarudzo ichi chakanaka kana nhamba yezvinhu zviri mukutakura kwemazana uye zvingave nyore kuwedzerwa pamwe chete, asi inowedzera kushanda sezvo nhamba yezvinhu zviri muhuwandu hwekuyera inowedzera kuita kuti kuwedzera kwavo kuve kwakaoma.
Chimwe chisarudzo, uye zvichida chisarudzo chakanakisisa-sezvo chinoshandisa zviyero zvemasero panzvimbo yeunhamba muhuwandu hwehuwandu-huchashandiswa kushandisa basa reSUM kuti muve nehuwandu hwekuparadzanisa nemutauro uri:
= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)
Zvinowanzova nani kupinda muchidimbu zvinyorwa pane kuti nhamba chaiyo ive maforomo sezvo ichiita kuti zvigadziridzwe nekuvandudza iyo kana data yefomula ichichinja.
Somuenzaniso, kana zvinhu zvakakosha zveMabasa zvakashandurwa kuva 0.5 mumuenzaniso uye kuedza ku 1.5, maitiro maviri okutanga emurairi aifanira kuchinjwa nemaoko kuti agadzirise dudziro.
Mukusiyana kwechitatu, data chete mumasero B3 neB4 inofanira kuongororwa uye rondedzero ichadzokorora chigumisiro.