Excel Dinamik Dizi Formülleri


İnceleme Beğeni Favori Değerlendirme Ortalama Puan Yıldız Sayısı
397 2 2 1 5
Reklam Alanı
Türkiye'nin en popüler Excel Portalındaki içeriklerin en başında reklamınızın yayınlanmasını ister misiniz?
Reklam vermek istiyorum
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:

FORMÜL 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
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)

BENZERSİZ Formülü

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:

Otomatik güncellenen BENZERSİZ formülü

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 formülünden 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ığını Sayma

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")

Tek Bir Formül

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:

image-20200421011443-1.png

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:

image-20200421013604-2.png

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:

Kolay Arama

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:

=B3:B12*C2:L2

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

Çarpım Tablosu

Not: Geleneksel Excel'de, formülü incelemek için F9 kullanırsanız dizi formülü tarafından döndürülen birden çok sonuç görebilirsiniz. Ancak, formülü çok hücreli dizi formülü olarak girmediğiniz sürece, çalışma sayfasında yalnızca bir değer görüntülenir.

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
{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="Kapalı" // Durum = "Kapalı"

Dizi Kontrol

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

image-20200427144447-3.png

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

image-20200427144456-4.png

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:

image-20200427144505-5.png

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:

image-20200427144516-6.png

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

image-20200427144524-7.png

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:

image-20200427144540-8.png

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:

image-20200427144551-9.png

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

@ 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.
BU MAKALEDE KULLANILAN EXCEL ÖZELLİKLERİ:
FİLTRE - ÇAPRAZARA - SIRALA - RASGDİZİ - SIRALI - SIRALAÖLÇÜT - BENZERSİZ - ÇAPRAZEŞLEŞTİR

Daha önce yorum yapılmamış!

Excel dosyalarınızı kapatırken otomatik olarak kaydedilmesini isterseniz, aşağıdaki kodu BuçalışmaKitabına eklemeniz yeterli olacaktır:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
 ThisWorkbook.Close True
End Sub
Yükleniyor...