Note: The other languages of the website are Google-translated. Back to English

كيفية البحث عن قيم متعددة وإرجاعها دون تكرار في Excel؟ 

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

يقوم المستند بإرجاع قيم فريدة متعددة 1

Vlookup وإرجاع قيم مطابقة متعددة بدون تكرارات باستخدام الوظيفة المحددة بواسطة المستخدم


Vlookup وإرجاع قيم مطابقة متعددة بدون تكرارات باستخدام الوظيفة المحددة بواسطة المستخدم

يمكن أن يساعدك رمز VBA التالي في إرجاع قيم مطابقة متعددة بدون تكرارات ، يرجى القيام بذلك على النحو التالي:

1. اضغط باستمرار على ALT + F11 مفاتيح لفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة.

2. انقر إدراج > وحدة، والصق الكود التالي في ملف وحدة نافذة او شباك.

كود VBA: Vlookup وإرجاع قيم متطابقة فريدة متعددة:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. بعد إدخال الرمز ، انقر فوق أدوات > مراجع في الفتح ميكروسوفت فيسوال باسيك للتطبيقات نافذة ، ثم في المنبثقة المراجع - VBAProject مربع الحوار ، تحقق وقت تشغيل البرمجة لـ Microsoft الخيار في المراجع المتاحة مربع القائمة ، انظر لقطة الشاشة:

يقوم المستند بإرجاع قيم فريدة متعددة 2

4. ثم اضغط OK لإغلاق مربع الحوار ، وحفظ وإغلاق نافذة التعليمات البرمجية ، والعودة إلى ورقة العمل ، وإدخال هذه الصيغة: =MultipleLookupNoRept(E2,A2:C17,3) في خلية فارغة حيث تريد إخراج النتيجة ، اضغط على أدخل مفتاح للحصول على النتيجة الصحيحة كما تحتاج. انظر لقطة الشاشة:

يقوم المستند بإرجاع قيم فريدة متعددة 3

ملاحظة: في الصيغة أعلاه ، E2 هي المعايير التي تريد البحث عنها ، A2: C17 هو نطاق البيانات الذي تريد استخدامه ، الرقم 3 هو رقم العمود الذي يحتوي على القيم التي تم إرجاعها.


أفضل أدوات إنتاجية المكتب

Kutools for Excel يحل معظم مشاكلك ويزيد إنتاجيتك بنسبة 80٪

  • إعادة استخدام: أدخل بسرعة الصيغ المعقدة والرسوم البيانية وأي شيء استخدمته من قبل ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
  • دمج الخلايا / الصفوف / الأعمدة دون فقدان البيانات ؛ تقسيم محتوى الخلايا ؛ ادمج الصفوف / الأعمدة المكررة... منع تكرار الخلايا؛ قارن النطاقات
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
  • أكثر من 300 ميزة قوية. يدعم Office / Excel 2007-2019 و 365. يدعم جميع اللغات. سهولة النشر في مؤسستك أو مؤسستك. الميزات الكاملة نسخة تجريبية مجانية لمدة 30 يومًا. ضمان استرداد الأموال لمدة 60 يومًا.
علامة تبويب kte 201905

يجلب Office Tab الواجهة المبوبة إلى Office ، ويجعل عملك أسهل بكثير

  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع
فرز التعليقات حسب
التعليقات (13)
لا يوجد تقييم. كن أول من يقيم!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
ماذا لو أردت إنشاء قائمة في جدول من هذا بدلاً من جميع النتائج في خلية واحدة؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا توم،
إذا كنت تريد استخراج القيم الفريدة في قائمة الخلايا بدلاً من خلية واحدة ، فقد تساعدك الصيغة التالية:

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

رجاءا حاول فعلها.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا Skyyang ،

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

حاولت تعديل هذا الجزء من كود VBA: xStr = xStr & xDic.Keys (I) & "،" ليكون هذا: xStr = xStr & xDic.Keys (I) & "،"

أدى ذلك إلى إضافة المسافة بين القيمتين ، ولكنه أضاف أيضًا فاصلة بعد القيمة الأخيرة. "إميلي ، جيمس ، ديزي ، غاري"

هل هناك طريقة لجعلها تعمل مع المساحة ولكن بدون فاصلة إضافية بعد القيمة الأخيرة؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا ديميتر ،
استخدم المساحة لفصل القيم ، ما عليك سوى تغيير رمز vba:
من xStr = xStr & xDic.Keys (i) & "،" ليكون هذا: xStr = xStr & xDic.Keys (i) & ""

رجاءا حاول فعلها.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
xStr = xStr & xDic.Keys (I) & "،" ليكون هذا: xStr = xStr & xDic.Keys (I) & "،"

هل هناك طريقة لاستبدال "،" بـ ALT + ENTER في الخلية ، بحيث تكون النتائج في نفس الخلية ولكن في سطور مختلفة؟ هل أحتاج إلى تقديم وحدة VBA إضافية لذلك ودمجها؟

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

الوظيفة MultipleLookupNoRept (قيمة البحث على هيئة سلسلة ، نطاق البحث كنطاق ، رقم العمود كرقم صحيح)
Dim xDic كـ قاموس جديد
خافت xRows طويلة
خافت xStr كسلسلة
خافت أنا طويلة
على خطأ استئناف التالي
xRows = LookupRange.Rows.Count
لأني = 1 إلى xRows
إذا كان LookupRange.Columns (1) .Cells (i) .Value = Lookupvalue ثم
xDic.Add LookupRange.Columns (ColumnNumber) .Cells (i) .Value، ""
إنهاء حالة
التالى
xStr = ""
MultipleLookupNoRept = xStr
إذا كان xDic.Count> 0 ثم
بالنسبة إلى i = 0 إلى xDic.Count - 1
xStr = xStr & xDic.Keys (i) & Chr (10) + Chr (13)
التالى
MultipleLookupNoRept = يسار (xStr ، Len (xStr) - 1)
إنهاء حالة
التصحيح. طباعة xStr
نهاية وظيفة

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

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

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

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

وأيضًا اعتدت على سطح المكتب الجديد كما أنه يتعطل فقط ...

تبلغ قيمة بياناتي حوالي 10,000 صف
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، لقد فعلت ما قلته وهو رائع ولكن ما زال لم يحل إحدى مشكلتي ، ماذا يحدث عندما تكون قيمة فريدة في كل شهر؟ = MultipleLookupNoRept (E2، A2: C17,3،2) ، أحاول استخدام E1 & XNUMX لشهر يناير ولكنه لا يعمل
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا يا جامع ،
هل يمكنك إعطاء مشكلتك كلقطة شاشة هنا ، حتى أتمكن من فهم متطلباتك؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
هذا عظيم! كيف يمكنني تكييف هذا لعدم إضافة قيم فارغة إلى القاموس؟ لقد حاولت إضافة الخط الغامق أدناه ، لكن السلسلة النهائية لا تزال ترجع مع "" ، مثيلات.


xRows = LookupRange.Rows.Count
لأني = 1 إلى xRows
إذا كان LookupRange.Columns (1) .Cells (i) .Value = Lookupvalue And Not IsEmpty (LookupRange.Columns (1) .Cells (i) .Value) ثم
xDic.Add LookupRange.Columns (ColumnNumber) .Cells (i) .Value، ""
إنهاء حالة
التالى

شكر،
لا توجد تعليقات منشورة هنا حتى الآن
اترك تعليقاتك
النشر كضيف
×
قيم المنشور:
0   الشخصيات
المواقع المقترحة