Tarih Dinamik Dizi Koşullu İşlevleri Birlikte Kullanma

Tarih, dinamik dizi ve koşullu işlevleri birlikte kullanma, bir tarih aralığını ölçüt alarak toplam, sayım ve ortalama hesaplamak ya da bu aralıktaki kayıtları ayrı bir listeye çıkarmak demektir. Dönem başını TARİH, dönem sonunu SERİAY ile üretir, bu iki tarihi ÇOKETOPLA gibi koşullu işlevlere ölçüt olarak verirsiniz; FİLTRE ve SIRALA ise aynı ölçütle sonuç listesini otomatik oluşturur. Dinamik dizi işlevleri için Excel 365 ya da Excel 2021 gerekir.

Tarih, dinamik dizi ve koşullu işlevler birlikte nasıl çalışır?​

Excel tarihleri seri numarası olarak saklar. Bu yüzden bir tarihin başka bir tarihten büyük ya da küçük olup olmadığını karşılaştırabilir, bu karşılaştırmayı hem koşullu işlevlerde hem de dinamik dizi işlevlerinde ölçüt olarak kullanabilirsiniz. Üç işlev grubu şu işi paylaşır:

• Tarih işlevleri: TARİH (DATE) verilen yıl, ay ve günden tarih üretir. SERİAY (EOMONTH) ayın son gününü, SERİTARİH (EDATE) belirtilen ay sayısı kadar ileri ya da geri tarihi verir.
• Koşullu işlevler: ÇOKETOPLA (SUMIFS), ÇOKEĞERSAY (COUNTIFS) ve ÇOKEĞERORTALAMA (AVERAGEIFS) birden fazla koşula uyan kayıtları toplar, sayar ve ortalamasını alır.
• Dinamik dizi işlevleri: FİLTRE (FILTER) koşula uyan satırları getirir, SIRALA (SORT) sonucu sıralar, BENZERSİZ (UNIQUE) yinelenenleri ayıklar. Sonuç, formülün yazıldığı hücreden başlayarak komşu hücrelere taşar.

Tipik düzen şöyledir: dönem başı bir hücreye yazılır, dönem sonu SERİAY ile bulunur ve her iki hücre de tüm formüllerde ortak ölçüt olur. Dönemi değiştirdiğinizde toplamlar ve liste birlikte güncellenir.

Tarih aralığına göre toplama, sayma ve ortalama alma​

ETOPLA, EĞERSAY ve EĞERORTALAMA tek koşul alır. Bir tarih aralığında iki sınır (başlangıç ve bitiş) gerektiği için ÇOKETOPLA, ÇOKEĞERSAY ve ÇOKEĞERORTALAMA kullanılır. Karşılaştırma işareti tırnak içinde yazılır ve & ile hücre başvurusuna bağlanır. Dönem başı F2, dönem sonu F3 hücresindeyse aylık toplam şöyle alınır:
İlk koşul başlangıç gününü de kapsar (büyük ya da eşit), ikinci koşul ayın son gününü de kapsar (küçük ya da eşit). Bitiş tarihini elle yazmak yerine SERİAY ile bulursanız Şubat, Nisan gibi kısa ayları ayrıca düşünmeniz gerekmez. Sayım ve ortalama için aynı iki koşulu ÇOKEĞERSAY ve ÇOKEĞERORTALAMA işlevlerine verebilirsiniz.

FİLTRE ve SIRALA ile bir dönemin kayıtlarını listeleme​

Toplamın yanında dönemdeki kayıtların kendisini de görmek istiyorsanız FİLTRE işlevi kullanılır. Birden fazla koşul çarpılarak birleştirilir; çarpma burada VE anlamına gelir. SIRALA ile sarmaladığınızda liste istediğiniz sütuna göre sıralı gelir. Mayıs 2024 kayıtlarını tutara göre büyükten küçüğe listeleyen formül şöyledir:
SIRALA işlevinin ikinci bağımsız değişkeni sıralanacak sütunun sırasıdır (burada FİLTRE sonucunun ikinci sütunu olan tutar), üçüncüsü ise yöndür: 1 artan, -1 azalan. Formülü yalnızca ilk hücreye yazın; sonuç sağa ve aşağıya kendiliğinden taşar ve kayıt sayısı değiştikçe liste büyür ya da küçülür.

Ay ay özet tablosu hazırlama​

Her ay için ayrı formül yazmak yerine ay başlarını SERİTARİH ile üretebilirsiniz. İlk ay başını bir hücreye yazın, sonraki hücrede ay sayısını bir artırın. Her ay başı için toplam, aynı ÇOKETOPLA yapısıyla alınır; dönem sonu ise o satırdaki ay başından SERİAY ile bulunur:
Veri aralıklarını mutlak başvuruyla ($) sabitleyip yalnızca ay başı hücresini göreli bırakırsanız formülü aşağı doğru kopyalayabilirsiniz. Kaç farklı şehirle çalışıldığını görmek için BENZERSİZ işlevini BAĞ_DEĞ_DOLU_SAY içine yerleştirmeniz yeterlidir; en yüksek ayı bulmak için MAK, KAÇINCI ve İNDİS işlevleri birlikte kullanılır.

1. Bir ayın toplam, adet ve ortalama değerleri​

Satış kayıtları A:C sütunlarında, dönem başı F2 hücresinde duruyor. Dönem sonunu SERİAY (EOMONTH) ile bulup üç koşullu işlevde ölçüt olarak kullanıyoruz.
Dönem sonu 31.03.2024 olarak hesaplanır. Bu tarihte yapılan satış da kapsama girer, çünkü ikinci koşul küçük ya da eşit işaretini kullanır. Dönem toplamı 10.450,00, satış adedi 5 ve ortalama tutar 2.090,00 olarak bulunur. F2 hücresine başka bir ayın ilk gününü yazarsanız üç sonuç da yeni döneme göre yeniden hesaplanır.

2. Dönemin kayıtlarını tutara göre sıralı listeleme​

Bilgisayar çevre birimi satışları A:C sütunlarında. E2 hücresine tek bir formül yazıyoruz: Mayıs 2024 kayıtları FİLTRE (FILTER) ile süzülüyor, SIRALA (SORT) ile tutara göre büyükten küçüğe diziliyor. Liste E ve F sütunlarına taşıyor, F8 hücresi ise aynı dönemin toplamını koşullu işlevle doğruluyor.
E2 hücresindeki formül Mayıs 2024 kayıtlarını süzer ve en yüksek tutarlı ürünü ilk sırada gösterir; listenin ilk ürünü Monitör olur. Liste E ve F sütunlarında aşağı doğru taştığı için taşma alanındaki hücreleri boş tutmanız gerekir. F8 hücresindeki ÇOKETOPLA sonucu 8.130,00 değerini verir; bu değer, listedeki tutarların toplamıyla aynı olmalıdır ve iki hesabı birbiriyle doğrulamanın kolay yoludur.

3. Ay ay özet ve benzersiz şehir sayısı​

Ocak, Şubat ve Mart satışları A:C sütunlarında. E2 hücresine ilk ay başını yazıp sonraki ay başlarını SERİTARİH (EDATE) ile üretiyoruz; her ayın toplamı SERİAY (EOMONTH) ve ÇOKETOPLA (SUMIFS) ile hesaplanıyor. BENZERSİZ (UNIQUE) ile farklı şehir sayısını, İNDİS (INDEX) ve KAÇINCI (MATCH) ile en yüksek ayı buluyoruz.
Ay başları 01.02.2024 ve 01.03.2024 olarak SERİTARİH ile üretilir. Üç ayın toplamları sırasıyla 3.500,00, 4.100,00 ve 6.400,00 değerlerini verir; genel toplam 14.000,00 olur. Şubat ayının son günü olan artık yıl tarihi de SERİAY sayesinde Şubat toplamına dahil edilir. BENZERSİZ ile bulunan farklı şehir sayısı 4, toplamı en yüksek olan ayın başlangıç tarihi ise 01.03.2024 olarak görünür.

Sık sorulan sorular​

FİLTRE ve SIRALA eski Excel sürümlerinde çalışır mı? Hayır. Dinamik dizi işlevleri Excel 2019 ve öncesinde bulunmaz; Excel 2021 ve Excel 365 sürümlerinde vardır. ÇOKETOPLA, ÇOKEĞERSAY, SERİAY ve SERİTARİH ise eski sürümlerde de kullanılabilir.
Tarihlerim koşula uymuyor, neden? Tarihler büyük olasılıkla metin olarak kaydedilmiştir. Hücreler sola yaslıysa bunu kontrol edin ve Veri > Veri Araçları > Metni Sütunlara Dönüştür (Data > Data Tools > Text to Columns) komutuyla gerçek tarihe çevirin.
Ay sonunu neden elle yazmıyorum? Aylar 28, 29, 30 ya da 31 gün çeker. SERİAY işlevi artık yılı da dikkate alarak doğru son günü verir.
Yeni satır eklediğimde sonuçlar güncellenir mi? Sabit aralıklar yeni satırı kapsamaz. Verinizi Ekle > Tablo (Insert > Table) ile tabloya çevirip formüllerde tablo sütunlarını kullanırsanız yeni kayıtlar otomatik hesaba katılır.

Kullanırken dikkat edin​

• Tarihlerin gerçek tarih olduğundan emin olun: Metin olarak kaydedilmiş tarihler karşılaştırmalarda yok sayılır ve toplam sıfır çıkabilir. Hücre hizalamasına bakın, gerekirse Metni Sütunlara Dönüştür komutuyla tarihi düzeltin.
• Ölçütü tırnak ve & ile birleştirin: Koşullu işlevlerde karşılaştırma işareti ile hücre başvurusu ayrı yazılamaz. Doğru biçim, işareti tırnak içinde yazıp & ile hücreye bağlamaktır; işareti ve başvuruyu tek tırnak içine koymak yanlış sonuç verir.
• Saat bilgisi içeren tarihlere dikkat edin: Hücrede tarihle birlikte saat varsa ayın son günündeki kayıtlar, bitiş ölçütü olarak SERİAY sonucunu kullandığınızda dışarıda kalabilir. Bu durumda bitiş için küçük işaretiyle bir sonraki ayın ilk gününü ölçüt alın.
• Taşma alanını boş bırakın: FİLTRE, SIRALA ve BENZERSİZ sonuçlarının taşacağı hücrelerde veri varsa Excel taşma hatası (#TAŞMA!) verir. Formülün altındaki ve yanındaki hücreleri temizleyin. Koşula uyan kayıt yoksa FİLTRE işlevi hata döndürür; bunu üçüncü bağımsız değişkenle bir metne çevirebilirsiniz.
• Sürüm farkını kontrol edin: Dinamik dizi işlevleri Excel 2019 ve öncesinde yoktur. Dosyayı bu sürümleri kullanan biriyle paylaşacaksanız özet tablolarda ÇOKETOPLA ve ÇOKEĞERSAY tabanlı çözümleri tercih edin.
• İlgili konular: ÇOKETOPLA, ÇOKEĞERSAY, ÇOKEĞERORTALAMA, ETOPLA, EĞERSAY, TARİH, SERİAY, SERİTARİH, BUGÜN, FİLTRE, SIRALA, BENZERSİZ, BAĞ_DEĞ_DOLU_SAY, İNDİS, KAÇINCI, MAK, Tablo olarak biçimlendirme ve Metni Sütunlara Dönüştür komutu.

Örneklerdeki tabloları indirilebilir Excel dosyasında kendiniz de deneyebilirsiniz.