Matris Dizi Formülleriyle Çoklu Veri İşleme

Matris dizi formülleri (array formulas), bir aralıktaki birçok değeri tek bir formülle birlikte işlemenizi sağlar; sonuç tek hücrede olabileceği gibi birden çok hücreye de yayılabilir. Excel 365'te formülü yazıp Enter tuşuna basmanız yeterlidir, eski sürümlerde ise Ctrl+Shift+Enter ile girilir. Aşağıda koşullu toplama, FİLTRE ile liste çıkarma ve matris çarpımı örneklerini adım adım bulacaksınız.

Matris dizi formülü nedir ve nasıl girilir​

Dizi formülü, bir işlevin tek bir değer yerine bir değer kümesi üzerinde çalışmasını sağlar. Örneğin yalnızca İstanbul satırlarının adetlerini toplamak için yardımcı sütun açmanız gerekmez; karşılaştırma ve çarpma işlemleri tüm satırlara aynı anda uygulanır.
İstanbul satırlarının toplam adedini bulan formül şöyledir:

• Excel 365 ve Excel 2021: Formülü yazıp Enter tuşuna basın; dizi davranışı otomatik çalışır.
• Eski sürümler: Formülü Ctrl+Shift+Enter ile girin; Excel formülü süslü parantez içine alır. Süslü parantezleri elle yazmayın.
• Çok hücreli sonuç: Eski sürümlerde önce sonucun yayılacağı aralığı seçin, formülü yazın ve Ctrl+Shift+Enter ile onaylayın.

Dinamik dizi işlevleri ve taşma nasıl çalışır​

Excel 365 ve Excel 2021'deki dinamik dizi işlevleri, sonucu tek bir formül hücresinden komşu hücrelere taşırır. Bu davranışa taşma (spill) denir. FİLTRE (FILTER), SIRALA (SORT) ve BENZERSİZ (UNIQUE) bu işlevlerin en çok kullanılanlarıdır ve birbirinin içine yerleştirilebilir.
Satış departmanında maaşı 30.000 ve üzeri olan personeli alfabetik sıralı listeleyen formül şöyledir:

• Taşma aralığı başvurusu: Formülün bulunduğu hücrenin adresine diyez işareti eklediğinizde (örneğin E2#) taşan sonuç aralığının tamamına başvurmuş olursunuz; kaynak veri değiştikçe aralık kendiliğinden büyür ya da küçülür.
• Taşma hatası: Sonucun yayılacağı hücrelerden biri doluysa formül #TAŞMA! hatası verir. Engelleyen hücreleri temizlediğinizde sonuç görünür.
• Düzenleme: Taşan hücrelerden birini silemezsiniz; değişiklik yalnızca formülün bulunduğu ilk hücrede yapılır.

Matris çarpımı ve koşullu dizi işlemleri​

Matris çarpımında ilk matrisin sütun sayısı, ikinci matrisin satır sayısına eşit olmalıdır. Sonuç, ilk matrisin satır sayısı ile ikinci matrisin sütun sayısı kadar boyuta sahip olur. Excel'de bu işlem DÇARP (MMULT) ile yapılır; satır halindeki bir aralığı sütuna çevirmek için DEVRİK_DÖNÜŞÜM (TRANSPOSE) kullanılır.
Üç kanala sevk edilen ürün adetlerini, kanal başına birim kargo maliyetiyle çarpıp ürün bazında toplayan formül şöyledir:
Koşullu dizi işlemlerinde karşılaştırmalar DOĞRU ya da YANLIŞ üretir; bunları çarparak (VE mantığı) ya da toplayarak (VEYA mantığı) sayıya çevirirsiniz. Satır bazında çarpım toplamı için eski sürümlerde bile Ctrl+Shift+Enter gerektirmeyen TOPLA.ÇARPIM (SUMPRODUCT) işlevini de kullanabilirsiniz.

1. Koşullu toplamayı tek formülle yapma​

A1:D9 aralığında şehir, ürün, adet ve birim fiyat bilgileri var. D10 hücresinde yalnızca İstanbul satırlarının cirosu, D11 hücresinde İstanbul ve Ankara satırlarının toplam cirosu hesaplanır. Yardımcı sütun kullanılmaz; karşılaştırma, adet ve fiyat çarpımı TOPLA işlevinin içinde dizi olarak işlenir.
D10 hücresi yalnızca İstanbul satırlarının adet ve fiyat çarpımlarını toplar ve 376.300,00 sonucunu verir. D11 hücresinde iki şehir koşulu toplama işaretiyle birleştirildiği için VEYA mantığı uygulanır ve sonuç 646.000,00 olur. Koşullar çarpılırsa VE mantığı elde edersiniz.

2. FİLTRE ile koşula uyan listeyi çıkarma​

A1:C9 aralığında personel, departman ve maaş bilgileri var. E2 hücresine yazılan tek formül, Satış departmanında maaşı 30.000 ve üzeri olan personeli alfabetik sırayla listeler. Taşacak hücreler (E3 ve altı) boş bırakılmıştır.
E2 hücresindeki tek formül iki koşulu çarparak VE mantığıyla uygular, uyan adları SIRALA ile alfabetik dizer ve sonucu aşağı doğru taşırır. Listenin ilk adı Ayşe Yılmaz olur. Kaynak tabloda bir maaş ya da departman değiştiğinde liste kendiliğinden güncellenir; taşma alanının boş kalması gerektiğini unutmayın.

3. DÇARP ile matris çarpımı​

B2:D5 aralığında dört ürünün Mağaza, Online ve Bayi kanallarına sevk edilen adetleri, B7:D7 aralığında ise her kanal için birim kargo maliyeti yer alıyor. F2 hücresine yazılan tek formül, her ürünün toplam kargo maliyetini F2:F5 aralığına yayar. F3:F5 hücreleri taşma için boş bırakılmıştır.
Adet matrisi 4 satır ve 3 sütun, maliyet vektörü ise DEVRİK_DÖNÜŞÜM ile 3 satır ve 1 sütuna çevrildiği için çarpım uyumludur. Sonuç 4 satır ve 1 sütundur; ilk ürün olan Telefon için kargo maliyeti 5.540,00 olur. Diğer üç ürünün değeri F3:F5 hücrelerine taşar.

Sık sorulan sorular​

Dizi formülündeki süslü parantezleri elle yazabilir miyim?
Hayır. Süslü parantezleri yalnızca Excel, eski sürümlerde Ctrl+Shift+Enter ile girdiğinizde ekler; elle yazılan parantezler formülü dizi formülü yapmaz.
#TAŞMA! hatasını neden alıyorum?
Sonucun yayılacağı alanda dolu ya da birleştirilmiş hücre vardır. Ayrıca dinamik dizi formülleri Excel tablosu (Ekle > Tablo) içinde taşamaz; formülü tablo dışındaki bir hücreye yazın.
Dinamik dizi işlevleri eski Excel sürümlerinde çalışır mı?
FİLTRE, SIRALA ve BENZERSİZ gibi işlevler yalnızca Excel 2021 ve Microsoft 365'te bulunur. Daha eski sürümlerde Ctrl+Shift+Enter ile girilen klasik dizi formülleri ve TOPLA.ÇARPIM kullanılır.
Dizi formülleri çalışma kitabını yavaşlatır mı?
Çok büyük aralıklarda ve çok sayıda dizi formülünde hesaplama süresi uzayabilir. Başvuruları verinin gerçek boyutuyla sınırlamak ve gereksiz tekrarları önlemek iyi bir yöntemdir.

Kullanırken dikkat edin​

• Aralık boyutlarını kontrol edin: Dizi işlemlerinde karşılaştırılan aralıkların satır ve sütun sayısı uyumlu olmalıdır. Boyutlar uyuşmadığında fazlalık hücreler hata verebilir. Matris çarpımında ilk matrisin sütun sayısı, ikinci matrisin satır sayısına eşit değilse sonuç hata olur.
• Taşma alanını boş bırakın: Dinamik dizi formülünün sonucu yayılacağı hücrelerde veri varsa #TAŞMA! hatası görürsünüz. Formül yazmadan önce hedef alanın boş olduğundan emin olun.
• Sürüm uyumuna dikkat edin: FİLTRE, SIRALA ve BENZERSİZ gibi işlevler Excel 2019 ve öncesinde bulunmaz. Dosyayı bu sürümleri kullanan biriyle paylaşacaksanız klasik dizi formüllerini ya da TOPLA.ÇARPIM işlevini tercih edin.
• Formülü adım adım izleyin: Karmaşık bir dizi formülünün hangi aşamada hata verdiğini bulmak için hücreyi seçip Formüller > Formül Denetleme > Formülü Değerlendir (Formulas > Formula Auditing > Evaluate Formula) komutunu kullanın. Böylece her işlemin ara sonucunu görürsünüz.
• Büyük aralıklarda hesaplamayı hafif tutun: Tüm sütunu kapsayan başvurular yerine verinin gerçek boyutuna uygun aralıklar kullanın. Çok sayıda dizi formülü içeren kitaplarda hesaplama süresi uzayabilir.
• İlgili konular:
  • İşlevler: TOPLA.ÇARPIM, DÇARP, DEVRİK_DÖNÜŞÜM, FİLTRE, SIRALA, BENZERSİZ, EĞER, TOPLA
  • Özellikler: Dinamik diziler ve taşma, Formülü Değerlendir, Excel tabloları

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