Excel SUMIF: Ƙididdigar Kuɗi na Saduwa da Takaddun Bayanai

01 na 08

Yadda SUMIF Function Works

Tasirin SUMIF na Excel na Excel. © Ted Faransanci

SUMIF Ayyuka Hanya

Ayyukan SUMIF ya hada aikin IF da SUM aiki a Excel. Wannan haɗin yana ba ka damar ƙara waɗannan dabi'u a cikin jerin zaɓuɓɓukan da aka zaɓa da suka dace da wasu ka'idodi.

Da IF wani ɓangare na aikin yana ƙayyade abin da bayanai ke haɗu da ƙayyadaddun ka'idodi da kuma SUM ɓangare na ƙari.

Yawanci, ana amfani da SUMIF tare da layukan bayanai da ake kira records. A cikin rikodin , duk bayanai a kowane tantanin halitta a jere suna da alaƙa - kamar sunan kamfanin, adireshin da lambar waya.

SUMIF ya dubi takamaiman ka'idoji a cikin tantanin halitta daya ko filin a cikin rikodin kuma, idan ya samo wasan, yana ƙara da cewa bayanai ko bayanai a wani filin da aka kayyade a cikin wannan rikodin.

SUMIF Ayyukan Mataki ta Mataki na Mataki

Wannan koyo yana amfani da saiti na bayanan bayanan da aikin SUMIF don gano yawan tallace-tallace na shekara shekara ga Sales Reps wanda ya sayar da fiye da 250 umarni.

Biye da matakai a cikin darussan koyawa da ke ƙasa ke tafiya ta hanyar samar da amfani da SUMIF aikin gani a cikin hoton da ke sama don ƙididdige yawan tallace-tallace a shekara.

Tutorial Topics

02 na 08

Shigar da Bayanan Tutorial

Tasirin SUMIF na Excel na Excel. © Ted Faransanci

Shigar da Bayanan Tutorial

Mataki na farko don yin amfani da SUMIF aiki a Excel shine shigar da bayanai.

Shigar da bayanai a cikin sassan B1 zuwa E11 na takardar aiki na Excel kamar yadda aka gani a cikin hoto a sama.

Ayyukan SUMIF da kuma ma'aunin bincike (fiye da umarni 250) za a kara zuwa jere 12 a ƙasa da bayanan.

Lura: Umurni na koyawa ba sun haɗa da matakan tsarawa don aikin aiki ba.

Wannan ba zai dame shi ba tare da kammala tutorial. Kayan aikinku zai bambanta da misalin da aka nuna, amma aikin SUMIF zai ba ku daidai wannan sakamakon.

03 na 08

Hanyoyin SUMIF Ayyuka

Hanyoyin SUMIF Ayyuka. © Ted Faransanci

Hanyoyin SUMIF Ayyuka

A cikin Excel, haɗin aikin yana nufin layout na aikin kuma ya haɗa da sunan aiki, shafuka, da muhawara .

Haɗin aikin SUMIF shine:

= SUMIF (Range, Criteria, Sum_range)

SumIF Ayyukan Yanayin SUMIF

Ayyukan aikin suna nuna aikin abin da muke gwadawa kuma wane irin bayanai ne a jimillar lokacin da yanayin ya hadu.

Range - rukuni na sel da aikin shine don bincika.

Tura - wannan darajar an kwatanta da bayanan da ke cikin sel. Idan an samu wasa sai an kara yawan bayanai a cikin sum_range . Ana iya shigar da bayanai na ainihi ko tantancewar tantanin halitta zuwa bayanai don wannan hujja.

Sum_range (na zaɓi) - an ƙaddamar da bayanai a wannan kewayon Kwayoyin a lokacin da aka samu matakan tsakanin shawara mai faɗi da ka'idoji . Idan an tsallake wannan kewayawa, za a ninka maɓallin farko a maimakon.

04 na 08

Fara aikin SUMIF

> Gyara SUMIF Ayyukan maganganu. © Ted Faransanci

Ana bude SUMIF Ayyukan maganganu

Kodayake yana yiwuwa a rubuta SUMIF kawai a cikin salula a cikin takardun aiki, mutane da yawa suna neman sauki don amfani da maganganun aikin don shigar da aikin.

Tutorial Steps

  1. Danna kan tantanin halitta E12 don yin sautin mai aiki . Wannan shi ne inda za mu shiga aikin SUMIF.
  2. Danna kan shafukan Formulas .
  3. Danna kan maɓallin Math & Trig a kan rubutun don buɗe jerin ayyukan da aka sauke.
  4. Danna kan SUMIF a jerin don kawo akwatin maganganun SUMIF.

Bayanin da muka shiga cikin layuka guda uku a cikin akwatin maganganu zai haifar da muhawarar aikin SUMIF.

Wadannan muhawara suna nuna aikin abin da muke gwadawa kuma wane irin bayanai ne zuwa jimillar lokacin da yanayin ya hadu.

05 na 08

Shigar da Magana na Range

Shigar da Magana na Range. © Ted Faransanci

Shigar da Magana na Range

A cikin wannan koyaswar muna so mu sami tallace-tallace na tallace-tallace ga wadanda suka sayar da tallace-tallace waɗanda suka fi umarni 250 a cikin shekara.

Shawarar Range ya nuna aikin SUMIF wanda rukuni na sel ya bincika a lokacin ƙoƙarin gano ka'idoji na musamman > 250 .

Tutorial Steps

  1. A cikin akwatin maganganu, danna kan Ranar Range .
  2. Kullukan da ba su da tsawa D3 zuwa D9 a kan takardun aiki don shigar da waɗannan maƙallan ƙwayoyin kamar layin da za a bincika ta wurin aikin.

06 na 08

Shigar da Sharuddan Magana

Tasirin SUMIF na Excel na Excel. © Ted Faransanci

Shigar da Sharuddan Magana

A cikin wannan misali idan bayanai a cikin kewayon D3: D12 yana da fiye da 250 to, adadin tallace-tallace na wannan rikodin za a kara ta da aikin SUMIF.

Kodayake ainihin bayanai - irin su rubutu ko lambobi kamar "> 250" za a iya shiga cikin maganganun maganganun wannan hujja mafi yawanci don ƙara bayanai zuwa cell a cikin takardun aiki sa'annan a shigar da wannan tantanin halitta a cikin akwatin maganganu.

Tutorial Steps

  1. Danna kan layi a cikin akwatin maganganu.
  2. Danna kan tantanin halitta E13 don shigar da wannan tantanin salula. Ayyukan za su bincika tasirin da aka zaba a mataki na baya don bayanai da suka dace da wannan sharuddan (Arewa).

Siffofin Siffofin Ƙara Ayyukan Sanya Ayyuka

Idan bincike na tantanin halitta, irin su E12, an shigar da shi azaman Maɗaukaki Bayani, aikin SUMIF zai nemo matakan zuwa duk bayanan da aka shigar a cikin tantanin ɗin a cikin takardun aiki.

Don haka bayan gano tallace-tallace na tallace-tallace na Sales Reps da fiye da 250 umarni zai zama sauƙin samun cikakken tallace-tallace don sauran lambobin lambobin - irin su kasa da 100 - kawai ta canzawa
"> 250" zuwa ". Aikin zai sabunta ta atomatik kuma nuna sabon sakamakon.

07 na 08

Shigar da Takaddun Sum_range

Shigar da Takaddun Sum_range. © Ted Faransanci

Shigar da Takaddun Sum_range

Ƙungiyar Sum_range ita ce rukuni na kwayoyin da aikin yake tarawa idan ya samo wasan a cikin Tasirin Ranar da aka gano a mataki na 5 na koyawa.

Wannan jigidar ita ce na zaɓi kuma, idan aka cire shi, Excel yana ƙara ƙwayoyin da aka ƙayyade a cikin Tunaniyar Range .

Tun da yake muna son tallar tallace-tallace na Sales Reps tare da umarni 250 fiye da 250 muna amfani da bayanan a cikin jerin tallace-tallace na Total kamar shirin Sum_range .

Tutorial Steps

  1. Danna kan layin Sum_range a cikin akwatin maganganu.
  2. Fassara sel E3 zuwa E12 a kan maƙallan rubutu don shigar da waɗannan ƙididdigar sel kamar Sum_range.
  3. Danna Ya yi don rufe akwatin maganganu kuma kammala aikin SUMIF.
  4. Amsar da zero ya kamata ya bayyana cikin tantanin halitta E12 - tantanin halitta inda muka shiga aikin - saboda ba mu riga muka kara bayanai zuwa filin Jagora (D12) ba.

Da zarar an shigar da bayanai zuwa tantanin halitta D12 a mataki na gaba, idan filin filin na rikodin ya ƙunshi wasa don ma'auni a cikin D12 - za a ƙara bayanai a cikin Ƙasar Tallace-tallace don wannan rikodin zuwa jimlar ta wurin aikin.

08 na 08

Ƙara Matakan Bincike

Ƙara Matakan Bincike. © Ted Faransanci

Ƙara Matakan Bincike

Mataki na karshe a cikin koyawa shine don ƙara ma'auni da muke so aikin ya dace.

A wannan yanayin muna son tallace-tallace na tallace-tallace na Sales Sales tare da umarni fiye da 250 don haka za mu kara da kalmar > 250 zuwa D12 - tantanin tantanin halitta da aka gano a cikin aikin kamar yadda ya ƙunshi jayayya.

Tutorial Steps

  1. A cikin cell D12 type > 250 kuma danna maɓallin shigarwa akan keyboard.
  2. Amsar $ 290,643.00 ya kamata ya bayyana a cikin cell E12. Sakamakon "> 250" an hadu ne a cikin kwayoyi hudu a shafi D: D4, D5, D8, D9. A sakamakon haka, lambobi a cikin sassan da ke daidai a cikin shafi na E: E4, E5, E8, E9 suna haɗuwa.
  3. Lokacin da ka danna kan tantanin halitta E12, cikakken aikin
    = SUMIF (D3: D9, D12, E3: E9) yana bayyana a cikin maɓallin tsari a sama da takardun aiki.
  4. Don samun tallace-tallace na tallace-tallace don lambobi daban-daban, rubuta adadin, kamar su na cell E12 kuma latsa maɓallin shigarwa a kan keyboard.
  5. Adadin tallace-tallace don yawan yawan kwayoyin halitta ya kamata ya bayyana a cell E12.