Soru İki değişken karşısına isim yazma.

[playermause]

Selam ustalarım.
Bana bir vba makro veya formül lazım. Yardımcı olursanız sevinirim. Sitede bir çok konuya baktım ama durumum uygun bir formül ve makro bulamadım.

10 arkadaş bir çalışmaya istinaden anket çalışması yapacağız. Elimizde aşağıda belirtilen gibi adres listesi var. Ve ayrıca gidilecek 20 -30 tane mahalle ve bu mahallelere ait cadde ve sokak düzenli listeleri var . Bazı mahalleler büyük bazı mahalleler ise küçük haliyle büyük mahallelerde çok cadde ve sokak bulunurken küçük mahallelerde daha az cadde ve sokaklar var. Bu bağlamda bazı mahalleleri 1 kişi gezerken bazı mahalleleri 2 veya 3 kişiye böleceğim.

Örnek olarak bana verilen listenin hepsi bir hücrede ve aşağıdaki gibi ;

19 mayıs mahallesi 147 cadde Kezban sokak no:xx Keçiören ANKARA
1487 cadde hasan sokak no : xx Atapark mah. Keçiören ANKARA
Ayvalı Mh. 152 cad. no:xx Keçiören ANKARA

Düzenli listenin cadde ve sokak isimleri karşısında arkadaşların adı yazıyor. Benim istediğim yukarıda bana verilen liste yanına elimdeki düzenli listeyi baz alarak yan hücresine arkadaşın adı yazsın.
Örnek olarak . 19 mayıs ve Kezban sokağı görünce yan hücresine arkadaşın adı yazsın.

19 mayıs mahallesi 147 cadde Kezban sokak no:xx Keçiören ANKARA Mahmut gibi

ÖRNEK DOSYAYI EKLİYORUM.

mahalle listesi.xlsx - 20 KB

Şimdiden çok teşekkür ederim.

 

[Korhan Ayhan]

Merhaba,

LİSTE sayfasında C1 hücresine AYVALI yazınız.

C2 hücresine aşağıdaki formülü uygulayıp alta doğru sürükleyiniz.

=EĞERHATA(İNDİS(DOLAYLI("'"&C$1&"'!$D$1:$D$1000");KAÇINCI(1;(DOLAYLI("'"&C$1&"'!$A$1:$A$1000")<>"")*ESAYIYSA(MBUL(DOLAYLI("'"&C$1&"'!$A$1:$A$1000");$A2))*ESAYIYSA(MBUL(DOLAYLI("'"&C$1&"'!$B$1:$B$1000");$A2));0));"")

Diğer sayfa isimlerini D1-E1-F1 hücrelerine yazarak formülü sağa ve alta doğru sürükleyiniz.

Eşleşen isimler listelenecektir.

 

[playermause]

Merhaba,

LİSTE sayfasında C1 hücresine AYVALI yazınız.

C2 hücresine aşağıdaki formülü uygulayıp alta doğru sürükleyiniz.

=EĞERHATA(İNDİS(DOLAYLI("'"&C$1&"'!$D$1:$D$1000");KAÇINCI(1;(DOLAYLI("'"&C$1&"'!$A$1:$A$1000")<>"")*ESAYIYSA(MBUL(DOLAYLI("'"&C$1&"'!$A$1:$A$1000");$A2))*ESAYIYSA(MBUL(DOLAYLI("'"&C$1&"'!$B$1:$B$1000");$A2));0));"")

Diğer sayfa isimlerini D1-E1-F1 hücrelerine yazarak formülü sağa ve alta doğru sürükleyiniz.

Eşleşen isimler listelenecektir.

Korhan hocam Allah razı olsun. Tebrik ederim.

Formülü bakıyorum ama anlayamadım yeminle kısaca anlatmanız mümkün mü ? sayfa 1 sayfa 2 leri nasıl gösterdiniz. formül içerisinde bilgi de yok.
bende 2 formül biliyorum diye geziyorum.

 

[Korhan Ayhan]

Sayfa isimlerini C1-D1-E1 ve devamına yazıyoruz.

Sonrasında DOLAYLI fonksiyonu ile sayfa isimlerini bu hücrelerden aldırıyoruz.

Sayfalardaki A-B sütunlarındaki CADDE ve SOKAK bilgileri LİSTE sayfasındaki A2 hücresindeki adres içinde aranıyor. Bu ikili eşleşme sonucunda 1;0;0;0;0..... şeklinde hücre bazında bir dizi oluşuyor. Bu dizi içinde KAÇINCI ile ilk 1 değerini buluyoruz. Formülün başındaki İNDİS ile sayfalardaki D sütunundaki isimleri getiriyoruz.

 

[playermause]

Sayfa isimlerini C1-D1-E1 ve devamına yazıyoruz.

Sonrasında DOLAYLI fonksiyonu ile sayfa isimlerini bu hücrelerden aldırıyoruz.

Sayfalardaki A-B sütunlarındaki CADDE ve SOKAK bilgileri LİSTE sayfasındaki A2 hücresindeki adres içinde aranıyor. Bu ikili eşleşme sonucunda 1;0;0;0;0..... şeklinde hücre bazında bir dizi oluşuyor. Bu dizi içinde KAÇINCI ile ilk 1 değerini buluyoruz. Formülün başındaki İNDİS ile sayfalardaki D sütunundaki isimleri getiriyoruz.

Koray hocam formül çalışıyor elinize emeğinize sağlık. Bir kaç soru ve sorun çıktı yardımınızı esirgemezseniz sevinirim.

Bugün 4-5 mahalleyi alt çalışma sayfalarına yerleştirmeye başladım ama formülü uygulama çalıştığımda excel çok kasmaya ve geç cevap vermeye başladı. Daha 30 a yakın mahalle ekleyecegim. Acaba formulü hızlandırma şansımız var mı.vba gibi.
- yavasladıgı için tüm mahalle ve sokakları tek bir sayfaya olsa daha mı hızlı olur acaba. Mahalle sokak arkadas ismi a-b-c gibi.
-kaydet tuşuna basmadan formülü uygulamadı. Formül hesaplama seçenekleri otomatik olmasına rağmen.

- arkadaş isimleri mahallenln altına geliyor aynı zamanda en başındaki sutunda da olabilir mi ? Alt alta olacak şekilde.

Teşekkür ediyorum

 

[Korhan Ayhan]

Veriler tek sayfada olursa hız olarak avantaj sağlayabilir.

 

[playermause]

Veriler tek sayfada olursa hız olarak avantaj sağlayabilir.

Değerli hocam , mahalleleri tek sayfada toplayınca 10000 e yakın mahalle var ama sadece 1000 tane mahalleyi getiriyor. formülde 1000 leri 10000 yaptım ama bu sefer de formül bozuldu. tek tek 10000 yaptım yine olmadı. Nerede yanlış yapıyorum acaba. Bu arada bir tık hızlandı.

saygılar

=EĞERHATA(İNDİS(DOLAYLI("'"&C$1&"'!$D$1:$D$1000");KAÇINCI(1;(DOLAYLI("'"&C$1&"'!$A$1:$A$1000")<>"")*ESAYIYSA(MBUL(DOLAYLI("'"&C$1&"'!$A$1:$A$1000");$A2))*ESAYIYSA(MBUL(DOLAYLI("'"&C$1&"'!$B$1:$B$1000");$A2));0));"")

 

[Korhan Ayhan]

Verileri tek sayfaya topladıysanız artık DOLAYLI fonksiyonuna ihtiyaç kalmayacaktır.

Formülü hücreye yazdıktan sonra hücre içindeyken CTRL+SHIFT+ENTER tuşlarına basarak DİZİ formülüne çevirip deneyiniz.

 

[playermause]

Hocam kusura bakmayın lütfen, hücre içinde de ctrl+shift+enter yaptım yine olmadı. Neyi beceremiyorum anlamadım..Listede 3000 adres var 2000 ni görmüyor. dosyayı yükledim rica etsem bakar mısınız neyi yanlış yapıyorum.

İndir mahalle listesi22 xlsx

Dosyayı indir mahalle listesi22 xlsx

dosya.co

 

[Korhan Ayhan]

Son satır bilgisi olarak formülde 1000 yazıyor. Geri kalanı görmemesi normal..

Verileri tek sayfaya aldığınız için sayfa adının dinamik olması için kullandığımız DOLAYLI fonksiyonuna gerek kalmamış oldu.

Aşağıdaki formülü yazıp aşağıya doğru sürükleyiniz.

=EĞERHATA(İNDİS(MAMAK!$D$1:$D$10000;KAÇINCI(1;(MAMAK!$A$1:$A$10000<>"")*ESAYIYSA(MBUL(MAMAK!$A$1:$A$10000;$B2))*ESAYIYSA(MBUL(MAMAK!$B$1:$B$10000;$B2));0));"")

 

[playermause]

Çok teşekkür ediyorum Koray hocam formül süper çalışıyor. Allah razı olsun.

Neredeyse çoğu Excel formülünü biliyorum ve kullanıyorum ama formülleri iç içe kullanamıyorum. Yada formülleri sıralamada sorun yaşıyorum. Forumunuzdan inşallah öğrenme çabasına gireceğim.