01 من 06
بدء تشغيل الدالة MATCH المتداخلة
إدخال الدالة MATCH كوسيطة رقم فهرس العمود
عادةً ، لا يُرجع VLOOKUP إلا البيانات من عمود واحد في جدول البيانات ، ويتم تعيين هذا العمود بواسطة وسيطة رقم فهرس العمود .
ومع ذلك ، في هذا المثال ، لدينا ثلاثة أعمدة نرغب في العثور على البيانات فيها ، لذا نحتاج إلى طريقة لتغيير رقم فهرس العمود بسهولة دون تحرير صيغة البحث الخاصة بنا.
هذا هو المكان الذي تدخل فيه وظيفة MATCH. سيسمح لنا بمطابقة رقم عمود لاسم الحقل - سواء يناير أو فبراير أو مارس - الذي نكتبه في الخلية E2 بورقة العمل.
وظائف التعشيش
وبالتالي ، تعمل دالة MATCH كوسيطة رقم فهرسة عمود VLOOKUP.
يتم تحقيق ذلك بواسطة تداخل الدالة MATCH داخل VLOOKUP في سطر Col_index_num لمربع الحوار.
دخول الدالة MATCH يدوياً
عند تداخل الوظائف ، لا يسمح Excel لنا بفتح مربع الحوار الخاص بالوظيفة الثانية لإدخال الوسيطات الخاصة به.
لذلك ، يجب إدخال دالة MATCH يدويًا في سطر Col_index_num .
عند إدخال الوظائف يدويًا ، يجب فصل كل وسيطة من الوسيطات بفاصلة "،" .
خطوات البرنامج التعليمي
إدخال وسيطة Lookup_value الخاصة بدالة MATCH
الخطوة الأولى في إدخال الدالة MATCH المتداخلة هي إدخال وسيطة Lookup_value .
سيكون Lookup_value هو الموقع أو مرجع الخلية لمصطلح البحث الذي نطابقه في قاعدة البيانات.
- في مربع الحوار الدالة VLOOKUP ، انقر فوق سطر Col_index_num .
- اكتب تطابق اسم الوظيفة متبوعًا بقوس مستدير مفتوح " ( "
- انقر فوق الخلية E2 لإدخال مرجع الخلية هذا في مربع الحوار.
- اكتب فاصلة "،" بعد إشارة الخلية E3 لإكمال إدخال وسيطة Lookup_value الخاصة بوظيفة MATCH.
- اترك مربع حوار الدالة VLOOKUP مفتوحًا للخطوة التالية في البرنامج التعليمي.
في الخطوة الأخيرة من البرنامج التعليمي سيتم إدخال Lookup_values في الخلايا D2 و E2 من ورقة العمل .
02 من 06
إضافة Lookup_array للدالة MATCH
إضافة Lookup_array للدالة MATCH
تغطي هذه الخطوة إضافة وسيطة Lookup_array للدالة MATCH المتداخلة.
Lookup_array هو نطاق الخلايا التي سيتم البحث عن الدالة MATCH للبحث عن الوسيطة Lookup_value المضافة في الخطوة السابقة من البرنامج التعليمي.
في هذا المثال ، نريد أن تقوم وظيفة MATCH بالبحث في الخلايا من D5 إلى G5 لمطابقة اسم الشهر الذي سيتم إدخاله في الخلية E2.
خطوات البرنامج التعليمي
يجب إدخال هذه الخطوات بعد إدخال الفاصلة في الخطوة السابقة على سطر Col_index_num في مربع الحوار الدالة VLOOKUP.
- إذا لزم الأمر ، انقر فوق سطر Col_index_num بعد الفاصلة لوضع نقطة الإدراج في نهاية الإدخال الحالي.
- قم بتمييز الخلايا من D5 إلى G5 في ورقة العمل لإدخال مراجع الخلية هذه كنطاق الدالة للبحث.
- اضغط المفتاح F4 على لوحة المفاتيح لتغيير هذا النطاق إلى مراجع الخلية المطلقة . سيجعل القيام بذلك من الممكن نسخ صيغة البحث المكتملة إلى مواقع أخرى في ورقة العمل في الخطوة الأخيرة من البرنامج التعليمي
- اكتب فاصلة "،" بعد مرجع الخلية E3 لإكمال إدخال الدالة Lookup_array للدالة MATCH.
03 من 06
إضافة نوع المطابقة وإكمال وظيفة MATCH
إضافة نوع المطابقة وإكمال وظيفة MATCH
الوسيطة الثالثة والأخيرة للدالة MATCH هي الوسيطة Match_type.
توضح هذه الوسيطة Excel كيفية مطابقة Lookup_value بالقيم في Lookup_array. الاختيارات هي: -1 ، أو 0 ، أو 1.
هذه الوسيطة اختيارية. إذا تم حذفها تستخدم الدالة القيمة الافتراضية 1.
- إذا كان Match_type = 1 أو تم تجاهله: MATCH العثور على أكبر قيمة أقل من أو يساوي Lookup_value. إذا تم اختيار هذه القيمة ، يجب فرز بيانات Lookup_array بترتيب تصاعدي.
- إذا وجد Match_type = 0: MATCH القيمة الأولى التي تساوي بالضبط Lookup_value. يمكن فرز بيانات Lookup_array بأي ترتيب.
- إذا وجد Match_type = 1: MATCH أصغر قيمة أكبر من أو تساوي Lookup_value. إذا تم اختيار هذه القيمة ، يجب فرز بيانات Lookup_array بترتيب تنازلي.
خطوات البرنامج التعليمي
يجب إدخال هذه الخطوات بعد إدخال الفاصلة في الخطوة السابقة على سطر Row_num في مربع حوار الدالة VLOOKUP.
- بعد الفاصلة الثانية على سطر Col_index_num ، اكتب صفر " 0 " لأننا نريد أن تقوم الدالة المتداخلة بإرجاع مطابقة تامة للشهر أدخل في الخلية E2.
- اكتب قوس مستدير إغلاق " ) " لإكمال وظيفة MATCH.
- اترك مربع حوار الدالة VLOOKUP مفتوحًا للخطوة التالية في البرنامج التعليمي.
04 من 06
دخول VLOOKUP نطاق بحث وسيطة
وسيطة البحث المدى
تعتبر وسيطة Range_lookup الخاصة بـ VLOOKUP قيمة منطقية (TRUE أو FALSE فقط) تشير إلى ما إذا كنت تريد VLOOKUP للعثور على تطابق دقيق أو تقريبي لـ Lookup_value.
- إذا تم حذف TRUE أو إذا تم حذف هذه الوسيطة ، تقوم VLOOKUP بإرجاع مطابقة تامة إلى Lookup_value ، أو إذا لم يتم العثور على تطابق تام ، تقوم VLOOKUP بارجاع القيمة التالية التالية. بالنسبة إلى الصيغة للقيام بذلك ، يجب فرز البيانات الموجودة في العمود الأول من Table_array بترتيب تصاعدي .
- إذا كانت FALSE ، فلن تستخدم VLOOKUP سوى المطابقة التامة إلى Lookup_value. إذا كان هناك قيمتين أو أكثر في العمود الأول من Table_array تتطابق مع قيمة البحث ، فسيتم استخدام أول قيمة تم العثور عليها. في حالة عدم العثور على تطابق تام ، يتم إرجاع خطأ # N / A.
في هذا البرنامج التعليمي ، نظرًا لأننا نبحث عن أرقام المبيعات لشهر معين ، سنقوم بتعيين Range_lookup تساوي False .
خطوات البرنامج التعليمي
- انقر فوق سطر Range_lookup في مربع الحوار
- اكتب الكلمة False في هذا السطر للإشارة إلى أننا نريد VLOOKUP لعرض مطابقة تامة للبيانات التي نسعى إليها
- انقر فوق "موافق" لإكمال صيغة البحث ثنائية الأبعاد وإغلاق مربع الحوار
- نظرًا لأننا لم ندخل بعد معايير البحث في الخانتين D2 و E2 ، فإن الخطأ # N / A سيكون موجودًا في الخلية F2
- سيتم تصحيح هذا الخطأ في الخطوة التالية في البرنامج التعليمي عندما نضيف معايير البحث في الخطوة التالية من البرنامج التعليمي.
05 من 06
اختبار صيغة البحث عن طريق اثنين
اختبار صيغة البحث عن طريق اثنين
لاستخدام صيغة البحث ثنائية الاتجاه للعثور على بيانات المبيعات الشهرية لملفات تعريف الارتباط المختلفة المدرجة في صفيف الجدول ، اكتب اسم ملف تعريف الارتباط في الخلية D2 ، والشهر في الخلية E2 ثم اضغط على المفتاح ENTER على لوحة المفاتيح.
سيتم عرض بيانات المبيعات في الخلية F2.
خطوات البرنامج التعليمي
- انقر فوق الخلية D2 في ورقة العمل الخاصة بك
- اكتب دقيق الشوفان في الخلية D2 واضغط على المفتاح ENTER على لوحة المفاتيح
- انقر فوق الخلية E2
- اكتب فبراير في الخلية E2 ثم اضغط المفتاح ENTER على لوحة المفاتيح
- يجب عرض القيمة $ 1،345 - مقدار مبيعات ملفات تعريف الارتباط الشوفان في شهر فبراير - في الخلية F2
- في هذه المرحلة ، يجب أن تطابق ورقة العمل الخاصة بك المثال الموجود في الصفحة 1 من هذا البرنامج التعليمي
- اختبر صيغة البحث بشكل أكبر عن طريق كتابة أي مجموعة من أنواع ملفات تعريف الارتباط والأشهر الموجودة في Table_array ويجب عرض أرقام المبيعات في الخلية F2
- تغطي الخطوة الأخيرة في البرنامج التعليمي نسخ صيغة البحث باستخدام مقبض التعبئة .
إذا ظهرت رسالة خطأ مثل #REF! يظهر في الخلية F2 ، قد تساعدك هذه القائمة من رسائل الخطأ VLOOKUP في تحديد مكان تكمن المشكلة.
06 من 06
نسخ صيغة البحث ثنائية الأبعاد مع مقبض التعبئة
نسخ صيغة البحث ثنائية الأبعاد مع مقبض التعبئة
لتبسيط مقارنة البيانات لأشهر مختلفة أو ملفات تعريف ارتباط مختلفة ، يمكن نسخ صيغة البحث إلى خلايا أخرى بحيث يمكن عرض مبالغ متعددة في نفس الوقت.
نظرًا لأن البيانات موضوعة بنمط عادي في ورقة العمل ، فيمكننا نسخ صيغة البحث في الخلية F2 إلى الخلية F3.
عند نسخ الصيغة ، سيقوم Excel بتحديث مراجع الخلية النسبية لإظهار الموقع الجديد للصيغة. في هذه الحالة ، يصبح D2 D3 ويصبح E2 E3 ،
بالإضافة إلى ذلك ، يحتفظ Excel بمرجع الخلية المطلق نفسه ، لذلك فإن النطاق المطلق $ D $ 5: $ G $ 5 يظل كما هو عند نسخ الصيغة.
هناك أكثر من طريقة لنسخ البيانات في Excel ، ولكن ربما تكون أسهل طريقة هي استخدام مقبض التعبئة.
خطوات البرنامج التعليمي
- انقر فوق الخلية D3 في ورقة العمل الخاصة بك
- اكتب دقيق الشوفان في الخلية D3 واضغط على المفتاح ENTER على لوحة المفاتيح
- انقر فوق الخلية E3
- اكتب مارس في الخلية E3 ثم اضغط المفتاح ENTER على لوحة المفاتيح
- انقر فوق الخلية F2 لجعلها الخلية النشطة
- ضع مؤشر الماوس فوق المربع الأسود في الزاوية السفلية اليمنى. سيتغير المؤشر إلى علامة الجمع "+" - وهذا هو مقبض التعبئة
- انقر فوق زر الماوس الأيسر واسحب مقبض التعبئة إلى الخلية F3
- حرر زر الماوس ويجب أن تحتوي الخلية F3 على صيغة البحث ثنائية الأبعاد
- يجب عرض القيمة $ 1،287 - مقدار مبيعات ملفات تعريف الارتباط الشوفان في شهر مارس - في الخلية F3