Excel Dinamik Dizi Formülleri

Excel Dinamik Dizi Formülleri​

Excel Dinamik Dizi Formülleri makalesinde, Excel 365 ile hayatımıza giren Dinamik Diziler ve bu diziler ile oluşturulan formüller hakkında bilgiler vardı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.

Bu büyük bir yükseltme ve yararkı bir değişikliktir.

Dinamik Diziler, Excel'deki gerçekten bazı zor sorunları çözecek ve çalışma sayfalarının tasarlanma ve inşa edilme şeklini temelden değiştirecektir.

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 sunmaz.

Makalemizde kolaylık sağlamak ve aşağıdaki sürümleri farklılaştırmak için iki kısaltma kullanacağız. Bu kısaltmalar:

Ofis 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:

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: 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:

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:

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.

Duruma Göre Davranış​

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 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.

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: Sonuç 100 hücre içeren 10 x 10 aralığı listelenir.

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: 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 aralığında 5 hücre bulunduğundan, sonuç bir dizide 5 DOĞRU/YANLIŞ değerdir: Aşağıdaki dizi işlemi 100'den büyük tutarları kontrol eder:

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

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 yapmanız 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 kaydedilir ve süslü parantez görmezsiniz. Aşağıdaki örnek, Dinamik Excel'de girilen tipik bir dizi formülünü gösterir:



Aynı formülü Geleneksel Excel'de açarsanız, süslü parantezler görürsünüz:



Geleneksel Excel'de dizi formülü açıldığında, formül çubuğunda süslü parantezleri görürsünüz. Örneğin, aşağıdaki ekran Geleneksel Excel'de basit bir dizi formülünü gösterir:



Dinamik Excel'de formülü hiçbir değişiklik yapmadan yeniden girerseniz, süslü ayraçlar kaldırılır ve formül aynı sonucu döndürür:



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

@ Karakteri​

Dinamik dizilerin kullanılmaya başlandığında, @ karakterinin formüllerde daha sık göründüğünü göreceksiniz. @ Karakteri "Gizli Kesişme" olarak bilinen bir davranışı mümkün kılar. 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ılan (gerektiğinde) sessiz bir davranıştır. Dinamik Excel'de, çalışma sayfasına birden çok sonuç dökülebileceğinden, genellikle gerekli değildir. Gerektiğinde, gizli kesişme @ karakteriyle manuel olarak çağrılı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ğini görebilirsiniz. Geleneksel Excel'de, birden çok değer döndüren bir formül çalışma sayfasına dökülmez. @ Karakteri Dinamik Excel'de aynı davranışı zorlar, böylece formül aynı şekilde davranır ve orijinal Excel sürümündekiyle aynı sonucu döndürür.

Başka bir deyişle, eski bir formülün çalışma sayfasına birden çok sonuç dökmesini önlemek için @ eklenir. Formüle bağlı olarak, @ karakterini kaldırabilirsiniz ve formülün davranışı değişmez.

Özet​

• Dinamik Diziler belirli formüllerin yazılmasını çok daha kolay hale getirir.
• Artık eşleşen verileri filtreleyebilir, formüllerle benzersiz değerleri kolayca sıralayabilir ve ayıklayabilirsiniz.
• Dinamik Dizi formülleri, filtre ve sıralama gibi şeyler yapmak için zincirlenebilir (iç içe yerleştirilebilir).
• Birden fazla değer döndüren formüller otomatik olarak dökülecektir.
• Dizi formülü girmek için Ctrl + Shift + Enter tuşlarına gerek yoktur.
• Dinamik dizi formülleri yalnızca Excel 365'te kullanılabilir.