Excelde LIKE Fonkisyonu Kullanabilir miyim!

[nevermore]

Merhaba,
ekte anlatmaya çalıştığım gibi açık adresi olan sütundan sadece bulunduğu ili almam gerekiyor.
Bunu yapabileceğim bir yol varmıdır. makro veya fonksiyon hiç farketmez.
teşekkürler.

 

[ulutanas]

dosyayı açtım fakat sormak istediğiniz soruyu anlayamadım daha detaylı bir şekilde sorununuzu yazarsanız yardımcı olmaya çalışırım kardeş.

 

[nevermore]

Ornek dosyayı degiştirerek ekledim. inşallah şimdi işe yarar.

Teşekkür ederim.

 

[Ömer]

Merhaba,

Veri düzeni her satır için aynı olsa yardımcı kullanmadan formül yazılabilir fakat veri düzeni farklı olduğu için data kullanmanızda fayda var.

Kısaca açıklamaya çalışayım;

Sheet2 sayfasının A sütununda tüm il adlarını yazın. Bu sayfa data sayfamız olsun.

Daha sonra Sheet1 sayfası C2 hücresine aşağıdaki formülü yazıp dizi formülüne çevirdikten sonra alt satırlara kopyalayın.

=İNDİS(Sheet2!$A$1:$A$100;KAÇINCI(1;--ESAYIYSA(MBUL(Sheet2!$A$1:$A$100;B2));0))

İNDİS ~ INDEX
KAÇINCI ~ MATCH
ESAYIYSA ~ ISNUMBER
MBUL ~ SEARCH

Dizi formülüne çevirme: Formülü hücreye yazdıktan sonra entera basmadan ctrl + shift + enter tuş kombinasyonu ile girişini tamamlayınız. Bu işlemden sonra formülün başına ve sonuna { } ayıraçları otomatik eklenecektir.

.

 

[nevermore]

vallaha çok makbule geçti. 1000 satırı manuel yapmaktan kurtardın. Allah razı olsun.
teşekkür ederim. iyi akşamlar.

 

[Ömer]

Rica ederim, iyi çalışmalar..

 

[ergun_guler]

Merhabalar,

Benimde benzer bir problemim var. Formülü uygulamama rağmen sonuç çıkmıyor. Yardımcı olabilirmisiniz? Örnek dosya ektedir.

Teşekkürler.

 

[Ömer]

Formülü dizi formülüne çevirmeniz gerekir. #4 nolu mesajda dizi formüyle ilgili açıklamayı okuyunuz..

.

 

[hakancetin5@yahoo.com]

Kolay gelsin

=İNDİS(Sheet2!$A$1:$A$100;KAÇINCI(1;--ESAYIYSA(MBUL(Sheet2!$A$1:$A$100;B2));0))


ben burada



;--ESAYIYSA(
Ve

MBUL(Sheet2!$A$1:$A$100;B3)

Dizi formülde Nasıl oluyorda çalışıyor onu anlamadım.

mesela ESAYIYSANIN BAŞINDAKİ -- LER NEDİR
VE MBUL NASIL OLUYORDA ARALIK ALIYOR ?

 

[hakancetin5@yahoo.com]

Hala cevap alamadım

 

[Ömer]

Kolay gelsin
=İNDİS(Sheet2!$A$1:$A$100;KAÇINCI(1;--ESAYIYSA(MBUL(Sheet2!$A$1:$A$100;B2));0))
ben burada
;--ESAYIYSA(
Ve
MBUL(Sheet2!$A$1:$A$100;B3)
Dizi formülde Nasıl oluyorda çalışıyor onu anlamadım.
mesela ESAYIYSANIN BAŞINDAKİ -- LER NEDİR
VE MBUL NASIL OLUYORDA ARALIK ALIYOR ?

Formül açıklamaları zaman gerektiren bir durum olduğu için konuya geri dönüşüm geçikti maalesef.

Konuyu farklı üyülerin de inceleyecek olduğunu düşünerek, sırayla ve basite indirerek açıklamaya çalışayım.

İndis fonksiyonu:
Belirlenen dizi içinde satır ve sütun indisini belirlediğiniz değeri size verir.
=indis(a1:c10;2;3) örneğinde a1:c10 arasındaki 2.satır 3. sütundaki değeri size verir. Bu değer C2 hücresine denk gelir.

Kaçıncı fonksiyonu:
Aranan değerin dizide kaçıncı sırada olduğunu bize verir.
=kaçıncı("a";a1:c1;0) örneğinde a1:c1 arasında a değeri b1 hücresinde ise formül sonucu bize 2 değerini verir. Çünkü a1:c1 dizindeki b1 hücresi 2. dizi elemanıdır.

Esayıysa fonksiyonu:
Değerin sayı olup olmadığını kontrol eder. Eğer veri sayı ise formül sonucu DOĞRU, sayı değilse YANLIŞ olarak döndürür.

Sizin formülde sorduğunuz Esayıysanın önündeki -- işlemi dizi sonucunu sayıya çevirmek için kullanılan metodlardan biridir.

Örnek verirsek, Esayıysa ile oluşan dizi {DOĞRU;DOĞRU;YANLIŞ;DOĞRU;YANIŞ} şeklinde olsun. Bu diziyi sayıya çevirmek için kullandığımız -- metodu ile dizi aşağıdaki hali alır.

--{DOĞRU;DOĞRU;YANLIŞ;DOĞRU;YANIŞ}
{1;1;0;1;0} dizinin son durumu bu şekilde olur.

Sayıya çevirme işleminde DOĞRU değeri 1 e, YANLIŞ değeride 0 a dönmüştür. Bu durumun progralama diliyle alakalıdır, Excelde sayısal işlem sonucu DOĞRU başvurusu 1'e , YANLIŞ başvurusu ise 0'a döner.

Bunu basit bir örnek ile açıklayabilirim.

A1 e 5, B1 e de 5 yazın ve C1 e =A1=B1 yazın. formül sonucunun DOĞRU yu çevirdiğiniz görebilirsiniz. Bu formüle basit bir matematik işlemi uygularsak,

=(A1=B1)+0 gibi. Formül sonucunu DOĞRU değil 1'e döndürdüğünü gözlemleyebilirsiniz.

Konumuza dönersek, neden biz diziyi sayıya çevirdik,

KAÇINCI(1;--ESAYIYSA(MBUL(Sheet2!$A$1:$A$100;B2));0)

Dikkat edilirse kaçıncı fonksiyonunda aranan değer 1 dir, işte biz 1 değerini aradığımız
{DOĞRU;DOĞRU;YANLIŞ;DOĞRU;YANIŞ} dizide -- ile sayıya çeviririzki {1;1;0;1;0} dizinin son haliyle 1 i yakalayabilelim.

Tabi arama olayı tamamen değişkendir. Formülü farklı yazarsanız sayıya çevirmeden de arama yaptırabilirdik.

KAÇINCI(DOĞRU;ESAYIYSA(MBUL(Sheet2!$A$1:$A$100;B2));0) gibi formülü yazsaydık, esayıysa ile oluşan diziyi -- ile sayıya çevirmemize gerek kalmazdı. Çünkü aranan değer DOĞRU başvurusudur.

Kısaca burada ki mantık bu şekildedir. Formüllerin mantığını algıladıktan sonra kullanım şekli formülü yazandan yazana değişir.

MBUL fonksiyonu;
Belirlenen bir değeri hücre içinde arar ve bulduğu değerin metin içindeki ilk karakter sayısını bize verir.
=mbul("ömer";"excel.web.tr de ömer adlı üye") formülde "ömer" değeri "excel.web.tr de ömer adlı üye" metninde aranır ve sonucu 17 olarak döndürür. Nedeni ise "excel.web.tr de ömer adlı üye" metinde "ömer" kelimesinin ilk karakteri metin içinde 17 karakterden başlamaktadır.

Bu formül diziye çevrilince neden aralık arar sorunuz ise; Dizi formüllerinin genel özelliği bu şekildedir. Bir çok değeri hesaplayıp sonuc üretme özelliğine sahiptir.
Dizi formülleriyle ilgli Excel Dersanesi bölümündeki makaleyi okumanızı tavsiye ederim.

Formül içinde kullanılan materyalleri açıkladıktan sonra formülü bir bütün olarak düşünürsek;

Formülü kısaltıp, tahmini değerlerle açıklamaya çalışayım.

=İNDİS(Sheet2!$A$1:$A$3;KAÇINCI(1;--ESAYIYSA(MBUL(Sheet2!$A$1:$A$3;B2));0))

Formül, Sheet2 A1:A3 aralığındaki tüm değerleri dizi içinde B2 hücresinde arar.

Bulduğu değerin Sheet2 de kaçıncı satırda olduğu verir ve bu satır indisi ile indis fonksiyonu ile değer alınır.

Örnek üzerinden gidersek,

Sheet2;
A1 hücresi bursa
A2 hücresi istanbul
A3 hücresi balıkesir olsun.

Sheet1 B2 deki veri de; "xx Cad. 201/A ISTANBULTÜRKIYE" olsun.

Bu veriye göre formülün iç düzeninde Mbul kısmı;
Mbul({"bursa"\"istanbul"\"balıkesir"};"xx Cad. 201/A İSTANBULTÜRKIYE")

Mbul fonksiyonu dizi oluşturarak "xx Cad. 201/A İSTANBULTÜRKIYE" değeri içinde 3 ayrı ilide arar. Bulduğu ilin sayısal ilk karekter sayısını verir bulamadığunda ise #değer hatası üretir.

{#değer!\15\#değer!} mbul dizisi bu hali alır. Dizi içerisinde görüldüğü gibi, bursa ve balıkesiri bulamadığı için değer hatası verdi, istanbulu bulduğu için ilk karakter sayısının sıra değerini verdi.

Mbul dan sonra Esayıysa kullanmamızda amaç ise , bu dizide görüleceği gibi hatalı değer ile sayı değerini birbirinden ayrımak içindir, Esaysısa burada devreye girer ve hatalı yani sayı olmayan değerleri YANLIŞ'a sayı olan değeri ise DOĞRU'ya çevirir.

Esayıysa({#değer!\15\#değer!}) dizisi

{YANLIŞ\DOĞRU\YANLIŞ} esayıysanın işleminden sonra bu hali alır.

-- operatörünü bu dizi önünde kullanarak Doğru ve Yanlışı sayısal değere çeviririz.

--{YANLIŞ\DOĞRU\YANLIŞ} dizisi {0\1\0} halini alır.

Artık formülün hesaplaması Kaçıncı fonksiyon ile devam edecektir.

KAÇINCI(1;--ESAYIYSA(MBUL(Sheet2!$A$1:$A$3;B2));0) işaretli bölümün yerine bulunan dizi yerleşir.

Kaçıncı(1;{0\1\0};0) halini alır. Burada 1 değerini dizi içinde ararız. 1 değeri dizinin 2. sırasında olduğu için formül sonucu 2 yi buluruz.

Son olarak bulduğumuz 2 değerini indis bölümünün içine yerleştirirsek;

=İNDİS(Sheet2!$A$1:$A$3;2) bu bölümde A1:A3 aralığındaki 2. satırdaki yani A2 deki "istanbul" değerini bize verir ve istediğimiz sonuca ulaşmız oluruz.


Düzeysel olarak formülün mantığı bu şekildedir. Anlaşılmayan bir bölüm olursa açmaya çalışırım.
Hızlı yazımdan dolayı, imla ve cümle düşüklüğü varsa kusuruma bakmayın.

.

 

[levoni]

Ömer Bey merhaba,
Elinize sağlık valla benimde çok işime yaradı.Tamda böyle bir şeye ihtiyacım vardı.
eksik olmayın