Başka bir çalışma kitabındaki değerleri nasıl ararsınız veya bulursunuz?

VLOOKUP kullanarak arama referanslarını yapılandırmak, özellikle kaynak dosyanın yolu, adı veya çalışma sayfası sık sık değişiyorsa kafa karıştırıcı olabilir. Böyle durumlarda, VBA kullanarak arama işlemini otomatikleştirmek daha akıcı bir çözüm olabilir; bu sayede kaynak çalışma kitabı kapalı olsa bile değerleri arayabilir, aralık seçimini ve veri döndürmeyi otomatik hale getirebilirsiniz.

Çapraz çalışma kitabı aramaları için VBA kullanmak üzere aşağıdaki adımları izleyin:

1. Microsoft Visual Basic for Applications düzenleyici penceresini açmak için Alt+F11 tuşlarına aynı anda basın.

2.VBA düzenleyicide Ekle>Modülseçeneğine tıklayın, ardından aşağıdaki kodu modül penceresine kopyalayıp yapıştırın:

VBA: Başka bir kapalı çalışma kitabından veri ve Dönüş değeri için VLOOKUP

Option Explicit

' Convert column number to column letter
Private Function GetColumn(ByVal Num As Integer) As String
    If Num <= 26 Then
        GetColumn = Chr(Num + 64)
    Else
        GetColumn = Chr((Num - 1) \ 26 + 64) & _
                    Chr((Num - 1) Mod 26 + 65)
    End If
End Function

Sub FindValue()

    Dim xAddress As String
    Dim xString As String
    Dim xFileName As Variant
    Dim xUserRange As Range
    Dim xRg As Range
    Dim xFCell As Range
    Dim xSourceSh As Worksheet
    Dim xSourceWb As Workbook
    
    On Error Resume Next
    
    ' Get current selection address
    xAddress = Application.ActiveWindow.RangeSelection.Address
    
    ' Ask user to select lookup range
    Set xUserRange = Application.InputBox( _
        Prompt:="Lookup values :", _
        Title:="Kutools for Excel", _
        Default:=xAddress, _
        Type:=8)
    
    If Err.Number <> 0 Then Exit Sub
    On Error GoTo 0
    
    ' Limit selection to used range
    Set xUserRange = Application.Intersect(xUserRange, _
                                           Application.ActiveSheet.UsedRange)
    
    ' Ask user to select source workbook
    xFileName = Application.GetOpenFilename( _
                "Excel Files (*.xlsx), *.xlsx", _
                1, _
                "Select a Workbook")
                
    If xFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    
    ' Open source workbook
    Set xSourceWb = Workbooks.Open(xFileName)
    Set xSourceSh = xSourceWb.Worksheets.Item(1)
    
    ' Build external reference string
    xString = "='" & xSourceWb.Path & Application.PathSeparator & _
              "[" & xSourceWb.Name & "]" & _
              xSourceSh.Name & "'!$"
    
    ' Loop through user range
    For Each xRg In xUserRange
    
        ' Find matching value in source sheet
        Set xFCell = xSourceSh.Cells.Find( _
                        What:=xRg.Value, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False)
        
        ' If found, write formula 2 columns to the right
        If Not xFCell Is Nothing Then
            xRg.Offset(0, 2).Formula = _
                xString & _
                GetColumn(xFCell.Column + 1) & _
                "$" & xFCell.Row
        End If
        
    Next xRg
    
    ' Close source workbook without saving
    xSourceWb.Close False
    
    Application.ScreenUpdating = True

End Sub

Önemli ayrıntılar:

• Kod, arama aralığının iki sütun sağında yer alan sütundan eşleşen değeri döndürür. Örneğin, B sütununu seçerseniz sonuçlar D sütununda görüntülenir.
• Sonucun farklı bir sütunda görünmesini istiyorsanız, xRg.Offset(0,2).Formulaifadesindeki sayıyı başka bir değere değiştirin (örneğin,)1 bir sonraki sütun için, 3 sağa doğru üçüncü sütun için).
• İstendiğinde doğru çalışma kitabını ve çalışma sayfasını seçin; kod her zaman seçilen dosyanın ilk çalışma sayfasını kullanır. Kaynak sayfanız ilk sayfa değilse kodu buna göre ayarlayın.
• Tanımadığınız makroları çalıştırmadan önce dosyanızı her zaman kaydedin. Makrolar yürütüldükten sonra geri alınamaz.

3. Makroyu çalıştırmak için F5 tuşuna basın veya Çalıştır düğmesine tıklayın. "Kutools for Excel" başlıklı bir iletişim kutusu, arama yapılacak hücre aralığını seçmenizi isteyecektir.

4. Aralığınızı seçtikten sonra Tamam düğmesine tıklayın. Ardından, kaynak çalışma kitabını (kapalı olsa bile) seçmeniz için başka bir iletişim kutusu açılacaktır. Uygun dosyayı bulun, seçin ve onaylamak için Aç düğmesine tıklayın.

Makro tamamlandığında, kaynak çalışma kitabındaki ilgili değerler Geçerli Çalışma Sayfası içindeki hedef sütuna döndürülecektir. Bazı değerler eksikse, Arama değeri aralığı içindeki Geçerli Çalışma Sayfası ile Kaynak Veri içindekilerin tam olarak eşleştiğini kontrol edin (tam eşleşme için büyük/küçük harf ve baştaki/Sonundaki Boşluklar önemli rol oynar).

Avantajlar: Kapalı çalışma kitaplarını işler, formüllerde sabitlenmiş dosya yolu kullanımını önler ve kaynak dosyaları anında seçmede esneklik sağlar.
Dikkat Edilmesi Gerekenler: Makroların etkinleştirilmiş olması gerekir; VBA korumalı sayfalarda veya Excel dosyası olmayan dosyalarda çalışmayabilir. Bu yöntemi sık kullanmayı planlıyorsanız, çalışma kitabınızı makro destekli (*.xlsm) olarak kaydedin.

Herhangi bir hatayla karşılaşırsanız, sayfa veya dosya adlarında yazım hatası olmadığından, Aralık seç uygun olduğundan ve Dosya Yolu erişilebilir olduğundan emin olun. Hata ayıklama için VBA düzenleyicide kodu satır satır incelemeyi düşünün.

Klasik VLOOKUP yöntemi ve VBA’nın yanı sıra, Excel’de çapraz çalışma kitabı aramaları yapmak için alternatif yollar da mevcuttur. Bu yöntemler, veri yapınız farklı olduğunda, makrolardan ziyade formülleri tercih ettiğinizde veya sola doğru arama yapmanız, birden fazla kriter kullanmanız gibi daha fazla esneklik gerektiren durumlarda idealdir.

Çalışma Kitapları Arasında INDEX ve MATCH İşlevlerini Kullanma

INDEX ve MATCH kombinasyonuyla, değerleri başka bir çalışma kitabında her yönde—sola, sağa, yukarı veya aşağı—aramanız mümkündür. Bu özellikle, veriyi almak istediğiniz sütunun arama sütununun sağında olmaması durumunda (VLOOKUP’ın bir sınırlaması) son derece kullanışlıdır.

Senaryo: Diyelim ki, meyve adının ilk sütunda olmadığı başka bir açık çalışma kitabından bir fiyat almak istiyorsunuz.

1.Hedef çalışma kitabınızda sonucun görüntülenmesini istediğiniz hücreyi seçin (örneğin C2) ve aşağıdaki formülü girin (çalışma kitabı, sayfa ve aralıkları gerektiği gibi değiştirin):

=INDEX([Price.xlsx]Sheet1!$B$1:$B$24, MATCH(B2, [Price.xlsx]Sheet1!$A$1:$A$24,0))

2.Enter tuşuna basın. Ardından, formülü diğer satırlara ihtiyaç duyduğunuz şekilde kopyalamak veya doldurmak için Dolgu Kulpu’nu sürükleyin.

Parametre açıklaması:

• [Price.xlsx]Sheet1!$B$1:$B$24: Fiyatların saklandığı aralıktır.
• B2: Aranacak meyvenin adıdır.
• [Price.xlsx]Sheet1!$A$1:$A$24: Arama değerinizin bulunacağı aralıktır.
• Formülün sonundaki 0, tam eşleşmeyi garanti eder.

Güçlü Yönler: Sola veya sağa arama yaparken işe yarar; daha esnek düzenlere uygundur.
İpuçları: Formülünüzü güncellemeksizin kaynak dosyaları taşımayın veya yeniden adlandırmayın.

Çapraz Çalışma Kitabı Araması İçin XLOOKUP Kullanımı (Excel 365 ve Sonrası)

Excel 365 veya Excel 2021 kullanıyorsanız, yeni XLOOKUP işlevi çok daha esnektir. Tam eşleşmeleri kolayca bulmanıza, sola doğru aramaları desteklemenize ve eksik değerleri hata vermeden otomatik olarak yönetmenize olanak tanır.

Kullanmak için:
Sonucu görmek istediğiniz hücreye şunu girin:

=XLOOKUP(B2, [Price.xlsx]Sheet1!$A$1:$A$24, [Price.xlsx]Sheet1!$B$1:$B$24, "Not found")

Enter tuşuna basın ve gerektiğinde formülü kopyalayın. Buradaki "Bulunamadı" metni, arama başarısız olduğunda göstermek istediğiniz herhangi bir özel metinle değiştirilebilir.

Avantajlar: VLOOKUP’tan daha esnektir ve kullanımı kolaydır; eski nesil formüllerde sıkça karşılaşılan hataların çoğundan kaçınmanızı sağlar. Ancak XLOOKUP yalnızca yeni Excel sürümlerinde desteklenir.

Çoklu kriter eşleme, birleştirilmiş sayfalarda arama veya çok büyük dosyalarda performans sorunlarından kaçınma gibi karmaşık ihtiyaçlarınız varsa, verilerinizi yapılandırılmış tablolarda düzenlemeyi veya Excel’in Power Query özelliğini kullanarak birden fazla çalışma kitabı arasında veri bağlamayı düşünün.