Subtotal funksiyası aralıq cəmləri göstərən bir funksiyadır. Belə ki, əgər siz Sum (Cəm) funksiyasından istifadə edib filter versəniz və sonra xanalardan hansısa birini gizlətsəniz, yenə əvvəlki dəyəri göstərəcək. Məsələni daha da sadələşdirmək üçün misallara baxaq.
Fərz edək ki, 3 adda (kitab, karandaş, telefon) məhsul və saylarından ibarət bir cədvəl verilmişdir. Bu cədvəlin yekununda Sum (Cəm) və Subtotal (Alt Cəm) funksiyasından istifadə etməklə onların cəmini tapaq.
Yuxarıdakı şəkildə B14 xanasına =SUBTOTAL(9; B2:B10) düsturu yazılmışdır. Sual verə bilərsiniz ki, düsturun birinci hissəsində olan 9 rəqəmi nəyi ifadə edir. Şəkildən göründüyü kimi funksiyanın sintaksisini yazarkən açılan pəncərədə çıxan təkliflərdə bir neçə seçim verilmişdir (Bu rəqəmləri özündə əks etdirən cədvəl aşağıda qeyd hissəsində verilmişdir).
Seçilən xanaların ədədi ortasını tapmaq üçün Average (Ortalama) düsturundan istifadə edilərsə 1 seçilməlidir. Yuxarıdakı misalda biz cəmi tapdığımızdan 9 rəqəmindən istifadə etmişik. Düsturda “; ” işarəsindən sonra qeyd edilən B2:B10 isə hansı xanaları cəmlədiyimizi ifadə edir.
Yuxarıda biz Subtotal (Alt Cəm) funksiyasından necə istifadə edə biləcəyimiz barədə məlumat verdik. İndi isə keçək bu funksiyadan istifadə etməyimizin bizə hansı üstunlükləri verəcəyi haqda danışmağa. Bu funksiya vasitəsilə:
- Xanalar filter edildiyi zaman göstərilən xanaların dəyərlərinin Subtotal (Alt Cəm) düsturu vasitəsilə cəmlənməsi mümkündür. Əgər bizim misalda “Kitab” məhsulunu seçsək, SUM (Cəm) düsturu ilə hesabladığımız xananın dəyəri dəyişməyəcək, lakin Subtotal (Alt Cəm) düsturu ilə yazdığımız xananın dəyəri dəyişəcək. Yəni Subtotal (Alt Cəm) funksiyası ilə ancaq “Kitab” adlı məhsulunuzun yekun cəmi görünəcək. Sum (Cəm) funksiyası ilə hesablanan xanada isə bütün məhsulların cəmi görünəcək.
- Gizlədilən xananın dəyərini nəzərə almadan cəmləmək mümkündür. Bizim misalda biz bir sətiri gizlədəriksə, bu xananın dəyəri düsturda nəzərə alınmayacaq. Aşağıda şəkildə 6-cı sətir gizlədilmişdir. Bu xananın dəyəri (5) diqqətə alınmamışdır. Gizlədilən xananın dəyərinin diqqətə alınmaması üçün düsturda 9 rəqəmi əvəzinə 109 yazmaq lazımdır. Bundan əlavə digər düsturlar üçün də analoji qayda tətbiq edilir (məs. gizlədilən xanalardan başqa digər xanaların ədədi ortasını tapmaq üçün 1 rəqəmi əvəzinə 101 yazılır).
Yuxarıdakı nümunədə B14 xanasına =SUBTOTAL(109; B2:B10) funksiyası yazılmışdır. Diqqət etsək görərik ki, gizlədilən 6-cı sətirdə qeyd edilən ədəd cəmləmə zamanı nəzərə alınmamışdır.
- Dinamik olaraq məlumatları cəmləmək mümkündür. Yuxarıda göstərildiyi kimi məlumatları dəyişməklə məlumatların avtomatik cəmlənməsi baş verəcək.
- Subtotal (Alt cəm) funksiyası daha əvvəl bu funksiyanın özü ilə cəmlənmiş alt cəmləri diqqətə almır.
Bizim misalda hər məhsulun cəmi və yekun cəm yazılmışdır. Əgər diqqət etsəniz görərsiniz ki, Sum (Cəm) funksiyası Subtotal (Alt Cəm) ilə cəmlənmiş xanaların (B5, B10, B13) dəyərini nəzərə almış, Subtotal (Alt Cəm) funksiyası isə nəzərə almamışdır.
- Subtotal (Alt cəmləri) hesablayarkən bu işləri daha asan yerinə yetirmək üçün DATA (Verilənlər) menyusunda yerləşən Subtotal (Alt Cəm) alt menyusundan istifadə edə bilərsiniz.
Qeyd: Aşağıda Subtotal (Alt Cəm) funksiyası zamanı istifadə edilən düsturların rəqəm qarşılığını özündə əks etdirən cədvəl göstərilmişdir.
AGGREGATE (Yekun) funksiyası
Biz əvvəlki hissələrdə SUM, AVERAGE, SUBTOTAL, MAX, MIN və s. digər statistik və riyazi funksiyalardan istifadə etdik. AGGREGATE funksiyasını onların universal forması hesab edə bilərik. Bu funksiya vasitəsilə, seçilmiş xanalar üzrə müəyyən xüsusi şərtlərə görə yekun nəticə əldə etmək mümkündür.
Sintaksisi=AGGREGATE(funksiya; xüsusi şərt; xanalar) şəklindədir.
Funksiyanın qəbul etdiyi birinci parametr vasitəsilə yekun nəticənin hansı funksiya əsasında alınacağı müəyyən edilir.
İkinci parametrdə isə yekun nəticə hesablanarkən aşağıdakı xüsusi şərtlər funksiyaya daxil edilə bilər.
- İç-içə yazılmış SUBTOTAL və AGGREGATE funksiyalarını nəzərə alma
- Gizlədilmiş sətirləri, iç-içə yazılmış SUBTOTAL və AGGREGATE funksiyalarını nəzərə alma
- Xəta olan dəyərləri, iç-içə yazılmış SUBTOTAL və AGGREGATE funksiyalarını nəzərə alma
- Gizlədilmiş sətirləri, xəta olan dəyərləri, iç-içə yazılmış SUBTOTAL və AGGREGATE funksiyalarını nəzərə alma
- Heç nəyi nəzərə alma
- Gizlədilmiş sətirləri nəzərə alma
- Xətalı dəyərləri nəzərə alma
- Gizlədilmiş sətirləri və xətalı dəyərləri nəzərə alma
Yuxarıda qeyd edilənləri aşağıdakı nümunə üzərində izah edək:
Bu cədvəldə C5 xanasına =SUBTOTAL(9; C2:C4) funksiyası, C11 xanasına isə =AGGREGATE(9; 6; C6:C10) funksiyası yazılmışdır. Bundan əlavə, 7-ci sətir gizlədilmişdir. Bizə lazımdır ki, C12 xanasında bütün bu halları nəzərə alaraq bu xanaların cəmini hesablayaq.
Əgər cəmləmə işini ənənəvi olaraq SUM (Cəm) funksiyası ilə aparsaq, aşağıdakı kimi nəticə əldə ediləcəkdir:
Gördüyünüz kimi, SUM (Cəm) funksiyası ilə bu işi yerinə yetirdikdə, funksiya xəta qaytardı.
Baxaq görək, SUBTOTAL funksiyası ilə hansı nəticəni əldə edəcəyik.
Gördüyünüz kimi, bu funksiya da NA xətasını nəticə olaraq geri qaytardı. Demək ki, yuxarıda göstərdiyimiz 3 xüsusi şərtin üçünü də SUM (Cəm) və SUBTOTAL (Alt cəm) funksiyaları eyni anda nəzərə ala bilmədilər. Bu işi AGGREGATE (Yekun) funksiyası ilə görsək, aşağıdakı kimi nəticə əldə edəcəyik:
Deməli, C12 xanasına yazılan AGGREGATE (Yekun) funksiyası həm xəta olan dəyərləri, həm SUBTOTAL (Alt Cəm), həm AGGREGATE (Yekun) funksiyasının özünü, həm də gizlədilmiş sətirləri nəzərə alaraq hesablama aparmış oldu.
Xatırladaq ki, bu məqalə INNAB-ın məhsulu olan “55 Dərsə Excel” kitabından götürülmüşdür. Kitabda bu kimi praktiki həllərlə yanaşı Excelin bütün əmr və menyuları, 222 funksiyası izah edilmişdir. Aşağıdakı linkdən qeydiyyatdan keçməklə kitabı ünvanınızda əldə edə bilərsiniz. Kitabın qiyməti 20 AZN-dir. Mövzuların daha da yaxşı başa düşülməsi üçün kitabın üzərində video disk də hədiyyə edilir. Bundan əlavə kitabı əldə edən oxucular innabCell–in imkanlarından faydalanma imkanı verilir.
Ətraflı məlumat üçün 050 290 6121, 012 594 01 94 nömrəsi ilə əlaqə saxlaya bilərsiniz.
