Dizi formülü hk.

[recepkull]

Arkadaşlar merhaba herkese hayırlı bayramlar diliyorum. Excede yapamadığım ve nasıl soracağımı da bilemediğim bir soruyla karşınızdayım yine. Aşağıdaki resimde ve örnek dosyada anlatmaya çalıştım yardımcı olursanız çok sevinirim.

 

[Korhan Ayhan]

B12 hücresine uygulayıp deneyiniz

DİZİ formüldür.

=EĞERHATA(--(İNDİS($B$6:$AF$6;KÜÇÜK(EĞER($B$7:$AF$7="x";SÜTUN($B$7:$AF$7)-1);SÜTUN(A$1)))&"."&$B$5&"."&YIL(BUGÜN()));"")

 

[recepkull]

Hocam teşekkür ederim tam istediğim gibi çalışıyor. Normalde bu formülü dosyama uygulayıp sorunumu çözerim ama ben öğrenme istiyorum. Formüldeki SÜTUN(A$1) neyi ifade ediyor? Tablonun A1 ile hiç bir bağlantısı yok ama bu formülde hangi mantıkla çalışıyor ? Bana formülü anlatabilir misiniz ? Yada bana kaynak gösterebilir misiniz ? Google da, youtube da bu tarz formülleri araştırmam için ne olarak arattırmalıyım ?

 

[Korhan Ayhan]

Formülün mantığı şu şekilde işliyor;

İNDİS formülü ile belirlenen aralıktaki kesişim yerine ulaşıyoruz.

=İNDİS(TABLO;SATIR;SÜTUN)

Bu tarz formüllerde başlangıç satırı ya da sütunu önemlidir.

Aradığımız değer B6:AF6 aralığında bulunuyor. Fonksiyondaki TABLO bölümüne bu aralığı yazıyoruz.

TABLO'muz tek satırlı bir aralık olduğu SÜTUN parametresini kullanmaya gerek yok. Bu sebeple SATIR parametresine odaklanıyoruz.

Gözle kontrol ettiğimizde ilk "x" içeren hücremiz C7 hücresidir. Bu hücre sütun olarak 3. sütundur. Ama biz TABLO aralığını B sütunundan başlattığımız için 2. sütun olmuş oluyor. (B=1 , C=2)

Buradaki durum neden böyle oldu. Çünkü TABLO'muzu eğer A sütunundan başlatsaydık C sütununu 3. sütun olurdu. TABLO'muz B sütunundan başladığı için sütun sayısı bir eksiliyor. Bu durumda C sütunu 2. sütun olmuş oluyor.

TABLO'muzdaki satır değerlerine küçükten büyüğe doğru ulaşmamız gerekiyor. Bu işlemi dinamik hale getirmek içinse KÜÇÜK fonksiyonundan faydalanıyoruz.

=KÜÇÜK(Alan;Kaçıncı_Küçük)

Bu fonksiyonda ALAN olarak tanmladığımız bölümde ise dolu yani "x" içeren hücrelerin sıra (sütun) numarasına ulaşmamız gerekiyor.

Burada bir EĞER sorgusu devreye giriyor.

Formüldeki EĞER Sorgusu şu şekilde çalışıyor;

EĞER($B$7:$AF$7="x";SÜTUN($B$7:$AF$7)-1)

EĞER alandaki hücrede "x" değeri varsa bana bunun sütun numarasını 1 eksilterek ver. Bu sorgu sonucunda aşağıdaki gibi bir dizi oluşacaktır. Neden 1 eksiltiyoruz. Çünkü bizim TABLO'muz B sütunundan başlıyor. Başlangıç sütunu olduğu için B=1 indexi oluşuyor.

{YANLIŞ;2;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;7;8;YANLIŞ;YANLIŞ;YANLIŞ;12;YANLIŞ...........}

Gördüğünüz gibi koşul sonucu boş olan hücreler için excel bize YANLIŞ sonucunu döndürdü. Sorgu sonucu dolu (x içeren) hücrelerin ise sütun numaralarını 1 eksilterek döndürdü.

Şimdi bu dizi ile KÜÇÜK fonksiyonunu birleştireceğiz.

KÜÇÜK fonksiyonundaki Alan bölümüne bu EĞER fonksiyonu ile ulaştığımız sorgu bölümünü yazacağız.

KÜÇÜK(EĞER($B$7:$AF$7="x";SÜTUN($B$7:$AF$7)-1);Kaçıncı_Küçük)

Bu aşamadan sonra EĞER fonksiyonu ile elde ettiğimiz dizide sırasıyla 1. küçük, 2. küçük, 3. küçük vs... diye giden bir mantık kurmamız gerekiyor. Burada Kaçıncı_Küçük parametresi devreye giriyor. Biz bu parametreyi de dinamik kullanmamız gerekiyor. Çünkü formül bize ilk uyguladığımız hücrede 1. küçük sütun numarasını verirken yana sürüklediğimizde 2. küçük sütun numarasını vermelidir. Bu şekilde formülü yana sürüklediğimizde artarak giden bir yapı olmalıdır. İşte bunun içinde SÜTUN fonksiyonu devreye giriyor.

SÜTUN fonksiyonunu boş bir hücreye =SÜTUN() şeklinde yazdığınızda size yazdığınız hücrenin sütun numarasını döndürecektir.

Yani biz =SÜTUN(A$1) dediğimizde bize 1 sonucunu verecektir. Çünkü A sütunu excelde ilk sütundur. Yani sayısal değeri 1'dir. Bize de fonksiyonu ilk uyguladığımız hücrede Kaçıncı_Küçük parametresi için 1 değeri gerektiğinden bu fonksiyonu kullandık. Bu sebeple formülde ilk hücre için aşağıdaki yapıyı kullandık.

KÜÇÜK(EĞER($B$7:$AF$7="x";SÜTUN($B$7:$AF$7)-1);SÜTUN(A$1))

Formülün bu bölümünü değerlendirdiğimizde aşağıdaki dizi oluşacaktır.

KÜÇÜK({YANLIŞ;2;YANLIŞ;YANLIŞ;YANLIŞ;YANLIŞ;7;8;YANLIŞ;YANLIŞ;YANLIŞ;12;YANLIŞ...........};1)

Bu dizinin sonucu olarak KÜÇÜK formülü bize 2 değerini verecektir. Çünkü dizideki ilk küçük değer 2'dir.

Bu değeri İNDİS fonksiyonu ile birleştirdiğimizde aşağıdaki gibi bir sonuç çıkacaktır.

İNDİS($B$6:$AF$6;2)

Excel bu aşamada şunu yapıyor. $B$6:$AF$6 aralığındaki 2. hücredeki değeri bize veriyor. Yani kesişim değerini bize veriyor.

Bu formülü bir bütün olarak yana sürüklediğinizde formülün içindeki 2 değeri bir sonraki hücrede 7 oluyor. Çünkü KÜÇÜK fonksiyonu içinde kullandığımız SÜTUN(A$1) fonksiyonu bu sefer yana sürüklediğimiz için SÜTUN(B$1) olarak değişecek ve bize 2 değeri döndürecektir. Yani dizi içindeki 2. küçük değer olan 7 değerini bize sonuç olarak verecektir.

Sizin eklediğiniz dosyada "x" içeren hücre sayısı 4'tür. Bu sebeple KÜÇÜK fonksiyonu içinde 4 adet küçük sütun değeri bulabiliriz. 5'inci sütun değerini almak istersek KÜÇÜK fonksiyonu hata ile sonuçlanacaktır.

Bu hata olayını engellemek içinde formüle EĞERHATA fonksiyonu eklenmiştir. Formül hata verirse boş bırak mantığı kurulmuştur.

Önemli Bilgi ; Eğer siz 6. satırda 1-2-3-4 şeklinde sayılar yerine MART ayının günlerini tarih olarak yazsaydınız. Benim önerdiğim çözümdeki aşağıdaki kırmızı renkli bölümlere ihtiyaç duyulmayacaktı. Formül daha sade yazılabilecekti.

=EĞERHATA(--(İNDİS($B$6:$AF$6;KÜÇÜK(EĞER($B$7:$AF$7="x";SÜTUN($B$7:$AF$7)-1);SÜTUN(A$1)))&"."&$B$5&"."&YIL(BUGÜN()));"")

 

[Murat OSMA]

Korhan Hocam, alternatif olması ve yeni dinamik dizi fonksiyonlarının kolaylığını göstermek adına FİLTRE formülü ile bir çözüm paylaşmak istedim.

=--(FİLTRE(B6:AF6;B7:AF7="x")&B5&YIL(BUGÜN()))

Saygılar

 

[Korhan Ayhan]

Yeni laptop alırsam kesin Microsoft 365 alacağım. Yeni fonksiyonlar çok cezbedici ve pratik gerçekten.

 

[Haluk]

Tek sıkıntısı; herkes kullanmadığı için, yine eski teknikleri kullanmak zorunda kalınması ...

.

 

[Murat OSMA]

Yeni laptop alırsam kesin Microsoft 365 alacağım. Yeni fonksiyonlar çok cezbedici ve pratik gerçekten.

Çok iyi olur bence, kesinlikle kullanmalısınız. ????????
Birçok yönden (pratik, hızlı, esnek, yalın gibi.. ) artı katacağını görüyorsunuz.

 

[Murat OSMA]

Tek sıkıntısı; herkes kullanmadığı için, yine eski teknikleri kullanmak zorunda kalınması ...

Artılarının yanında onu hiç düşünmezsiniz bile, aksine farklı bakabilmenizi sağlar. Formüllerde bilgi gelişimini çift yönlü çalıştırır, çeşitlilik sayesinde yapılabilenleri gördükçe insan daha bi' heveslenir diye düşünüyorum.

Bir örnek vereyim; A ile G arasında aşağıdaki gibi bir tablomuz var.
İstediğimiz de şu olsun; Satış Bölgesindeki benzersiz verilere göre, hangi benzersiz ürünlerin satıldığını bulup, alfabetik olarak sıralamak ve her bir ürün adı arasına da virgül koymak istediğinizde aşağıdaki formülü yazmak makro yazmaktan çok çok daha kolay.



Formülü buraya da bırakıyorum.. =METİNBİRLEŞTİR(", ";1;SIRALA(BENZERSİZ(FİLTRE(E:E;D:D=I2));1;1))

 

[okan32]

Ben OFFİCE 365 PRO PLUS DİJİTAL LİSANS HESABI aldım bu hesaptan ofis 365 indirip kurduğum zaman full olarak kullanabilir miyim Murat OSMA bey

 

[YUSUF44]

Herkese hayırlı bayramlar.

Sayın @Murat OSMA , Ofis 365 yüklediğimizde eski versiyondaki son kullanılanlar vs diğer kişisel ayarlamaları muhafaza edebilir miyiz? Yoksa her şey sıfırdan mı olacak?

 

[Murat OSMA]

Ben OFFİCE 365 PRO PLUS DİJİTAL LİSANS HESABI aldım bu hesaptan ofis 365 indirip kurduğum zaman full olarak kullanabilir miyim Murat bey

Evet Okan Bey, bir de Insider topluluğuna katılın, haftada bir iki kez güncelleme/derleme de alırsınız.

Herkese hayırlı bayramlar.

Sayın @Murat OSMA , Ofis 365 yüklediğimizde eski versiyondaki son kullanılanlar vs diğer kişisel ayarlamaları muhafaza edebilir miyiz? Yoksa her şey sıfırdan mı olacak?

Denemedim Yusuf Bey, sıfırdan kurulum yaptığım için tecrübe etmedim.

 

[recepkull]

@Korhan Ayhan hocam çok teşekkür ederim hiç usanmadan en ince ayrıntısına kadar anlatmışsınız elinize emeğine sağlık. Konuya ilgi gösteren yardımcı olan diğer arkadaşlara da teşekkür ediyorum.

 

[okan32]

Tamamdır Murat bey teşekkürler

 

[Erdem Akdemir]

Merhaba,

Dizi formülü olmayan çözüm.

=EĞERHATA(--(İNDİS(BÜYÜK(($B$7:$AF$7="x")*($B$6:$AF$6);SATIR(DOLAYLI("1:"&EĞERSAY($B$7:$AF$7;"x"))));1/(EĞERSAY($B$7:$AF$7;"x")+1-SÜTUN(A1))^-1)&"."&$B$5&"."&YIL(BUGÜN()));"")

 

[YUSUF44]

Denemedim Yusuf Bey, sıfırdan kurulum yaptığım için tecrübe etmedim.

Kurulum yaptım. Hem son kullandığım dosyalar, hem sanitlediğim dosyalar hem de hızlı erişim çubuğu eskisi gibi geldi, bir kayıp yaşamadım. Bunun sebebi oturum açmış olmam olabilir.