كيفية البحث عن قيم متعددة في Excel: خطوة بخطوة

عند العمل مع مجموعات كبيرة من البيانات في Microsoft Excel ، قد يكون من الصعب العثور على قيم متعددة تطابق عدة شروط. تم تصميم بعض الوظائف المضمنة ، مثل VLOOKUP ، في الأصل للعمل مع قيم فردية.

يمكن دمج العديد من وظائف Excel للبحث عن قيم متعددة. وهي تشمل دالات VLOOKUP و INDEX و MATCH و IF. يمكن للإصدارات الحالية من Excel استخدام المصفوفات الديناميكية بينما تستخدم الإصدارات الأقدم صيغ الصفيف.

ستوضح لك هذه المقالة بالضبط كيفية استخدام هذه الوظائف في الصيغ التي تجد قيمًا متعددة في بياناتك.

دعنا نذهب!

جدول المحتويات

• كيفية استخدام VLOOKUP بقيم متعددة
  • ما هي صيغ الصفيف؟
  • مثال على VLOOKUP
  • أحدث إصدارات Excel
  • طريقة قديمة
  • المطابقة التامة مقابل المطابقة التقريبية
  • المزيد حول VLOOKUP
• كيفية استخدام الفهرس والمطابقة للبحث عن قيم متعددة
  • كيفية استخدام الفهرس والمطابقة معًا في Excel 365
  • العمل مع الإصدارات الأقدم من Excel
• كيفية استخدام الدالة IF للبحث عن قيم متعددة
  • الإصدارات الحالية من Excel
  • الإصدارات القديمة من Excel
• كيفية استخدام SUMPRODUCT لمعايير متعددة
• 3 أخطاء شائعة مع وظائف البحث
  • 1. أخطاء # N / A
  • 2. # REF! أخطاء
  • 3. أخطاء دائرية
• كيفية استخدام IFERROR مع وظائف البحث
• 7 تقنيات بحث متقدمة
  • 1. بحث الموقف النسبي
  • 2. وظيفة صغيرة
  • 3. وظيفة ROW
  • 4. وظيفة التصفية
  • 5. أعمدة المساعد
  • 6. المصفوفات الديناميكية
  • 7. Power Query
• افكار اخيرة

كيفية استخدام VLOOKUP بقيم متعددة

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

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

ما هي صيغ الصفيف؟

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

توجد عدة خطوات سهلة عند إنشاء صيغة صفيف:

1. حدد نطاقًا من الخلايا للبحث فيه.

2. أدخل الصيغة في شريط الصيغة.

3. اضغط على Ctrl + Shift + Enter لإكماله.

يتشابه بناء جملة صيغة الصفيف مع صيغة الصيغة العادية ، لكنه يتضمن أقواس معقوفة {} حول الصيغة. تشير الأقواس المتعرجة إلى أن الصيغة عبارة عن صيغة صفيف وأنها سترجع صفيفًا من القيم.

ستوضح لك الأمثلة في هذه المقالة كيفية استخدام صيغ الصفيف بشكل صحيح.

مثال على VLOOKUP

يحتوي مثالنا على خمسة عناصر في العمود A من ورقة العمل:

• تفاحة

• موز

• خبز

• جزرة

• الكرز

تتمثل المهمة في التحقق مما إذا كانت هناك ثلاث فواكه محددة في هذه القائمة: التفاح والموز والكرز.

أحدث إصدارات Excel

يعتمد بناء جملة VLOOKUP على إصدار Excel الذي تستخدمه.

تدعم أحدث الإصدارات ( Excel 365 أو Excel 2021 ) المصفوفات الديناميكية. تسمح هذه الميزة للصيغ بإرجاع نتائج متعددة "تنسكب" في الخلايا المجاورة.

هذا هو بناء الجملة (علامة التساوي تبدأ الصيغة):

= VLOOKUP (lookup_value، table_array، col_index_num، [range_lookup])

• lookup_value: القيمة التي تريد البحث عنها.

• table_array: جدول البيانات بالكامل الذي تريد البحث فيه.

• col_index_num: رقم عمود الجدول في table_array الذي يحتوي على البيانات التي تريد إرجاعها.

• range_lookup: اختياري. يحدد ما إذا كنت تريد المطابقة التامة أو المطابقة التقريبية.

يستخدم مثالنا المحدد هذه الصيغة:

= VLOOKUP ({"apple"، "banana"، "cherry"}، A1: A5،1، FALSE)

عند إدخال الصيغة في الخلية B1 ، تنتشر النتائج في الخليتين C1 و D1. توضح هذه الصورة المثال في العمل:

طريقة قديمة

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

اتبع هذه الخطوات:

1. انقر فوق الخلية حيث تريد عرض نتائج العنصر الأول (على سبيل المثال ، العمود B).

2. اكتب الصيغة التالية بدون الضغط على Enter حتى الآن: = VLOOKUP ({"apple"، "banana"، "cherry"}، A1: A5، 1، FALSE)

3. اضغط على Ctrl + Shift + Enter لتحويل هذا إلى صيغة صفيف.

4. انسخ الخلية B1 والصقها في الخلية أدناه (أو استخدم مقابض التعبئة).

عند استخدام Ctrl + Shift + Enter ، يضيف Excel أقواس معقوفة حول الصيغة. يشير هذا إلى أنها صيغة صفيف.

المطابقة التامة مقابل المطابقة التقريبية

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

إذا كنت تريد إجراء مطابقة تامة ، فيمكنك تعيين وسيطة range_lookup على FALSE.

ضع في اعتبارك أن المطابقات التقريبية تعمل بشكل أفضل مع القيم الرقمية المرتبة. عادة ما يكون غير مناسب عندما تكون قيمة الخلية نصًا.

المزيد حول VLOOKUP

إذا كنت تريد معرفة المزيد حول هذه الوظيفة متعددة الاستخدامات ، فراجع هذه المقالات:

• كيفية مقارنة عمودين في Excel مع VLOOKUP

• كيف تجد التناقضات مع VLOOKUP

الآن بعد أن تم ضبطك باستخدام وظيفة VLOOKUP ، دعنا نلقي نظرة على وظيفتين أخريين يمكنهما القيام بما تقوم به بطريقة مختلفة: INDEX و MATCH.

كيفية استخدام الفهرس والمطابقة للبحث عن قيم متعددة

يمكنك دمج دالتي INDEX و MATCH معًا للعثور على قيم متعددة في صفوف متعددة.

ترجع الدالة INDEX في Excel قيمة أو مرجعًا إلى خلية ضمن نطاق محدد.

= INDEX (صفيف ، row_num ، [column_num])

• المصفوفة: نطاق الخلايا المراد البحث عنها عن القيمة.

• row_num: رقم الصف داخل المصفوفة المطلوب إرجاع قيمة منه.

• العمود: (اختياري) رقم العمود داخل المصفوفة التي يتم إرجاع قيمة منها. إذا تم حذفها ، تقوم الدالة بإرجاع الصف بأكمله.

ترجع الدالة MATCH في Excel موضع قيمة ضمن نطاق محدد.

= MATCH (lookup_value، lookup_array، [match_type])

• lookup_value: القيمة المراد البحث عنها داخل lookup_array.

• lookup_array: نطاق الخلايا المراد البحث عنه lookup_value.

• match_type: (اختياري) نوع المطابقة المطلوب إجراؤها. إذا تم حذفها ، تؤدي الوظيفة مطابقة تامة.

كيفية استخدام الفهرس والمطابقة معًا في Excel 365

لاستخدام INDEX و MATCH معًا للبحث عن قيم متعددة في Excel ، تحتاج إلى استخدام صيغة صفيف.

عند العمل باستخدام البيانات النموذجية السابقة ، هذه هي الصيغة في Excel 365:

= INDEX (A1: A5، MATCH ({"apple"، "banana"، "cherry"}، A1: A5، 0))

يتفكك المثال أعلاه على النحو التالي:

• INDEX: يؤدي هذا إلى إرجاع قيمة خلية في نطاق محدد بناءً على رقم صف وعمود معين. في هذه الحالة ، ستُرجع القيمة من النطاق A1: A5.

• A1: A5: هذا هو نطاق الجدول المحدد حيث تبحث عن القيمة والذي سيتم عرض النتيجة منه.

• MATCH: هذا يبحث عن عنصر محدد في نطاق من الخلايا ويعيد الموضع النسبي لهذا العنصر في النطاق.

• {"apple"، "banana"، "cherry"}: هذا هو ثابت المصفوفة الذي يحتوي على القيم التي تريد البحث عنها.

• A1: A5: هذا هو النطاق حيث ستبحث MATCH عن القيم من ثابت الصفيف.

• 0: هذا هو نوع المطابقة لوظيفة MATCH. في هذه الحالة ، تكون القيمة 0 ، مما يعني أنك تبحث عن تطابق تام بدلاً من تطابق وثيق.

 تُظهر هذه الصورة الصيغة قيد التنفيذ:

العمل مع الإصدارات الأقدم من Excel

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

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

اتبع هذه الخطوات:

1. انقر فوق الخلية حيث تريد نتيجة العنصر الأول (على سبيل المثال ، الخلية B1)

2. اكتب الصيغة أدناه:

3. = INDEX (A1: A5، MATCH (“apple”، A1: A5، 0))

4. اضغط على Enter لتنفيذ الصيغة.

5. اكتب هذه الصيغة في الخلية B2: = INDEX (A1: A5، MATCH (“banana”، A1: A5، 0))

6. اكتب هذه الصيغة في الخلية B3: = INDEX (A1: A5، MATCH (“cherry”، A1: A5، 0))

تُظهر هذه الصورة مرجع الخلية B3:

وظائف INDEX و MATCH ليستا الوحدتان اللتان يمكنهما من العثور على قيم متعددة. في القسم التالي ، سننظر في كيفية استخدام وظيفة IF كبديل.

كيفية استخدام الدالة IF للبحث عن قيم متعددة

هناك طريقة أخرى للبحث عن قيم خلايا متعددة استنادًا إلى معايير معينة وهي استخدام الدالة IF مع وظائف أخرى.

تسمح لك وظيفة IF باختبار عدة شروط وإرجاع نتائج مختلفة بناءً على نتيجة تلك الاختبارات.

على سبيل المثال ، لنفترض أن لديك جدول بيانات مبيعات به أعمدة للمنتج والمبيعات. تريد البحث عن مبلغ المبيعات وإجماليه لاثنين من المنتجات الثلاثة.

الإصدارات الحالية من Excel

للعثور على مجموع عمود المبيعات حيث يكون المنتج إما "Apple" أو "Banana" باستخدام الدالة IF ، يمكنك استخدام صيغة صفيف مع الدالات IF و SUM و OR.

بافتراض أن بياناتك تبدأ في الخلية A1 ، استخدم الصيغة التالية:

= SUM (IF ((A2: A4 = ”Apple”) + (A2: A4 = ”Banana”)، B2: B4، 0))

ينشئ القسم (A2: A4 = "Apple") + (A2: A4 = "Banana") مصفوفة لها قيمة 1 إذا كانت الخلية الموجودة في النطاق A2: A4 تحتوي على "Apple" أو "Banana" ، و 0 بخلاف ذلك .

تتحقق جملة IF من كل عنصر من وسيطة الصفيف. إذا كانت القيمة 1 (أي أن المنتج إما "Apple" أو "Banana") ، فإنه يأخذ القيمة المقابلة في عمود المبيعات (النطاق B2: B4) ؛ خلاف ذلك ، يستغرق 0.

تجمع الدالة SUM القيم من الدالة IF ، وتلخص بشكل فعال قيم المبيعات لكل من "Apple" و "Banana".

تُظهر هذه الصورة الصيغة قيد التشغيل في نطاق البحث:

الإصدارات القديمة من Excel

في Excel 2019 أو إصدار سابق ، تحتاج إلى استخدام صيغة صفيف. اتبع هذه الخطوات:

1. اكتب الصيغة ولكن لا تضغط على Enter.

2. اضغط على Ctrl + Shift + Enter لجعلها صيغة صفيف.

سيضيف Excel أقواسًا متعرجة {} حول الصيغة ، مما يشير إلى أنها صيغة صفيف.

بعد ذلك ، ننظر في كيفية استخدام SUMPRODUCT للبحث عن عدة قيم بناءً على المعايير الخاصة بك. دعنا نذهب!

كيفية استخدام SUMPRODUCT لمعايير متعددة

تسمح لك الدالة SUMPRODUCT أيضًا بالبحث عن قيم متعددة بناءً على معايير متعددة.

نظرًا لأنه لا يتطلب استخدام صيغة صفيف ، فإن بناء الجملة هو نفسه بغض النظر عن إصدار Excel.

باستخدام نفس البيانات كما في المثال السابق ، تبدو الصيغة كما يلي:

= SUMPRODUCT (((A2: A4 = ”Apple”) + (A2: A4 = ”Banana”) ، B2: B4)

ينشئ القسم (A2: A4 = "Apple") + (A2: A4 = "Banana") مصفوفة لها قيمة 1 إذا كانت الخلية الموجودة في النطاق A2: A4 تحتوي على "Apple" أو "Banana" ، و 0 بخلاف ذلك .

تضرب الدالة SUMPRODUCT عناصر الصفيف بالعناصر المقابلة في عمود المبيعات (النطاق B2: B4). ثم تجمع القيم الناتجة ، وتلخص بشكل فعال قيم المبيعات لكل من "Apple" و "Banana".

توضح الصيغة أدناه ذلك أثناء العمل:

تعد وظائف Excel مذهلة عندما تعمل كما هو متوقع ، ولكن في بعض الأحيان قد تواجه أخطاء. في القسم التالي ، نغطي بعض الأخطاء الشائعة وكيف يمكنك التعامل معها.

3 أخطاء شائعة مع وظائف البحث

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

1. # N / A أخطاء

2. # REF! أخطاء

3. أخطاء دائرية

1. أخطاء # N / A

يحدث الخطأ # N / A عندما لا يمكن العثور على قيمة البحث في صفيف البحث.

هناك عدة أسباب لحدوث هذا الخطأ ، بما في ذلك:

• قيمة البحث بها أخطاء إملائية أو غير صحيحة.

• لا يتم فرز صفيف البحث بترتيب تصاعدي.

• قيمة البحث ليست في مجموعة البيانات.

عندما لا تكون قيمة البحث في مجموعة البيانات ، فهذه معلومات مفيدة. ومع ذلك ، قد يعتقد مستخدمو Excel عديمي الخبرة أن # N / A تعني حدوث خطأ ما في الصيغة. يوضح القسم التالي كيفية جعل هذا أكثر سهولة في الاستخدام.

2. # REF! أخطاء

# REF! يحدث خطأ عندما يتم حذف أو نقل مصفوفة البحث أو مصفوفة الإرجاع.

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

3. أخطاء دائرية

أثناء دمج الدالات في الصيغ المعقدة ، قد يخبرك Excel أن لديك مرجعًا دائريًا.

ستجد هذه الأمور أسهل في التحقيق باستخدام دليلنا للعثور على مراجع دائرية في Excel .

كيفية استخدام IFERROR مع وظائف البحث

تعد الدالة IFERROR أداة مفيدة لمعالجة الأخطاء في وظائف البحث. يسمح لك بتحديد قيمة أو صيغة لإرجاعها إذا قامت دالة البحث بإرجاع خطأ.

يكون بناء جملة الدالة IFERROR كما يلي:

= IFERROR (القيمة ، value_if_error)

القيمة: القيمة أو الصيغة التي تريد تقييمها.

· Value_if_error: القيمة أو الصيغة المراد إرجاعها إذا أرجعت الوسيطة الأولى خطأً.

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

كما ترى ، يتم عرض الخطأ # N / A ، والذي يمكن أن يكون مربكًا لمستخدمي Excel عديمي الخبرة.

بدلاً من ذلك ، يمكنك استخدام IFERROR لعرض خلية فارغة أو رسالة تقول "لم يتم العثور عليها" باستخدام هذه الصيغة:

= IFERROR (VLOOKUP (lookup_value، table_array، column_index، FALSE)، "غير موجود")

في هذا المثال ، إذا قامت الدالة VLOOKUP بإرجاع خطأ ، فستقوم الدالة IFERROR بإرجاع الرسالة "غير موجود" بدلاً من ذلك.

تظهر هذه الصورة الصيغة في العمل. يحتوي العمود B على القيمة المفقودة ، بينما وجد العمود C والعمود D تطابقًا.

لقد غطينا الكثير من النقاط حتى الآن ، وأنت مستعد أخيرًا لتعلم المزيد من التقنيات المتقدمة لعمليات البحث ، وهو موضوع القسم التالي.

7 تقنيات بحث متقدمة

يمكن أن يكون البحث عن قيم متعددة في Excel مهمة صعبة ، خاصة عند التعامل مع مجموعات البيانات الكبيرة. قد تواجه مشكلات في الأداء مع المعالجة البطيئة.

هناك سبع تقنيات بحث متقدمة يمكنك استخدامها لجعل العملية أسهل وأكثر كفاءة.

• البحث عن الموضع النسبي

• وظيفة صغيرة

• وظيفة ROW

• وظيفة التصفية

• أعمدة المساعد

• المصفوفات الديناميكية

• استعلام القوة

1. بحث الموقف النسبي

من أبسط الطرق للبحث عن قيم متعددة في Excel استخدام البحث النسبي عن الموضع. يتضمن ذلك تحديد إزاحة الصفوف والعمود من خلية معينة لتحديد القيمة المطلوبة.

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

= OFFSET (خلية، 2، 3)

2. وظيفة صغيرة

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

من خلال دمج هذه الوظيفة مع وظائف البحث الأخرى مثل INDEX و MATCH ، يمكنك البحث عن قيم متعددة بناءً على معايير محددة.

على سبيل المثال ، تبحث الصيغة التالية عن ثاني أصغر قيمة في نطاق من الخلايا التي تفي بشرط معين:

= فهرس (بيانات ، تطابق (صغير (إذا (معايير ، نطاق) ، 2) ، نطاق ، 0))

3. وظيفة ROW

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

على سبيل المثال ، تبحث الصيغة التالية عن قيمة في جدول بناءً على معرّف فريد:

= INDEX (data، MATCH (unique_identifier، data [unique_identifier]، 0)، column_index_number)

4. وظيفة التصفية

لا تتوفر وظيفة التصفية في الإصدارات القديمة من Excel.

في Excel 365 ، يمكنك استخدامه لتصفية نطاق من الخلايا بناءً على معايير معينة ، وإرجاع القيم التي تفي بهذه المعايير فقط. هذا هو النحو وثلاث حجج:

= FILTER (مصفوفة ، تتضمن ، [if_empty])

• المصفوفة: البيانات المحددة التي تريد تصفيتها.

• include: إن الوسيطة include هي المعايير أو الشروط التي تريد تطبيقها على المصفوفة.

• [if_empty] (اختياري): القيمة المطلوب عرضها إذا لم تكن هناك صفوف أو أعمدة تفي بالمعايير المحددة في وسيطة التضمين.

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

= SUM (FILTER (B2: B4، (A2: A4 = ”Apple”) + (A2: A4 = ”Banana”)))

توضح هذه الصورة عدد الصفوف المتطابقة ومجموع القيم المختلفة:

5. أعمدة المساعد

يمكنك استخدام عمود مساعد لربط حقول متعددة معًا داخل دالة مثل VLOOKUP.

افترض أنك تعمل باستخدام الاسم الأول والأخير في ورقة منفصلة. يمكنك تجميعها في عمود مساعد تتم الإشارة إليه داخل الصيغة النهائية.

6. المصفوفات الديناميكية

كما تعلمت في الأمثلة السابقة ، يمكن لمستخدمي Microsoft 365 الاستفادة من المصفوفات الديناميكية للبحث عن قيم متعددة في Excel.

تسمح لك المصفوفات الديناميكية بإرجاع قيم متعددة من صيغة واحدة ، مما يسهل البحث عن كميات كبيرة من البيانات.

7. Power Query

يعد Power Query أداة فعالة في Excel يمكن استخدامها لإرجاع القيم بناءً على معايير متعددة.

على سبيل المثال ، يجد هذا الفيديو بيانات فوضوية في جدول بيانات وينظفها.

افكار اخيرة

يختتم ذلك غوصنا العميق في فن البحث عن قيم متعددة في Excel! بمجرد أن تتقن صيغ VLOOKUP و INDEX و MATCH والصفيف ، ستجد نفسك تتنقل عبر مجموعات البيانات المعقدة مثل السكين الساخن من خلال الزبدة.

المفتاح هو فهم بناء الجملة والمنطق وراء كل صيغة. ضع في اعتبارك أن هذه الصيغ يمكن أن تكون معقدة ، لذلك من المهم أن تأخذ وقتك وتختبر الصيغ الخاصة بك جيدًا قبل الاعتماد عليها لتحليل البيانات المهمة.

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