Yaya za a ƙayyade ɗaukar nauyi a Excel tare da SUMPRODUCT

01 na 01

Taskar Ayyukan Excel ta Excel

Wuri mai nauyi vs. Ra'ayin Farko na Farko

Yawancin lokaci, yayin da aka ƙayyade matsakaici ko mahimmanci, kowane lambar yana da daidaito ko nauyin daidai.

An ƙayyade matsakaici ta hanyar ƙara lambobin lambobi tare sannan a rarraba wannan jimlar ta hanyar adadin lambobi a cikin kewayon .

Misali zai kasance (2 + 3 + 4 + 5 + 6) / 5 wanda ya ba da matsakaicin matsayi na 4.

A cikin Excel, ana iya yin wannan lissafi ta hanyar amfani da aikin AVERAGE .

Matsakaicin matsakaicin, a gefe guda, ya ɗauki lambobi ɗaya ko fiye a cikin kewayon don ya fi darajar, ko yana da nauyi fiye da sauran lambobi.

Alal misali, wasu alamomi a makaranta, irin su midterm da jarrabawa na karshe, sun fi darajar fiye da gwaje-gwaje na yau da kullum ko ayyukan.

Idan ana amfani dashi don ƙididdige ƙarshen ƙarshen ɗalibin jarrabawa da tsakiyar gwagwarmaya da kuma jarrabawar ƙarshe za a ba da nauyi.

A cikin Excel, ana iya ƙididdiga farashin mai nauyi ta amfani da aikin SUMPRODUCT .

Yaya Ayyukan SUMPRODUCT Works

Abin da SUMPRODUCT ya aikata yana ninka abubuwa na abubuwa biyu ko fiye kuma sannan ƙara ko ƙayyade samfurori.

Alal misali, a cikin halin da ake ciki inda zane-zane biyu tare da abubuwa hudu an shigar da shi a matsayin gardama don aikin SUMPRODUCT:

• kashi na farko na array1 ya karu ne ta hanyar farko a array2;
• kashi na biyu na array1 yana karuwa ta kashi na biyu na array2;
• kashi na uku na array1 an karu ta kashi na uku na array2;
• kashi na hudu na array1 yana karu ta kashi hudu na array2.

Bayan haka, samfurori na ayyukan haɓaka ta hudu an taƙaita kuma sun dawo ta wurin aikin a matsayin sakamakon.

Hanyoyin aiki na Excel SUMPRODUCT da jayayya

Haɗin aikin yana nufin layout na aikin kuma ya haɗa da sunan aikin, shafuka, da muhawara.

Haɗin aikin aikin SUMPRODUCT shine:

= SUMPRODUCT (array1, array2, array3, ... array255)

Maganganun aikin SUMPRODUCT sune:

array1: (da ake buƙata) na farko jigidar gardama.

array2, array3, ... array255: (zaɓin) karin kayan aiki, har zuwa 255. Tare da abubuwa biyu ko fiye, aikin yana ƙara abubuwa da kowane tsararren tare sa'an nan kuma kara da sakamakon.

- abubuwa masu tsafta za su iya kasancewa a cikin labaran da aka ba da bayanai a cikin takardun aiki ko lambobin da aka raba ta hanyar masu amfani da harshe - irin su da (+) ko alamu (-). Idan an shigar da lambobin ba tare da rabuwa da masu aiki ba, Excel yana bi da su azaman bayanin rubutu. Wannan yanayin ya kasance a cikin misalin da ke ƙasa.

Lura :

• Dukkan jayayyar gwaje-gwaje dole ne girman daidai. Ko kuma, a wasu kalmomi, dole ne a sami nau'in adadin abubuwa a cikin kowane tsararru. Idan ba haka ba, SUMPRODUCT ya dawo da #VALUE! kuskure kuskure.

• Idan duk abubuwan tsararrun ba lambobi ba - irin su bayanan rubutu - SUMPRODUCT yana bi da su azaman nau'i.

Misali: Ƙididdiga Matsayi mai Mahimmanci a Excel

Misalin da aka nuna a cikin hoton da ke sama yana ƙayyade matsakaicin ma'auni don alamar ƙirar dalibi ta yin amfani da aikin SUMPRODUCT.

Ayyukan na aiwatar da wannan ta hanyar:

• ninka yawan alamomi da nauyin nauyin nauyin nauyin;
• Ƙara abubuwan da waɗannan haɗin kai suka haɗa tare;
• raba kashi na sama da jimlar nauyin ma'auni 7 (1 + 1 + 2 + 3) don kimantawa hudu.

Shigar da Formula Weighting

Kamar sauran ayyuka a Excel, SUMPRODUCT kullum ana shiga cikin takardun aiki ta amfani da akwatin maganganun aikin. Duk da haka, tun da ma'aunin ma'auni yana amfani da SUMPRODUCT a hanya marar daidaituwa - sakamakon aikin ya rabu da nauyin nauyin nauyin - nauyin ma'auni ya kamata a buga shi a cikin sashin layi.

Matakan da aka yi amfani da su sunyi amfani da su a cikin tantanin halitta C7:

1. Danna kan C7 C7 don sa shi tantanin aiki - wurin da za a nuna alama ta karshe na dalibin
2. Rubuta ma'anar wannan a tantanin halitta:

   = SUMPRODUCT (B3: B6, C3: C6) / (1 + 1 + 2 + 3)

3. Latsa maɓallin shigarwa a kan keyboard

4. Amsar 78.6 ya kamata ya bayyana a cell C7 - amsarka tana iya samun wurare masu yawa

Matsayin da ba daidai ba don guda hudu alamu zai kasance 76.5

Tun da ɗalibin ya sami kyakkyawan sakamako ga jarrabawar jarraba da jarrabawar ƙarshe, yin la'akari da matsakaici ya taimaka wajen inganta cikakkiyar alama.

Formula Bambancin

Don jaddada cewa sakamakon aikin SUMPRODUCT ya raba ta kashi ɗaya daga cikin ma'aunin ma'aunin ma'auni na kowane kundin gwagwarmaya, an raba sassan - kashi na raba rabawa - (1 + 1 + 2 + 3).

Za'a iya ƙayyade cikakken ma'auni ta hanyar shigar da lamba 7 (jimlar nauyin nauyi) a matsayin mai raba. Wannan tsari zai kasance:

= SUMPRODUCT (B3: B6, C3: C6) / 7

Wannan zabi yana da lafiya idan yawan abubuwa a cikin tsararren tsararraki ƙananan ƙananan kuma za'a iya haɗa su tare da juna, amma ya zama ƙasa da tasiri kamar yawan adadin abubuwan da ke cikin nauyin nauyi yana ƙaruwa don ƙara ƙarin ɗakinsu.

Wani zaɓi, kuma tabbas mafi kyaun zabi - tun da yake yana amfani da bayanan salula fiye da lambobi a cikin ɓangaren raɗaɗɗi - zai kasance don amfani da SUM aiki don ƙaddamar da raba tare da ma'anar shine:

= SUMPRODUCT (B3: B6, C3: C6) / SUM (B3: B6)

Yawancin lokaci ya fi dacewa don shigar da bayanan salula fiye da ainihin lambobi a cikin ƙidodi kamar yadda ya sauƙaƙe ana ɗaukaka su idan bayanin bayanan ya canza.

Alal misali, idan an canja abubuwan da suka shafi nauyin Ayyuka zuwa 0.5 a cikin misali da kuma gwaje-gwaje zuwa 1.5, dole ne a gyara rubutattun siffofin farko na wannan tsari da hannu don gyara mai raba.

A cikin bambancin na uku, kawai bayanai a cikin sel B3 da B4 suna buƙata a sake sabuntawa kuma wannan tsari zai sake gano sakamakon.