يتم استخدام وظيفة VLOOKUP في Excel للبحث عن معلومات من جدول البيانات وإعادتها استنادًا إلى قيمة بحث تختارها.
عادة ، يتطلب VLOOKUP أن تكون قيمة البحث في العمود الموجود في أقصى اليسار من جدول البيانات ، وتقوم الدالة بإرجاع حقل بيانات آخر موجود في نفس الصف إلى يمين هذه القيمة.
ومع ذلك ، من خلال دمج VLOOKUP مع وظيفة CHOOSE ، يمكن إنشاء صيغة بحث يسرى:
- يسمح لقيمة البحث أن تكون من أي عمود في جدول البيانات
- إرجاع المعلومات الموجودة في أي عمود على يمين قيمة البحث
ملحوظة: تنطبق هذه الإرشادات على إصدارات Excel 2019 و 2016 و 2013 و 2010 لـ Office 365.
01 من 05البرنامج التعليمي: استخدام VLOOKUP و CHOOSE دالات في صيغة بحث Left
لإنشاء صيغة البحث الأيسر التي تظهر في صورة المثال ، استخدم الصيغة التالية:
= VLOOKUP ($ D $ 2، واختيار ({1،2}، $ F: $ F، $ D: D $)، 2، FALSE)
في هذا المثال ، تجعل الصيغة من الممكن العثور على الجزء الموفر من قبل الشركات المختلفة المدرجة في العمود 3 من جدول البيانات.
مهمة وظيفة CHOOSE في الصيغة هي خداع VLOOKUP للاعتقاد بأن العمود 3 هو العمود 1. ونتيجة لذلك ، يمكن استخدام اسم الشركة كقيمة البحث للعثور على اسم الجزء الذي قدمته كل شركة.
أدخل بيانات البرنامج التعليمي
- أدخل العنوان المورد في الخلية D1.
- أدخل العنوان جزء في الخلية E1.
- أدخل جدول البيانات في الصورة أعلاه في الخلايا D4 إلى F9
- الصفوف 2 و 3 تركت فارغة لاستيعاب معايير البحث وصيغة البحث الأيسر التي تم إنشاؤها خلال هذا البرنامج التعليمي
افتح مربع حوار VLOOKUP
على الرغم من أنه من الممكن كتابة الصيغة مباشرة في الخلية F1 في ورقة العمل ، يواجه العديد من الأشخاص صعوبة في بناء جملة الصيغة.
في هذه الحالة ، يكون أكثر أماناً استخدام مربع الحوار VLOOKUP. تحتوي جميع وظائف Excel تقريبًا على مربع حوار يسمح لك بإدخال كل من وسائط الدالة على سطر منفصل.
- انقر فوق الخلية E2 من ورقة العمل. E2 هو الموقع حيث سيتم عرض نتائج صيغة البحث الأيسر.
- اضغط على الصيغ علامة من الشريط.
- اضغط على البحث والمراجع الخيار في الشريط لفتح القائمة المنسدلة الدالة.
- انقر فوق VLOOKUP في القائمة لإظهار مربع الحوار الخاص بالوظيفة.
دخول الحجج في صندوق حوار VLOOKUP
وسائط الدالة هي القيم التي تستخدمها الدالة لحساب النتيجة.
في مربع الحوار الخاص بالوظيفة ، يكون اسم كل وسيطة على سطر منفصل متبوعًا بحقل يتم إدخال قيمة فيه.
أدخل القيم التالية لكل واحدة من وسيطات VLOOKUP على السطر الصحيح لمربع الحوار كما هو موضح في الصورة المرفقة.
قيمة البحث
قيمة البحث هي حقل المعلومات المستخدمة للبحث في صفيف الجدول. تُرجع VLOOKUP حقلًا آخر من البيانات من نفس صف قيمة البحث.
يستخدم هذا المثال مرجع خلية إلى الموقع حيث سيتم إدخال اسم الشركة في ورقة العمل. ميزة هذا أنه يجعل من السهل تغيير اسم الشركة دون تحرير الصيغة.
- اضغط على ابحث عن القيمة خط في مربع الحوار.
- انقر فوق الخلية D2 لإضافة هذه الخلية المرجعية إلى ابحث عن القيمة خط.
- اضغط على F4 المفتاح على لوحة المفاتيح لجعل مرجع الخلية مطلقًا - $ D $ 2.
مراجع الخلية المطلقة
يتم استخدام مراجع الخلية المطلقة لقيمة البحث ووسائط صفيف الجدول لمنع الأخطاء إذا تم نسخ صيغة البحث إلى خلايا أخرى في ورقة العمل.
دخول وظيفة CHOOSE
وسيطة صفيف الجدول هي مجموعة من البيانات المتجاورة التي يتم استرداد معلومات محددة منها.
عادة ، يبدو VLOOKUP فقط إلى يمين وسيطة قيمة البحث للعثور على البيانات في صفيف الجدول. للحصول على مظهر اليسار ، يجب خداع VLOOKUP عن طريق إعادة ترتيب الأعمدة في صفيف الجدول باستخدام الدالة CHOOSE.
في هذه الصيغة ، تحقق الدالة CHOOSE مهمتين:
- يقوم بإنشاء صفيف جدول بعرض عمودين فقط (الأعمدة D و F).
- يتغير ترتيب الأعمدة من اليمين إلى اليسار في صفيف الجدول بحيث يأتي العمود F أولاً ويحتل العمود الثاني.
دخول وظائف
عند إدخال الوظائف يدويًا ، يجب فصل كل وسيطة من الوسيطات بفاصلة.
- في مربع حوار الدالة VLOOKUP ، انقر فوق Table_array خط.
- أدخل التالي أختر وظيفة:اختيار ({1،2}، $ F: $ F، $ D: D $)
رقم فهرس العمود
عادةً ما يشير رقم فهرس العمود إلى عمود صفيف الجدول الذي يحتوي على البيانات التي تتابعها. ومع ذلك ، في هذه الصيغة ، يشير إلى ترتيب الأعمدة التي تم تعيينها بواسطة الدالة CHOOSE.
تنشئ الدالة CHOOSE صفيف جدول بعرض عمودين مع العمود F أولاً متبوعًا بالعمود D. نظرًا لأن المعلومات المطلوبة - اسم الجزء - موجودة في العمود D ، يجب تعيين قيمة وسيطة فهرس الأعمدة إلى 2.
- اضغط على Col_index_num خط في مربع الحوار.
- نوع أ 2 في هذا الخط.
مجموعة البحث
تعتبر وسيطة Range_lookup الخاصة بـ VLOOKUP قيمة منطقية (TRUE أو FALSE فقط) تشير إلى ما إذا كنت تريد VLOOKUP للعثور على تطابق دقيق أو تقريبي لقيمة البحث.
- إذا تم حذف TRUE أو إذا تم حذف هذه الوسيطة ، تقوم VLOOKUP بإرجاع مطابقة تامة إلى Lookup_value ، أو إذا لم يتم العثور على تطابق تام ، تقوم VLOOKUP بارجاع القيمة الأكبر التالية. بالنسبة إلى الصيغة للقيام بذلك ، يجب فرز البيانات الموجودة في العمود الأول من Table_array بترتيب تصاعدي.
- إذا كانت FALSE ، VLOOKUP تستخدم فقط مطابقة تامة لـ Lookup_value. إذا كان هناك قيمتين أو أكثر في العمود الأول من Table_array تتطابق مع قيمة البحث ، فسيتم استخدام أول قيمة تم العثور عليها. في حالة عدم العثور على تطابق تام ، يتم إرجاع خطأ # N / A.
في هذا البرنامج التعليمي ، نظرًا لأننا نبحث عن اسم جزء معين ، يتم تعيين Range_lookup على خاطئة بحيث يتم إرجاع المطابقات بالضبط بواسطة الصيغة.
- اضغط على مجموعة البحث خط في مربع الحوار.
- اكتب الكلمة خاطئة في هذا السطر للإشارة إلى أننا نريد VLOOKUP لعرض مطابقة تامة للبيانات التي نسعى إليها.
- انقر حسنا لإكمال صيغة البحث الأيسر وإغلاق مربع الحوار.
- نظرًا لأننا لم ندخل بعد اسم الشركة في الخلية D2 ، يظهر خطأ # N / A في الخلية E2.
إرجاع البيانات باستخدام صيغة البحث الأيسر
للعثور على الشركات التي توفر قطع الغيار ، اكتب اسم الشركة في الخلية D2 واضغط على أدخل المفتاح على لوحة المفاتيح.
يتم عرض اسم الجزء في الخلية E2.
- انقر فوق الخلية D2 في ورقة العمل الخاصة بك.
- نوع أدوات بلاس في الخلية D2 واضغط على أدخل المفتاح على لوحة المفاتيح.
- يجب عرض النص "الأدوات الذكية" - الجزء الذي توفره الشركة Gadgets Plus - في الخلية E2.
اختبر صيغة البحث أكثر عن طريق كتابة أسماء شركات أخرى في الخلية D2 ، ويجب أن يظهر اسم الجزء المقابل في الخلية E2.
في حالة ظهور رسالة خطأ مثل # N / A في الخلية E2 ، تحقق من وجود أخطاء إملائية في الخلية D2.
04 من 05إنشاء صفيف جدول صفرين
يكون بناء جملة الدالة CHOOSE:
= CHOOSE (Index_number، Value1، Value2، … Value254)
عادةً تقوم الدالة CHOOSE بإرجاع قيمة واحدة من قائمة القيم (Value1 إلى Value254) استنادًا إلى رقم الفهرس الذي تم إدخاله.
إذا كان رقم الفهرس 1 ، تقوم الدالة بإرجاع Value1 من القائمة؛ إذا كان الرقم القياسي هو 2 ، تقوم الدالة بإرجاع Value2 من القائمة وما إلى ذلك.
عندما يتم إدخال أرقام فهرس متعددة ، تقوم الدالة بإرجاع قيم متعددة في أي ترتيب مطلوب. يتم إجراء اختيار لإرجاع قيم متعددة عن طريق إنشاء صفيف.
لإدخال مصفوفة ، قم بإحاطة الأرقام التي تم إدخالها باستخدام الأقواس المعقوفة أو الأقواس المعقوفة. تم إدخال رقمين لرقم الفهرس:{ 1,2 }.
تجدر الإشارة إلى أن CHOOSE لا يقتصر على إنشاء جدول عمودين. من خلال تضمين رقم إضافي في الصفيف - مثل {1،2،3} - ونطاق إضافي في وسيطة القيمة ، يقوم CHOOSE بإنشاء جدول ثلاثة أعمدة.
تسمح لك الأعمدة الإضافية بإرجاع معلومات مختلفة باستخدام صيغة البحث اليسرى عن طريق تغيير وسيطة رقم فهرس عمود VLOOKUP إلى رقم العمود الذي يحتوي على المعلومات المطلوبة.
05 من 05تغيير ترتيب الأعمدة باستخدام دالة CHOOSE
في دالة CHOOSE المستخدمة في هذه الصيغة:
اختيار ({1،2}، $ F: $ F، $ D: D $)
يتم سرد نطاق العمود F قبل العمود D.
نظرًا لأن الدالة CHOOSE تقوم بتعيين صفيف جدول VLOOKUP (مصدر البيانات لهذه الدالة) ، يتم تبديل ترتيب الأعمدة في دالة CHOOSE إلى VLOOKUP.
وبقدر ما يتعلق VLOOKUP ، فإن مصفوفة الجدول ليست سوى عمودين بعرض عمود F على اليسار والعمود D على اليمين. بما أن العمود F يحتوي على اسم الشركة التي نريد البحث عنها ، وبما أن العمود D يحتوي على أسماء الأجزاء ، فيمكن لـ VLOOKUP تنفيذ واجبات البحث المنتظمة الخاصة به في البحث عن البيانات الموجودة على يسار قيمة البحث.
ونتيجة لذلك ، يمكن لـ VLOOKUP استخدام اسم الشركة للعثور على الجزء الذي توفره.
