Array Formulalar – Giriş (+Video)

Array Formulalar

Exceldə müəyyən müddət işlədikdən sonra sizə standard və ya mürəkkəb funksiyalarin yetərli olmadiğını görəcəksiniz. Exceldə formulalarda ustalaşmaq və bir mərhələ də ilərləmək üçün “Array” funksiyaları bilmək şərtdir.

Giriş
Array fomulalara nə vaxt ehtiyac olur?

Standard funksiyalar ilə hesablanması mümkün olmayan əməliyyatlarda

Standard funksiyalarda hesablanması mümkündür ancaq, həmin nəticəyə gəlmək çox çətindir.

Xanalar arasında bütünlük yaratmaq lazım olduğunda.

Üstünlükləri nədir?

Hesablama müddətini azaldır

Faylın həcminin şişməsini azaldır, çünki köməkçi cədvəllərdən daha az istifadə olunur. (Hər bir köməkçi cədvəl əlavə yer tutur faylınızda)

Normalda hesablanması mümkün olmayan əməliyyatlarda yeni metodlar açır.

“Array” funksiyalar hansılardır?

Bu şəkildə funksiya kateqoriyası yoxdur. Bu əslində funskiyaların qurulma şəklidir. Ancaq funksiyalarda “Array” metodu istifadə olunduğunda hamısı eyni şəkildə davranmır. Yəni,

Bəzi funksiyalar var ki, ancaq “Array” kimi işləyir. Misal üçün, TRANSPOSE, FREQUENCY və s

Bəzi funksiyalar tərkibində “Array” qəbul etmir. Misal üçün, SUMIF(S), COUNTIF(S) və s

SUMPRODUCT funksiyası “Array” məntiqi ilə işləyir. Burda “Ctrl+Shift+Enter”-ə ehtiyac olmur.

 

“Array” funksiyaların qurulma məntiqi

Standard funksiyalarda nəticə almaq üçün bizdə həmişə hazır datalar olur. Misal üçün: əgər “A1:A5” xanalarının toplamını istəyiriksə deməli bizim həmin xanalarda rəqəmlərimiz var və biz həmin hazır rəqəmlərə SUM funskiyası verib toplamını alırıq. Ancaq, “Array” funksiyalrında biz hesablama zamanı funskiya daxilində əlimizdə olan datanı bir və ya bir neçə dəfə dəyişdirib istədiyimiz formaya saldıqdan sonra nəticəni əldə edirik.

Nümunə 1

A1:A10 diapazonunda tək rəqəmlərin toplamı neçədir?

Funksiyalar ilə aranız yaxşıdırsa, siz də bilirsiniz ki, “şərtə görə tək rəqəmləri topla” deyə bir funksiya yoxdur. O zaman bu məsələni necə həll edəcəyik?

“Array” funksiyasının köməyi ilə, əlimizdə olan bu rəqəmləri funksiya daxilində istədiyimiz formaya salıb daha sonra toplama edəcəyik

İlk şərtimiz, əldə olan rəqəmlərin tək olub olmadığını müəyyən etməkdir

=MOD(A1:A10;2)

Rəqəmlərimizin tək olub olmamasını tapmaq üçün MOD funksiyasından istifadə etdim. MOD funksiyası, böləni bölünənənə böldükdən sonra geri qalan hissəni verir. Yəni, MOD(5;2) əməliyyatında bölən (2) bölünənənə (5) bölündükdən sonra geriya qalan “1” rəqəmini verəcək. Əgər rəqəmlərimiz təkdirsə, o zaman MOD funksiyası ilə əməliyyat gördüyümüz bütün tək rəqəmlər “1” nəticəsini verəcək. Ayrıca, üstdəki əməliyyatda biz “A1:A10” diapazonunun hamısını seçdik. Həmin funksiyanı “F9” ilə incələdiyimizdə aşağıdakı sıyahını verəcək

={1;0;1;0;1;0;1;1;1;1}

Biz artıq tək olan rəqəmlərimizi müəyyən etdik. Əldə etdiyimiz yeni siyahıya əsasən, tək olan rəqəmlərimiz “1” rəqəmi ilə, olmayanlar isə “0” rəqəmi ilə ifadə olunur. Bu artıq “array” əməliyyatıdır. Xanalarda olan rəqəmlərdən istifadə edərək üzərində əməliyyat gördük və yeni bir siyahı əldə etdik.

İndi isə, əldə etdiyimiz “1-0” siyahısını həmin xanalardakı rəqəmlər ilə vuracayıq

=MOD(A1:A10;2)*A1:A10

Funksiyanı “f9” ilə incələyək

{61;0;47;0;33;0;19;49;11;83}

Vurma əməliyyatı nəticəsində, tək rəqəmlər “1” ilə vurulduğuna görə, cüt rəqəmlər isə “0” ilə vurulduğuna görə bizim siyahımızda təkcə tək rəqəmlər qalmış oldu. Bu isə artıq əldə etdiyimiz ikinci siyahıdır. Bunlar hamısı funksiya daxilində olur. Son mərhələ olaraq, əldə etdiyimiz tək rəqəmləri toplayacayıq.

=SUM(MOD(A1:A10;2)*A1:A10)                                  Nəticə:303

“Array” funksiyalarının qurulumu digər funksiyalardan fərqləndiyi üçün, funksiyaları daxil edərkən, “Enter” əvəzinə “Ctrl+Shift+Enter” klikləməliyik. Bu səbəbdən, “Array” funksiyalarına bəzən “CSE” funksiyaları deyirlər.

Funksiyanı daxil etdikdən sonra, şəkildəki kimi mötərizələr  ({ }) görsənəcək. Bu, funksiyanın “array” olduğunu göstərir. Biz əgər “CSE” əvəzinə təkcə “Enter” klikləsək düzgün nəticə almayacayıq (“61” nəticəsini verəcək, çünki siyahıdakı ilk rəqəm “61”-dir). Həmin mötərizə işarələrini özünüz əlnən yazmaya çalışmayan, “CSE”-dən sonra özləri avtomatik yaranır.

Nümunədə də gördüyünüz kimi, biz köməkçi siyahılardan istifadə etmədən və funksiya daxilində yazılan əməliyyatlar nəticəsində yeni bir siyahı (array) əldə etdik və həmin rəqəmləri toplatdıq. Köməkçi sıyahılardan istifadə etməməklə, faylımızın həcmi böyümədi (böyük cədvəllərdə fərq hiss olunur). Əlavə olaraq, hesablama müddəti daha az oldu. Əslində, “Array” funksiyaların hesablama müddəti digər funksiyalara nəzərən daha yavaşdır. Ancaq, köməkçi sütunlar yaratmış olsaydıq, hər birisində ayrı funksiyalar yazacaqdıq. Həmin köməkçi sütunları da nəzərə alaraq deyə bilərik ki, nəticə etibari ilə “Array” funskiyalar hesablama müddətində bizə üstünlük verir. Digər bir nümunə ilə daha da aydın olacaq.

Nümunə 2

Siyahıda neçə kəlimə böyük hərf ilə başlayır?

Qırmızı hərflər ilə verilən sıyahıda, neçə nəfərin adı böyük hərf ilə başladığını tapmaq üçün gəlin əvvəcə mərhələləri tək-tək incəliyək və bunu standart funksiyalar ilə etməyə çalışaq.

İlk mərhələ olaraq, adların ilk hərflərini əldə edəcəyik, çünki daha sonra ilk hərfin böyük olub-olmadığını müəyyən etməliyik. Funksiyanı “B2” xanasına daxil edib aşağı çəkirik.

=LEFT(A2;1)

İkinci mərhələdə isə, həmin hərflərin böyük hərf qarşılıqlarını əldə etməliyik. Bunun üçün UPPER funksiyasından istifadə edirik çünki, bu funksiya bütün hərfləri böyük hərfə çevirir.

=UPPER(B2)

Üçüncü mərhələdə isə, həmin hərflərin böyük hərf olmasını yoxlayaq. EXACT funskiyası böyük və kiçik hərf arasında fərq qoyduğu üçün həmin hərfləri özlərinin böyük hərfləri ilə qarşılaşdıracaq. “D2” xanasına funksiyanı daxil edib aşağı çəkirik.

=EXACT(B2;C2)

TRUE nəticəsi verənlər böyük hərflə, FALSE nəticəsi verənlər isə kiçik hərflə başlayır. Artıq “D” sütununda, hansı hərflərin böyük hərflə başladığını tapdıq. Son mərhələdə isə, həmin TRUE və FALSE məlumatlarını “1-0” rəqəmlərinə çevirərək toplayacayıq.

=--D2                     "E2 xanasında yazılıb aşağı çəkilir"

=SUM(E2:E7)               "əldə edilən 1-0 rəqəmləri toplanılır"     Nəticə:4

Nümunədə qeyd olunan bütün əməliyyatlar tək bir xanada hesablandığı zaman fayllarımız daha sadə, başa düşülən və sürətli olacaqdır.

=SUM(--EXACT(LEFT(A2:A7;1);UPPER(LEFT(A2:A7;1))))  (Ctrl+Shift+Enter)             Nəticə: 4

 

Xanalarda bütünlük yaradır

Şəkildəki kimi sadə cədvəlinizin olduğunu düşünək və burda biz sadə çıxma əməliyyatı ilə “Büdcə-Fakt” fərqini tapa bilərik. Ancaq, bu hissədə “array” istifadə etsək, seçilən xanaları bütün olaraq ələ alacaq və hər hansı bir xanada dəyişikliyə icazə verməyəcək. Bunun üçün “B2:H2” xanaları seçilir və “=B2-B3” funksiyası əvəzinə “B2:H2-B3:H3” funksiyası daxil edilərək “Ctrl+Shift+Enter” düymələri bir yerdə kliklənir.

İndi isə, funksiya daxil edilən hər hansı bir xananı silməyə çalışın. Excel bizə, şəkildə görsəndiyi kimi xəbərdarlıq verəcək. “Siyahının (array) bir hissəsini dəyişdirə bilməzsən”. Bu şəkildə, funksiya daxil edilən hissələr bir bütün olaraq ələ alınacaq və istənilən məlumat dəyişikliyinə icazə verməyəcək. Yeni sütun əlavə etmək istədiyiniz zaman yenə eyni xəbərdarlığı verəcək. Dəyişiklik etmək üçün bütün sətri (funksiya olan) silib daha sonra istədyiniz əməliyyatları edə bilərsiniz.

 

“Array” olaraq işləyən funksiyalar

Bəzi funksiyalar da vardır ki, sadəcə “array” məntiqi ilə işləyir. Nümunə olaraq TRANSPOSE funksiyasını göstərəcəm.

TRANSPOSE funksiyası, sütunlarda olan məlumatları sətrlər üzrə (və ya tərsi) çevirir. “Kopyala-Yapışdır” menüsündəki “transpose”-dan fərqi odur ki, funksiya ilə daxil edildiyində məlumatın göstərilən diapazonlar ilə əlaqəsi olur və yenilənməsinə ehtiyac olmur.

Funksiyanın arqumentində də görsəndiyi kimi, sadəcə “array” tələb edir. Məlumatları gətirmək istdəyimiz hissəni seçdikdən sonra diapazonu seçirik və “Ctrl+Shift+Enter” klikləyirik. Biz burda da, hər hansı bir xananı dəyişdiriə bilmirik.

Əgər “Array” formulalarından istifadə etmədən məsələlərin həlli mümkündürsə, o zaman standart metodlar məsləhətdir. Ancaq, qeyd olunan səbəblərə görə məsələnin həll ancaq “array” ilə olacaqsa o zaman istifadə oluna bilər. Başlanğıc olaraq çətin gələ bilər amma iləriki tarixlərdə yazılacaq nümunə və məsələlər ilə daha da başadüşülən olacaq.

Comments:

No Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

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 …