كيف تبحث عن قيمة وتُرجع جميع القيم المطابقة لها في Excel؟
يتحدث هذا البرنامج التعليمي عن البحث عن قيمة وإرجاع قيم متعددة مطابقة لها في Excel كما هو موضح في لقطات الشاشة أدناه:
البحث عن قيمة وإرجاع قيم متعددة مطابقة لها باستخدام صيغة صفيفية
البحث عن قيمة وإرجاع قيم متعددة مطابقة لها باستخدام التصفية
البحث عن قيمة وإرجاع قيم متعددة مطابقة لها باستخدام دالة معرّفة
البحث عن قيمة وإرجاع قيم متعددة مطابقة لها باستخدام صيغة صفيفية
هناك صيغة صفيفية طويلة تساعدك في البحث عن قيمة وإرجاع جميع القيم المطابقة لها.
1. أدخل القيمة التي تريد البحث عنها في خلية فارغة، كما هو موضح في لقطة الشاشة:
2. في الخلية المجاورة، اكتب هذه الصيغة=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),«»,)
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2))فيها، ثم اضغط على مفاتيحShift + Ctrl + Enterمعًا، ثم اسحب مقبضالتعبئة التلقائيةلأسفل حتى تظهر أول خلية فارغة. انظر لقطة الشاشة:
ملاحظة: في الصيغة أعلاه، يشير $A$1:$B$7 إلى نطاق البيانات، ويشير $A$1:$A$7 إلى العمود الذي تبحث فيه عن القيمة المحددة، وتشير الخلية $D$4 إلى المكان الذي أدخلت فيه قيمة البحث في الخطوة 1، بينما يشير الرقم 2 إلى العمود الثاني الذي سيتم استخراج القيمة المطابقة منه.
البحث عن قيمة وإرجاع قيم متعددة مطابقة لها باستخدام التصفية
في Excel، يمكنك أيضًا الاستفادة من ميزة التصفية لحل هذه المشكلة بسهولة.
1. حدد نطاق العمود الذي تريد البحث عن قيمة فيه، ثم انقر علىبيانات > تصفية. انظر لقطة الشاشة:
2. بعد ذلك، انقر على زر السهم في الخلية الأولى من نطاق التحديد، واختر القيمة التي تريد البحث عنها من القائمة المنسدلة. راجع لقطة الشاشة:
3. انقر علىموافق، وسترى الآن أن العرض قد تم تصفية ليشمل القيمة المبحوث عنها والقيم المطابقة لها فقط.
البحث عن قيمة وإرجاع قيم متعددة مطابقة لها باستخدام دالة معرّفة
إذا كنت مهتمًّا بالدوال المعرَّفة، فيمكنك أيضًا حل المشكلة باستخدام دالة معرَّفة.
1. اضغط على مفاتيحAlt + F11 لفتح نافذةمايكروسوفت فيجوال بيسك للتطبيقات.
2. انقر علىإدراج > وحدة البرمجةلإدراج نافذةوحدة البرمجة، ثم انسخ كود VBA أدناه إلى النافذة.
VBA: ابحث عن قيمة وأعد جميع القيم المطابقة لها.
Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
If pWorkRng = pRng.Cells(i, 1) Then
arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
ReDim Preserve arr(UBound(arr) + 1)
End If
Next
If pType = "h" Then
xCol = Range(Application.Caller.Address).Columns.Count
For i = UBound(arr) To xCol
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function
3. أغلق هذه النافذة، ثم اكتب الصيغة التالية في الخلية: =MyVlookup(A10,$A$2:$B$7,2) (حيث يشير A10 إلى قيمة البحث، ويشير $A$2:$B$7 إلى نطاق البيانات، والرقم 2 يشير إلى رقم فهرس العمود). بعد ذلك، اضغط على مفاتيحShift + Ctrl + Enter، ثم اسحب مقبض التعبئة لأسفل عبر الخلايا. بعد ذلك، ضع المؤشر فيشريط الصيغةواضغط مرة أخرى على مفاتيحShift + Ctrl + Enter.
تلميح: إذا كنت تريد إرجاع القيم في خلايا أفقية، فاكتب هذه الصيغة: =MyVlookup(A10, $A$2:$B$7, 2, «h»).
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل