Dinamik Dizi

Nerede bulunur ve nasıl çalışır​

• F2: seçili hücrede formülü düzenleme moduna alır.
• Enter: dinamik dizi formülünü onaylar; eski sürümlerdeki Ctrl+Shift+Enter gerekmez.
• Ctrl+/: bulunduğunuz hücrenin ait olduğu dizinin ya da taşma aralığının tamamını seçer.
• Ctrl+ters tırnak : sonuçlar yerine formülleri gösterir; taşma aralıklarını incelemek için kullanışlıdır.
• Ctrl+Alt+V: Özel Yapıştır penceresini açar; dinamik sonucu sabit değere çevirmek için kullanılır.

Adım adım ilk dinamik formül​

• Hücreyi seçin: D2 gibi, altında ve sağında yeterince boş hücre bulunan bir hücre seçin.
• Formülü yazın: aşağıda verilen aralık çarpımını yazın; tek bir satıra kopyalamanıza gerek yoktur.
• Enter'a basın: sonuç D2:D7 aralığına taşar; Ctrl+Shift+Enter ya da doldurma tutamacı gerekmez.
• Taşma aralığını inceleyin: D4 gibi taşan bir hücre seçin; formül çubuğundaki formül gri görünür, çünkü formül yalnızca D2 hücresindedir.
• Kaynağı değiştirin: B ya da C sütunundaki bir sayıyı değiştirin; sonuç anında güncellenir.
• Sonuca başvurun: başka bir hücrede D2# yazarak taşma aralığının tamamına başvurabilirsiniz.

=B2:B7*C2:C7

=TOPLA(D2#)

=SUM(D2#)

Dinamik dizi işlevleri ve seçimi​

• SORT: bir aralığı ya da diziyi belirtilen sütuna göre artan ya da azalan sıralar. Tek ölçüt yeterliyse seçin.
• SORTBY: sıralama ölçütü sonuç dizisinde bulunmayan ya da birden çok ayrı aralık olan durumlarda kullanılır.
• FILTER: koşulu sağlayan satırları ya da sütunları döndürür; koşul sağlanmazsa üçüncü bağımsız değişkenle bir ileti verebilirsiniz, yoksa #CALC! hatası alırsınız.
• UNIQUE: yinelenenleri ayıklar; ikinci bağımsız değişken sütunlara göre karşılaştırma yapar, üçüncüsü TRUE ise yalnızca bir kez geçen değerleri döndürür.
• SEQUENCE: satır, sütun, başlangıç ve artış vererek sayı dizisi üretir; tarih, sıra numarası ve matris kurmada kullanılır.
• RANDARRAY: rastgele sayı dizisi üretir; her hesaplamada değişir.
• XLOOKUP ve XMATCH: XLOOKUP, dönüş aralığı birden çok sütunsa satırın tamamını taşırır; DÜŞEYARA'nın dizi döndüren modern karşılığıdır.
• LET: ara hesapları adlandırıp tekrar kullanmanızı sağlar; uzun formülleri okunur ve hızlı yapar (Excel 2021 ve Microsoft 365).
• Yeni dizi işlevleri: TAKE, DROP, VSTACK, HSTACK, TOCOL, TOROW, CHOOSECOLS, EXPAND, TEXTSPLIT ve LAMBDA ile çalışan MAP, REDUCE, SCAN, BYROW, BYCOL işlevleri Microsoft 365 abonelerinde bulunur; Excel 2021'de yoktur.

Taşma aralığı ve # işleci​

=SIRALA(FİLTRE(A2:C10;B2:B10="Satış");3;-1)

=SORT(FILTER(A2:C10,B2:B10="Satış"),3,-1)

• Başka formüller: TOPLA, SATIRSAY ya da EĞER gibi işlevlerin bağımsız değişkeni olarak E2# yazılabilir.
• Veri doğrulama: Veri > Veri Doğrulama (Data > Data Validation) penceresinde Liste kaynağına E2# yazarsanız açılır liste, taşma aralığıyla birlikte büyür.
• Grafik: Ad Yöneticisi'nde (Formüller > Tanımlı Adlar > Ad Yöneticisi) taşma aralığına başvuran bir ad tanımlayıp grafik serisinde bu adı kullanırsanız grafik dinamik olur.
• Başka sayfa: başka sayfadaki taşma aralığına Sayfa1!E2# biçiminde başvurulur.

Örtük kesişim, @ işareti ve VBA​

Sub DinamikFormulYaz()
    Range("D2").Formula2 = "=B2:B7*C2:C7"
    If Range("D2").HasSpill Then
        Debug.Print Range("D2").SpillingToRange.Address
    End If
End Sub

Sürüm farkları ve eski dizi formülleri​

• Excel 2016 ve 2019: dinamik dizi yoktur. SORT, FILTER, UNIQUE, SEQUENCE gibi işlevler #AD? hatası verir; dosya bu sürümde açılırsa formül başında _xlfn ön eki görünebilir.
• Excel 2021: SORT, SORTBY, FILTER, UNIQUE, SEQUENCE, RANDARRAY, XLOOKUP, XMATCH ve LET vardır; TAKE, VSTACK, TEXTSPLIT ve LAMBDA aileleri yoktur.
• Microsoft 365: tüm işlevler bulunur; yeni işlevler kademeli olarak ve bazı kanallara daha geç gelir.
• Excel Web: dinamik dizileri destekler; makro (VBA) çalışmaz.
• Excel Mac: Microsoft 365 ve 2021 sürümlerinde desteklenir; Mac için Excel 2019 ve öncesinde yoktur.

Sınırlar ve performans​

• Sayfa sınırı: taşma aralığı çalışma sayfasının 1.048.576 satır ve 16.384 sütun sınırını aşamaz; aşarsa #SPILL! hatası oluşur.
• Dizi içinde dizi yoktur: bir dizinin elemanı başka bir dizi olamaz; böyle bir sonuç #CALC! hatası verir. Çözüm olarak sonucu tek tabloya indirgeyin.
• Tablo içinde taşmaz: Excel Tablosu (Ctrl+T ile oluşturulan) içine dinamik dizi formülü yazılamaz; formülü tablonun dışına yazın ve tabloya yapılandırılmış başvuruyla bakın.
• Birleştirilmiş hücreler: taşma aralığı birleştirilmiş hücreye çarparsa #SPILL! oluşur.
• Veri tablosu: Ya Olursa analizindeki Veri Tablosu (Data Table) ile dinamik dizi birlikte kullanılamaz.

• Tam sütun başvurularından kaçının: A:A gibi başvurular dizi aritmetiğinde yaklaşık bir milyon satırı hesaplatır; gerçek veri aralığını ya da bir Tablo sütununu kullanın.
• Ara sonucu LET ile bir kez hesaplayın: aynı FILTER ya da SORT ifadesini formülde birden çok yazmak hesaplamayı katlar.
• RANDARRAY uçucudur: her hesaplamada yeniden çalışır; büyük çalışma kitaplarında kullanımını sınırlayın.
• Zincirleme taşmalardan kaçının: bir taşmanın üzerine onlarca taşma bağlamak, kaynak değiştiğinde zincirleme yeniden hesaplamaya yol açar.

Neden çalışmıyor​

• #SPILL! (engel): Belirti: formül hücresinde hata görünür. Neden: taşma aralığındaki bir hücrede veri, boşluk karakteri ya da biçimlendirilmiş içerik var. Çözüm: hata simgesine tıklayıp Engelleyen Hücreleri Seç komutunu kullanın ve engelleri silin ya da taşıyın.
• #SPILL! (tablo içinde): Belirti: Tablo içinde yazılan formül hata verir. Neden: Tablolar dinamik taşmayı desteklemez. Çözüm: formülü tablonun dışındaki bir hücreye taşıyın.
• #SPILL! (birleştirilmiş hücre ya da çok büyük): Belirti: hata türü 'taşma çok büyük' ya da 'birleştirilmiş hücre' olarak görünür. Neden: sonuç sayfa sınırını aşıyor ya da birleştirilmiş hücreye çarpıyor. Çözüm: birleştirmeyi kaldırın, başvuruları gerçek veri aralığıyla sınırlayın.
• #CALC!: Belirti: FILTER veya UNIQUE sonucunda hata. Neden: koşulu sağlayan satır yok ya da sonuç dizi içinde dizi. Çözüm: FILTER'ın üçüncü bağımsız değişkenine bir ileti yazın, sonuç yapısını sadeleştirin.
• #AD? (#NAME?): Belirti: yeni işlev tanınmıyor. Neden: Excel 2016 ya da 2019 kullanıyorsunuz, ya da işlev adını yanlış yazdınız. Çözüm: sürümünüzü kontrol edin; Türkçe Excel'de adı İşlev Ekle penceresinden seçin.
• Formülün başında @ görünüyor: Belirti: sonuç tek hücrede kalıyor. Neden: eski bir dosyada ya da VBA'da Formula ile yazılmış. Çözüm: @ işaretini silip Enter'a basın; VBA'da Formula2 kullanın.
• Formül metin olarak görünüyor: Belirti: hücrede formül yazısı duruyor. Neden: hücre Metin biçimli ya da formülün önünde kesme işareti var. Çözüm: biçimi Genel yapın, F2 ve Enter ile yeniden girin.
• Sonuç güncellenmiyor: Belirti: kaynak değişince değerler değişmiyor. Neden: hesaplama Elle ayarında. Çözüm: Formüller > Hesaplama Seçenekleri > Otomatik (Formulas > Calculation Options > Automatic) seçin ya da F9'a basın.
• Formül çalışıyor ama taşmıyor: Belirti: yalnızca ilk değer görünüyor. Neden: formül tek değer döndürüyor ya da işlev dizi kabul etmiyor. Çözüm: bağımsız değişkenlerin aralık olduğundan emin olun.

1. Temel taşma ve toplam​

• Verileri A1:C7 aralığına girin, D1 hücresine Tutar, F1 hücresine Genel Toplam yazın.
• D2 hücresini seçin ve aşağıdaki formülü yazın, ardından Enter'a basın.
• F2 hücresine, taşma aralığını toplayan ikinci formülü yazın.
• D2:D7 aralığını seçip Giriş sekmesindeki Sayı grubundan Binlik Ayracı ve iki ondalık biçimini uygulayın.

=B2:B7*C2:C7

=TOPLA(D2#)

=SUM(D2#)

2. Tek hücrede dizi hesabı​

• Verileri A1:C9 aralığına yazın; D1 hücresine Şehir, D2 hücresine İstanbul yazın. F1 hücresine Toplam, G1 hücresine En Yüksek başlığını girin.
• F2 hücresine toplam formülünü yazıp Enter'a basın.
• G2 hücresine en yüksek değeri bulan formülü yazıp Enter'a basın.
• Her iki hücreyi Binlik Ayracı biçimiyle biçimlendirin.

=TOPLA((B2:B9=D2)*C2:C9)

=SUM((B2:B9=D2)*C2:C9)

=MAK(EĞER(B2:B9=D2;C2:C9))

=MAX(IF(B2:B9=D2,C2:C9))

3. SORT ile sıralama​

• Verileri A1:C9 aralığına yazın, E1:G1 hücrelerine sıralı listenin başlıklarını girin.
• E2 hücresini seçin ve SORT işlevini yazın: ilk bağımsız değişken tablo, ikincisi sıralanacak sütunun sırası (3), üçüncüsü yön (-1 azalan).
• Enter'a basın; sonuç E2:G9 aralığına taşar.
• Kaynak listede bir notu değiştirip sıranın kendiliğinden yenilendiğini görün.

=SIRALA(A2:C9;3;-1)

=SORT(A2:C9,3,-1)

4. FILTER ile koşullu liste​

• Verileri A1:D9 aralığına yazın, F1:I1 hücrelerine sonuç başlıklarını girin.
• F2 hücresine FILTER formülünü yazın: ilk bağımsız değişken döndürülecek tablo, ikincisi koşul, üçüncüsü sonuç boşsa görünecek ileti.
• Enter'a basın; koşulu sağlayan satırlar F2:I5 aralığına taşar.
• C sütunundaki bir stok değerini asgari değerin üzerine çıkarın; ürünün listeden kendiliğinden çıktığını görün.

=FİLTRE(A2:D9;C2:C9<D2:D9;"Yok")

=FILTER(A2:D9,C2:C9<D2:D9,"Yok")

5. UNIQUE ile tekil liste​

• Verileri A1:C10 aralığına yazın; E1, G1 ve H1 hücrelerine başlıkları girin.
• E2 hücresine UNIQUE formülünü yazıp Enter'a basın; tekil şehirler alt alta taşar.
• G2 hücresine, taşma aralığındaki satırları sayan ikinci formülü yazın.
• H2 hücresine UNIQUE işlevinin üçüncü bağımsız değişkenini TRUE yaparak yalnızca bir kez geçenleri listeleyin.

=BENZERSİZ(B2:B10)

=UNIQUE(B2:B10)

=SATIRSAY(E2#)

=ROWS(E2#)

=BENZERSİZ(B2:B10;YANLIŞ;DOĞRU)

=UNIQUE(B2:B10,FALSE,TRUE)

6. SEQUENCE ile takvim ve matris​

• Giderleri A1:B5 aralığına yazın; D1, E1 ve G1 hücrelerine başlıkları girin.
• D2 hücresine, 12 ayın ilk günlerini döndüren tarih formülünü yazın ve sonucu mmmm yyyy biçiminde gösterin.
• E2 hücresine, gider toplamını ay numarasıyla çarpan formülü yazın.
• G2 hücresine 3 satır, 4 sütunluk bir matris üreten formülü yazın.

=DATE(2025,SEQUENCE(12),1)

=SUM($B$2:$B$5)*SEQUENCE(12)

=SEQUENCE(3,4,10,5)

7. SORT ve FILTER birlikte​

• Verileri A1:C10 aralığına yazın; D1 hücresine Aranan Departman, D2 hücresine Satış yazın. F1:H1 hücrelerine sonuç başlıklarını girin.
• F2 hücresine SORT içinde FILTER kullanan formülü yazıp Enter'a basın.
• H2:H10 aralığına Binlik Ayracı biçimini uygulayın.
• D2 hücresine Muhasebe yazarak listenin değiştiğini görün.

=SIRALA(FİLTRE(A2:C10;B2:B10=D2;"Kayıt yok");3;-1)

=SORT(FILTER(A2:C10,B2:B10=D2,"Kayıt yok"),3,-1)

8. UNIQUE ile dinamik özet​

• Verileri A1:C10 aralığına yazın; E1, F1 ve G1 hücrelerine Şehir, Toplam, Pay başlıklarını girin.
• E2 hücresine tekil şehirleri veren formülü yazın.
• F2 hücresine, E2# başvurusunu ölçüt olarak kullanan koşullu toplam formülünü yazın.
• G2 hücresine toplamın genel toplama oranını veren formülü yazıp sütunu yüzde biçimine çevirin.

=BENZERSİZ(B2:B10)

=UNIQUE(B2:B10)

=ETOPLA(B2:B10;E2#;C2:C10)

=SUMIF(B2:B10,E2#,C2:C10)

=F2#/TOPLA(F2#)

=F2#/SUM(F2#)

9. XLOOKUP ile çok sütunlu arama​

• Verileri A1:D9 aralığına yazın; E1 hücresine Aranan Personel, E2 hücresine Elif Çelik yazın. G1:I1 hücrelerine sonuç başlıklarını girin.
• G2 hücresine XLOOKUP formülünü yazın: aranan değer, arama aralığı, dönüş aralığı (B2:D9) ve bulunamazsa iletisi.
• Enter'a basın; sonuç G2:I2 aralığına taşar.
• I2 hücresine Binlik Ayracı biçimini uygulayın ve E2'deki adı değiştirerek farklı bir kişiyi arayın.

=ÇAPRAZARA(E2;A2:A9;B2:D9;"Bulunamadı")

=XLOOKUP(E2,A2:A9,B2:D9,"Bulunamadı")

10. SPILL hatası ve engel​

• A1:A7 aralığına ürün adlarını yazın; C1 ve E1 hücrelerine başlıkları girin.
• C4 hücresine herhangi bir metin (örneğin Engel) yazın.
• C2 hücresine SORT formülünü yazıp Enter'a basın; taşma yolunda C4 dolu olduğundan #SPILL! hatası görünür.
• Aynı formülü E2 hücresine yazın; engel olmadığı için liste düzgün taşar.
• C2 hücresindeki uyarı simgesine tıklayıp Engelleyen Hücreleri Seç komutunu seçin ve C4 hücresini silin; liste taşar.

=SIRALA(A2:A7)

=SORT(A2:A7)

Kullanırken dikkat edin​

• Sürümünüzü kontrol edin: Dinamik dizi Microsoft 365, Excel 2021 ve sonrası, Excel Web ve Mac sürümlerinde çalışır. Excel 2016 ve 2019'da SORT, FILTER, UNIQUE gibi işlevler tanınmaz; bu dosyayı paylaşacağınız kişilerin sürümünü önceden öğrenin.
• Taşma yolu boş olmalı: Taşma aralığındaki hücrelerde veri, boşluk karakteri ya da formül varsa #SPILL! hatası oluşur. Hata simgesinden Engelleyen Hücreleri Seç komutuyla engelleri bulabilirsiniz; görünmeyen boşluk karakterlerini de denetleyin.
• Tabloda taşma yapmaz: Excel Tablosu (Ctrl+T) içine dinamik dizi formülü yazamazsınız. Formülü tablonun dışına koyun; tablodaki bir sütunu işleve vermek için yapılandırılmış başvuruyu (örneğin Satışlar[Tutar]) kullanın.
• # işlecini tercih edin: Sonuç aralığına E2:E20 gibi sabit adresle başvurmak, sonuç büyüdüğünde eksik hesaba yol açar. E2# yazmak, taşma aralığı ne kadar büyürse büyüsün başvurunun uyumlu kalmasını sağlar.
• Taşan hücreleri düzenleyemezsiniz: Taşma aralığındaki tek bir hücreyi silemez ya da yazamazsınız. Değişiklik için sol üst hücredeki formülü düzenleyin ya da tüm aralığı silmek için o hücreyi silin.
• VBA'da Formula2 kullanın: Dinamik dizi sonucu bekliyorsanız makroda Formula2 özelliğini kullanın. Formula ile yazılan formüle Excel @ ekler ve sonuç taşmaz. Taşma durumunu HasSpill ve SpillingToRange ile denetleyebilirsiniz.
• @ işaretini silmeyi unutmayın: Eski dosyalarda formülün önünde @ görünüyorsa Excel tek değer beklemektedir. Taşmasını istediğiniz formüllerde @ işaretini silip Enter'a basın.
• Tam sütun başvurusundan kaçının: Dizi aritmetiğinde A:A gibi tam sütun başvuruları yaklaşık bir milyon satırı hesaplatır ve çalışma kitabını yavaşlatır. Gerçek veri aralığını ya da bir Tablo sütununu kullanın; LET ile ara sonuçları bir kez hesaplayın.
• Eski dizi formüllerini dönüştürme: Ctrl+Shift+Enter ile girilmiş süslü parantezli formülleri dinamik biçime çevirmek için hücreyi seçin, F2 ve Enter'a basın. Sonuç boyutunun değişebileceğini ve altındaki hücrelerin boş olması gerektiğini unutmayın.
• Sonucu sabit değere çevirme: Taşma aralığını seçip kopyalayın, Giriş > Yapıştır > Değerleri Yapıştır (Home > Paste > Values) komutuyla aynı yere yapıştırın. Bu işlem formülü kaldırır ve değerleri sabitler; sonuç artık kaynak veriyle güncellenmez.
• Biçimlendirmeyi geniş tutun: Sayı biçimi ve koşullu biçim hücre bazlıdır; taşma aralığı büyürse yeni hücreler biçimi taşımaz. Sonucun büyüyebileceği sütunun tamamına biçim uygulayın.
• Veri > Sırala işe yaramaz: Taşma aralığına Veri > Sırala (Data > Sort) ya da Otomatik Filtre uygulayamazsınız; sıralama ve süzme için kaynak veriyi değiştirmeyin, formülde SORT ya da FILTER kullanın.
• Birleştirilmiş hücrelerden uzak durun: Taşma yolundaki birleştirilmiş hücreler #SPILL! hatasına neden olur. Raporunuzda başlık için birleştirme yerine Seçim Üzerinde Ortala (Center Across Selection) biçimini kullanın.
• Boş sonuç için ileti verin: FILTER hiç eşleşme bulamazsa #CALC! hatası verir. Üçüncü bağımsız değişkene Kayıt yok gibi bir ileti yazarak raporunuzun hata göstermesini önleyin.
• İlgili konular:
  • İşlevler: SIRALA (SORT), FİLTRE (FILTER), BENZERSİZ (UNIQUE), SEQUENCE, ÇAPRAZARA (XLOOKUP), DÜŞEYARA, TRANSPOZE, TOPLA, ETOPLA, EĞER
  • Özellikler: Dizi Formülü (Ctrl+Shift+Enter), Excel Tablosu, Veri Doğrulama, Özet Tablo, Ad Yöneticisi
  • VBA öğeleri: Range.Formula2, Range.HasSpill, Range.SpillingToRange, Range.SpillParent