Sətir və sütunların kəsişməsindəki dəyərlərin tapılması

Cədvəlimizə diqqət yetirək. KLM şirkətinin ilk yarımillik satışları olan cədvəli görürük.

SUMPRODUCT magic - 1

Bu cədvəl hər hansı bir hesabatlıq baxımından çox da məna ifadə etmir. Bəli, bütün göstəricilər göz qabağındandır, lakin bunların hər biri rəhbərlik üçün maraqlı olmaq məcburiyyətində deyil. Onlar müəyyən məhsulların müəyyən aylardakı satış dəyərlərini görmək istəyə bilər. Bunun üçün onlara bütn cədvəl əvəzinə, özlərinin seçimlər edəcəyi sadə bir forma verməliyik.

Çox sadə olması baxımından aşağıdakı kimi bir cədvəl hazırlaya bilərik.

SUMPRODUCT magic - 2

Data Validation vasitəsilə, məhsul adlarını və ayları siyahı halında müvafiq olaraq ProductMonth xanalarına daxil etdikdən sonra, əsas məsələ həmin məhsulun həmin aydakı satış məbləğinin formul vasitəsilə müəyyənləşdirilməsi olacaqdır.

Bunun üçün, qəliz olmaqla bərabər, çox faydalı bir texnikadan istifadə edəcəyik. SUMPRODUCT funksiyası ilə bu kiçik möcüzəni həyata keçirəcəyik. Ümumiyyətlə, SUMPRODUCT funksiyası normal işləyişindən savayı, bir çox problemin praktik həllində də əvəzedilməz bir vasitədir.

Gəlin, yazacağımız formula nəzər yetirək. Öncə formulu yazıb, daha sonra açıqlamasını verəcəyəm. Hesab edək ki, Tablets məhsulunun Mart ayına olan satış dəyərini bilmək istəyirik.

SUMPRODUCT magic - 3

Formul 3 komponentdən əmələ gəlir.

  1. B3:B7=C11 – Excel-ə deyirik ki, B3:B7 aralığına bax və ordan C11 xanasındakı dəyəri tap. Excel deyiləni edir və B4 xanasında durur.
  2. C2:H2=C12 – Excel-ə ikinci göstərişi verərək deyirik ki, C2:H2 aralığındakı C12 xanasındakı dəyəri tap. Excel bu dəfə E4 xanasında durur.
  3. C3:H7 – Formulun son komponenti ilə isə bütün dəyərlər olan aralıq seçilir və 1 və 2-ci komponentlərdəki sətir və sütunların kəsişməsində yer alan dəyər tapılır.

Diqqət etdinizsə, hər 3 komponent bir-birinə vurulmalıdır. Bu, SUMPRODUCT funksiyasının xüsusi istifadə üsullarından biridir, o səbəbdən, formulu müəyyən ölçüdə əzbərləmək lazım gələ bilər. Digər tərəfdən, formulun sadə və bəsit məntiqini yaxşı qavrasaq, o halda heç əzbərləmək də lazım olmayacaqdır.

Artıq, istənilən qədər ProductMonth xanalarını dəyişərək nəticənin avtomatik yeniləndiyini müşahidə edə bilərik.

capture-1

Son olaraq bir şeyi də qeyd edim ki, eyni əməliyyat INDEXMATCH funksiyalarının birgə istifadəsi zamanı da həyata keçirilə bilər, lakin SUMPRODUCT ilə bu, çox daha rahat tətbiq edilir.

Əlavədəki faylı endirib formulu daha yaxından analiz edə bilərsiniz.

Uğurlar!

Faylı endir – SUMPRODUCT magic

Advertisements

2 thoughts on “Sətir və sütunların kəsişməsindəki dəyərlərin tapılması

  1. Məqalə üçün təşəkkürlər. Əla yazı alınıb. Mənə lazım olan vaxtda paylaşılması lap yerinə düşdü. Kalkulyator üçün hazırladığım formulanı asanlaşdırdı.
    [=IF(Q4>E2;IF(A4>Q2;IF(Q3=B3;B4;IF(Q3=C3;C4;IF(Q3=D3;D4;IF(Q3=E3;E4;IF(Q3=F3;F4;IF(Q3=G3;G4;IF(Q3=H3;H4;IF(Q3=I3;I4;IF(Q3=J3;J4;IF(Q3=Q3;K3;0))))))))));IF(Q3=B3;B5;IF(Q3=C3;C5;IF(Q3=D3;D5;IF(Q3=E3;E5;IF(Q3=F3;F5;IF(Q3=G3;G5;IF(Q3=H3;H5;IF(Q3=I3;I5;IF(Q3=J3;J5;IF(Q3=K3;K5;0)))))))))));IF(A4>Q2;IF(Q3=B3;B6;IF(Q3=C3;C6;IF(Q3=D3;D6;IF(Q3=E3;E6;IF(Q3=F3;F6;IF(Q3=G3;G6;IF(Q3=H3;H6;IF(Q3=I3;I6;IF(Q3=J3;J6;IF(Q3=K3;K6;0))))))))));IF(Q3=B3;B7;IF(Q3=C3;C7;IF(Q3=D3;D7;IF(Q3=E3;E7;IF(Q3=F3;F7;IF(Q3=G3;G7;IF(Q3=H3;H7;IF(Q3=I3;I7;IF(Q3=J3;J7;IF(Q3=K3;K7;0))))))))))))*Q2] funksiyasını [=SUMPRODUCT((A4:A7=IF(Q4>E2;IF(Q2>A4;A5;A4);IF(Q2<A4;A6;A7)))*(B3:K3=Q3)*B4:K7)*Q2] ilə əvəz etdim.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s