Pivot cədvəlləri hər kəs üçün yaxşıdır - onlar tez hesablayırlar və çevik şəkildə konfiqurasiya edilirlər və tələb olunarsa dizayn onlara zərif şəkildə daxil edilə bilər. Amma məlhəmdə bir neçə milçək də var, xüsusən də bir xülasə yaratmaq mümkün deyil, burada dəyər sahəsi nömrələri deyil, mətni ehtiva etməlidir.
Gəlin bu məhdudiyyəti aşmağa çalışaq və oxşar vəziyyətdə “bir neçə qoltuqağacı” tapaq.
Tutaq ki, şirkətimiz məhsullarını konteynerlərdə Ölkəmizin və Qazaxıstanın bir neçə şəhərlərinə daşıyır. Konteynerlər ayda bir dəfədən çox olmayaraq göndərilir. Hər bir konteynerin alfasayısal nömrəsi var. İlkin məlumat olaraq, hər bir şəhərə və hər aya göndərilən konteynerlərin sayını aydın görmək üçün bir növ xülasə etməli olduğunuz çatdırılmaların siyahısını göstərən standart bir cədvəl var:
Rahatlıq üçün əmrdən istifadə edərək əvvəlcədən ilkin məlumatları olan cədvəli "ağıllı" edək Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format) və ona bir ad verin Təchizat nişanı konstruktor (Dizayn). Gələcəkdə bu, həyatı asanlaşdıracaq, çünki. cədvəlin adından və onun sütunlarından birbaşa düsturlarda istifadə etmək mümkün olacaq.
Metod 1. Ən asanı – Power Query istifadə edin
Power Query Excel-də məlumatların yüklənməsi və dəyişdirilməsi üçün super güclü vasitədir. Bu əlavə 2016-cı ildən etibarən defolt olaraq Excel-də qurulub. Əgər sizdə Excel 2010 və ya 2013 varsa, onu ayrıca yükləyə və quraşdıra bilərsiniz (tamamilə pulsuz).
Bütün prosesi, aydınlıq üçün, aşağıdakı videoda addım-addım təhlil etdim:
Power Query-dən istifadə etmək mümkün deyilsə, başqa yollarla - pivot cədvəli və ya düsturlar vasitəsilə gedə bilərsiniz.
Metod 2. Köməkçi xülasə
Orijinal cədvəlimizə daha bir sütun əlavə edək, burada sadə düsturdan istifadə edərək cədvəldəki hər bir sətirin sayını hesablayırıq:
Aydındır ki, -1 lazımdır, çünki cədvəlimizdə bir sətirli başlıq var. Cədvəliniz vərəqin əvvəlində deyilsə, cari cərgənin və cədvəl başlığının nömrələri arasındakı fərqi hesablayan bir az daha mürəkkəb, lakin universal düsturdan istifadə edə bilərsiniz:
İndi standart bir şəkildə məlumatlarımıza əsaslanaraq istədiyiniz tipdə bir pivot cədvəli quracağıq, lakin dəyər sahəsində sahəni buraxacağıq. Sətir nömrəsi istədiyimizin əvəzinə konteyner:
Eyni şəhərdə eyni ayda bir neçə konteynerimiz olmadığı üçün xülasəmiz əslində bizə lazım olan qabların miqdarını deyil, sətir nömrələrini verəcək.
Əlavə olaraq, tabda ümumi və ara cəmiləri söndürə bilərsiniz Konstruktor - Ümumi yekunlar и Arxivlər (Dizayn - Ümumi Toplamlar, Ara Cəmlər) və eyni yerdə düymə ilə xülasəni daha rahat masa tərtibatına keçirin Hesabat maketi (Hesabat Düzeni).
Beləliklə, biz artıq nəticənin yarısındayıq: şəhərin və ayın kəsişməsində, mənbə cədvəlində bir sıra nömrəsinin olduğu, bizə lazım olan konteyner kodunun olduğu bir cədvəlimiz var.
İndi xülasəni kopyalayaq (eyni vərəqə və ya digərinə) və onu dəyərlər kimi yapışdıraq və sonra düsturumuzu dəyər sahəsinə daxil edin, bu, xülasədə tapılan sətir nömrəsi ilə konteyner kodunu çıxaracaq:
Function IF (Əgər), bu halda, xülasədəki növbəti xananın boş olmadığını yoxlayır. Boşdursa, boş mətn sətrini "" çıxarın, yəni xananı boş qoyun. Boş deyilsə, sütundan çıxarın Konteyner mənbə cədvəli Təchizat funksiyadan istifadə edərək sətir nömrəsinə görə hüceyrə məzmunu İNDEKSİ (İNDEKS).
Bəlkə də burada çox aydın olmayan yeganə məqam qoşa sözdür Konteyner düsturda. Belə qəribə bir yazı forması:
Təchizat[[Konteyner]:[Konteyner]]
… yalnız sütuna istinad etmək lazımdır Konteyner mütləq idi (adi “ağıllı olmayan” cədvəllər üçün $ işarələri olan arayış kimi) və düsturumuzu sağa köçürərkən qonşu sütunlara sürüşmürdü.
Gələcəkdə, mənbə cədvəlindəki məlumatları dəyişdirərkən Təchizat, biz köməkçi xülasəmizi sətir nömrələri ilə yeniləməyi unutmamalıyıq, bunun üzərinə sağ klikləyərək və əmri seçməklə Yeniləyin və Saxlayın (Təzələmək).
Metod 3. Düsturlar
Bu üsul aralıq pivot cədvəlinin yaradılmasını və əl ilə yenilənməsini tələb etmir, lakin Excel-in “ağır silahı” funksiyasından istifadə edir. SUMMESLIMN (SUMIFS). Xülasədə sıra nömrələrini axtarmaq əvəzinə, bu düsturdan istifadə edərək onları hesablaya bilərsiniz:
Bəzi xarici həcmliliklə, əslində bu, seçmə toplama funksiyası üçün standart istifadə halıdır SUMMESLIMNVerilmiş şəhər və ay üçün sıra nömrələrini cəmləyən A. Yenə eyni şəhərdə eyni ayda bir neçə konteynerimiz olmadığına görə, funksiyamız əslində məbləği deyil, sətir nömrəsini özü verəcəkdir. Və sonra əvvəlki metoddan artıq tanış olan funksiya İNDEKSİ Siz həmçinin konteyner kodlarını çıxara bilərsiniz:
Əlbəttə ki, bu halda, artıq xülasəni yeniləmək barədə düşünmək lazım deyil, lakin böyük cədvəllərdə funksiya SUMMESLI nəzərəçarpacaq dərəcədə yavaş ola bilər. Sonra düsturların avtomatik yenilənməsini söndürməli və ya birinci üsuldan - pivot cədvəlindən istifadə etməli olacaqsınız.
Xülasə görünüşü hesabatınız üçün çox uyğun deyilsə, ondan sətir nömrələrini bizim etdiyimiz kimi birbaşa deyil, funksiyadan istifadə edərək yekun cədvələ çıxara bilərsiniz. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). Bunu necə etmək olar burada tapa bilərsiniz.
- Pivot cədvəlindən istifadə edərək hesabatı necə yaratmaq olar
- Pivot cədvəllərində hesablamaları necə qurmaq olar
- SUMIFS, COUNTIFS və s. ilə seçmə hesablama.