Dizi Formülü

Dizi Formülü (Array Formula), tek bir değer yerine bir değerler kümesiyle (dizi) çalışan ve sonucu tek hücrede ya da birden çok hücrede veren formüldür. Koşullu toplama, benzersiz sayım, çok ölçütlü arama gibi işlemleri yardımcı sütun kullanmadan tek formülle yapmanızı sağlar. Microsoft 365'te dinamik dizi özelliği sayesinde Enter ile girilir; eski sürümlerde Ctrl+Shift+Enter gerekir.

Dizi formülü nasıl çalışır​

Normal bir formül her argümanda tek bir değerle çalışır. Dizi formülü ise bir hücre aralığındaki ya da bellekte üretilen değerler kümesiyle işlem yapar: işlemi kümenin her öğesine ayrı ayrı uygular, ara sonuçları bellekte tutar ve sonunda bunları tek bir değere indirger ya da birden çok hücreye yayar. Örneğin A2:A9 aralığında şehri İstanbul olan satırların C sütunundaki değerlerini toplamak için şu formül kullanılabilir:
Bu formülde EĞER işlevi aralıktaki her satırı ayrı ayrı sınar ve bir sonuç dizisi üretir; TOPLA bu diziyi tek sayıya indirger. Excel 2019 ve öncesinde böyle bir formül Ctrl+Shift+Enter ile girilmezse yanlış sonuç ya da hata verebilir. Microsoft 365 ve Excel 2021'de ise Enter yeterlidir.

Nerede bulunur ve nasıl girilir​

Dizi formülü için şeritte ayrı bir düğme yoktur; formül, formül çubuğunda ya da hücrede normal formül gibi yazılır. Girişin nasıl tamamlanacağı Excel sürümüne bağlıdır:

• Microsoft 365 ve Excel 2021: Formülü ilk hücreye yazıp Enter'a basın. Sonuç birden çok değerse komşu hücrelere otomatik taşar (spill). Taşan alana E2# gibi bir taşma başvurusuyla ulaşılır. Taşma alanındaki hücreler doluysa #TAŞMA! (#SPILL!) hatası görünür.
• Excel 2019 ve öncesi: Çok hücreli bir sonuç istiyorsanız önce sonuç aralığını seçin, formülü yazın ve Ctrl+Üst Karakter+Enter (Ctrl+Shift+Enter) tuşlarına basın. Excel formülü {=...} biçiminde küme parantezleri içine alır; parantezleri elle yazmayın.
• Düzenleme: Dizi formülü bulunan hücreyi seçin, F2'ye basın, değişikliği yapın ve eski sürümlerde yeniden Ctrl+Shift+Enter ile onaylayın.
• Diziyi seçme: Giriş > Düzenleme > Bul ve Seç > Özel Git > Geçerli Dizi (Home > Editing > Find & Select > Go To Special > Current Array) yolunu ya da Ctrl+/ kısayolunu kullanın.
• Adım adım izleme: Formüller > Formül Denetleme > Formülü Değerlendir (Formulas > Formula Auditing > Evaluate Formula) ile formülün ara sonuçlarını görebilirsiniz. Formül çubuğunda bir bölümü seçip F9'a basmak da o bölümün dizisini gösterir; Esc ile vazgeçin.

Dizi türleri​

Dizi formülleri sonuçlarına göre üç grupta incelenir:

• Tek hücreli dizi formülü: Ara hesaplar dizi olarak yapılır, sonuç tek hücreye yazılır. Koşullu toplama, benzersiz sayım, en büyük değeri koşula göre bulma bu gruba girer.
• Çok hücreli dizi formülü: Eski sürümlerde seçilen aralığın tamamına tek formül olarak girilir. Aralığın tek bir hücresi silinemez ya da değiştirilemez; formül ancak bütün olarak düzenlenir.
• Dinamik dizi formülü: Microsoft 365 ve Excel 2021'deki yeni davranıştır. Formül tek hücreye yazılır, sonuç gerektiği kadar hücreye taşar. FİLTRE, BENZERSİZ ve SIRALA gibi işlevler bu yapıyla çalışır.

Dizi sabitleri ve boyut uyumu​

Formülün içine doğrudan yazılan değer kümelerine dizi sabiti denir; küme parantezleriyle yazılır, örneğin {10\20\30}. Satır ve sütun ayırıcıları bölgesel ayarlara göre değişebilir; Türkçe ayarlarda genellikle sütunlar için ters eğik çizgi, satırlar için noktalı virgül kullanılır. Dizi sabitlerinde hücre başvurusu, ad ya da formül bulunamaz.
İki dizi işleme girdiğinde boyutlar uyumlu olmalıdır. Aynı boyuttaki diziler öğe öğe eşleşir; tek sütunlu bir dizi ile tek satırlı bir dizi çarpılırsa tablo (matris) oluşur. Boyutlar uyuşmuyorsa fazla kalan öğelerde #YOK (#N/A) hatası görünür.

1. Koşullu toplama (TOPLA.ÇARPIM ile)​

A1:D9 aralığına şehir, ürün, adet ve birim fiyat verilerini girin. B11 hücresine İstanbul'daki satırların adet ile birim fiyat çarpımını toplayan formülü, B12 hücresine ise Ankara'da satılan farelerin toplam adedini veren benzer bir formülü yazın. TOPLA.ÇARPIM, argümanlarını dizi olarak değerlendirdiği için Ctrl+Shift+Enter gerekmez. B11 formülü şöyledir:
İstanbul cirosu 38.200,00 olarak hesaplanır. Formülde her karşılaştırma bir DOĞRU/YANLIŞ dizisi üretir, çarpma işlemi bu değerleri 1 ve 0'a çevirir ve yalnızca koşulu sağlayan satırların adet ile fiyat çarpımı toplama girer. İki koşulun birlikte aranması, karşılaştırma dizilerinin çarpılmasıyla sağlanır; Ankara'da satılan fare adedi 30 bulunur.

2. Koşullu en büyük değer ve son eşleşme​

A1:C9 aralığına personel, departman ve prim tutarlarını girin. C10 hücresine yalnızca Satış departmanındaki en yüksek primi bulan formülü yazın. TOPLAMA işlevinin 14 numaralı işlevi (BÜYÜK) ve 6 numaralı seçeneği (hataları yok say) kullanıldığında, koşulu sağlamayan satırlarda oluşan sıfıra bölme hataları yok sayılır ve formül dizi olarak çalışır:
C11 hücresinde İNDİS ve KAÇINCI işlevlerini, içteki İNDİS işlevine 0 satır numarası vererek birleştirin; bu yöntem Ctrl+Shift+Enter gerektirmeden diziyi hesaplatır. C12 hücresinde ise ARA işleviyle Satış departmanında listedeki son kişiyi bulun.
Satış departmanındaki en yüksek prim 9.750,00 olarak bulunur. C11 hücresi bu tutarı alan kişiyi, yani Elif Demir adını döndürür: iç İNDİS işlevi departman ve tutar koşullarının çarpımından 1 ve 0 dizisi üretir, KAÇINCI bu dizide ilk 1'in sırasını bulur. C12 hücresindeki ARA formülü, koşulu sağlamayan satırlarda hata üreten bir dizi kurar ve hataları atlayarak son uygun satırı seçer; sonuç Oğuz Çelik olur.

3. Benzersiz sayım ve VEYA mantığı​

A1:C9 aralığına müşteri, ürün ve miktar verilerini girin. C10 hücresinde listedeki müşteri sayısını yinelenenleri bir kez sayarak bulun. Her müşterinin kaç kez geçtiğini EĞERSAY işlevi dizi olarak verir; 1 değerini bu sayılara böldüğünüzde her müşteri toplamda 1 eder:
C11 hücresinde Kalem ürününün toplam miktarını, C12 hücresinde ise ürünü Kalem ya da Silgi olan satırların sayısını hesaplayın. VEYA mantığı için koşullar çarpılmaz, toplanır ve sonucun sıfırdan büyük olup olmadığına bakılır.
Listede toplam 4 farklı müşteri vardır. Bir müşteri üç kez geçiyorsa her satırı 1/3 değerini üretir ve müşteri toplamda tek sayılır. Kalem toplamı 390 bulunur. C12 hücresinde iki koşul toplanıp sıfırdan büyük olanlar sayıldığı için Kalem ya da Silgi olan 5 satır bulunur. Çift eksi işareti DOĞRU/YANLIŞ değerlerini 1 ve 0'a çevirir.

Kullanırken dikkat edin​

• Küme parantezini elle yazmayın: Eski sürümlerde formülün başındaki ve sonundaki { } işaretlerini Excel Ctrl+Shift+Enter ile ekler. Parantezleri klavyeden yazarsanız formül dizi formülüne dönüşmez, metin gibi davranır.
• Çok hücreli dizinin bir parçası değiştirilemez: Eski sürümlerde birden çok hücreye girilmiş dizi formülünün tek bir hücresini silmeye ya da düzenlemeye çalışırsanız Excel bunu engeller. Önce dizinin tamamını seçin (Ctrl+/), sonra silin ya da formülü bütün olarak yeniden girin.
• Boyutları uyumlu tutun: Birlikte işlenen aralıklar aynı satır ve sütun sayısına sahip olmalıdır. TOPLA.ÇARPIM'da boyutlar farklıysa sonuç #DEĞER! (#VALUE!) olur. Metin içeren hücreler çarpma işlemine girerse yine #DEĞER! hatası oluşur; virgül ya da noktalı virgülle ayrılmış TOPLA.ÇARPIM argümanları ise metni sıfır sayar.
• Boş hücreler sayımı bozar: Benzersiz sayım formülünde aralıkta boş hücre varsa EĞERSAY sıfır döndürür ve bölme işlemi #SAYI/0! (#DIV/0!) hatası verir. Aralığı yalnızca dolu hücreleri kapsayacak şekilde seçin ya da formülü boşları dışlayacak biçimde genişletin.
• Performansı düşünün: Dizi formülleri her hesaplamada bütün aralığı işler. Tam sütun başvuruları (A:A gibi) ve çok sayıda dizi formülü dosyayı yavaşlatır. Gerçek veri aralığını kullanın ya da veriyi tabloya çevirin.
• Sürüm farkları: Microsoft 365 ve Excel 2021'de yazılan dinamik dizi formülleri eski sürümlerde açıldığında {=...} biçiminde dizi formülü olarak görünür ve taşma özelliği çalışmaz. Eski bir dosyada formüllerin başında @ işareti görürseniz bu, örtülü kesişimin korunduğunu gösterir. Birleştirilmiş hücrelere dizi formülü girilemez.
• İlgili konular: İlgili öğeler: TOPLA.ÇARPIM, TOPLAMA, İNDİS, KAÇINCI, ARA, EĞERSAY, FİLTRE, BENZERSİZ, SIRALA, DEVRİK_DÖNÜŞÜM, ÇARPIM.MATRİS, Formülü Değerlendir, Özel Git, taşma başvurusu ve dizi sabitleri.

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