كيفية البحث عن عدة قيم متطابقة في Excel وتسلسلها؟
كما نعلم جميعًا ، فإن VLOOKUP يمكن أن تساعدنا الوظيفة في Excel في البحث عن قيمة وإرجاع البيانات المقابلة في عمود آخر ، ولكن بشكل عام ، يمكنها فقط الحصول على القيمة النسبية الأولى إذا كانت هناك عدة بيانات مطابقة. في هذه المقالة ، سأتحدث عن كيفية البحث عن عدة قيم مقابلة وتسلسلها في خلية واحدة فقط أو قائمة عمودية.
Vlookup وإرجاع قيم مطابقة متعددة عموديًا مع الصيغة
Vlookup وسلسلة قيم مطابقة متعددة في خلية بوظيفة محددة بواسطة المستخدم
Vlookup وسلسلة قيم مطابقة متعددة في خلية باستخدام Kutools for Excel
Vlookup وإرجاع قيم مطابقة متعددة عموديًا مع الصيغة
لنفترض أن لدي النطاق التالي من البيانات ، للحصول على جميع القيم المقابلة بناءً على قيمة محددة عموديًا كما هو موضح في لقطة الشاشة التالية ، يمكنك تطبيق صيغة صفيف.
1. أدخل هذه الصيغة: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") في خلية فارغة حيث تريد وضع النتيجة ، على سبيل المثال ، E2 ، ثم اضغط على كترل + شيفت + إنتر مفاتيح معًا للحصول على قاعدة القيمة النسبية بناءً على معيار معين ، انظر الصورة:
ملاحظات: في الصيغة أعلاه:
A1: A16 هو نطاق العمود الذي يحتوي على القيمة المحددة التي تريد البحث عنها ؛
D2 يشير إلى القيمة المحددة التي تريد البحث عنها ؛
B1: B16 هو نطاق العمود الذي تريد إرجاع البيانات المقابلة منه ؛
$ 1: $ 16 يشير إلى مرجع الصفوف داخل النطاق.
2. ثم حدد الخلية E2 ، واسحب مقبض التعبئة لأسفل إلى الخلايا حتى تحصل على خلايا فارغة ، ويتم سرد جميع القيم المطابقة في العمود كما هو موضح في لقطة الشاشة التالية:
Vlookup وسلسلة قيم مطابقة متعددة في خلية بوظيفة محددة بواسطة المستخدم
بدلاً من الحصول على القيم النسبية عموديًا ، في بعض الأحيان ، تريد القيم المطابقة في خلية واحدة وربطها بفاصل معين. في هذه الحالة ، قد تقدم لك الوظيفة المحددة من قبل المستخدم خدمة.
1. اضغط باستمرار ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.
2. انقر إدراج > وحدة، والصق الكود التالي في ملف وحدة نافذة او شباك.
رمز VBA: Vlookup وسلسلة قيم مطابقة متعددة في خلية
Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
result = result & " " & x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
3. ثم احفظ وأغلق هذا الرمز ، عد إلى ورقة العمل ، وأدخل هذه الصيغة: = cusvlookup (D2، A1: B16,2،XNUMX) في خلية فارغة حيث تريد وضع النتيجة ، واضغط على أدخل مفتاح ، تم إرجاع جميع القيم المقابلة بناءً على بيانات محددة في خلية واحدة مع فاصل مسافة ، انظر الصورة:
ملاحظات: في الصيغة أعلاه: D2 يشير إلى قيم الخلايا التي تريد البحث عنها ، أ 1: ب 16 هو نطاق البيانات الذي تريد جلب البيانات ، الرقم 2 هو رقم العمود الذي سيتم إرجاع قيمة المطابقة منه ، يمكنك تغيير مراجع الأطروحات حسب حاجتك.
Vlookup وسلسلة قيم مطابقة متعددة في خلية باستخدام Kutools for Excel
إذا كان لديك كوتولس ل إكسيل، بما لديها الجمع بين الصفوف المتقدمة ميزة ، يمكنك إنهاء هذه المهمة بسرعة بكل سهولة. يمكن أن تساعدك هذه الميزة في دمج جميع القيم المطابقة مع محددات معينة بناءً على نفس البيانات في عمود آخر.
كوتولس ل إكسيل : مع أكثر من 300 وظيفة إضافية مفيدة في Excel ، يمكنك تجربتها مجانًا دون قيود في أيام 30. |
بعد تثبيت كوتولس ل إكسيليرجى القيام بما يلي:
1. حدد نطاق البيانات الذي تريده للحصول على القيم المقابلة بناءً على البيانات المحددة.
2. ثم اضغط كوتولس > دمج وتقسيم > الجمع بين الصفوف المتقدمة، انظر لقطة الشاشة:
3. في الجمع بين الصفوف المتقدمة في مربع الحوار ، انقر فوق اسم العمود الذي تريد الدمج بناءً عليه ، ثم انقر فوق المفتاح الأساسي زر ، انظر لقطة الشاشة:
4. ثم انقر فوق اسم عمود آخر تريد إرجاع القيم المتطابقة ، وانقر فوق دمج لاختيار فاصل واحد لفصل القيم المجمعة ، انظر الصورة:
5. ثم انقر فوق Ok زر ، تم دمج جميع القيم المقابلة بناءً على نفس القيم مع فاصل معين ، انظر لقطات الشاشة:
قم بتنزيل Kutools for Excel والإصدار التجريبي المجاني الآن!
عرض توضيحي: Vlookup وسلسلة قيم مطابقة متعددة في خلية باستخدام Kutools for Excel
أفضل أدوات إنتاجية المكتب
عزز مهاراتك في Excel باستخدام Kutools for Excel، واختبر كفاءة لم يسبق لها مثيل. يقدم Kutools for Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية وتوفير الوقت. انقر هنا للحصول على الميزة التي تحتاجها أكثر...
يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير
- تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
- فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
- يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!