• DİKKAT !

    Forum içeriğine ve tüm hizmetlerimize erişim sağlamak için foruma kayıt olmalı ya da giriş yapmalısınız. Foruma üye olmak Dosya Yükleme tamamen ücretsizdir.

    Giriş yap veya Şimdi kayıt ol

Soru Büyük Veride Kasmayan Çok Kriterli Benzersiz Sayım

saliya

saliya

Yeni Üye
Aktivite

0%
Katılım
2 May 2024
Mesajlar
1
Aldığı beğeni
0
Excel V
Office 2016 TR
Konu Sahibi
Windows 10 Google Chrome 151
Herkese merhaba, iyi forumlar.
Şirket verilerimiz büyüdükçe standart Excel formülleri hesaplama yaparken kilitlenmeye, %100 CPU tüketerek dosyayı çökertmeye başladı. Yaşadığım bu performans sorununa en optimize çözümü bulabilmek adına foruma konusuny açmak istedim.
Ekte paylaştığım buyuk_veri_test.xlsx dosyasında örnek bir satış veri seti bulunuyor. Yapmak istediğim şey şu:
  • Yıl: 2026
  • Bölge: Marmara
  • Ürün Grubu: Elektronik
Bu 3 kritere uyan satırlardaki BENZERSİZ (Unique) Müşteri Sayısını bulmak istiyorum (Aynı müşteri birden fazla alışveriş yaptığı için satırlarda tekrar ediyor, onları tek saymalı).
Forumdaki değerli hocalarımıza ve üstatlarımıza sormak istiyorum; dosyanın kasılmasını engellemek ve en yüksek performansı almak için sizce hangi mimariyi kurmalıyız?
  1. Formüllerle: BENZERSİZ (UNIQUE) ve FİLTRE (FILTER) gibi yeni nesil 365 formüllerini iç içe kullanarak mı?
  2. VBA / Makro ile: Hafızada çalışan Scripting.Dictionary ya da Collection nesneli dizi (Array) mimarisiyle mi?
  3. Power Query / Power Pivot ile: Veriyi Veri Modeline alıp DAX fonksiyonlarındaki DISTINCTCOUNT yöntemiyle mi?
Hocalarımız ekteki test dosyası üzerinde kendi yazdıkları yöntemlerin kaç saniyede sonuç verdiğini (hız testini) kod veya formül olarak paylaşabilirse, büyük veriyle çalışan herkes için harika bir rehber konu olacaktır.

Şimdiden çok ama çok teşekkür ederim.
 

Ekli dosyalar

Tarihe göre sırala Oylara göre sırala
Windows 10 Opera 134
Ekteki dosyayı kontrol ettim. Veri setinde 500 satış kaydı var. Verdiğiniz üç kriteri birlikte uyguladığımızda 43 satır eşleşiyor ve bunların içindeki benzersiz müşteri sayısı 29.

Dolayısıyla beklenen sonuç:

2026 + Marmara + Elektronik → 29 benzersiz müşteri

Performans açısından da üç yöntemi aynı dosya üzerinde karşılaştırmak çok güzel bir forum testi olur. Ancak 500 satır gerçek performans farkını göstermek için oldukça küçük; 100 bin, 500 bin ve 1 milyon satırlık testler oluşturup UNIQUE/FILTER, VBA Dictionary ve Power Pivot/DAX DISTINCTCOUNT yöntemlerini aynı şartlarda süre ölçerek karşılaştırmak çok daha anlamlı olur.
 
Olumlu oy 0 Negatif oyla
Windows 10 Opera 134
Mevcut dosyanızı temel alıp üç yöntemi yan yana karşılaştırabileceğiniz, süre ölçümlerinin de kaydedildiği bir benchmark çalışma kitabına bakabilirsiniz.
Temel test ölçeğini 100.000 satır olarak kuruyorum; aynı veri üzerinde 10.000 / 50.000 / 100.000 satır seviyelerini ayrı ayrı ölçebileceksiniz. Böylece dosya gereksiz yere devleşmeden ölçek büyüdükçe performans eğilimi görülebilecek.

Dosyada kaynak Sheet1 aynen korunuyor. Ayrıca Benchmark, VBA_Kodu ve PowerPivot_DAX sayfalarını ekledim. 2026 + Marmara + Elektronik için kontrol sonucu 29 benzersiz müşteri olarak yer alıyor.

VBA_Kodu sayfasında ayrıca 100.000 / 500.000 / 1.000.000 satırlık veri üretme, UNIQUE + FILTER süresini ölçme ve Array + Scripting.Dictionary benchmark kodları hazır. Dosya .xlsx olduğu için makro doğrudan gömülü değil; kodu VBA modülüne yapıştırıp .xlsm olarak kaydettiğinizde çalıştırabilirsiniz. PowerPivot_DAX sayfasında da DISTINCTCOUNT ölçüsü hazır.
 

Ekli dosyalar

Olumlu oy 0 Negatif oyla
Windows 10 Opera 134
Dosyada üç farklı yöntem var; önce Formül, sonra VBA, en son Power Pivot testini çalıştırmanız en kolay yol.

  1. Benchmark dosyasını açın. Excel 365 kullanıyorsanız önce Veri_100K sayfasına geçin. A2:E2 hücrelerinde dinamik formüller var; bunlar aşağı doğru otomatik taşarak yaklaşık 100.000 satırlık test verisi oluşturur. Birkaç saniye içinde veri görünmelidir.
  2. Sonra Benchmark sayfasına geçin. Sol tarafta kriterler:
    Yıl = 2026, Bölge = Marmara, Ürün Grubu = Elektronik.
    Sağ tarafta 10.000, 50.000 ve 100.000 satır için formül testi vardır. Sonuçların 29 çıkması gerekir. Burada kullanılan mantık UNIQUE + FILTER yöntemidir.
  3. VBA testini çalıştırmak için önce dosyayı makrolu formata çevirin. Excel'de Dosya → Farklı Kaydet → Excel Makro Etkin Çalışma Kitabı (*.xlsm) seçin. Örneğin buyuk_veri_benchmark.xlsm olarak kaydedin.
  4. Ardından VBA_Kodu sayfasına gidin. Orada bulunan kodun tamamını kopyalayın. Klavyeden Alt + F11 tuşlarına basın. VBA penceresinde Ekle → Modül seçin ve kodu açılan boş pencereye yapıştırın. Sonra VBA penceresini kapatabilirsiniz.
  5. Excel'e dönüp Alt + F8tuşlarına basın. İki önemli makro göreceksiniz:
    • BenchmarkFormula → Formül hesaplama süresini ölçer.
    • BenchmarkDictionary → VBA Array + Scripting.Dictionary yöntemini ölçer.

Önce BenchmarkFormula seçip Çalıştır, ardından BenchmarkDictionary seçip Çalıştır deyin. İşlem bittiğinde Benchmark sayfasına dönün; sonuç ve süre alanları doldurulmuş olacaktır.

Power Pivot için ise PowerPivot_DAX sayfasındaki yönergeleri kullanın. Temel ölçü şudur:

HTML:
Kod:
İçeriği görebilmek için Giriş yap ya da Üye ol.


Sonra PivotTable'da 2026 / Marmara / Elektronik filtrelerini verdiğinizde sonuç yine 29 olmalı.


Ben ilk aşamada özellikle VBA Dictionary ile UNIQUE+FILTER'ı karşılaştırmanızı öneririm; kullanım açısından en kolay iki test bunlar.
 
Olumlu oy 0 Negatif oyla
Cevap yazmak için giriş yap yada kayıt ol.
Geri
Üst