Dinamik Dizi Formülleri, Excel formüllerinde yıllar içinde meydana gelen belki de en büyük değişikliği anlatacağımız makalenin adıdır.
Dinamik Diziler‘in bir formülde aynı anda birden çok değerle kolayca çalışmanıza izin vermektedir. 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ı çözmektediler. Böylece, çalışma sayfalarının tasarlanma ve inşa edilme şeklini temelden değiştirmektedirler.
Dinamik Dizi Formülleri – Kullanılabilirlik
Öncelikle, dinamik diziler yalnızca Excel 365‘in en son sürümünde kullanılan yeni bir özelliktir. Yeni nesil Excel’in yayınlandığı tarihten sonra çıkan Excel 2021 versiyonunda bazıları çalışmaktadırlar. Ancak, Excel 2019 ve daha alt versiyonlarda dinamik dizi formülleri bulunmamaktadırlar. Makalemizde kolaylık sağlamak ve aşağıdaki sürümleri farklılaştırmak için iki kısaltma kullanılmaktadır. Bu kısaltmalar:
- Excel 365 sürümü için “Dinamik Excel“
- Excel 2019 ve öncesi sürümler için ise “Geleneksel Excel” şeklinde olmaktadır.
Yeni Fonksiyonlar
İlk olarak, Microsoft’un Dinamik dizi güncellemesinin ilk yayını olan ve Geleneksel Excel ile çözülmesi zor olan sorunların üstesinden gelen 8 yeni fonksiyon bulunmaktadır. Her fonksiyonun ayrıntıları ve örnekleri için aşağıdaki bağlantıları tıklayabilirsiniz:
| FONKSİYON | AMAÇ |
| FİLTRE | Verilere filtre uygular ve eşleşen kayıtları listeler |
| RASGDİZİ | Rastgele sayılar dizisi oluşturur |
| SIRALI | Sıralı sayılar dizisi oluşturur |
| SIRALA | Aralığı sütuna göre sıralar |
| SIRALAÖLÇÜT | Aralığı başka bir aralığa veya diziye göre sıralar |
| BENZERSİZ | Bir listeden veya aralıktan benzersiz değerleri ayıklar |
| ÇAPRAZARA | DÜŞEYARA formülü için modern değişimler sağlar |
| ÇAPRAZEŞLEŞTİR | KAÇINCI formülü için modern değişimler sağlar |
Dinamik Dizi Formülleri – Örnek
Öncelikle, ayrıntılara girmeden basit bir örneğ hazırlnamıştır. Aşağıda, B5:B15 aralığından benzersiz değerleri listeleme için, E5 hücresine BENZERSİZ fonksiyonunu kullandık.
Sonuç olarak, E5:E12 aralığında sekiz benzersiz şehir listelenmektedir.
Tüm fonksiyonlar gibi BENZERSİZ fonksiyonuda veri değiştiğinde otomatik olarak güncellenmektedir. Aşağıda, tablomuzun 8. sırasında yer alan İstanbul şehrinin yerine Kırkareli şehrini yazdığımızda, BENZERSİZ fonkisoyunun 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üncellenmektedir.
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 formülünden 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:
{1\2\3} // 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 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:
Dinamiz 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 gerekmekz.
Dinamik Dizi Formülleri @ 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ülmemektedir. @ Karakteri Dinamik Excel’de aynı davranışı zorlamaktadır. Böylece, formül aynı şekilde davranır ve 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, @ karakterini kaldırabilirsiniz ve formülün davranışı değişmemektedir.
Dinamik Dizi Formülleri – Ö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.
Makalemizden faydalanmanız temennisiyle.