Kəpənək diaqramlar – tornado diaqramlar kimi Y oxunun hər iki tərəfində sətir diaqramları olan xüsusi əmək tələb edən diaqram növləridir. Bu yazıda, bu diaqramların necə qurulduğunu praktik misal üzərində müzakirə edəcəyik. Yazı müəyyən qədər uzun sayıla bilər, lakin, bir-birindən fərqli bir çox yeni texnika ilə də tanış etməkdədir.
Öncəliklə qeyd edim ki, yuxarıdakı dinamik, dropdown siyahılı diaqramı addımbaaddım quracağıq, lakin, bunu etməkdə istəkli olmayanlar, yekun halı ilə Excel faylını məqalənin ən sonundakı keçiddən təmənnasız endirə bilərlər.
Diaqramı çox effektiv edən – klub adlarını dəyişdiyimiz anda, onlara aid statistikanın da anında əks olunmasıdır. Gəlin, çox uzatmadan diaqramın hazırlanma mərhələlərinə keçək.
1. Input datanın hazırlanması
İlkin mərhələdə, analiz aparılacaq 10 klub (əlifba sırası ilə – Barcelona, Bayern, Dortmund, Juventus, Liverpool, Man. United, Milan, Porto, Paris Saint-Germain və Real Madrid) və bu klubların yerli və beynəlxalq arenada qazandıqları kubokları müəyyən etmək durur. Bu kuboklar isə:
- Milli çempionat (League)
- Ölkə kuboku (National Cup)
- Ölkə superkuboku (National Super Cup)
- Çempionlar Liqası (Champions League)
- Avropa Liqası (Europa League)
- Kuboklar Kuboku (Cup Winners’ Cup)
- Klublararası dünya çempionatı (FIFA World Cup)
- Avropa Superkuboku (European Supercup)
Bu siyahıya əlavə olaraq isə, bu klublarda çıxış edərkən qazanılan Qızıl Topların (Ballon D’or) sayı yer almaqdadır.
Cədvəlin ən gözəl tərəfi odur ki, Club sütunundakı dəyərləri işçilərin / yeni məhsulların / investisiya layihələrinin adları ilə dəyişdikdən sonra, onlara müvafiq göstəriciləri kubok adları olan sütun başlıqlarında da əvəz edərək, diaqramın kontekstini tamamilə dəyişmək mümkündür.
Gördüymüz kimi, müəyyən texniki və praktiki səbəblərə görə, klub adları sətirlərdə, kubok adları isə sütunlarda yer almaqdadır. Bu cədvəli transpose edərək yeni bir cədvələ dönüşdürməliyik.
2. Cədvəlin Transpose edilməsi
Yeni cədvəlimizin formatı aşağıdakı kimi olmalıdır:
Fikir verirsinizsə, cədvələ bir çox sütun əlavə edilmişdir. Gəlin, onların hansı funksiyanı yerinə yetirdiklərinə baxaq.
Ümumiyyətlə, bu faylı update etmək üçün, yuxarıdakı hər iki cədvəldə sarı olan xanaları və ilk cədvəldə kubok adları olan sütun adlarını yeniləmək kifayət edəcəkdir. Boz fonlu bütün xanalar formullardır.
3. Köməkçi (Helper) sütunların əlavə edilməsi
Transpose edilən cədvəlimizdən də görürük ki, seçilən 2 klubdan əlavə, 3 yeni köməkçi sütun da əlavə edilmişdir. Bu köməkçi sütunlar bir neçə addım sonra quracağımız sətir diaqramlarında istifadə ediləcəkdir. Diaqramı qurduqdan sonra, bu sütunlardakı formulların məntiqinə geri dönəcəyik.
4. Klublara aid 2 sütunun qurulması
Yekun diaqramımızda müqayisə ediləcək dəyərlər, quracağımız bu 2 sütun əsasında formalaşacaqdır. Bu sütunlarda istifadə edilən formullar SUMPRODUCT funksiyasının möcüzəvi texnikasından istifadə edilməklə qurulmuşdur. O səbəblə, bu mövzuya toxunmuram, daha ətraflı, qeyd edilən keçiddən tanış ola bilərsiniz.
5. Label sütunlarının qurulması
Label sütunları daha çox diaqramlardakı sətirlərin üzərində olan dəyərlərin əks edilməsi zaman köməyimizə çatacaqdır, çünki bəzi sətirlər çox qısa və fon ağ olduğu üçün, 3-dən kiçik kubok saylarının diaqramlarda görünməməsi məqsədini daşıyır.
Formulun 2 komponenti var:
- D18-dəki dəyərin 3-dən böyük və ya bərabər olduğunu yoxlayır. Nəticə doğrudursa, TRUE (riyazi olaraq 1), əks halda FALSE (riyazi olaraq 0) nəticəsini verir.
- Bu nəticəni həmin xanadakı dəyərə vuraraq, ya həmin dəyəri ya da sıfır gətirir.
Beləliklə də, 3-dən kiçik olan heç bir dəyər diaqramımızda əks olunmamış olur.
6. Diaqramın qurulması
Kubok adları ola sütundan başlayaraq Helper Column 3 də daxil olmaqla bütün diapazon seçilərək Insert >> 100% Stacked Bar Chart seçilir.
Qarşımıza çox qarışıq, çirkin bir diaqram çıxacaq.
7. Diaqramın formatlanması
Yuxarıdakı diaqramı formatlamaq mütləqdir, çünki lazımsız bir çox məlumat yer almaqdadır. Aşağıdakı şəkildə ilk addımlarımızı atırıq:
Sağ və solda yer alan göy rəngli Helper 1 və Helper 3 sütunlarında ola dəyərləri “görünməz” edirik. Bunun üçün göy seriyalar aktivləşdirildikdən sonra, Format >> Shape Fill >> No Fill seçilir.
Görürük ki, Helper sütunlar sadəcə müəyyən ölçüləri saxlamağa yaramaqla bərabər, gizlədilərək də sanki orda deyillərmiş kimi illüziya yaradırlar.
8. Kubok adlarının ortaya gətirilməsi
Bunun üçün də çox sadə, lakin hiyləgər bir texnikadan istifadə edirik. Ortadakı boz seriyaların üzərinə gəlib, sağ klikləyib Add Data Labels edirik.
Daha sonra həmin rəqəmlərdən (Label) hər hansı birinin üzərinə klikləyib Ctrl+1 ilə sağ tərəfdən Formatlama Blokunu açırıq.
Value və Show Leader Lines-ın qarşısında olan check-ləri yığışdırıb, Value From Cells-i aktivləşdirib Select Range düyməsini kliklədikdən sonra, kubokların adları olan siyahıya istinad veririk.
9. Yekun formatlamalar
Yekun formatlamalarımızı da verdikdən sonra (hər birinin nə olduğunu faylı endirdikdən sonra daha rahat görəcəksiniz), yekun məhsula bir addım qala, diaqramımız bu halını alacaqdır:
Lakin bu diaqram yekun deyil, çünki əks olunan göstərcilir statikdir, Barcelona və Real Madridə aid göstəricilərdir. Biz bunu dinamik hala gətirməli, klubları dəyişdikcə bu göstəricilərin dəyişməsinə nail olmalıyıq.
10. Dropdown siyahılar və yekun toxunuşlar
9-cu mərhələdəki problemi aradan qaldırmaq üçün isə, diaqramın hər iki yanına 2 fərqli (lakin eyni diapazona istinad verən) dropdown siyahı əlavə edirik. İstinad olaraq isə, Input cədvəlimizdəki klub adlarını göstəririk.
Dinamik olması baxımından, klubların adları adlandırılmış siyahı (Named ranges) olaraq qeyd edilmişdir.
İşimiz bununla da bitmir. Son mərhələ olaraq, transpose edilən cədvəlimizdə 2 kluba aid sütunlar var idi, xatırlayırsınız. O klub adları da dropdown siyahı olan xanalardan istinad alır. Ki sonrasında avtomatik hesablamaları apara bilsin.
YEKUN
Bütün bu addımları atdıqdan sonra, dinamik diaqramımız artıq qarşımızdadır. Artıq, istənilən klubu, istənilən klubla müqayisə edə bilərik. Faylın ən gözəl tərəfi isə odur ki, Input cədvəlindəki dəyərləri istədiyiniz kimi dəyişib, məsələn, işçilərin siyahısını əlavə edib, onlara aid fərqli KPI göstəricilərini müqayisə edə bilərsiniz.
Faylı endir: Futbol Klublarının Analizi