Çok Hücreli Dizi Formülü

Çok Hücreli Dizi Formülü (Multi Cell Array Formula), seçili bir hücre aralığının tamamına tek bir formülle sonuç yazan dizi formülüdür. Aralıktaki her hücre, formülün ürettiği sonuç dizisinin bir öğesini gösterir ve tüm hücreler aynı formülü paylaşır. Böylece satır satır kopyalanan formüller yerine tek bir merkezden yönetilen, tutarlı bir hesap elde edersiniz.

Çalışma mantığı​

Normal bir formül tek bir değer döndürür. Dizi formülü ise aralıklar üzerinde işlem yapar ve birden çok değerden oluşan bir sonuç dizisi üretir. Bu dizi tek bir hücreye sığmıyorsa, formülü önceden seçtiğiniz aralığa yayarsanız çok hücreli dizi formülü elde edersiniz.

Örneğin adet ve birim fiyat sütunlarını satır satır çarpan bir sonuç sütunu için D2:D7 aralığına şu formül girilir:
Aralıktaki her hücrede formül çubuğunda aynı formül, küme parantezleri içinde görünür. D2 hücresi B2 ile C2'nin çarpımını, D3 hücresi B3 ile C3'ün çarpımını gösterir. Küme parantezlerini Excel kendisi ekler, elle yazmanız gerekmez.

Koşullu sonuç üretmek için işlevler de dizi içinde kullanılabilir:

Nasıl girilir​

Çok hücreli dizi formülünü girmek için şu adımları izleyin:

• Aralığı seçin: Sonuçların yazılacağı hücrelerin tamamını seçin (ör. D2:D7). Sonuç dizisinin boyutu ile seçilen aralığın boyutu uyumlu olmalıdır.
• Formülü yazın: Seçili aralık üzerindeyken formül çubuğuna ya da ilk hücreye formülü yazın.
• Onaylayın: Enter yerine Ctrl+Shift+Enter tuşlarına birlikte basın. Excel formülü küme parantezleri içine alır ve tüm seçili hücrelere uygular.

Microsoft 365 ve Excel 2021 gibi dinamik dizi destekli sürümlerde formülü yalnızca ilk hücreye yazıp Enter'a basmanız yeterlidir; sonuçlar komşu hücrelere kendiliğinden taşar. Eski sürümlerde ise Ctrl+Shift+Enter zorunludur. Makroyla girmek için Range nesnesinin FormulaArray özelliği kullanılır.

Düzenleme, silme ve sınırlar​

Çok hücreli dizi formülü tek bir bütündür; parçalarına ayrı ayrı müdahale edemezsiniz.

• Düzenleme: Dizinin yalnızca bir hücresini değiştirmeye çalışırsanız Excel dizinin bir bölümünün değiştirilemeyeceğini bildirir. Düzenlemek için dizideki herhangi bir hücreyi seçip F2 tuşuna basın, formülü değiştirin ve yeniden Ctrl+Shift+Enter ile onaylayın.
• Dizinin tamamını seçme: Dizi içindeki bir hücredeyken Ctrl+/ tuşlarına basın ya da Giriş > Düzenleme > Bul ve Seç > Özel Git (Home > Editing > Find & Select > Go To Special) yolundan Geçerli dizi (Current array) seçeneğini kullanın.
• Silme: Diziyi silmek için tüm aralığı seçip Delete tuşuna basın. Tek bir hücreyi silemezsiniz.
• Satır ve sütun ekleme: Dizi aralığının içine satır ya da sütun ekleyemezsiniz.
• Boyut uyumsuzluğu: Seçilen aralık sonuç dizisinden büyükse fazla hücrelerde #YOK (#N/A) hatası görünür; küçükse sonucun bir kısmı gösterilmez.
• Tablo kısıtı: Çok hücreli dizi formülleri Excel tablolarının (Ctrl+T) içinde kullanılamaz.

Dinamik dizilerle farkı​

Excel'in yeni sürümlerindeki dinamik dizi (taşan dizi) formülleri, çok hücreli dizi formüllerinin yaptığı işi aralığı önceden seçmeden yapar. Taşan formülde yalnızca ilk hücre formülü taşır, diğer hücreler sonucu gösterir ve taşma aralığı sonuç boyutuna göre otomatik değişir.

Eski Ctrl+Shift+Enter ile girilmiş küme parantezli formüller yeni sürümlerde de aynen çalışmaya devam eder ve aralığı sabittir. Dosyanızı eski sürüm kullanan kişilerle paylaşacaksanız çok hücreli dizi formülü daha güvenli bir seçimdir; taşan formüller eski sürümlerde düzgün çalışmaz.

1. Satır tutarlarını tek formülle hesaplama​

Ürün, adet ve birim fiyat verilerini girin. Ardından D2:D7 aralığını seçin, formül çubuğuna B2:B7 ile C2:C7 aralıklarını çarpan formülü yazın ve Ctrl+Shift+Enter ile onaylayın. Bu örnekte işlemi bir makro sizin yerinize yapar ve D2:D7 aralığına çok hücreli dizi formülünü yazar.
D2:D7 aralığındaki tüm hücreler aynı dizi formülünü paylaşır. D2 hücresi 5.400,00, D3 hücresi 4.500,00, D7 hücresi 6.480,00 değerini gösterir. D4 gibi tek bir hücreye tıklayıp silmeye ya da değiştirmeye çalışırsanız Excel dizinin bir bölümünün değiştirilemeyeceğini bildirir.

2. Birden çok sütunda dizi formülü ve koşul​

Personel, satış ve hedef verilerini girin. D2:D7 aralığına satışın hedefe oranını veren dizi formülünü, E2:E7 aralığına ise hedef aşıldıysa Tamam, aşılmadıysa Eksik yazan EĞER (IF) içeren dizi formülünü girin. Her aralık için önce aralığı seçip sonra Ctrl+Shift+Enter ile onaylayın. Örnekte iki formülü de makro yazar.
Sayfada iki ayrı çok hücreli dizi formülü vardır: D2:D7 ve E2:E7. İlk personelin gerçekleşme oranı D2 hücresinde 108%, durumu E2 hücresinde Tamam olarak görünür. Dördüncü personelin durumu E5 hücresinde Eksik değerini alır. Her aralık kendi içinde bir bütündür; D sütunundaki diziyi düzenlemek E sütununu etkilemez.

3. Dikey veriyi yataya çevirme​

Aylar ve ciro değerlerini A2:B5 aralığına girin. C2:F2 aralığını seçip DEVRİK_DÖNÜŞÜM (TRANSPOSE) işleviyle ay adlarını, C3:F3 aralığını seçip aynı işlevle ciro değerlerini yatay olarak yazdırın. Her ikisini de Ctrl+Shift+Enter ile onaylayın. Örnekte formülleri makro girer.
DEVRİK_DÖNÜŞÜM işlevi dikey bir aralığı yatay bir diziye çevirir ve sonucu ancak çok hücreli dizi formülüyle birden çok hücreye yazabilirsiniz. C2:F2 aralığında ilk ay C2 hücresinde Ocak, son ay F2 hücresinde Nisan olarak görünür. Ciro satırında C3 hücresi 125.000, F3 hücresi 156.000 değerini gösterir. Seçilen aralık yatay olarak 4 hücreden oluştuğu için kaynağın 4 satırıyla birebir uyumludur; aralığı 5 hücre seçseydiniz son hücrede #YOK hatası görürdünüz.

Kullanırken dikkat edin​

• Ctrl+Shift+Enter unutulursa: Eski sürümlerde aralığı seçip yalnızca Enter'a basarsanız formül yalnızca ilk hücreye normal formül olarak girilir ya da tüm hücrelerde aynı değer görünür. Formül çubuğunda küme parantezleri yoksa dizi formülü girilmemiş demektir.
• Küme parantezlerini elle yazmayın: Küme parantezleri formülün dizi olarak girildiğini gösteren işarettir ve yalnızca Ctrl+Shift+Enter ile oluşur. Elle yazdığınız parantezler formülü dizi formülü yapmaz.
• Aralık boyutunu sonuca göre seçin: Seçilen aralık sonuç dizisinden büyükse fazla hücrelerde #YOK (#N/A) hatası görünür. Aralık küçükse sonucun yalnızca bir kısmı gösterilir. Yatay ve dikey yönlere dikkat edin; DEVRİK_DÖNÜŞÜM gibi işlevlerde sonuç boyutu kaynağın tersidir.
• Parçalarına müdahale edilemez: Dizi içindeki tek bir hücreyi silemez, değiştiremez ya da dizinin ortasına satır ekleyemezsiniz. Değişiklik için tüm diziyi seçip F2 ile düzenleyin ve yeniden Ctrl+Shift+Enter ile onaylayın; silmek için tüm aralığı seçip Delete tuşuna basın.
• Tablolarda ve büyük verilerde: Çok hücreli dizi formülleri Excel tablolarının içinde kullanılamaz; bu durumda aralığı tablo dışına taşıyın ya da normal formüller kullanın. Çok büyük aralıklarda her hücre dizinin tamamını hesapladığından dosya yavaşlayabilir.
• Sürüm farkı: Microsoft 365 ve Excel 2021'de aynı iş tek hücreye girilen Enter ile taşan dizi olarak yapılabilir. Eski sürümlerde bu mümkün olmadığından çok hücreli dizi formülü gerekir. Dosyayı eski sürümlerle paylaşacaksanız küme parantezli diziler daha uyumludur.
• İlgili konular: Tek Hücreli Dizi Formülü, Dinamik Dizi Formülü (taşan formüller), Ctrl+Shift+Enter ile dizi girişi, Özel Git ve Geçerli dizi seçeneği, VBA'da FormulaArray özelliği, DEVRİK_DÖNÜŞÜM, EĞER, TOPLA.ÇARPIM, İNDİS, KAYDIR

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