I-Excel yenze ukubala isilinganiso samaseli amaningi kwaba umsebenzi olula kakhulu - vele usebenzise umsebenzi ISAVERAGE (ISILINGANISO). Kodwa kuthiwani uma ezinye izindinganiso zinesisindo esingaphezu kwamanye? Isibonelo, ezifundweni eziningi, izivivinyo zithwala isisindo esingaphezu kwezabelo. Ezimweni ezinjalo, kuyadingeka ukubala isilinganiso esilinganiselwe.
I-Excel ayinawo umsebenzi wokubala isilinganiso esinesisindo, kodwa kunomsebenzi ozokwenzela umsebenzi omningi: SUMPRODUCT (UMkhiqizo we-SUM). Futhi noma ngabe ungakaze usebenzise lesi sici ngaphambilini, ekupheleni kwalesi sihloko uzobe usisebenzisa njengochwepheshe. Indlela esiyisebenzisayo isebenza kunoma iyiphi inguqulo ye-Excel kanye namanye amaspredishithi njenge-Google AmaSpredishithi.
Silungisa itafula
Uma uzobala isilinganiso esinesisindo, uzodinga okungenani amakholomu amabili. Ikholomu yokuqala (ikholomu B esibonelweni sethu) iqukethe amaphuzu omsebenzi ngamunye noma ukuhlolwa. Ikholomu yesibili (ikholomu C) iqukethe izisindo. Isisindo esiningi sisho umthelela owengeziwe womsebenzi noma ukuhlolwa ebangeni lokugcina.
Ukuze uqonde ukuthi siyini isisindo, ungacabanga ngakho njengephesenti lebanga lakho lokugcina. Eqinisweni, lokhu akunjalo, ngoba kulokhu izisindo kufanele zengeze ku-100%. Ifomula esizoyihlaziya kulesi sifundo izobala yonke into ngendlela efanele futhi ayincikile enanini izisindo ezihlanganisayo.
Sifaka ifomula
Manje njengoba itafula lethu selilungile, sengeza ifomula esitokisini B10 (noma iyiphi iseli engenalutho izokwenza). Njenganoma iyiphi enye ifomula ku-Excel, siqala ngophawu olulinganayo (=).
Ingxenye yokuqala yefomula yethu umsebenzi SUMPRODUCT (UMkhiqizo we-SUM). Izimpikiswano kufanele zifakwe kubakaki, ngakho-ke siyazivula:
=СУММПРОИЗВ(
=SUMPRODUCT(
Okulandelayo, engeza izimpikiswano zomsebenzi. SUMPRODUCT (I-SUMPRODUCT) ingaba nezimpikiswano eziningi, kodwa ngokuvamile ezimbili zisetshenziswa. Esibonelweni sethu, ukuphikisana kokuqala kuzoba uhla lwamaseli. B2:B9A equkethe amaphuzu.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
I-agumenti yesibili izoba ububanzi bamaseli C2:C9, equkethe izisindo. Lawa ma-agumenti kumele ahlukaniswe isemicolon (comma). Uma yonke into isilungile, vala amabakaki:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Manje ake sengeze ingxenye yesibili yefomula yethu, ezohlukanisa umphumela obalwe ngomsebenzi SUMPRODUCT (SUMPRODUCT) ngesamba sezisindo. Sizoxoxa kamuva ukuthi kungani lokhu kubalulekile.
Ukuze senze umsebenzi wokuhlukanisa, siqhubeka nefomula esivele ifakiwe enophawu / (i-slash eqondile), bese ubhala umsebenzi ISUM (SUM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Okokusebenza ISUM (SUM) sizocacisa i-agumenti eyodwa kuphela - ububanzi bamaseli C2:C9. Ungakhohlwa ukuvala abakaki ngemva kokufaka ingxabano:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Ilungile! Ngemva kokucindezela ukhiye Faka, i-Excel izobala isilinganiso esikaliwe. Esibonelweni sethu, umphumela wokugcina uzoba 83,6.
Ukuthi isebenza kanjani
Ake sihlukanise ingxenye ngayinye yefomula, siqale ngomsebenzi SUMPRODUCT (SUMPRODUCT) ukuze uqonde ukuthi isebenza kanjani. Umsebenzi SUMPRODUCT (I-SUMPRODUCT) ibala umkhiqizo wesikolo sento ngayinye nesisindo sayo, bese ihlanganisa yonke imikhiqizo ewumphumela. Ngamanye amazwi, umsebenzi uthola isamba semikhiqizo, yingakho igama. Ngakho for Izabelo 1 phindaphinda 85 ngo-5, futhi ngenxa Isivivinyo phindaphinda 83 ngo-25.
Uma uzibuza ukuthi kungani sidinga ukuphindaphinda amanani engxenyeni yokuqala, cabanga ukuthi uma umsebenzi unesisindo esikhulu, kufanele sicabangele ibanga lawo izikhathi eziningi. Ngokwesibonelo, Umsebenzi 2 kubalwe izikhathi ezi-5 futhi Ukuhlolwa kokugcina - izikhathi ezingama-45. Yingakho ke Ukuhlolwa kokugcina kunomthelela omkhulu ebangeni lokugcina.
Ukuze uqhathanise, lapho kubalwa isilinganiso esivamile se-arithmetic, inani ngalinye libhekwa kanye kuphela, okungukuthi, wonke amanani anesisindo esilinganayo.
Uma ungabheka ngaphansi kwe-hood yomsebenzi SUMPRODUCT (SUMPRODUCT), sibone ukuthi empeleni uyakukholelwa lokhu:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
Ngenhlanhla, asikho isidingo sokuthi sibhale ifomula ende ngoba SUMPRODUCT (I-SUMPRODUCT) ikwenza konke lokhu ngokuzenzakalelayo.
Umsebenzi ngokwawo SUMPRODUCT (I-SUMPRODUCT) isibuyisela inombolo enkulu − 10450. Kuleli qophelo, ingxenye yesibili yefomula iqala ukusebenza: /I-SUM(C2:C9) or /I-SUM(C2:C9), ebuyisela umphumela ebangeni elijwayelekile lamaphuzu, inikeze impendulo 83,6.
Ingxenye yesibili yefomula ibaluleke kakhulu ngoba ikuvumela ukuthi ulungise ngokuzenzakalela izibalo. Khumbula ukuthi izisindo akudingeki zingeze kufika ku-100%? Konke lokhu sibonga ingxenye yesibili yefomula. Isibonelo, uma sinyusa inani lesisindo esisodwa noma ngaphezulu, ingxenye yesibili yefomula izomane ihlukanise ngevelu enkulu, futhi kuphumele empendulweni efanele. Noma singenza izisindo zibe zincane kakhulu, isibonelo ngokucacisa amanani afana 0,5, 2,5, 3 or 4,5, futhi ifomula isazosebenza kahle. Kuhle, akunjalo?