OFFSET Funksiyası nədir? (+Video)

COUNTIF

Düşünün ki, bir dostunuzdan adres soruşursunuz və onun olduğu yerə necə gedəbiləcəyinizi sizə başa salır və deyir ki:

“Olduğun yerdən 5 km sağa, daha sonra isə 3 km sola get. Qarşına çıxacaq iki bloklu və üç mərtəbəli bina bizim binadır”

Əslində OFFSET funksiyası qeyd olunan adresdən fərqli birşey deyil. İnternetdə OFFSET funksiyasını araşdırdığınız zaman qarşınıza “iləri səviyə funksiyalar” kateqoriyasında çıxacaq. Amma məntiqi adres soruşmaqdan daha çətin deyil.

Tərcüməsi “mərkəzdən kənarlaşma” deməkdir. Qeyd olunan referans nöqtəsindən, qeyd olunan sətr və (ya) sütun sayısı uzaqlıqda bir və ya bir neçə xanadan ibarət diapazona referans vermək üçün istifadə olunur. Beş arqumenti var:

=OFFSET(referans;sətrlər;sütunlar;[hündürlük];[genişlik])
=OFFSET(reference;rows;columns;[height];[width])

Referans, başlanğıc nöqtəsini göstərir

Sətrlər, başlanğıc nöqtəsindən kənarlaşmalı sətr sayını

Sütunlar, sətr sayısı ədədincə kənarlaşmadan sonra kənarlaşmalı olan sütun sayısını

Hündürlük, gəlinən nöqtədə neçə sətrdən ibarət xananın olacağını göstərir. Seçimlikdir və qeyd olunmazsa “1” kimi qəbul edir.

Genişlik, gəlinən nöqətədə neçə sütundan ibarət xana olacağını göstərir. Seçimlikdir və qeyd olunmazsa “1” kimi qəbul edir.

Görüntülü nümunə ilə daha aydın olacaq.

 

Nümunəmizdə başlanğıc yeri “D3” göstərilib. Sətr arqumentində “5” rəqəmini yazdıqdan sonra, “D3” başlanğıc hissəsindən (xana özü daxil deyil) 5 sətr kənarlaşdı (yaşıl rəng). Üçüncü arqumentdə “3” yazdıqdan sonra isə, başlanğıc və sətr uzaqlaşmalarını da nəzərə alaraq 3 sütun kənarlaşdı. Hündürlük və genişlik arqumentlərimiz isə məcburi deyil. Əgər yazılmazsa 1 xana kimi nəzərə alacaq. Videoda da həmin hissə qırmızı rəng ilə göstərilib. Əgər qeyd olunarsa o zaman bizim referans ərazimiz bir xandan daha çox olacaq. Hündürluk arqumentinə “5” yazdığımız zaman 5 sətrdən ibarət hissəni götürdü. Genişlik isə qeyd olunacaq sütun sayısını təmsil edir. Həmin arqumentdə “3” yazdığımız zaman seçim hissəsini genişlədərək 3 sütun və 5 sətrdən ibarət ərazıni göstərdi. Hal hazırda funksiya 15 xanadan ibarət ərazini göstərir. Funksiya içərisində bu şəkildə qeyd olunur

=OFFSET(D3;5;3;5;3)

Arqumentlər hissəsi mənfi rəqəmləri də qəbul edir. Əgər arqumentlər mənfi ilə qeyd olunarsa sətr hissəsi üstə doğru, sütun hissəsi isə sola doğru referans verəcək. Hündürlük və genişlik arqumentlərində də eyni şəkildə üstə və sola doğru kənarlaşma olacaq.

Şəkildəki nümunədə, başlanğıc xanası “H10” qeyd olunduqdan sonra, 4 sətr üstə doğru və 3 sətr sola doğru kənarlaşma olub. (Genişlik və hündürlük arqumentləri son xanadan başladığı üçün sütun kənarlaşması 2 kimi görsənir, üçüncü xana qırmızı ərazinin daxilindədir).

Hündürlük və genişlik arqumentlərinin hər birisi 2 xana göstərilib, üstə və sola doğru genişlənmişdir.

 

İstəfadəsi və Xüsusiyyətləri

Dəyişkən ərazilərə refarans vermək üçün istifadə olunur. Misal üçün, son bir həftənin ortalaması və s.

Digər funksiyalar daxilində istifadəsi daha yayğındır

Tək xana referans verildiyi zaman həmin ərazidəki dəyəri, 2 və daha çox ərazi referans verildiyi zaman isə (əgər başqa funksiya daxilində istifadə olunmuyubsa) #VALUE! xətası verir

Əgər referans verilən hissə səhifənin sərhədlərini keçirsə  #REF!  xətası verəcəkdir.

Excel faylında hər hansı dəyişiklik olarsa yenidən hesablayır, bu səbəbdən çox istifadə olunduğunda fayllarda yavaşlamaya səbəb olur

INDEX, SUMPRODUCT kimi funksiyalar bəzi hallarda alternativ kimi istifadə olunur

 

Nümunə

“E4:F15” diapazonunda verilen satış rəqəmlərində regionlar üzrə şərti toplama əməliyyatı OFFSET funksiyası ilə həll etməyə çalışacayıq. Ancaq burda bizim başlanğıc yerimiz “C2” xanasıdır. Yəni, “C2” xanasından başlayaraq funksiyanın düzgün rəqəmlərə necə referans veriləcək ona baxacayıq.

 

“C2” xanasından başlayacağımız üçün ilk arqumentdə həmin xananı seçtik. İkinci arqumentdə isə “A11” xanasında olan Region adını tapmaq üçün MATCH funksiyasından istifadə etdik. MATCH funksiyası nəticəsindən 2 rəqəminin çıxılma səbəbi isə, başlanğıc xananın ikinci sətrdən başlamasına görədir. Bu şəkildə region adına çatmaq üçün neçə sətr aşağı düşüləcəyini tapdıq. Üçüncü arqumntdə “AZN” sütununa gəlmək üçün “3” rəqəmini daxil etdik. Dördüncü arqumentdə toplanacaq “AZN” məbləğlərinin neçə sətrdən ibarət olduğunu tapmaq lazımdır. Yəni, hər bir regionun qarşısındakı məbləğləri toplamaq istəyiriksə, o zaman həmin regionun təkrarlanma sayısı qədər rəqəmlərimiz olmalıdır. Bu səbəbdən COUNTIF funksiyası istifadə etdik. Beşinci arqumentdə isə bizə sadəcə bir sütun lazım olduöu üçün “1” rəqəmini daxil etdik. Yekunda isə, OFFSET funksiyası ilə əldə etdiyimiz rəqəmləri SUM funksiyası ilə toplayırıq.

=SUM(OFFSET(C2;MATCH(A11;E:E;0)-2;3;COUNTIF(E:E;A11);1))

Qeyd: funksiyanın doğru nəticə alması üçün bu nümunədə region adları düzgün sıralanmış olmalıdır.

Məqalənin videolu izahını Youtube kanalından izləyə bilərsiniz

 

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 …

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

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 …