koşu toplamı (olarak da bilinir)kümülatif toplam) birçok durumda çok yaygın olarak kullanılır.Bu, şimdiye kadarki değerlerin toplamının ne olduğunu size söyleyen bir metriktir.
Örneğin, aylık satış verileriniz varsa, değişen toplam size ayın ilk gününden belirli bir tarihe kadar kaç satış yapıldığını söyleyecektir.
Banka hesap özetlerindeki/defterlerdeki nakit bakiyelerinin hesaplanması, yemek planlarındaki kalorilerin hesaplanması vb. gibi toplamların sıklıkla kullanıldığı başka durumlar da vardır.
Microsoft Excel'de, çalışan toplamları hesaplamanın birkaç farklı yolu vardır.
Seçtiğiniz yöntem ayrıca verilerin yapısına da bağlıdır.
Örneğin, basit tablo verileriniz varsa, basit bir SUM formülü kullanabilirsiniz, ancak bir Excel tablonuz varsa, yapılandırılmış başvuruları kullanmak daha iyidir.Bunu yapmak için Power Query'yi de kullanabilirsiniz.
Bu derste hepsini ele alacağımExcel'de çalışan bir toplamı hesaplayınfarklı yöntemler.
O halde başlayalım!
İçerik
Tablo verilerini kullanarak koşu toplamlarını hesaplayın
Tablo verileriniz varsa (yani, Excel'de Excel tablolarına dönüştürülmeyen tablolar), değişen toplamları hesaplamak için bazı basit formüller kullanabilirsiniz.
Ekleme operatörünü kullanın
Diyelim ki tarihe göre satış verileriniz var ve在 C sütunundahesaplamaToplam çalışan.
İşte bunu yapmak için adımlar.
Aşama 1 – Toplamını çalıştırmak istediğiniz ilk hücre olan C2 hücresine,
= B2
Bu sadece B2 hücresinde aynı satış değerini alır.
Aşama 2– C3 hücresine aşağıdaki formülü girin:
= C2 + B3
Aşama 3– Formülü tüm sütuna uygulayın.Seçmek ve sürüklemek için doldurma tutamacını kullanabilir veya C3 hücresini kalan tüm hücrelere kopyalayıp yapıştırabilirsiniz (bu, referansı otomatik olarak ayarlayacak ve doğru sonucu verecektir).
Bu size aşağıda gösterilen sonucu verecektir.
Bu, çoğu durumda iyi sonuç veren çok basit bir yöntemdir.
Mantık basittir - her hücre, üzerindeki değeri alır (bu, önceki güne kadar olan kümülatif toplamdır) ve değeri bitişik hücreye ekler (bu, geçerli günün satış değeridir).
Tek bir dezavantajı var - bu veri kümesindeki mevcut herhangi bir satırı silerseniz, aşağıdaki tüm hücreler bir referans hatası (#REF!)
Veri kümenizin bu olasılığı varsa, SUM formülünü kullanarak sonraki yöntemi kullanın.
SUM'u kısmen kilitli hücre referanslarıyla kullanma
Tarihe göre satış verileriniz olduğunu ve C sütununda bir cari toplamı hesaplamak istediğinizi varsayalım.
Aşağıda, size toplam bir toplam verecek SUM formülü verilmiştir.
= TOPLA ($ B $ 2: B2)
Bu formülün nasıl çalıştığını açıklayayım.
Yukarıdaki SUM formülünde, referansı $B$2:B2 olarak eklemek için kullandım.
- $B$2 - Bu mutlak bir referanstır, yani aynı formülü aşağıdaki hücreye kopyaladığımda bu referans değişmez.Dolayısıyla aşağıdaki hücredeki formülü kopyaladığınızda formül SUM($B$2:B3) olarak değişecektir.
- B2 - Bu referansın ikinci kısmı, göreceli bir referans, yani formülü aşağı veya sağa kopyaladığımda bu ayarlanıyor.Yani aşağıdaki hücredeki formülü kopyalarken değer B3 olur.
Bu yaklaşımın güzel yanı, veri kümesindeki herhangi bir satırı silerseniz, bu formülün ayarlanması ve yine de size doğru toplam toplamı vermesidir.
Excel sayfasında çalışan toplamı hesaplayın
Excel'de tablo verileriyle çalışırken, bunları bir Excel tablosuna dönüştürmek en iyisidir.Verileri yönetmeyi kolaylaştırır ve ayrıca Power Query ve Power Pivot gibi araçların kullanımını kolaylaştırır.
Excel tablosu kullanmak, yapılandırılmış başvurular (tablodaki verilere başvurmayı ve formüllerde kullanmayı çok kolaylaştırır) ve tabloya veri eklerken veya tablodan veri kaldırırken başvuruların otomatik olarak ayarlanması gibi çeşitli avantajlar sağlar.
Size bir Excel sayfasında gösterdiğim yukarıdaki formülü kullanmaya devam edebilecek olsanız da, bunu yapmanın daha iyi yollarını göstereyim.
Aşağıdakine benzer bir Excel tablonuz olduğunu ve C sütununda değişen bir toplam hesaplamak istediğinizi varsayalım.
İşte bunu yapmak için formül:
=SUM(SalesData[[#Headers],[Satış]]:[@Satış])
Yukarıdaki formül biraz uzun görünebilir, ancak bunu kendiniz yazmak zorunda değilsiniz.Toplama formülünde gördüğünüz şeye yapılandırılmış başvuru denir ve bu, Excel'in bir Excel tablosundaki belirli veri noktalarına başvurması için etkili bir yoldur.
Örneğin SalesData[[#Headers],[Sale]], SalesData tablosundaki Sales başlığını ifade eder (SalesData, tabloyu oluştururken verdiğim Excel tablosunun adıdır)
[@Sale] ise Satış sütununun aynı satırındaki bir hücredeki değeri ifade eder.
Bunu sadece anlamanız için burada anlatıyorum, ancak yapılandırılmış referanslar hakkında hiçbir şey bilmiyorsanız bile, yine de bu formülü kolayca oluşturabilirsiniz.
İşte bunu yapmak için adımlar:
- C2 hücresine =SUM(
- Satış değerine sahip sütunun başlığı olan B1 hücresini seçin.Fareyi kullanabilir veya yön tuşlarını kullanabilirsiniz.Excel'in hücreye otomatik olarak yapılandırılmış bir başvuru girdiğini fark edeceksiniz.
- Ekle: (kolon)
- B2 hücresini seçin.Excel, hücrelere yeniden yapılandırılmış başvuruları otomatik olarak ekleyecektir
- Braketi kapatın ve Enter'a basın
Ayrıca tüm sütundaki formülü kopyalamanız gerekmediğini fark edeceksiniz, Excel sayfası bunu sizin için otomatik olarak yapıyor.
Bu yaklaşımın bir başka yararı da, bu veri kümesine yeni bir kayıt eklerseniz, Excel sayfasının tüm yeni kayıtlar için otomatik olarak değişen bir toplam hesaplamasıdır.
Sütun başlıklarını formüle dahil etmemize rağmen, formülün başlık metnini yok saydığını ve yalnızca sütundaki verileri dikkate aldığını unutmayın.
Power Query ile çalışan toplamları hesaplayın
Power Query, veritabanlarına bağlanmak, birden çok kaynaktan veri çıkarmak ve bunları Excel'e yerleştirmeden önce dönüştürmek söz konusu olduğunda harika bir araçtır.
Halihazırda Power Query kullanıyorsanız, verileri Power Query Düzenleyicisi'nde dönüştürürken (önce verileri Excel'de alıp ardından yukarıdaki yöntemlerden herhangi birini kullanarak değişen toplamları eklemek yerine) değişen toplamları eklemek daha verimlidir.
Power Query'de çalışan toplamları eklemek için yerleşik bir işlev olmasa da (keşke olsaydı), bunu yine de basit bir formülle yapabilirsiniz.
Şuna benzeyen bir Excel tablonuz olduğunu ve bu verilere değişen toplamları eklemek istediğinizi varsayalım:
İşte bunu yapmak için adımlar:
- Excel tablosundaki herhangi bir hücreyi seçin
- tıklama verileri
- Al ve Dönüştür sekmesinde, Tablodan/Aralıktan simgesini tıklayın.Bu, tabloyu Power Query Düzenleyicisi'nde açacaktır.
- [İsteğe bağlı] Tarih sütununuz zaten sıralanmamışsa, tarih sütunundaki filtre simgesini ve ardından Artan Sırala'yı tıklayın.
- Power Query Düzenleyicisi'nde Sütun Ekle sekmesine tıklayın
- Genel grubunda, Dizin Sütunu açılır menüsünü tıklayın (Dizin Sütunu simgesini tıklamayın, daha fazla seçeneği göstermek için yanındaki küçük siyah eğik oka tıklayın)
- "1'den" seçeneğine tıklayın.Bunu yapmak, 1'den başlayacak ve sütun boyunca 1'er artan sayıları girecek yeni bir dizin sütunu ekleyecektir.
- Özel Sütunlar simgesini tıklayın (ayrıca Sütun Ekle sekmesinde)
- Açılan Özel Sütunlar iletişim kutusunda yeni sütun için bir ad girin.Bu örnekte "Çalışan Toplam" adını kullanacağım.
- Özel sütun formülü alanına aşağıdaki formülü girin:List.Sum(List.Range(#"Dizin Eklendi"[Satış],0,[Dizin]))
- İletişim kutusunun altında "Sözdizimi hatası algılanmadı" yazan bir onay kutusu olduğundan emin olun.
- Tamam'ı tıklayın.Bu, yeni bir çalışan toplam sütunu ekleyecektir
- dizin sütununu bırak
- Dosya sekmesini ve ardından Kapat ve Yükle'yi tıklayın.
Yukarıdaki adımlar, çalışma kitabınıza toplamları içeren bir tablo içeren yeni bir çalışma sayfası ekleyecektir.
Şimdi, bunların basit formüllerle önceki yaklaşıma kıyasla çok fazla adım olduğunu düşünüyorsanız, haklısınız.
Halihazırda bir veri kümeniz varsa ve tek yapmanız gereken değişen toplamları eklemekse, Power Query kullanmamak en iyisidir.
Power Query'yi kullanmak, bir veritabanından veri çıkarmanız veya birkaç farklı çalışma kitabından verileri birleştirmeniz ve ayrıca süreçte bunlara çalışan toplamları eklemeniz gereken durumlarda anlamlıdır.
Ayrıca, bunu Power Query ile otomatikleştirdiğinizde, veri kümeniz bir sonraki değiştiğinde bunu tekrar yapmanız gerekmez, yalnızca sorguyu yenileyebilirsiniz ve bu size yeni veri kümesine dayalı sonuçlar verecektir.
Bu nasıl çalışıyor?
Şimdi bu yaklaşımla ne olduğunu hızlıca açıklayayım.
Power Query Düzenleyicisi'nde yaptığımız ilk şey, 1'den başlayan ve hücrede aşağı indikçe artan bir dizin sütunu eklemektir.
Bunu yapıyoruz çünkü bir sonraki adıma eklenen başka bir sütunda çalışan toplamı hesaplarken bu sütunu kullanmamız gerekiyor.
Sonra özel bir sütun ekliyoruz ve aşağıdaki formülü kullanıyoruz
List.Sum(List.Range(#"Dizin Eklendi"[Satış],0,[Dizin]))
Bu, içinde belirtilen aralığın toplamını size verecek bir List.Sum formülüdür.
Aralık, List.Range işlevi kullanılarak belirtilir.
List.Range işlevi, satış sütununda belirtilen aralığı çıktı olarak alır ve aralık, Index değerine göre değişir.Örneğin, ilk kayıt için aralık, ilk satış değeridir.Hücrede aşağı indikçe bu aralık genişler.
Yani, ilk hücre için.List.Sum size yalnızca ilk satış değerinin toplamını verir, ikinci hücre için size ilk iki satış değerinin toplamını verir vb.
Bu yaklaşım iyi çalışsa da büyük veri kümeleri (binlerce satır) için çok yavaş olabilir.
Kriterlere göre çalışan toplamları hesaplayın
Şimdiye kadar, bir sütundaki tüm değerlerin toplamını hesaplamanın örneklerini gördük.
Ancak bazı durumlarda, belirli bir kayıt için geçerli bir toplamı hesaplamak isteyebilirsiniz.
Örneğin, aşağıda, çalışan toplam yazıcı ve tarayıcıları iki farklı sütunda saymak istediğim bir veri kümem var.
Bu, belirtilen koşulların karşılandığından emin olarak bir çalışan toplamı hesaplayan SUMIF formülü kullanılarak yapılabilir.
Yazıcı sütunu için bunu yapmak için formül:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Aynı şekilde, tarayıcının çalışan toplamını hesaplamak için aşağıdaki formülü kullanın:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Yukarıdaki formülde, belirtilen koşullar sağlandığında bana bir aralıkta toplam verecek olan SUMIF kullandım.
Formül üç parametre alır:
- menzil: Bu, belirtilen koşula göre kontrol edilecek koşulların aralığıdır.
- kriterleri: bu, yalnızca bu kriter karşılandığında kontrol edilecek olan kriterdir, ardından üçüncü parametredeki değer, toplam aralığı eklenecektir.
- [Toplam aralığı]: koşul sağlandığında eklenecek değerlerin toplamının aralığıdır.
Ek olarakmenzil和Toplam aralığıparametre, referansın ikinci bölümünü kilitledim, böylece hücrede aşağı indikçe aralık genişlemeye devam ediyor.Bu, yalnızca o aralığa kadar olan değerleri göz önünde bulundurmamıza ve eklememize izin verir (dolayısıyla bir çalışan toplam).
Bu formülde kriter olarak başlık sütunlarını (Yazıcılar ve Tarayıcılar) kullanıyorum.Sütun başlıklarınız standart metinle tam olarak aynı değilse standardı sabit kodlayabilirsiniz.
Birden çok koşulu kontrol etmeniz gerekiyorsa, ETOPLA formülünü kullanabilirsiniz.
Toplamları bir pivot tabloda çalıştırma
PivotTable sonuçlarınıza değişen bir toplam eklemek istiyorsanız, PivotTable'lardaki yerleşik işlevselliği kullanarak bunu kolayca yapabilirsiniz.
Bir sütunda tarihler ve diğerinde satış değerleri olan aşağıdaki gibi bir pivot tablonuz olduğunu varsayalım.
Tarihe göre kümülatif satışları gösterecek ek bir sütun ekleme adımları şunlardır:
- Satış alanını sürükleyin ve Değer alanına bırakın.
- Bu, satış değerine sahip başka bir sütun ekleyecektir.
- Değer alanında Toplam Satış 2 seçeneğine tıklayın
- Değer Alanı Ayarları seçeneğine tıklayın
- Değer Alanı Ayarları iletişim kutusunda, Özel Adı Değişen Toplam olarak değiştirin
- "Değeri Farklı Görüntüle" sekmesine tıklayın
- Değeri şu şekilde görüntüle açılır menüsünde, "Çalışma Toplamı" seçeneğini seçin
- Temel Alanlar seçeneklerinde Tarih'in seçili olduğundan emin olun.
- Tamam'ı tıklayın
Yukarıdaki adımlar, ikinci satış sütununu "çalışan toplam" sütunu olarak değiştirecektir.
Bu nedenle, bunlar Excel'de çalışan toplamları hesaplamak için kullanabileceğiniz yöntemlerden bazılarıdır.Tablo verileriniz varsa basit formüller, Excel tablolarınız varsa yapılandırılmış başvuruları kullanan formüller kullanabilirsiniz.
Ayrıca Power Query ve PivotTable'ları kullanarak çalışan toplamların nasıl hesaplanacağını da anlattım.
Umarım bu öğreticiyi faydalı bulmuşsunuzdur.
