Excel SUMIFS: Makhalidwe Okha Okha Misonkhano Yambiri Yambiri

Ntchito ya SUMIFS imapindulitsa ntchito ya SUMIF mwa kukulolani kufotokozera kuchokera pa 2 mpaka 127 kusiyana ndi imodzi monga SUMIF.

Kawirikawiri, SUMIFS imagwira ntchito ndi mizere ya deta yomwe imatchedwa mayina. M'mbuyo , deta yonse mu selo iliyonse kapena gawo mumzerewu ikugwirizana - monga dzina la kampani, adiresi ndi nambala ya foni.

SUMIFS imafufuza zoyenera pazinthu ziwiri kapena zingapo m'makalata ndipo ngati zimapeza machesi m'munda uliwonse womwe watchulidwa ndi deta ya mbiriyo.

01 pa 10

Momwe SUMIFS Imagwirira Ntchito

Mu sitepe ya SUMIF ndi phunziro la magawo tinalumikizana ndi njira imodzi yokha ya ogulitsa malonda omwe anagulitsa malamulo oposa 250 pachaka.

Mu phunziroli, tidzakhazikitsa zikhalidwe ziwiri pogwiritsa ntchito SUMIFS - zomwe zimagulitsa amalonda ku dera la East sales omwe anali ndi malonda oposa 275 chaka chatha.

Kukhazikitsa zinthu zoposa ziwiri kungatheke mwa kutchula zina Zowonjezera Criteria_range ndi Zotsutsa za SUMIFS.

Kutsatira ndondomeko m'mitu ya maphunziro yomwe ili pansipa ikukuyendetsani kupanga ndikugwiritsa ntchito ntchito ya SUMIFS yomwe ikuwonetsedwa pa chithunzi pamwambapa.

Mitu Yophunzitsa

• Kulowa Datorial Data
• Syntax ya SUMIFS Function
• Kuyambira SUMIFS Ntchito
• Zokambirana Zophatikizapo
• The Criteria_range1 Kutsutsana
• Chotsutsa1 Kukangana
• Mtsutso wa Criteria_range2
• Mtsutso wa Criteria2

02 pa 10

Kulowa Datorial Data

Njira yoyamba yogwiritsira ntchito SUMIFS ntchito ku Excel ndikolowetsa deta.

Lowani deta mu maselo D1 mpaka F11 pa tsamba la Excel monga momwe taonera pa chithunzi pamwambapa.

SUMIFS imagwira ntchito komanso zofufuza (zosakwana 275 malamulo ndi ogulitsa malonda kuchokera ku East East chiwerengero) zidzawonjezedwa ku mzere 12 pansi pa deta.

Mauthengawa saphatikizapo kupanga mapangidwe a tsamba.

Izi sizidzasokoneza kukwaniritsa maphunziro. Tsamba lanu la ntchito lidzawoneka mosiyana ndi chitsanzo chikuwonetsedwa, koma ntchito ya SUMIFS idzakupatsani zotsatira zofanana.

03 pa 10

Syntax ya SUMIFS Function

Mu Excel, syntax ya ntchito imatanthawuza momwe ntchitoyo ikuyendera ndipo imaphatikizapo dzina la ntchito, mabakita, ndi zifukwa .

Chidule cha ntchito ya SUMIFS ndi:

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

Zindikirani: Kufikira 127 Criteria_range / Pawiri zoyenera zikhoza kufotokozedwa mu ntchitoyi.

Zokambirana za SUMIFS Ntchito

Mfundo zokhudzana ndi ntchitoyi zimapangitsa kuti ntchito izi ziyesedwe komanso kuti ndi deta yochuluka bwanji zomwe zidzakwaniritsidwe.

Zonse zokhudzana ndi ntchitoyi zimafunika.

Chidule_chidziwitso - data mu maselo ambiriwa ikuphatikizidwa pamene masewerawa amapezeka pakati pa Zomwe zimayikidwa ndi zifukwa zawo zofanana za Criteria_range .

Criteria_range - gulu la maselo ntchitoyo ndi kufufuza machesi ku mgwirizano wovomerezeka woyenera.

Zowonongeka - mtengo uwu umafanizidwa ndi deta mu Criteria_range yofanana. Dongosolo lenileni kapena mawonekedwe a selo pa deta angalowetsedwe pazitsutsano izi.

04 pa 10

Kuyambira SUMIFS Ntchito

Ngakhale kuti n'zotheka kungolemba SUMIFS ntchito mu selo mu tsamba , anthu ambiri amavutika kuti agwiritse ntchito bokosi la ntchitoyi kuti alowe ntchito.

Maphunziro Otsogolera

1. Dinani pa selo F12 kuti mupange selo yogwira ntchito . Apa ndi pamene tidzalowa ntchito ya SUMIFS.
2. Dinani pa Fomu ya Fomu.
3. Dinani pa Math & Trig icon pa riboni kuti mutsegule ntchito yolemba pansi.
4. Dinani pa SUMIFS mndandanda kuti mubweretse bokosi la ntchito la SUMIFS.

Deta yomwe tifika mu mizere yopanda kanthu mu bokosi la bokosi idzakhazikitsa zifukwa za ntchito ya SUMIFS.

Zokambirana izi zimapereka ntchito zomwe tikuyesera komanso zomwe zingatipangitse kuti tidziwe ngati zinthuzo zatha.

05 ya 10

Kulowa Mphindi_kutsutsana

Mtsutsano wa_magulu ali ndi mafotokozedwe a selo ku deta yomwe tikufuna kuwonjezera.

Pamene ntchitoyo imapeza machesi pakati pa zifukwa zonse zofotokozera ndi zofunikira za Criteria_range za mbiri ya Sum_range field ya mbiriyi ikuphatikizidwa mu chiwerengerocho.

Mu phunziro ili, deta ya mkangano wa Sum_range uli mu Total Sales column.

Maphunziro Otsogolera

1. Dinani pa Mzere wamtundu_mzere mubox .
2. Onetsetsani maselo F3 mpaka F9 mu worksheet kuti muwonjezere maumboni awa pa mzere wa Sum_range .

06 cha 10

Kulowa mu Criteria_range1 Kukangana

Mu phunziro ili tikuyesera kufanana ndi zofunikira ziwiri pa chiwerengero chilichonse cha data:

1. Ogulitsa malonda kuchokera ku East East dera.
2. Ogulitsa malonda omwe osachepera 275 amagulitsa chaka chino.

Mtsutso wa Criteria_range1 ukuwonetsera maselo osiyanasiyana SUMIFS amafufuza pamene akuyesera kufanana ndi zoyambazo - dera la East East.

Maphunziro Otsogolera

1. Mu bokosi la bokosi , dinani pa Criteria_range1 mzere.
2. Onetsetsani maselo D3 mpaka D9 mu tsamba lolemba kuti mulowetse maumboni awa ngati malo omwe angasaka ndi ntchitoyo .

07 pa 10

Kulowetsa Zotsutsana1 Kukangana

Mu phunziroli, zoyambirira zomwe tikuyang'ana kuti zifanane ndi ngati deta iliyonse D3: D9 ikufanana ndi East .

Ngakhale chidziwitso chenicheni - monga mawu a East - chingalowe mu bokosi lazokambirana pazokambirana izi ndi bwino kuwonjezera deta mu selolo pa tsambalo ndiyeno lowetsani selololo mu bokosilo.

Maphunziro Otsogolera

1. Dinani pa Criteria1 mzere mu bokosi la dialog .
2. Dinani pa selo D12 kuti mulowetse selolo. Ntchitoyi idzafufuza zosankhidwa zomwe zasankhidwa kumbuyo kwa deta yomwe ikugwirizana ndi izi.
3. Nthawi yofufuzira (Kummawa) idzawonjezedwa ku selo D12 mu sitepe yotsiriza ya phunzirolo.

Momwe Mapepala Amagwiritsira Ntchito Ambiri Amawonjezera SUMIFS Kusiyanitsa

Ngati mawonekedwe a selo, monga D12, alowetsedwa ngati Chotsutsa Chotsutsa, ntchito ya SUMIFS idzayang'ana machesi kwa chilichonse chimene chidaikidwa mu selololo pa tsamba .

Tsono mutatha kupeza malonda a kumadera a Kum'maŵa zidzakhala zosavuta kupeza deta yofanana ndi malo ena ogulitsira malonda pokhapokha mutasintha East mpaka kumpoto kapena kumadzulo mu selo D12. Ntchitoyi idzangosintha ndi kusonyeza zotsatira zatsopano.

08 pa 10

Kulowa mu Criteria_range2 Kukangana

Monga tanenera kale, mu phunziro ili tikuyesera kufanana ndi zigawo ziwiri pazomwe deta iliyonse:

1. Ogulitsa malonda kuchokera ku East East dera.
2. Ogulitsa malonda omwe osachepera 275 amagulitsa chaka chino.

Mtsutso wa Criteria_range2 ukuwonetsa maselo osiyanasiyana SUMIFS ndiyesa kufufuza pamene akuyesera kufanana ndi chiyeso chachiwiri - ogulitsa malonda amene agulitsa maola oposa 275 chaka chino.

Maphunziro Otsogolera

1. Mu bokosi la bokosi , dinani pa Criteria_range2 mzere.
2. Limbikitsani maselo E3 mpaka E9 mu tsamba la ntchito kuti mulowetse maumboni awa ngati gawo lachiwiri lofufuzidwa ndi ntchitoyi .

09 ya 10

Kulowa pa Zotsutsa2 Kutsutsana

Mu phunziroli, njira yachiwiri yomwe tikuyang'ana kuti tiyifane ndi ngati deta iliyonse E3: E9 ndi yosakwana 275 malonda.

Mofanana ndi ndondomeko ya Criteria1 , tidzalowa mu selo loyang'ana pa malo a Criteria2 mu bokosi la zokambirana m'malo mwa deta.

Maphunziro Otsogolera

1. Dinani pa Criteria2 mzere mu bokosi la bokosi .
2. Dinani pa selo E12 kuti mulowetse selolo. Ntchitoyi idzafufuza zosankhidwa zomwe zasankhidwa kumbuyo kwa deta yomwe ikugwirizana ndi izi.
3. Dinani OK kuti mutsirizitse ntchito SUMIFS ndi kutseka bokosi la dialog.
4. Yankho la zero (0) lidzawoneka mu selo F12 - selo limene talowa mu ntchito - chifukwa sitinayambe kuwonjezera deta ku minda ya Criteria1 ndi Criteria2 (C12 ndi D12). Mpaka titachita, palibe chilichonse chothandizira kuwonjezera ndipo chiwerengerocho chimakhala pa zero.
5. Zotsatira zofufuzira zidzawonjezedwa mu sitepe yotsatira ya phunziroli.

10 pa 10

Kuwonjezera Zofuna Zosaka ndi Kumaliza Tutorial

Gawo lomaliza la phunzirolo ndi kuwonjezera deta ku maselo omwe ali pa tsamba lolembedwa kuti ali ndi zifukwa zotsutsa .

Maphunziro Otsogolera

Pothandizidwa ndi chitsanzo ichi onani chithunzi pamwambapa.

1. Mu selo la D12 mtundu wa East ndipo dinani fungulo lolowani mukibokosi.
2. Muchigawo cha E12 <275 ndikusindikizira fungulo lolowamo mu kibokosi ("<" ndi chizindikiro chochepa kuposa mu Excel).
3. Yankho la $ 119,719.00 liyenera kuoneka mu selo F12.
4. Ndi ziwiri zokha zomwe zimalemba zomwe zili m'mizere 3 ndi 4 zikugwirizana ndi zofunikira zonsezo, choncho, zogulitsa zonse zokhudzana ndi zolemba ziwirizi zikuphatikizidwa ndi ntchitoyi.
5. Chiwerengero cha $ 49,017 ndi $ 70,702 ndi $ 119,719.
6. Mukasindikiza pa selo F12, ntchito yonse
   = SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) amawoneka mu barra yolozera pamwamba pa tsamba .