SUMIF funkcija programmā Excel un summēšana ar vairākiem nosacījumiem

Excel ir neticami funkcionāla programma. Pat ar iebūvēto funkciju komplektu pietiek, lai veiktu gandrīz jebkuru uzdevumu. Un bez standarta, kas pazīstamas daudziem, ir arī tādi, par kuriem daži ir dzirdējuši. Bet tajā pašā laikā tie nepārstāj būt noderīgi. Viņiem ir šaurāka specializācija, un ne vienmēr tie ir vajadzīgi. Bet, ja jūs par tiem zināt, tad kritiskā brīdī tie var būt ļoti noderīgi.

Šodien mēs runāsim par vienu no šādām funkcijām − SUMMESLIMN.

Ja lietotājs saskaras ar uzdevumu summēt vairākas vērtības, koncentrējoties uz noteiktiem kritērijiem, tad ir jāizmanto funkcija SUMMESLIMN. Formula, kas izmanto šo funkciju, ņem šos nosacījumus kā argumentus, pēc tam summē tiem atbilstošās vērtības, un pēc tam atrastā vērtība tiek ievadīta šūnā, kurā tā ir ierakstīta. 

SUMIFS funkcijas detalizēts apraksts

Pirms funkcijas apsvēršanas SUMMESLIMN, vispirms ir jāsaprot, kas ir tā vienkāršāka versija – SUMMESLI, jo uz to ir balstīta funkcija, kuru mēs apsveram. Ikvienam no mums droši vien jau ir zināmas divas bieži lietotas funkcijas – SUM (veic vērtību summēšanu) un JA (pārbauda vērtību pret noteiktu nosacījumu).

Ja jūs tos apvienojat, jūs iegūsit citu funkciju - SUMMESLI, kas pārbauda datus pēc lietotāja norādītajiem kritērijiem un summē tikai tos skaitļus, kas atbilst šiem kritērijiem. Ja mēs runājam par Excel angļu valodas versiju, tad šo funkciju sauc par SUMIF. Vienkāršiem vārdiem sakot, nosaukums - valodā ir tiešs tulkojums no angļu valodas nosaukuma. Šo funkciju var izmantot dažādiem mērķiem. Jo īpaši to var izmantot kā alternatīvu VPR, tas ir, pierakstiet

Galvenā atšķirība starp funkciju SUMMESLIMN  no parastās funkcijas SUMMESLI ir izmantoti vairāki kritēriji. Tās sintakse no pirmā acu uzmetiena ir diezgan sarežģīta, taču, rūpīgāk izpētot, izrādās, ka šīs funkcijas loģika ir ļoti vienkārša. Vispirms jums ir jāizvēlas diapazons, kurā dati tiks pārbaudīti, un pēc tam jāiestata nosacījumi, saskaņā ar kuriem tiks veikta analīze. Un šādu operāciju var veikt diezgan daudziem apstākļiem.

Pati sintakse ir:

SUMIFS(summa_diapazons, nosacījuma_diapazons1, nosacījums1, [nosacījuma_diapazons2, nosacījums2], …)

Piemērotās vietās ir nepieciešams ievietot konkrētam gadījumam piemērotu šūnu masīvus. 

Apskatīsim argumentus sīkāk:

1. Sum_range. Šis arguments, kā arī 1. un 1. nosacījuma diapazons ir nepieciešams. Tas ir šūnu kopums, kas ir jāapkopo.
2. Nosacījuma_diapazons1. Šis ir diapazons, kurā tiks pārbaudīts stāvoklis. Tas ir savienots pārī ar nākamo argumentu - Condition1. Kritērijam atbilstošo vērtību summēšana tiek veikta šūnās, kas norādītas iepriekšējā argumentā.
3. Stāvoklis1. Šis arguments nosaka kritērijus, kas ir jāpārbauda. To var iestatīt, piemēram, šādi: “> 32”.
4. Nosacījuma diapazons 2, nosacījums 2… Šeit šādi nosacījumi tiek iestatīti tādā pašā veidā. Ja ir jānorāda vairāk nekā daži nosacījumi, tiek pievienoti argumenti Nosacījuma diapazons 3 un Nosacījums 3. Sintakse ir vienāda šādiem argumentiem.

Funkcija ļauj maksimāli apstrādāt līdz pat 127 apstākļu un diapazonu pāriem. 

Varat to izmantot vairākās jomās vienlaikus (mēs sniegsim tikai dažas, patiesībā saraksts ir vēl garāks):

1. Grāmatvedība. Piemēram, ir labi izmantot funkciju SUMMESLIMN lai izveidotu kopsavilkuma pārskatus pa ceturkšņiem, piemēram, par tēriņiem, kas pārsniedz noteiktu summu. Vai arī izveidojiet pārskatu par vienu produktu no noteiktas cenu kategorijas.
2. Pārdošanas vadība. Arī šeit šī funkcija var būt ļoti noderīga. Piemēram, mēs saskaramies ar uzdevumu summēt tikai to preču izmaksas, kuras tika pārdotas noteiktam klientam noteiktā laikā. Un šādā situācijā funkcija SUMMESLIMN var būt ļoti noderīga.
3. Izglītība. Šodien mēs sniegsim vairāk praktisku piemēru no šīs jomas. Jo īpaši varat to izmantot, lai iegūtu skolēnu atzīmju kopsavilkumu. Jūs varat izvēlēties vienu priekšmetu vai atsevišķas pakāpes. Cilvēks uzreiz var uzstādīt vairākus kritērijus, pēc kuriem tiks izvēlēts vērtējums, kas ir ļoti ērti un var ietaupīt daudz laika.

Kā redzat, šīs funkcijas lietojumu klāsts ir ļoti plašs. Bet tas nav vienīgais viņa nopelns. Apskatīsim vēl dažas šīs funkcijas priekšrocības:

1. Spēja iestatīt vairākus kritērijus. Kāpēc tā ir priekšrocība? Varat izmantot parasto funkciju SUMMESLI! Un tas viss tāpēc, ka tas ir ērti. Nav nepieciešams veikt atsevišķus aprēķinus katram kritērijam. Visas darbības var ieprogrammēt iepriekš, pat iepriekš. kā tiks veidota datu tabula. Tas ir lielisks laika ietaupījums.
2. Automatizācija. Mūsdienu laikmets ir automatizācijas laikmets. Tikai tas, kurš zina, kā pareizi automatizēt savu darbu, var nopelnīt daudz. Tieši tāpēc spēja apgūt Excel un funkciju SUMMESLIMN jo īpaši tas ir tik svarīgi jebkurai personai, kas vēlas veidot karjeru. Zinot vienu funkciju, varat veikt vairākas darbības vienlaikus, kā vienu. Un šeit mēs pārejam pie nākamās šīs funkcijas priekšrocības.
3. Ietaupot laiku. Tikai tāpēc, ka viena funkcija vienlaikus veic vairākus uzdevumus.
4. Vienkāršība. Neskatoties uz to, ka sintakse no pirmā acu uzmetiena ir diezgan smaga tās apjomīguma dēļ, patiesībā šīs funkcijas loģika ir ļoti vienkārša. Vispirms tiek atlasīts datu diapazons, pēc tam vērtību diapazons, kurā tiks pārbaudīta atbilstība noteiktam nosacījumam. Un, protams, ir jāprecizē arī pats nosacījums. Un tā vairākas reizes. Faktiski šī funkcija ir balstīta tikai uz vienu loģisku konstrukciju, kas padara to vienkāršāku par labi zināmo VPR neskatoties uz to, ka to var izmantot tiem pašiem mērķiem, ņemot vērā arī lielāku skaitu kritēriju. 

Funkcijas SUMIFS izmantošanas iespējas

Šīs funkcijas izmantošanai ir vairākas funkcijas, kurām jāpievērš uzmanība. Pirmkārt, šī funkcija ignorē diapazonus ar teksta virknēm vai nullēm, jo ​​šos datu tipus nevar pievienot kopā aritmētiskā shēmā, tikai savienot kā virknes. Šī funkcija to nevar izdarīt. Jums arī jāpievērš uzmanība šādiem nosacījumiem:

1. Varat izmantot šāda veida vērtības kā nosacījumus šūnu atlasei, lai tālāk pievienotu tajās esošās vērtības: skaitliskās vērtības, Būla izteiksmes, šūnu atsauces un tā tālāk. 
2. Ja tiek pārbaudīts teksts, loģiskās izteiksmes vai matemātiskās zīmes, tad šādi kritēriji tiek norādīti pēdiņās.
3. Nevar izmantot terminus, kas garāki par 255 rakstzīmēm.
4. Ir iespējams izmantot aptuvenus kritērijus vērtību atlasei, izmantojot aizstājējzīmes. Jautājuma zīmi izmanto, lai aizstātu vienu rakstzīmi, un reizināšanas zīme (zvaigznīte) ir nepieciešama, lai aizstātu vairākas rakstzīmes. 
5. Būla vērtības, kas atrodas summēšanas diapazonā, tiek automātiski pārveidotas par skaitliskām vērtībām atbilstoši to veidam. Tādējādi vērtība “TRUE” pārvēršas par vienu, bet “FALSE” – par nulli. 
6. Ja #VĒRTĪBA! šūnā parādās kļūda, tas nozīmē, ka šūnu skaits nosacījuma un summēšanas diapazonos ir atšķirīgs. Jums ir jāpārliecinās, ka šo argumentu izmēri ir vienādi. 

Funkcijas SUMIFS izmantošanas piemēri

funkcija SUMMESLIMN nav tik sarežģīti, kā šķiet no pirmā acu uzmetiena, izrādās. Bet lielākas skaidrības labad apskatīsim dažus praktiskus šīs funkcijas izmantošanas piemērus SUMMESLIMN. Tādējādi būs daudz vieglāk iedziļināties tēmā.

Nosacījuma summēšanas dinamiskais diapazons

Tātad, sāksim ar pirmo piemēru. Pieņemsim, ka mums ir tabula, kurā ir informācija par to, kā skolēni tiek galā ar mācību programmu konkrētajā priekšmetā. Ir atzīmju komplekts, sniegums tiek vērtēts 10 ballu skalā. Uzdevums ir atrast eksāmena vērtējumu tiem skolēniem, kuru uzvārds sākas ar burtu A un minimālais punktu skaits ir 5.

Tabula izskatās šādi.

Lai mēs varētu aprēķināt kopējo punktu skaitu, pamatojoties uz iepriekš aprakstītajiem kritērijiem, mums ir jāpiemēro šāda formula.

Sīkāk aprakstīsim argumentus:

1. C3:C14 ir mūsu summēšanas diapazons. Mūsu gadījumā tas sakrīt ar nosacījumu diapazonu. No tā tiks atlasīti punkti, kas tiek izmantoti summas aprēķināšanai, bet tikai tie, kas atbilst mūsu kritērijiem.
2. “>5” ir mūsu pirmais nosacījums.
3. B3:B14 ir otrais summēšanas diapazons, kas tiek apstrādāts, lai atbilstu otrajam kritērijam. Mēs redzam, ka nav sakritības ar summēšanas diapazonu. No tā mēs secinām, ka summēšanas diapazons un nosacījuma diapazons var būt vai nebūt identisks. 
4. “A*” ir otrais diapazons, kas nosaka atzīmju atlasi tikai tiem skolēniem, kuru uzvārds sākas ar A. Mūsu gadījumā zvaigznīte nozīmē jebkuru rakstzīmju skaitu. 

Pēc aprēķiniem mēs iegūstam šādu tabulu.

Kā redzat, formula summēja vērtības, pamatojoties uz dinamisko diapazonu un lietotāja norādītajiem nosacījumiem.

Selektīvā summēšana pēc nosacījuma programmā Excel

Tagad pieņemsim, ka mēs vēlamies iegūt informāciju par to, kādas preces uz kurām valstīm tika nosūtītas pēdējā ceturkšņa laikā. Pēc tam atrodiet kopējos ieņēmumus no sūtījumiem jūlijā un augustā.

Pati tabula izskatās šādi. 

Lai noteiktu gala rezultātu, mums ir nepieciešama šāda formula.

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

Pēc šīs formulas veikto aprēķinu rezultātā mēs iegūstam šādu rezultātu.

Uzmanību! Šī formula izskatās diezgan liela, lai gan mēs izmantojām tikai divus kritērijus. Ja datu diapazons ir vienāds, varat ievērojami samazināt formulas garumu, kā parādīts zemāk.

SUMIFS funkcija, lai summētu vērtības dažādos apstākļos

Tagad ilustrācijai sniegsim vēl vienu piemēru. Šajā gadījumā tabula paliek tāda pati kā iepriekšējā gadījumā. 

Mēs izmantojam šādu formulu (bet mēs to rakstām kā masīva formulu, tas ir, ievadām to, izmantojot taustiņu kombināciju CTRL + SHIFT + ENTER).

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

Pēc funkcijas SUMMESLIMN summēs vērtību masīvu, pamatojoties uz formulā norādītajiem kritērijiem (tas ir, Ķīnas un Gruzijas valstis), iegūtais masīvs tiek summēts ar parasto funkciju SUM, kas ir uzrakstīts kā masīva formula.

Ja nosacījumi tika nodoti kā masīva konstante vairāk nekā vienam pārim, formula sniegs nepareizu rezultātu.

Tagad apskatīsim tabulu, kurā ir kopsummas.

Kā redzat, mums tas ir izdevies. Tev arī noteikti izdosies. Lieliski panākumi šajā jomā. Šī ir ļoti vienkārša funkcija, ko var saprast cilvēks, kurš tikko spēris Excel apguves ceļu. Un mēs jau zinām, ka funkcija SUMMESLIMN ļauj būt efektīvam jebkurā darbības jomā, sākot no grāmatvedības līdz pat izglītībai. Pat ja veidojat karjeru kādā citā jomā, kas nav aprakstīta iepriekš, šī funkcija joprojām palīdzēs jums nopelnīt naudu. Tāpēc viņa ir vērtīga.

Pats galvenais, tas ļauj ietaupīt laiku, kas diemžēl ir ierobežots resurss. Šķiet, ka divu funkciju pielietošanai ir pāris sekundes, bet, ja ir jāveic milzīgs skaits atkārtotu darbību, tad šīs sekundes summējas par stundām, kuras varētu veltīt kaut kam citam. Tāpēc mēs iesakām praktizēt šīs funkcijas izmantošanu. Turklāt tas ir neticami vienkārši.