كيف تجد قيمة في Excel باستخدام شرطين أو أكثر؟

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

صيغة المصفوفة 1: العثور على قيمة باستخدام معيارين أو أكثر في Excel

الهيكل العام لهذه الصيغة هو كما يلي:

{=INDEX(array,MATCH(1,(criteria1=lookup_array1)*(criteria2= lookup_array2)…*(criteria n= lookup_array n),0))}

على سبيل المثال، إذا كنت تريد العثور على مبلغ مبيعاتالمانجوالمباعة في9/3/2019، فأدخل الصيغة التالية في خلية فارغة واضغطCtrl+Shift+Enterلتأكيدها كصيغة مصفوفة:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0))

ملاحظة: في هذا المثال،

• F3:F22هو عمود «المبلغ» الذي تريد استرداد القيمة منه.
• B3:B22هو عمود «التاريخ»؛ وC3:C22هو عمود «الفواكه».
• J3 هو التاريخ المعياري الأول، وJ4 هو اسم الفاكهة المعياري الثاني.

إضافة معايير إضافية أمرٌ في غاية السهولة! على سبيل المثال، للبحث عن مبلغ مبيعاتالمانجوفي9/3/2019 بوزن211، ما عليك سوى إضافة الشرط الثالث إلى كلٍّ من MATCH ومصفوفات البحث كما هو موضح:

=INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22),0))

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

صيغة المصفوفة 2: العثور على قيمة باستخدام معيارين أو أكثر في Excel بالدمج

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

=INDEX(array,MATCH(criteria1& criteria2…& criteriaN, lookup_array1& lookup_array2…& lookup_arrayN,0),0)

على سبيل المثال، لاسترداد مبلغ المبيعات لفاكهة ذاتوزن 242في9/1/2019:

=INDEX(F3:F22,MATCH(J3&J4,B3:B22&C3:C22,0),0)

ملاحظة: هنا،

• F3:F22 هو عمود المبلغ؛ وB3:B22 هو التاريخ؛ وE3:E22 هو عمود الوزن.
• J3 هو التاريخ؛ وJ5 هو قيمة الوزن الخاصة بمعاييرك.

لأكثر من معيارين، قم بتوسيع كلٍّ من المعايير ومصفوفات البحث بنفس الترتيب:

=INDEX(F3:F22,MATCH(J3&J4&J5,B3:B22&C3:C22&E3:E22,0),0)

كما في السابق، اضغط علىCtrl+Shift+Enter للحصول على النتيجة الصحيحة.

تتيح لك طريقتا صيغ المصفوفة العثور على القيمة الأولى التي تستوفي جميع معاييرك. ومع ذلك، فإنهما تتطلبان أن تكون نطاقات الخلايا بنفس الحجم، ولا تُعيدان سوى أول تطابق فقط—وليس جميع القيم المتطابقة. إذا لم يُوجد تطابق، فستُرجع الصيغة خطأً#N/A. وإذا كنت تفضل صيغة تعرض جميع التطابقات دفعة واحدة، ففكّر في استخدام دالة FILTER (راجع التفاصيل أدناه).

بعض النصائح والملاحظات العملية:

• إذا كنت تعمل مع إصدارات أحدث من Excel (مثل Microsoft 365 وExcel 2021)، فيمكنك استخدام صيغ المصفوفات الديناميكية ودالةFILTER لتبسيط هذه العملية.
• لتجنب أخطاء #N/A عند عدم وجود تطابقات، يمكنك تغليف الصيغة بدالةIFERROR، على سبيل المثال: =IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),«Not found»).
• تأكد من أن خلايا معايير البحث خالية من المسافات الزائدة أو أنواع البيانات غير المتجانسة.
• إذا ظهر خطأ بعد الضغط على Enter فقط، فتأكد من استخدامكCtrl+Shift+Enter لتأكيدها كصيغة مصفوفة (لـ Excel 2019 والإصدارات الأقدم).

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

1. انتقل إلى تبويببيانات، واخترمتقدمضمن مجموعة الفرز والتصفية لفتح مربع حوار التصفية المتقدمة.

2. في مربع حوار التصفية المتقدمة، أكمل الإعدادات التالية:
(1) حددنسخ إلى موقع آخرفي قسمالإجراء.
(2) بالنسبة إلىنطاق القائمة، حدّد النطاق الذي يحتوي على البيانات التي ترغب في تصفيتها ()A1:E21 في هذا المثال).
(3) بالنسبة إلىنطاق المعايير، حدّد النطاق الذي يحتوي على شروط التصفية الخاصة بك ()H1:J2 هنا). تأكد من أن العناوين في نطاق المعايير هذا تطابق تمامًا تلك الموجودة في جدول البيانات الخاص بك.
(4) فينسخ إلى، حدّد الخلية الأولى التي ترغب في لصق نتائج التصفية فيها ()H9 في هذه الحالة).

3. انقرموافقلتنفيذ التصفية.

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

بعض النصائح والاحتياطات:

• تأكد من أن عناوين نطاق المعايير تطابق تمامًا تلك الموجودة في جدول البيانات الرئيسي، وإلا فقد لا تعمل التصفية بشكل صحيح.
• يدعم التصفية المتقدمة شروطَي AND وOR؛ فوضع المعايير في نفس الصف يُطبّق منطقَ AND (حيث يجب أن تكون جميع الشروط صحيحة)، بينما يُطبّق استخدامُ صفوفٍ منفصلة منطقَ OR (حيث يكفي أن يكون أيٌّ منها صحيحًا).
• لا يتم تحديث التصفية المتقدمة تلقائيًا عند تعديل البيانات؛ بل يتطلب الأمر إعادة تطبيقها يدويًّا بعد تحديث بياناتك أو معاييرك.
• ضع في اعتبارك أن الخلايا الفارغة ضمن نطاق المعايير قد تُفسَّر على أنها «تطابق أي قيمة» لذلك الحقل.

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

إذا كنت تستخدم إصدارًا حديثًا من Excel (مثل Microsoft 365 أو Excel 2021 فأحدث)، فإن دالةFILTER توفر طريقة ديناميكية وبديهية لاستخراج جميع القيم التي تستوفي معايير متعددة. يُوصى بهذا الحل بشدة لأولئك الذين يحتاجون إلى تحديث النتائج تلقائيًا كلما تغيّرت البيانات أو المعايير، دون الحاجة إلى إدخال مصفوفات معقدة.

1. في خلية فارغة، أدخل صيغة مشابهة للآتي:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

في هذه الصيغة:

• F3:F22 هو عمود المبالغ الخاص بك.
• B3:B22 هو عمود التواريخ، المطابق للتاريخ الموجود فيJ3.
• C3:C22 هو عمود الفواكه، المطابق للفاكهة الموجودة فيJ4.

إذا كنت تريد إضافة شرط ثالث، مثل مطابقة عمود الوزن(E3:E22)لقيمة موجودة فيJ5، قم بتوسيع الصيغة:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

بعد الضغط على Enter، سيعرض Excel جميع المبالغ التي تستوفي المعايير كافةً. وإذا لم يُعثَر على أي تطابق، فستُرجع الصيغة خطأً من نوع#CALC!، ويمكنك التعامل معه بسهولة باستخدام الدالة IFERROR:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

المزايا:

• تُحدَّث النتائج تلقائيًا فور تغيير بياناتك أو معاييرك.
• الصيغ أسهل في الصيانة والتوسيع مقارنةً بصيغ المصفوفات القديمة.
• تُعيد جميع التطابقات، وليس القيمة الأولى فحسب.

القيود:متوفرة فقط في Microsoft 365 وExcel 2021 أو أحدث. ولا تدعمها الإصدارات الأقدم.