Elimizde bu şekilde örnek bir veri var.
| SIRKETNO | YIL | DEPNO | GRCKOD | IRSNO | SIRANO | HARTIP | IRSTARIH |
| 1 | 2024 | 6 | C | 393 | 1 | 7 | 4.01.2024 00:00 |
| 1 | 2024 | 6 | C | 393 | 2 | 7 | 4.01.2024 00:00 |
| 1 | 2024 | 6 | C | 393 | 3 | 7 | 4.01.2024 00:00 |
| 1 | 2024 | 1 | C | 409 | 1 | 3 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | C | 409 | 2 | 3 | 5.01.2024 00:00 |
| 1 | 2024 | 14 | C | 424 | 2 | 7 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | C | 411 | 1 | 4 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | C | 411 | 2 | 4 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | C | 411 | 3 | 4 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | G | 411 | 4 | 4 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | G | 411 | 5 | 4 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | S | 411 | 6 | 4 | 5.01.2024 00:00 |
| 1 | 2024 | 1 | S | 411 | 7 | 4 | 5.01.2024 00:00 |
bu veri bir milyon satıra kadar gidiyor. Şimdi amacımız
| DEPNO | GRCKOD | HARTIP | tarih 1 | tarih 2 |
| 1 | C | 4 | 1.01.2024 | 4.01.2024 |
DEPNO değeri “1” olan
GRCKOD değeri “C” olan
HARTIP değeri “4” olan
IRSTARIH değeri “1.01.2024” eşit olan ve yine aynı kombinasyonda IRSTARIH “1.01.2024” ile “4.01.2024” tarihlerini içerek şekilde bu tarih aralığındaki verileri saydırmak olacak. 2 farklı formül yazacağız. Sadece 2 tarih aralığını seçeceğimiz için ilk hazırlayacağımız koda çok küçük bir ekleme yapacağız.
Benim verilerim “veri” sayfasında. Bende kodlarımı “rapor” sayfasında yazıyorum. Örnek excel dosyasını da ayrıca yazının sonunda paylaşacağım.
Bu formül ile belirli koşulları sağlayan benzersiz verilerin sayısını alacağız. Olabildiği kadar formülü adım adım açıklamaya çalışacağım.
Formülün Tamamı 2 durum içinde
tek bir tarih için verinin alınması
=EĞER(BENZERSİZ(FİLTRE(veri!E:E; (veri!C:C = rapor!G4) * (veri!D:D = rapor!H4) * (veri!F:F = rapor!I4) * (veri!G:G = rapor!J4); "")) = ""; 0; BAĞ_DEĞ_DOLU_SAY(BENZERSİZ(FİLTRE(veri!E:E; (veri!C:C = rapor!G4) * (veri!D:D = rapor!H4) * (veri!F:F = rapor!I4) * (veri!G:G = rapor!J4); ""))))
Belirli bir tarih aralığı için verinin alınması
=EĞER(BENZERSİZ(FİLTRE(veri!E:E; (veri!C:C=rapor!G4) * (veri!D:D=rapor!H4) * (veri!F:F=rapor!I4) * (veri!G:G>=rapor!J4) * (veri!G:G<=rapor!K4); "")=""); 0; BAĞ_DEĞ_DOLU_SAY(BENZERSİZ(FİLTRE(veri!E:E; (veri!C:C=rapor!G4) * (veri!D:D=rapor!H4) * (veri!F:F=rapor!I4) * (veri!G:G>=rapor!J4) * (veri!G:G<=rapor!K4); ""))))
Şimdi sırayla tek tek kodları açıklamaya çalışalım
FİLTRE Fonksiyonu
FİLTRE(veri!E:E; (veri!C:C=Sayfa2!G4) * (veri!D:D=Sayfa2!H4) * (veri!F:F=Sayfa2!I4) * (veri!G:G>=Sayfa2!J4) * (veri!G:G<=Sayfa2!J5); "")
FİLTRE fonksiyonu, belirli koşulları karşılayan verileri seçer.
veri!E:E: E sütunundaki tüm verileri filtreler.
bu filtrelemeyi yaparken de aşağıdaki koşulları arar
- (veri!C:C=rapor!G4): C sütunundaki hücrelerin rapor!G4 hücresindeki değere eşit olup olmadığını kontrol eder.
- (veri!D:D=rapor!H4): D sütunundaki hücrelerin rapor!H4 hücresindeki değere eşit olup olmadığını kontrol eder.
- (veri!F:F=rapor!I4): F sütunundaki hücrelerin rapor!I4 hücresindeki değere eşit olup olmadığını kontrol eder.
- (veri!G:G>=rapor!J4): G sütunundaki hücrelerin rapor!J4 hücresindeki tarihe eşit veya ondan büyük olup olmadığını kontrol eder.
- (veri!G:G<=rapor!K4): G sütunundaki hücrelerin rapor!J5 hücresindeki tarihe eşit veya ondan küçük olup olmadığını kontrol eder.
Bu koşulları sağlayan verilerin E sütunundaki değerlerini seçer.
“”: burada “Eğer filtrelenen veriler yoksa, boş bir sonuç dönsün” diyoruz.
Burada dikkat etmemiz gereken husus veri!G:G sütununda tarihimiz var. eğer belirli bir tarih almak istiyorsak “veri!G:G=rapor!J4” (tarih bilgisini içeren hücre) diyerek o tarihe ait verileri istiyoruz.
Eğer 2 tarih arasındaki verileri almak istiyorsak “>=” küçük eşit ve “<=” büyük eşit operatörlerini kullanıyoruz.
“veri!G:G>=rapor!J4” ve “veri!G:G<=rapor!K4” diyerke 1. ve 2. tarihimiz rapor!J4 ve rapor!K4 hücrelerinde bulanacak şekilde formülümüzü çalıştırıyoruz. .
BENZERSİZ Fonksiyonu
BENZERSİZ(FİLTRE(...))
-
- BENZERSİZ fonksiyonu, FİLTRE fonksiyonunun döndürdüğü sonuçlar arasından benzersiz olanları seçer.
- Bu, aynı değeri birden fazla kez içeren satırları tek bir değer olarak döndürür.
EĞER Fonksiyonu
EĞER(BENZERSİZ(FİLTRE(...))=""; 0; BAĞ_DEĞ_DOLU_SAY(BENZERSİZ(FİLTRE(...))))
-
- EĞER fonksiyonu, bir koşula göre iki farklı sonuç döndürür.
- BENZERSİZ(FİLTRE(…))=””: Eğer BENZERSİZ fonksiyonu boş bir sonuç dönerse (yani, belirtilen koşullara uyan veri yoksa), 0 döner.
- Aksi takdirde, BAĞ_DEĞ_DOLU_SAY(BENZERSİZ(FİLTRE(…))) kısmını çalıştırır.
BAĞ_DEĞ_DOLU_SAY Fonksiyonu
BAĞ_DEĞ_DOLU_SAY(BENZERSİZ(FİLTRE(...)))
-
- BAĞ_DEĞ_DOLU_SAY fonksiyonu, BENZERSİZ fonksiyonunun döndürdüğü benzersiz değerlerin sayısını döner.
- Bu, belirtilen koşullara uyan ve benzersiz olan veri sayısını hesaplar.
Aslında FİLTRE fonksiyonu ile belirli koşulları karşılayan verileri seçmek için kullandık,
BENZERSİZ fonksiyonu seçmiş olduğumuz verilerin içinden benzersiz olanları almak için kullandık,
EĞER fonksiyonu verilerin boş dönme ihtimaliyet karşı kullandık. Yani belirli kriterleri belirtilen verinin karlığu yoksa sonuç “0” dönsün. Yoksa benzersiz değerlerin sayısı dönsün.
BAĞ_DEĞ_DOLU_SAY fonksiyonu benzersiz olarak filtrelediğimiz verilerin sayılarını almak için kullandık.
Bu kadar anlattıktan sonra daha kısa ve alternatif bir yol olan
=TOPLA.ÇARPIM(--DEĞİL(EHATALIYSA(BENZERSİZ(FİLTRE(veri!E:E;(veri!D:D=H4)*(veri!F:F=I4)*(veri!G:G=J4))))))
yukarıdaki kodu da buraya bırakayım. Excel de bir sonuca ulaşmanın her zaman birden fazla yolu vardır. Siz hangisi işinize geliyorsa onu kullanırsınız. Burada kriterleri istediğiniz kadar kendinize göre özelleştirebilirsiniz.
örnek excek dosyasını indirmek için tıklayınız.