Yöntem 1. Formüller
Isınmak için en basit seçenekle başlayalım - formüller. Girdi olarak tarihe göre sıralanmış küçük bir tablomuz varsa, ayrı bir sütunda değişen toplamı hesaplamak için temel bir formüle ihtiyacımız var:
Buradaki ana özellik, SUM işlevi içindeki aralığın zor bir şekilde sabitlenmesidir - aralığın başlangıcına yapılan referans mutlak (dolar işaretleri ile) ve sona - göreli (dolar olmadan) yapılır. Buna göre, formülü tüm sütuna kopyalarken, toplamını hesapladığımız genişleyen bir aralık elde ederiz.
Bu yaklaşımın dezavantajları açıktır:
- Tablo tarihe göre sıralanmalıdır.
- Veri içeren yeni satırlar eklerken formülün manuel olarak genişletilmesi gerekecektir.
Yöntem 2. Özet tablo
Bu yöntem biraz daha karmaşık, ama çok daha hoş. Daha da ağırlaştırmak için, daha ciddi bir sorunu ele alalım – tarih sütununa göre sıralamanın olmadığı, ancak tekrarların olduğu 2000 satırlık bir veri tablosu (yani aynı günde birkaç kez satabiliriz):
Orijinal tablomuzu “akıllı” (dinamik) bir klavye kısayoluna dönüştürüyoruz Ctrl+T veya takım Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir), ardından komutla üzerinde bir pivot tablo oluşturuyoruz Ekle – Özet Tablo (Ekle — Özet Tablo). Özette satırlar alanına tarihi, değerler alanına satılan mal adedini koyuyoruz:
Excel'in oldukça eski bir sürümüne sahip değilseniz, tarihlerin yıllara, çeyreklere ve aylara göre otomatik olarak gruplandığını lütfen unutmayın. Farklı bir gruplamaya ihtiyacınız varsa (veya buna hiç ihtiyacınız yoksa), herhangi bir tarihe sağ tıklayıp komutları seçerek düzeltebilirsiniz. Gruplandır / Grubu Çöz (Grup / Grubu Çöz).
Hem periyotlara göre elde edilen toplamları hem de değişen toplamı ayrı bir sütunda görmek istiyorsanız, alanı değer alanına atmak mantıklıdır. Satıldı Alanın bir kopyasını almak için tekrar - içinde toplamların gösterimini açacağız. Bunu yapmak için alana sağ tıklayın ve komutu seçin Ek Hesaplamalar – Kümülatif Toplam (Değerleri şu şekilde göster — Değişen Toplamlar):
Orada ayrıca yüzde olarak artan toplamlar seçeneğini de seçebilirsiniz ve bir sonraki pencerede birikimin gideceği alanı seçmeniz gerekir - bizim durumumuzda bu tarih alanıdır:
Bu yaklaşımın avantajları:
- Büyük miktarda veri hızla okunur.
- Manuel olarak formül girilmesine gerek yoktur.
- Kaynak verilerde değişiklik yapılırken farenin sağ tuşu ile veya Data – Refresh All komutu ile özetin güncellenmesi yeterlidir.
Dezavantajları, bunun bir özet olması gerçeğinden kaynaklanmaktadır; bu, içinde istediğiniz her şeyi yapamayacağınız anlamına gelir (satır ekleme, formül yazma, herhangi bir diyagram oluşturma, vb.) artık çalışmayacaktır.
Yöntem 3: Güç Sorgusu
Komutu kullanarak "akıllı" tablomuzu kaynak verilerle Power Query sorgu düzenleyicisine yükleyelim. Veriler – Tablodan/Aralıktan (Veri — Tablodan/Aralıktan). Bu arada, Excel'in en son sürümlerinde yeniden adlandırıldı - şimdi denir yaprakları ile (Sayfadan):
Ardından aşağıdaki adımları gerçekleştireceğiz:
1. Tabloyu komutla tarih sütununa göre artan düzende sıralayın Artan şekilde sırala tablo başlığındaki filtre açılır listesinde.
2. Biraz sonra, toplam sayıyı hesaplamak için sıra numarasına sahip yardımcı bir sütuna ihtiyacımız var. komutuyla ekleyelim Sütun Ekle – Dizin Sütunu – 1'dan (Sütun ekle — Dizin sütunu — 1'dan itibaren).
3. Ayrıca, değişen toplamı hesaplamak için sütuna bir referansa ihtiyacımız var. Satıldı, özetlenen verilerimizin bulunduğu yer. Power Query'de sütunlara listeler (liste) de denir ve buna bir bağlantı almak için sütun başlığına sağ tıklayın ve komutu seçin detaylandırma (Detayları göster). İhtiyacımız olan ifade, önceki adımın adından oluşan formül çubuğunda görünecektir. #”Dizin eklendi”, tabloyu ve sütun adını nereden alıyoruz [Satış] köşeli parantez içindeki bu tablodan:
Daha fazla kullanım için bu ifadeyi panoya kopyalayın.
4. Gereksiz daha son adımı sil Satıldı ve bunun yerine komutla çalışan toplamı hesaplamak için hesaplanmış bir sütun ekleyin Sütun Ekleme – Özel Sütun (Sütun ekle — Özel sütun). İhtiyacımız olan formül şöyle görünecek:
Buradaki fonksiyon Liste.Aralık orijinal listeyi alır (sütun [Satış]) ve ilkinden başlayarak öğeleri ondan ayıklar (Formülde bu 0'dır, çünkü Power Query'deki numaralandırma sıfırdan başlar). Alınacak eleman sayısı, sütundan aldığımız satır numarasıdır. [Dizin]. Bu nedenle, ilk satır için bu işlev, sütunun yalnızca bir ilk hücresini döndürür. Satıldı. İkinci satır için - zaten ilk iki hücre, üçüncü için - ilk üç, vb.
Peki, o zaman fonksiyon Liste.Topla çıkarılan değerleri toplar ve her satırda önceki tüm öğelerin toplamını alırız, yani kümülatif toplam:
Geriye artık ihtiyacımız olmayan Dizin sütununu silmek ve sonuçları Ana Sayfa - Kapat ve Yükle komutuyla Excel'e geri yüklemek kalıyor.
Problem çözüldü.
Hızlı ve Öfkeli
Prensip olarak, bu durdurulabilirdi, ancak merhemde küçük bir sinek var - yarattığımız istek bir kaplumbağa hızında çalışıyor. Örneğin, en zayıf olmayan bilgisayarımda sadece 2000 satırlık bir tablo 17 saniyede işleniyor. Ya daha fazla veri varsa?
Hızlandırmak için, kendisine argüman olarak verilen listeyi (listeyi) RAM'e yükleyen ve gelecekte erişimi büyük ölçüde hızlandıran özel List.Buffer işlevini kullanarak arabelleğe almayı kullanabilirsiniz. Bizim durumumuzda, 2000 satırlık tablomuzun her satırında çalışan toplamı hesaplarken Power Query'nin erişmesi gereken #”Eklenen dizin”[Satıldı] listesini arabelleğe almak mantıklıdır.
Bunu yapmak için, Ana sekmesindeki Power Query düzenleyicisinde, sorgumuzun kaynak kodunu Power Query'de yerleşik M dilinde açmak için Gelişmiş Düzenleyici düğmesine (Ana Sayfa – Gelişmiş Düzenleyici) tıklayın:
Ve sonra orada bir değişken içeren bir satır ekleyin Listemdeğeri arabelleğe alma işlevi tarafından döndürülen ve bir sonraki adımda listeye yapılan çağrıyı bu değişkenle değiştiriyoruz:
Bu değişiklikleri yaptıktan sonra, sorgumuz önemli ölçüde daha hızlı hale gelecek ve 2000 satırlık bir tabloyla sadece 0.3 saniyede başa çıkacak!
Başka bir şey, değil mi? 🙂
- Pareto grafiği (80/20) ve Excel'de nasıl oluşturulacağı
- Power Query'de metinde anahtar sözcük arama ve sorgu arabelleğe alma