SUMIF imagwira ntchito mu Excel ndikuphatikiza ndi zinthu zingapo

Excel ndi pulogalamu yogwira ntchito kwambiri. Ngakhale zida zomangidwa mkati ndizokwanira kumaliza ntchito iliyonse. Ndipo kuwonjezera pa muyezo, wodziwika kwa ambiri, palinso omwe anthu ochepa amvapo. Koma panthawi imodzimodziyo, sasiya kukhala othandiza. Iwo ali ndi ukatswiri wocheperako, ndipo sikuti nthawi zonse amafunikira. Koma ngati mukudziwa za iwo, ndiye kuti pa nthawi yovuta, zingakhale zothandiza kwambiri.

Lero tikambirana imodzi mwa ntchito zotere - SUMMESLIMN.

Ngati wogwiritsa ntchito akukumana ndi ntchito yofotokozera mwachidule mfundo zingapo, poyang'ana njira zina, ndiye kuti m'pofunika kugwiritsa ntchito ntchitoyi. SUMMESLIMN. Fomula yomwe imagwiritsa ntchito ntchitoyi imatenga mikangano iyi ngati mikangano, kenako imawerengera zomwe zimakwaniritsa, kenako mtengo womwe wapezeka umalowetsedwa muselo lomwe lalembedwa. 

Kufotokozera Kwatsatanetsatane kwa Ntchito ya SUMIFS

Musanaganizire ntchito SUMMESLIMN, muyenera kumvetsetsa kaye kuti mtundu wake ndi wosavuta - SUMMESLI, chifukwa ndi momwe ntchito yomwe tikuganizirayi idakhazikitsidwa. Aliyense wa ife mwina amadziwa kale ntchito ziwiri zomwe zimagwiritsidwa ntchito nthawi zambiri - SUM (amachita zowerengera zamtengo wapatali) ndi NGATI (amayesa mtengo motsutsana ndi chikhalidwe chodziwika).

Mukawaphatikiza, mumapeza ntchito ina - SUMMESLI, yomwe imayang'ana deta motsatizana ndi zomwe munthu amagwiritsa ntchito ndikuwerengera manambala okhawo omwe amakwaniritsa izi. Ngati tilankhula za mtundu wa Chingerezi wa Excel, ndiye kuti ntchitoyi imatchedwa SUMIF. M'mawu osavuta, dzina -language ndi kumasulira kwachindunji kwa chilankhulo cha Chingerezi. Izi zitha kugwiritsidwa ntchito pazifukwa zosiyanasiyana. Makamaka, angagwiritsidwe ntchito ngati njira ina VPR, ndiko kuti, lembani

Kusiyana kwakukulu pakati pa ntchito SUMMESLIMN  kuchokera kuntchito yachizolowezi SUMMESLI ndikuti njira zingapo zimagwiritsidwa ntchito. Syntax yake ndi yovuta kwambiri poyang'ana koyamba, koma poyang'anitsitsa, zikuwoneka kuti lingaliro la ntchitoyi ndilosavuta. Choyamba muyenera kusankha mitundu yomwe deta idzayang'anidwe, ndiyeno ikani mikhalidwe yotsatiridwa yomwe kusanthula kudzachitidwa. Ndipo opaleshoni yotereyi ikhoza kuchitidwa pazifukwa zambiri.

Syntax palokha ndi:

SUMIFS(sum_range, condition_range1, condition1, [condition_range2, condition2], ...)

M'malo abwino m'pofunika kuyika magulu a maselo oyenera pazochitika zinazake. 

Tiyeni tiwone zotsutsanazo mwatsatanetsatane:

1. Sum_range. Mtsutso uwu, komanso kuchuluka kwa chikhalidwe 1 ndi chikhalidwe 1, ndizofunikira. Ndi gulu la ma cell omwe amayenera kufotokozedwa mwachidule.
2. Condition_range1. Uwu ndi mgawo womwe udzawunikiridwa. Zimaphatikizidwa ndi mkangano wotsatira - Condition1. Kuphatikizika kwa zinthu zofananira ndi muyeso kumachitika m'maselo omwe atchulidwa mkangano wam'mbuyomu.
3. Condition1. Mtsutsowu umatchula zomwe ziyenera kutsatiridwa. Ikhoza kukhazikitsidwa, mwachitsanzo, motere: "> 32".
4. Chikhalidwe 2, Chikhalidwe 2… Apa, zinthu zotsatirazi zimayikidwa mofanana. Ngati zinthu zingapo zikufunika kufotokozedwa, ndiye kuti mikangano ya Condition 3 ndi Condition 3 imawonjezedwa. Kalembedwe ka mawu ndi chimodzimodzi pa mfundo zotsatirazi.

Ntchitoyi imalola kukonza kwakukulu mpaka ma 127 awiriawiri amikhalidwe ndi magawo. 

Mutha kuzigwiritsa ntchito m'malo angapo nthawi imodzi (tingopereka ochepa, mndandandawo ndi wautali):

1. Kuwerengera ndalama. Mwachitsanzo, ndi bwino kugwiritsa ntchito SUMMESLIMN kupanga malipoti achidule, ndi kotala kuti awononge ndalama zambiri, mwachitsanzo. Kapena pangani lipoti la chinthu chimodzi kuchokera kugulu linalake lamitengo.
2. Kasamalidwe ka malonda. Apanso ntchitoyo ikhoza kukhala yothandiza kwambiri. Mwachitsanzo, timayang’anizana ndi ntchito yofotokoza mwachidule mtengo wa zinthu zimene zinagulitsidwa kwa kasitomala wina panthaŵi inayake. Ndipo muzochitika zotere, ntchitoyo SUMMESLIMN zingakhale zothandiza kwambiri.
3. Maphunziro. Tipereka zitsanzo zambiri zothandiza kuchokera kuderali lero. Makamaka, mutha kuzigwiritsa ntchito kuti mupeze chidule cha magiredi a ophunzira. Mutha kusankha phunziro limodzi kapena magiredi apawokha. Munthu akhoza kukhazikitsa nthawi yomweyo njira zingapo zomwe kuwunika kudzasankhidwa, zomwe ndi zabwino kwambiri ndipo zimatha kupulumutsa nthawi yambiri.

Monga mukuonera, mitundu yosiyanasiyana ya ntchito za ntchitoyi ndi yaikulu kwambiri. Koma uku sikuli koyenera kwake kokha. Tiyeni tiwone maubwino enanso omwe gawoli lili nawo:

1. Kutha kukhazikitsa njira zingapo. Chifukwa chiyani izi ndi zabwino? Mukhoza kugwiritsa ntchito yachibadwa SUMMESLI! Ndipo zonse chifukwa ndi yabwino. Palibe chifukwa chowerengera zosiyana pazifukwa zilizonse. Zochita zonse zitha kukonzedwa pasadakhale, ngakhale kale. momwe tebulo la deta lidzapangidwira. Iyi ndi nthawi yabwino yopulumutsira.
2. Zochita zokha. M'badwo wamakono ndi m'badwo wa automation. Ndi munthu yekhayo amene akudziwa kuwongolera bwino ntchito yake angapeze ndalama zambiri. Ichi ndichifukwa chake luso lodziwa bwino Excel ndi ntchito SUMMESLIMN makamaka, ndizofunikira kwambiri kwa munthu aliyense amene akufuna kumanga ntchito. Kudziwa ntchito imodzi kumakupatsani mwayi wochita zinthu zingapo nthawi imodzi, ngati imodzi. Ndipo apa tikupita ku phindu lotsatira la mbaliyi.
3. Kupulumutsa nthawi. Chifukwa chakuti ntchito imodzi imagwira ntchito zingapo nthawi imodzi.
4. Kuphweka. Ngakhale kuti syntax ndi yolemetsa poyang'ana koyamba chifukwa cha kuchuluka kwake, kwenikweni, malingaliro a ntchitoyi ndi ophweka kwambiri. Choyamba, chiwerengero cha deta chimasankhidwa, ndiye chiwerengero cha zikhalidwe, zomwe zidzafufuzidwa kuti zigwirizane ndi chikhalidwe china. Ndipo, ndithudi, chikhalidwecho chiyeneranso kufotokozedwa. Ndipo kotero kangapo. Ndipotu, ntchitoyi imachokera pamapangidwe amodzi okha, omwe amachititsa kuti zikhale zosavuta kusiyana ndi zodziwika bwino VPR ngakhale kuti ingagwiritsidwe ntchito pazifukwa zomwezo, ndikuganiziranso zochulukirapo. 

Mawonekedwe ogwiritsira ntchito ntchito ya SUMIFS

Pali zinthu zingapo zogwiritsira ntchito ntchitoyi zomwe muyenera kuziganizira. Choyamba, ntchitoyi imanyalanyaza mizere yokhala ndi zingwe zolembera kapena zopanda pake, chifukwa mitundu iyi ya data siyingawonjezedwe pamodzi mu masamu, yongolumikizidwa ngati zingwe. Ntchitoyi siyingachite izi. Muyeneranso kulabadira zinthu zotsatirazi:

1. Mutha kugwiritsa ntchito mitundu iyi yamakhalidwe ngati mikhalidwe yosankha ma cell kuti muwonjezere zomwe zili mmenemo: manambala, mawu a boolean, maumboni a cell, ndi zina zotero. 
2. Ngati malemba, mawu omveka bwino kapena zizindikiro za masamu zikuyang'aniridwa, ndiye kuti izi zimatchulidwa kupyolera mu mawu.
3. Sitingathe kugwiritsa ntchito mawu opitilira zilembo 255.
4. Ndizotheka kugwiritsa ntchito njira zofananira posankha makonda pogwiritsa ntchito makadi akutchire. Funso limagwiritsidwa ntchito m'malo mwa chilembo chimodzi, ndipo chizindikiro chochulutsa (nyenyezi) chimafunika kuti chilowe m'malo mwa zilembo zingapo. 
5. Makhalidwe a Boolean omwe ali mumtundu wophatikiza amasinthidwa okha kukhala manambala malinga ndi mtundu wawo. Choncho, mtengo wa "CHOONADI" umasandulika kukhala chimodzi, ndipo "BODZA" - kukhala ziro. 
6. Ngati #VALUE! cholakwika chimapezeka mu cell, zikutanthauza kuti kuchuluka kwa ma cell omwe ali mumkhalidwewo ndi magawo ake amasiyana. Muyenera kuwonetsetsa kuti miyeso ya mikanganoyi ndi yofanana. 

Zitsanzo zogwiritsa ntchito ntchito ya SUMIFS

ntchito SUMMESLIMN osati zovuta monga zikuwonekera poyang'ana koyamba, zikuwoneka. Koma kuti mumve zambiri, tiyeni tiwone zitsanzo za momwe mungagwiritsire ntchito ntchitoyi SUMMESLIMN. Izi zipangitsa kuti kukhale kosavuta kuwunikira mutuwo.

Condition summation dynamic range

Choncho tiyeni tiyambe ndi chitsanzo choyamba. Tiyerekeze kuti tili ndi tebulo limene lili ndi mfundo za mmene ophunzira amachitira ndi maphunziro a phunziro linalake. Pali magiredi, magwiridwe antchito amawunikidwa pamlingo wa 10-point. Ntchito ndikupeza giredi ya mayeso a ophunzira omwe dzina lawo lomaliza limayamba ndi chilembo A, ndipo zochepera zawo ndi 5.

Gome likuwoneka motere.

Kuti tithe kuwerengera zigoli zonse potengera zomwe tafotokozazi, tiyenera kugwiritsa ntchito njira iyi.

Tiyeni tifotokoze zokanganazo mwatsatanetsatane:

1. C3:C14 ndiye mtundu wathu wowerengera. Kwa ife, zimagwirizana ndi momwe zinthu zilili. Kuchokera pa izo zidzasankhidwa mfundo zomwe zimagwiritsidwa ntchito kuwerengera ndalamazo, koma zomwe zimagwera pansi pa zomwe tikufuna.
2. "> 5" ndi chikhalidwe chathu choyamba.
3. B3:B14 ndi mtundu wachiwiri wowerengera womwe umakonzedwa kuti ufanane ndi muyeso wachiwiri. Tikuwona kuti palibe kuphatikizika ndi kuchuluka kwa mafotokozedwe. Kuchokera pa izi timaganiza kuti kuchuluka kwa mafotokozedwe ndi kuchuluka kwa momwe zinthu zilili zitha kukhala zofanana kapena sizingafanane. 
4. "A*" ndi mndandanda wachiwiri, womwe umatchula kusankha kwa zizindikiro kwa ophunzira okhawo omwe dzina lawo lomaliza limayamba ndi A. Kwa ife, nyenyezi imatanthauza nambala iliyonse ya zilembo. 

Titawerengera, timapeza tebulo lotsatirali.

Monga mukuwonera, fomulayo idafotokoza mwachidule zamtengo wapatali kutengera kuchuluka kwamphamvu komanso kutengera zomwe wogwiritsa ntchito amafotokozera.

Kusankha mwachidule malinga ndi momwe ziliri mu Excel

Tsopano tiyerekeze kuti tikufuna kudziwa zambiri za katundu omwe adatumizidwa kumayiko omwe m'gawo lapitali. Pambuyo pake, pezani ndalama zonse zomwe zatumizidwa mu Julayi ndi Ogasiti.

Tebulo lokha likuwoneka chonchi. 

Kuti tidziwe chotsatira chomaliza, timafunikira fomula yoteroyo.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Chifukwa cha mawerengedwe opangidwa ndi formula iyi, timapeza zotsatirazi.

Chenjerani! Fomula iyi ikuwoneka yayikulu kwambiri ngakhale tidangogwiritsa ntchito ziwiri zokha. Ngati mndandanda wa deta uli wofanana, ndiye kuti mukhoza kuchepetsa kutalika kwa ndondomekoyi, monga momwe tawonetsera pansipa.

SUMIFS imagwira ntchito kuti iwononge mitengo pamikhalidwe ingapo

Tsopano tiyeni tipereke chitsanzo china kuti tifotokozere. Pankhaniyi, tebulo limakhala lofanana ndi lapitalo. 

Timagwiritsa ntchito ndondomekoyi (koma timayilemba ngati ndondomeko yowonjezera, ndiye kuti, timalowetsamo kudzera muzitsulo CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Pambuyo ntchito SUMMESLIMN adzawerengera kuchuluka kwa zikhalidwe kutengera zomwe zafotokozedwa mu chilinganizo (ndiko kuti, mayiko a China ndi Georgia), mndandanda wotsatirawu umafupikitsidwa ndi ntchito yanthawi zonse. SUM, zomwe zimalembedwa ngati njira yotsatsira.

Ngati mikhalidweyo idaperekedwa ngati gulu lokhazikika kwa gulu limodzi, ndiye kuti chilinganizocho chidzapereka zotsatira zolakwika.

Tsopano tiyeni tione tebulo lomwe lili ndi ziwonkhetso.

Monga mukuonera, tapambana. Inunso mudzapambana. Kupambana kwakukulu m'munda uno. Iyi ndi ntchito yophweka kwambiri yomwe munthu amene wangoponda kumene pa njira yophunzirira Excel angamvetse. Ndipo ife tikudziwa kale kuti ntchito SUMMESLIMN kumakupatsani mwayi wogwira ntchito m'munda uliwonse, kuyambira kuwerengera ndalama mpaka maphunziro. Ngakhale mukupanga ntchito kudera lina lililonse lomwe silinafotokozedwe pamwambapa, izi zikuthandizanibe kupeza ndalama. Ichi ndi chifukwa chake ndi wofunika.

Chofunika kwambiri, chimakupatsani mwayi wosunga nthawi, zomwe, mwatsoka, ndizochepa. Zitha kuwoneka kuti pali masekondi angapo kuti mugwiritse ntchito ntchito ziwiri, koma mukayenera kuchita zinthu zambiri zobwerezabwereza, masekondi awa amawonjezera maola omwe angagwiritsidwe ntchito pazinthu zina. Chifukwa chake tikukulimbikitsani kuti muyesetse kugwiritsa ntchito izi. Komanso, ndi amazipanga yosavuta.