Düşeyara'dan daha kapsamlı bir fonksiyon

[vaskal]

Sevgili dostlar ekli dosyamda ne istediğimi anlatmaya çalıştım.Bir bakarsanız sevinirim.

İsteğim kısaca şu: DÜŞEYARA bir hücredeki değeri başka bir tablodaki denk gelen satırdan istediğiniz sütundaki değeri verir, tamam...

Ancak ben değişken bir satır ve değişken bir sütunun kesiştiği hücredeki değeri diğer tablonun denk gelen satır ve sütununun kesiştiği yerden getirecek bir fonksiyon yada komut arıyorum.

 

[geren36]

düşeyara' dan başka bir şey istemişsin ama ben yine de düşeyara ile yaptım.

bana en kolay yol böyle geldi

not :[ vlookup = düşeyara ]

 

[Orion1]

Merhaba.
Kullanıcı tanımlı (KTF) fonksiyonla yaptım.
Ekli dosyayı inceleyiniz.

Function arabul(aranan_gorev As Range, aranılan_gorev As Range, aranan_gun As Range, aranılan_gun As Range)
If aranan_gorev = "" Or aranan_gun = "" Then Exit Function
Set sat = aranılan_gorev.Find(aranan_gorev, LookIn:=xlValues, lookat:=xlWhole)
Set sut = aranılan_gun.Find(aranan_gun, LookIn:=xlValues, lookat:=xlWhole)
If sat Is Nothing Or sut Is Nothing Then Exit Function
arabul = Sheets("Liste").Cells(sat.Row, sut.Column).Value
End Function

 

[Merhum İdris SERDAR]

Sevgili dostlar ekli dosyamda ne istediğimi anlatmaya çalıştım.Bir bakarsanız sevinirim.

İsteğim kısaca şu: DÜŞEYARA bir hücredeki değeri başka bir tablodaki denk gelen satırdan istediğiniz sütundaki değeri verir, tamam...

Ancak ben değişken bir satır ve değişken bir sütunun kesiştiği hücredeki değeri diğer tablonun denk gelen satır ve sütununun kesiştiği yerden getirecek bir fonksiyon yada komut arıyorum.

Dosyanız ekte.

Günlerin adları için uzun formül yazmanız gerekmez. Onu da değiştirdim.

..

 

[vaskal]

Hepinize ilginizden dolayı çok teşekkür ederim. Kardeşler formüllerin mantığını tam anlayamadım. Mantığı en kolay olan Geren36 onu biraz anlayabilirim gibi. Kısa bir açıklama gönderirmisiniz.

Geren36 program istediğim gibi değil. Çünkü tarih değişince sarı satırlardaki günlerde otomatik olarak değişiyor. Fakat senin çalışmanda günlere göre girilmiş olan rakamların yeri sabit kalıyor. Mesela Müdür pazartesi hep 10 olması gerekirken. Günler değiştiği zaman busefer atıyorum çarşamba 10 oluyor. Çünkü bulduğu değerle hep sabit kalıyor.. Formülü daha tam olarak kavrayamadığım için düzeltemedim.

Sayın yurttaş gün adları formülüne bir kitapta rastlamıştım. Zaten mantığınıda tam kavrayamadım desem yalan olmaz.

Benim yapmayı düşündüğüm DÜŞEYARA yöntemini bir bilseniz inanamazsınız. Acayip karmaşık ve çok acemice olacaktı. Sizinkiler çok basit ancak mantıklarını anlayamıyorum. Yani kendi asıl çalışmama nasıl uygulayabileceğimi tam çıkaramadım.

 

[Merhum İdris SERDAR]

Hepinize ilginizden dolayı çok teşekkür ederim. Kardeşler formüllerin mantığını tam anlayamadım. Mantığı en kolay olan Geren36 onu biraz anlayabilirim gibi. Kısa bir açıklama gönderirmisiniz.

Geren36 program istediğim gibi değil. Çünkü tarih değişince sarı satırlardaki günlerde otomatik olarak değişiyor. Fakat senin çalışmanda günlere göre girilmiş olan rakamların yeri sabit kalıyor. Mesela Müdür pazartesi hep 10 olması gerekirken. Günler değiştiği zaman busefer atıyorum çarşamba 10 oluyor. Çünkü bulduğu değerle hep sabit kalıyor.. Formülü daha tam olarak kavrayamadığım için düzeltemedim.

Sayın yurttaş gün adları formülüne bir kitapta rastlamıştım. Zaten mantığınıda tam kavrayamadım desem yalan olmaz.

Benim yapmayı düşündüğüm DÜŞEYARA yöntemini bir bilseniz inanamazsınız. Acayip karmaşık ve çok acemice olacaktı. Sizinkiler çok basit ancak mantıklarını anlayamıyorum. Yani kendi asıl çalışmama nasıl uygulayabileceğimi tam çıkaramadım.

AÇIKLAMA:

=IF(ISERROR(INDEX(Liste!$A$1:$H$15;MATCH(Sayfa2!$B10;Liste!$A$1:$A$15;0);MATCH(Sayfa2!C$2;Liste!$A$1:$H$1;0)));"";INDEX(Liste!$A$1:$H$15;MATCH(Sayfa2!$B10;Liste!$A$1:$A$15;0);MATCH(Sayfa2!C$2;Liste!$A$1:$H$1;0)))

=EĞER(EHATALIYSA(İNDİS(Liste!$A$1:$H$15;KAÇINCI(Sayfa2!$B10;Liste!$A$1:$A$15;0);KAÇINCA(Sayfa2!C$2;Liste!$A$1:$H$1;0)));"";İNDİS(Liste!$A$1:$H$15;KAÇINCI(Sayfa2!$B10;Liste!$A$1:$A$15;0);KAÇINCI(Sayfa2!C$2;Liste!$A$1:$H$1;0)))

1. Esas formül:

=İNDİS(Liste!$A$1:$H$15;KAÇINCI(Sayfa2!$B10;Liste!$A$1:$A$15;0);KAÇINCI(Sayfa2!C$2;Liste!$A$1:$H$1;0)))

Şeklindedir.

Bu formülde Excel’in yerleşik iki fonksiyonu yer almaktadır. Bunlar İNDİS ve KAÇINCI fonksiyonlarıdır.

INDEX - İNDİS

Tabloda veya dizide bulunan öğenin değerini, satır ve sütun numarası indisleri ile seçerek verir.
İNDİS fonksiyonunun iki sözdizimi biçimi vardır: Dizi ve başvuru. Dizi biçimi, her zaman bir değer veya değer dizisi verir, başvuru biçimiyse her zaman bir başvuru verir. İNDİS fonksiyonunun birinci bağımsız değişkeni bir dizi sabitiyse, dizi biçimini kullanın.

Dizi biçimi
Sözdizimi:
İNDİS(dizi;satır_sayısı;sütun_sayıs&#305
INDEX(array,row_num,column_num)

=İNDİS(Liste!$A$1:$H$15 formülünü yazdığımızda bize satır ve sütun sayısı gerekecektir. Nasıl bir satır sayısı ve sütun sayısı vermemiz gerekeceğini de KAÇINCI fonksiyonu ile bulmaya çalışırız.

MATCH - KAÇINCI

Bir dizide belirtilen sıradaki, belirtilen değerle eşleştirilen öğenin göreli konumunu verir. Öğenin kendisine değil, ama öğenin aralıktaki konumuna gerek KAÇINCI fonksiyonu kullanılır.

Sözdizimi:
KAÇINCI(aranan_değer;aranan_dizi;eşleştir_tür)
MATCH(lookup_value,lookup_array,match_type)

Aranan_değer tabloda bulunmasını istediğiniz değeri bulmak için kullandığınız değerdir.
• Aranan_değer, aranan_dizide bulmak istediğiniz değerdir. Örneğin, telefon defterinde numara ararken, aranan değer olarak kişinin adını kullanırsınız, telefon numarası ise bulmak istediğiniz değerdir.
• Aranan_değer bir değer (sayı, metin ya da mantıksal değer) ya da bir sayı, metin ya da mantıksal değere yapılan bir hücre başvurusu olabilir.
Aranan_dizi olası aranan değerleri içeren bitişik bir hücre aralığıdır. Aranan_dizinin dizi ya da dizi başvurusu olması gerekir.
Eşleştir_tür -1, 0 ya da 1 sayısıdır. Eşleştir_tür, Microsoft Excel'in aranan_değeri aranan_dizideki değerlerle nasıl eşleştirdiğini belirler.
• Eşleştir_tür 1 ise, KAÇINCI fonksiyonu aranan_değerden küçük ya da eşit olan en büyük değeri bulur. Aranan_dizi artan sırada yerleştirilmelidir: ...-2, -1, 0, 1, 2, ...A-Z, YANLIŞ, DOĞRU.
• Eşleştir_tür 0 ise, KAÇINCI fonksiyonu aranan_değere tam olarak eşit olan ilk değeri bulur. Aranan_dizi herhangi bir sırada olabilir.
• Eşleştir_tür -1 ise, KAÇINCI fonksiyonu aranan_değerden büyük ya da eşit olan en küçük değeri bulur. Aranan_dizi azalan sırada yerleştirilmelidir: DOĞRU, YANLIŞ, Z-A, ...2, 1, 0, -1, -2, ..., vb.
• Eşleştir_tür belirtilmezse, 1 olduğu varsayılır.


İlk aradığımız değer B10, B11, B12 hücrelerindeki değerlerden birisidir. Bunu Liste sayfasının !$A$1:$A$15 aralığındaki satırlarda aramamız gerekir. O zaman İNDİS formülümüzün satır sayısını bize verecek formül parçası şu şekilde olacaktır: KAÇINCI(Sayfa2!$B10;Liste!$A$1:$A$15;0)

İkinci arayacağımız değer A1, B1, …..H1 hücrelerinden birisidir. Bunu da Liste sayfasının A1:H1 aralığında aramamız gerekir. Bu kez İNDİS formülümüzün satır sayısını bize verecek formül parçası şu şekilde olacaktır: KAÇINCI(Sayfa2!C$2;Liste!$A$1:$H$1;0)

Bunları yanyana yazarsak temel formülümüz:

=İNDİS(Liste!$A$1:$H$15;KAÇINCI(Sayfa2!$B10;Liste!$A$1:$A$15;0);KAÇINCI(Sayfa2!C$2;Liste!$A$1:$H$1;0)))

Şeklinde olacaktır.

2. IF – EĞER fonksiyonu

Belirtilen koşul DOĞRU olarak değerlendirilirse bir değer, YANLIŞ olarak değerlendirilirse başka bir değer verir.

Değerler ve formüller üzerinde koşullu sınamalar yürütmek için EĞER fonksiyonunu kullanılır.

Sözdizimi:

EĞER(mantıksal_sınama;eğer_doğruysa_değer;eğer_yanlışsa_değer)
IF(logical_test,value_if_true,value_if_false)
EHATALIYSA() fonksiyonu:“Eğer hatalıysa” deyiminin kısa şekli olan ehatalıysa() eğer ilgili hücre değeri hata değerlerinden birisi ise “DOĞRU” hata mesajı değilse “YANLIŞ” değerini alır. Hata mesajları ise #YOK!, #DEĞER!, #BAŞV!, #SAYI/0!, #NUM!, #AD!, #BOŞ!...

Şimdi bizim temel formülümüz değerlerden birisini tabloda görmezse #YOK! Hata mesajın verecektir. Biz bunları görmek istemezsek şöyle bir formül kalıbını kullanmamız gerekir.

=EĞER(EHATALIYSA(Temel Formülümüz);””; Temel Formülümüz)

Buna göre formülümüz:

=EĞER(EHATALIYSA(İNDİS(Liste!$A$1:$H$15;KAÇINCI(Sayfa2!$B10;Liste!$A$1:$A$15;0);KAÇINCA(Sayfa2!C$2;Liste!$A$1:$H$1;0)));"";İNDİS(Liste!$A$1:$H$15;KAÇINCI(Sayfa2!$B10;Liste!$A$1:$A$15;0);KAÇINCI(Sayfa2!C$2;Liste!$A$1:$H$1;0)))

Şeklinde olacaktır.

3. Gelelim ilgili tarihin gününü yazdırmaya. TEXT =METNEÇEVİR fonksiyonuna
Bir değeri belirtilen sayı biçiminde metne çevirir.

Sözdizimi
METNEÇEVİR(değer;biçim_metni)

Değer sayısal değer, sayısal değeri değerlendiren bir formül veya sayısal değer içeren bir hücreye yapılan bir başvurudur. Biçim_metni Hücreleri Biçimlendir iletişim kutusundaki Sayı sekmesinde Kategori kutusundaki metin biçiminde bir sayı biçimidir.

=TEXT(WEEKDAY(C1);"gggg")

=METNEÇEVİR(HAFTANINGÜNÜ(C1);"gggg") şeklinde yazarsak, sizin yazdığınız :

=IF(WEEKDAY(C1;3)=0;"Pazartesi";IF(WEEKDAY(C1;3)=1;"Salı";IF(WEEKDAY(C1;3)=2;"Çarşamba";IF(WEEKDAY(C1;3)=3;"Perşembe";IF(WEEKDAY(C1;3)=4;"Cuma";IF(WEEKDAY(C1;3)=5;"Cumartesi";IF(WEEKDAY(C1;3)=6;"Pazar";"")))))))

Formülünden daha kısa ve aynı sonuca ulaşırsınız.

Umarım anlatabilmişimdir.

Daha fazla bilgi için aşağıdaki linklerde yer alan dosyaları inceleyin.

http://www.excel.web.tr/showthread.php?t=17327

http://www.excel.web.tr/showthread.php?t=26373

http://www.excel.web.tr/showthread.php?t=24184


.

 

[vaskal]

Teşekürler....Yurttaş

Şimdi farkettim, A1 hücresine tam tarih mesela 21/08/2007 yazıp başka bir hücreye
=METNEÇEVİR(A1;"gggg") formülünü yazınca da aynı sonucu veriyor. Acaba çalışmamda bu formülü kullansam başka bir zaman sorun verebilirmi?

 

[Orion1]

Geren36 program istediğim gibi değil. Çünkü tarih değişince sarı satırlardaki günlerde otomatik olarak değişiyor. Fakat senin çalışmanda günlere göre girilmiş olan rakamların yeri sabit kalıyor. Mesela Müdür pazartesi hep 10 olması gerekirken. Günler değiştiği zaman busefer atıyorum çarşamba 10 oluyor. Çünkü bulduğu değerle hep sabit kalıyor.. Formülü daha tam olarak kavrayamadığım için düzeltemedim.

Benim eklediğim dosyayı inceledinizmi?Onda esneklik vardı.Kullanıcı tanımlı fonksiyon ile hazırlamıştım.

 

[Merhum İdris SERDAR]

Teşekürler....Yurttaş

Şimdi farkettim, A1 hücresine tam tarih mesela 21/08/2007 yazıp başka bir hücreye
=METNEÇEVİR(A1;"gggg") formülünü yazınca da aynı sonucu veriyor. Acaba çalışmamda bu formülü kullansam başka bir zaman sorun verebilirmi?

Neden versin? Bu da olur.

.

 

[vaskal]

Arkadaşlar ilginze teşekkürler... Artık bu işi tam anladım gibi. Sıra kendi programıma uyarlamaya geldi.

Sevgili Orion2 senin yöntemin çok daha kolay ancak şu anda sadece makroyu kopyala yapıştır yapıp kendi programıma aktarıyorum.
Makro kodlarının nasıl yazıldığını ve mantığını daha hiç bilmiyorum.
Benim maksadım çalışmamı hazırlarken bir yandanda excel'i anlamak.
Bu yüzden yurttaş ın açıklamasını dikkatle inceliyorum. Çünkü onu kavrayabiliyorum. Zaten çalışmamda anlamasamda senin makronu(arabul) kullanmayı düşünüyorum çünkü daha basit.