Özet: Bu öğreticide, bir listedeki bir öğenin son tekrarını bulmak için bir Excel formülünün nasıl kullanılacağını öğreneceksiniz.
Geçenlerde bir toplantı gündemi üzerinde çalışıyordum.
Excel'de, insanların bir listesini ve "başkanlarla görüştükleri" tarihleri içeren bir listem var.
Ayrıca, listedeki tekrarlar nedeniyle bir kişinin en son ne zaman "toplantı başkanı" olduğunu bilmem gerekiyor (bu, bir kişinin birden çok kez toplantı başkanı olduğu anlamına gelir).
Bunun nedeni, en son başkanın yeniden atanmadığından emin olmam gerektiğidir.
Bu yüzden işi yapmak için bazı Excel fonksiyon sihrini kullanmaya karar verdim.
Aşağıda nihai sonuç var, açılır menüden bir isim seçebiliyorum ve bu bana o ismin listede en son geçtiği tarihi veriyor.
Excel fonksiyonlarını iyi anlıyorsanız, bunu yapabilecek bir Excel fonksiyonunun olmadığını bilirsiniz.Burada mucizeler yaratıyoruz.
Bu derste, size bunu yapmanın üç yolunu göstereceğim.
İçerik
MAX işlevini kullanarak son oluşumu bulun
Listedeki son değeri döndürecek Excel formülü:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Bu formül şu şekilde çalışır:
- MAX işlevi, eşleşen son adın satır numarasını bulmak için kullanılır.Örneğin, ad Glen ise, 11. satırda olduğu için 11 değerini döndürür.Listemiz ikinci satırdan başladığı için 1 çıkarılıyor.Bu nedenle, Glen'in son oluşumu listemizde 10'dur.
- 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.
ARA işlevini kullanarak son tekrarı bulun
İşte aynı işi yapan başka bir formül:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Bu formül şu şekilde çalışır:
- Arama değeri 2'dir (nedenini göreceksiniz.. okumaya devam edin)
- Arama aralığı 1/($A$2:$A$14=$D$3) şeklindedir - eşleşen bir ad bulduğunda 1, aksi takdirde bir hata döndürür.Böylece bir dizi ile bitirdiniz.例如，查找值為 Glen，數組將為 {#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}。
- Üçüncü parametre ([sonuç_vektör]), sonucu verdiği aralıktır, bu durumda tarih.
Bu formülün çalışmasının nedeni, ARA işlevinin yaklaşık bir eşleştirme tekniği kullanmasıdır.Bu, tam bir eşleşme bulabilirse geri döneceği, ancak bulamazsa tüm diziyi sonuna kadar tarayacağı ve arama değerinden daha küçü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 veya bir hata alırız.Böylece tüm diziyi tarar ve son 1'in konumunu döndürür - bu, ad için son eşleşen değerdir.
Son Oluşumu Bul - Özel İşlevi Kullanma (VBA)
Size başka bir yol da göstereyim.
VBA kullanarak özel işlevler (kullanıcı tanımlı işlevler olarak da bilinir) oluşturabiliriz.
Özel işlevler oluşturmanın yararı, kullanım kolaylığıdır.İşin çoğu VBA arka ucunda gerçekleştiği için her seferinde karmaşık formüller oluşturma konusunda endişelenmenize gerek yok.
Basit bir formül oluşturdum (DÜŞEYARA formülü gibi).
Özel işlevler oluşturmak için VB düzenleyicide VBA koduna ihtiyacınız vardır.Kodu ve onu VB düzenleyiciye alma adımlarını daha sonra vereceğim, ancak önce nasıl çalıştığını göstermeme izin verin:
İşte size sonucu verecek formül:
=SonItemLookup($D$3,$A$2:$B$14,2)
Formül üç parametre alır:
- Değeri bulun (bu, D3 hücresindeki ad olacaktır)
- Aralığı bulun (bu, adı ve tarihi olan aralık olacaktır – A2:B14)
- sütun numarası (bu, sonucunu istediğimiz sütundur)
Formülü oluşturup kodu VB düzenleyicisine yerleştirdikten sonra, diğer normal Excel çalışma sayfası işlevleri gibi kullanabilirsiniz.
İşte formülün kodu:
İşlev LastItemLookup(Dize Olarak Arama Değeri, Aralık Olarak LookupRange, Tamsayı Olarak SütunNumarası) Dim i As Long For i = LookupRange.Columns(1).Cells.Count To 1 Adım -1 Eğer Lookupvalue = LookupRange.Cells(i, 1) ise LastItemLookup = LookupRange.Cells(i, ColumnNumber) Exit Function Son If Next i End Function
Bu kodu VB düzenleyicisine yerleştirme adımları şunlardır:
- Geliştirici sekmesine gidin.
- Visual Basic Seçenekleri'ne tıklayın.Bu, arka uçta VB düzenleyicisini açacaktır.
- VB düzenleyicisinin Proje Gezgini bölmesinde, çalışma kitabında kod eklemek istediğiniz herhangi bir nesneyi sağ tıklayın.Proje Gezgini'ni görmüyorsanız, Görünüm sekmesine gidin ve Proje Gezgini'ne tıklayın.
- Ekle'ye gidin ve Modüller'e tıklayın.Bu, çalışma kitabınız için bir modül nesnesi ekleyecektir.
- Kodu kopyalayıp modül penceresine yapıştırın.
Artık formül, çalışma kitabının tüm sayfalarında mevcut olacak.
Çalışma kitabını makro içerdiğinden .XLSM biçiminde kaydetmeniz gerekeceğ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ınıza kaydedebilir veya ondan bir eklenti oluşturabilirsiniz.
