Mətnlərə Əsasən Şərti Toplama – SUMIF(S)

Orta

SUMIF(S) funksiyasının standart xüsusiyyətlərinin və rəqəm (tarix) əsasında istifadəsi haqqında məqalələr yazmışdıq. Bugün isə, SUMIF(S) funksiyasında müxtəlif mətn variantlarına görə əməliyyatların aparılmasına baxacayıq. Əgər bu funksiya haqqında ilkin məlumatlar yoxdursa, aşağıdakı məqalələri oxumağınızı məsləhət görərdim

  1. Şərtə görə Toplama Əməliyyatı – SUMIF və SUMIFS Funksiyaları
  2. İki Tarix Arası və Rəqəm Aralığına görə Toplama
  3. Exceldə Mətn İşarələrinin (Wildcard – *,?,~,=) Istifadəsi

Mətnlərə əsasən SUMIF funksiyasının məntiqi, funksiya daxilində mətn işarələrinin (* ? =) istifadəsi haqqındadır. Yəni, həmin mətn işarələrini funsksiya daxilində istifadə edərək istədiyimiz formada mətn və ya mətn hissələrinə görə toplama əməliyyatlarını icra edəcəyik. Ən çox istifadə edilənlər isə ulduz (*) və sual (?) işarələridir. Bu işarələrin mənası isə bu şəkildədir:

  • Ulduz (*) = istənilən bir və ya bir neçə simvoldan ibarət mətn,
  • Sual (?) = istənilən bir (ədəd) simvol

 

Nümunə 1

Cədvəlimizdə məhsul kodları və onların satış məbləğləri göstərilib. Həmin cədvələ əsasən üç ayrı hesabat hazırlayaraq ulduz (*) işarəsinin üç ayrı vəziyyətdə istifadəsinə baxacayıq.

Ulduz işarəsi axtarılan mətndən sonra göstərilirsə, həmin mətn ilə başlayan istənilən kəlimə deməkdir. Mavi cədvəlimizdə qeyd olunan simvollar (FUR-OFF-TEC) ilə başlayan məhsul kodlarımızın toplamını almışıq. Həmin simvollardan sonra hansı mətnin gələcəyi bizim üçün maraqlı deyil, yetərki kodlar həmin simvollar ilə başlasın. Bu səbəbdən, ulduz işarəsini simvollardan sonra göstərmişik. Misal üçün, “FUR” mətni ilə başlayan kodlarımızı “FUR*” şəklində göstəririk. Funksiya içərisində isə, mətn (və ya xana) ilə ulduz işarəsini birləşdirmək üçün aralarında & (birləşdirmə) simvolunu yazmalıyıq.

=SUMIF(A:A;D2&"*";B:B)

Ulduz işarəsi axtarılan mətnin hər iki tərəfində göstərilirsə, həmin mətn kəlimənin istənilən hissəsində olması deməkdir. Yəni, axtarılan mən kəlimanın əvvəlində, axırında və ya sonunda olmasının fərqi yoxdur. Əgər axtarılan mətn tapılacaqsa, o zaman toplama əməliyyatı icra olunacaq. Yaşıl cədvəldə həmin hesablama göstərilib. Biz burda kodlarımızı (CH, BI, CO), cədvəldəki kəlimələrin istənilən hissəsində (indiki nümunədə orta hissədə) axtarmışıq. Kodlarımızın hər iki tərəfinə ulduz işarəsi yazdıq. Funksiya daxilində & işarəsi ilə bərabər göstərəcəyik.

=SUMIF(A:A;"*"&G2&"*";B:B)

Ulduz işarəsi axtarılan mətnin əvvəlində göstərilirsə, həmin mətn ilə bitən istənilən kəlimə deməkdir. Qırmızı cədvəldə göstərilən kodlar, cədvəldəki məhsul kodlarının sonunda axtarılıb.

=SUMIF(A:A;"*"&J2;B:B)

 

Nümunə 2

Şəkildə yenə də satış kodlarımız və məbləğlərimiz göstərilib. Burdakı toplama şərtimiz is:

Məhsul Kodu “S” hərfi ilə başlayan və tərkibində “MS” mətnləri olan kodlarımızın toplam satışları nə qədərdir?

Burda, məhsul kodunun “S1” və ya “S2” olmasının fərqi yoxdur. Bu səbəbdən, “S” hərfindən sonra gələn rəqəmləri nəzərə almayacayıq. Bu kimi hallarda, “?” işarəsindən istəfadə edirik. “?” işarəsi istənilən bir ədəd simvol deməkdir. “MS” mətnindən sonra isə “*” işarəsi istifadə edərək həmin mətnlərdən sonra gələn simvolların fərqi olmadığını göstərəcəyik. Axtarış kriteriyamız belə olacaq: “S?.MS*”. Həmin məntiqi funksiya daxilində göstərməliyik

=SUMIF(A:A;"S?.MS*";B:B)

Eyni məntiq aşağıdakı kimi də yazıla bilər

=SUMIFS(B:B;A:A;"S*";A:A;"*MS*")

Bu nümunədə SUMIFS funksiyasından istifadə edərək həmin şərti iki ayrı kriteriyada göstərdik. Burdakı məntiq isə, məhsul kodu eyni zamanda “S” ilə başlasın və tərkibində “MS” mətni olsun şəklində qurulub. Ancaq burdakı şərt, “SXXXXXMS” kimi kodu da nəzərə alır. Əlimizdəki datalardan əmin olduqdan sonra, əgər axtarışdan kənarlaşma ehtimalı yoxdursa fərqli variantlardan istifadə oluna bilər.

Mətnlərimiz əgər xanalarda göstərilibsə, o zaman referanslarımızı xanalara verərək funksiyamızı quracayıq. Misal üçün, “S” mətni C1 xanasında, “MS” mətni isə “C2” xansında isə funksiyamız belə olacaq

=SUMIF(A:A;C1&"?."&C2&"*";B:B)

"və ya"

=SUMIFS(B:B;A:A;C1&"*";A:A;"*"&C2&"*")

Əgər hərf sayısına görə şərti toplama almaq istəyirsinizsə, funksiyada hər bir simvol üçün bir ədəd “?” işarəsi istifadə edə bilərsiniz.

=SUMIF(A:A;"?????";B:B)

Bu funksiya, “A:A” diapazonunda beş kəlimədən ibarət bütün kəlimələri “B:B” sütunundakı rəqəmlərə əsasən toplayır.

 

Mətn işarələrinin düzgün istifadəsi

Tələb olunan şərtləri verərkən mətn işarələri düzgün kodlaşdırılmazsa, funksiya səhf nəticə verəcəkdir. Bu kimi hallarda həmin səhvi görməmiz mümkün olmur.

Fərz edin ki, şəkildəki kimi göstərilən müştəri kodlarına əsasən şərti toplama vermək istəyirik və bizim şərtimiz isə, üçüncü hərfi “1” rəqəmi olan kodlardır. Bu şərtin funksiyasını qurarkən, biz əgər “*1*” kimi kodlaşdırsaq səhf nəticə alacayıq. Çünki bu, “1” rəqəmini kodun istənilən hissəsində axtarır. İkinci sütunda da, həmin kodlaşdırmanın hansı sətrlərə aid edildiyi “1” rəqəmi ilə göstərilib. Düzgün olan isə, üçüncü sütunda göstərilən “??1*” kodlaşdırmasıdır. Burda, “1” rəqəminin dəqiq olaraq üçüncü simvol olduğu göstərilib.

Digər bir yoxlama metodu isə, cədvəlinizə filter tətbiq edərək şərtə uyğun olan sətrlərin toplamını manual olaraq yoxlamaqdır.

Toplamaq istədiyimiz mətnlərin əgər ortaq xüsusiyyətləri varsa, o zaman onlardan istifadə edib əməliyyatlarımızı asanlaşdıra bilərik. Bu nümunədə isə, nöqtədən sonrakı rəqəmlərə əsasən toplama almaq istəyirik. Həmin rəqəmlər məhsul kodlarının dördüncü simvoludur. Funksiyamız bu şəkildə yazıla bilər “???1*“. Ancaq, iki nöqtə arasında rəqəmlərimizi göstərərək funksiyamızı daha da asanlaşdıra bilirik. Burda isə biz, “*.1.*” kodlaşdırmasından istifadə etdik. Funksiya, “.1.” simvollarını kodun istənilən hissəsində axtarır. Əlimizdəki kodların qurulumundan əmin olduğumuza görə alternativ həll yollarından da istifadə edə bilirik.

=SUMIF(F:F;"*.1.*";G:G)

Rəqəm (1) əgər xanada göstərilibsə və biz xanaya referans verəcəyiksə & işarəsindən istifadə edəcəyik.

=SUMIF(F:F;"*."&J2&".*";G:G)

 

 

Orta
Sıra sayılarının funksiya ilə düzgün hesablanması

Hesabatlarımızı hazırlayarkən bir çoxumuzun ilk sütunu sıra sayıları olur. Ancaq tez-tez sətir silərkən və ya yeni məlumatlar əlavə edərkən sıra sayılarımızı yeniləməli oluruq. Bu məqalədə göstərilən metodla artıq sıra sayılarımızı yeniləməyə ehtiyac qalmır. Hər yeni məlumat əlavə edildikdə və silindikdə funksiya rəqəmləri yeniləyir. Bu məsələ Facebook qrupunda müsabiqə sualı kimi …

COUNTA
Siyahıların Funksiya ilə bərabər Hissələrə Bölünməsi

Düşünün ki, minlərcə sətrlərdə ibarət siyahınız var və həmin siyahını bir neçə hissəyə bölüb ya iş bölgüsü ya da başqa bir əməliyyat edəcəksiniz. Ancaq manual olaraq həmin siyahını bərabər hissələrə bölmək vaxt alacaq. Bunu sadə funskiyalarla həll etmək mümkündür. Əvvəlcə Facebookda yayımlanmış bu sualı görüntülü olaraq izləyək. Siyahının neçə hissəyə …

Başlanğıc
Şərtə Əsasən Sayım – COUNTIF

COUNTIF funksiyası, seçilən diapazonda qeyd olunan şərtə (şərtlərə) uyğun gələn xanaları sayır. Əvvəlki məqalələrdə SUMIF haqqında yazılanların hamısı COUNTIF funksiyasına aid edilə bilər. Sözügedən məqalələr aşağıdakı kimidir: Şərtə görə Toplama Əməliyyatı – SUMIF və SUMIFS Funksiyaları İki Tarix Arası və Rəqəm Aralığına görə Toplama (SUMIF) Mətnlərə Əsasən Şərti Toplama – SUMIF(S) …