DÜŞEYARA işlevi, ofis 365'in içeriden öğrenen programına özeldir. ARA işlevi, DÜŞEYARA ve YATAYARA işlevinin birçok zayıflığının üstesinden gelen birçok işlevselliğe sahiptir, ancak ne yazık ki şu anda bizim için mevcut değildir. Ancak endişelenmeyin, MS Excel'de yakında çıkacak olan DÜŞEYARA işleviyle tamamen aynı şekilde çalışan bir DÜŞEYARA işlevi oluşturabiliriz. İşlevleri tek tek ekleyeceğiz.
XLOOKUP işlevinin VBA kodu
Aşağıdaki UDF arama işlevi birçok sorunu çözecektir. Kopyalayın veya aşağıdaki dosyanın altındaki xl eklentisini indirin.
XLOOKUP(lk Varyant Olarak, lCol Aralık Olarak, rCol Aralık Olarak) XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0)) End Function
Açıklama:
Yukarıdaki kod, VBA'da kullanılan yalnızca temel INDEX-MATCH'dir. Bu, yeni bir kullanıcının karşılaştığı birçok şeyi basitleştirir. INDEX-MATCH işlevinin karmaşıklığını çözer ve yalnızca üç bağımsız değişken kullanır. Excel dosyanıza kopyalayabilir veya aşağıdaki .xlam dosyasını indirip Excel eklentisi olarak kurabilirsiniz. Eklenti oluşturmayı ve kullanmayı bilmiyorsanız buraya tıklayın, size yardımcı olacaktır.
XLOOKUP Eklentisi
Excel çalışma sayfasında nasıl çalıştığını görelim.
XLOOKUP sözdizimi
| =XLOOKUP(arama_değeri, arama_dizisi, sonuç_dizisi) |
aranan_değer: Bu, içinde aramak istediğiniz değerdir. arama_dizisi.
arama_dizisi: Aramak istediğiniz tek boyutlu bir aralıktır. aranan_değer.
sonuç_dizisi: Aynı zamanda tek boyutlu bir aralıktır. Bu, değeri almak istediğiniz aralıktır.
Bu XLOOKUP işlevini çalışırken görelim.
XLOOKUP Örnekleri:
Burada, excel'de bir veri tablom var. Bu veri tablosunu kullanarak bazı işlevleri keşfedelim.
İşlevsellik 1. Bire bir aynı Arama değerinin solunda ve sağında arayın.
Excel DÜŞEYARA işlevinin, arama değerinin solundaki değerleri alamadığını bildiğimiz gibi. Bunun için karmaşık INDEX-MATCH kombinasyonunu kullanmanız gerekir. Ama artık değil.
Bazı rulo numaralarının tablosunda bulunan tüm bilgileri almamız gerektiğini varsayarsak. Bu durumda, rulo numarası sütununun solundaki bölgeyi de almanız gerekecektir.
Bu formülü yazın, I2:
| =DÖNÜŞME(H2,$B$2:$B$14,$A$2:$A$14) |
112 numaralı rulo için Kuzey sonucunu alıyoruz. İlgili bölgelerle doldurmak için aşağıdaki hücrelerdeki formülü kopyalayın veya aşağı sürükleyin.
O nasıl çalışır?
Mekanizma basittir. Bu işlev, aranan_değer içinde arama_dizisi ve ilk tam eşleşmenin dizinini döndürür. Ardından, değeri almak için bu dizini kullanır. sonuç_dizisi. Bu işlev, adlandırılmış aralıklarla mükemmel şekilde çalışır.
Benzer şekilde, her sütundan değeri almak için bu formülü kullanın.
İşlevsellik 2. Tam Yatay Arama değerinin üstünde ve altında arama yapın.
XLOOKUP aynı zamanda tam bir HLOOKUP işlevi olarak da çalışır. YATAYARA işlevi, DÜŞEYARA işleviyle aynı sınırlamaya sahiptir. Arama değerinin üstünden değer getiremez. Ancak XLOOKUP, yalnızca HLOOKUP olarak çalışmakla kalmaz, aynı zamanda bu zayıflığın da üstesinden gelir. Nasıl olduğunu görelim.
Varsayımsal olarak, iki kaydı karşılaştırmak istiyorsanız. Zaten sahip olduğunuz arama kaydı. Karşılaştırmak istediğiniz kayıt, aranan_aralığın üzerindedir. Bu durumda bu formülü kullanın.
| =DÖNME(H7,$A$9:$E$9,$A$2:$E$2) |
formülü aşağı sürükleyin ve karşılaştırma satırının tüm kaydına sahip olursunuz.
İşlevsellik 3. Sütun numarasına ve varsayılan tam eşleşmeye gerek yoktur.
DÜŞEYARA işlevini kullandığınızda, değerleri almak istediğiniz sütun numarasını söylemelisiniz. Bunun için sütunları saymanız veya bazı hileler kullanmanız, diğer işlevlerden yardım almanız gerekir. Bu UDF XLOOKUP ile bunu yapmanıza gerek yok.
DÜŞEYARA'yı yalnızca bir sütundan bir değer almak veya sütunda bir değer olup olmadığını kontrol etmek için kullanıyorsanız, bu bana göre en iyi çözümdür.
İşlevsellik 4. INDEX-MATCH, DÜŞEYARA, YATAYARA işlevinin yerini alır
Basit görevler için, XLOOKUP işlevimiz yukarıda belirtilen işlevlerin yerini alır.
XLOOKUP'ın Sınırlamaları:
DÜŞEYARA'nın arama sütununu başlıklarla tanımladığı Dinamik Sütun Dizini ile DÜŞEYARA gibi karmaşık formüller söz konusu olduğunda, bu XLOOKUP başarısız olur.
Diğer bir sınırlama ise, tablodan birden fazla rastgele sütun veya satır aramak zorunda kalırsanız, bu formülü tekrar tekrar yazmak zorunda kalacağınız için bu fonksiyon işe yaramaz olacaktır. Bu, adlandırılmış aralıklar kullanılarak aşılabilir.
Şimdilik yaklaşık işlevi eklemedik, bu nedenle elbette yaklaşık eşleşmeyi alamazsınız. Bunu çok yakında ekleyeceğiz.
DÜŞEYARA işlevi arama değerini bulamazsa, #YOK değil #DEĞER hatası döndürür.
Evet arkadaşlar, excel tablolarındaki değerleri almak, aramak ve doğrulamak için XLOOKUP'ı bu şekilde kullanırsınız. Bu kullanıcı tanımlı işlevi, arama değerinin solunda veya üstünde sorunsuz bir arama için kullanabilirsiniz. Bu işlevle veya EXCEL 2010/2013/2016/2019/365 veya VBA ile ilgili sorguyla ilgili hâlâ herhangi bir şüpheniz veya herhangi bir özel gereksiniminiz varsa, bunu aşağıdaki yorumlar bölümünde sorun. Mutlaka bir cevap alacaksınız.
Dizi Döndürmek için VBA İşlevi Oluştur | Kullanıcı tanımlı işlevden bir dizi döndürmek için, UDF'yi adlandırdığımızda onu bildirmemiz gerekir.
Excel'de Diziler Formül|Excel'de dizilerin ne olduğunu öğrenin.
VBA ile Kullanıcı Tanımlı İşlev Nasıl Oluşturulur | Excel'de kullanıcı tanımlı işlevlerin nasıl oluşturulacağını öğrenin
Microsoft Excel'de VBA kullanarak başka bir çalışma kitabından Kullanıcı Tanımlı İşlev (UDF) Kullanma | Excel'in başka bir çalışma kitabında kullanıcı tanımlı işlevi kullanın
Microsoft Excel'de VBA kullanarak kullanıcı tanımlı işlevlerden hata değerleri döndürme | Kullanıcı tanımlı bir işlevden nasıl hata değerleri döndürebileceğinizi öğrenin
Popüler Makaleler:
Excel Sayfasını VBA Kullanarak Sütuna Dayalı Birden Çok Dosyaya Böl | Bu VBA kodu, belirli bir sütundaki benzersiz değerlere dayalı excel sayfası tabanını böler. Çalışan dosyayı indirin.
Microsoft Excel 2016'da VBA Kullanarak Uyarı Mesajlarını Kapatın | Çalışan VBA kodunu kesintiye uğratan uyarı mesajlarını kapatmak için Application sınıfını kullanıyoruz.
Microsoft Excel 2016'da VBA Kullanarak Yeni Çalışma Kitabı Ekleme ve Kaydetme | VBA kullanarak çalışma kitapları eklemek ve kaydetmek için Workbooks sınıfını kullanıyoruz. Workbooks.Add yeni çalışma kitabını kolayca ekler, ancak…