Dinamik Excel

Dinamik Excel (Dynamic Excel), tek bir formülün birden çok hücreye sonuç döndürebildiği ve kaynak veri değiştiğinde sonuç alanının kendiliğinden büyüyüp küçüldüğü modern Excel hesaplama yapısıdır. Dinamik dizi motoru Microsoft 365, Excel 2021 ve sonraki sürümlerde bulunur. Formülü bir kez yazarsınız, sonuçlar altındaki ve yanındaki boş hücrelere taşar (spill); Ctrl+Shift+Enter ya da formülü aşağı kopyalama gerekmez.

Taşma (spill) mantığı​

Dinamik dizi formülü birden çok değer döndürdüğünde sonuçlar formülün yazıldığı hücreden başlayarak komşu boş hücrelere taşar. Taşan alana taşma aralığı (spill range) denir. Formül yalnızca ilk hücrededir; taşan hücreler soluk renkle gösterilir ve tek tek düzenlenemez. Taşma aralığına bir hücre seçtiğinizde etrafında mavi bir kenarlık görünür.

• Otomatik boyut: Kaynak veriye satır eklendiğinde ya da silindiğinde taşma aralığı kendiliğinden yeniden boyutlanır.
• Taşma aralığı işleci (#): Taşma aralığının tamamına başvurmak için sol üst hücrenin adresinin sonuna diyez eklenir, örneğin E2#. Aralık büyüse de küçülse de başvuru güncel kalır.
• Örtük kesişim işleci (@): Eski sürümlerde tek değere indirgenen başvurular yeni sürümde formülde @ işaretiyle gösterilir. Dinamik dizi davranışı istediğinizde bu işareti silebilirsiniz.
• Taşma engeli: Taşma aralığında dolu bir hücre varsa formül #YAYILMA! (#SPILL!) hatası verir. Engelleyen hücreyi temizlediğinizde formül çalışır.

Dinamik dizi işlevleri​

Dinamik Excel ile birlikte, dizi döndürmek üzere tasarlanmış işlevler de gelmiştir. Başlıcaları şunlardır:

• FİLTRE (FILTER): Ölçüte uyan satırları ayıklar.
• SIRALA (SORT) ve SORTBY: Bir listeyi sıralı olarak döndürür.
• BENZERSİZ (UNIQUE): Yinelenen değerleri ayıklayıp benzersiz listeyi verir.
• SEQUENCE: Belirtilen satır ve sütun sayısında ardışık sayı dizisi üretir.
• RANDARRAY: Rastgele sayılardan oluşan dizi üretir.
• ÇAPRAZARA (XLOOKUP): Tek hücre ya da birden çok sütun döndürebilen modern arama işlevidir.
• LET: Formül içinde ad tanımlayarak uzun formülleri sadeleştirir.

İşlevleri iç içe kullanabilirsiniz. Örneğin benzersiz değerleri sıralı almak için:

Eski formüllerden farkı ve sürüm uyumu​

Eski Excel sürümlerinde çok hücreli sonuç için formülü önce hücre aralığını seçip Ctrl+Shift+Enter ile girmeniz gerekirdi; formül kaşlı ayraç içinde görünürdü ve aralık sabit kalırdı. Dinamik Excel'de aralığı seçmenize gerek yoktur, Enter yeterlidir ve aralık veriye göre değişir.

Dinamik dizi içeren bir çalışma kitabını Excel 2019 ve öncesinde açtığınızda bu formüller eski tür dizi formülüne dönüşür ve taşma davranışı çalışmaz. Dosyayı paylaşacağınız kişilerin sürümünü önceden kontrol edin.

1. Benzersiz ve sıralı liste çıkarma​

Şehir, ürün ve satış tutarı içeren bir liste var. E2 hücresine benzersiz şehirleri sıralı döndüren, F2 hücresine de kaç farklı şehir olduğunu veren formüller yazılır. E3 ve altındaki hücreleri boş bırakın; sonuçlar oraya taşar.
E2 hücresindeki tek formül, A sütunundaki tekrarlı şehir adlarından sıralı bir liste üretir ve sonuç E2 hücresinden aşağı doğru taşar; E2 hücresinin ilk değeri Ankara olarak görünür. F2 hücresindeki formül benzersiz şehir sayısını verir: 4. Listeye yeni bir şehir eklendiğinde E sütunundaki liste kendiliğinden uzar.

2. FİLTRE ile ölçüte uyan kayıtları listeleme​

Personel, departman ve maaş bilgilerinden Satış departmanındaki çalışanları ayrı bir sütunda listelemek istiyorsunuz. E2 hücresine FILTER formülünü, F2 hücresine aynı ölçütle toplam maaşı hesaplayan formülü yazın. E3 ve altı boş kalmalıdır.
E2 hücresindeki FILTER formülü, departmanı Satış olan personelin adlarını E sütununa taşırır; ilk ad Ayşe Yılmaz olarak görünür. Ölçüte uyan kayıt bulunmazsa üçüncü bağımsız değişken olan "Kayıt yok" metni gösterilir. F2 hücresi aynı ölçütle süzülen maaşların toplamını verir: 127.000,00. Ölçütü bir hücreye bağlarsanız departman adını değiştirdiğinizde liste anında yenilenir.

3. SEQUENCE ile ardışık dizi ve çok hücreli hesap​

A2 hücresine SEQUENCE ile 1'den 6'ya kadar ay numaralarını üretin. B2 hücresindeki CHOOSE formülü bu numaralara karşılık gelen ay adlarını, C2 hücresindeki formül ise her ay için hedefi hesaplar. D2 hücresi hedeflerin toplamını verir. A3:C7 aralığı boş bırakılır.
A2 hücresindeki SEQUENCE(6) formülü altı satırlık ardışık numara dizisi üretir; ilk değer 1 olarak görünür. B2 ve C2 hücrelerindeki formüller bu diziyi bağımsız değişken olarak aldığı için kendileri de altı satıra taşar: ilk ay adı Ocak, ilk hedef 2.500. D2 hücresi tüm hedeflerin toplamını verir: 52.500. Satır sayısını değiştirmek için SEQUENCE içindeki 6 değerini ve aralık başvurularını güncellemeniz yeterlidir; Excel'de A2# gibi taşma aralığı başvurusu kullanırsanız aralık büyüdüğünde başvuruları elle düzeltmeniz gerekmez.

Kullanırken dikkat edin​

• #YAYILMA! (#SPILL!) hatası: Taşma aralığındaki hücrelerden biri doluysa, aralık birleştirilmiş hücre içeriyorsa ya da sonuç sayfa sınırını aşıyorsa formül #YAYILMA! hatası verir. Formülün bulunduğu hücreyi seçip hata simgesinden engelleyen hücreyi görebilir, ardından o hücreyi temizleyebilirsiniz.
• Excel tablosu içinde sınır: Dinamik dizi formülleri Excel tablolarının (Ctrl+T ile oluşturulan) içinde kullanılamaz; taşma tablo dışında gerçekleşmelidir. Sonuç alanını tablonun yanındaki ya da altındaki boş bir yere koyun, kaynak veri olarak tablo sütunlarına başvurabilirsiniz.
• Sürüm uyumu: Dinamik diziler Microsoft 365, Excel 2021 ve sonrasında vardır. Excel 2019 ve öncesinde FİLTRE, SIRALA, BENZERSİZ gibi işlevler bulunmaz; dosya o sürümlerde açılırsa bu formüller hata verir ya da dizi formülüne dönüşür.
• Taşma aralığına tek hücre olarak müdahale: Taşan hücrelerin içeriğini tek tek silemez ya da değiştiremezsiniz. Sonucu kalıcı değer olarak kullanmak isterseniz taşma aralığını kopyalayıp Özel Yapıştır > Değerler (Paste Special > Values) ile yapıştırın.
• Sonucu başka formülde kullanma: Taşan sonuca başka formülden başvururken taşma aralığı işlecini kullanmak (E2# gibi) en güvenli yoldur. Sabit aralık yazarsanız sonuç büyüdüğünde formül yeni satırları kapsamaz.
• İlgili konular: Dinamik Excel ile birlikte şu öğeleri de inceleyin: FİLTRE, SIRALA, BENZERSİZ, ÇAPRAZARA işlevleri, LET ve SEQUENCE işlevleri, dizi formülü (Ctrl+Shift+Enter), taşma aralığı işleci (#), örtük kesişim işleci (@), Excel tablosu ve #YAYILMA! hatası.

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