Daha rahat avtomatlaşdırma üçün Exceldə bazaları necə qurmalı?

Başlanğıc

Bir çoxumuz Exceli işlərimizi avtomatlışdırmaq üçün istifadə edirik. İstəyirik ki, bir dəfə cədvəlimizi və funksiyalarımızı quraq və daha sonra eyni əziyyəti çəkmiyək. Bu şəkildə, həmin Excel faylında həm hesabatlarımız, həm də arxivlədiyimiz məlumatlar olsun və istənilən vaxt həmin bazadan istifadə edək.

Ancaq, bunu etməyə çalışarkən cədvəllərimiz üçün düzgün struktur yaratmır və bir müddət keçdikdən sonra iş çıxılmaz vəziyyət alır. Belə ki, cədvəllər elə bir vəziyyər alır ki, hesabat almaq çox çətinləşir hətta sadə filter əməliyyatı çox əziyyətli olmağa başlayır.

Bu məqalədə, əvvəlcə ən düzgün bazalama forması göstəriləcək, daha sonra isə, digər cədvəl növlərinin bizlərə nə kimi çətinlik yaratdığı haqqında yazmağa çalışacam. Əslində qeyd olunan metod heç də yeni deyil. SQL Server kimi proqramlarla işləyən proqramistlərin hər zaman istifadə etdiyi cədvəl növüdür. Ancaq Excel istifadəçilərində baza qurmaq seçimi sərbəst olduğu və bazalar haqqında ilkin məlumatlarının olmadığı üçün bu problem ilə tez tez qarşılaşılır.

Tabular Cədvəllər

Bazanın qurulması üçün ən uyğun cədvəl növüdür. Bu cədvəllərdə hər bir məlumat növü ayrı sütunda göstərilir. Bu formatlı cədvəllərdə istənilən məlumat almaq üçün filter tətbiqi və funksiyaların qurulumu daha sadə olur. Nümunə format bu şəkildədir.

Yeni məlumat əlavə olunduğunda cədvəlin son sətrindən sonra əlavə olunduğu üçün funksiyaları da yeniləməyə ehtiyac qalmır. Bu formada olan cədvəllərdən hesabat yaratmaq da daha asan olur. Baza yaradarkən bəzi nüanslara da diqqət yetirmək lazımdır:

  • Hər bir sütuna uyğun ad verilməlidir. Yəni, “başlıq” hissəsi həm boş olmamalı, həm də təmsil etdiyi sətrlərə uyğun olmalıdır. Misas üçün, tarix, market adı və s.
  • Məlumatlar düzgün daxil edilməlidir. Belə ki, hər bir sütun bir növ məlumatı saxladığı üçün həmin sütunda ancaq uyğun datalar daxil edilməlidir. Misal üçün, “tarix” sütununda ancaq tarix məlumatları olmalıdır. Ayrıca, məlumat formatlarına da fikir vermək lazımdır. Rəqəm olan sütuna mətn daxil edilməməlidir. Misal üçün, “AZN” olan sütuna rəqəm yazmaq yerinə “100 AZN” şəklində mətn daxil edilməməlidir.
  • Sətrlər və sütunlar arasında boşluq olması məsləhət deyil. Yeni məlumatlar ən son sətrdən (sütundan) sonra daxil edilərək bütünlük təşkil olunmaldır. Bu vəziyyət xüsusən filter (süzgəç) və sort (sıralama) əməliyyatlarında problem yarada bilər.
“Table” cədvəl istifadəsi

Cədvəlləri “Table” içərisində istifadə edərək əməliyyatları daha da sadələşdirmək olur. “Table” haqqında daha ətralı bu məqalələrdən oxuya bilərsiniz

  1. Exceldə Cədvəl (Table) İstifadəsinin Üstünlükləri
  2. “Cədvəl”lərdə (Table) Funksiya İstifadəsi

Bəs bazaların cədvəl içərisinə alınmasının hansı üstünlükləri var?

  • Sütuna format verdiyinizdə yeni məlumatlara da tətbiq edir. Misal üçün, “Formatlama” hissəsində “Tarix” sütununu istəyinizə görə formatladınız və yeni məlumat əlavə olunduğunda formatlama onlara da tətbiq olunur.
  • Sütun adının boş olmasını icazə vermir.
  • Bazada bütünlük yaradır.
  • Hesablanmış sütunlarda funksiya yeni sətrlərə də tətbiq olunur. (Daha ətraflı məqalələrdən oxuya bilərsiniz)
Orijinal dataya toxunmamaq

Fərz edin ki, hər ay ERP sistemindən məlumatları çəkib Excelə əlavə edirsiniz və onun əsasında hesabat yaradırsınız. Ancaq çəkilən məlumatlarda tarix formatı Excelin tarix formatına uyğun gəlmir. Bu səfər həmin məlumatı Excelin başa düşəbiləcəyi formata salıb daha sonra hesablama aparmaq lazımdır. Əgər original tarix məlumatlarını dəyişdirsəniz hər dəfə eyni əziyyəti çəkməli olacaqsanız. Ancaq orijinal dataya toxunmayıb “Cədvəl” içərisində əlavə hesablama sütunu yaratsanız yeni məlumatlar əlavə iş tələb etməyəcək.

Şəkildə də görsəndiyi kimi, yeni məlumat üçün əlavə heç bir əməliyyat görmədik. Məlumat əlavə olunduqdan sonra artıq hesabatımızı yeni yaratdığımız “Yeni Tarix” sütununa əsasən verəcəyik.

“Hesabat” formalı cədvəllər

“Hesabat” formalı cədvəllərə nümunə olaraq aşağıdakı cədvəli göstərmək olar

Bu növ cədvəllər baza yaratmaq üçün uyğun deyil. Bunlar əsasən “tabular” cədvəl əsasında yaradılmış hesabatlardır və müəyyən məqsəd (şərt) üçün hesablanmış toplam rəqəmləri göstərir. Uyğun olmamasının ən əsas səbəbləri:

  • Bir məlumat növü bir neçə sütunda göstərilib. Misal üçün, şəkildəki cədvəldə satış rəqəmləri altı ayrı sütunda göstərilib.
  • Bu cədvəllərdən ətraflı məlumat almaq olmur. Misal üçün, şəkildəki “Hesabat” formalı cədvəldə sadəcə məhsul və şəhər məlumatları əsasında satış rəqəmləri var. Ancaq üstdəki cədvəldə əlavə olaraq tarix və market adları var. Real bazalarda isə məlumat çeşiti daha çox olur.
  • “Tabular” cədvəllərə əsasən bu cədvəllərdə funksiyalar daha mürəkkəb olur.

Funksiyaların mürəkkəbliyi haqqında qarşılaşdırmaya nümunə olaraq aşağıdakı hesablamanı göstərə bilərik. Biz burda, iki dəyişkənə əsasən (Məhsul və Şəhər) toplam satış rəqəmini əldə etməyə çalışacayıq.

Hərəkətli şəkildə də görsəndiyi kimi, eyni nəticəyə gəlmək üçün “Hesabat” formalı cədvəldə daha qəliz funksiya qurmalı olduq. Funksiyalar bu şəkildədir.

=SUMIF(A:A;I3;INDEX(B:G;0;MATCH(I4;B1:G1;0)))
=SUMIFS(E:E;C:C;G2;D:D;G3)

Qeyd olunan funksiyalar haqqında daha ətraflı bu məqalələrdən oxuya bilərsiniz.

 

Bazaların ayrı səhifələrdə qurulması

Ayrı səhifələrdə bazaların qurulması dedikdə, eyni növ cədvəlləri ayıraraq ayrı-ayrı səhifələrə arxivlənməsi nəzərdə tutulur. Bu növ cədvəllərində baza olaraq istifadəsi uyğun deyil. Tamami ilə ayrı təyinatlı cədvəllərin ayrı səhifələrdə yaradılması normal haldır.

Ayrı səhifələrdə qurulan cədvəllərin “tabular” və ya “hesabat” formasında olmasının fərqi yoxdur. Əsas məqsəd səhifələr arasında olan cədvəllərin bir-birlərindən aralı (qopuq) olmasının yaratmış olduğu çətinliyə diqqət çəkməkdir.

“Tabular” cədvəldə yeni məlumat əlavə edərkən eyni səhifədə cədvəlin altına əlavə edirik. Ancaq burda yeni məlumatları ayrı səhifə yaradaraq boş səhifəyə əlavə edilir.

Bir çox zaman, şəkildəki kimi aylara bölünmüş fərqli səhifələrlə baza yaratmağa çalışılır. Bu növ bazanın idarə edilməsi digər iki cədvələ görə daha çətindir. Burda funksiya qurulumu çox daha çətin olur. Çünki, funksiyaların yaradılma məqsədi əsasən sütunlar üzərindən olur. Burda isə biz ya INDIRECT ya da 3D kimi iləri səviyyə funksiyalar istifadə etməliyik ki, bunu da hər kəs istifadə edə bilmir. Ayrıca bu funksiyaların elastikliyi çox azdır. Hər növ hesabatın yaradılmasında istifadə etmək olmur. Ən yaxşı ehtimalda iç-içə IF funksiyası istifadə ediləcək ki, bu da effetktiv yol deyil.

Power Query

Bu məqamda Power Query haqqında qısa məlumat yerinə düşərdi. Power Query, Excelin 2013 versiyasında əlavə add-in kimi, 2016 versiyasında isə proqramın bir hissəsi kimi təqdim etdiyi əlavədir. Power Query-nin köməkliyi ilə, ayrı səhifələrdə və fayllarda olan məlumatları birləşdirib bir cədvəl halına salmaq çox rahat olur. Hətta, “Hesabat” formalı cədvəlləri “Unpivot” tətbiq edərək “Tabular” formaya salmaq olur. Digər bir deyimlə, bu proqram məlumatları birləşdirib yekunda “Tabular” formaya salır. Power Query haqqında İnşallah gələcək tarixlərdə məqalələrimiz olacaq.

Orta
Sıra sayılarının funksiya ilə düzgün hesablanması

Hesabatlarımızı hazırlayarkən bir çoxumuzun ilk sütunu sıra sayıları olur. Ancaq tez-tez sətir silərkən və ya yeni məlumatlar əlavə edərkən sıra sayılarımızı yeniləməli oluruq. Bu məqalədə göstərilən metodla artıq sıra sayılarımızı yeniləməyə ehtiyac qalmır. Hər yeni məlumat əlavə edildikdə və silindikdə funksiya rəqəmləri yeniləyir. Bu məsələ Facebook qrupunda müsabiqə sualı kimi …

Başlanğıc
“Cədvəl”lərdə (Table) Funksiya İstifadəsi

Cədvəllərdə funksiyanın istifadəsi iki şəkildə ələ alınacaq: cədvəl içində funksiya istifadəsi və kənardan yazılan funksiyalarda cədvələ referans verilməsi. Amma funksiya istifadəsinə keçməmişdən qabaq cədvəlin hissələri (elementləri) haqqında qısa məlumat vermək düzgün olardı. Cədvəlimizi yaradan kimi “Table Tools” adında yeni menümüz yaranır. Excel avtomatik olaraq cədvəllərimizə “Table1,2…..” şəklində adlar təyin edir. …

Başlanğıc
Exceldə Cədvəl (Table) İstifadəsinin Üstünlükləri

Excelin 2007 versiyasından sonra “Cədvəl” (Table) xüsusiyyətini təqdim etdi. “Cədvəl”-lərin köməkliyi ilə məlumatlarımız strukturlaşdırılır, qruplaşdırılır və onları analız etmək daha asan olur. İstifadəsi və yaradılması çox asan olmaqla bərabər, əməliyyatlarda çox üstünlüklər verir. Bəzi üstünlükləri: Yeni məlumatlar daxil edildikçə “Cədvəl” genişliyir və yaradılan funksiya, pivot və qrafiklərdə diapazonları yeniləməyə ehtiyac qalmır. Funksiya …