Excel-də SUMIF funksiyası və bir çox şərtlərlə cəmi

Excel inanılmaz dərəcədə funksional proqramdır. Hətta daxili funksiya dəsti demək olar ki, hər hansı bir işi yerinə yetirmək üçün kifayətdir. Çoxlarına tanış olan standartlardan başqa, az adamın eşitdiyi də var. Ancaq eyni zamanda, onlar faydalı olmaqdan əl çəkmirlər. Onların daha dar bir ixtisası var və onlara həmişə ehtiyac yoxdur. Ancaq onlar haqqında bilirsinizsə, kritik anda onlar çox faydalı ola bilər.

Bu gün belə funksiyalardan biri haqqında danışacağıq - SUMMESLIMN.

Əgər istifadəçi müəyyən meyarlara diqqət yetirərək bir neçə dəyəri yekunlaşdırmaq vəzifəsi ilə qarşılaşırsa, o zaman funksiyadan istifadə etmək lazımdır. SUMMESLIMN. Bu funksiyadan istifadə edən düstur bu şərtləri arqument kimi qəbul edir, sonra onlara uyğun gələn dəyərləri cəmləyir və sonra tapılan dəyər onun yazıldığı xanaya daxil edilir. 

SUMIFS Funksiyasının Ətraflı Təsviri

Funksiyanı nəzərdən keçirməzdən əvvəl SUMMESLIMN, əvvəlcə bunun daha sadə versiyasının nə olduğunu başa düşməlisiniz - SUMMESLI, çünki nəzərdən keçirdiyimiz funksiya məhz ona əsaslanır. Yəqin ki, hər birimiz tez-tez istifadə olunan iki funksiya ilə tanışıq - SUM (qiymətlərin cəmlənməsini həyata keçirir) və ƏGƏR (dəyəri müəyyən edilmiş şərtlə sınayır).

Onları birləşdirsəniz, başqa bir funksiya əldə edəcəksiniz - SUMMESLI, bu, məlumatları istifadəçi tərəfindən müəyyən edilmiş meyarlara uyğun yoxlayır və yalnız bu meyarlara cavab verən nömrələri cəmləşdirir. Excel-in ingilis versiyasından danışırıqsa, bu funksiya SUMIF adlanır. Sadə sözlə desək, -dil adı ingilis dillinin birbaşa tərcüməsidir. Bu funksiya müxtəlif məqsədlər üçün istifadə edilə bilər. Xüsusilə, alternativ olaraq istifadə edilə bilər VPR, yəni yazın

Funksiya arasındakı əsas fərq SUMMESLIMN  adi funksiyadan SUMMESLI bir neçə meyardan istifadə olunur. Onun sintaksisi ilk baxışda kifayət qədər mürəkkəbdir, lakin diqqətlə araşdırdıqda məlum olur ki, bu funksiyanın məntiqi çox sadədir. Əvvəlcə məlumatların yoxlanılacağı aralığı seçməlisiniz və sonra təhlilin aparılacağı uyğunluq şərtlərini təyin etməlisiniz. Və belə bir əməliyyat kifayət qədər çox sayda şərtlər üçün həyata keçirilə bilər.

Sintaksis özü belədir:

SUMIFS(cəm_aralığı, şərt_aralığı1, şərt1, [şərt_aralığı2, şərt2], …)

Uyğun yerlərdə müəyyən bir vəziyyətdə uyğun olan hüceyrələr sıralarını qoymaq lazımdır. 

Arqumentlərə daha ətraflı baxaq:

1. cəm_aralığı. Bu arqument, həmçinin şərt 1 və şərt 1 diapazonu tələb olunur. Bu cəmlənməsi lazım olan hüceyrələr toplusudur.
2. Vəziyyət_aralığı1. Bu, vəziyyətin yoxlanılacağı diapazondur. O, növbəti arqumentlə - Şərt 1 ilə qoşalaşmışdır. Kriteriyaya uyğun gələn dəyərlərin cəmlənməsi əvvəlki arqumentdə göstərilən xanalar daxilində aparılır.
3. Vəziyyət 1. Bu arqument yoxlanılacaq meyarları müəyyənləşdirir. Məsələn, bu şəkildə təyin edilə bilər: “> 32”.
4. Şərt diapazonu 2, Şərt 2... Burada aşağıdakı şərtlər eyni şəkildə qurulur. Əgər bir neçə şərtin göstərilməsinə ehtiyac varsa, o zaman Şərt Aralığı 3 və Şərt 3 arqumentləri əlavə edilir. Sintaksis aşağıdakı arqumentlər üçün eynidir.

Funksiya 127 cüt şərait və diapazonun maksimum işləməsinə imkan verir. 

Siz eyni anda bir neçə sahədə istifadə edə bilərsiniz (yalnız bir neçəsini verəcəyik, siyahı əslində daha uzundur):

1. Mühasibat uçotu. Məsələn, funksiyadan istifadə etmək yaxşıdır SUMMESLIMN məsələn, müəyyən bir məbləğdən artıq xərcləmə üçün rüb üzrə xülasə hesabatlar yaratmaq. Və ya müəyyən bir qiymət kateqoriyasından bir məhsul haqqında hesabat yaradın.
2. Satış menecmenti. Burada da funksiya çox faydalı ola bilər. Məsələn, bizim qarşımızda yalnız müəyyən vaxtda müəyyən müştəriyə satılan malların dəyərini yekunlaşdırmaq vəzifəsi durur. Və belə bir vəziyyətdə, funksiyası SUMMESLIMN çox faydalı ola bilər.
3. Təhsil. Bu gün bu sahədən daha praktik nümunələr verəcəyik. Xüsusilə, siz tələbə qiymətlərinin xülasəsini əldə etmək üçün istifadə edə bilərsiniz. Siz tək fənn və ya fərdi qiymətlər üçün seçim edə bilərsiniz. Bir şəxs dərhal qiymətləndirmənin seçiləcəyi bir neçə meyar təyin edə bilər ki, bu da çox rahatdır və çox vaxta qənaət edə bilər.

Gördüyünüz kimi, bu funksiyanın tətbiq dairəsi çox genişdir. Ancaq bu, onun yeganə üstünlüyü deyil. Bu xüsusiyyətin daha bir neçə faydasına nəzər salaq:

1. Çoxsaylı kriteriyalar təyin etmək bacarığı. Niyə bu üstünlükdür? Normal funksiyadan istifadə edə bilərsiniz SUMMESLI! Və hamısı rahat olduğu üçün. Meyarların hər biri üçün ayrıca hesablamalar aparmağa ehtiyac yoxdur. Bütün hərəkətlər əvvəlcədən, hətta əvvəllər də proqramlaşdırıla bilər. məlumat cədvəlinin necə formalaşacağı. Bu əla vaxta qənaətdir.
2. Avtomatlaşdırma. Müasir dövr avtomatlaşdırma əsridir. Yalnız işini düzgün avtomatlaşdırmağı bilən adam çox qazana bilər. Buna görə Excel və funksiyanı mənimsəmək bacarığı SUMMESLIMN xüsusilə karyera qurmaq istəyən hər bir insan üçün çox vacibdir. Bir funksiyanı bilmək bir anda bir neçə hərəkəti yerinə yetirməyə imkan verir. Və burada bu xüsusiyyətin növbəti faydasına keçirik.
3. Vaxta qənaət. Sadəcə bir funksiyanın eyni anda bir neçə işi yerinə yetirməsi səbəbindən.
4. Sadəlik. Sintaksis həcminə görə ilk baxışdan kifayət qədər ağır olmasına baxmayaraq, əslində bu funksiyanın məntiqi çox sadədir. Əvvəlcə bir sıra məlumatlar, sonra müəyyən bir şərtə uyğunluq yoxlanılacaq bir sıra dəyərlər seçilir. Və təbii ki, şərtin özü də dəqiqləşdirilməlidir. Və beləliklə bir neçə dəfə. Əslində, bu funksiya yalnız bir məntiqi konstruksiyaya əsaslanır ki, bu da onu məlum olandan daha sadə edir VPR eyni məqsədlər üçün istifadə oluna biləcəyinə baxmayaraq, daha çox sayda meyar nəzərə alınmaqla. 

SUMIFS funksiyasından istifadənin xüsusiyyətləri

Bu funksiyadan istifadə edərkən diqqət etməli olduğunuz bir neçə xüsusiyyət var. Əvvəla, bu funksiya mətn sətirləri və ya nullları olan diapazonlara məhəl qoymur, çünki bu məlumat növləri arifmetik nümunədə bir-birinə əlavə edilə bilməz, yalnız sətirlər kimi birləşdirilir. Bu funksiya bunu edə bilməz. Aşağıdakı şərtlərə də diqqət yetirməlisiniz:

1. Bu cür dəyərlərdən, onlarda olan dəyərləri əlavə etmək üçün xanaların seçilməsi şərtləri kimi istifadə edə bilərsiniz: rəqəmli dəyərlər, boolean ifadələr, hüceyrə istinadları və s. 
2. Mətn, məntiqi ifadələr və ya riyazi işarələr yoxlanılırsa, o zaman belə meyarlar dırnaq işarəsi ilə müəyyən edilir.
3. 255 simvoldan çox olan terminlərdən istifadə etmək olmaz.
4. Joker işarələrdən istifadə edərək dəyərləri seçmək üçün təxmini meyarlardan istifadə etmək mümkündür. Sual işarəsi tək simvolu əvəz etmək üçün istifadə olunur və vurma işarəsi (ulduz işarəsi) bir çox simvolu əvəz etmək üçün lazımdır. 
5. Toplama diapazonunda olan mantiqi dəyərlər növlərinə görə avtomatik olaraq ədədi dəyərlərə çevrilir. Beləliklə, “TRUE” dəyəri birə, “YANLIŞ” isə sıfıra çevrilir. 
6. Əgər #VALUE! xanada xəta görünür, bu o deməkdir ki, vəziyyət və toplama diapazonlarında xanaların sayı fərqlidir. Bu arqumentlərin ölçülərinin eyni olduğundan əmin olmalısınız. 

SUMIFS funksiyasından istifadə nümunələri

Function SUMMESLIMN ilk baxışdan göründüyü qədər mürəkkəb deyil, belə çıxır. Ancaq daha aydınlıq üçün, funksiyadan necə istifadə edə biləcəyinizlə bağlı bəzi praktik nümunələrə baxaq SUMMESLIMN. Bu, mövzunun dərinləşməsini xeyli asanlaşdıracaq.

Şərtlərin cəmlənməsi dinamik diapazonu

Beləliklə, birinci nümunə ilə başlayaq. Tutaq ki, tələbələrin müəyyən bir fənn üzrə kurrikulumun öhdəsindən necə gəldiyi barədə məlumatları özündə əks etdirən cədvəlimiz var. Qiymətlər toplusu var, performans 10 ballıq sistemlə qiymətləndirilir. Tapşırıq soyadı A hərfi ilə başlayan və minimum balı 5 olan tələbələrin imtahan qiymətini tapmaqdır.

Cədvəl belə görünür.

Yuxarıda təsvir olunan meyarlar əsasında ümumi balı hesablamağımız üçün aşağıdakı düsturu tətbiq etməliyik.

Arqumentləri daha ətraflı təsvir edək:

1. C3:C14 bizim toplama diapazonumuzdur. Bizim vəziyyətimizdə vəziyyət diapazonu ilə üst-üstə düşür. Ondan məbləği hesablamaq üçün istifadə olunan ballar seçiləcək, ancaq meyarlarımıza uyğun gələnlər.
2. “>5” bizim birinci şərtimizdir.
3. B3:B14 ikinci kriteriyaya uyğun işlənən ikinci toplama diapazonudur. Biz görürük ki, toplama diapazonu ilə heç bir təsadüf yoxdur. Buradan belə nəticəyə gəlirik ki, toplama diapazonu və şərtin diapazonu eyni ola bilər və ya olmaya da bilər. 
4. “A*” yalnız soyadı A hərfi ilə başlayan tələbələr üçün qiymətlərin seçilməsini təyin edən ikinci diapazondur. Bizim vəziyyətimizdə ulduz işarəsi istənilən sayda simvol deməkdir. 

Hesablamalardan sonra aşağıdakı cədvəli alırıq.

Gördüyünüz kimi, düstur dinamik diapazona və istifadəçi tərəfindən müəyyən edilmiş şərtlərə əsaslanan dəyərləri ümumiləşdirir.

Excel-də şərtə görə seçmə cəmləmə

İndi fərz edək ki, son rüb ərzində hansı malların hansı ölkələrə daşındığı barədə məlumat almaq istəyirik. Bundan sonra, iyul və avqust ayları üçün daşınmalardan əldə edilən ümumi gəliri tapın.

Cədvəlin özü belə görünür. 

Son nəticəni müəyyən etmək üçün bizə belə bir formul lazımdır.

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

Bu düsturla aparılan hesablamalar nəticəsində aşağıdakı nəticəni alırıq.

Diqqət! Yalnız iki meyardan istifadə etsək də, bu düstur olduqca böyük görünür. Məlumat diapazonu eyni olarsa, aşağıda göstərildiyi kimi formulun uzunluğunu əhəmiyyətli dərəcədə azalda bilərsiniz.

SUMIFS bir çox şərtlərdə dəyərləri toplamaq funksiyası

İndi izah etmək üçün başqa bir misal verək. Bu vəziyyətdə cədvəl əvvəlki vəziyyətdə olduğu kimi qalır. 

Aşağıdakı düsturdan istifadə edirik (lakin onu massiv düsturu kimi yazırıq, yəni CTRL + SHIFT + ENTER düymələr kombinasiyası vasitəsilə daxil edirik).

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

Funksiyadan sonra SUMMESLIMN düsturda göstərilən meyarlara (yəni Çin və Gürcüstan ölkələri) əsaslanan dəyərlər massivini cəmləyəcək, nəticədə alınan massiv adi funksiya ilə cəmlənir. SUM, massiv düsturu kimi yazılır.

Şərtlər birdən çox cüt üçün massiv sabiti kimi ötürülübsə, düstur səhv nəticə verəcəkdir.

İndi yekunları ehtiva edən cədvələ nəzər salaq.

Gördüyünüz kimi, biz buna nail olduq. Siz də mütləq uğur qazanacaqsınız. Bu sahədə böyük uğur. Bu, Excel öyrənmək yoluna yenicə qədəm qoyan insanın başa düşə biləcəyi çox sadə bir funksiyadır. Və biz artıq bilirik ki, funksiyası SUMMESLIMN mühasibat uçotundan tutmuş hətta təhsilə qədər istənilən fəaliyyət sahəsində səmərəli olmağa imkan verir. Yuxarıda təsvir olunmayan hər hansı başqa sahədə karyera qursanız belə, bu xüsusiyyət yenə də pul qazanmağınıza kömək edəcək. Ona görə də dəyərlidir.

Ən əsası, vaxta qənaət etməyə imkan verir ki, bu da təəssüf ki, məhdud resursdur. Görünür ki, iki funksiyanı tətbiq etmək üçün bir neçə saniyə var, ancaq çox sayda təkrarlanan əməliyyatları yerinə yetirməli olduğunuzda, bu saniyələr başqa bir şeyə sərf edilə bilən saatları əlavə edir. Odur ki, bu funksiyadan istifadə edərək məşq etməyinizi tövsiyə edirik. Üstəlik, inanılmaz dərəcədə sadədir.