Excel ya sanya lissafin matsakaicin sel da yawa aiki mai sauƙi - kawai amfani da aikin GABAWA (MAGASKIYA). Amma idan wasu dabi'u suna ɗaukar nauyi fiye da sauran fa? Misali, a cikin darussa da yawa, gwaje-gwaje suna ɗaukar nauyi fiye da ɗawainiya. Don irin waɗannan lokuta, wajibi ne a lissafta matsakaiciyar nauyi.
Excel ba shi da aiki don ƙididdige matsakaicin nauyi, amma akwai aikin da zai yi muku mafi yawan ayyukan: SUMPRODUCT (SUM PRODUCT). Kuma ko da ba ku taɓa amfani da wannan fasalin ba, a ƙarshen wannan labarin za ku yi amfani da shi kamar pro. Hanyar da muke amfani da ita tana aiki a kowace sigar Excel da sauran maƙunsar bayanai kamar Google Sheets.
Muna shirya tebur
Idan za ku lissafta matsakaicin nauyi, kuna buƙatar aƙalla ginshiƙai biyu. Rukunin farko (shafi na B a cikin misalinmu) ya ƙunshi maki ga kowane aiki ko gwaji. Shafi na biyu (shafi C) ya ƙunshi ma'auni. Ƙarin nauyi yana nufin ƙarin tasiri na ɗawainiya ko gwaji a kan matakin ƙarshe.
Don fahimtar menene nauyin nauyi, zaku iya tunanin shi azaman kashi na ƙimar ku na ƙarshe. A gaskiya ma, wannan ba haka ba ne, tun da a cikin wannan yanayin ya kamata ma'auni ya kara har zuwa 100%. Tsarin da za mu yi nazari a cikin wannan darasi zai lissafta komai daidai kuma ba ya dogara da adadin da ma'aunin ya karu.
Mun shigar da dabara
Yanzu da teburin mu ya shirya, muna ƙara tsari zuwa tantanin halitta B10 (kowane tantanin halitta mara komai zai yi). Kamar kowace dabara a cikin Excel, muna farawa da alamar daidai (=).
Kashi na farko na tsarin mu shine aikin SUMPRODUCT (SUM PRODUCT). Dole ne a haɗa gardama a cikin maɓalli, don haka mu buɗe su:
=СУММПРОИЗВ(
=SUMPRODUCT(
Na gaba, ƙara muhawarar aikin. SUMPRODUCT (SUMPRODUCT) na iya samun mahawara da yawa, amma yawanci ana amfani da biyu. A cikin misalinmu, hujja ta farko za ta kasance kewayon sel. B2: b9A wanda ya ƙunshi maki.
=СУММПРОИЗВ(B2:B9
=SUMPRODUCT(B2:B9
Hujja ta biyu za ta kasance kewayon sel ku: ku2, wanda ya ƙunshi nauyin nauyi. Dole ne a raba waɗannan gardama ta hanyar waƙafi (waƙafi). Lokacin da komai ya shirya, rufe maƙallan:
=СУММПРОИЗВ(B2:B9;C2:C9)
=SUMPRODUCT(B2:B9,C2:C9)
Yanzu bari mu ƙara kashi na biyu na tsarin mu, wanda zai raba sakamakon da aka lissafta da aikin SUMPRODUCT (SUMPRODUCT) ta jimlar ma'auni. Za mu tattauna daga baya dalilin da ya sa wannan yake da muhimmanci.
Don yin aikin rarraba, muna ci gaba da tsarin da aka riga aka shigar tare da alamar / (slash kai tsaye), sannan rubuta aikin SUM (SUM):
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(
=SUMPRODUCT(B2:B9, C2:C9)/SUM(
Don aiki SUM (SUM) za mu ƙayyade hujja ɗaya kawai - kewayon sel ku: ku2. Kar a manta da rufe bakunan bayan shigar da gardama:
=СУММПРОИЗВ(B2:B9;C2:C9)/СУММ(C2:C9)
=SUMPRODUCT(B2:B9, C2:C9)/SUM(C2:C9)
Shirya! Bayan danna maɓallin Shigar, Excel zai lissafta matsakaicin nauyi. A cikin misalinmu, sakamakon ƙarshe zai kasance 83,6.
Yadda yake aiki
Bari mu rushe kowane bangare na dabara, farawa da aikin SUMPRODUCT (SUMPRODUCT) don fahimtar yadda yake aiki. Aiki SUMPRODUCT (SUMPRODUCT) yana ƙididdige samfurin makin kowane abu da nauyinsa, sannan ya tara duk samfuran da aka samu. A wasu kalmomi, aikin yana samo jimlar samfurori, saboda haka sunan. Don haka don Ayyuka 1 ninka 85 ta 5, kuma don Jarabawar ninka 83 ta 25.
Idan kana mamakin dalilin da yasa muke buƙatar ninka dabi'u a kashi na farko, yi tunanin cewa mafi girman nauyin aikin, yawancin lokuta dole ne mu yi la'akari da darajarsa. Misali, Aiki 2 kirga sau 5 kuma Jarabawar karshe – sau 45. Shi ya sa Jarabawar karshe yana da tasiri mafi girma akan matakin ƙarshe.
Don kwatantawa, lokacin ƙididdige ma'anar lissafin da aka saba, kowane ƙima ana la'akari da shi sau ɗaya kawai, wato, duk ƙimar suna da nauyi daidai.
Idan kuna iya duba ƙarƙashin murfin aiki SUMPRODUCT (SUMPRODUCT), mun ga cewa a gaskiya ta gaskata wannan:
=(B2*C2)+(B3*C3)+(B4*C4)+(B5*C5)+(B6*C6)+(B7*C7)+(B8*C8)+(B9*C9)
An yi sa'a, ba ma buƙatar rubuta irin wannan dogon dabarar saboda SUMPRODUCT (SUMPRODUCT) yana yin duk wannan ta atomatik.
Aiki a kanta SUMPRODUCT (SUMPRODUCT) yana dawo mana da babbar lamba - 10450. A wannan lokaci, kashi na biyu na dabara ya zo cikin wasa: /SUM(C2:C9) or /SUM(C2:C9), wanda ke mayar da sakamakon zuwa matsakaicin adadin maki, yana ba da amsa 83,6.
Sashi na biyu na dabara yana da matukar mahimmanci saboda yana ba ku damar gyara lissafin ta atomatik. Ka tuna cewa nauyi ba dole ba ne ya ƙara har zuwa 100%? Duk wannan godiya ga sashi na biyu na dabara. Misali, idan muka ƙara ƙima ɗaya ko fiye da nauyin nauyi, ɓangaren na biyu na dabara zai raba kawai da mafi girman ƙimar, sake haifar da amsa daidai. Ko kuma za mu iya sanya ma'aunin nauyi ya fi ƙanƙanta, misali ta hanyar ƙididdige ƙima kamar 0,5, 2,5, 3 or 4,5, kuma dabarar za ta yi aiki daidai. Yana da kyau, dama?