SUMIF function sa Excel at sum sa pamamagitan ng maraming kundisyon

Ang Excel ay isang hindi kapani-paniwalang functional na programa. Kahit na ang built-in na set ng tampok ay sapat na upang makumpleto ang halos anumang gawain. At bukod sa mga pamantayan, pamilyar sa marami, mayroon ding mga kakaunti ang narinig ng mga tao. Ngunit sa parehong oras, hindi sila tumitigil sa pagiging kapaki-pakinabang. Mayroon silang mas makitid na espesyalisasyon, at hindi palaging kailangan para sa kanila. Ngunit kung alam mo ang tungkol sa mga ito, kung gayon sa isang kritikal na sandali maaari silang maging lubhang kapaki-pakinabang.

Ngayon ay pag-uusapan natin ang tungkol sa isa sa mga naturang function − SUMMESLIMN.

Kung ang gumagamit ay nahaharap sa gawain ng pagbubuod ng ilang mga halaga, na tumutuon sa ilang pamantayan, kung gayon kinakailangan na gamitin ang pag-andar SUMMESLIMN. Ang formula na gumagamit ng function na ito ay tumatagal ng mga kundisyong ito bilang mga argumento, pagkatapos ay nagsusuma ng mga halaga na nakakatugon sa kanila, at pagkatapos ay ang nahanap na halaga ay ipinasok sa cell kung saan ito nakasulat. 

Detalyadong Paglalarawan ng Function ng SUMIFS

Bago isaalang-alang ang pag-andar SUMMESLIMN, kailangan mo munang maunawaan kung ano ang isang mas simpleng bersyon nito - SUMMESLI, dahil dito nakabatay ang function na aming isinasaalang-alang. Ang bawat isa sa atin ay malamang na pamilyar na sa dalawang function na kadalasang ginagamit - SUM (isinasagawa ang pagsusuma ng mga halaga) at KUNG (sumusubok ng isang halaga laban sa isang tinukoy na kundisyon).

Kung pagsasamahin mo ang mga ito, makakakuha ka ng isa pang function - SUMMESLI, na sumusuri sa data laban sa pamantayang tinukoy ng user at nagsusuma lamang ng mga numerong nakakatugon sa mga pamantayang iyon. Kung pinag-uusapan natin ang Ingles na bersyon ng Excel, kung gayon ang function na ito ay tinatawag na SUMIF. Sa simpleng salita, ang -language name ay isang direktang pagsasalin ng English-language one. Maaaring gamitin ang function na ito para sa iba't ibang layunin. Sa partikular, maaari itong magamit bilang isang kahalili VPR, ibig sabihin, isulat

Ang pangunahing pagkakaiba sa pagitan ng pag-andar SUMMESLIMN  mula sa karaniwang pag-andar SUMMESLI ay maraming pamantayan ang ginagamit. Ang syntax nito ay medyo kumplikado sa unang sulyap, ngunit sa mas malapit na pagsusuri, lumalabas na ang lohika ng function na ito ay napaka-simple. Una kailangan mong piliin ang hanay kung saan susuriin ang data, at pagkatapos ay itakda ang mga kondisyon para sa pagsunod kung saan isasagawa ang pagsusuri. At ang ganitong operasyon ay maaaring isagawa para sa isang medyo malaking bilang ng mga kondisyon.

Ang syntax mismo ay:

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

Sa angkop na mga lugar kinakailangan na maglagay ng mga arrays ng mga cell na angkop sa isang partikular na kaso. 

Tingnan natin ang mga argumento nang mas detalyado:

1. Sum_range. Ang argumentong ito, pati na rin ang hanay ng kundisyon 1 at kundisyon 1, ay kinakailangan. Ito ay isang set ng mga cell na kailangang summed.
2. Condition_range1. Ito ang hanay kung saan susuriin ang kundisyon. Ito ay ipinares sa susunod na argumento - Kondisyon1. Ang pagbubuo ng mga halaga na tumutugma sa pamantayan ay isinasagawa sa loob ng mga cell na tinukoy sa nakaraang argumento.
3. Kondisyon1. Tinutukoy ng argumentong ito ang pamantayang susuriin. Maaari itong itakda, halimbawa, sa ganitong paraan: "> 32".
4. Saklaw ng kundisyon 2, Kundisyon 2... Dito, ang mga sumusunod na kundisyon ay nakatakda sa parehong paraan. Kung higit sa ilang kundisyon ang kailangang tukuyin, idaragdag ang mga argumento ng Condition Range 3 at Condition 3. Ang syntax ay pareho para sa mga sumusunod na argumento.

Ang function ay nagbibigay-daan sa maximum na pagproseso ng hanggang 127 pares ng mga kondisyon at saklaw. 

Magagamit mo ito sa ilang lugar nang sabay-sabay (iilan lang ang ibibigay namin, mas mahaba pa talaga ang listahan):

1. Accounting. Halimbawa, magandang gamitin ang function SUMMESLIMN upang lumikha ng mga buod na ulat, sa bawat quarter para sa paggastos sa isang tiyak na halaga, halimbawa. O gumawa ng ulat sa isang produkto mula sa isang partikular na kategorya ng presyo.
2. Pamamahala ng benta. Dito rin ang function ay maaaring maging lubhang kapaki-pakinabang. Halimbawa, nahaharap tayo sa gawain ng pagbubuod lamang ng halaga ng mga kalakal na naibenta sa isang partikular na customer sa isang tiyak na oras. At sa ganoong sitwasyon, ang function SUMMESLIMN ay maaaring maging kapaki-pakinabang.
3. Edukasyon. Magbibigay kami ng mas praktikal na mga halimbawa mula sa lugar na ito ngayon. Sa partikular, magagamit mo ito upang makakuha ng buod ng mga marka ng mag-aaral. Maaari kang pumili para sa isang paksa o para sa mga indibidwal na grado. Ang isang tao ay maaaring agad na magtakda ng ilang mga pamantayan kung saan ang pagtatasa ay pipiliin, na kung saan ay napaka-maginhawa at maaaring makatipid ng maraming oras.

Tulad ng nakikita mo, ang hanay ng mga application para sa function na ito ay napakalawak. Ngunit hindi lamang ito ang merito nito. Tingnan natin ang ilan pang benepisyo na mayroon ang feature na ito:

1. Kakayahang magtakda ng maraming pamantayan. Bakit ito ay isang kalamangan? Maaari mong gamitin ang normal na function SUMMESLI! At lahat dahil ito ay maginhawa. Hindi na kailangang magsagawa ng hiwalay na mga kalkulasyon para sa bawat isa sa mga pamantayan. Ang lahat ng mga aksyon ay maaaring ma-program nang maaga, kahit na bago. kung paano mabubuo ang talahanayan ng data. Ito ay isang mahusay na oras saver.
2. Automation. Ang modernong panahon ay ang edad ng automation. Tanging ang taong marunong mag-automate ng maayos sa kanyang trabaho ang maaaring kumita ng malaki. Iyon ang dahilan kung bakit ang kakayahang makabisado ang Excel at ang pag-andar SUMMESLIMN sa partikular, ay napakahalaga para sa sinumang tao na gustong bumuo ng karera. Ang pag-alam sa isang function ay nagpapahintulot sa iyo na magsagawa ng ilang mga aksyon nang sabay-sabay, bilang isa. At narito na tayo sa susunod na benepisyo ng feature na ito.
3. Nagtipid ng oras. Dahil lamang sa katotohanan na ang isang function ay gumaganap ng ilang mga gawain nang sabay-sabay.
4. pagiging simple. Sa kabila ng katotohanan na ang syntax ay medyo mabigat sa unang tingin dahil sa bulkiness nito, sa katunayan, ang logic ng function na ito ay napaka-simple. Una, pipiliin ang isang hanay ng data, pagkatapos ay isang hanay ng mga halaga, na susuriin para sa pagsunod sa isang partikular na kundisyon. At siyempre, ang kundisyon mismo ay dapat ding tinukoy. At kaya ilang beses. Sa katunayan, ang function na ito ay batay lamang sa isang lohikal na konstruksyon, na ginagawang mas simple kaysa sa kilala VPR sa kabila ng katotohanan na maaari itong magamit para sa parehong mga layunin, isinasaalang-alang din ang isang mas malaking bilang ng mga pamantayan. 

Mga tampok ng paggamit ng SUMIFS function

Mayroong ilang mga tampok ng paggamit ng function na ito na kailangan mong bigyang-pansin. Una sa lahat, binabalewala ng function na ito ang mga hanay na may mga string ng text o null, dahil ang mga uri ng data na ito ay hindi maaaring idagdag nang magkasama sa isang pattern ng aritmetika, pinagsama-sama lamang tulad ng mga string. Hindi ito magagawa ng function na ito. Kailangan mo ring bigyang pansin ang mga sumusunod na kondisyon:

1. Maaari mong gamitin ang mga uri ng value na ito bilang mga kundisyon para sa pagpili ng mga cell upang higit pang idagdag ang mga value na nakapaloob sa mga ito: mga numeric na halaga, boolean expression, cell reference, at iba pa. 
2. Kung ang teksto, mga lohikal na expression o mga palatandaang pangmatematika ay sinusuri, kung gayon ang mga pamantayan ay tinukoy sa pamamagitan ng mga panipi.
3. Hindi maaaring gumamit ng mga terminong mas mahaba sa 255 character.
4. Posibleng gumamit ng tinatayang pamantayan para sa pagpili ng mga halaga gamit ang mga wildcard. Ang tandang pananong ay ginagamit upang palitan ang isang character, at ang multiplication sign (asterisk) ay kailangan upang palitan ang maramihang mga character. 
5. Ang mga boolean na halaga na nasa hanay ng pagbubuod ay awtomatikong kino-convert sa mga numerong halaga ayon sa kanilang uri. Kaya, ang halaga na "TRUE" ay nagiging isa, at "FALSE" - sa zero. 
6. Kung isang #VALUE! lumilitaw ang error sa isang cell, nangangahulugan ito na ang bilang ng mga cell sa kundisyon at mga hanay ng pagsusuma ay iba. Kailangan mong tiyakin na ang mga sukat ng mga argumentong ito ay pareho. 

Mga halimbawa ng paggamit ng SUMIFS function

tungkulin SUMMESLIMN hindi kasing kumplikado na tila sa unang tingin, lumalabas. Ngunit para sa higit pang kalinawan, tingnan natin ang ilang praktikal na halimbawa kung paano mo magagamit ang function SUMMESLIMN. Ito ay magiging mas madali upang bungkalin ang paksa.

Dynamic range ng pagbubuo ng kundisyon

Kaya magsimula tayo sa unang halimbawa. Sabihin nating mayroon tayong talahanayan na naglalaman ng impormasyon tungkol sa kung paano nakayanan ng mga mag-aaral ang kurikulum sa isang partikular na paksa. Mayroong isang hanay ng mga marka, ang pagganap ay tinasa sa isang 10-point scale. Ang gawain ay hanapin ang marka para sa pagsusulit ng mga mag-aaral na ang apelyido ay nagsisimula sa titik A, at ang kanilang pinakamababang marka ay 5.

Ang mesa ay ganito ang hitsura.

Upang makalkula natin ang kabuuang marka batay sa pamantayang inilarawan sa itaas, kailangan nating ilapat ang sumusunod na formula.

Ilarawan natin ang mga argumento nang mas detalyado:

1. C3:C14 ang aming hanay ng pagbubuod. Sa aming kaso, ito ay tumutugma sa hanay ng kondisyon. Mula dito ay pipiliin ang mga puntos na ginamit upang kalkulahin ang halaga, ngunit ang mga nasa ilalim lamang ng aming pamantayan.
2. Ang ">5" ay ang aming unang kondisyon.
3. Ang B3:B14 ay ang pangalawang hanay ng pagbubuod na pinoproseso upang tumugma sa pangalawang pamantayan. Nakikita namin na walang coincidence sa hanay ng pagbubuod. Mula dito napagpasyahan namin na ang hanay ng pagsusuma at ang hanay ng kundisyon ay maaaring magkapareho o hindi. 
4. Ang "A*" ay ang pangalawang hanay, na tumutukoy sa pagpili ng mga marka para lang sa mga mag-aaral na ang apelyido ay nagsisimula sa A. Sa aming kaso, ang asterisk ay nangangahulugang anumang bilang ng mga character. 

Pagkatapos ng mga kalkulasyon, nakukuha namin ang sumusunod na talahanayan.

Tulad ng nakikita mo, ang formula ay nagbubuod ng mga halaga batay sa dynamic na hanay at batay sa mga kundisyon na tinukoy ng user.

Selective summation ayon sa kundisyon sa Excel

Ngayon, ipagpalagay na gusto naming makakuha ng impormasyon tungkol sa kung anong mga kalakal ang ipinadala sa kung aling mga bansa noong huling quarter. Pagkatapos nito, hanapin ang kabuuang kita mula sa mga pagpapadala para sa Hulyo at Agosto.

Ang mesa mismo ay ganito ang hitsura. 

Upang matukoy ang pangwakas na resulta, kailangan namin ng gayong formula.

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

Bilang resulta ng mga kalkulasyon na isinagawa ng formula na ito, nakukuha namin ang sumusunod na resulta.

Atensyon! Mukhang malaki ang formula na ito kahit na dalawang pamantayan lang ang ginamit namin. Kung pareho ang hanay ng data, maaari mong makabuluhang bawasan ang haba ng formula, tulad ng ipinapakita sa ibaba.

Ang SUMIFS ay gumagana upang sumama ang mga halaga sa maraming kundisyon

Ngayon magbigay tayo ng isa pang halimbawa upang ilarawan. Sa kasong ito, ang talahanayan ay nananatiling pareho sa nakaraang kaso. 

Ginagamit namin ang sumusunod na formula (ngunit isinulat namin ito bilang isang array formula, iyon ay, ipinasok namin ito sa pamamagitan ng key na kumbinasyon CTRL + SHIFT + ENTER).

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

Pagkatapos ng function SUMMESLIMN ay magbubuod ng hanay ng mga halaga batay sa pamantayang tinukoy sa pormula (iyon ay, ang mga bansa ng Tsina at Georgia), ang nagreresultang hanay ay nasusuma ng karaniwang pag-andar SUM, na nakasulat bilang isang array formula.

Kung ang mga kundisyon ay naipasa bilang array constant para sa higit sa isang pares, ang formula ay magbibigay ng maling resulta.

Ngayon tingnan natin ang talahanayan na naglalaman ng mga kabuuan.

Tulad ng nakikita mo, nagtagumpay kami. Siguradong magtatagumpay ka rin. Malaking tagumpay sa larangang ito. Ito ay isang napaka-simpleng function na ang isang tao na nakatapak sa landas ng pag-aaral ng Excel ay maaaring maunawaan. At alam na natin na ang function SUMMESLIMN nagbibigay-daan sa iyo na maging epektibo sa anumang larangan ng aktibidad, mula sa accounting hanggang sa edukasyon. Kahit na nagtatayo ka ng isang karera sa anumang iba pang lugar na hindi pa inilarawan sa itaas, ang tampok na ito ay makakatulong pa rin sa iyo na kumita ng pera. Ito ang dahilan kung bakit siya ay mahalaga.

Pinakamahalaga, pinapayagan ka nitong makatipid ng oras, na, sa kasamaang-palad, isang limitadong mapagkukunan. Mukhang may ilang segundo para maglapat ng dalawang function, ngunit kapag kailangan mong magsagawa ng malaking bilang ng mga paulit-ulit na operasyon, ang mga segundong ito ay magdadagdag ng mga oras na maaaring gastusin sa ibang bagay. Kaya inirerekomenda namin na magsanay ka sa paggamit ng feature na ito. Bukod dito, ito ay hindi kapani-paniwalang simple.