Tarihe göre veri bulma

Katılım
14 Kasım 2004
Mesajlar
297
Excel Vers. ve Dili
microsoft office professional plus 2016
Merhaba arkadaşlar ekli dosyada bir tablo var ve tablodan h36 hücresine tarih yazdığımda h37 den w37 ye kadar veri getirmesi gerekiyor. tablo ekleyerek anlatmamın daha kolay olacağını düşündüm şimdiden herkese teşekkür ederim.


dosyanın indireleceği link : https://dosya.co/diogf4jqslog/Kitap1.xlsx.html
şifresi : mrt950
 
Katılım
14 Kasım 2004
Mesajlar
297
Excel Vers. ve Dili
microsoft office professional plus 2016
Merhaba arkadaşlar ekli dosyada bir tablo var ve tablodan h36 hücresine tarih yazdığımda h37 den w37 ye kadar veri getirmesi gerekiyor. tablo ekleyerek anlatmamın daha kolay olacağını düşündüm şimdiden herkese teşekkür ederim.


dosyanın indireleceği link : https://dosya.co/diogf4jqslog/Kitap1.xlsx.html
şifresi : mrt950
Merhaba arkadaşlar ekli dosyada bir tablo var ve tablodan h36 hücresine tarih yazdığımda h37 den w37 ye kadar veri getirmesi gerekiyor. tablo ekleyerek anlatmamın daha kolay olacağını düşündüm şimdiden herkese teşekkür ederim.


dosyanın indireleceği link : https://dosya.co/diogf4jqslog/Kitap1.xlsx.html
şifresi : mrt950
yardım
 
Katılım
21 Aralık 2016
Mesajlar
674
Excel Vers. ve Dili
Office 365 TR

Bir önceki mesajdaki ekte verilen formül, 2010 versiyon ve üzeri için geçerlidir.
Sizin versiyonu sonradan gördüğüm için dosyayı hem 2010 ve üzeri ve hem de 2003 ve 2007 versiyon için geçerli olacak formül olmak üzere iki satır formüle edildi...2003 versiyon için olan formül DİZİ formülüdür... CTRL+SHIFT+ENTER ile girilmelidir)
Ayrıca ben ilk mesajdaki ekte tek satır düşünerek formüle etmiştim. Yani$F$37 şeklinde kullandım. Alt satırlara da kopya edilebilmesi için $F37 şeklinde düzeltildi....
 
Katılım
14 Kasım 2004
Mesajlar
297
Excel Vers. ve Dili
microsoft office professional plus 2016

Bir önceki mesajdaki ekte verilen formül, 2010 versiyon ve üzeri için geçerlidir.
Sizin versiyonu sonradan gördüğüm için dosyayı hem 2010 ve üzeri ve hem de 2003 ve 2007 versiyon için geçerli olacak formül olmak üzere iki satır formüle edildi...2003 versiyon için olan formül DİZİ formülüdür... CTRL+SHIFT+ENTER ile girilmelidir)
Ayrıca ben ilk mesajdaki ekte tek satır düşünerek formüle etmiştim. Yani$F$37 şeklinde kullandım. Alt satırlara da kopya edilebilmesi için $F37 şeklinde düzeltildi....
Elinize emeğinize sağlık hocam teşekkür ederim yanlız sizden bir ricam formülü anlamaya çalışıyorum da affınıza sığınarak formul de bir iki yeri sormak istiyorum..

EĞERHATA(EĞER(H$36="İSİM";İNDİS(KAYDIR($B$3:$V$3;-1;;1;);TOPLAMA(15;6;(SÜTUN($B$3:$V$3)-SÜTUN($B$3)+1)/(KAYDIR($B$3:$V$3;KAÇINCI($F37;$A$4:$A$34;);;1;)<>"");(EĞERSAY($H$36:H$36;"=İSİM")-1)*3+1));İNDİS(KAYDIR($B$3:$V$3;KAÇINCI($F37;$A$4:$A$34;);;1;);TOPLAMA(15;6;(SÜTUN($B$3:$V$3)-SÜTUN($B$3)+1)/(KAYDIR($B$3:$V$3;KAÇINCI($F37;$A$4:$A$34;);;1;)<>"");EĞERSAY($H$36:H$36;"<>İSİM"))));"")

hocam kırmızı renkle yazılı olanları anlayamadım özellikle 15;6; satır ve sutunu ifade ediyor sanırım ancak satırları sutunları üstten alttan saydım tutturamadım.
 
Katılım
21 Aralık 2016
Mesajlar
674
Excel Vers. ve Dili
Office 365 TR
Selamlar,
TOPLAMA (AGGREGATE) işlevinin açıklanmasını yaparsak, kırmızı ile belirtilen değerler anlaşılmış olacaktır....
Açıklamaya çalışırsam,

TOPLAMA(15,6 :
Burada 15 parametresi : İşlemin KÜÇÜK işlevi gibi çalışması içindir.
6 parametresi ise : Hataların YOK sayılması içindir.
TOPLAMA (AGGREGATE) işlevi, formülün Dizi Formül gibi CTRL+SHIFT+ENTER ile değil de normal formül gibi ENTER ile girilerek çalışması içindir. Dizi formül mantığında çalışır.

KÜÇÜK işlevi ile, TOPLAMA(15,6,dizi, k) genel biçiminde yazılır.

dizi : B3 : V3 arasında veriler var. Yani 21 adet sütun.

Öncelikle, Diziyi elde etmek için SÜTUN($A$1:$U$1) ya da SÜTUN($A$3:$U$3) şeklinde yazılması gerekir ki formül çalışırken 1, 2,3, ......,21 şeklinde ilk diziyi üretsin.
Bunun için (SÜTUN($B$3:$V$3)-SÜTUN($B$3)+1) şeklinde formüle ettik.
SÜTUN($B$3:$V$3) ile 2, 3, 4, 5....,22 şeklinde dizi elde edilir. (B sütunu 2 ye karşılık gelir)
SÜTUN($B$3:$V$3)-SÜTUN($B$3) ile 2-2, 3-2, 4-2, 5-2,....,22-2 yani, 0, 1, 2, 3,.....,20 dizisi elde edilir.
İstenilen 1, 2, 3 ...., 21 dizisi olduğundan SÜTUN($B$3:$V$3)-SÜTUN($B$3) + 1 şeklinde formüle edilerk istenilen dizi elde edildi.

Şimdi koşulun değerlendirilmesi var.
Koşul (KAYDIR($B$3:$V$3;KAÇINCI($F37;$A$4:$A$34; );;1; )<>"") şeklinde...
Koşulun TOPLAMA işlevi içerisinde değerlendirilip istenilen dizinin elde edilmesi, elde edilen SÜTUN dizisinin KOŞUL a bölünmesi ile sağlanıyor.

Son parametre olan k :
21 sütun verimiz var. Fakat istenilen listede ise, araya İSİM lerin de katılmasıyla 28 sütun yapmakta. Burada değerlendirilen bir özellik var... Her 3 sütun sonrasında bir İSİM yer almakta. Yani her 4 sütunda 1 İSİM yazmakta...

k, KÜÇÜK işlevinde Sıra No anlamındadır. Bunun da DİZİ olması gerekir. DİZİ miz 21 sütun olduğundan k 1, 2, 3, ..... 21 olmalıdır.
Her 3 sütun sonrasında İSİM tekrar ettiğinden, EĞERSAY($H$36:H$36;"=İSİM") ile İSİMleri saydırıyoruz. İlk İSİM 1 olacağından biz 0 olmasını istiyoruz ve İSİM olmayanları da 1 den itibaren sayabilelim. O nedenle (EĞERSAY($H$36:H$36;"=İSİM")-1) şeklinde yazıyoruz. Her 3 sütun sonrasında İSİM tekrar ettiğinden dolayı da bu ifadeyi 3 ile çarpıyoruz. (EĞERSAY($H$36:H$36;"=İSİM")-1)*3...... Şimdi de 1 ilave ederek dizinin 1 den başlamasını sağlıyoruz. (EĞERSAY($H$36:H$36;"=İSİM")-1)*3 + 1......
Bunu irdelersek, ilk İSİM i saydık sonuç 1 .... 1 çıkarttık sonuç 0..... 3 ile çarptık sonuç 0.... 1 ekledik sonuç 1....
$H36 : K$36 ya kadar 1, 2, 3 elde ettik. $H36 : M$36 olduğunda ise 2 adet İSİM var. M36 da ise 4 elde edilmesi gerekir.
(EĞERSAY($H$36:M$36;"=İSİM")-1)*3 + 1....
Bunu irdelersek, 2 adet İSİM saydık sonuç 2 .... 1 çıkarttık sonuç 1..... 3 ile çarptık sonuç 3.... 1 ekledik sonuç 4....
Örnek olarak, Y36 da ise, k = 13 olmalı... Yani W36 da 12 elde ettik.. 1, 2, 3,.....,12 şimdi13 ü bulmak gerekir
(EĞERSAY($H$36:Y$36;"=İSİM")-1)*3 + 1....
Bunu irdelersek, 5 adet İSİM saydık sonuç 5 .... 1 çıkarttık sonuç 4..... 3 ile çarptık sonuç 12.... 1 ekledik sonuç 13....

Böylelikle, k 1, 2, 3,....,21 e kadar DİZİ elde edilmektedir.

Eğer k için değer bulunamayan hücreye gelinirse, o zaman #YOK hatası verecektir. TOPLAMA işlevinde 6 parametresi ile HATAlar YOK sayıldığından da değerlendirmeye alınmayacaktır.

Umarım açıklayabilmişimdir....

Not : İkinci Gönderdiğim dokumanda, Excel2003 versiyon için bir satır daha eklemiştim. Bu satırda ise TOPLAMA işlevinin yerine klasik DİZİ formülü yani
İNDİS(........;KÜÇÜK(EĞER(Koşul;SATIR(ya da SÜTUN));k)) DİZİ formülü kullanılmıştır. Karşılaştırabilirsiniz....
 
Üst