Çalışan toplam (aynı zamanda kümülatif toplam) birçok durumda oldukça yaygın olarak kullanılır. Şimdiye kadarki değerlerin toplamının ne olduğunu söyleyen bir ölçümdür.
Örneğin, aylık satış verileriniz varsa, değişen toplam size ayın ilk gününden belirli bir güne kadar ne kadar satış yapıldığını söyler.
Ayrıca, banka hesap özetlerinizdeki/defterinizdeki nakit bakiyenizi hesaplamak, yemek planınızda kalori saymak gibi genel toplamın sıklıkla kullanıldığı başka durumlar da vardır.
Microsoft Excel'de, çalışan toplamları hesaplamanın birçok farklı yolu vardır.
Seçtiğiniz yöntem, verilerinizin nasıl yapılandırıldığına da bağlı olacaktı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 en iyisidir. Bunu yapmak için Power Query'yi de kullanabilirsiniz.
Bu derste, tüm bu farklı yöntemleri ele alacağım. Excel'de çalışan toplamları hesapla.
O halde başlayalım!
Tablo Verileriyle Koşu Toplamını Hesaplama
Tablo verileriniz varsa (yani, Excel'de Excel tablosuna dönüştürülmemiş bir tablo), mevcut toplamları hesaplamak için bazı basit formüller kullanabilirsiniz.
Toplama Operatörünü Kullanma
Tarih bazında satış verileriniz olduğunu ve C sütununda cari toplamı hesaplamak istediğinizi varsayalım.
Bunu yapmak için adımlar aşağıdadır.
Aşama 1 - Çalışan toplamı istediğiniz ilk hücre olan C2 hücresine girin
=B2
Bu, B2 hücresindeki aynı satış değerlerini alacaktır.
Adım 2 - C3 hücresine aşağıdaki formülü girin:
=C2+B3
Aşama 3 - Formülü tüm sütuna uygulayın. Doldurma tutamağını seçip sürüklemek için kullanabilir veya C3 hücresini kopyalayıp kalan tüm hücrelere yapıştırabilirsiniz (bu, referansı otomatik olarak ayarlar ve doğru sonucu verir).
Bu size aşağıda gösterildiği gibi sonucu verecektir.
Bu gerçekten basit bir yöntemdir ve çoğu durumda iyi sonuç verir.
Mantık basittir - her hücre, üstündeki değeri alır (bu, önceki tarihe kadar kümülatif toplamdır) ve bitişik hücredeki değeri (o günün satış değeridir) ekler.
Tek bir dezavantajı var - bu veri kümesindeki mevcut satırlardan herhangi birini silmeniz durumunda, aşağıdaki tüm hücreler bir referans hatası (#REF!)
Veri kümenizde bu bir olasılıksa, SUM formülünü kullanan sonraki yöntemi kullanın.
SUM'u Kısmen Kilitli Hücre Referansıyla Kullanma
Tarih bazında satış verileriniz olduğunu ve C sütununda cari toplamı hesaplamak istediğinizi varsayalım.
Aşağıda, size çalışan 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 referans, yani aynı formülü aşağıdaki hücrelere kopyaladığımda bu referans değişmeyecek. Bu nedenle, aşağıdaki hücredeki formülü kopyalarken formül SUM($B$2:B3) olarak değişecektir.
- B2 - bu, referansın göreli bir referans olan ikinci kısmıdır; bu, formülü aşağı veya sağa kopyaladığımda bunun ayarlanacağı anlamına gelir. Yani aşağıdaki hücredeki formülü kopyalarken bu değer B3 olur.
Ayrıca şunu okuyun: Excel'de Mutlak, Göreli ve Karışık Hücre Başvuruları
Bu yöntemle ilgili en iyi şey, veri kümesindeki satırlardan herhangi birini silmeniz durumunda, bu formülün ayarlama yapması ve yine de size doğru toplamları vermesidir.
Excel Tablosunda Çalışan Toplamı Hesaplama
Excel'de tablo verileriyle çalışırken, bunları bir Excel tablosuna dönüştürmek iyi bir fikirdir. Verileri yönetmeyi çok daha kolaylaştırır ve ayrıca Power Query ve Power Pivot gibi araçların kullanımını kolaylaştırır.
Excel tablolarında çalışmak, yapılandırılmış referanslar (tablodaki verilere başvurmayı ve formüllerde kullanmayı gerçekten kolaylaştırır) ve tablodan veri eklemeniz veya silmeniz durumunda referansların otomatik olarak ayarlanması gibi avantajlarla birlikte gelir.
Size bir Excel tablosunda gösterdiğim yukarıdaki formülü kullanmaya devam edebilirken, bunu yapmak için size daha iyi yöntemler göstereyim.
Aşağıda gösterildiği gibi bir Excel tablonuz olduğunu ve C sütununda geçen toplamı hesaplamak istediğinizi varsayalım.
Bunu yapacak formül aşağıdadır:
=SUM(SalesData[[#Headers],[Satış]]:[@Satış])
Yukarıdaki formül biraz uzun görünebilir, ancak bunu kendiniz yazmak zorunda değilsiniz. toplam formülünde gördüklerinize yapılandırılmış başvurular denir; bu, Excel'in bir Excel tablosundaki belirli veri noktalarına başvurmanın etkili bir yoludur.
Örneğin SalesData[[#Headers],[Sale]], SalesData tablosundaki Sale başlığını ifade eder (SalesData, tabloyu oluştururken verdiğim Excel tablosunun adıdır)
Ve [@Sale], Sale sütunundaki aynı satırdaki hücredeki değeri ifade eder.
Bunu sadece anlamanız için burada açıkladım, ancak yapılandırılmış referanslar hakkında hiçbir şey bilmiyorsanız bile, yine de bu formülü kolayca oluşturabilirsiniz.
Bunu yapmak için gereken adımlar aşağıdadır:
- 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 bu hücre için yapılandırılmış başvuruyu otomatik olarak girdiğini fark edeceksiniz.
- Bir : (iki nokta üst üste sembolü) ekleyin
- B2 hücresini seçin. Excel, hücre için yapılandırılmış başvuruyu yeniden otomatik olarak ekler
- Braketi kapatın ve enter tuşuna basın
Ayrıca tüm sütundaki formülü kopyalamanız gerekmediğini fark edeceksiniz, bir Excel tablosu bunu sizin için otomatik olarak yapar.
Bu yöntemle ilgili bir başka harika şey de, bu veri kümesine yeni bir kayıt eklemeniz durumunda, Excel tablosunun tüm yeni kayıtlar için çalışan toplamı otomatik olarak hesaplamasıdır.
Sütunun başlığını formülümüze dahil ederken, formülün başlık metnini yok sayacağını ve yalnızca sütundaki verileri dikkate alacağını unutmayın.
Power Query Kullanarak Koşu Toplamını Hesaplama
Power Query, veritabanlarıyla bağlantı kurmak, birden çok kaynaktan verileri çı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 ile çalışıyorsanız, verileri Power Query düzenleyicisinin kendisinde dönüştürürken (1. verileri Excel'de alıp ardından yukarıdakilerden herhangi birini kullanarak çalışan toplamları eklemek yerine) değişen toplamları eklemek daha verimli olacaktır. Yukarıda belirtilen yöntemler).
Power Query'de çalışan toplamları eklemek için yerleşik bir özellik olmasa da (ki keşke olsaydı), bunu yine de basit bir formül kullanarak yapabilirsiniz.
Aşağıda gösterildiği gibi bir Excel tablonuz olduğunu ve bu verilere değişen toplamları eklemek istediğinizi varsayalım:
Bunu yapmak için gereken adımlar aşağıdadır:
- Excel tablosundaki herhangi bir hücreyi seçin
- Veri'ye tıklayın
- Al ve Dönüştür sekmesinde, Tablodan/Aralıktan simgesine tıklayın. Bu, Power Query düzenleyicisinde tabloyu açacaktır.
- [İsteğe bağlı] Tarih sütununuz henüz sıralanmamışsa, Tarih sütunundaki filtre simgesine tıklayın ve ardından Artan Sırala'ya tıklayın
- Power Query düzenleyicisinde Sütun Ekle sekmesine tıklayın
- Genel grubunda, Dizin Sütunu açılır menüsüne tıklayın (Dizin Sütunu simgesine değil, daha fazla seçenek göstermek için hemen yanındaki küçük siyah eğik oka tıklayın)
- '1'den' seçeneğine tıklayın. Bunu yapmak, birden başlayarak yeni bir dizin sütunu ekleyecek ve tüm sütuna 1 artan sayılar girecektir.
- 'Özel Sütun' simgesine tıklayın (Sütun Ekle sekmesinde de bulunur)
- Açılan özel sütun iletişim kutusunda yeni sütun için bir ad girin. bu örnekte 'Koşu 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 kısmında "Sözdizimi hatası algılanmadı" yazan bir onay kutusu bulunduğundan emin olun.
- Tamam'ı tıklayın. Bu, yeni bir çalışan toplam sütunu ekler
- Dizin Sütununu Kaldır
- Dosya sekmesine tıklayın ve ardından 'Kapat ve Yükle'ye tıklayın
Yukarıdaki adımlar, çalışma kitabınıza, çalışan toplamları içeren bir tabloyla yeni bir sayfa ekler.
Şimdi, bunların önceki basit formülleri kullanma yöntemlerine 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 daha iyidir.
Power Query'yi kullanmak, bir veritabanından veri çıkarmanız veya birden çok farklı çalışma kitabından verileri birleştirmeniz ve bu süreçte buna çalışan toplamları eklemeniz gerektiğinde mantıklıdır.
Ayrıca, bu otomasyonu Power Query kullanarak bir kez yaptığınızda, veri kümeniz bir sonraki değiştiğinde bunu tekrar yapmanız gerekmez, sorguyu yenileyebilirsiniz ve bu size yeni veri kümesine dayalı sonucu verir.
Bu nasıl çalışıyor?
Şimdi bu yöntemde neler olduğunu hızlıca anlatayım.
Power Query düzenleyicisinde yaptığımız ilk şey, birden başlayarak ve hücrelerde aşağı indikçe birer birer artan bir dizin sütunu eklemektir.
Bunu yapıyoruz çünkü bir sonraki adımda eklediğimiz 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 olan bir List.Sum formülüdür.
Ve bu aralık List.Range işlevi kullanılarak belirlenir.
List.Range işlevi satış sütununda belirtilen aralığı çıktı olarak verir ve bu aralık Index değerine göre değişir. Örneğin, ilk kayıt için aralık, yalnızca ilk satış değeri olacaktır. Ve hücrelerde aşağı indikçe bu aralık genişleyecektir.
Yani, ilk hücre için. List.Sum size yalnızca ilk satış değerinin toplamını verir ve ikinci hücre için size ilk iki satış değerinin toplamını verir vb.
Bu yöntem iyi çalışsa da büyük veri kümelerinde (binlerce satır) gerçekten yavaşlar. Büyük bir veri seti ile uğraşıyorsanız ve buna çalışan toplamları eklemek istiyorsanız, daha hızlı olan diğer yöntemleri gösteren bu öğreticiye bir göz atın.Kriterlere Göre Koşu Toplamını Hesaplama
Şimdiye kadar, bir sütundaki tüm değerler için geçerli toplamı hesapladığımız örnekleri gördük.
Ancak belirli kayıtlar için mevcut toplamı hesaplamak istediğiniz durumlar olabilir.
Örneğin, aşağıda bir veri setim var ve Yazıcılar ve Tarayıcılar için çalışan toplamı iki farklı sütunda ayrı ayrı hesaplamak istiyorum.
Bu, belirtilen koşulun karşılandığından emin olarak toplam toplamı hesaplayan bir SUMIF formülü kullanılarak yapılabilir.
Yazıcı sütunları için bunu yapacak formül aşağıdadır:
=TOPLA($C$2:C2,$D$1,$B$2:B2)
Benzer şekilde, Tarayıcıların toplamını hesaplamak için aşağıdaki formülü kullanın:
=TOPLA($C$2:C2,$E$1,$B$2:B2)
Yukarıdaki formüllerde, belirtilen kriterler karşılandığında bana bir aralıktaki toplamı verecek olan SUMIF kullandım.
Formül üç argüman alır:
- Aralık: bu, belirtilen kriterlere göre kontrol edilecek kriter aralığıdır
- kriterler: bu, yalnızca bu kriter karşılanırsa kontrol edilecek olan kriterdir, daha sonra toplam aralık olan üçüncü argümandaki değerler eklenecektir.
- [Toplam aralığı]: bu, kriterlerin karşılanması durumunda değerlerin ekleneceği toplam aralıktır
Ayrıca, içinde Aralık ve Toplam aralığı argüman, referansın ikinci bölümünü kilitledim, böylece hücrelerde aşağı indikçe aralık genişlemeye devam edecekti. Bu, yalnızca o aralığa kadar olan değerleri dikkate almamıza ve eklememize izin verir (dolayısıyla toplamlar çalışır).
Bu formülde kriter olarak başlık sütununu (Yazıcı ve Tarayıcı) kullandım. sütun başlıklarınız ölçüt metniyle tam olarak aynı değilse ölçütleri sabit kodlayabilirsiniz.
Kontrol etmeniz gereken birden fazla koşul varsa, SUMIFS formülünü kullanabilirsiniz.
Pivot Tablolarda Toplamı Çalıştırma
Bir Pivot Tablo sonucuna değişen toplamlar eklemek istiyorsanız, bunu Pivot tablolarındaki yerleşik bir işlevi kullanarak kolayca yapabilirsiniz.
Diyelim ki bir sütunda tarih ve diğer sütunda satış değeri olan aşağıda gösterildiği gibi bir Pivot Tablonuz var.
Aşağıda, tarihe göre satışların devam eden toplamını gösterecek ek bir sütun ekleme adımları verilmiştir:
- Satış alanını sürükleyin ve Değer alanına koyun.
- Bu, Satış değerlerine sahip başka bir sütun ekleyecektir.
- Değer alanında Satış Toplamı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ı 'Çalışan Toplamlar' olarak değiştirin
- 'Değeri Farklı Göster' sekmesine tıklayın
- Değeri farklı göster açılır menüsünde, 'Çalışma Toplamı' seçeneğini seçin
- Temel alan seçeneklerinde Tarih'in seçili olduğundan emin olun.
- Tamam'ı tıklayın
Yukarıdaki adımlar, ikinci satış sütununu Değişen Toplam sütununa değiştirir.
Bu nedenle, Excel'de mevcut toplamı hesaplamak için kullanabileceğiniz yöntemlerden bazıları şunlardır. Tablo biçiminde verileriniz varsa basit formüller, Excel tablonuz varsa yapılandırılmış başvurulardan yararlanan formüller kullanabilirsiniz.
Ayrıca, Power Query kullanarak ve Pivot Tablolarda çalışan toplamın nasıl hesaplanacağını da ele aldım.
Umarım bu öğreticiyi faydalı bulmuşsunuzdur.
