كيف يمكنني البحث عن القيم في مصنف آخر؟

قد يكون إعداد مراجع البحث باستخدام VLOOKUP أمرًا محيرًا، خاصةً إذا كنت تغيّر مسار ملف المصدر أو اسمه أو الورقة بشكل متكرر. في مثل هذه السيناريوهات، يُعدّ أتمتة عملية البحث باستخدام VBA حلاً أكثر سلاسة، إذ يتيح لك البحث عن القيم حتى عندما يكون ملف المصدر مغلقًا، مع تحديد النطاقات وإرجاع البيانات تلقائيًا.

اتبع الخطوات التالية لاستخدام VBA في عمليات البحث عبر ملفات العمل:

1. اضغطAlt+F11 في نفس الوقت لفتح نافذة محررMicrosoft Visual Basic for Applications.

2.في محرر VBA، انقر فوقInsert>Module، ثم انسخ والصق الكود التالي في نافذة الوحدة:

VBA: البحث عن بيانات باستخدام 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

تفاصيل مهمة:

• يُعيد الكود القيمة المطابقة من عمود يقع على بُعد عمودين من نطاق البحث. فعلى سبيل المثال، إذا اخترت العمود B، فستظهر النتائج في العمود D.
• إذا أردت عرض النتيجة في عمود مختلف، غيّر الرقم2 فيxRg.Offset(0,2).Formula إلى قيمة أخرى (مثل: 1 للعمود التالي، أو3 للعمود الثالث إلى اليمين).
• اختر ملف العمل والورقة الصحيحة عند المطالبة بذلك؛ إذ يستخدم الكود دائمًا الورقة الأولى في الملف المحدد. قم بتعديل الكود إذا لم تكن ورقة المصدر هي الأولى.
• احفظ ملفك دائمًا قبل تشغيل ماكرو جديد، إذ لا يمكن التراجع عن الماكرو بعد تنفيذه.

3. نفّذ الماكرو بالضغط على مفتاحF5 أو بالنقر على زرRun. ستظهر نافذة حوار بعنوان «Kutools لـ Excel» وتطلب منك تحديد نطاق الخلايا التي تريد البحث عن قيمها.

4.بعد تحديد النطاق، انقر فوقOK. بعد ذلك بقليل، ستظهر نافذة حوار أخرى تطلب منك اختيار ملف المصدر (حتى لو كان مغلقًا). تصفّح واختر الملف المناسب، ثم انقر فوقOpen للتأكيد.

عند اكتمال الماكرو، سيتم إرجاع القيم المقابلة من ملف المصدر إلى العمود المستهدف في ورقة العمل الحالية. إذا كانت بعض القيم مفقودة، فتأكد من أن نطاق قيمة البحث في ورقة العمل الحالية يتطابق تمامًا مع نظيره في البيانات الأصلية—فالأحرف الكبيرة والصغيرة والمسافات البادئة أو الخلفية تلعب دورًا حاسمًا لتحقيق تطابق دقيق!

المزايا:يتعامل مع ملفات العمل المغلقة، ويتجنب تضمين مسار الملف مباشرةً في الصيغ، ويمنحك مرونة في اختيار ملفات المصدر أثناء التشغيل.
اعتبارات:يجب تمكين الماكروات؛ وقد لا يعمل VBA على الأوراق المحمية أو مع ملفات Excel غير المتوافقة. احفظ ملفك بتنسيق يدعم الماكرو (*.xlsm) إذا كنت تنوي استخدام هذه الطريقة بشكل متكرر.

إذا واجهت أي أخطاء، فتأكد من خلو أسماء الأوراق والملفات من الأخطاء الإملائية، ومن أن يكون نطاق التحديد مناسبًا، وأن يكون مسار الملف متاحًا. ولأغراض التصحيح، ننصحك بتنفيذ الكود سطرًا بسطر باستخدام محرر VBA.

بالإضافة إلى طريقة VLOOKUP التقليدية وVBA، توجد طرق بديلة لأداء عمليات البحث عبر ملفات العمل في Excel. وقد تكون هذه الطرق أكثر فعالية في حالات معينة—مثل اختلاف هيكل بياناتك، أو تفضيلك استخدام الصيغ بدل الماكروات، أو حاجتك إلى مزيد من المرونة (كالبحث يسارًا أو تطبيق معايير متعددة).

استخدام دالتي INDEX وMATCH عبر ملفات العمل

باستخدام دالتي INDEX وMATCH معًا، يمكنك البحث عن القيم في أي اتجاه—يسارًا، يمينًا، لأعلى، أو لأسفل—حتى في ملف عمل آخر. وهذا مفيد جدًّا عندما لا يكون العمود الذي تريد استخراج البيانات منه موجودًا على يمين عمود البحث، وهو قيدٌ تفرضه دالة VLOOKUP.

السيناريو:لنفترض أنك تريد استرجاع سعرٍ من ملف عمل مفتوح آخر، وقد لا يكون اسم الفاكهة موجودًا في العمود الأول.

1.في ملف العمل الوجهة، حدد الخلية التي تريد عرض النتيجة فيها (مثل C2)، وأدخل الصيغة أدناه (استبدل اسم الملف والورقة والنطاق حسب الحاجة):

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

2. اضغطEnter، ثم انقل الصيغة أو أدخلها في الصفوف الأخرى حسب الحاجة بسحب مقبض الملء.

شرح المعلمات:

• [Price.xlsx]Sheet1!$B$1:$B$24: النطاق المُخزَّن فيه الأسعار.
• B2: اسم الفاكهة التي تريد البحث عنها.
• [Price.xlsx]Sheet1!$A$1:$A$24: النطاق الذي سيتم فيه البحث عن قيمة البحث الخاصة بك.
• إن0في النهاية يضمن تطابقًا دقيقًا.

المزايا:تتيح لك البحث يسارًا أو يمينًا، وتتكيّف بسلاسة مع تخطيطات أكثر مرونة.
نصائح:تجنّب نقل ملفات المصدر أو إعادة تسميتها دون تحديث صيغتك أولًا.

استخدام XLOOKUP للبحث عبر ملفات العمل (Excel 365 والإصدارات الأحدث)

إذا كنت تستخدم Excel 365 أو Excel 2021، فإن دالة XLOOKUP الجديدة تمنحك مرونة أكبر: فهي تتيح لك العثور بسهولة على تطابقات دقيقة، وتدعم عمليات البحث إلى اليسار، وتتعامل تلقائيًا مع القيم المفقودة دون عرض أخطاء.

لاستخدامها:
في الخلية التي تريد ظهور النتيجة فيها، أدخل:

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

اضغطEnterوانسخ الصيغة حسب الحاجة. هنا، يمكنك تغيير «Not found» إلى أي نص مخصص ترغب في عرضه إذا فشل البحث.

المزايا:أكثر مرونة من VLOOKUP وأسهل في الإدارة، كما تساعدك على تجنب العديد من الأخطاء الشائعة في الصيغ القديمة. ومع ذلك، فهي متاحة فقط في إصدارات Excel الأحدث.

لمتطلبات معقدة مثل المطابقة متعددة المعايير، أو البحث في أوراق مدمجة، أو تجنُّب مشكلات الأداء في الملفات الكبيرة جدًّا، ننصحك بتنظيم البيانات الأصلية في جداول منظمة أو باستخدام أداة Power Query في Excel لربط البيانات بين ملفات العمل المتعددة بكفاءة.