1. Amaç

Bu konuda daha önce bana gelen bir sorunun cevabını vermek istiyorum. Site kurulduğundan bu yana takip eden Ahmet Bey’den geldi bu soru. Farklı metinlerden oluşan bir listede belirli bazı metinlerin yer alıp almadığını, var ise hangi konumda olduklarını ve bu konumdan sonraki belirli adetteki karakteri farklı bir hücreye aktarmak için bir formüle ihtiyaç duyuyordu.

Buradaki iki önemli konu birden fazla aranan değer mevcut. İkincisi ise bu değerler metin içinde kaçıncı sırada bulunuyor.

Aranan değer ise toplamda 11 haneden oluşan bir rakam. Ancak metin içinde yer aldığında önce bunların farklı bir hücreye alınması ve sonradan rakam olarak ele alınması gerekiyor.

Basit bir tablo hazırlayalım:

TarihAçıklamaAranan Rakam
03/05/22Mehmet Saygın (11100001225) Numaralı Gönderi 
03/05/22Hasan Tüfekçi Gönderisi (11100225251) 
04/05/22Zeynep Çakır – No: 222007819523 
04/05/22Ali Zeytin 04/05/22 Gönderisi Numara 33300064911 
05/05/22Gönderi 22208825479 Şener Konur 

Tabloyu B2 hücresinden itibaren oluşturdum. Arama yapacağım metin C3 hücresinden itibaren başlıyor. Formülleri de buna göre yazdığımı belirteyim.
Tabloda göreceğiniz üzere rakamların metin içindeki konumu farklı, bazıları parantez içinde, metinlerin ilk 3 numarası birbirinden farklı gibi özel bir şart aramamıza izin vermeyecek bir durum mevcut.
Bize lazım olan rakam 11 hane ve ilk 3 hanesi 999-888-777 gibi sabit rakamlardan oluşuyor. Şimdi biz bunu dikkate alarak bir formül geliştirmemiz gerek.

Ahmet Bey’in sorusunda buradaki aranacak rakamın ilk 3 hanesi sadece 2 rakamdan oluşuyordu. Bu Ahmet Bey’in bulduğu yöntem ve benim ilk yazacağım formül için önemli.

Ben burada 111-222-333 olarak 3 tane farklı rakam ekledim, daha sonra biraz daha ileri seviye bir uygulama yapmak için bu tarz bir yol izledim.

Bu yazıda kullandığım dosyayı buradan indirebilirsiniz.

2. Ahmet Bey'in Bulduğu Çözüm

Öncelikle Ahmet Bey’in bu sorun için başka kaynaklardan bulduğu formülü bir incelemek istiyorum:

=EĞERHATA(PARÇAAL(E10;BUL(“*”; YERİNEKOY(YERİNEKOY(YERİNEKOY(E10; “111”; “***”); “222”;”***”); “111”;”***”)); 11); “”)

Burada yapılan işlem sırası ile “YERİNEKOY” formülleri ile “111” ve “222” rakamlarını “***” ifadesi ile değiştirmek.

Sonrasında bu yeni metin içinde “*” karakterinin ilk hangi sırada olduğunu bulmak. Ve PARÇAAL işlevi ile orijinal metnin bu konumundan itibaren 11 karakterini çekmek.

Bu formül işimize yarayacaktır ancak gereksiz işlemler mevcut, ilk YERİNEKOY formülü ile son YERİNEKOY formülü teknik olarak aynı işi yapıyor. Ve formül oldukça uzun ve karmaşık gözüküyor.

3. Basit Çözüm

Burada aradığımız değer sadece 2 tane olduğuna dikkat ederek sadeec bu iki değere göre bir formül yazacağız.

Öncelikle aradığımız ilk numarayı metin içinde bulalım, bunun için BUL işlevini kullanacağız:

=BUL(“111”;C3;1)

Bu formül metin içinde 111 değerini bulursa konumunu, bulamazsa hata döndürür. Şimdi de aradığımız ikinci değer için aynı formülü yazalım:

=BUL(“222”;C3;1)

Bu iki formülü EĞERHATA işlevi ile birleştirirsek birinci formülün hata döndürmesi durumunda ikinci formülün kullanılmasını sağlayabiliriz:

=EĞERHATA(BUL(“111”;C3;1); BUL(“222”;C3;1))

Şimdi artık elimizde aradığımız 2 değerin herhangi birinin metin içinde nerede olduğunu gösteren bir rakam var. Bunu PARÇAAL işlevi ile birlikte kullanacağız:

=PARÇAAL(C3; EĞERHATA(BUL(“111”;C3;1); BUL(“222”;C3;1));11)

Şimdi bu formülü yazıp aşağıya doğru uzattığımızda istediğimiz rakamı elde edebiliriz. Ancak benim örneğimde bulmak istediğim rakam sayısı 3. Bu nedenle 4. Satırda hata değeri göreceksiniz.

Biz birden fazla EĞERHATA formülü yazarak bu sorunu çözebiliriz ancak aradığımız değerlerin miktarı değiştikçe biz daha fazla EĞERHATA formülü yazarak daha uzun, daha karmaşık ve güncellemesi daha zor bir formül elde ederiz. Daha basit olarak bir liste içindeki değerleri arayan bir formül yazsak ve yeni değerler bulmamız gerektiğinde sadece bu listeyi güncellesek daha güzel olmaz mı?

4. Listedeki Metinleri Çekme

Ben arama yapmak istediğim rakamları içeren ayrı bir listeyi F2 hücresinden itibaren yapacağım ve bunu bir TABLO haline getireceğim, tabloma da “Arananlar” ismini vereceğim:

Yukarıdaki resimde seçili alanın bir tablo olduğuna ve isim alanında Arananlar yazdığına dikkat edin lütfen. Kullanılan formüllerde bu ayrıntı önemli.

Öncelikle aradığımız değerleri metin içinde bulalım:

=BUL(Arananlar[Aranan];C3;1)

Bu formül bir dizi döndürecektir. Burada elimize (16; #DEĞER; #DEĞER) gibi bir dizi gelecek. Bu dizideki ilk rakamı çekelim, bunun için Excel’in bize sunduğu en esnek işlevlerden biri olan TOPLAMA işlevini kullanabiliriz:

=TOPLAMA(14;3;BUL(Arananlar[Aranan];$C3;1);1)

Buradaki formülde dikkat edeceğiniz konu be ilk parametre olarak 14 ve formülün en sonunda da 1 değerini kullandım. Bu sayede formül bana listedeki en büyük rakamı döndürecek. Listede aranan metinden sadece 1 tane varsa sorun olmayacaktır ancak birden fazla yerde bu metin var ise soruna yol açabilir.

Artık elimizde aradığımız değerin hangi sırada olduğunu gösteren bir rakam var. Şimdi PARÇAAL ile bu sıradan sonraki 11 rakamı çekelim:

=PARÇAAL(C3;TOPLAMA(14;3;BUL(Arananlar[Aranan];$C3;1);1);11)

Artık elimizde tam olarak istediğimiz rakam var.

Şimdi diyelim ki bize yeni bir liste geldi ve bulmak istediğim rakamlar listesine “444” değerini eklemem gerekti. Tek yapmam gereken aranan listesini güncellemek olacaktır.

Gördüğünüz gibi oldukça esnek ve yeni gelişecek şartlara göre hızlıca güncellenebilecek bir formül elde ettik. Bu formülün tek bir sıkıntısı mevcut. Aradığınız ilk 3 karakterin arama yaptığınız metin içinde birden fazla noktada yer almadığından emin olmanız gerekiyor. Misal 444 değeri eğer metin içinde 2 yerde geçiyor ise bu formül metnin bulunduğu sıranın büyük olanını döndürecektir.

İlginizi Çekebilecek Diğer Yazılar
Etiketler: , , , ,

Leave a Reply