كيفية البحث بسرعة عن قيمة في أوراق عمل متعددة أو مصنفات؟

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

البحث عن قيمة في أوراق عمل متعددة لمصنف باستخدام وظيفة البحث والاستبدال

البحث عن قيمة في جميع المصنفات الموجودة في مجلد باستخدام VBA

البحث بسرعة عن قيمة عبر عدة مصنفات مفتوحة باستخدام Kutools لـ Excel

البحث عن قيمة عبر أوراق عمل متعددة باستخدام صيغ Excel

البحث عن قيمة في أوراق عمل متعددة لمصنف باستخدام وظيفة البحث والاستبدال

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

1. لبدء البحث، حدد علامات أوراق العمل التي تريد تضمينها في بحثك بالضغط باستمرار على مفتاح Ctrl والنقر على كل ورقة عمل في شريط علامات الورقة بشكل فردي. هذا يضمن أن البحث سيتم تطبيقه على جميع الأوراق المحددة في وقت واحد. انظر الصورة:

2. بمجرد تحديد الأوراق المطلوبة، اضغط على Ctrl + F لفتح مربع الحوار البحث والاستبدال. اكتب القيمة التي تريد العثور عليها في مربع النص البحث عن تحت تبويب البحث، ثم انقر على زر البحث عن الكل. سيقوم Excel على الفور بعرض قائمة بجميع الخلايا في الأوراق المحددة التي تحتوي على قيمة البحث الخاصة بك، إلى جانب مواقعها. انظر الصورة:

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

احتياطات: هذه الطريقة لا تبحث عبر المصنفات المغلقة أو المخفية ولا تقوم بتمييز الخلايا تلقائيًا - بل ستُظهر فقط النتائج للملاحة.

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

العثور على القيم واستبدالها بسهولة عبر أوراق عمل متعددة ومصنفات

يقدم خيار البحث والاستبدال المتقدم في Kutools لـ Excel وسيلة فعالة للبحث عن القيم واستبدالها عبر أوراق عمل متعددة أو حتى عبر جميع المصنفات المفتوحة. باستخدام هذه الميزة المتقدمة، يمكنك توفير وقت الحفظ والقضاء على الأخطاء عند العمل مع مجموعات بيانات كبيرة، مما يجعل مهام Excel الخاصة بك أسرع وأكثر دقة.

Kutools لـ Excel - قم بتعزيز Excel بأكثر من 300 أدوات أساسية. استمتع بميزات ذكاء اصطناعي مجانية بشكل دائم! احصل عليه الآن

البحث عن قيمة في جميع المصنفات الموجودة في مجلد باستخدام VBA

إذا كنت بحاجة للبحث عن قيمة معينة عبر عدة مصنفات محفوظة في مجلد – بما في ذلك الملفات التي لم تفتحها – أدوات Excel المدمجة لا يمكن أن تقوم بذلك مباشرة. في هذه الحالة، يمكن لـ VBA (Visual Basic for Applications) أن يقوم بأتمتة العملية لك، حيث يفتح كل مصنف في المجلد بطريقة نظامية، ويقوم بمسح جميع الأوراق، ويسجل المواقع التي تم العثور فيها على التطابقات. هذا النهج عملي للغاية للتدقيق الدوري أو للتحقق من القيم في الملفات المؤرشفة أو الدفعات.

1. ابدأ بفتح مصنف جديد (أو فارغ) في Excel. حدد الخلية التي تريد أن تظهر فيها نتائج البحث (عادة الخلية A1). اضغط على Alt + F11 لتشغيل نافذة محرر Microsoft Visual Basic for Applications.

2. داخل محرر VBA، انتقل إلى إدراج > وحدة لإنشاء وحدة جديدة، ثم ألصق الكود التالي لـ VBA في نافذة الوحدة.

VBA: البحث عن قيمة عبر جميع المصنفات الموجودة في مجلد.

Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
    Dim xFso As Object
    Dim xFld As Object
    Dim xStrSearch As String
    Dim xStrPath As String
    Dim xStrFile As String
    Dim xOut As Worksheet
    Dim xWb As Workbook
    Dim xWk As Worksheet
    Dim xRow As Long
    Dim xFound As Range
    Dim xStrAddress As String
    Dim xFileDialog As FileDialog
    Dim xUpdate As Boolean
    Dim xCount As Long
    Dim xAWB As Workbook
    Dim xAWBStrPath As String
    Dim xBol As Boolean
    Set xAWB = ActiveWorkbook
    xAWBStrPath = xAWB.Path & "\" & xAWB.Name
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xStrSearch = "KTE"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = Worksheets.Add
    xRow = 1
    With xOut
        .Cells(xRow, 1) = "Workbook"
        .Cells(xRow, 2) = "Worksheet"
        .Cells(xRow, 3) = "Cell"
        .Cells(xRow, 4) = "Text in Cell"
        Set xFso = CreateObject("Scripting.FileSystemObject")
        Set xFld = xFso.GetFolder(xStrPath)
        xStrFile = Dir(xStrPath & "\*.xls*")
        Do While xStrFile <> ""
            xBol = False
            If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
                xBol = True
                Set xWb = xAWB
            Else
                Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
            End If
            For Each xWk In xWb.Worksheets
                If xBol And (xWk.Name = .Name) Then
                Else
                Set xFound = xWk.UsedRange.Find(xStrSearch)
                If Not xFound Is Nothing Then
                    xStrAddress = xFound.Address
                End If
                Do
                    If xFound Is Nothing Then
                        Exit Do
                    Else
                        xCount = xCount + 1
                        xRow = xRow + 1
                        .Cells(xRow, 1) = xWb.Name
                        .Cells(xRow, 2) = xWk.Name
                        .Cells(xRow, 3) = xFound.Address
                        .Cells(xRow, 4) = xFound.Value
                    End If
                    Set xFound = xWk.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
                End If
            Next
            If Not xBol Then
            xWb.Close (False)
            End If
            xStrFile = Dir
        Loop
        .Columns("A:D").EntireColumn.AutoFit
    End With
    MsgBox xCount & " cells have been found", , "Kutools for Excel"
ExitHandler:
    Set xOut = Nothing
    Set xWk = Nothing
    Set xWb = Nothing
    Set xFld = Nothing
    Set xFso = Nothing
    Application.ScreenUpdating = xUpdate
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
End Sub

3. اضغط على مفتاح F5 أو انقر على زر التشغيل لتنفيذ هذا الماكرو. ستظهر نافذة حوار اختيار مجلد، مما يسمح لك باختيار المجلد الذي يحتوي على المصنفات التي تريد البحث فيها. انظر الصورة:

4. انقر موافق. بعد انتهاء البحث، ستظهر نافذة رسالة لتبلغك بإجمالي عدد الخلايا التي تم العثور عليها والتي تحتوي على القيمة المحددة. انظر الصورة:

5. انقر موافق لإغلاق الرسالة. يتم سرد جميع المواقع التي تم العثور فيها على القيمة في ورقة عمل جديدة، بما في ذلك اسم المصنف، اسم ورقة العمل، مرجع الخلية، ومحتوى الخلية الدقيق لمراجعتك.

نصيحة: المصطلح الحالي للبحث محدد في VBA كـ “KTE”. يمكنك تخصيص هذا عن طريق تغيير السطر xStrSearch = "KTE" في الكود لأي قيمة ترغب في البحث عنها.

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

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

البحث بسرعة عن قيمة في عدة مصنفات مفتوحة باستخدام Kutools لـ Excel

عندما تريد تنفيذ عملية بحث عبر عدة مصنفات مفتوحة بالفعل في جلسة Excel الخاصة بك، يوفر Kutools لـ Excel لوحة بحث واستبدال مخصصة تجعل العملية أسهل وأكثر تنظيمًا. هذا مفيد بشكل خاص للبحث السريع في مصنفاتك دون الحاجة إلى كتابة البرامج النصية أو إعداد معقد، وهو مثالي للمستخدمين الذين يعملون غالبًا مع ملفات متعددة في نفس الوقت وبحاجة إلى أداة سهلة الاستخدام وبديهية لإدارة عمليات البحث.

1. في أي من المصنفات المفتوحة لديك، انتقل إلى Kutools التبويب واختر التنقل. ثم انقر على الزر البحث والاستبدال   لإظهار لوحة البحث والاستبدال ، عادة ما تكون موجودة في الجانب الأيسر من نافذة Excel الخاصة بك. انظر الصورة:

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

نصيحة: الأداة المتقدمة للبحث والاستبدال في Kutools لـ Excel تتيح لك البحث والاستبدال في البيانات ليس فقط في جميع المصنفات المفتوحة، ولكن أيضًا في الأوراق المحددة، المصنف النشط، ورقة العمل الحالية، أو حتى في التحديد الحالي فقط. هذا يمنحك تحكمًا مخصصًا بناءً على احتياجاتك.

احتياطات: تأكد من فتح جميع المصنفات التي تريد البحث فيها قبل بدء البحث، لأن هذه الأداة لا يمكنها البحث في الملفات التي ليست مفتوحة حاليًا في Excel.

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

عرض توضيحي: البحث عن قيمة عبر مصنفات متعددة مفتوحة باستخدام Kutools لـ Excel

البحث عن قيمة عبر أوراق عمل متعددة باستخدام صيغ Excel

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

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

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

مثال: لنفترض أن لديك ثلاث أوراق تُدعى Sheet1 وSheet2 وSheet3. تريد أن تعرف في أي ورقة (أو الأوراق) تظهر قيمة معينة، مثل "Invoice123"، في الخلية A1، أو ببساطة للتحقق مما إذا كانت القيمة موجودة في أي مكان في هذه الأوراق.

الخطوة 1. افترض أن لديك قائمة بأسماء الأوراق في D2:D4 (D2: Sheet1، D3: Sheet2، D4: Sheet3). أدخل القيمة التي تريد البحث عنها (مثل "Invoice123") في E1. ثم، في F2، أدخل هذه الصيغة:

=IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"), $E$1) >0, "Found", "Not Found")

الخطوة 2. اسحب الصيغة من F2 إلى F4 للتحقق من جميع الأوراق المدرجة في D2:D4. ستعيد "تم العثور" أو "لم يتم العثور" لكل ورقة.

كيف تعمل: تستخدم الصيغة INDIRECT لإنشاء مرجع لكل ورقة مدرجة وCOUNTIF للتحقق مما إذا كانت القيمة في E1 تظهر في العمود A لكل ورقة. قم بتعديل النطاق A:A لاستهداف عمود آخر أو نطاق محدد (مثل A1:Z100) إذا لزم الأمر.

نصيحة إضافية: لاسترداد أسماء الأوراق التي تحتوي على القيمة، استخدم الصيغة المصفوفة التالية (أدخلها باستخدام Ctrl + Shift + Enter في إصدارات Excel القديمة أو كصيغة عادية في Microsoft 365/Excel 2021+):

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"), $E$1)>0, D2:D4, ""))

ستعيد هذه الصيغة قائمة مفصولة بفواصل لجميع أسماء الأوراق التي تم العثور فيها على القيمة. كن حذرًا مع INDIRECT – فهو يعمل فقط مع المصنفات المفتوحة ولا يسمح بالبحث في الملفات المغلقة.

احتياطات: إذا تم تغيير أسماء الأوراق أو حذفها، ستُرجع الصيغة خطأ #REF!؛ دائمًا تحقق من أن قائمة أسماء الأوراق صحيحة. بالنسبة للمصنفات الكبيرة، قد تبطئ الصيغ المستندة إلى INDIRECT أداء المصنف.

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

مقالات ذات صلة:

• كيفية العثور بسرعة واستبدال عبر أوراق عمل متعددة أو المصنف بأكمله؟
• كيفية العثور بسرعة واستبدال في عدة ملفات Excel مفتوحة؟
• كيفية العثور على أكبر قيمة سالبة (أقل من 0) في Excel؟
• كيفية استخراج جميع التكرارات من عمود في Excel؟