Excel'de birden fazla kritere göre benzersiz değerleri nasıl sayarsınız?
Birçok pratik senaryoda, genellikle yalnızca değerleri saymak yerine, verilerinizdeki belirli koşulları karşılayan kaç tane benzersiz öğe olduğunu belirlemek gerekir. Örneğin, belirli bir satış personelinin kaç farklı ürün sattığını veya belirli bir zaman diliminde kaç benzersiz sipariş verildiğini öğrenmek isteyebilirsiniz. Excel'de bu tür görevlerle etkili bir şekilde başa çıkmak için uygun formüller, gelişmiş özellikler (örneğin pivot tablolar) veya özel VBA çözümleri hakkında bilgi sahibi olmanız gerekir. Bu makalede, adım adım talimatlar ve ipuçlarıyla birlikte bir veya daha fazla kritere dayalı olarak benzersiz değerleri sayma yöntemlerini inceleyeceğiz.
Tek kritere göre benzersiz değerleri sayma
İki tarih aralığına göre benzersiz değerleri sayma
İki kritere göre benzersiz değerleri sayma
Üç kritere göre benzersiz değerleri sayma
Pivot Tablo ile benzersiz değerleri sayma (Farklı Sayım, Excel 2013+)
VBA Kodu ile benzersiz değerleri sayma (karmaşık/otomatik durumlar için)
Tek kritere göre benzersiz değerleri sayma
Yaygın bir örneği ele alalım: Tom’un kaç farklı ürün sattığını saymak istiyorsunuz. Bu yöntem, basit bir veri setiniz varsa ve tek bir kişiye ait satış kayıtlarına dayanarak benzersizliği değerlendirmek istediğinizde uygundur. Yöntem doğrudan olsa da dizi formülleri konusunda dikkatli olunmalıdır.
Bu senaryo için aşağıdaki formülü boş bir hücreye girin (örneğin, G2 hücresi):
=TOPLA(EĞER("Tom"=$C$2:$C$20;1/(EĞERSAY($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20));0))
Formülü yazdıktan sonra, bunu bir dizi formülü olarak onaylamak için Ctrl + Shift + Enter tuşlarına basın (yalnızca Enter değil). Formül çubuğunda formülün etrafında kıvrık parantezler görünecek ve sonucu anında aşağıdaki gibi göreceksiniz:
Not:
- “Tom” sonuçları filtrelemek için kullanacağınız kriterdir. Daha fazla esneklik isterseniz “Tom”u başka bir hücreye referansla değiştirebilirsiniz (örneğin, $F$2).
- $C$2:$C$20 değerlendirilecek satış personeli isimlerini içerir.
- $A$2:$A$20, benzersiz sayımlarını istediğimiz ürün sütunudur.
- Veri aralığınız değişirse, referansları buna göre ayarlamayı unutmayın.
İpucu: Excel 365 veya Excel 2019 ve sonrası sürümlerini kullanıyorsanız, daha kolay formüller için UNIQUE ve FILTER fonksiyonlarını deneyebilirsiniz.
Herhangi bir #BÖL/0! hatasıyla karşılaşırsanız, kriterleri tekrar kontrol edin ve aralıklarınızın uzunluklarının eşit olduğundan emin olun.
İki tarih aralığına göre benzersiz değerleri sayma
Belirli bir tarih aralığındaki benzersiz öğelerin sayısını bulmanız gereken durumlarda, örneğin 2016/9/1 ve 2016/9/30 arasında satılan tüm benzersiz ürünleri saymak için bu yaklaşımı uygulayabilirsiniz. Bu yöntem, aylık, üç aylık veya özel tarih aralıkları gibi belirli dönemler arasındaki veri trendlerini analiz ederken özellikle faydalıdır. Ancak, tarih biçimlendirmesine dikkat edin; bu, çalışma sayfanızdaki tarih değerleriyle eşleşmelidir.
Sonucu göstermek istediğiniz boş bir hücreye aşağıdaki formülü yerleştirin:
=TOPLA(EĞER($D$2:$D$20<=TARİH(2016,9,30)*($D$2:$D$20>=TARİH(2016,9,1));1/EĞERSAY($A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&TARİH(2016,9,30);$D$2:$D$20; ">="&TARİH(2016,9,1));0))
Formülü girdikten sonra, onu bir 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ş tarih kriterleridir. Bunları gerektiğinde değiştirebilir veya dinamik tarih filtreleri için hücre referansları kullanabilirsiniz.
- $D$2:$D$20 kontrol edilecek tarih girişlerini içerir.
- $A$2:$A$20 yine benzersiz olarak saymak istediğiniz öğe veya ürün sütunudur.
- Sonuç beklediğiniz gibi görünmüyorsa, tarih biçimlendirmesini ve aralıkları kontrol edin.
İpucu: Bölgesel tarih biçimlendirme sorunlarını önlemek için TARİH(yıl, ay, gün) kullanın. Dinamik aralıklar kullandığınızda, netlik açısından adlandırılmış aralıklar kullanmayı düşünün.
İki kritere göre benzersiz değerleri sayma
Eylül ayında Tom'un sattığı ürünleri analiz etmek istiyorsanız, bu yöntemi kullanabilirsiniz. Bu senaryo, periyodik performans değerlendirmeleri veya bölümlere ayrılmış analizler için yaygındır. Kriterleriniz genişledikçe, formüller daha karmaşık hale gelir ve veri doğruluğuna olan dikkat daha da önem kazanır.
Aşağıdaki formülü herhangi bir boş hücreye girin, örneğin H2:
=TOPLA(EĞER(("Tom"=$C$2:$C$20)*($D$2:$D$20<=TARİH(2016,9,30))*($D$2:$D$20>=TARİH(2016,9,1));1/EĞERSAY($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&TARİH(2016,9,30);$D$2:$D$20; ">="&TARİH(2016,9,1)));0))
Formülü yazdıktan sonra, Ctrl + Shift + Enter ile onaylayın. Benzersiz sayımı hemen görmelisiniz; aşağıdaki resme bakın:
Notlar:
- “Tom” isim kriteridir, “2016,9,1” ve “2016,9,30” tarih aralığı sınırlarını temsil eder. Gerektiğinde değiştirin veya dinamik tarihler için hücre referansları yapın.
- $C$2:$C$20 personel (veya başka bir ilk kriter) sütunudur; $D$2:$D$20 tarih sütunudur; $A$2:$A$20 benzersiz olarak sayılacak öğeleri içerir.
- Hataları önlemek için tüm aralıkların uzunluklarının eşit olması gerekir.
“Ya da” koşullarını kullanmak isterseniz, örneğin Tom tarafından satılan veya Güney bölgesindeki benzersiz ürünleri saymak için aşağıdaki formülü kullanabilirsiniz. Bu, daha geniş arama koşullarını mümkün kılar, ancak aynı kayıt her iki kriteri de karşılıyorsa sonuçlar çakışabilir:
=TOPLA(--(SIKLIK(EĞER(("Tom"=$C$2:$C$20)+("Güney"=$B$2:$B$20); EĞERSAY($A$2:$A$20; "<"&$A$2:$A$20); ""); EĞERSAY($A$2:$A$20; "<"&$A$2:$A$20))>0))
Ctrl + Shift + Enter tuşlarına basmayı unutmayın. Sonuçları aşağıdaki gibi göreceksiniz:
İpucu: VEYA koşulları uygularken, aynı kayıt her iki koşulu da karşılıyorsa çift sayım yapma ihtimaline dikkat edin. Büyük veri setlerinde performans etkilenebilir.
Üç kritere göre benzersiz değerleri sayma
Bazen analizinizi üç veya daha fazla koşula göre yapmanız gerekebilir, örneğin Eylül ayında yalnızca Kuzey bölgesinde Tom'un sattığı benzersiz ürünleri belirlemek gibi. Bu, raporlama veya hedefli iş görüşleri için çok boyutlu veri analizinde yaygındır. Böyle bileşik mantığı işlemek için dikkatli referans yönetimi gereklidir.
Bu dizi formülünü boş bir hücreye (örneğin I2) yerleştirin:
=TOPLA(EĞER(("Tom"=$C$2:$C$20)*($D$2:$D$20<=TARİH(2016,9,30))*($D$2:$D$20>=TARİH(2016,9,1))*("Kuzey"=$B$2:$B$20);1/EĞERSAY($C$2:$C$20; "Tom"; $A$2:$A$20; $A$2:$A$20; $D$2:$D$20; "<="&TARİH(2016,9,30); $D$2:$D$20; ">="&TARİH(2016,9,1); $B$2:$B$20; "Kuzey"));0)
Ctrl + Shift + Enter tuşlarına basarak tamamlayın. Referans için örnek bir sonuç burada:
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. Hizalamalar yanlışsa hatalar veya yanıltıcı sonuçlar ortaya çıkabilir.
İpuçları:
- Büyük veri setlerinde performans sorunları yaşarsanız, formülü bölmeyi veya Excel’in PivotTable çözümünü kullanmayı düşünün.
- Tüm kriterler için adlandırılmış aralıklar veya hücrelere başvuru yapmak okunabilirliği artırır ve formül hatalarını azaltır.
- Sık kullanım için bu formülleri adlandırılmış hücre referanslarında veya özel fonksiyonlarda kaydetmeyi düşünün.
PivotTable ile benzersiz değerleri sayma (Farklı Sayım, Excel 2013+)
Excel 2013 veya sonraki sürümleri kullananlar için PivotTable'lar, bir veya birden fazla kritere göre benzersiz değerleri saymak için formülsüz, etkileşimli bir alternatif sunar. Farklı Sayım özelliği, büyük veri setlerini özetlemenize ve filtrelemenize yardımcı olur, bu yöntem özellikle dinamik, rapor tabanlı ortamlar için uygundur. Ancak, Excel'in önceki sürümlerinde PivotTable içinde Farklı Sayım işlevi bulunmamaktadır.
Bu yöntemi nasıl kullanılır:
- Veri setinizi seçin ve Ekle > PivotTable'a gidin.
- Create PivotTable iletişim kutusunda, PivotTable'ı nereye yerleştireceğinizi seçin, “Add this data to the Data Model” (Bu veriyi Veri Modeline ekle) kutusunu işaretleyin ve ardından Tamam'a tıklayın.
- Benzer şekilde 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ında alan üzerine tıklayın ve Alan Ayarlarını Değerler’e tıklayın.
- Açılan diyalogda aşağı kaydırın ve Farklı Sayım’ı seçin (Bu seçenek yalnızca Excel 2013 veya sonraki sürümlerde mevcuttur ve PivotTable “Add this data to the Data Model” seçeneği etkinleştirilerek oluşturulduğunda görünür.).
- Kriter alanlarınızı (örneğin Satış Elemanı, Bölge, Tarih) Filtreler veya Satırlar/Sütunlar alanına ekleyerek tek veya çoklu koşullar uygulayın.
- PivotTable'ınız artık seçtiğiniz kriterlere göre filtrelenen benzersiz değer sayısını gösterecektir.
Avantajlar: Çok görsel, formülleri düzenlemeksizin filtreleri kolayca ayarlayabilir, etkileşimli raporlamaya uygundur.
Sınırlamalar: Excel 2010 veya önceki sürümlerde mevcut değildir; yeni veri eklemek için PivotTable’ı manuel olarak yenilemek gerekir.
Pratik İpucu: Kaynak verilerde aynı kayıtlarda istenmedikçe yinelenen veriler olmadığından emin olun. Farklı Sayım seçeneği eksikse, PivotTable’ı yeniden oluşturun ve “Add this data to the Data Model” seçeneğini işaretleyin.
VBA Kodu ile benzersiz değerleri sayma (karmaşık/otomatik durumlar için)
Bazen, özellikle çok büyük veri setleriyle çalışırken veya analizi sık sık tekrarladığınızda, çeşitli kriterlere dayalı olarak benzersiz değerleri otomatik olarak saymanız gerekebilir. VBA makrosu, kurulumdan sonra elle müdahale gerektirmeden farklı mantıkları —çoklu koşullu filtreleme dahil— hızlıca işleyebildiği için bu tür durumlar için idealdir. Ancak VBA, normal Excel özellikleri kadar kolay değildir, bu nedenle makrolara aşina olan veya sürekli analiz ihtiyacı duyan kullanıcılar tarafından en iyi şekilde kullanılabilir.
İşlem adımları:
- VBA düzenleyiciyi açmak için Alt + F11 tuşlarına basın. Düzenleyicide Ekle > Modül seçerek yeni bir modül oluşturun.
- Aşağıdaki VBA kodunu modüle kopyalayıp 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çin ve makroyu çalıştırın.
- Verilerinize göre aralıkları ve kriterleri belirtmek için giriş istemlerini izleyin. Sonuç, seçtiğiniz hücrede ve ayrıca bir ileti kutusu olarak görünecektir.
Parametre açıklaması ve notlar:
- Bu makro şu anda tek bir kriter için ayarlanmıştır. Birden fazla kriter için döngü içindeki
Eğer ... O Zamanmantığını değiştirin. - Makroları çalıştırmadan önce her zaman çalışma kitabınızı 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 yetersiz kaldığı büyük veya sık güncellenen veri için iyi çalışır.
Avantajlar: Yüksek düzeyde özelleştirilebilir ve otomasyona uygun, büyük ve değişen veri setlerini verimli bir şekilde işler. Gelişmiş veya tekrarlayan iş akışı ihtiyaçları için uygundur.
Dezavantajlar: Makro izinleri gerektirir ve başlangıç seviyesindeki kullanıcılar VBA işlemlerine aşina olmak için zamana ihtiyaç duyabilir.
Kriterlere dayalı benzersiz değer sayımlarıyla çalışırken, her zaman aralık referanslarınızı kontrol edin ve tüm kriter sütunlarının boyutlarının eşit olduğundan emin olun. Uyuşmayan aralıklar, hataların veya yanlış sonuçların yaygın bir kaynağıdır. Formüller beklenmedik sonuçlar döndürüyorsa, gizli biçimlendirme sorunlarını veya boş hücreleri kontrol edin. Performans açısından kritik senaryolarda, PivotTable'lar ve VBA, dizi formüllerine güçlü alternatifler sağlar. Kendi rahatlık düzeyinize ve veri setinizin karmaşıklığına en uygun çözümü seçin. Unutmayın, Kutools for Excel, karmaşık çalışma kitaplarında bu görevleri daha da etkin bir şekilde gerçekleştirmek için ek araçlar ve kısayollar sağlar.
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