Örnek çalışma kitabını indirin
Bu Öğretici, Excel ARA TOPLAM İşlevi Özet istatistikleri hesaplamak için Excel'de.
ARA TOPLAM İşleve Genel Bakış
ARA TOPLAM İşlevi Bir dizi veri için bir özet istatistik hesaplar. Mevcut istatistikler, ortalama, standart sapma, sayım, min ve maks. dahil ancak bunlarla sınırlı değildir. İşlev girişleri bölümünde aşağıdaki tam listeye bakın:
ARA TOPLAM Excel Çalışma Sayfası İşlevini kullanmak için bir hücre seçin ve şunu yazın:
(Formül girişlerinin nasıl göründüğüne dikkat edin)
SUBTOTAL İşlev Sözdizimi ve Girişler:
| 1 | =ARATOPLAM(işlev_sayısı,REF1) |
işlev_sayısı - Hangi işlemin gerçekleştirileceğini gösteren bir sayı.
REF1 - Hesaplanacak verileri içeren aralıklar veya referanslar.
ALTTOPLAM işlevi nedir?
ARA TOPLAM, elektronik tablolardaki benzersiz işlevlerden biridir, çünkü gizli hücreler ile gizli olmayan hücreler arasındaki farkı söyleyebilir. Bu, filtrelenmiş aralıklarla uğraşırken veya farklı kullanıcı seçimlerine dayalı hesaplamalar yapmanız gerektiğinde oldukça yardımcı olabilir. Diğer ARA TOPLAM işlevlerini hesaplamalarından yok saymayı da bildiğinden, onu çift sayma korkusu olmadan büyük özetlenmiş veriler içinde de kullanabiliriz.
SUBTOTAL ile Temel Özet
Diyelim ki, sıralanmış bir ürün satışları tablonuz var ve her bir ürün için toplamlar ve genel bir toplam oluşturmak istiyorsunuz. Bir PivotTable kullanabilir veya bazı formüller ekleyebilirsiniz. Bu düzeni göz önünde bulundurun:
B5 ve B8 hücrelerine bazı ARA TOPLAM işlevleri yerleştirdim.
| 1 | =ARATOPLAM(9, B2:B4) |
Sözdiziminden, ilk argüman için çeşitli sayılar kullanabilirsiniz. Özel durumumuzda, toplam yapmak istediğimizi belirtmek için 9 kullanıyoruz.
B9 hücresine odaklanalım. B sütununun tüm veri aralığını içeren ancak diğer alt toplamları içermeyen bu formüle sahiptir.
| 1 | =ARATOPLAM(9, B2:B8) |
NOT: Tüm özet formülleri kendiniz yazmak istemiyorsanız, Veri şeridine gidebilir ve Anahat - Ara Toplam sihirbazını kullanabilirsiniz. Otomatik olarak satırlar ekleyecek ve formülleri sizin için yerleştirecektir.
İlk argümanlardaki fark
İlk örnekte, toplam yapmak istediğimizi belirtmek için 9 kullandık. 9 ile 109 arasındaki fark, fonksiyonun gizli satırları nasıl işlemesini istediğimizdir. 1XX belirtimlerini kullanırsanız, işlev manuel olarak gizlenmiş veya filtrelenmiş satırları içermez.
İşte önceki tablomuz. 9 ve 109 argüman arasındaki farkı görebilmemiz için fonksiyonları değiştirdik. Tüm görünür, sonuçlar aynıdır.
Sütun B'deki 6 değerini filtrelemek için bir filtre uygularsak, iki işlev aynı kalır.
Satırları manuel olarak gizlersek farkı görürüz. 109 işlevi, 9 işlevi görmezken gizli satırı yoksayabiliyordu.
SUBTOTAL ile Matematik İşlemini Değiştir
Bazen, kullanıcınıza ne tür hesaplamaların gerçekleştirildiğini değiştirme yeteneği vermek isteyebilirsiniz. Örneğin, toplamı mı yoksa ortalamayı mı almak istiyorlar. SUBTOTAL, matematik işlemini bir bağımsız değişken numarasıyla kontrol ettiğinden, bunu tek bir formülde yazabilirsiniz. İşte kurulumumuz:
D2'de kullanıcının "Topla" veya "Ortalama"yı seçebileceği bir açılır menü oluşturduk. E2'deki formül:
| 1 | =ARATOPLAM(EĞER(D2="Ortalama",1,EĞER(D2="Toplam",9)),B2:B4) |
Burada EĞER işlevi, ARATOPLAM'a hangi sayısal argümanın verileceğini belirleyecektir. A5 “Ortalama” ise, 1 çıktısı verir ve ARA TOPLAM, B2:B4 ortalamasını verir. Veya, A5 “Topla”ya eşitse, IF 9 verir ve farklı bir sonuç alırız.
Gerçekleştirmek istediğiniz daha fazla işlem türünü listelemek için bir arama tablosu kullanarak bu yeteneği genişletebilirsiniz. Arama tablonuz şöyle görünebilir
Ardından, E2'deki formülü şu şekilde değiştirebilirsiniz:
| 1 | =ARATOPLAM(DÜŞEYARA(A5, Arama Tablosu, 2, 0), B2:B4) |
SUBTOTAL ile koşullu formüller
SUBTOTAL'in yapabileceği birçok işlem olmasına rağmen, kriterleri tek başına kontrol edemez. Ancak bu işlemi gerçekleştirmek için bir helper sütununda kullanabiliriz. Bildiğiniz bir veri sütununuz olduğunda her zaman içinde bir parça veri varsa, gizli satırları tespit etmek için SUBTOTALs özelliğini kullanabilirsiniz.
İşte bu örnekte çalışacağımız tablo. Sonunda, "Apple" için değerleri toplayabilmek, ancak kullanıcının Miktar sütununu filtrelemesine izin vermek istiyoruz.
İlk olarak, ARA TOPLAM işlevini barındıracak bir yardımcı sütun oluşturun. C2'de formül:
| 1 | =ARATOPLAM(103, A2) |
103'ün bir COUNTA yapmak istediğimiz anlamına geldiğini unutmayın. COUNTA kullanmanızı öneririm çünkü daha sonra A2 referans hücrenizin doldurulmasını sağlayabilirsiniz. herhangi biri sayılar veya metin. Şimdi şuna benzeyen bir tablonuz olacak:
Tüm değerler sadece 1 olduğu için bu ilk başta yararlı görünmüyor. Ancak, 3. satırı gizlersek, C3'teki bu “1” gizli bir satırı işaret ettiğinden 0 olarak değişecektir. Belirli gizli hücrenin değerini gösteren bir görüntüye sahip olmak imkansız olsa da, satırı gizleyerek ve ardından kontrol etmek için bunun gibi temel bir formül yazarak kontrol edebilirsiniz.
| 1 | =C3 |
Artık gizli olup olmamasına göre değeri değişen bir sütunumuz olduğuna göre, son denklemi yazmaya hazırız. SUMIFS'imiz şöyle görünecek
Bu formülde, yalnızca A sütunu "Elma"ya eşit olduğunda B sütunundaki değerleri toplayacağız, ve C sütunundaki değer 1'dir (diğer bir deyişle, satır gizli değildir). Diyelim ki kullanıcımız 600'ü filtrelemek istiyor çünkü anormal derecede yüksek görünüyor. Formülümüzün doğru sonuç verdiğini görebiliriz.
Bu yetenekle, COUNTIFS, SUMIFS ve hatta bir SUMPRODUCT'e bir çek uygulayabilirsiniz. Kullanıcılarınızın bazı tablo dilimleyicilerini kontrol etmesine izin verme yeteneğini eklersiniz ve harika bir kontrol paneli oluşturmaya hazırsınız.
Google E-Tablolar'da ARA TOPLAM
ARA TOPLAM İşlevi, Google E-Tablolar'da Excel'dekiyle tamamen aynı şekilde çalışır:
VBA'da ARA TOPLAM Örnekler
VBA'da ALTTOPLAM işlevini de kullanabilirsiniz. Tip:application.worksheetfunction.alttoplam(işlev_sayısı,reh1)
Aşağıdaki VBA deyimlerini yürütme
| 1234567891011121314151617 | Aralık("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))Aralık("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))Aralık("C9") = Application.WorksheetFunction.Subtotal(4, Aralık("C2:C5"))Aralık("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))Aralık("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))Aralık("D7") = Application.WorksheetFunction.Subtotal(1, Aralık("D2:D5"))Aralık("D8") = Application.WorksheetFunction.Subtotal(2, Range("D2:D5"))Aralık("D9") = Application.WorksheetFunction.Subtotal(4, Aralık("D2:D5"))Aralık("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))Aralık("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))Range("E7") = Application.WorksheetFunction.Subtotal(1, Range("E2:E5"))Aralık("E8") = Application.WorksheetFunction.Subtotal(2, Range("E2:E5"))Aralık("E9") = Application.WorksheetFunction.Subtotal(4, Aralık("E2:E5"))Aralık("E10") = Application.WorksheetFunction.Subtotal(5, Range("E2:E5"))Aralık("E11") = Application.WorksheetFunction.Subtotal(9, Range("E2:E5")) |
aşağıdaki sonuçları üretecek
İşlev bağımsız değişkenleri için (işlev_sayısı vb.), bunları doğrudan işleve girebilir veya bunun yerine kullanılacak değişkenleri tanımlayabilirsiniz.
Excel'deki Tüm İşlevler Listesine Dön
