دالة MATCH في Microsoft Excel
واحدة من المشغلات الأكثر شعبية بين مستخدمي Excel هي وظيفة MATCHING . وتتمثل مهمتها في تحديد رقم العنصر للعنصر في مجموعة البيانات المعينة. أكبر فائدة يجلبها ، عند استخدامها بالاقتران مع المشغلين الآخرين. دعونا نرى ما هي وظيفة MATCH ، وكيف يمكن استخدامها في الممارسة.
محتوى
باستخدام مشغل MAP
ينتمي عامل تشغيل MAP إلى الفئة "ارتباطات ومصفوفات" . يبحث عن العنصر المحدد في المصفوفة المحددة ويعيد عدد موضعه في هذا النطاق إلى خلية منفصلة. في الواقع ، حتى اسمها يشير إلى هذا. أيضا ، عند دمجها مع المشغلين الآخرين ، تقوم هذه الدالة بإعلامهم برقم موضع عنصر معين للمعالجة اللاحقة لهذه البيانات.
تبدو صيغة جملة MATCH كالتالي:
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
الآن النظر في كل من هذه الحجج الثلاث على حدة.
"البحث عن المعنى" هو العنصر الذي يجب العثور عليه. يمكن أن يكون لها صيغة نصية رقمية ، كما أنها تأخذ قيمة منطقية. يمكن أن تكون هذه الوسيطة أيضًا مرجعًا لخلية تحتوي على أي من القيم المذكورة أعلاه.
"المصفوفة الممسوحة ضوئياً" هو عنوان النطاق الذي توجد فيه القيمة. هو موضع هذا العنصر في هذه الصفيف الذي يجب أن يحدده عامل تشغيل MAP .
يشير "نوع المطابقة " إلى المطابقة التامة التي تريد البحث عنها أو عدم دقتها. يمكن أن تحتوي هذه الوسيطة على ثلاث قيم: "1" و "0" و "-1" . إذا كانت القيمة "0" ، فإن المشغل يبحث فقط عن تطابق تام. إذا تم تحديد القيمة "1" ، في حالة عدم وجود تطابق تام ، ينتج MATCH أقرب عنصر إليها بترتيب تنازلي. إذا كانت القيمة "-1" ، ففي الحالة إذا لم يتم العثور على مطابقة تامة ، فإن الدالة تنتج العنصر الأقرب إليها بترتيب تصاعدي. من المهم ، إذا لم يكن البحث قيمة دقيقة ، ولكنه تقريبي ، بحيث يتم ترتيب المصفوفة المراد فحصها بترتيب تصاعدي (نوع التعيين "1" ) أو تنازلي (نوع التعيين "-1" ).
تعتبر وسيطة "نوع المطابقة" اختيارية. يمكن تفويتها إذا لم تكن هناك حاجة إليها. في هذه الحالة ، تكون قيمته الافتراضية هي "1" . إن تطبيق الوسيطة "نوع المطابقة" أولاً لا معنى له إلا عند معالجة القيم الرقمية وليس النص.
إذا لم يتمكن MATCH للإعدادات المعينة من العثور على العنصر المطلوب ، فستعرض عامل التشغيل الخطأ "# N / D" في الخلية.
عند إجراء بحث ، لا يميز المشغل بين سجلات الأحرف. إذا كان هناك العديد من التطابقات الدقيقة في الصفيف ، فسيعرض MATCHING موضع أول واحد في الخلية.
الطريقة الأولى: عرض مكان عنصر في نطاق من بيانات النص
لننظر إلى مثال الحالة الأبسط ، عند استخدام MATCH ، يمكنك تحديد موقع العنصر المحدد في مصفوفة من البيانات النصية. سوف نكتشف أي موضع في النطاق الذي توجد فيه أسماء السلع ، هي كلمة "سكر" .
- حدد الخلية التي سيتم إخراج النتيجة التي تمت معالجتها. انقر فوق رمز "إدراج الدالة" بجوار شريط الصيغة.
- يبدأ معالج الدالة . افتح الفئة "قائمة أبجدية كاملة" أو "ارتباطات ومصفوفات" . في قائمة المشغلين ، نبحث عن اسم "MATCH" . بعد العثور عليها وتحديدها ، انقر فوق الزر "موافق" في أسفل النافذة.
- يتم تنشيط نافذة وسائط مشغل MAP . كما ترى ، في هذه النافذة ، هناك ثلاثة حقول وفقًا لعدد الوسائط. يجب أن نملأهم
وبما أننا نحتاج إلى العثور على موضع كلمة "سكر" في النطاق ، فإننا نقود هذا الاسم إلى الحقل "البحث عن القيمة" .
في الحقل "المصفوفة الممسوحة ضوئيًا" تحتاج إلى تحديد إحداثيات النطاق نفسه. يمكن أن يتم إمساكها باليد ، ولكن من السهل وضع المؤشر في الحقل وتحديد هذا المصفوفة على الورقة ، مع الضغط باستمرار على زر الماوس الأيسر. بعد ذلك ، سيتم عرض عنوانه في نافذة الحجج.
في حقل "نوع المطابقة" الثالث ، نضع الرقم "0" ، لأننا سنعمل مع البيانات النصية ، وبالتالي نحتاج إلى نتيجة دقيقة.
بعد ضبط جميع البيانات ، انقر على زر "موافق" .
- يقوم البرنامج بحساب وعرض الرقم الترتيبي لموضع "السكر" في المصفوفة المحددة في الخلية التي حددناها في الخطوة الأولى من هذه التعليمات. سيكون رقم الموقف هو "4" .
الطريقة الثانية: أتمتة استخدام عامل التشغيل MATCH
في ما سبق ، نظرنا في الحالة الأكثر بدائية في استخدام مُشغل POSITION ، ولكن حتى يمكن تشغيلها تلقائيًا.
- لتوفير الراحة ، على الورقة ، أضف حقلين إضافيين: "القيمة المحددة" و "الرقم" . في الحقل "القيمة المُخصصة " ، نقود الاسم الذي نحتاج إلى العثور عليه. الآن دعها تكون "اللحوم" . في حقل "الرقم" ، اضبط المؤشر وانتقل إلى نافذة وسائط المشغل بنفس الطريقة التي تمت مناقشتها أعلاه.
- في نافذة وسائط الدوال ، في الحقل "قيمة البحث" ، نحدد عنوان الخلية حيث تكون كلمة "Meat" مكتوبة. في حقلي "المصفوفة الممسوحة ضوئيًا" و "نوع التعيين" ، نحدد نفس البيانات كما في الطريقة السابقة - عنوان النطاق والرقم "0" ، على التوالي. بعد ذلك ، انقر على زر "موافق" .
- بعد أن نتخذ الإجراءات المذكورة أعلاه ، يعرض حقل "الرقم" موضع الكلمة "Meat" في النطاق المحدد. في هذه الحالة ، تساوي "3" .
- هذه الطريقة جيدة لأننا إذا أردنا معرفة موضع أي اسم آخر ، فلن نضطر إلى إعادة كتابة الصيغة أو تغييرها في كل مرة. من السهل جدًا في الحقل "القيمة المحددة" إدخال كلمة بحث جديدة بدلاً من الكلمة السابقة. ستتم معالجة وإخراج النتيجة بعد ذلك تلقائيًا.
الطريقة الثالثة: استخدم عامل التشغيل MAP للتعابير الرقمية
الآن دعنا ننظر في كيفية استخدام MATCHING للعمل مع التعبيرات الرقمية.
وتتمثل المهمة في العثور على السلع لمبلغ تحقيق 400 روبل أو أقرب إلى هذا المبلغ بترتيب تصاعدي.
- أولاً ، نحتاج إلى فرز العناصر الموجودة في عمود "المبلغ" بترتيب تنازلي. حدد هذا العمود وانتقل إلى علامة التبويب "الرئيسية" . نضغط على أيقونة "الفرز والتصفية" ، الموجودة على الشريط في كتلة "تحرير" . في القائمة التي تظهر ، حدد "التصنيف من الحد الأقصى إلى الحد الأدنى" .
- بعد الانتهاء من الفرز ، حدد الخلية التي سيتم عرض النتيجة فيها ، ثم قم بتشغيل نافذة الوسائط بالطريقة نفسها كما هو موضح في الطريقة الأولى.
في حقل "قيمة البحث" ، نقود رقم "400" . في حقل "الصفيف الممسوح ضوئيًا" ، حدد إحداثيات عمود "المبلغ" . في حقل "نوع المطابقة" ، قم بتعيين القيمة إلى "-1" ، بينما نبحث عن قيمة مساوية أو أكبر من القيمة التي نبحث عنها. بعد الانتهاء من جميع الإعدادات ، انقر فوق الزر "موافق" .
- يتم عرض نتيجة المعالجة في الخلية المحددة مسبقًا. هذا هو الموقف "3" . إنها تقابل "البطاطا" . في الواقع ، فإن مبلغ الإيرادات من بيع هذا المنتج هو الأقرب إلى 400 في ترتيب تصاعدي وهو 450 روبل.
وبالمثل ، يمكنك البحث عن أقرب موضع إلى "400" بترتيب تنازلي. لهذا الغرض فقط ، من الضروري تصفية البيانات بترتيب تصاعدي ، وفي حقل "نوع المطابقة" في وسيطات الدالة ، اضبط القيمة على "1" .
الدرس: فرز وتصفية البيانات في Excel
الطريقة الرابعة: استخدام بالاقتران مع عوامل تشغيل أخرى
من الأكثر فاعلية استخدام هذه الوظيفة مع المشغلين الآخرين كجزء من صيغة معقدة. في معظم الأحيان يتم استخدامه جنبا إلى جنب مع وظيفة INDEX . تقوم هذه الوسيطة بإخراج محتويات النطاق المحدد بواسطة رقم صفها أو عمودها إلى الخلية المحددة. ولا يتم تنفيذ الترقيم ، كما هو الحال في مشغل POSITION ، بالنسبة إلى الورقة بأكملها ، ولكن ضمن النطاق فقط. بناء جملة هذه الوظيفة هو:
=ИНДЕКС(массив;номер_строки;номер_столбца)
في هذه الحالة ، إذا كان المصفوفة أحادية البعد ، فيمكنك استخدام واحدة فقط من الوسيطتين: "رقم الخط" أو "رقم العمود" .
تكمن خصوصية الارتباط بين دالات INDEX و MATCH في أنه يمكن استخدام هذا الأخير كوسيطة أولى ، أي ، أشر إلى موضع صف أو عمود.
دعونا نلقي نظرة على كيفية القيام بذلك في الممارسة العملية ، وذلك باستخدام نفس الجدول. لدينا مهمة لإخراج اسم البضاعة في الحقل الإضافي لورقة "السلع" ، ويبلغ إجمالي إيراداتها 350 روبل أو الأقرب إلى هذه القيمة بترتيب تنازلي. يشار إلى هذه الحجة في الحقل "المبلغ التقريبي للإيرادات على الورقة" .
- صنّف العناصر في عمود "مبلغ الإيرادات" بترتيب تصاعدي. للقيام بذلك ، حدد العمود المطلوب ، وفي علامة التبويب "الصفحة الرئيسية" ، انقر فوق الرمز "فرز وتصفية" ، ثم انقر فوق "فرز من الحد الأدنى إلى الحد الأقصى" في القائمة التي تظهر.
- حدد الخلية في حقل "المنتج" واستدعاء معالج الدالة بالطريقة المعتادة من خلال الزر "إدراج الدالة" .
- في النافذة المفتوحة في معالج الوظائف في الفئة "روابط ومصفوفات" ، نقوم بالبحث عن الاسم "INDEX" ، وتحديده والنقر فوق الزر "موافق" .
- بعد ذلك ، يتم فتح نافذة توفر خيار الخيار INDEX : لمجموعة أو كمرجع. نحن بحاجة إلى الخيار الأول. لذلك ، نترك جميع الإعدادات الافتراضية في هذه النافذة ونضغط على زر "موافق" .
- يفتح نافذة الوسائط لوظيفة INDEX . في حقل "Array" ، حدد عنوان النطاق الذي سيبحث فيه عامل التشغيل INDEX عن اسم المنتج. في حالتنا ، هذا هو عمود "اسم المنتج" .
سوف يحتوي حقل "رقم السطر" على الدالة MATCH المتداخلة. يجب أن يتم إبرامها يدوياً باستخدام الصيغة المذكورة في بداية المقال. اكتب اسم الدالة فورًا - "MATCH" بدون علامات اقتباس. ثم افتح القوس. الحجة الأولى لهذا المشغل هي "قيمة الفائدة" . يقع على الورقة في الحقل "المبلغ التقريبي للإيرادات" . حدد إحداثيات الخلية التي تحتوي على الرقم 350 . ضع فاصلة منقوطة. الوسيطة الثانية هي "المصفوفة الممسوحة ضوئيا" . سيظهر " المطابقة" في النطاق الذي تم العثور فيه على مقدار العائد والبحث عن الأكثر تقريبًا إلى 350 روبل. لذلك ، في هذه الحالة ، نشير إلى إحداثيات عمود "مبلغ الأرباح" . مرة أخرى ، نضع فاصلة منقوطة. الوسيطة الثالثة هي "نوع المطابقة" . نظرًا لأننا نبحث عن رقم مساوٍ للرقم المعين أو أصغر ، فإننا نضع الرقم "1" هنا . أغلق الأقواس.
يتم ترك الوسيطة الثالثة الدالة INDEX "رقم العمود" فارغة. بعد ذلك ، انقر على زر "موافق" .
- كما ترى ، تعرض الدالة INDEX بمساعدة عامل POSITION في الخلية المحددة مسبقًا اسم "Tea" . في الواقع ، فإن المبلغ من بيع الشاي (300 روبل) هو الأقرب في ترتيب تنازلي لمجموع 350 روبل من جميع القيم في الجدول معالجتها.
- إذا قمنا بتغيير الرقم في الحقل "مبلغ الأرباح التقريبي" إلى آخر ، فسيتم تلقائيًا إعادة احتساب محتويات حقل "المنتج" وفقًا لذلك.
وكما ترى ، فإن مشغل MAP هو وظيفة ملائمة للغاية لتحديد العدد الترتيبي للعنصر المحدد في مصفوفة البيانات. لكن الاستفادة منها تزداد بشكل كبير إذا تم استخدامها في صيغ معقدة.