Excel'de Bir Listede Arama Değerinin Son Oluşumunu Bulun

Bu öğreticide, Excel formüllerini kullanarak bir listedeki bir öğenin son tekrarını nasıl bulacağınızı öğreneceksiniz.

Geçenlerde bir toplantının gündemini belirlemeye çalışıyordum.

Excel'de bir listem vardı ve burada bir kişi listesi ve 'Toplantı Başkanı' olarak görev yaptıkları tarihler vardı.

Listede tekrarlar olduğu için (bir kişinin birden çok kez Toplantı Başkanı olduğu anlamına gelir), bir kişinin en son ne zaman 'Toplantı Başkanı' olduğunu da bilmem gerekiyordu.

Bunun nedeni, yakın zamanda başkanlık yapan birinin tekrar atanmamasını sağlamam gerektiğiydi.

Bu yüzden bunu yapmak için bazı Excel İşlev sihrini kullanmaya karar verdim.

Aşağıda, açılır listeden bir isim seçebildiğim nihai sonuç var ve bu bana o ismin listede en son geçtiği tarihi veriyor.

Excel İşlevlerini iyi anlıyorsanız, bunu yapabilecek tek bir Excel işlevi olmadığını bilirsiniz.

Ama Formula Hack bölümündesiniz ve burada sihrin gerçekleşmesini sağlıyoruz.

Bu derste, size bunu yapmanın üç yolunu göstereceğim.

Son Oluşumu Bul - MAX işlevini kullanma

Bu tekniğin kredisi, Excel MVP Charley Kyd'in bir makalesine gider.

İşte listeden son değeri döndürecek Excel formülü:

=INDEX($B$2:$B$14,SUMPRODUCT(MAKS(SATIR($A$2:$A$14)*($D$3=$A$2:$A$14))-1))

İşte bu formül nasıl çalışır:

• MAX işlevi, eşleşen son adın satır numarasını bulmak için kullanılır. Örneğin, isim Glen ise, 11 satırda olduğu gibi 11'i döndürür. Listemiz ikinci satırdan itibaren başladığı için 1 çıkarılmıştır. Yani Glen'in son oluşumunun konumu listemizde 10.
• SUMPRODUCT, dizi formüllerini işleyebildiğinden, Control + Shift + Enter tuşlarını kullanmanız gerekmediğinden emin olmak için kullanılır.
• INDEX işlevi artık son eşleşen adın tarihini bulmak için kullanılıyor.

Son Oluşumu Bul - ARA işlevini kullanma

Aynı işi yapmak için başka bir formül:

=ARA(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)

İşte bu formül nasıl çalışır:

• Arama değeri 2'dir (nedenini göreceksiniz… okumaya devam edin)
• Arama aralığı 1/($A$2:$A$14=$D$3) - Bu, eşleşen adı bulduğunda 1, bulamadığı zaman bir hata döndürür. Böylece bir dizi elde edersiniz. Örneğin, arama değeri Glen ise, dizi {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ olacaktır. 0!;#SAYI/0!;#BÖL/0!;1;#BÖL/0!;#BÖL/0!;#BÖL/0!}.
• Üçüncü argüman ([sonuç_vektör]), bu durumda tarihler olan sonucu verdiği aralıktır.

Bu formülün çalışmasının nedeni, ARA işlevinin yaklaşık eşleştirme tekniğini kullanmasıdır. Bu, tam eşleşen değeri bulabilirse bunu döndüreceği, ancak bulamazsa tüm diziyi sonuna kadar tarayacağı ve arama değerinden daha düşük olan bir sonraki en büyük değeri döndüreceği anlamına gelir.

Bu durumda, arama değeri 2'dir ve dizimizde sadece 1'ler veya hatalar alacağız. Böylece tüm diziyi tarar ve ismin son eşleşen değeri olan son 1'in konumunu döndürür.

Son Oluşumu Bulun - Özel İşlevi (VBA) Kullanma

Size bunu yapmanın başka bir yolunu da göstereyim.

VBA kullanarak özel bir işlev (Kullanıcı Tanımlı İşlev olarak da adlandırılır) oluşturabiliriz.

Özel bir işlev oluşturmanın yararı, kullanımının kolay olmasıdır. İşin çoğu VBA arka ucunda gerçekleştiğinden, her seferinde karmaşık bir formül oluşturma konusunda endişelenmenize gerek yok.

Basit bir formül oluşturdum (bu DÜŞEYARA formülüne çok benziyor).

Özel bir işlev oluşturmak için VB Editöründe VBA koduna sahip olmanız gerekir. Kodu ve VB Editörüne yerleştirme adımlarını birazdan vereceğim ama önce nasıl çalıştığını göstereyim:

Size sonucu verecek formül şudur:

=LastItemLookup($D$3,$A$2:$B$14,2)

Formül üç argüman alır:

• Arama Değeri (bu, D3 hücresindeki ad olacaktır)
• Arama Aralığı (bu, adları ve tarihleri ​​olan aralık olacaktır - A2:B14)
• Sütun Numarası (sonucunu istediğimiz sütun budur)

Formülü oluşturup kodu VB Editor'a yerleştirdikten sonra, onu diğer normal Excel çalışma sayfası işlevleri gibi kullanabilirsiniz.

İşte formülün kodu:

'Bu, bir arama değerinin son tekrarını bulan ve belirtilen sütundan karşılık gelen değeri döndüren bir işlev için bir koddur 'Code by Sumit Bansal (https://trumpexcel.com) Function LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Adım -1 Eğer Lookupvalue = LookupRange.Cells(i, 1) O zaman LastItemLookup = LookupRange.Cells(i, ColumnNumber) Çıkış Fonksiyonu Bitir Sonraki İse Bitir Fonksiyonu Bitir

Bu kodu VB Editörüne yerleştirme adımları şunlardır:

1. Geliştirici sekmesine gidin.
2. Visual Basic seçeneğine tıklayın. Bu, arka uçta VB düzenleyicisini açacaktır.
3. VB Düzenleyicisi'ndeki Proje Gezgini bölmesinde, kodu eklemek istediğiniz çalışma kitabı için herhangi bir nesneye sağ tıklayın. Proje Gezgini'ni görmüyorsanız, Görünüm sekmesine gidin ve Proje Gezgini'ne tıklayın.
4. Ekle'ye gidin ve Modül'e tıklayın. Bu, çalışma kitabınız için bir modül nesnesi ekleyecektir.
5. Modül penceresindeki kodu kopyalayıp yapıştırın.

Artık formül, çalışma kitabının tüm çalışma sayfasında mevcut olacaktır.

Çalışma kitabını, içinde bir makro olduğu için .XLSM biçiminde kaydetmeniz gerektiğini unutmayın. Ayrıca, bu formülün kullandığınız tüm çalışma kitaplarında olmasını istiyorsanız, Kişisel Makro Çalışma Kitabına kaydedebilir veya ondan bir eklenti oluşturabilirsiniz.