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

كيفية إرجاع قيم مطابقة متعددة بناءً على معيار واحد أو عدة معايير في Excel؟

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

قم بإرجاع قيم مطابقة متعددة استنادًا إلى معيار واحد أو عدة معايير باستخدام صيغ الصفيف


قم بإرجاع قيم مطابقة متعددة استنادًا إلى معيار واحد أو عدة معايير باستخدام صيغ الصفيف

على سبيل المثال ، أريد استخراج جميع الأسماء التي يبلغ عمرها 28 عامًا وتأتي من الولايات المتحدة ، يرجى تطبيق الصيغة التالية:

1. انسخ أو أدخل الصيغة أدناه في خلية فارغة حيث تريد تحديد النتيجة:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

ملاحظة: في الصيغة أعلاه ، B2: B11 هو العمود الذي يتم إرجاع القيمة المطابقة منه ؛ F2, C2: C11 هي الشرط الأول وبيانات العمود التي تحتوي على الشرط الأول ؛ G2, D2: D11 هي الشرط الثاني وبيانات العمود التي تحتوي على هذا الشرط ، يرجى تغييرها حسب حاجتك.

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

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

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


المزيد من المقالات ذات الصلة:

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

 


  • سوبر فورميولا بار (بسهولة تحرير أسطر متعددة من النص والصيغة) ؛ تخطيط القراءة (قراءة وتحرير أعداد كبيرة من الخلايا بسهولة) ؛ لصق في النطاق المصفى
  • دمج الخلايا / الصفوف / الأعمدة وحفظ البيانات. تقسيم محتوى الخلايا ؛ اجمع الصفوف المكررة والمجموع / المتوسط... منع تكرار الخلايا؛ قارن النطاقات
  • حدد مكرر أو فريد صفوف حدد صفوف فارغة (جميع الخلايا فارغة) ؛ البحث الفائق والبحث الغامض في العديد من المصنفات. تحديد عشوائي ...
  • نسخة طبق الأصل خلايا متعددة بدون تغيير مرجع الصيغة ؛ إنشاء المراجع تلقائيًا إلى أوراق متعددة أدخل الرموز النقطية، مربعات الاختيار والمزيد ...
  • المفضلة وإدراج الصيغ بسرعةوالنطاقات والمخططات والصور ؛ تشفير الخلايا مع كلمة السر إنشاء قائمة بريدية وإرسال رسائل البريد الإلكتروني ...
  • استخراج النص، إضافة نص ، إزالة حسب الموضع ، إزالة الفضاء؛ إنشاء وطباعة المجاميع الفرعية لترحيل الصفحات ؛ التحويل بين محتوى الخلايا والتعليقات
  • سوبر تصفية (حفظ وتطبيق مخططات التصفية على أوراق أخرى) ؛ فرز متقدم حسب الشهر / الأسبوع / اليوم ، التكرار والمزيد ؛ مرشح خاص بواسطة bold، italic ...
  • اجمع بين المصنفات وأوراق العمل؛ دمج الجداول على أساس الأعمدة الرئيسية ؛ تقسيم البيانات إلى أوراق متعددة; تحويل دفعة xls و xlsx و PDF
  • تجميع الجدول المحوري حسب رقم الأسبوع واليوم من الأسبوع والمزيد ... إظهار الخلايا غير المؤمنة والمغلقة بألوان مختلفة قم بتمييز الخلايا التي لها صيغة / اسم
علامة تبويب kte 201905
  • تمكين التحرير والقراءة المبوبة في Word و Excel و PowerPointوالناشر والوصول و Visio والمشروع.
  • فتح وإنشاء مستندات متعددة في علامات تبويب جديدة من نفس النافذة ، بدلاً من النوافذ الجديدة.
  • يزيد من إنتاجيتك بنسبة 50٪ ، ويقلل مئات النقرات بالماوس كل يوم!
أوفيسيتاب القاع

 

فرز التعليقات حسب
التعليقات (25)
لا يوجد تقييم. كن أول من يقيم!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
لقد جربت نفس الصيغة بالضبط. نسخ 100٪. الشيء الوحيد الذي قمت بتغييره هو مطابقة البيانات وإعادتها. عندما أستخدم هذه الصيغة ، يقول Excel "لقد أدخلت عددًا كبيرًا جدًا من الوسائط لهذه الوظيفة). = INDEX ('2020 Volume Report'! $ B $ 3: $ B $ 100، SMALL (IF (COUNTIF ($ A $ 1، '2020 Volume تقرير '! $ A $ 3: $ A $ 100) * COUNTIF ($ 3 $ ،' 2020 Volume Report '! $ D $ 3: $ D $ 100) ، ROW (' 2020 Volume Report '! $ 3 $: $ G $ 100) - MIN (ROW ('2020 Volume Report'! $ A $ 3: $ G $ 100)) + 1، "0")، ROW (A1)، COLUMN (A1))
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، هل يمكنك إعطاء بياناتك وخطأ الصيغة على شكل لقطة شاشة هنا؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، كيف يمكنني استخدامه للحالة الأفقية.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
ما هو "0" بعد +1 في الصيغة؟ هذا ليس في المثال الأول.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، لقد جربت نفس الصيغة. أحصل على نتيجة ولكن عند إعطاء محرك البحث المخصص لا يقدم أي ردود متعددة
تم تصغير هذا التعليق بواسطة المشرف على الموقع

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

كنت أتساءل عما إذا كان من الممكن على الإطلاق إدخال معيار ثانٍ ولكن من نفس النطاق مثل المعيار الأول ،

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

شكرا لك على المساعدة مقدما.
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا نيك،

سعيد للمساعدة. إذا كنت ترغب في الحصول على أسماء أشخاص من كل من أمريكا وفرنسا ، أنصحك باستخدام صيغتنا مرتين للحصول على النتيجة. يرجى الاطلاع على لقطة الشاشة ، في F2 و G2 قيمتان "الولايات المتحدة" و "فرنسا". تطبيق الصيغة = IFERROR (INDEX ($ B $ 2: $ B $ 11 ، صغير (IF ($ F $ 2 = $ D $ 2: $ D $ 11 ، ROW ($ D $ 2: $ D $ 11) -ROW ($ D $ 2) +1 ) ، ROW (1: 1))) ، "") للحصول على النتائج لأمريكا. وقم بتطبيق الصيغة = IFERROR (INDEX ($ B $ 2: $ B $ 11، SMALL (IF ($ G $ 2 = $ D $ 2: $ D $ 11 ، ROW ($ D $ 2: $ D $ 11) -ROW ($ D $ 2) + 1) ، ROW (1: 1))) ، "") للحصول على نتائج فرنسا. انه سهل. يرجى المحاولة.

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

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

مع خالص الشكر والتقدير،
ماندي
تم تصغير هذا التعليق بواسطة المشرف على الموقع
أهلا،
لقد جربت استخدام الصيغة وتقوم إما بإنشاء القيمة 0 أو الصورة المرفقة
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا ميلكو
أظهرت لقطة الشاشة الخاصة بك برنامج WPS من إصدار MAC ، لذلك لست متأكدًا مما إذا كانت صيغتنا متاحة أم لا.
لقد قمت بتحميل ملف Excel إلى هنا ، يمكنك محاولة معرفة ما إذا كان بإمكانه الحساب بشكل صحيح في بيئتك.
شكرا!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،
ما هو المطلوب لتوسيع الصيغة الأولى في الحالة التالية:
بعض المعرفات فارغة (على سبيل المثال الخلية A5 فارغة) وأود شرطًا إضافيًا لإخراج سطور فقط عندما لا تكون المعرفات فارغة. (لذا يجب أن يكون الناتج جيمس وعبدال.
شكر!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا جو
لحل مشكلتك ، يرجى تطبيق الصيغة التالية:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

من فضلك خذ راي ، آمل أن يساعدك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،

إذا كتبت في الخلية H1 "الاسم" وأردت ربط ذلك بالصيغة ، فكيف سيعمل ذلك؟
ثم يمكنني كتابة "ID" في الخلية H1 وسأحصل تلقائيًا على النتيجة: AA1004؛ DD1009 ؛ PP1023 (للصيغة الأولى)

شكرا لكم مقدما!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا ماري
عُذْرًا ، لَا أَفْهَمْ لِمَشَكْلِتِكَ الأولى ، فهل يمكنك شرح مشكلتك بشكل أوضح وتفصيلي؟ أو يمكنك إدراج لقطة شاشة هنا لوصف مشكلتك.
بالنسبة للسؤال الثاني ، تحتاج فقط إلى تغيير مرجع الخلية على النحو التالي:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

تذكر الضغط كترل + شيفت + إنتر مفاتيح معا.
من فضلك حاول ، آمل أن يساعدك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، شكرًا لك على الصيغة. عملت من أجل القيم "الثابتة" / النص كمعايير. ومع ذلك ، فإن أحد المعايير التي أحاول استخدامها هو شرط (قيم <> 0) ، لكنه لا يعمل بالصيغة الموصوفة. هل تعرفون يا رفاق ما الذي يجب علي تغييره لتكييف الصيغة حتى أتمكن من الحصول على شرط كأحد المعايير ، من فضلك؟

أفضل،

جون
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا ماركوس
لحل مشكلتك ، يرجى الاطلاع على هذا المقال:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
هناك بعض التفسيرات التفصيلية لهذه المهمة. تحتاج فقط إلى تغيير criteira لك.
شكرا!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،

أولا ، شكرا لك على المشاركة!

هل يمكنك تقديم حل للقضية أدناه:

لدي 3 أعمدة (أ: تحتوي على معلومات مرجعية ، ب: تحتوي على معلومات للبحث ، ج: نتيجة البحث)

يتم توفير عنوان url للصورة أدناه

https://ibb.co/VHCd09K

العمود أ ------------------------- العمود ب ------------ العمود ج
اسم الملف ------------------------- الاسم ---------------- اسم الملف ، اسم الوثيقة ، اسم العنصر والاسم
العنصر المتغير ----------------- العنصر ------------- العنصر المتغير ، اسم العنصر ، معرف العنصر
موقع العمود
اسم الملف
اسم العنصر
الاسم
الاختصاص
الضمان
منحدر
معرف العنصر

ما أحتاجه هو البحث في العمود A عن أي تطابق جزئي مع الخلية B2 (الاسم) أو B3 (العنصر) والحصول على النتيجة في خلية واحدة ،

شكرا لك بهزاد
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا بهزاد
ربما يمكن أن تساعدك الوظيفة المحددة من قبل المستخدم أدناه.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


بعد نسخ هذا الرمز ولصقه ، ثم استخدم هذه الصيغة:= ConcatPartLookUp (B2، $ A $ 2: $ A $ 8) للحصول على النتيجة التي تحتاجها.
يرجى المحاولة ، آمل أن يساعدك!
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا،

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

أرغب في الحصول على تواريخ الأيام التي كانت فيها مناوباتي أو التي عملت فيها "بعضًا" (> 0) ساعات للعميل.

لذلك في I3 هو الاسم وفي J3 الشهر. K3 و L3 هما التحولات (1 يعمل) والساعات (لا أعرف كيفية ضبط هذا ، يجب أن يكون أكثر من صفر)

نتائجي المتوقعة في:
التحولات: I7 و I8
ساعات: J7

لذلك عملت أكثر من 0 ساعة لـ "الشخص 2" في أكتوبر بتاريخ 3-10-2022
كانت نوبات العمل للشخص 2 في الفترة "10-10-2022 "و28-10-2022

عندما أضيف '= INDEX ($ B $ 2: $ B $ 11 ، SMALL (IF (COUNTIF ($ F $ 2 ، $ C $ 2: $ C $ 11) * COUNTIF ($ G $ 2 ، $ D $ 2: $ D $ 11) ، ROW ($ A $ 2: $ D $ 11) -MIN (ROW ($ A $ 2: $ D $ 11)) + 1) ، ROW (A1)) ، COLUMN (A1)) 'في ورقة Excel الخاصة بي ، لا يسمح فاصلة بين الأجزاء المختلفة من الصيغة.
لذلك أحتاج إلى تغييرها إلى "؛".
ولكن عندما أجربها ، ستظهر دائمًا عبارة: "# NAME؟"

فهل يمكن لأحد أن يساعدني في هذا؟

مع أطيب التحيات،

BAS
[img] https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view؟usp=share_link [/ img]
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبًا ، إذا كانت هناك قيم مكررة (على سبيل المثال ، اثنان من adams) ، كيف يمكنني التأكد من أنها تعرض فقط adam واحدًا وليس 1؟
تم تصغير هذا التعليق بواسطة المشرف على الموقع
مرحبا بوبي ،
لاستخراج قيم المطابقة الفريدة فقط ، يجب عليك تطبيق الصيغة أدناه:
بعد لصق الصيغة ، الرجاء الضغط كترل + شيفت + إنتر مفاتيح معًا للحصول على النتيجة الصحيحة.
= IFERROR (INDEX ($ B $ 2: $ B $ 5، MATCH (0، COUNTIF (H1: $ H $ 1، $ B $ 2: $ B $ 5) + IF ($ D $ 2: $ D $ 5 <> $ G $ 2، 1 ، 0) + IF ($ C $ 2: $ C $ 5 <> $ F $ 2، 1، 0)، 0))، "")

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

تواصل معنا

حقوق التأليف والنشر © 2009 - شبكة الاتصالات العالمية.extendoffice.com. | كل الحقوق محفوظة. مشغل بواسطة ExtendOffice. | | خريطة الموقع
Microsoft وشعار Office هما علامتان تجاريتان أو علامتان تجاريتان مسجلتان لشركة Microsoft Corporation في الولايات المتحدة و / أو دول أخرى.
محمي بواسطة Sectigo SSL