Dinamik Dizi Formülleri

Dinamik Dizi Formülleri makalesinde, Excel 365 ile hayatımıza giren Dinamik Diziler ile oluşturulan formüller hakkında bilgiler yer almaktadır

Yeni Nesil Excel Formülleri​

Dinamik Diziler, Excel formüllerinde yıllar içinde meydana gelen belki de en büyük değişikliktir. Bunun söylemin nedeni, Dinamik Diziler’in bir formülde aynı anda birden çok değerle kolayca çalışmanıza izin vermesidir. Birçok kullanıcı, dizi formüllerini belkide ilk kez anlayacak ve kullanacaklardır. Çünkü, bu büyük bir yükseltme ve yararlı bir değişikliktir. Dinamik Diziler, Excel’deki gerçekten bazı zor sorunları çözeceklerdir. Ayrıca, çalışma sayfalarının tasarlanma ve inşa edilme şeklini temelden değiştireceklerdir.

Kullanılabilirlik​

Dinamik diziler yalnızca Ofis 365’in en son sürümünde kullanılabilen yeni bir özelliktir. Excel 2016 ve Excel 2019 dinamik dizi formülü desteği sunmamaktadır. Makalemizde kolaylık sağlamak ve aşağıdaki sürümleri farklılaştırmak için iki kısaltma kullanılacaktır. Bu kısaltmalar, Excel 365 sürümü için “Dinamik Excel” 2019 ve öncesi sürümler için ise “Geleneksel Excel” şeklinde olacaktır.

Yeni Formüller​

Dinamik dizi güncellemesinin bir parçası olarak, Excel artık geleneksel formüllerle, geleneksel olarak çözülmesi zor olan sorunları çözmek için dinamik dizilerden doğrudan yararlanan 8 yeni formülü içermektedir.

Her formülün ayrıntıları ve örnekleri için aşağıdaki bağlantıları tıklayın:

Not: ÇAPRAZARA ve ÇAPRAZEŞLEŞTİR, yeni dinamik dizi fonksiyonları orijinal grubunda değildir ama yeni dinamik dizi yapısında harika ve sorunsuz olarak çalışmaktadırlar. ÇAPRAZARA formülü, DÜŞEYARA formülünün yerini alır ve dizilerden yararlanan modern, esnek bir yaklaşım sunar. ÇAPRAZEŞLEŞTİR, İNDİS ve KAÇINCI formüllerine yeni özellikler sağlayan KAÇINCI formülüne yapılan bir yükseltmedir.

Örnek​

Ayrıntılara girmeden önce basit bir örneğe bakalım. Aşağıda, E5 hücresine, B5:B15 aralığından benzersiz değerleri ayıklamak için BENZERSİZ formülünü kullanıyoruz:

=BENZERSİZ(B5:B16)

Sonuç olarak, E5:E12 aralığında sekiz benzersiz şehir listelendi.

Tüm formüller gibi BENZERSİZ formülüde veri değiştiğinde otomatik olarak güncellenir. Aşağıda, tablomuzun 8. sırasında yer alan İstanbul şehrinin yerine Kırkareli şehrini yazdığımızda, BENZERSİZ formülünün yer aldığı listeye otomatik olarak Kırklareli şehrinin geldiği görünmektedir:

Taşma – Bir formül, Birçok değer​

Dinamik Excel’de, birden çok değer döndüren formüllerde bu değerler doğrudan çalışma sayfasına “taşma” yapar. Bu, formül kullanıcıları için daha kullanışlı ve tamamen dinamik bir davranıştır – kaynak veriler değiştiğinde, listelenen sonuçlar hemen güncellenir.

Değerleri içine alan dikdörtgene “taşma aralığı” denir. Taşma aralığının özel olarak vurgulandığını göreceksiniz. Yukarıdaki BENZERSİZ örneğinde, taşma aralığı E5:E13’tür.

Veri değiştiğinde, taşma aralığı gerektiği gibi genişler veya daralır. Yeni değerler eklendiğini veya mevcut değerlerin azaldığını görebilirsiniz. Bu şekilde, bir taşma aralığı yeni bir tür dinamik aralıktır.

Not: Taşma diğer veriler tarafından engellendiğinde #TAŞMA! hatası görürsünüz. Taşma aralığı için yer açtığınızda, formül otomatik olarak listelenecektir.

Taşma Aralığı Referansları​

Bir taşma aralığına başvurmak için, aralıktaki ilk hücreden sonra bir karma sembolü (#) kullanmanız yeterli olacaktır. Örneğin, yukarıdaki BENZERSİZ fonksiyonundan elde edilen sonuçlara başvurmak için aşağıdaki gibi bir formül girmeniz yeterli olacaktır:

=E5#

Bu, tüm taşma aralığına başvurmakla aynıdır ve tüm taşma aralığına karşılık gelen bir formül yazdığınızda bu sözdizimini görürsünüz.

Bir taşma aralığı referansını doğrudan diğer formüllere besleyebilirsiniz. Örneğin, BENZERSİZ formülü tarafından döndürülen şehir sayısını saymak için aşağıdaki formülü kullanabilirsiniz:

=BAĞ_DEĞ_DOLU_SAY(E5#)

Taşma aralığı değiştiğinde, formülde otomatik olarak en son verileri yansıtır.

Büyük sadeleştirme​

Yeni dinamik dizi formüllerinin eklenmesi, bazı formüllerin büyük ölçüde basitleştirilebileceği anlamına gelir. İşte birkaç örnek:

• Benzersiz değerleri kolayca ayıklar ve listeler (önce | sonra)
• Benzersiz değerleri sayar (önce | sonra)
• Kayıtları filtreler ve çıkarır (önce | sonra)
• Kısmi eşleşmeleri çıkarır (önce | sonra)

Sadece Bir İşlem​

“Bir formül, birçok değer” yaklaşımının en güçlü faydalarından biri, mutlak veya karma başvurulara daha az güvenilmesidir. Dinamik dizi formülü sonuçları çalışma sayfasına listelendiği için başvurular değişmeden kalır, ancak formül doğru sonuçlar üretir.

Örneğin, aşağıda “A” grubundaki kayıtları listelemek için FİLTRE formülünü kullanıyoruz. F5 hücresine aşağıdaki gibi tek bir formül girilir:

=FİLTRE(B5:D11;B5:B11="A")

Her iki aralığın da göreceli başvuruların sabitlenmediğine dikkat edin, ancak formül mükemmel çalışıyor.

Bu, birçok kullanıcı için büyük bir avantajdır, çünkü formül yazma işlemini çok daha basit hale getirir.

Zincirleme fonksiyonlar​

Birden fazla dinamik dizi işlevini bir araya getirdiğinizde işler gerçekten ilginçleşir.

Örneğin BENZERSİZ formülü tarafından döndürülen sonuçları sıralamak isteyebilirsiniz.

Bu çok kolay… SIRALA formülünü aşağıdaki gibi BENZERSİZ formülünün dışına eklemeniz yeterli olacaktır:

Daha önce olduğu gibi, kaynak veriler değiştiğinde, yeni benzersiz sonuçlar otomatik olarak güzel bir şekilde sıralanır.

Dinamik Dizi Formülleri Duruma Göre Davranış​ Yapar

Dinamik dizi davranışının duruma göre olduğunu anlamak önemlidir. Herhangi bir formül birden çok sonuç döndürdüğünde, bu sonuçlar çalışma sayfasındaki birden çok hücreye dökülür. Bu, orijinal olarak dinamik dizilerle çalışmak üzere tasarlanmamış daha eski formüller için bile geçerlidir.

Örneğin, Geleneksel Excel’de, UZUNLUK formülüne bir dizi metin değeri verirsek, tek bir sonuç görürüz. Diğer hücrelere aynı formülü tek tek kopyalamamız gerekir. Ama Dinamik Excel’de, UZUNLUK formülünü bir değer aralığına uygularsak, birden çok sonucu tek bir seferde görürüz.

Aşağıdaki resmin solundaki eski davranışı ve sağındaki yeni davranışı gösterir:

Bu, her türlü formülü etkileyebilecek büyük bir değişikliktir.

Örneğin, DÜŞEYARA formülü, bir sütun dizini kullanarak bir tablodan tek bir değer almak üzere tasarlanmıştır.

Ancak, Dinamik Excel’de, DÜŞEYARA formülüne aşağıdaki gibi bir dizi sabiti kullanarak birden fazla sütun dizini verirsek:

=DÜŞEYARA("Aykan";$F$7:$H$10;{1;2;3};0)

DÜŞEYARA birden fazla sütunu, tek ve kolay bir şekilde döndürür:

Başka bir deyişle, DÜŞEYARA asla birden fazla değer döndürmek için tasarlanmamış olsa da, Dinamik Excel’deki yeni formül metodu sayesinde artık bunu yapabilir.

Dinamik Dizi Formülleri Tüm Formülleri Kapsar​

Son olarak, dinamik dizilerin yalnızca yeni nesil fomüllerle değil tüm formüllerle çalıştığını bilmemiz gerekir. Aşağıdaki örnekte C3 hücresi tek bir formül içerir:

=B3:B12*C2:L2

Sonuç 100 hücre içeren 10 x 10 aralığı listelenir.

Dinamik Dizi Formülleri – Yayılan Dizinler​

Dinamik dizilerin piyasaya sürülmesiyle “dizi” sözcüğü çok daha sık kullanılır hale gelmiştir. Aslında, “dizi” ve “aralık” ifadelerinin neredeyse birbirinin yerine kullanıldığını görebilirsiniz. Excel’de, aşağıdaki gibi süslü parantez içine alınmış dizileri göreceksiniz:

Son olarak, dinamik dizilerin yalnızca yeni nesil fomüllerle değil tüm formüllerle çalıştığını bilmemiz gerekir. Aşağıdaki örnekte C3 hücresi tek bir formül içerir:

{1;2;3} // yatay diziler
{123} // dikey diziler

Dizi, belirli bir sırada görünen öğelerin listesini ifade eden bir programlama terimidir. Excel formüllerinde dizilerin bu kadar sık ortaya çıkmasının nedeni, dizilerin bir hücre aralığındaki değerleri mükemmel bir şekilde ifade edebilmesidir.

Dizi İşlemlerinin Önem Kazanması​

Dinamik Excel formülleri birden çok değerle kolayca çalışabildiğinden, dizi işlemleri daha önemli hale gelecektir. “Dizi işlemi” terimi, bir dizi üzerinde mantıksal bir test veya matematik işlemi çalıştıran bir ifadeyi ifade eder. Örneğin, aşağıdaki ifade B5:B9 aralığındaki değerlerin “Kapalı” değerine eşit olup olmadığını kontrol eder.

=B5:B9="Kapalı" // Durum = "Kapalı"

B5:B9 aralığında 5 hücre bulunduğundan, sonuç bir dizide 5 DOĞRU/YANLIŞ değerdir:

{YANLIŞ;DOĞRU;YANLIŞ;DOĞRU;DOĞRU}

Aşağıdaki dizi işlemi 100’den büyük tutarları kontrol eder:

=C5:C9>100 // harcama > 100

Son dizi işlemi A testini ve B testini tek bir ifadede birleştirir:

=(B5:B9="Kapalı")*(C5:C9>100) // Durum = "Kapalı" and Harcama > 100

Not: Excel, matematik işlemi sırasında DOĞRU ve YANLIŞ değerlerini otomatik olarak 1 ve 0’a zorlar.

FİLTRE formülü ile iki işlemi için içine dahil etmek için aşağıdaki gibi bir formül yapmak yeterli olacaktır:

FİLTRE Formülü, Durum = “Kapalı” ve Harcama > 100 olan iki kaydı listeler.

Yeni ve eski dizi formülleri​

Dinamik Excel’de, Ctrl + Shift + Enter ile dizi formülleri girmeye gerek yoktur. Bir formül oluşturulduğunda, Excel formülün birden çok değer döndürüp döndüremeyeceğini denetler. Böylece, otomatik olarak dinamik bir dizi formülü olarak kaydedilmektedir. Bu formüllerde, süslü parantez görülmemektedir. Aşağıdaki örnek, Dinamik Excel’de girilen tipik bir dizi formülünü göstermektedir:

Aynı formül Geleneksel Excel’de açıldığında, süslü parantezler görülmektedir.

Geleneksel Excel’de dizi formülü açıldığında, formül çubuğunda süslü parantezler görülmektedir. Örneğin, aşağıdaki ekran Geleneksel Excel’de basit bir dizi formülünü göstermektedir:

Dinamik Excel’de formülü hiçbir değişiklik yapmadan yeniden girildiğinde, süslü ayraçlar kaldırılmaktadır. Buna rağmen, formül aynı sonucu döndürmektedir.

Dimanik Excel’de Geleneksel Excel formülleri ile çalışsanız bile, bir dizi formülü yazıyorsanız Ctrl + Shift + Enter girmeniz gerekmez.

Dinamik Dizi Formülleri – @ Karakteri​

Dinamik dizilerin kullanılmaya başlandığında, @ karakterinin formüllerde daha sık göründüğünü görülecektir. @ Karakteri “Gizli Kesişme” olarak bilinen bir davranışı mümkün kılmaktadır. Gizli Kesişme, birçok değerin bir değere düşürüldüğü mantıklı bir süreçtir.

Geleneksel Excel’de, gizli kesişme, bir hücrede birden çok değeri tek bir sonuca düşürmek için kullanılmaktadır. Dinamik Excel’de, çalışma sayfasına birden çok sonuç dökülebileceğinden, genellikle gerekli değildir. Ancak, gerektiğinde gizli kesişme @ karakteriyle manuel olarak çağrılmaktadır.

Excel’in daha eski bir sürümü ile dosya oluştururken, birçok karakteri döndürme potansiyeli olan mevcut formüllere @ karakterini otomatik olarak eklendiği görülmektedir. Geleneksel Excel’de, birden çok değer döndüren bir formül çalışma sayfasına dökülmemektedir. @ Karakteri Dinamik Excel’de aynı davranışı zorlamaktadır. Böylece formül aynı şekilde davranmaktadır. Ardından, orijinal Excel sürümündekiyle aynı sonucu döndürmektedir.

Başka bir deyişle, eski bir formülün çalışma sayfasına birden çok sonuç dökmesini önlemek için @ eklenmektedir. Formüle bağlı olarak, @ karakteri kaldırılmaktadır. Bu işlem, formülün davranışı değiştirmemektedir.

Dinamik Dizi Formülleri – Sonuç​

• Dinamik Diziler belirli formüllerin yazılmasını çok daha kolay hale getirmektedir.
• Artık, eşleşen verileri kolayca filtrelenmektedir. Bu formüllerle, benzersiz değerler kolayca sıralanmaktadır. Böylece ayıklama işlemini de kolayca gerçekleşmektedir.
• Dinamik Dizi formülleri, filtre ve sıralama gibi şeyler yapmak için iç içe kullanılabilmektedir.
• Birden fazla değer döndüren formüller otomatik olarak dökülmektedir.
• Dizi formülü girmek için Ctrl + Shift + Enter tuşlarına gerek olmamaktadır.
• Dinamik dizi formülleri yalnızca Excel 365 sürümlerinde kullanılmaktadır.