Excel’de bir aralıkta birden fazla kritere göre benzersiz değerlerin sayısını nasıl alabilirsiniz?
Birçok pratik senaryoda, değerleri yalnızca saymakla kalmayıp verilerinizde belirli koşulları karşılayan kaç benzersiz öğe olduğunu belirlemeniz gerekir. Örneğin, belirli bir satış temsilcisinin kaç farklı ürün sattığını ya da belirli bir zaman diliminde kaç benzersiz sipariş verildiğini öğrenmek isteyebilirsiniz. Excel’de bu tür görevleri verimli şekilde yerine getirmek, uygun formüllerin yanı sıra PivotTable gibi gelişmiş özelliklerden veya hatta özel VBA çözümlerinden yararlanmayı gerektirir. Bu makalede, tek ya da çoklu kriterlere göre bir aralıktaki benzersiz değerlerin sayısını bulmak için adım adım talimatlar ve kullanışlı ipuçlarıyla birlikte birkaç pratik yöntemi inceleyeceğiz.
Tek kritere göre Bir aralıktaki benzersiz değerlerin sayısı
İki belirtilen tarihe göre Bir aralıktaki benzersiz değerlerin sayısı
İki kritere göre Bir aralıktaki benzersiz değerlerin sayısı
Üç kritere göre Bir aralıktaki benzersiz değerlerin sayısı
Bir aralıktaki benzersiz değerlerin sayısı ile PivotTable (Benzersiz Sayım, Excel 2013+)
VBA Kodu ile Bir aralıktaki benzersiz değerlerin sayısı (karmaşık/otomatik durumlar için)
Tek kritere göre Bir aralıktaki benzersiz değerlerin sayısı
Yaygın bir senaryoyu ele alalım: Tom’un kaç farklı ürün sattığını saymak istiyorsunuz. Bu yöntem, veri kümeniz basit olduğunda ve benzersiz değerleri tek bir koşula göre (örneğin, yalnızca bir kişinin satış kayıtlarına dayalı olarak) değerlendirmek istediğinizde idealdir. Doğrudan bir yaklaşımdır; ancak dizi formüllerinin dikkatli kullanılmasını gerektirir.
Bu senaryo için aşağıdaki formülü boş bir hücreye (örneğin G2 hücresine) girin:
=SUM(IF("Tom"=$C$2:$C$20,1/(COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20)),0))
Formülü yazdıktan sonra, onu dizi formülü olarak onaylamak için yalnızca Enter’a değil, Ctrl + Shift + Enter tuşlarına basın. Formül çubuğunda (Formül Çubuğu), formülün çevresinde kıvırcık ayraçlar belirecek ve sonucu aşağıdaki örnekteki gibi anında göreceksiniz:
Not:
- «Tom», sonuçları filtrelemek için kullanmak istediğiniz koşuldur. Daha fazla esneklik istiyorsanız «Tom» ifadesini başka bir hücreye yapılan başvuruyla (örneğin $F$2) değiştirebilirsiniz.
- $C$2:$C$20, değerlendirilecek satıcı adlarını içerir.
- $A$2:$A$20, benzersiz ürün sayısını almak istediğiniz sütundur.
- Veri aralığı değişirse, başvuruları buna göre güncellemeyi unutmayın.
İpucu: Excel 365 veya Excel 2019 ve sonraki sürümleri kullanıyorsanız, daha kolay formüller için BENZERSİZ ve FİLTRE işlevlerini deneyebilirsiniz.
#BÖL/0! gibi hatalarla karşılaşırsanız, kriterlerinizin ve aralıklarınızın eşit uzunlukta olduğundan emin olmak için bunları tekrar kontrol edin.
İki belirtilen tarihe göre Bir aralıktaki benzersiz değerlerin sayısı
Belirli bir tarih aralığındaki benzersiz öğe sayısını belirlemeniz gerektiğinde — örneğin, 1 Eylül 2016 ile 30 Eylül 2016 tarihleri arasında satılan tüm benzersiz ürünler — bu yaklaşımı kullanabilirsiniz. Bu yöntem, özellikle aylık, üç aylık veya özel tarih aralıkları gibi belirli dönemlerdeki veri eğilimlerini analiz ederken büyük fayda sağlar. Ancak tarih biçimlendirmesine dikkat edin; çalışma sayfanızdaki tarih değerleriyle tam olarak eşleşmelidir.
Sonucu görüntülemek istediğiniz boş bir hücreye aşağıdaki formülü yerleştirin:
=SUM(IF($D$2:$D$20=DATE(2016,9,1)),1/COUNTIFS( $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "="&,DATE(2016,9,1))),0)
Formülü girdikten sonra dizi formülü olarak çalıştırmak için Ctrl + Shift + Enter tuşlarına basın. Aşağıdaki ekran görüntüsü sonucu göstermektedir:
Not:
- 2016,9,1 ve 2016,9,30, başlangıç ve bitiş tarihi kriterleridir. Bunları istediğiniz gibi değiştirebilir veya dinamik tarih filtreleri için hücre başvuruları bile kullanabilirsiniz.
- $D$2:$D$20, kontrol edilecek tarih girişlerini içerir.
- $A$2:$A$20, benzersiz şekilde saymak istediğiniz ürün ya da öğe sütunudur.
- Tarihlerinizin metin değil, geçerli Excel tarihleri olarak saklandığından emin olun. Sonuç beklendiği gibi görünmüyorsa, tarih biçimlendirmenizi ve aralıklarınızı gözden geçirin.
İpucu: Bölgesel tarih biçimlendirmeyle ilgili sorunlardan kaçınmak için DATE(yıl, ay, gün)Fonksiyonunu kullanın. Dinamik aralıklar oluştururken, açıklık açısından adlandırılmış aralıkları tercih edin.
İki kritere göre Bir aralıktaki benzersiz değerlerin sayısı
Tom’un eylül ayında sattığı ürünleri, isim ve Tarih Aralığı bilgisini benzersiz sayımınıza dahil ederek analiz etmek istediğinizi varsayalım. Bu senaryo, dönem bazlı performans incelemeleri veya bölümlere ayrılmış analizler için yaygındır. Kriterleriniz arttıkça formül daha da karmaşıklaşır ve veri doğruluğuna dikkat etmek daha da önemli hale gelir.
Aşağıdaki formülü H2 gibi herhangi bir boş hücreye girin:
=SUM(IF(("Tom"=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))),1/COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "="&,DATE(2016,9,1))),0)
Formülü yazdıktan sonra Ctrl + Shift + Enter tuşlarına basarak onaylayın. Benzersiz sayımı hemen göreceksiniz— aşağıdaki görseli inceleyin:
Notlar:
- «Tom» bir isim kriteridir; «2016,9,1» ve «2016,9,30» ise tarih aralığınızın sınırlarıdır. Bunları istediğiniz gibi ayarlayabilir veya dinamik hale getirmek için hücre başvurularını kullanabilirsiniz.
- $C$2:$C$20, personel (veya başka bir ilk kriter) sütunudur; $D$2:$D$20, tarih sütunudur; $A$2:$A$20 ise benzersiz olarak sayılacak öğeleri içerir.
- Hataları önlemek için tüm aralıkların eşit uzunlukta olması şarttır.
«Veya» koşulları kullanmak isterseniz — örneğin, Tom tarafından veya Güney bölgesinde satılan benzersiz ürünleri saymak — aşağıdaki formülü kullanabilirsiniz. Bu, daha geniş arama koşullarına olanak tanır; ancak veriler her iki kriteri de karşılıyorsa sonuçlar çakışabilir:
=TOPLA(--(FREKANS(EĞER(("Tom"=$C$2:$C$20)+("South"=$B$2:$B$20), COUNTIF($A$2:$A$20, "0))
Ctrl + Shift + Enter tuşlarına basmayı unutmayın! Sonuçları aşağıdaki gibi göreceksiniz:
İpucu: VEYA kriterlerini uygularken, aynı kaydın her iki koşulu da sağlaması durumunda çift sayım riskine dikkat edin. Büyük veri kümelerinde performans etkilenebilir.
Üç kritere göre Bir aralıktaki benzersiz değerlerin sayısı
Bazen analiziniz, örneğin Eylül ayında yalnızca Kuzey bölgesinde Tom tarafından satılan benzersiz ürünleri belirlemek gibi üç veya daha fazla koşulu bir arada değerlendirmeyi gerektirebilir. Bu tür çok boyutlu veri analizleri, raporlama ve hedefe yönelik iş içgörülerinde yaygındır. Böyle karmaşık mantık yapılarıyla çalışırken, referansların dikkatli yönetilmesi kritik öneme sahiptir.
Bu dizi formülünü boş bir hücreye (örneğin I2) yerleştirin:
=SUM(IF(("Tom"=$C$2:$C$20)*($D$2:$D$20=DATE(2016,9,1))*("North"=$B$2:$B$20),1/COUNTIFS($C$2:$C$20, "Tom", $A$2:$A$20, $A$2:$A$20, $D$2:$D$20, "="&DATE(2016,9,1), $B$2:$B$20, "North")),0)
Tamamlamak için Ctrl + Shift + Enter tuşlarına basın. Referans olması için örnek bir sonuç aşağıdadır:
Gelişmiş koşullar için tüm aralıkların tutarlı olduğundan ve veri türlerinin (örneğin tarih ve metin) doğru olduğundan emin olun. Hizasızlıklar hatalara veya yanıltıcı sonuçlara neden olabilir.
İpuçları:
- Büyük veri kümelerinde performans sorunlarıyla karşılaşırsanız, formülü parçalara ayırmanızı veya Excel’in PivotTable çözümünü kullanmanızı düşünün.
- Tüm kriterler için adlandırılmış aralıklar veya hücre başvuruları kullanmak, okunabilirliği artırır ve formül hatalarını en aza indirir.
- Sık kullandığınız formülleri, adlandırılmış hücre başvurularında veya özel işlevlerde kaydetmeyi düşünün.
Bir aralıktaki benzersiz değerlerin sayısı, PivotTable ile (Benzersiz Sayım, Excel 2013+)
Excel 2013 veya sonraki sürümleri kullanan kullanıcılar için PivotTable, tek ya da çoklu kriterlere göre Bir aralıktaki benzersiz değerlerin sayısı işlemi yapmanın formül dışı, daha etkileşimli bir alternatifidir. Benzersiz Sayım özelliği, büyük veri kümelerini özetlemenize ve filtrelemenize yardımcı olarak bu yöntemi özellikle dinamik, rapor temelli ortamlar için uygundur. Ancak Excel’in önceki sürümlerinin PivotTable içinde Benzersiz Sayım özelliğini desteklemediğini unutmayın.
Bu yöntemi nasıl kullanırsınız:
- Veri kümenizi seçin ve Ekle > PivotTablo yolunu izleyin.
- PivotTablo Oluştur iletişim kutusunda, PivotTabloyu nereye yerleştireceğinizi seçin, "Bu verileri Veri Modeline ekle" onay kutusunu işaretleyin ve ardından Tamam’a tıklayın.
- Benzersiz olarak saymak istediğiniz alanı (örneğin Ürün) Değerler alanına sürükleyin. Varsayılan olarak «… Sayısı» şeklinde görüntülenecektir.
- Değerler alanındaki alana tıklayın ve Değer Alan Ayarları seçeneğini belirleyin.
- Açılan iletişim kutusunda aşağı kaydırın ve Benzersiz Sayım seçeneğini belirleyin. (Bu seçenek yalnızca Excel 2013 veya sonraki sürümlerde mevcuttur ve PivotTablo, «Bu verileri Veri Modeline ekle» seçeneği etkinleştirilerek oluşturulduğunda görünür.)
- Tek veya çoklu koşullar uygulamak için kriter alanlarınızı (örneğin Satıcı, Bölge, Tarih) Filtreler veya Satırlar/Sütunlar alanına ekleyin.
- PivotTablonuz artık seçtiğiniz kriterlere göre filtrelenmiş değerlerin benzersiz sayısını gösterecektir.
Avantajlar: Son derece görseldir, formüllerde değişiklik yapmanıza gerek kalmadan filtreleri kolayca ayarlamanızı sağlar ve etkileşimli raporlamaya mükemmeldir.
Sınırlamalar: Excel 2010 veya önceki sürümlerde kullanılamaz; yeni veri ekledikten sonra Özet Tablo’yu manuel olarak yenilemeniz gerekir.
Pratik ipucu: Aynı kayıt içinde istenmeyen yinelenenler varsa, bunların olmadığını her zaman kontrol edin. Benzersiz Sayım seçeneğini göremiyorsanız, Özet Tablo’yu yeniden oluşturun ve “Bu veriyi Veri Modeli’ne ekle” seçeneğini işaretleyin.
VBA Kodu ile Bir aralıktaki benzersiz değerlerin sayısı (karmaşık/otomatik senaryolar için)
Çok büyük veri kümeleriyle çalışırken veya analizi sık sık tekrarlarken, çeşitli kriterlere göre Bir aralıktaki benzersiz değerlerin sayısı işlemini otomatik olarak gerçekleştirmeniz gerekebilir. Bu durumlarda bir VBA makrosu idealdir; çünkü kurulumdan sonra manuel müdahale olmadan çoklu koşullu filtreleme dahil farklı mantık yapılarını hızlıca işleyebilir. Ancak VBA, normal Excel özelliklerinden daha ileri seviyededir; bu nedenle makrolara aşina kullanıcılar veya sürekli analitik ihtiyaçları olan kişiler tarafından kullanılması önerilir.
İşlem adımları:
- VBA düzenleyicisini açmak için Alt + F11 tuşlarına basın. Düzenleyicide, yeni bir modül oluşturmak için Ekle > Modül yolunu izleyin.
- Aşağıdaki VBA kodunu modüle kopyalayın ve yapıştırın:
Sub CountUniqueWithCriteria()
Dim DataRange As Range
Dim CriteriaRange As Range
Dim CriteriaValue As Variant
Dim Dict As Object
Dim i As Long
Dim UniqueCount As Long
Dim ResultCell As Range
Set Dict = CreateObject("Scripting.Dictionary")
' Prompt for range settings
Set DataRange = Application.InputBox("Select data range (items to count):", "KutoolsforExcel", Type:=8)
Set CriteriaRange = Application.InputBox("Select criteria range (e.g. Salesperson):", "KutoolsforExcel", Type:=8)
CriteriaValue = Application.InputBox("Enter criteria value:", "KutoolsforExcel", "", Type:=2)
Set ResultCell = Application.InputBox("Select cell for result output:", "KutoolsforExcel", Type:=8)
On Error Resume Next
For i = 1 To DataRange.Rows.Count
If CriteriaRange.Cells(i, 1).Value = CriteriaValue Then
If Not Dict.Exists(DataRange.Cells(i, 1).Value) Then
Dict.Add DataRange.Cells(i, 1).Value, 1
End If
End If
Next i
UniqueCount = Dict.Count
ResultCell.Value = UniqueCount
MsgBox "Unique count for '" & CriteriaValue & "': " & UniqueCount, vbInformation, "KutoolsforExcel"
End Sub- VBA düzenleyicisini kapatın ve çalışma sayfanıza dönün. Alt + F8 tuşlarına basın, CountUniqueWithCriteria seçeneğini belirleyin ve makroyu çalıştırın.
- Verilerinize göre aralıkları ve kriterleri belirtmek için giriş istemlerini takip edin. Sonuç, seçtiğiniz hücrede ve ayrıca bir ileti kutusunda görünecektir.
Parametre açıklamaları ve notlar:
- Bu makro şu anda tek bir kriter için ayarlanmıştır. Birden fazla kriteri desteklemek üzere genişletmek istiyorsanız, döngü içindeki
If ... Thenmantığını güncelleyin. - Çalışma kitabınızı her zaman makroları çalıştırmadan önce kaydedin; çünkü yapılan değişiklikler geri alınamaz.
- Çalıştırma hatalarıyla karşılaşırsanız, Excel ayarlarınızda makroları etkinleştirin.
- Bu yöntem, manuel formüllerin zahmetli olacağı daha büyük veya sık güncellenen veriler için iyi sonuç verir.
Faydalar: Son derece özelleştirilebilir ve otomatikleştirilebilir; büyük ve değişken veri kümelerini verimli şekilde işler. Gelişmiş veya tekrarlayan iş akışı ihtiyaçlarınıza mükemmel uyum sağlar.
Dezavantajlar: Makro izinleri gerektirir ve yeni başlayanların VBA işlemlerine alışması biraz zaman alabilir.
Kriterlere dayalı benzersiz değer sayımlarıyla çalışırken her zaman aralık referanslarınızı doğrulayın ve tüm kriter sütunlarının boyutlarının eşleştiğinden emin olun. Uyumsuz aralıklar, hataların veya yanlış sonuçların yaygın bir kaynağıdır. Formüller beklenmedik sonuçlar verirse gizli biçimlendirme sorunlarını veya boş hücreleri kontrol edin. Performans açısından kritik senaryolarda PivotTable ve VBA, dizi formüllerine karşı sağlam alternatifler sunar. Rahatlık seviyenize ve veri kümenizin karmaşıklığına en uygun çözümü seçin. Unutmayın ki Kutools for Excel, karmaşık çalışma kitaplarında bu tür görevleri daha da verimli hale getirmek için ek araçlar ve kısayollar sağlar.
En İyi Office Üretkenlik Araçları
Excel becerilerinizi Kutools for Excel ile geliştirin ve verimlilikte hiç yaşamadığınız bir deneyim yaşayın.Kutools for Excel, üretkenliğ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 bir arayüz kazandırarak 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.
- Belgeleri yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açıp oluşturun.
- Günlük üretkenliğ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 eklentilerini ve ayrıca Office Tab Pro’yu içeren kapsamlı bir çözümdür; 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— birkaç dakikada hazır (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamalarında akıcı üretkenlik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— ayrı ayrı eklenti satın almaya göre tasarruf sağlar