Metod 1. Düsturlar
Başlayaq, isinmək üçün, ən sadə variant - düsturlar. Giriş kimi tarixə görə çeşidlənmiş kiçik bir cədvəlimiz varsa, çalışan cəmini ayrı bir sütunda hesablamaq üçün bizə elementar düstur lazımdır:
Burada əsas xüsusiyyət SUM funksiyası daxilində diapazonun çətin fiksasiyasıdır – diapazonun əvvəlinə istinad mütləq (dollar işarələri ilə), sonuna isə nisbi (dollar olmadan) edilir. Müvafiq olaraq, düsturu bütün sütuna köçürərkən, cəmini hesabladığımız genişlənən bir sıra alırıq.
Bu yanaşmanın mənfi cəhətləri açıqdır:
- Cədvəl tarixə görə sıralanmalıdır.
- Məlumatlarla yeni sətirlər əlavə edərkən, düstur əl ilə uzadılmalı olacaq.
Metod 2. Pivot cədvəli
Bu üsul bir az daha mürəkkəbdir, lakin çox daha xoşdur. Və daha da kəskinləşdirmək üçün daha ciddi bir problemi nəzərdən keçirək - 2000 sıra məlumat cədvəli, burada tarix sütununa görə çeşidləmə yoxdur, lakin təkrarlar var (yəni eyni gündə bir neçə dəfə sata bilərik):
Orijinal cədvəlimizi "ağıllı" (dinamik) klaviatura qısayoluna çeviririk Ctrl+T və ya komanda Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format), və sonra əmri ilə onun üzərində pivot cədvəli qururuq Daxil et - Pivot Cədvəl (Daxil et - Pivot Cədvəl). Xülasədəki sətirlər sahəsinə tarixi və qiymətlər sahəsində satılan malların sayını qoyuruq:
Nəzərə alın ki, əgər sizdə Excel-in çox köhnə versiyası yoxdursa, o zaman tarixlər avtomatik olaraq illər, rüblər və aylar üzrə qruplaşdırılır. Fərqli qruplaşdırmaya ehtiyacınız varsa (və ya ümumiyyətlə ehtiyacınız yoxdursa), istənilən tarixə sağ klik edərək və əmrləri seçməklə onu düzəldə bilərsiniz. Qrup / Qrupdan çıxarın (Qrup / Qrupdan Çıxar).
Əgər siz həm dövrlər üzrə nəticələnən cəmi, həm də işləyən cəmi ayrı bir sütunda görmək istəyirsinizsə, onda sahəni dəyər sahəsinə atmağın mənası var. Satılır yenidən sahənin dublikatını əldə etmək üçün - orada çalışan yekunların ekranını açacağıq. Bunu etmək üçün sahəyə sağ vurun və əmri seçin Əlavə hesablamalar – məcmu cəmi (Dəyərləri aşağıdakı kimi göstərin — İşləyən Cəmilər):
Orada siz həmçinin faiz olaraq ümumi artım seçimini seçə bilərsiniz və növbəti pəncərədə yığımın gedəcəyi sahəni seçməlisiniz - bizim vəziyyətimizdə bu tarix sahəsidir:
Bu yanaşmanın üstünlükləri:
- Böyük miqdarda məlumat tez oxunur.
- Heç bir düsturları əl ilə daxil etmək lazım deyil.
- Mənbə məlumatlarını dəyişdirərkən, siçanın sağ düyməsini və ya Data – Hamısını Yenilə əmri ilə xülasəni yeniləmək kifayətdir.
Dezavantajlar bunun xülasə olmasından irəli gəlir, yəni siz orada istədiyinizi edə bilməyəcəksiniz (sətirlər daxil edin, düsturlar yazın, hər hansı diaqramlar qurun və s.) artıq işləməyəcək.
Metod 3: Power Query
Komandadan istifadə edərək Power Query sorğu redaktoruna mənbə məlumatları olan “ağıllı” cədvəlimizi yükləyək Data – Cədvəldən/Aralıqdan (Məlumat - Cədvəldən/Aralıqdan). Excel-in ən son versiyalarında, yeri gəlmişkən, onun adı dəyişdirildi - indi o, adlanır Yarpaqları ilə (Cədvəldən):
Sonra aşağıdakı addımları yerinə yetirəcəyik:
1. Komanda ilə cədvəli tarix sütununa görə artan qaydada çeşidləyin Artan sırala cədvəl başlığındaki filtr açılan siyahıda.
2. Bir az sonra, işləyən cəmi hesablamaq üçün sıra sıra nömrəsi olan köməkçi sütuna ehtiyacımız var. Komanda ilə əlavə edək Sütun əlavə et - İndeks Sütunu - 1-dən (Sütun əlavə et - İndeks sütunu - 1-dən).
3. Həmçinin, çalışan cəmi hesablamaq üçün sütuna istinad lazımdır Satılır, bizim ümumiləşdirilmiş məlumatlarımızın olduğu yer. Power Query-də sütunlar həmçinin siyahılar (siyahı) adlanır və ona keçid əldə etmək üçün sütun başlığına sağ klikləyin və əmri seçin. Ətraflı məlumat (Ətraflı göstər). Bizə lazım olan ifadə əvvəlki addımın adından ibarət formula sətrində görünəcək #"İndeks əlavə edildi", cədvəli və sütun adını aldığımız yerdən [Satış] kvadrat mötərizədə bu cədvəldən:
Sonrakı istifadə üçün bu ifadəni panoya kopyalayın.
4. Lazımsız daha son addımı silin Satılır və yerinə komanda ilə işləyən cəmi hesablamaq üçün hesablanmış sütun əlavə edin Sütun əlavə etmək - Fərdi Sütun (Sütun əlavə et - Fərdi sütun). Bizə lazım olan formula belə görünəcək:
Burada funksiya Siyahı.Aralıq orijinal siyahını götürür (sütun [Satış]) və birincidən başlayaraq elementləri ondan çıxarır (düsturda bu, 0-dır, çünki Power Query-də nömrələmə sıfırdan başlayır). Alınacaq elementlərin sayı sütundan aldığımız sıra nömrəsidir [İndeks]. Beləliklə, birinci sıra üçün bu funksiya sütunun yalnız bir ilk xanasını qaytarır Satılır. İkinci sətir üçün - artıq ilk iki xana, üçüncü üçün - ilk üç və s.
Yaxşı, sonra funksiya Siyahı.Sum çıxarılan dəyərləri cəmləyir və hər cərgədə bütün əvvəlki elementlərin cəmini, yəni məcmu cəmini alırıq:
Artıq ehtiyacımız olmayan İndeks sütununu silmək və nəticələri Ev – Bağla və Yüklə əmri ilə Excel-ə yükləmək qalır.
Problem həll olunur.
Tez və qəzəbli
Prinsipcə, bunun qarşısını almaq olardı, amma məlhəmdə kiçik bir milçək var - bizim yaratdığımız sorğu tısbağa sürətində işləyir. Məsələn, mənim ən zəif kompüterimdə cəmi 2000 sətirdən ibarət cədvəl 17 saniyə ərzində işlənir. Daha çox məlumat varsa nə olacaq?
Sürətləndirmək üçün xüsusi List.Buffer funksiyasından istifadə edərək buferləşdirmədən istifadə edə bilərsiniz, bu funksiya ona arqument kimi verilmiş siyahını (siyahısını) RAM-a yükləyir və bu, gələcəkdə ona çıxışı xeyli sürətləndirir. Bizim vəziyyətimizdə, 2000 cərgəlik cədvəlimizin hər bir sətirində işləyən cəmi hesablayarkən Power Query-nin daxil olduğu #”Əlavə edilmiş indeks”[Satılmış] siyahısını bufer etmək məntiqlidir.
Bunu etmək üçün, Əsas sekmesinde Power Query redaktorunda, Power Query-də quraşdırılmış M dilində sorğumuzun mənbə kodunu açmaq üçün Qabaqcıl Redaktor düyməsini (Ev - Qabaqcıl Redaktor) klikləyin:
Və sonra orada dəyişənli bir xətt əlavə edin Siyahısım, dəyəri buferləmə funksiyası tərəfindən qaytarılır və növbəti addımda siyahıya çağırışı bu dəyişənlə əvəz edirik:
Bu dəyişiklikləri etdikdən sonra sorğumuz xeyli sürətlənəcək və cəmi 2000 saniyə ərzində 0.3 cərgəlik cədvəlin öhdəsindən gələcək!
Başqa bir şey, hə? 🙂
- Pareto diaqramı (80/20) və onu Excel-də necə qurmaq olar
- Mətndə açar söz axtarışı və Power Query-də sorğu buferlənməsi