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