Şərtə görə MAX və MIN dəyərlərin tapılması – OFFSET ilə (+Video)
- By : Elnur Isayev
- Category : COUNTIF, İləri, MATCH, MAX, MIN, OFFSET
- Tags: Şərtə əsasən MAX və MIN hesablaması
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
- OFFSET Funksiyası nədir?
- OFFSET ilə İki Sütunlu Cədvəllərdə Məlumatların Gətirilməsi
- Statistik Funksiyalar – MAX(A), MIN(A), SMALL, LARGE