Excel SUMIFS: Ƙayyadaddun Ƙunni ne kawai Kan gamuwa da yawa

Ayyukan SUMIFS yana ƙarfafa aikin SUMIF ta ƙyale ka ka ƙayyade daga 2 zuwa 127 sharuddan maimakon ɗaya kamar SUMIF.

Yawanci, SUMIFS yana aiki tare da layuka na bayanai da ake kira records. A cikin rikodin , duk bayanai a cikin kowane cell ko filin a jere suna da alaƙa - irin su sunan kamfanin, adireshin da lambar waya.

SUMIFS yana neman ƙayyadaddun ma'auni a wurare biyu ko fiye a cikin rikodin kuma idan ya samo wasa don kowane filin da aka ƙayyade shi ne bayanan da aka ƙayyade wannan rikodin.

01 na 10

Yadda SUMIFS Function Works

A cikin SUMIF koyaushe a kowane mataki mun dace da jinsin tallace-tallace na tallace-tallace da suka sayar da fiye da 250 umarni a cikin shekara guda.

A cikin wannan koyo, za mu kafa yanayi guda biyu ta yin amfani da SUMIFS - wanda ke cikin tallace-tallace a yankin Gabas ta Kudu wanda ke da talatin fiye da 275 a cikin shekara ta gabata.

Za'a iya sanya fiye da yanayi biyu ta hanyar ƙayyade ƙarin Criteria_range da Criteria abubuwan da aka yi wa SUMIFS.

Biye da matakai a cikin darussan koyawa da ke ƙasa ke tafiya ta hanyar samarwa da yin amfani da aikin SUMIFS da aka gani a cikin hoto a sama.

Tutorial Topics

• Shigar da Bayanan Tutorial
• Halin SUMIFS Function's Syntax
• Fara aikin SUMIFS
• Amsar Sum_range
• A Criteria_range1 Argument
• The Criteria1 Argument
• The Criteria_range2 Argument
• The Criteria2 Argument

02 na 10

Shigar da Bayanan Tutorial

Mataki na farko don amfani da SUMIFS aiki a Excel shi ne shigar da bayanai.

Shigar da bayanai a cikin sel D1 zuwa F11 na aikin aiki na Excel kamar yadda aka gani a cikin hoto a sama.

Ayyukan SUMIFS da ka'idojin bincike (ƙananan umarni 275 da masu sayar da tallace-tallace daga yankin Gabas ta Tsakiya) za a kara zuwa jere 12 a kasa da bayanan.

Umarnin koyawa ba sun haɗa da matakan tsarawa don takardar aiki ba.

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

03 na 10

Halin SUMIFS Function's Syntax

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

Haɗin aikin SUMIFS shine:

= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Note: Har zuwa 127 Criteria_range / Criteria nau'i-nau'i za a iya ƙayyade a cikin aiki.

SumIFS Ayyukan Yanayin SUMIFS

Ayyukan aikin suna nuna aikin abin da ake jarraba ka'idodin da kuma wace irin bayanai zuwa sumba idan an cika waɗannan yanayi.

Ana buƙatar dukan muhawarar wannan aikin.

Sum_range - bayanai a cikin wannan jeri na sel an taƙaita lokacin da aka samu wasa a tsakanin Matakan da aka ƙayyade da matakan Criteria_range na daidai.

Criteria_range - rukuni na kwayoyin halitta shine aikin nema don bincika wasan da aka dace da hujja.

Tura - wannan darajar an kwatanta da bayanan da ke cikin Criteria_range daidai. Ana iya shigar da bayanai na ainihi ko tantancewar tantanin halitta zuwa bayanai don wannan hujja.

04 na 10

Fara aikin SUMIFS

Ko da yake yana yiwuwa a rubuta SUMIFS kawai a cikin salula a cikin takardar aiki , mutane da yawa sun fi sauƙi don amfani da maganganun aikin don shigar da aikin.

Tutorial Steps

1. Danna kan tantanin halitta F12 don yin sautin mai aiki . Wannan shine inda za mu shiga aikin SUMIFS.
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 SUMIFS cikin jerin don kawo akwatin maganganun SUMIFS.

Bayanin da muka shiga cikin layi a cikin akwatin maganganu zai haifar da muhawarar aikin SUMIFS.

Wadannan muhawara suna nuna aikin abin da muke gwadawa kuma wane irin bayanan bayanai idan aka hadu da waɗannan yanayi.

05 na 10

Shigar da Takaddun Sum_range

Takaddun Sum_range yana dauke da tantancewar labaran bayanan da muke son ƙarawa.

Lokacin da aikin ya samo wasa tsakanin dukan Kayan da aka ƙayyade da Takaddun abubuwan da aka tsara don rikodin filin Sum_range domin wannan rikodin ya haɗa cikin jimlar.

A cikin wannan koyo, bayanan Sum_range yana samuwa a cikin Total Sales column.

Tutorial Steps

1. Danna kan layin Sum_range a cikin akwatin maganganu .
2. Fassara sassan F3 zuwa F9 a cikin takardun aiki don ƙara waɗannan tantanin halitta a cikin layin Sum_range .

06 na 10

Shigar da Criteria_range1 Argument

A cikin wannan koyo muna ƙoƙari mu daidaita ma'auni guda biyu a kowane rikodin bayanai:

1. Jami'an sayar da kayayyaki daga yankin Gabas ta Tsakiya.
2. Jami'an sayar da kayayyaki da suka sanya talatin fiye da 275 a wannan shekara.

Shaidar Criteria_range1 yana nuna iyakar sassan da SUMIFS ke nema don bincika lokacin ƙoƙarin daidaita ka'idodin farko - yankin yankin gabas.

Tutorial Steps

1. A cikin akwatin maganganu , danna kan Criteria_range1 line.
2. Sanya sassa D3 zuwa D9 a cikin takardun aiki don shigar da waɗannan maƙallan siginar kamar layin da za a bincike ta wurin aikin .

07 na 10

Shigar da Criteria1 Argument

A cikin wannan koyawa, matakan farko da muke kallon wasan shine idan bayanai a cikin kewayon D3: D9 yana daidaita gabas .

Kodayake ainihin bayanai - irin su kalmar Gabas - za a iya shigar da akwatin 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 na Criteria1 a cikin akwatin maganganu .
2. Danna kan tantanin halitta D12 don shigar da wannan tantanin salula. Ayyukan za su bincika tashar da aka zaba a mataki na baya don bayanai da suka dace da wannan sharudda.
3. Za a kara ma'anar bincike (East) zuwa cell D12 a mataki na karshe na koyawa.

Ta yaya Siffofin Sake Ƙara SUMIFS Versatility?

Idan an yi la'akari da tantanin tantanin halitta, kamar D12, a matsayin Maƙasudin Magana, aikin SUMIFS zai bincika matakan zuwa duk wani bayanai da aka shigar a cikin tantanin a cikin takardun aiki .

Don haka bayan da aka gano adadin tallace-tallace na yankin Gabas zai zama sauƙin samun bayanai guda don wani yanki na tallace-tallace ta wurin canza Gabas zuwa Arewa ko Yamma a cell D12. Aikin zai sabunta ta atomatik da kuma nuna sabon sakamakon.

08 na 10

Shigar da Criteria_range2 Argument

Kamar yadda aka ambata a baya, a cikin wannan koyo muna ƙoƙarin daidaita ka'idodi guda biyu a kowane rikodin bayanai:

1. Jami'an sayar da kayayyaki daga yankin Gabas ta Tsakiya.
2. Jami'an sayar da kayayyaki da suka sanya talatin fiye da 275 a wannan shekara.

Shaidar Criteria_range2 ta nuna iyakar sassan da SUMIFS ke nema don bincika lokacin ƙoƙarin daidaita ka'idodi na biyu - masu sayar da tallace-tallace waɗanda suka sayar da fiye da umarni 275 a wannan shekara.

Tutorial Steps

1. A cikin akwatin maganganu , danna kan jerin Criteria_range2 .
2. Ƙananan kwayoyin E3 zuwa E9 a cikin takardun aiki don shigar da waɗannan ƙididdigar sel kamar matsayi na biyu da za a bincika ta aikin .

09 na 10

Shigar da Criteria2 Argument

A cikin wannan koyo, na biyu ma'aunin da muke kallon wasan shine idan bayanai a cikin kewayon E3: E9 ba shi da kasafin 275.

Kamar yadda yake da hujja na Criteria1 , za mu shiga cikin tantanin salula zuwa wurin Criteria2 a cikin akwatin maganganu maimakon bayanai kanta.

Tutorial Steps

1. Danna kan layi na Criteria2 a cikin akwatin maganganu .
2. Danna kan tantanin halitta E12 don shigar da wannan tantanin salula. Ayyukan za su bincika tashar da aka zaba a mataki na baya don bayanai da suka dace da wannan sharudda.
3. Danna Ya yi don kammala aikin SUMIFS kuma rufe akwatin maganganu.
4. Amsar da zero (0) zai bayyana a tantanin halitta F12 - tantanin halitta inda muka shiga aikin - saboda ba mu riga muka ƙara bayanai zuwa ga Criteria1 da kuma Criteria2 (C12 da D12) ba. Har sai mun yi, babu wani abu don aiki don ƙarawa kuma don haka jimlar ta tsaya a sifilin.
5. Za a kara matakan bincike a mataki na gaba na koyawa.

10 na 10

Ƙara Masarrafan Bincike da Ƙarshen Tutorial

Ƙarshen mataki a cikin koyawa shine don ƙara bayanai zuwa sel a cikin takardun aikin da aka gano kamar yadda ke dauke da hujjoji na Criteria .

Tutorial Steps

Don taimako tare da wannan misali duba hoton da ke sama.

1. A cikin sel D12 irin ta gabas kuma latsa maɓallin shigarwa akan keyboard.
2. A cikin cell E12 type <275 kuma danna maɓallin shigarwa a kan keyboard ("<" alama ce ta kasa da Excel).
3. Amsar $ 119,719.00 ya kamata ya bayyana a cell F12.
4. Biyu kawai sun rubuta abin da ke cikin layuka 3 da 4 daidai da ma'auni kuma, sabili da haka, kawai ƙididdigar tallace-tallace na waɗannan rubutun biyu an taƙaita ta aikin.
5. Jimlar $ 49,017 da $ 70,702 shine $ 119,719.
6. Lokacin da ka danna kan tantanin halitta F12, cikakken aikin
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) ya bayyana a cikin maɓallin tsari a sama da takardun aiki .