كيفية استخدام وظيفة Excel Match

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

تشبه وظيفة MATCH وظيفة VLOOKUP حيث تم تصنيفهما ضمن وظائف البحث / المرجع في Excel. يبحث VLOOKUP عن قيمة معينة في عمود ويعيد قيمة في نفس الصف بينما تبحث الدالة MATCH عن قيمة معينة في نطاق وتقوم بإرجاع موضع تلك القيمة.

تبحث دالة Excel MATCH عن قيمة محددة في نطاق من الخلايا أو صفيف وتقوم بإرجاع الموضع النسبي لأول ظهور لتلك القيمة في النطاق. يمكن أيضًا استخدام دالة MATCH للبحث عن قيمة معينة وإرجاع قيمتها المقابلة بمساعدة دالة INDEX (تمامًا مثل Vlookup). دعونا نرى كيفية استخدام وظيفة Excel MATCH للعثور على موضع قيمة البحث في نطاق من الخلايا.

وظيفة Excel MATCH

دالة MATCH هي دالة مضمنة في Excel وتستخدم بشكل أساسي لتحديد الموضع النسبي لقيمة البحث في عمود أو صف.

بناء جملة دالة المطابقة:

= MATCH (lookup_value، lookup_array، [match_type})

أين:

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

lookup_array - مصفوفات الخلايا التي تبحث فيها عن قيمة. يجب أن يكون عمودًا واحدًا أو صفًا واحدًا.

نوع مباراة - هي معلمة اختيارية يمكن ضبطها على 0،1 أو -1 والمعيار الافتراضي هو 1.

  • 0 يبحث عن تطابق تام ، عندما لا يتم العثور عليه ، يعرض خطأ.
  • -1 يبحث عن أصغر قيمة أكبر من أو تساوي lookup_value عندما يكون صفيف البحث بترتيب تصاعدي.
  • 1 يبحث عن أكبر قيمة أقل من أو تساوي قيمة look_up عندما يكون مصفوفة البحث بترتيب تنازلي.

البحث عن موقف تطابق تام

لنفترض أن لدينا مجموعة البيانات التالية حيث نريد العثور على موضع قيمة معينة.

تحتوي هذه الصورة على سمة alt فارغة ؛ اسم الملف الخاص به هو allthings.how-how-to-use-excel-match-function-image-1.png

في هذا الجدول ، نريد العثور على موضع اسم مدينة (ممفيس) في العمود (A2: A23) ، لذلك نستخدم هذه الصيغة:

= MATCH ("memphis"، A2: A23،0)

تم تعيين الوسيطة الثالثة على "0" لأننا نريد العثور على تطابق تام لاسم المدينة. كما يمكنك أن ترى أن اسم المدينة "ممفيس" في الصيغة مكتوب بأحرف صغيرة بينما في الجدول يكون الحرف الأول من اسم المدينة بأحرف كبيرة (ممفيس). ومع ذلك ، فإن الصيغة قادرة على العثور على موضع القيمة المحددة في النطاق المحدد. ذلك لأن دالة MATCH غير حساسة لحالة الأحرف.

ملحوظة: إذا لم يتم العثور على lookup_value في نطاق البحث أو إذا حددت نطاق بحث خاطئ ، فستُرجع الدالة الخطأ # N / A.

يمكنك استخدام مرجع خلية في الوسيطة الأولى للدالة بدلاً من القيمة المباشرة. تبحث الصيغة أدناه عن موضع القيمة في الخلية F2 وتُرجع النتيجة في الخلية F3.

البحث عن موقع مباراة تقريبية

هناك طريقتان يمكنك من خلالهما البحث عن مطابقة تقريبية أو تامة لقيمة البحث وإرجاع موضعها.

  • تتمثل إحدى الطرق في العثور على أصغر قيمة أكبر من أو تساوي (ثاني أكبر تطابق) للقيمة المحددة. يمكن تحقيقه عن طريق تعيين الوسيطة الأخيرة (نوع match_type) للوظيفة كـ "-1"
  • هناك طريقة أخرى وهي القيمة الأكبر التي تكون أقل من أو تساوي (أصغر تطابق تالي) للقيمة المحددة. يمكن تحقيق ذلك من خلال تعيين نوع match_type للوظيفة كـ "1"

التالي أصغر تطابق

إذا لم تتمكن الوظيفة من العثور على تطابق تام مع القيمة المحددة عند تعيين نوع المطابقة على "1" ، فإنها تحدد أكبر قيمة أقل قليلاً من القيمة المحددة (مما يعني القيمة الأصغر التالية) وتعيد موضعها . لكي يعمل هذا ، تحتاج إلى فرز المصفوفة بترتيب تصاعدي ، وإلا فسيؤدي ذلك إلى حدوث خطأ.

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

= MATCH (F2، D2: D23،1)

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

أكبر مباراة قادمة

عندما يتم تعيين نوع المطابقة على "-1" وتعذر على وظيفة MATCH العثور على تطابق تام ، فإنها تعثر على أصغر قيمة أكبر من القيمة المحددة (مما يعني القيمة الأكبر التالية) وتعيد موضعها. يجب فرز مصفوفة البحث بترتيب تنازلي لهذه الطريقة وإلا ستُرجع خطأً.

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

= MATCH (F2، D2: D23، -1)

تبحث دالة MATCH هذه عن القيمة الموجودة في F2 (55) في نطاق البحث D2: D23 ، وعندما لا تتمكن من العثور على التطابق التام ، فإنها تعرض الموضع (16) للقيمة التالية الأكبر ، أي 58.

مباراة البدل

يمكن استخدام أحرف البدل في دالة MATCH فقط عندما يتم تعيين match_type على "0" وتكون قيمة البحث عبارة عن سلسلة نصية. هناك أحرف بدل يمكنك استخدامها في وظيفة MATCH: علامة النجمة (*) وعلامة الاستفهام (؟).

  • علامة استفهام (؟) يستخدم لمطابقة أي حرف أو حرف واحد مع السلسلة النصية.
  • النجمة (*) يستخدم لمطابقة أي عدد من الأحرف مع السلسلة.

على سبيل المثال ، استخدمنا حرفين من أحرف البدل "؟" في lookup_value (Lo ؟؟ n) لوظيفة MATCH للعثور على قيمة تطابق السلسلة النصية بأي حرفين (في أماكن أحرف البدل). وتقوم الدالة بإرجاع الموضع النسبي للقيمة المطابقة في الخلية E5.

= MATCH ("Lo ؟؟ n"، A2: A22،0)

يمكنك استخدام (*) wildcard بنفس طريقة (؟) ، ولكن يتم استخدام علامة النجمة لمطابقة أي عدد من الأحرف أثناء استخدام علامة الاستفهام لمطابقة أي حرف واحد.

على سبيل المثال ، إذا كنت تستخدم "sp *" ، يمكن أن تتطابق الوظيفة مع مكبر الصوت ، أو speed ، أو spielberg ، وما إلى ذلك. ولكن إذا عثرت الوظيفة على قيم متعددة / مكررة تطابق قيمة البحث ، فستعيد فقط موضع القيمة الأولى.

في المثال ، أدخلنا "Kil * o" في وسيطة lookup_value. لذا فإن دالة MATCH () تبحث عن نص يحتوي على "Kil" في البداية ، و "o" في النهاية ، وأي عدد من الأحرف بينهما. تطابق "Kil * o" كليمنجارو في المصفوفة ، وبالتالي تُرجع الدالة الموضع النسبي لكليمنجارو ، وهو 16.

الفهرس والمطابقة

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

يمكن لـ VLOOKUP البحث عن قيمة عموديًا فقط ، أي الأعمدة بينما يمكن أن تقوم مجموعة INDEX MATCH بالبحث الرأسي والأفقي.

تُستخدم دالة INDEX لاسترداد قيمة في موقع محدد في جدول أو نطاق. ترجع الدالة MATCH الموضع النسبي لقيمة في عمود أو صف. عند دمجها ، تبحث MATCH عن رقم الصف أو العمود (الموقع) لقيمة معينة ، وتسترد الدالة INDEX قيمة بناءً على هذا الصف ورقم العمود.

بناء جملة دالة INDEX:

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

على أي حال ، دعونا نرى كيف تعمل INDEX MATCH مع مثال.

في المثال أدناه ، نريد استرداد نتيجة "Quiz2" للطالبة "Anne". للقيام بذلك ، سوف نستخدم الصيغة التالية:

= INDEX (B2: F20، MATCH (H2، A2: A20،0)، 3)

يحتاج INDEX إلى رقم صف وعمود لاسترداد قيمة. في الصيغة أعلاه ، تجد دالة MATCH المتداخلة رقم الصف (الموضع) للقيمة "Anne" (H2). ثم نوفر رقم الصف هذا لوظيفة INDEX بنطاق B2: F20 ورقم عمود (3) ، والذي نحدده. وتعيد الدالة INDEX النتيجة "91".

بحث ثنائي الاتجاه باستخدام INDEX و MATCH

يمكنك أيضًا استخدام الدالتين INDEX و MATCH للبحث عن قيمة في نطاق ثنائي الأبعاد (بحث ثنائي الاتجاه). في المثال أعلاه ، استخدمنا وظيفة MATCH لتحديد رقم صف القيمة ، لكننا أدخلنا رقم العمود يدويًا. ولكن يمكننا العثور على كل من الصف والعمود عن طريق تداخل دالتين MATCH ، واحدة في وسيطة row_num والأخرى في وسيطة العمود للدالة INDEX.

استخدم هذه الصيغة للبحث ثنائي الاتجاه باستخدام INDEX و MATCH:

= INDEX (A1: F20، MATCH (H2، A2: A20،0)، MATCH (H3، A1: F1،0))

كما نعلم ، يمكن لوظيفة MATCH البحث عن قيمة أفقية ورأسية. في هذه الصيغة ، تبحث دالة MATCH الثانية في وسيطة colum_num عن موضع Quiz2 (4) وتوفرها للدالة INDEX. ويسترجع الفهرس النتيجة.

الآن ، أنت تعرف كيفية استخدام وظيفة المطابقة في Excel.