Ay: Eylül 2019

Öncelikle uzun bir makale olacak, ara ara detaylara gireceğim fakat olabildiğince sizi çok boğmadan, sıkmadan, bilgi dolu bir anlatımla Dizi Formülü konusunu birlikte irdeleyeceğiz.
Anlatımla beraber sizler de uygulamasını yapabilirseniz, olayı kavramanız ve kafanızda netleştirmeniz daha kolay olacaktır. Hatta makale sonunda formülü silip tekrar tekrar yapmaya çalışmanızı öneririm.

 

GENEL BİLGİLER

Eylül 2018’de Office Insider‘da test amaçlı birkaç yeni dinamik dizi formülleri duyuruldu ve kullanılmaya başlandı.
Gerekli testlerden sonra Office 365‘te herkesin kullanımına açık olacak.
Bu yeni dinamik dizi formülleri, eskiden Ctrl + Shift + Enter ile tamamladığımız ve dizi formülü olarak bilinen kullanım şeklinin gereksinimini ortadan kaldırdı, fakat geriye dönük uyumluluk açısında kullanımı devam edecek.

Yine Eylül 2018’de açıklanan Hızlı Arama (Speedy Lookup) ile bazı işlerin artık Excel‘de daha hızlı yapılabileceğini biliyoruz.
Düşeyara (VLookup), Yatayara (HLookup), Kaçıncı (Match) gibi formüllerde de Süper Şarj olarak nitelendirilen Hızlı Arama özelliği gelmiş bulunmakta.
Bu makaleden ve bu yeni dinamik dizi formüllerinde belirtilen açıklamalara bakacak olursak, bu yeni kullanım eski CSE (Ctrl+Shift+Enter) kullanımına göre performans bakımından daha hızlı sonuçlar vereceğini söyleyebiliriz.
Önce yeni dizi formülleri ile eskisi arasındaki farkı aşağıda göreceksiniz, sonrasında da CSE kullanımına göre dizi formüllerinin analitiğini sizlere anlatmaya çalışacağım.

 

AYRINTILAR

Excel‘de yazdığınız dinamik dizi formüllerinde, bir diziyi geri döndürmenin olası olup olmadığı belirlenir. Eğer dizi olarak döndürülebilir ise, formül dinamik bir dizi olarak kaydedilir.
Excel‘in eski bir sürümünü kullanıyorsanız, dinamik dizi formülleri içeren bir çalışma kitabını açarsanız, o formülleri eski CSE diziler olarak algılar.
Eski CSE dizi formülleri, dinamik dizi formülleri ile benzer biçimde davranırlar. Ana fark; yeni dinamik dizi formülünü girip Enter dedikten sonra formülün yeniden boyutlandırılması.
Eski CSE kullanımı, kompleks işlerde hesaplama anlamında hem birkaç fonksiyonun iç içe yazılmasından, hem de birden fazla kayıt hesaplanması ve döndürmesinden dolayı, hesaplama yapmayı da ağırlaştırıyordu.

Dinamik Dizi Formülleri ile eski CSE kullanımı karşılaştırmasını aşağıda görebilirsiniz.
Eski CSE kullanımında formülü yazıp, uygulamak istediğimiz alanı seçtikten sonra Ctrl + Shift + Enter demek gerekirken, dinamik dizi formüllerinde tek bir hücreye formülü yazıp Enter demek yeterli oluyor..

 

Şu an için bize sunulan Yeni Dinamik Dizi Formüllerini aşağıda bulabilirsiniz.

‣ FİLTRE işlevi
‣ RASTGDİZİ işlevi
‣ SIRALI işlevi
‣ TEKLİ işlevi
‣ SIRALA işlevi
‣ SIRALAÖLÇÜT işlevi
‣ BENZERSİZ işlevi

 

Şimdi gelelim Dizi Formüllerinin analitiğine..

Dizi deyince aklımıza, içinde her türlü veriyi (sayı, metin, tarih vs.) barındırabilen bir veri kümesi gelsin.
Meselâ; A1 ile A5 arasında bazı verilerimiz olsun. C1 hücresine de şu formülü yazalım; =A1:A5

formülü yazdıktan sonra F9 tuşuna basın. Seçmiş olduğumuz alandaki verilerin bu şekilde bir küme içerisinde yan yana yazıldığını göreceksiniz.
İşte bu bir dizidir. Yukarıda da belirttiğim gibi; içerisinde seçmiş olduğumuz alandaki verilerin olduğu bir küme olarak düşünün.

Eğer verileriniz farklı sütunlarda ise, o zaman da aşağıdaki gibi bir yapıda, aralarda noktalı virgül olacak şekilde tutulur. Hemen yukarıdaki resim ile karşılaştırıp farkı görebilirsiniz.

Metinsel veriler çift tırnak içerisinde belirtilirken, sayısal ifadelerde çift tırnak olmadan dizi içerisinde görülebilir.

 

KARŞILAŞTIRMA OPERATÖRLERİ İLE MANTIKSAL İFADE DÖNDÜRMEK

Tablonuzdaki bir alanda bulunan veriler ile bir hücredeki veriyi karşılaştırıp, hangi verilerin eşit olduğunu mantıksal ifade şeklinde DOĞRU veya YANLIŞ olarak görebiliyor ve ona göre aksiyon alabiliyoruz.
Hangi veriler kriterimize uyuyorsa DOĞRU, uymuyorsa YANLIŞ sonucunu döndürür.
Şimdi basitçe bir karşılaştırma yapalım; A2:A9 arasında bazı isimlerimiz olsun, C2 hücresinde de karşılaştırma yapacağımız isim olsun.
E1 hücresine aşağıdaki gibi =A2:A9=C2 formülü yazalım.

Hangi verilerin C2‘deki veriye eşit olduğunu, F9‘a basarak hesaplatıp dizi içerisinde DOĞRU ifadesiyle görebiliyoruz.

DOĞRU ifadesinin karşılığı 1‘dir, YANLIŞ‘ın 0‘dır. Bunu örneğin, Düşeyara fonksiyonunun son parametresi olan aralık_bak ‘ta YANLIŞ seçeneği yerine genellikle 0 yazıldığı durumlarda görebilirsiniz
Bazen fazla bir aralık seçilip karşılaştırma yapıldığında, uzun bir DOĞRU ve YANLIŞ metni karşımıza çıkar, bu DOĞRU ve YANLIŞ ifadelerini de 1 ve 0 olarak kısaltıp daha anlaşılır hâle getirebiliyoruz.
Bunun için; mevcut formülü parantez içine alıp başına — (iki tire) yazarak gösterebilirsiniz.
Sonuç olarak, uzun DOĞRU YANLIŞ metinleri yerine, kısaca bu şekilde 1 ve 0 ifadelerini F9‘a basarak görüntüleyebilirsiniz.
Buradaki 1 sayıları DOĞRU olanları ifade etmektedir ve genellikle bizim bütün işlerimiz bu 1 sayıları yani DOĞRU ifadeleri ile olur.

Şimdi de karşılaştırmamızı biraz geliştirip birden fazla alan için karşılaştırma yapıp sonucunu görelim.
A2:A9 arasındaki veriler eşitse E2 hücresindeki veriye ve B2:B9 arasındaki veriler de eşitse F2 hücresine diyelim.
Bu noktada formülümüz şöyle olacak;

Formülde her bir parantezi F9 ile hesaplattığımızda, iki şartımıza da uyan ve uymayan kayıtları DOĞRU ve YANLIŞ olarak görelim.

Her iki parantezi de ayrı ayrı seçip F9‘a basarsanız bu şekilde uzun bir dizi göreceksiniz.

Bu diziyi kısaltmak için, daha önce de belirttiğimiz gibi S formülü içerisine alırsak sonuçlar 1 ve 0 olarak dönecektir.
O zaman da formül bize şu değerleri döndürecektir. Bu bize; belirttiğimiz iki şartımıza da uyan 2 verinin olduğunu gösterir.

Son olarak da şunu yapalım; iki şartımıza da uyan verilerimiz varsa onların Puan alanındaki sayılarını toplayalım.
Burada bir toplama işlemi uygulayacağımız için TOPLA formülünü dahil edeceğiz.
TOPLA formülü; bir sütundaki veya bir veri aralığındaki sayıların toplamını almak için kullanılan bir fonksiyondur.
İki veya daha fazla kritere göre toplam alınacak ise, bu durumda TOPLA formülü diğer sütunları karşılaştırıp DOĞRU üreten değerleri kriter alarak bir sonuç üretmesi için Dizi formülüne çevrilmesi gereklidir.
Bu noktada formülümüz şu hâli alacaktır. 
Bu formül henüz dizi formülüne çevrilmemiş hâldedir ve sonuç olarak size istediğinizi vermeyebilir.
Sebebi ise şudur; Dizi formülüne dönüştürülmeyen diziler tek bir sonuç döndürür ve ilk verinin sonucunu verir, oysa dizi içerisinde birçok eleman bulunabilir ve bunların hesaplanması ve hücreye dökülmesi için dizi formüllerine dönüştürülmesi gerekir.

Bir formülü Dizi formülüne dönüştürmek için şu yönergeleri izleyebilirsiniz.
‣ Hücre içerisine girilir. (F2)
‣ CTRL + SHIFT + ENTER tuşlarına basılır.

Bu işlemlerden sonra sonuç olarak formülümüz şu hâli alır;
Gördüğünüz gibi, formülün başına ve sonuna küme parantezlerini otomatik olarak ekler, bu işlem o formülün bir dizi formülü olduğu anlamına gelir.
Dizi formülü olduğunda, her iki küme içindeki elemanları sırasıyla birbiriyle çarpar ve sonuç hesaplanır.

 

* İPUCU
TOPLA işlevinin kullanılıp verilerin toplamının alınmak istendiği durumlarda, aslında Dizi formülüne çevirmeye gerek olmadan,
daha kısa bir şekilde TOPLA.ÇARPIM fonksiyonu kullanılabilir. TOPLA.ÇARPIM fonksiyonu da aslında DOĞRU değerler için 1 değeri üretip her iki sütunda 1*1 değeri üretmiş bir sütunun başka sütundaki toplamı gösterir.
Yani; Dizi formülüne dönüştürmeden de TOPLA.ÇARPIM formülü ile kolayca toplatabiliriz, çünkü TOPLA.ÇARPIM fonksiyonu da dizi mantığında çalışır.

Her iki yöntemi de bu gif resimde görebilirsiniz.

Peki sütunda karşılaştırma yaparak birden fazla var olan verinin diğer sütunundaki verileri nasıl listeletebiliriz?
Örnek olarak; A sütununda Ahmet olanların Puanlarını F sütununda alt alta listelemek istediğimizi varsayalım.

Mantık olarak ilerleyişimiz şöyle olacak; Eğer A1:A9 arasında Ahmet yazan hücre varsa, ilk bulduğu Ahmet‘ten itibaren hepsini F2 hücresinden itibaren Puanlarını alt alta listele.
Bunun için, adım adım düşünerek gidelim; öncelikle Eğer formülü ile A1:A9 aralığındakilerden hangilerinin E2 hücresine eşit olup olmadıklarını bulalım..
İlk oluşturduğumuz formül bu olacak.
Formülü seçip F9‘a bastığınızda, eşit olanların Puanlarını, olmayanların da aşağıdaki resimde YANLIŞ olarak geri döndüğünü göreceksiniz.

Eğer bu şekilde dizi formülüne dönüştürürsek bize dizi içindeki elemanları sırasıyla alt alta dökecektir, fakat bizim YANLIŞ ifadeleriyle bir işimiz yok, sadece Puanlarını almamız gerekir.
O noktada da dizi içinde küçükten büyüğe bir sıralama yapmamız gerekecek ki; dizi içerisindeki sayılar (10;18;24) en başa gelsin.
Bildiğiniz üzere, Excel‘de sıralamada öncelik sayıların, sonra metinlerindir. O sebeple küçükten büyüğe bir sıralama yaptığımızda sayılar başta listelenmektedir.

Şimdi de Eğer fonksiyonu ile elde ettiğimiz dizi içindeki elemanları küçükten büyüğe sıralamak için KÜÇÜK fonksiyonu içerisine alalım.
Bu fonksiyon bizden bir de  parametresini ister. Yani; kaçıncı en küçük sayıyı almak istediğimizi belirtmemiz gerekir. Örneğin; 1. en küçük, 2. en küçük gibi, 1-2-3 gibi sayılar yapabildiğimiz bir parametredir.
Bize birden fazla veri döneceğini bildiğimiz için bu verileri sırasıyla 1. en küçük, 2. en küçük, 3. en küçük şeklinde belirtmemiz gerekecek.

Bu noktada, bu sıra numarası işlemini manuel olarak her bir satır için elle yazmak yerine, k parametresi için, formülü aşağıya doğru çektiğimizde, kendiliğinden 1,2,3 şeklinde ilerlemesini sağlayacak başka bir uçucu fonksiyon kullanmamız iyi olacaktır.
Bu fonksiyonumuz da SATIR fonksiyonudur. İki şekilde çalışır.
Birincisi; =SATIR() şeklindedir, formülü hangi satıra yazarsanız o satırın numarasını size verir.
İkincisi; =SATIR(A1) şeklindedir, formülü hangi hücreye yazarsanız yazın, başvuru adresi olarak A1 hücresi belirtildiği için o adresin satır numarasını yani 1 sayısını verir.

Şimdi de tüm bu bilgiler ışığında formülümüzü son bir nokta hariç resimdeki gibi tamamlayalım.

Artık formülümüz dizi formülüne dönüştürülmeye hazır, sadece dizi içerisindeki elemanları küçükten büyüğe sıralarken sayıları başa aldık ve YANLIŞ ifadelerini de sayıların arkasına attık ya, dizi formülüne dönüştürdüğümüzde hatalar dahil tüm elemanlar listelenecektir.
Çoğu zaman insan olarak da hatalarımızı görmek istemeyiz, görmezden geliriz veya bizim için o an bir önemi olmayabilir, o yüzden oluşabilecek hataları da listelemek yerine, EĞERHATA fonksiyonu ile; bir hata dönerse “boş bırak” diyebiliyoruz.
Dolayısıyla dizi formülüne dönüştürüldüğünde, sadece dizi içerisinde var olan puanlar listelenecektir.
Oluşan formülümüzün başına EĞERHATA yazıp parantez açtıktan sonra formülün en sonuna gidip noktalı virgül ve “” koyarak parantezimizi kapatıyoruz.

Formülümüz hazır. Şimdi son noktayı koymaya geldik.
Artık mevcut formülümüzü dizi formülü hâline dönüştürebiliriz.
Bunu da nasıl yapıyorduk, tekrar hatırlayalım:
‣ Hücre içerisine girilir. (F2)
‣ CTRL + SHIFT + ENTER tuşlarına basılır.
‣ Formül aşağıya doğru çekilir.

Ve mutlu son!
Gördüğünüz gibi; A1:A9 arasındaki veriler eşitse E2 hücresindeki veriye, kaç tane veri varsa onların puanlarını küçükten büyüğe listelemiş olduk.

Makalenin başlarında da bahsettiğimiz ve şu an için sadece Office Insider‘da kullanılabilen FİLTRE işlevi genele açıldığında artık çok daha kolay bir yazım ile bu işlemi gerçekleştirebileceğiz.
FİLTRE işlevinin kullanımını görmeniz açışından örnek bir görseli de ekliyorum.

Bu makaleyi beğendiyseniz çevrenizle de paylaşmayı ihmâl etmeyiniz..