من خلال دمج وظيفة VLOOKUP الخاصة بـ Excel مع وظيفة MATCH ، يمكننا إنشاء ما يُعرف باسم صيغة البحث ثنائية الاتجاه أو ثنائية الأبعاد التي تسمح لك بسهولة بالرجوع إلى حقلين للمعلومات في قاعدة بيانات أو جدول بيانات.
تكون صيغة البحث ذات الاتجاهين مفيدة عندما ترغب في العثور على النتائج أو المقارنة بينها لمجموعة متنوعة من المواقف المختلفة.
في المثال الموضح في الصورة أعلاه ، تجعل صيغة البحث من السهل استرداد أرقام المبيعات لملفات تعريف الارتباط المختلفة في الأشهر المختلفة ببساطة عن طريق تغيير اسم ملف تعريف الارتباط والشهر في الخلايا الصحيحة.
01 من 06
العثور على البيانات في نقطة التقاطع من صف وعمود
يتم تقسيم هذا البرنامج التعليمي إلى قسمين. يؤدي اتباع الخطوات المذكورة في كل جزء إلى إنشاء صيغة البحث ذات الاتجاهين في الصورة أعلاه.
يتضمن البرنامج التعليمي تعشيش وظيفة MATCH داخل VLOOKUP.
تتضمن وظيفة التعشيق إدخال دالة ثانية كواحدة من الوسيطات للدالة الأولى.
في هذا البرنامج التعليمي ، سيتم إدخال الدالة MATCH كوسيطة رقم فهرس العمود لـ VLOOKUP.
محتويات البرنامج التعليمي
- دخول بيانات البرنامج التعليمي
- إنشاء نطاق مسمى لجدول البيانات
- بدء تشغيل وظيفة VLOOKUP
- الدخول إلى وسيطة قيمة البحث
- دخول جدول المصفوفة الحجة
- بدء تشغيل الدالة MATCH المتداخلة
- إضافة نطاقات البيانات لوظيفة MATCH
- إضافة نوع المطابقة وإكمال وظيفة MATCH
- استكمال وظيفة VLOOKUP
- إضافة معايير البحث لاختبار الصيغة المكتملة
- نسخ صيغة البحث ثنائية الأبعاد مع مقبض التعبئة
02 من 06
دخول بيانات البرنامج التعليمي
الخطوة الأولى في البرنامج التعليمي هي إدخال البيانات في ورقة عمل Excel.
من أجل اتباع الخطوات الواردة في البرنامج التعليمي ، أدخل البيانات الموضحة في الصورة أعلاه في الخلايا التالية.
- أدخل النطاق الأعلى للبيانات في الخلايا من D1 إلى F1
- أدخل النطاق الثاني في الخلايا من D4 إلى G8
يتم ترك الصفوف 2 و 3 فارغة من أجل استيعاب معايير البحث وصيغة البحث التي تم إنشاؤها خلال هذا البرنامج التعليمي.
لا يتضمن البرنامج التعليمي التنسيق المرئي في الصورة ، ولكن هذا لن يؤثر في كيفية عمل صيغة البحث.
تتوفر معلومات حول خيارات التنسيق المشابهة لتلك المعروضة أعلاه في " دروس تنسيق Excel Basic" .
خطوات البرنامج التعليمي
- أدخل البيانات كما يظهر في الصورة أعلاه في الخلايا من D1 إلى G8
03 من 06
إنشاء نطاق مسمى لجدول البيانات
النطاق المحدد هو طريقة سهلة للإشارة إلى نطاق من البيانات في صيغة. بدلاً من كتابة مراجع الخلية للبيانات ، يمكنك فقط كتابة اسم النطاق.
الميزة الثانية لاستخدام نطاق مسمى هي أن مراجع الخلية لهذا النطاق لا تتغير أبدًا حتى عند نسخ الصيغة إلى خلايا أخرى في ورقة العمل.
خطوات البرنامج التعليمي
- قم بتمييز الخلايا من D5 إلى G8 في ورقة العمل لتحديدها
- انقر فوق " مربع اسم" الموجود فوق العمود A
- اكتب "الجدول" (لا يوجد علامات اقتباس) في "مربع الاسم"
- اضغط على المفتاح ENTER على لوحة المفاتيح
- الخلايا من D5 إلى G8 لديها الآن اسم نطاق "الجدول". سنستخدم الاسم لوسيطة صفيف جدول VLOOKUP لاحقًا في البرنامج التعليمي
04 من 06
فتح مربع حوار VLOOKUP
على الرغم من أنه من الممكن فقط كتابة صيغة البحث الخاصة بنا مباشرة في خلية في ورقة عمل ، إلا أن العديد من الأشخاص يجدون صعوبة في الحفاظ على بناء الجملة مستقيمة - خاصة لصيغة معقدة مثل تلك التي نستخدمها في هذا البرنامج التعليمي.
بديل ، في هذه الحالة ، هو استخدام مربع الحوار VLOOKUP. تحتوي جميع وظائف Excel تقريبًا على مربع حوار يسمح لك بإدخال كل من وسائط الدالة على سطر منفصل.
خطوات البرنامج التعليمي
- انقر فوق الخلية F2 لورقة العمل - الموقع حيث سيتم عرض نتائج صيغة البحث ثنائية الأبعاد
- انقر فوق علامة التبويب صيغ من الشريط
- انقر فوق خيار البحث والمرجع في الشريط لفتح القائمة المنسدلة الدالة
- انقر فوق VLOOKUP في القائمة لإظهار مربع الحوار الخاص بالوظيفة
05 من 06
الدخول إلى وسيطة قيمة البحث
عادةً ، تطابق قيمة البحث حقل بيانات في العمود الأول من جدول البيانات.
في مثالنا ، تشير قيمة البحث إلى نوع ملف تعريف الارتباط الذي نريد العثور على معلومات عنه.
الأنواع المسموح بها للبيانات لقيمة البحث هي:
- بيانات النص
- قيمة منطقية (TRUE أو FALSE فقط)
- رقم
- مرجع خلية لقيمة في ورقة العمل
في هذا المثال ، سنقوم بإدخال مرجع الخلية إلى حيث سيكون اسم ملف تعريف الارتباط - الخلية D2.
خطوات البرنامج التعليمي
- انقر فوق خط lookup_value في مربع الحوار
- انقر فوق الخلية D2 لإضافة مرجع الخلية هذا إلى خط lookup_value . هذه هي الخلية حيث سنكتب اسم ملف تعريف الارتباط الذي نسعى للحصول على معلومات عنه
06 من 06
دخول جدول المصفوفة الحجة
مصفوفة الجدول هي جدول البيانات التي تبحث عنها صيغة البحث للعثور على المعلومات التي نريدها.
يجب أن يحتوي مصفوفة الجدول على عمودين على الأقل من البيانات .
- يحتوي العمود الأول على وسيطة قيمة البحث (الخطوة السابقة في البرنامج التعليمي)
- سيتم البحث في الثانية ، وأي أعمدة إضافية ، بواسطة صيغة البحث للعثور على المعلومات التي نحددها.
يجب إدخال وسيطة صفيف الجدول إما كنطاق يحتوي على مراجع الخلية لجدول البيانات أو كاسم نطاق .
في هذا المثال ، سنستخدم اسم النطاق الذي تم إنشاؤه في الخطوة 3 من هذا البرنامج التعليمي.
خطوات البرنامج التعليمي
- انقر على سطر table_array في مربع الحوار
- اكتب "جدول" (بدون علامتي اقتباس) لإدخال اسم النطاق لهذه الوسيطة
- اترك مربع حوار الدالة VLOOKUP مفتوحًا للجزء التالي من البرنامج التعليمي