Şərtə görə MAX və MIN dəyərlərin tapılması – OFFSET ilə (+Video)

COUNTIF

Düşünün ki, şəkildəki kimi “Bolgələr və Şəhərlər” üzrə satış rəqəmləriniz var və siz hər bir regionda ən az (MIN) və ən çox (MAX) satış rəqəmlərini əldə etmək istəyirsiniz.

Biz istəyirik ki, şəkildəki cədvələ əsasən əgər “Bakı Abşeron Bölgəsi” seçərsəm bizə ən az satış olan 600 (1-ci ay üçün) və ən çox satış olan 3100 rəqəmlərini gətrisin. Eyni şəkildə, digər bölgələrdə hər birisi üçün hesablama aparaq. Məsələnin həllini əvvəlcə 1-ci ay üçün sonra isə bölgə və ay asılıqları üzrə həll etməyə çalışacayıq. Yəni, “Qərb Bölgəsi – 3-cü ayda” ən çox və ən az satışlar nəqədərdir?

İlk həll yolumuz OFFSET funksiyası ilə olacaq. Bu funksiyanın düzgün işləməsi üçün “Bölgə” sütunu düzgün sıralanmış olmalıdır. Çünki, bu funsiya ardıcıl xanalar üzrə işləyir.

Şərtə əsasən MAX və MIN rəqəmlərinin tapılması

İlk əvvəl ay nömrəsi üçün asılılıq verilməyəcək. Yəni, təkcə 1-ci ay üzrə rəqəmlər tapılacaq.

"MIN rəqəm tapılması"
=MIN(OFFSET(A1;MATCH(I4;A2:A31;0);2;COUNTIF(A:A;I4);1))

"MAX rəqəm tapılması"
=MAX(OFFSET(A1;MATCH(I4;A2:A31;0);2;COUNTIF(A:A;I4);1))

Burdakı əsas məqsəd, bölgə seçimindən asılı olaraq onun qarşısındakı rəqəmləri əldə etməkdir. Daha sonra isə, əldə olunan rəqəmləri MIN və ya MAX funksiyası içərisində istifadə edib istədiyimiz rəqəmin əldə edəcəyik. Bu səbəbdən, əvvəlcə OFFSET funksiyası ilə başladıq.

1-ci arg (başlanğıc xana), funksiyamızı “A1” xanasından başladırıq.

2-ci arq (sətrlər), neçə sətr aşağı enəcəyini soruşur. Bu isə seçilən bölgənin sıra sayıdır. Misal üçün, “Bakı” 1-ci sətrdən, “Aran” isə 5-ci sətrdən başlayır. Bu şəkildə rəqəmlərimiz hardan başlayacağını tapırıq. Bunun üçün isə MATCH funksiyası istifadə etdik.

=MATCH(I4;A2:A31;0)           "Nəticə: 1"

3-cü arg (sütunlar), rəqəmlərimiz əldə etmək üçün 2 sütun sağa hərəkət etməliyik. Çünki bizə 1-ci ayın rəqəmləri lazımdır

4-cü arg (hündürlük), bizə seçilən bölgənin qarşısındakı rəqəmlər lazım olduğu üçün, həmin bölgənin sayısını tapmalıyıq. Bu səbəbdən COUNTIF istofadə olundu

=COUNTIF(A:A;I4)           "Nəticə: 4"

5-ci arg (genişlik) – rəqəmlərimizi saədəcə bir sütuna əsasən ələ aldığımız üçün bu argumentdə 1 rəqəmini yazırıq.

Yekunda OFFSET funksiyası aşağıdakı rəqəmləri əldə edəcək. OFFSET funksiyasını yazdıqdan sonra “F9” klikləyərək nəticəni görə bilərik. (Ancaq “Enter” klikləsək #VALUE! xətası olacaq çünki funksiyanın bir neçə nəticəsi var, xanalar isə sadəcə bir nəticə qəbul edir)

{3100;600;1400;2000}

Artıq əldə olunan rəqəmlərin ən kiçik və ən böyüyünü tapmalıyıq. Bunun üçün isə, OFFSET funksiyasını MAX və MIN funksiyaları daxilində istifadə edirik.

Bölgə və ay seçimlərinə əsasən MAX və MIN rəqəmlərinin tapılması

Burda isə, OFFSET funksiyasının üçüncü arqumentində olan “2” rəqəmi əvəzinə MATCH funksiyası yazacayıq. Bu şəkildə ay seçimini xanada asılı edəcəyik.

Tam funksiya bu şəkildədir

=MIN(OFFSET(A1;MATCH(J7;A2:A31;0);MATCH(J8;B1:G1;0);COUNTIF(A:A;J7);1))

=MAX(OFFSET(A1;MATCH(J7;A2:A31;0);MATCH(J8;B1:G1;0);COUNTIF(A:A;J7);1))

Məqalədə istifadə olunan funksiyalar haqqında oxumaq üçün

  1. OFFSET Funksiyası nədir?
  2. OFFSET ilə İki Sütunlu Cədvəllərdə Məlumatların Gətirilməsi
  3. Statistik Funksiyalar – MAX(A), MIN(A), SMALL, LARGE

Array Formulalar
Hesablanmış dəyərə əsasən LOOKUP verilməsi – Array (+Video)

Biz normal şərtlərdə LOOKUP verərkən sadə məntiqlə axtarılan dəyərin qarşısındakı (və ya hərhansı tərəfindəki) məlumatları əldə etmək istəyirik. Bəs müəyyən şərtlərə əsasən hesablanmış rəqəmlərin qarşısındakı məlumatları necə gətirmək olar? Bugünkü məqalədə bucür hesablamaların əsasında əldə edilən dəyərlərə görə necə LOOKUP veriləbiləcəyinə baxacayıq. Nümunə olaraq aşağıdakı cədvələ nəzər yetirək: Nümunədə hər hansı …

Array Formulalar
Şərtə görə MAX və MIN dəyərlərin tapılması – Array ilə (+Video)

Bir əvvəlki məqaləmizdə OFFSET ilə şərtə əsasən MAX və MIN rəqəmlərin tapılmasını gördük. İndi isə eyni nəticəyə Array metodu ilə gələcəyik. Array istifadəsinin OFFSET-ə nəzərən üstünlükləri: OFFSET funksiyasında şərtlərin olduğu sütun ardıcıl olmalıydı. Ancaq array istifadəsində buna ehtiyac yoxdur Şərtlərimizin sayı 2 və daha çox olarsa OFFSET ilə bunu həll …

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) …