Excel'de alt toplam satırını hariç tutarak veriler nasıl filtrelenir?
Günlük veri işleme süreçlerinde, genellikle toplamlar, ortalamalar, sayımlar ve diğer birleşimler gibi önemli rakamları özetlemek için veri setinizin altına bir toplam satırı hesaplamak yaygındır. Ancak Excel’in filtre özelliğini kullandığınızda, toplam satırı da verilerinizin geri kalanıyla aynı şekilde filtrelenebilir, yani filtreler uygulandığında gizlenebilir. Özellikle özet raporlama, finansal tablolar veya devam eden veri analitiği gibi birçok senaryoda, çalışma sayfanızdaki diğer verilere hangi filtreler uygulanırsa uygulansın toplam satırının her zaman verilerinizin en altında görünür kalmasını isteyebilirsiniz; aşağıdaki ekran görüntüsünde gösterildiği gibi.
Bu makale, alt toplam satırının filtreleme sırasında her zaman görünür kalmasını sağlamak için size çeşitli pratik yöntemler sunacak. Her bir yöntem farklı kullanım senaryoları ve kullanıcı tercihleri için uygundur. Makale, yalnızca Excel'in yerleşik özelliklerini değil, ayrıca formül yaklaşımlarını ve programatik VBA çözümlerini de içerir, böylece ihtiyaçlarınıza göre geniş bir seçenek yelpazesi sunar.
➤ Tablo oluşturarak alt toplam satırını hariç tutarak verileri filtreleme
➤ Formül ekleyerek alt toplam satırını hariç tutarak verileri filtreleme
➤ VBA kodu kullanarak alt toplam satırını hariç tutarak verileri filtreleme
Tablo oluşturarak alt toplam satırını hariç tutarak verileri filtreleme
Toplam satırınızı filtreleme sırasında görünür tutmanın en basit yolu, verilerinizi resmi bir Excel Tablosuna dönüştürmektir. Excel Tablolarının, verilerinizin alt kısmında akıllıca tasarlanmış olarak kalmak üzere yerleştirilmiş bir Toplam Satır işlevi bulunmaktadır. Bu yöntem için detaylı adımlar, yaygın senaryolar ve notlar aşağıda açıklanmıştır:
1. Verileriniz zaten manuel olarak hesaplanmış bir toplam satırı içeriyorsa (yani tabloya dahil edilmemiş toplam formülleri içeren bir satır), çakışmayı önlemek için bu satırı geçici olarak silin.
2. Gerçek veri aralığınızı seçin (manuel olarak oluşturulan toplam satırı hariç). Ardından Ekle > Tablo'ya gidin. Tablo Oluştur iletişim kutusunda, verileriniz sütun başlıkları içeriyorsa Tablomun Başlıkları Var seçeneğinin işaretli olduğundan emin olun. Veri aralığını gözden geçirerek tüm satırların içerildiğinden emin olun.
3. Tamam'a tıkladıktan sonra, verileriniz bir Excel Tablosu olarak biçimlendirilecektir. Tablo seçiliyken, Tasarım sekmesine gidin (bazı sürümlerde Tablo Tasarımı). Tablo Stil Seçenekleri grubunda, Toplam Satır kutusunu işaretleyin. Excel, alt kısma otomatik olarak yeni bir toplam satırı ekleyecektir.
4. Artık herhangi bir sütundaki filtre oklarını kullanarak verilerinizi gerektiği gibi filtreleyebilirsiniz. Toplam Satır, uyguladığınız filtrelere bakılmaksızın her zaman tablonun son satırı olarak görünecektir.
Uygulanabilir Senaryolar: Bu yöntem, sık sık güncellenen, ekipler arasında paylaşılan veya tekrarlayan raporlarda kullanılan veri setleri için idealdir - özellikle bantlı satırlar, yapılandırılmış referanslar ve otomatik biçimlendirme gibi yerleşik tablo özelliklerinden faydalandığınızda.
Avantajlar:
- Excel'in yerleşik özelliklerine sorunsuz entegrasyon
- Veri genişlediğinde veya daraldığında formülleri güncellemenize gerek yok
- Toplam satırı dinamik kalır ve otomatik olarak güncellenir
Sınırlamalar:
- Toplam satırındaki özel formüller, Tablo'ya dönüştürdükten sonra yeniden yapılandırılmalıdır
- Büyük veri setleri gerçek zamanlı biçimlendirmeden dolayı küçük performans düşüşleri yaşayabilir
- Tabloya dönüştürmeden önce manuel olarak oluşturulan toplam satırını silmeyi unutmayın, aksi takdirde çoğaltılmış toplamlar görebilirsiniz.
- Tablo yapısını tutarlı tutmak için veri eklemek veya kaldırmadan önce filtreleri temizleyin.
Kutools AI ile Excel Sihirini Keşfedin
- Akıllı Yürütme: Hücre işlemleri gerçekleştirin, verileri analiz edin ve grafikler oluşturun—tümü basit komutlarla sürülür.
- Özel Formüller: İş akışlarınızı hızlandırmak için özel formüller oluşturun.
- VBA Kodlama: VBA kodunu kolayca yazın ve uygulayın.
- Formül Yorumlama: Karmaşık formülleri kolayca anlayın.
- Metin Çevirisi: Elektronik tablolarınız içindeki dil engellerini aşın.
Formül ekleyerek alt toplam satırını hariç tutarak verileri filtreleme
Eğer bir Excel Tablosu kullanmak istemiyorsanız, SUBTOTAL formülünü kullanarak verilerinizin altına manuel bir toplam satırı koruyabilirsiniz. SUBTOTAL işlevi, Excel filtreleriyle dinamik olarak çalışacak şekilde özel olarak tasarlanmıştır - hesaplaması yalnızca görünür (filtrelenmiş) satırları yansıtır, ancak formül kendisi filtreleme sırasında gizlenmez veya hariç tutulmaz. Bu yaklaşım esnektir ve klasik veri aralıklarıyla iyi çalışır.
1. Amaçladığınız toplam satırının ilk hücresine, veri tablonuzun hemen altına aşağıdaki formülü girin (örneğin, değerleriniz B sütununda 2. satırdan 13. satıra kadar ise):
=SUBTOTAL(9, B2:B13)Formüldeki B2:B13’ü verilerinizin gerçek sütun ve satırlarına karşılık gelen aralıklarla değiştirin. Formüldeki 9 sayısı TOPLA işlevini belirtir; gerektiğinde farklı işlev numaraları kullanabilirsiniz (örneğin, ORTALAMA için 1, SAYI için 2 vb.).
2. Onaylamak için Enter tuşuna basın. Toplam satırı, görünür (filtrelenmemiş) satırlara dayalı olarak hemen hesaplanan toplamı gösterecektir. Excel’in yerleşik filtre düğmelerini kullanarak filtreler uyguladığınızda, toplam satırı tablonun en altında görünür kalır ve yalnızca görünür satırlar için geçerli ara toplamı gösterir.
SUBTOTAL formülünüzdeki aralığı ayarlayın. Bu formül, DİNAMİK ARALIK fonksiyonu gibi OFFSET veya INDEX kullanmadığınız sürece otomatik olarak genişlemez.Özet: SUBTOTAL formülü, çeşitli birleştirme işlevlerini destekler (ORTALAMA, SAYI vb.), bu nedenle finans, envanter ve raporlama iş akışlarında kullanılan özet satırları için esnektir.
VBA kodu kullanarak alt toplam satırını hariç tutarak verileri filtreleme
Daha büyük veri setleriyle çalışan veya alt toplam satırının her zaman görüntülenmesini otomatikleştirmek isteyen kullanıcılar için (filtre ayarlarına bakılmaksızın asla gizlenmez), VBA (Visual Basic for Applications) kullanarak filtreleme işlemini programlı olarak yönetebilirsiniz. Bu çözüm, özellikle veri aralığı veya toplam satırı konumu dinamik olarak değişen çalışma kitaplarında veya tüm kullanıcılar için tutarlılığı zorunlu kılmak istediğinizde çok yararlıdır.
Bu VBA örneği, veri aralığınızın son satırını kontrol ederek ve filtreler uygulandıktan sonra bile görünür kalmasını sağlayarak çalışır. Toplam satırının her zaman gösterilmesini sağlar ve makroyu bir düğmeye atayarak yeniden kullanılabilir.
Bu VBA çözümünü kullanma adımları:
1. Excel'de Geliştirici sekmesine gidin. Bu sekme görünmüyorsa, Excel Seçenekleri'nden Özelleştirme Şeridi bölümünde "Geliştirici" seçeneğini işaretleyerek etkinleştirmeniz gerekebilir.
2. Visual Basic'i açmak için tıklayın. Yeni pencerede, Ekle > Modül'e tıklayın ve aşağıdaki kodu yeni modüle yapıştırın:
Sub AlwaysShowTotalRow()
Dim ws As Worksheet
Dim lastRow As Long
On Error Resume Next
Set ws = ActiveSheet
' Get last used row in column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
' Ensure total row is visible before applying filter
ws.Rows(lastRow).Hidden = False
' If filtering is active, reapply filter
If ws.FilterMode Then
ws.ShowAllData
End If
' Make sure total row remains visible
ws.Rows(lastRow).Hidden = False
End Sub3. Kodu girdikten sonra VBA düzenleyicisini kapatın. Excel'de Alt + F8'e basın, AlwaysShowTotalRow seçin ve Çalıştır'a tıklayın. Makro, filtreler uygulandıktan sonra bile son satırın (toplam satırınız) görünür olmasını sağlayacaktır.
• Makro, toplam satırının A sütunundaki son kullanılan satır olduğunu varsayar. Verileriniz boş satırlar veya birden fazla özet içeriyorsa mantığı ayarlayabilirsiniz.
• Verileriniz sık sık değişiyorsa bu makroyu kolayca yeniden kullanmak için bir düğme atamanızı düşünün.
• Çalışma kitabınızda birden fazla bölüm varsa, doğru toplam satırını daha güvenilir bir şekilde bulmak için adlandırılmış bir aralık veya belirli bir sütun işaretleyici kullanın.
Avantajlar: Kullanıcı eylemlerine veya filtrelemeye bakılmaksızın toplam satırının görünürlüğünü otomatikleştirir. Gösterge panoları, raporlar veya paylaşılan çalışma kitapları için idealdir.
Sınırlamalar: Makro etkin çalışma kitabı ve uygun makro izinleri gerektirir. Daha karmaşık sayfa düzenleri için özelleştirme gerekebilir.
Özet: Bu VBA yaklaşımı, toplam satırlarının manuel kontrolünün güvenilmez veya uygun olmadığı otomatikleştirilmiş ortamlar için önerilir. Tüm kullanım durumlarında özet verilerin görünürlüğünü korumaya yardımcı olur.
En İyi Ofis Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi güçlendirin ve benzersiz bir verimlilik deneyimi yaşayın. Kutools for Excel, üretkenliği artırmak ve zamandan tasarruf etmek için300'den fazla Gelişmiş Özellik sunuyor. İhtiyacınız olan özelliği almak için buraya tıklayın...
Office Tab, Ofis uygulamalarına sekmeli arayüz kazandırır ve işinizi çok daha kolaylaştırır.
- Word, Excel, PowerPoint'te sekmeli düzenleme ve okuma işlevini etkinleştirin.
- Yeni pencereler yerine aynı pencerede yeni sekmelerde birden fazla belge açıp oluşturun.
- Verimliliğinizi %50 artırır ve her gün yüzlerce mouse tıklaması azaltır!
Tüm Kutools eklentileri. Tek kurulum
Kutools for Office paketi, Excel, Word, Outlook & PowerPoint için eklentileri ve Office Tab Pro'yu bir araya getirir; Office uygulamalarında çalışan ekipler için ideal bir çözümdür.
- Hepsi bir arada paket — Excel, Word, Outlook & PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans — dakikalar içinde kurulun (MSI hazır)
- Birlikte daha verimli — Ofis uygulamalarında hızlı üretkenlik
- 30 günlük tam özellikli deneme — kayıt yok, kredi kartı yok
- En iyi değer — tek tek eklenti almak yerine tasarruf edin