Excel'de kriterlere göre benzersiz değerleri nasıl toplarsınız?
Excel’de veri kümeleriyle çalışırken—sipariş kayıtları, finansal kayıtlar ya da anket sonuçları gibi—bir sütundaki benzersiz değerlerin toplamını, başka bir sütuna uygulanan filtreler veya belirli kriterlere göre hesaplama ihtiyacı sıkça karşınıza çıkar. Örneğin, Ad ve Sipariş olmak üzere iki sütundan oluşan bir veri tablosu düşünün. Her Ad için yalnızca benzersiz Sipariş değerlerini (yinelenenleri göz ardı ederek) toplamak istiyorsanız, bunu Excel’de nasıl etkili bir şekilde yapabilirsiniz? Basitçe eşleşen tüm öğeleri toplamak, yinelenenler nedeniyle şişkin sonuçlara yol açabileceği için bu ihtiyaç, birçok iş ve veri analizi senaryosunda oldukça yaygındır.
Aşağıdaki örnek ekran görüntüsü tipik bir senaryoyu göstermektedir: Bir Ad Listesi ve her ada karşılık gelen, yinelenen sipariş değerleri verildiğinde, her ad için ayrı ayrı benzersiz sipariş değerlerini toplayarak verileri özetlemek istiyorsunuz.
Bu görevde karşılaşılan yaygın zorluklar arasında, belirli kriterlere göre benzersiz girdileri doğru şekilde tanımlamak, yalnızca ilk oluşumun sayıldığından emin olmak ve filtrelenmiş verileri kopyalayıp yapıştırırken oluşabilecek manuel hataları önlemek yer alır. Dizi formülleri, Kutools ve Power Query gibi çeşitli pratik Excel çözümleri bu sorunların üstesinden gelmenize yardımcı olabilir; her biri farklı kullanım senaryolarına en uygun şekilde cevap verir.
- Dizi formülleriyle bir veya daha fazla kritere göre benzersiz değerleri toplayın
- Kutools for Excel’nin Gelişmiş Satırları Birleştir ile Kriterlere Göre Benzersiz Değerleri Toplama
- Diğer Yerleşik Excel Yöntemleri: Benzersiz toplam analizi için PivotTable kullanın
<h4"> Dizi formülleriyle bir veya daha fazla kritere göre benzersiz değerleri toplayın
Etkili ve esnek bir yaklaşım, belirli kriterleri karşılayan benzersiz değerleri özetlemenizi sağlayan dizi formüllerini kullanmaktır. Bu yöntem, özellikle veri veya kriterler değiştiğinde hesaplamanın otomatik olarak güncellenmesini istediğinizde mükemmel sonuç verir.
Başka bir sütundaki filtre veya koşula göre yalnızca bir sütundaki benzersiz değerleri toplamak için aşağıdaki formülü uygulayabilirsiniz:
1. Boş bir hücreye (örneğin,)E2), aşağıdaki formülü girin:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=D2,MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW($B$2:$B$12)-ROW($B$2)+1),$B$2:$B$12))Formülü onaylamadan önce şunları dikkatlice kontrol edin:
- A2:A12: Kriterlerin bulunduğu aralık (bu örnekte adlar).
- D2: Hedef koşulunuzun (örneğin belirli bir adın) bulunduğu hücre.
- B2:B12: Benzersiz değerlerin toplamını almak istediğiniz aralık.
Bu aralıkları gerçek veri yapınıza göre gerektiğinde esnekçe ayarlayabilirsiniz. Formül hatalarını önlemek için tüm aralıkların uzunluğunun eşit olduğundan emin olun.
2. Bu dizi formülünü etkinleştirmek için, yazdıktan sonra aynı anda Ctrl + Shift + Enter tuşlarına basın. Formülün çevresinde kıvırcık parantezler belirecek ve bu da onun bir dizi formülü olduğunu gösterecektir. Ardından, formülü özet sütununuzdaki diğer hücrelere kopyalamak için doldurma tutamacını aşağı doğru sürükleyin—böylece her öğe kendi benzersiz toplamını otomatik olarak alır.
Pratik ipucu: Excel 365 veya Excel 2021 kullanıyorsanız, BENZERSİZ ve ÇOKETOPLA gibi yeni dinamik dizi işlevleri bu tür hesaplamaları çok daha basit hale getirebilir; ancak yukarıdaki formül birçok Excel sürümünde güvenilir şekilde çalışır.
=SUMIF(A2:B12, UNIQUE(D2), B2:B12)
Daha fazla kriterle uğraşın: =TOPLA(SUMIFS(sum_range, criteria_range1, UNIQUE(criteria_range1), [criteria_range2, criteria2], …)
Önlemler:
- Excel 2019 veya önceki sürümleri kullanıyorsanız, dizi formülü girdiğinizden emin olun ()Ctrl + Shift + Enter). Excel 365/2021’de dinamik formüller için normal Enter tuşu yeterlidir.
- Eğer Aralık’ınız özellikle büyükse, dizi yaklaşımı yavaşlayabilir; bu nedenle çok büyük veri kümeleri için öncelikle verilerinizi filtrelemeyi veya diğer yöntemleri değerlendirmenizi öneririz.
- Ekstra boşluklara ve veri türü tutarlılığına dikkat edin—düzensiz metin veya sayı biçimlendirmesi, eşleşmeme hatalarına yol açabilir.
İpuçları: İki kritere göre tüm benzersiz değerleritoplamanız gerekiyorsa, aşağıdaki dizi formülünün genişletilmiş sürümü kullanılabilir:
=SUM(IF(FREQUENCY(IF($A$2:$A$12=E2,IF($B$2:$B$12=F2,MATCH($C$2:$C$12,$C$2:$C$12,0))),ROW($C$2:$C$12)-ROW($C$2)+1),$C$2:$C$12))Bu formül prensip olarak benzerdir, ancak Bsütunundan ek bir filtre desteği sunar (şimdi ikincil koşul olarak)F2 ile karşılaştırılır) ve C sütunundan benzersiz değerleri toplar. Bu formülü seçtiğiniz özet hücreye girdikten sonra, onaylamak için Ctrl + Shift + Enter tuşlarına basın ve gerektiğinde diğer özet satırlarına uygulayın.
Özet önerisi: Dizi formülleri çoğu senaryoda doğru sonuçlar verse de, gizli yinelenenleri (örneğin ekstra boşluklar veya metin biçimlendirme farklılıkları içeren kayıtları) her zaman kontrol edin ve özet alanlarınızın doğru şekilde filtrelenmiş listelerden veri çektiğinden emin olun.
Excel'in sihrini KUTOOLS AI ile keşfedin
- Akıllı Yürütme: Hücre işlemlerini gerçekleştirin, verileri analiz edin ve hepsini basit komutlarla oluşturulan grafiklerle görselleştirin.
- Özel formüller: İş akışlarınızı kolaylaştırmak için özelleştirilmiş formüller oluşturun.
- VBA Kodlama: VBA kodlarını zahmetsizce yazın ve uygulayın.
- Formül Yorumlama: Karmaşık formülleri kolayca anlayın.
- Metin Çevirisi: Elektronik tablolarınızdaki dil bariyerlerini aşın.
Kutools for Excel’nin Gelişmiş Satırları Birleştir ile Kriterlere Göre Benzersiz Değerleri Toplama
Kutools for Excel’nin Gelişmiş Satırları Birleştir özelliği, belirli bir koşula göre yalnızca benzersiz değerleri toplamayı inanılmaz derecede kolaylaştırır! Sadece birkaç tıklamayla verilerinizi akıllıca gruplandırır ve özel özet mantığı uygular—formüle gerek yok, zahmet yok, sadece doğru sonuçlar.
Adım 1: Veri Tablonuzu Seçin
Tüm tabloyu, başlıkları da içerecek şekilde vurgulayın.
Adım 2: Şuraya gidin: Kutools > İçerik > Gelişmiş Satırları Birleştir.
Adım 3: Gruplama Sütununu Ayarlayın
Açılan iletişim kutusunda, gruplamak istediğiniz sütunu (örn. Meyve) seçin ve Birincil Anahtar olarak İşlem bölümünde ayarlayın.
Adım 4: Benzersiz Toplam İçin Alanı Ayarlayın
Satış sütununu seçin ve hesaplama türünü (örn. Toplam) İşlem bölümünde belirleyin.
İpucu: Birleştirilmiş sonucu iletişim kutusunda önizleyebilirsiniz.
Adım 5: Tamam’a tıklayın. Tablo artık müşteriye göre gruplanmıştır; her müşteri için benzersiz ürün tutarlarının toplamı görüntülenir.
Diğer Yerleşik Excel Yöntemleri: Benzersiz toplam analizi için PivotTable kullanın
Excel’in Özet Tablo özelliği, verileri kriterlere göre özetlemenin başka bir güçlü yerleşik yoludur. Özet Tablolar varsayılan olarak doğrudan benzersiz değerleri toplamaz; ancak Excel 2013 ve sonraki sürümlerinde, belirtilen bir alandaki benzersiz girişlerin sayısını analiz etmenize yardımcı olan Benzersiz Sayım hesaplamasını destekler. Doğrudan benzersiz değerlerin toplamını hesaplamasa da, Benzersiz Sayım özelliğini manuel düzeltme veya hesaplanan alan ile birlikte kullanarak benzer bir özet elde edebilirsiniz.
Avantajlar: Özet Tablolar, formül ezberleme veya VBA kodlama gerektirmez ve son derece esnek sürükle-bırak arayüzleri sunar. Periyodik raporlama, grup analizi, hızlı genel bakış veya ekip içi iş birliği gibi senaryolar için idealdir. Ancak özetleme ve analiz açısından en iyisidir; ileri düzey hesaplamalar veya otomasyon için formül yazmaya uygun değildir.
Benzersiz toplam analizi için Özet Tablo kullanımını şu şekilde yapabilirsiniz:
- Veri aralığınızı seçin (örneğin,)A1:B12, başlıkları dahil) ve Ekle > Özet Tablo seçeneğine tıklayın. İletişim kutusunda, Özet Tabloyu yeni bir çalışma sayfasına mı yoksa mevcut bir çalışma sayfasına mı yerleştireceğinizi belirleyin.
- Özet Tablo Alan Listesinde, Ad alanını Satırlar bölümüne, Sipariş alanını ise Değerler bölümüne sürükleyin.
- Sipariş girdileri için Değerler bölümünde, açılır oku tıklayın > Değer Alan Ayarları > Toplam olarak ayarlayın (yinelenenleri de içeren toplam sipariş miktarını gösterir).
Sınırlamalar:
- Benzersiz Sayım (Distinct Count) işlevi yalnızca Excel 2013 ve sonraki sürümlerde kullanılabilir; önceki sürümlerde daha manuel işlemler gerekir.
Özetle, Özet Tablo etkileşimli özetleme ve analiz için mükemmeldir; ancak gerçek anlamda benzersiz toplam hesaplamalarına ihtiyacınız varsa, formül veya VBA tabanlı yaklaşımlarla birlikte kullanmayı düşünün.
İlgili diğer makaleler:
- Excel'de Tek Kriterle Birden Fazla Sütunu Toplama
- Excel’de genellikle birden fazla sütunu belirli bir kritere göre toplama ihtiyacınız olur. Örneğin, aşağıdaki ekran görüntüsünde gösterilen veri aralığında, Ocak, Şubat ve Mart aylarında KTE’nin toplam değerlerini almak istiyorum.
- Excel'de Satırlarda veya Sütunlarda VLOOKUP ve Eşleşmeleri Toplama
- VLOOKUP ve TOPLA işlevlerini kullanmak, belirtilen kriterlere hızlıca ulaşmanızı ve bunlara karşılık gelen değerleri aynı anda toplamanızı sağlar. Bu makalede, Excel’de satır veya sütunlarda ilk eşleşen ya da tüm eşleşen değerleri VLOOKUP ile nasıl toplayabileceğinizi göstermek üzere iki yöntem sunuyoruz.
- Excel'de Ay ve Yıla Göre Değerleri Toplama
- Elinizde bir veri aralığınız varsa—A sütununda tarihler ve B sütununda sipariş sayıları yer alıyorsa—başka bir sütundan alınan ay ve yıla göre bu sayıları toplamanız gerekebilir. Bu durumda, Ocak 2016’ya ait toplam sipariş sayısını hesaplayarak aşağıdaki sonucu elde etmek istiyorum. Bu makalede, Excel’de bu görevi gerçekleştirmenize yardımcı olacak bazı pratik ipuçlarını paylaşacağım.
- Excel'de Metin Kriterine Göre Değerleri Toplama
- Excel’de, metin kriterlerini içeren başka bir sütuna göre değerleri toplamayı denediniz mi? Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi çalışma sayfamda bir veri aralığım var. Şimdi, A sütunundaki metin değerleri belirli bir kriteri karşıladığında—örneğin “KTE” içerdiğinde—bu değerlere karşılık gelen B sütunundaki tüm sayıları toplamak istiyorum.
- Excel'de Açılır Liste Seçimine Göre Değerleri Toplama
- Aşağıdaki ekran görüntüsünde gösterildiği gibi, bir Kategori ve bir Tutar sütunu içeren bir tablonuz var. Ayrıca tüm kategorileri içeren bir veri doğrulama Açılır Liste’niz bulunuyor. Bu listeden herhangi bir kategori seçtiğinizde, B sütunundaki ilgili tüm değerleri toplayıp sonucu belirtilen bir hücreye yerleştirmek istiyorsunuz. Örneğin, Açılır Liste’den “CC” kategorisini seçtiğinizde, B5 ve B8 hücrelerindeki değerler otomatik olarak toplanmalı ve 40 + 70 = 110 sonucu elde edilmelidir. Peki bunu nasıl başarabilirsiniz? Bu makaledeki yöntem size yardımcı olabilir.
En İyi Office Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi üst seviyeye taşıyın ve verimlilikten önce hiç yaşanmamış bir deneyim yaşayın.Kutools for Excel, verimliliği artırmak ve Zamanı Kaydet için 300’den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen edinmek için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli arayüz getirir ve işlerinizi çok daha kolay hale getirir
- Word, Excel, PowerPoint, Publisher, Access, Visio ve Project’te sekmeli düzenleme ve okuma özelliğini etkinleştirin.
- Birden fazla belgeyi yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açın ve oluşturun.
- Günlük verimliliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri — tek bir kurulum dosyasında!
Kutools for Office paketi; Excel, Word, Outlook ve PowerPoint için eklentileri ile Office Tab Pro’yu içerir ve Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek kurulum, tek lisans— dakikalar içinde hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı verimlilik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi değer— tek tek eklenti satın almaya göre tasarruf sağlar