Excel'de kriterlere göre benzersiz değerlerin toplamını nasıl hesaplarsınız?
Sipariş kayıtları, finansal kayıtlar veya anket sonuçları gibi Excel veri setleriyle çalışırken, bir sütundaki benzersiz değerlerin toplamını başka bir sütundaki filtreler veya kriterlere dayanarak hesaplama ihtiyacıyla sık karşılaşırsınız. Örneğin, iki sütun içeren bir veri tablosu düşünelim: İsim ve Sipariş. Her bir İsim için yalnızca benzersiz Sipariş değerlerinin toplamını (tekrar eden değerleri hariç tutarak) hesaplamak istiyorsanız, bunu Excel'de etkili bir şekilde nasıl yapabilirsiniz? Bu, özellikle tüm eşleşen öğelerin basit toplamının tekrarlar nedeniyle şişirilmiş sonuçlar vereceği birçok iş veya veri analizi senaryosunda ortak bir gerekliliktir.
Aşağıdaki örnek ekran görüntüsü, tipik bir senaryoyu göstermektedir: tekrarlar da dahil olmak üzere isimlerin ve karşılık gelen sipariş değerlerinin bir listesi verildiğinde, her bir isim için ayrı ayrı benzersiz sipariş değerlerini toplayarak verileri özetlemek istersiniz.
Bu görevdeki yaygın zorluklar arasında belirli kriterlere dayanarak benzersiz girişleri tanımlama, yalnızca ilk oluşumun sayılmasını sağlama ve filtrelenmiş verileri kopyalarken ortaya çıkabilecek manuel hataları önleme bulunur. Dizi formülleri, Kutools ve Power Query gibi çeşitli pratik Excel yaklaşımları bu sorunu çözmeye yardımcı olabilir; her biri farklı kullanım senaryolarına uygundur.
- Dizi formülleri ile bir veya daha fazla kritere göre benzersiz değerleri toplama
- Kutools for Excel’in Gelişmiş Satırları Birleştir Özelliği ile Kritere Göre Benzersiz Değerleri Toplama
- Diğer Yerleşik Excel Yöntemleri: Benzersiz toplam analizi için Pivot Tablo kullanın
<h4"> Dizi formülleri ile bir veya daha fazla kritere göre benzersiz değerleri toplama
Etkili ve esnek bir yaklaşım, dizi formüllerini kullanmaktır. Bu, belirli kriterleri karşılayan benzersiz değerleri özetlemenize olanak tanır. Veri veya kriterler değiştiğinde hesaplamanın otomatik olarak güncellenmesini istediğinizde özellikle iyi çalışır.
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) şu 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 dikkatlice kontrol edin ki:
- A2:A12: Kriterleri içeren aralık (bu durumda isimler).
- D2: Hedef kriterinizin (belirli bir isim gibi) bulunduğu hücre.
- B2:B12: Tek olarak toplamak istediğiniz değerler aralığı.
Gerçek veri yapınıza göre bu aralıkları gerektiği gibi ayarlayabilirsiniz. Tüm aralıkların uzunluğunun aynı olduğundan emin olun, aksi takdirde formül hataları oluşabilir.
2. Formülü yazdıktan sonra Ctrl + Shift + Enter tuşlarına aynı anda basarak bu dizi formülünü etkinleştirin. Formülün çevresinde kıvırcık parantezler belirecektir, bu da bir dizi formülü olduğunu gösterir. Ardından, özet sütunundaki her bir değeri için formülü kopyalamak üzere dolgu tutamacını aşağı çekin. Böylece her bir öğe otomatik olarak doğru benzersiz toplamı alır.
Pratik ipucu: Excel 365 veya Excel 2021 kullanıyorsanız, UNIQUE ve SUMIFS gibi yeni dinamik dizi fonksiyonları bazı hesaplamaları daha da basitleştirebilir, ancak yukarıdaki formül birçok Excel sürümünde güvenilir bir şekilde çalışır.
=TOPLA.EĞER(A2:B12; BENZERSİZ(D2); B2:B12)
Daha fazla kriterle başa çıkma: =TOPLA(TOPLA.EĞERLER(toplam_aralığı; kriter_aralığı1; BENZERSİZ(kriter_aralığı1); [kriter_aralığı2; kriter2]; ...)
Önlemler:
- Excel 2019 veya daha eski sürümlerdeyseniz, dizi formülü girişi (Ctrl + Shift + Enter) kullandığınızdan emin olun. Excel 365/2021 için dinamik formüllerde normal Enter yeterli olabilir.
- Veri aralıklarınız özellikle büyükse, dizi yaklaşımı yavaşlayabilir, bu yüzden önce verilerinizi filtrelemeyi düşünün veya çok büyük veri setleri için diğer yöntemleri kullanın.
- Fazladan boşluklar veya veri türü tutarlılığı konusunda dikkatli olun — düzensiz metin veya sayı biçimlendirmesi eşleşme hatasına neden olabilir.
İpuçları: İki kritere göre tüm benzersiz değerleri toplamanız gerekiyorsa, aşağıdaki dizi formülünün uzantısını kullanabilirsiniz:
=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 prensipte benzerdir, ancak B sütunundan (şimdi F2’ye ikincil bir koşul olarak kıyaslanır) ekstra bir filtre destekler ve C sütunundan benzersiz değerleri toplar. Bu formülü seçilen özet hücresine girdikten sonra, onu onaylamak için Ctrl + Shift + Enter tuşlarına basın ve ardından diğer özet satırlarına gerektiğinde uygulayın.
Özet önerisi: Dizi formülleri çoğu senaryo için doğru sonuçlar verirken, gizli tekrarları (fazladan boşluk veya metin biçimi farklılıkları olanlar gibi) dikkatlice kontrol edin ve özet alanlarınızın doğru şekilde filtrelenmiş listelerden veri çektiğinden emin olun.
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.
Kutools for Excel’in Gelişmiş Satırları Birleştir Özelliği ile Kritere Göre Benzersiz Değerleri Toplama
Kutools for Excel’in Gelişmiş Satırları Birleştir özelliği, belirli bir koşula göre yalnızca benzersiz değerleri toplamayı kolaylaştırır! Birkaç tıklamayla verilerinizi akıllıca gruplandırır ve özel özet mantığı uygular—formülsüz, uğraşsız, yalnızca doğru sonuçlar.
Adım 1: Veri Tablonuzu Seçin
Başlıklar da dahil olmak üzere tüm tabloyu vurgulayın.
Adım 2: Kutools > Metin > Gelişmiş Satırları Birleştir’e gidin.
Adım 3: Gruplama Sütununu Ayarlayın
Açılan diyalogda, gruplamak istediğiniz sütunu seçin (örneğin Meyve), İşlem bölümünde Anahtar olarak ayarlayın.
Adım 4: Benzersiz Toplam Alanını Ayarlayın
Satış sütununu seçin, İşlem bölümünde istediğinizi hesaplama türünü (örneğin Toplam) ayarlayın.
İpucu: Diyalogda birleştirilmiş sonucu önizleyebilirsiniz.
Adım 5: Tamam'a tıklayın. Tablo artık müşteriye göre gruplanmış, her birinin benzersiz ürün miktarlarının toplamıyla birlikte.
Diğer Yerleşik Excel Yöntemleri: Benzersiz toplam analizi için Pivot Tablo kullanın
Excel'in PivotTable özelliği, kriterlere göre verileri özetlemek için başka güçlü yerleşik bir yaklaşım sunar. PivotTablolar varsayılan olarak doğrudan benzersiz değerleri toplamaz, ancak Excel 2013'ten sonra Distinct Count hesaplamasını destekler, bu da size belirli bir alan için benzersiz giriş sayısını analiz etmenize yardımcı olur. Bu, doğrudan benzersiz değer toplamını hesaplamasa da, Distinct Count'u manuel ayarlamayla veya hesaplanmış bir alanla birlikte kullanarak benzer bir özet oluşturabilirsiniz.
Avantajlar: PivotTablolar formül ezberlemeyi veya VBA kodlamayı gerektirmez ve oldukça esnek sürükle-bırak arabirimler sunar. Periyodik raporlama, grup analizi, hızlı genel bakışlar veya ekiplar arasında işbirliği yaparken uygun. Ancak en iyi özet ve analiz için uygundur, ileri hesaplama veya otomasyon için formüller oluşturmaya uygun değildir.
Benzersiz toplam analizi için bir PivotTablo nasıl kullanılır:
- Veri aralığınızı seçin (örneğin, başlıklar da dahil olmak üzere A1:B12), ve Ekle > PivotTable’a gidin. Diyalogda, PivotTabloyu yeni bir çalışma sayfasına mı yoksa mevcut bir çalışma sayfasına mı yerleştireceğinizi seçin.
- PivotTable Alan Listesinde, İsim'i Satırlar alanına, Sipariş'i Değerler alanına sürükleyin.
- Sipariş girişleri için Değerler alanında, açılır ok > Değer Alanı Ayarları > Toplam olarak ayarlayın (tekrarlar dahil toplam sipariş toplamını gösterir).
Sınırlamalar:
- Distinct Count fonksiyonu yalnızca Excel 2013 veya daha yeni sürümlerde mevcuttur; daha eski sürümler daha fazla manuel işlem gerektirir.
Sonuç olarak, PivotTable etkileşimli özet ve analiz için harika olsa da, gerçek benzersiz toplam hesaplamalarına ihtiyacınız varsa onu formül veya VBA temelli yaklaşımlarla birlikte kullanmayı düşünün.
Daha fazla ilgili makale:
- Excel'de Tek Kritere Göre Birden Fazla Sütunu Toplama
- Excel'de birden fazla sütunu tek bir kritere göre toplamanız gerekebilir. Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi bir veri aralığım var ve Ocak, Şubat ve Mart aylarındaki KTE'nin toplam değerlerini elde etmek istiyorum.
- Excel'de Vlookup ve Eşleşen Değerleri Toplama
- Vlookup ve sum fonksiyonu kullanmak, belirli bir kriteri hızlıca bulmanıza ve aynı anda karşılık gelen değerleri toplamanıza yardımcı olur. Bu makalede, size Excel'deki satırlarda veya sütunlarda ilk veya tüm eşleşen değerleri vlookup ve toplamak için iki yöntemi göstereceğiz.
- Excel'de Ay ve Yıl Bazında Değerleri Toplama
- Eğer bir veri aralığınız varsa, sütun A'da bazı tarihler ve sütun B'de sipariş sayısı varsa, başka bir sütundan ay ve yıla göre sayıları toplamanız gerekebilir. Bu durumda, Ocak 2016'nın toplam siparişlerini hesaplamak istiyorum ve aşağıdaki sonucu elde etmek istiyorum. Bu makalede, bu işi Excel'de çözmek için bazı püf noktalarından bahsedeceğim.
- Excel'de Metin Kriterlerine Göre Değerleri Toplama
- Excel'de, bir başka sütundaki metin kriterlerine göre değerleri toplamayı denediniz mi? Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi bir çalışma sayfasında bir veri aralığı varsa ve sütun A'daki metin değerleri belli bir kriteri karşılayan sütun B'deki tüm sayıları toplamak istiyorsanız (örneğin, sütun A'daki hücreler KTE içeriyorsa sayıları toplamak gibi).
- 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 sütunu ve bir Miktar sütunu içeren bir tablonuz var ve tüm kategorileri içeren bir veri doğrulama açılır listesi oluşturmuşsunuzdur. Açılır listeden herhangi bir kategori seçildiğinde, sütun B'deki tüm karşılık gelen hücre değerlerini toplamak ve sonucu belirli bir hücreye yerleştirmek istiyorsunuz. Örneğin, açılır listeden CC kategorisini seçtiğinizde, B5 ve B8 hücrelerindeki değerleri toplamanız ve toplam 40+70=110 sonucunu elde etmeniz gerekiyor. Bunu nasıl başarabilirsiniz? Bu makaledeki yöntem size yardımcı olabilir.
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