Exceldə bir çox funksiyalar var ki, onların əvəzləyicisi yoxdur və istifadə yerinə görə əvəzsizdir. Ancaq bir funskiya var ki, bir çox funskiyanın əvəzinə istifadə olunabilər və digər oxşar funksiyalar ilə mümkün olmayan əməliyyatları hesablaya bilər. Eyni zamanda istəyinizə əsasən daxilində fərqli funksiyalar işlədərək sadədən mürəkkəbə qədər bir çox həll yolları yarada bilərsiniz.

Bugün, Exceldəki ən güclü və istifadə olunduğu zaman bir çox problemlərimizi həll edəcək SUMPRODUCT funksiyası haqqında yazacam. Bu funskiyanın ən üstün cəhətlərindən birisi çox elastik olmasıdır. Yəni, bir çox funskiya əgər olmamış olsa, SUMPRODUCT funksiyasını onların əvəzinə rahatlıqla istifadə oluna bilər. Misal üçün, SUMIF(S), COUNTIF(S), AVERAGEIF(S) və s. Ayrıca, bu funskya bəzi hallarda “Array” funskiyaları əvəzinə də istifadə olunur. Ancaq, üstünlükləri ilə bərabər bəzi mənfi yönləri də var:

1. Digər funksiyalardakı kimi (SUMIF, COUNTIF) bütün sütunu seçmək olmur. Ancaq, Excelin “Table” xüsusiyyətindən istifadə edib bu çatışmazlığı nisbətən də olsa aradan qaldırmaq olar.
2. Hesablama müddəti digər funksiyalara nəzərən yavaşdır. Ancaq, cədvəllərimiz çox böyük olduğu zaman aradakı fərq hiss olunur.

Təcrübəmə əsasən deyə bilərəm ki, qeyd edilən iki çatışmazlıqdan başqa bu funskiyanın mənfi cəhətlərini görməmişəm.  İndi isə, funksiyanın ən sadə şəkildə necə istifadə olunur ona baxaq.

SUMPRODUCT, müvafiq diapazon və ya siyahını bir-birinə vuraraq onların toplamını gətirir. Funksiyanın bir ədəd məcburi, 2-dən 255-ə qədər isə məcburi olmayan arqumentləri var.

Nümunədə məhsul adları, satış miqdarı, vahidin qiyməti və endirim faizləri verilib. İlk əvvəl biz əgər endirimsiz toplam satış məbləğini əldə etmək istıyiriksə funksiyanı bu şəkildə istifadə edəcəyik

=SUMPRODUCT(B2:B7;C2:C7)      Nəticə: 3380

Burda funksiya, hər iki diapazondakı müvafiq rəqəmləri bir-birinə vuraraq hər birisini topladı. Yəni, funksiya öz daxilində 5*260 + 1*350 +3*150+4*170+2*210+1*180 əməliyyatlarını tək-tək hesablayaraq nəticələrini topladı. Biz funksiyada, hər bir diapazonu ayri arqumentlərdə yazmayaraq tək arqument daxilində bir-birilərinə vuraraq toplada bilərik.

=SUMPRODUCT(B2:B7*C2:C7)

şəkildəki kimi diapazonları seçib “F9” klikləsək {1300;350;450;680;420;180} nəticələrini əldə edirik. Biz burda, funksiyanın ayrı ayrılıqda diapazonlar üzərində əməliyyat aparması əvəzinə özümüz vurma əməliyyatını icra edib SUMPRODUCT ilə toplatdıq.

Arqumentlərin sayısını artırdığımız zaman funksiya hər bir arqumentdəki siyahını yenə də bir birinə vurub toplayır. Üstdəki nümunədə biz endirim ilə bərabər toplam satış məbləğini almaq istəsək funksiyamızı bu şəkildə yazacayıq

=SUMPRODUCT(B2:B7;C2:C7;1-D2:D7)                 Nəticə: 2837
"və ya"
=SUMPRODUCT(B2:B7*C2:C7*(1-D2:D7))               Nəticə: 2837

İkinci nümunədə 1-D2:D7 əməliyyatını mötərizə içərisində yazamasaydıq səhf nəticə alacaqdıq.

 

Funksiyada diqqət ediləsi məqamlar

Arqumentdəki diapazonlar

Arqumentdəki diapazonlar ya eyni uzunluqda olmalı, ya da paralel getməlidir.

Şəkildəki birinci nümunədə diapazonlar eyni uzunluqda deyil. Bu kimi hallarda funksiya #VALUE! xətası verəcəkdir.

İkinci nümunədə isə eyni uzunluqda olmasına baxmayaraq diapazonlar paralel seçilmiyib. Əslində diapazonların bu şəkildə seçilməsi birinci nümunədən daha təhlükəlidir çünki, birincisində biz nəticə ala bilmirik və səhf elədiyimizi anlayırıq. İkincisində isə funksiya bizə nəticə verir amma əldə olunan nəticə səhfdir. Əgər fikir verilməzsə səhf elədiyimizi anlamaya bilərik. Burda biz “63” nəticəsini alırıq halbuki düzgün cavab “68”-dir.

Diapazonlarda mətnlərin istifadəsi

Əgər seçilən diapazonlarda mətnlər varsa funksiya həmin mətnləri “0” kimi qəbul edir.

=SUMPRODUCT(A1:A4;B1:B4)      Nəticə:44

Qeyd olunan nümunədə funksiya mətnləri “0” kimi hesabladığı üçün (2*10+4*0+3*8+1*0) toplama əməliyyatını icra edib bizə “44” nəticəsini gətirdi.

Əgər biz SUMPRODUCT funksiyasının içərisində mətnlər ilə riyazi əməliyyat aparsaq o zaman #VALUE! xətası alacayıq.

=SUMPRODUCT(A1:A4*B1:B4)              Nəticə: #VALUE!

Xətalar haqqında yazılan məqalədə qeyd etmişdik ki, mətn və rəqəm məlumatlarının birgə əməliyyatı nəticəsində biz #VALUE! xətası alırıq. Burda da eyni vəziyyət yaşanır. Belə ki, funksiyada arqumenti seçib “F9” ilə incələsək {20;#VALUE!;24;#VALUE!} nəticəsini görəcəyik. Mətnlər ilə olan sətrlərdə xəta aldığımız üçün funksiya toplama əməliyyatını da xəta kimi verdi.

 

SUMPRODUCT funksiyasında şərtlərin verilməsi

Məqalənin əvvəlində SUMPRODUCT funksiyasının elastik olmasını qeyd etmişdik. Ancaq indiyə qədər yazılan nümunələrdə hər hansı bir elastiklik yoxdur, sadəcə olaraq funksiya daxilində olan diapazonları bir-birlərinə vurub toplatdıq.

Funksiyanın əsas xüsusiyyəti, daxilində müxtəlif funksiyaları istifadə edərək istədiyimiz şərtləri tanıtdırıb bizə lazım olan rəqəmləri əldə etmə imkanı verməsidir. Bugünki məqalədə bu haqda ətraflı yazmaq mümkün olmayacaq ancaq, ən sadə şəkliylə funksiyanın SUMIF və COUNTIF əvəzinə necə istifadə olunduğuna baxacayıq. Burdakı əsas məntiq, hesablamaya daxil etmək istədiyimiz şərtləri “1” rəqəmi ilə, daxil etmək istəmədiyimiz şərtləri isə “0” rəqəmi ilə əvəz edərək toplama əməliyyatı zamanı onları “0” və “1” lərə vuraraq istədiyimiz nəticələri almaqdır. Nümunələr ilə daha aydın olacaq

SUMIF əvəzi istifadə

Əvvəlcə, “Samsung” modelinin anbarda toplam neçə ədəd olduğunu tapaq

=SUMPRODUCT(--(A2:A6="Samsung");E2:E6)               Nəticə: 26

burda biz birinci arqumentdə “A2:A6” diapazonunun “Samsung” kəliməsinə bərabər olub olmadığını yoxladıq

=SUMPRODUCT(--({TRUE;TRUE;FALSE;FALSE;TRUE});E2:E6)

Hansı sətr “Samsung” kəliməsinə bərabərdirsə “TRUE”, bərabər deyilsə “FALSE” nəticəsini verdi. Ancaq, TRUE-FALSE rəqəm olmadığına görə və funksiya bunları tanımıyacağına görə funksiya “0” nəticəsini verəcəkdi. Biz əgər Boolean məlumatları (TRUE-FALSE) üzərində riyazə əməliyyat aparsaq, TRUE –  “1” rəqəminə, FALSE isə “0” rəqəminə çevriləcək. Bu səbədən iki ədəd (–) mənfi işarəsi istifadə etdik. Yəni, bir mənfi işarəsi TRUE məlumatını “-1” ə, digəri isə yenidən müsbət “1”- ə çevirdi. Burda “–” işarələrinin istifadəsi şərt deyil. Nəticəni dəyişməmək şərti ilə istədiyimiz riyazi əməliyyat apara bilərik. Misal üçün, “+0”, “*1” və s.

=SUMPRODUCT({1;1;0;0;1};{10;4;9;8;12})

Daxildə funksiya bu şəkildə hesablanıb. “Samsung” kəliməsi olan sətrlər “1” ilə göstərilərək qarşısındakı rəqəmə vurulub və toplanıb.

Funksiya bu şəkildə də yazıla bilərdi (Daxildə, tək arqument içərisində vurma əməliyyatı ilə)

=SUMPRODUCT((A2:A6="Samsung")*E2:E6)                   Nəticə: 26

Fikir verdinizsə, burda “–” işarəsi istifadə etmədik çünki, onsuzda vurma əməliyyatı aparılır və bu zaman Boolean məlumatları özləri rəqəmə çevriləcək. Həmin hissəni “F9” ilə incələsək aşağıdakı hesablamanı görərik

=SUMPRODUCT({10;4;0;0;12})

İndi isə, “Samsung” markalı məhsulların anbarda toplam AZN olaraq dəyərini tapaq

=SUMPRODUCT(--(A2:A6="Samsung");D2:D6;E2:E6)              Nəticə: 29174
"və ya"
=SUMPRODUCT((A2:A6="Samsung")*D2:D6*E2:E6)

Biz bu əməliyyatı SUMIF ilə etmək istəsəydik, köməkçi sütun yaradıb onun üzəridə hesablama aparmalıydıq. Çünki, SUMIF öz içərisində bu cür hesablamaya icazə vermir.

COUNTIF əvəzi istifadə

İndi isə, “Xiaomi” markalı neçə fərqli məhsulumuzun olduğunu tapaq.

=SUMPRODUCT(--(A2:A6="Xiaomi"))         Nəticə: 2

“Xiaomi” mətninin toplam sayısını tapmaqla biz, bu modeldə neçə fərqli telefonumuzun olduğunu tapdıq.

Qiyməti 1000 AZN-dən yuxarı olan neçə “Xiaomi” telefonumuz var?

=SUMPRODUCT(--(A2:A6="Xiaomi");--(D2:D6>1000))
"və ya"
=SUMPRODUCT((A2:A6="Xiaomi")*(D2:D6>1000))

Hər bir arqumenti “F9” ilə incələyək

=SUMPRODUCT({0;0;1;1;0};{1;1;1;0;0})

Burda hər bir arqument öz şərtlərinə görə “0-1” nəticəsini verdi. Daha sonra funksiya hər birisini müvafiq rəqəmlərə vurdu. Yəni, 0*1+0*1+1*1+1*0+0*0 hesablaması icra olundu. Hər iki şərtin yerinə yetirilməsi üçün hər birisinin “1” olması lazımdır, əks halda “0” ilə vurma əməliyyatı ilə “0” nəticəsi verəcək.

Məqaləni, iki ayri video şəklində Youtube kanalından izləyə bilərsiniz