من خلال دمج دالة VLOOKUP في Excel مع وظيفة COLUMN ، يمكنك إنشاء صيغة بحث تُرجع قيمًا متعددة من صف واحد من قاعدة بيانات أو جدول بيانات. تعرف على كيفية إنشاء صيغة بحث تُرجع قيمًا متعددة من سجل بيانات واحد.
تنطبق الإرشادات الواردة في هذه المقالة على Excel 2019 و 2016 و 2013 و 2010 ؛ و Excel لـ Microsoft 365.
الخط السفلي
تتطلب صيغة البحث أن تتداخل دالة COLUMN داخل VLOOKUP. يتضمن تداخل دالة إدخال الوظيفة الثانية كأحد الوسائط للدالة الأولى.
أدخل بيانات البرنامج التعليمي
في هذا البرنامج التعليمي ، يتم إدخال الدالة COLUMN كوسيطة رقم فهرس العمود لـ VLOOKUP. تتضمن الخطوة الأخيرة في البرنامج التعليمي نسخ صيغة البحث إلى أعمدة إضافية لاسترداد قيم إضافية للجزء المختار.
الخطوة الأولى في هذا البرنامج التعليمي هي إدخال البيانات في ورقة عمل Excel. من أجل اتباع الخطوات الواردة في هذا البرنامج التعليمي ، أدخل البيانات الموضحة في الصورة أدناه في الخلايا التالية:
- أدخل النطاق الأعلى للبيانات في الخلايا من D1 إلى G1.
- أدخل النطاق الثاني في الخلايا من D4 إلى G10.
يتم إدخال معايير البحث وصيغة البحث التي تم إنشاؤها في هذا البرنامج التعليمي في الصف 2 من ورقة العمل.
لا يتضمن هذا البرنامج التعليمي تنسيق Excel الأساسي الموضح في الصورة ، لكن هذا لا يؤثر على كيفية عمل صيغة البحث.
إنشاء نطاق مسمى لجدول البيانات
النطاق المسمى هو طريقة سهلة للإشارة إلى نطاق من البيانات في صيغة. بدلاً من كتابة مراجع الخلية للبيانات ، اكتب اسم النطاق.
الميزة الثانية لاستخدام نطاق مسمى هي أن مراجع الخلايا لهذا النطاق لا تتغير أبدًا حتى عند نسخ الصيغة إلى خلايا أخرى في ورقة العمل. أسماء النطاقات هي بديل لاستخدام مراجع الخلايا المطلقة لمنع الأخطاء عند نسخ الصيغ.
لا يتضمن اسم النطاق العناوين أو أسماء الحقول للبيانات (كما هو موضح في الصف 4) ، فقط البيانات.
-
تسليط الضوء الخلايا D5 إلى G10في ورقة العمل.
Image -
ضع المؤشر في مربع الاسم الموجود أعلى العمود A ، واكتب Table ، ثم اضغط على أدخل. الخلايا من D5 إلى G10 لها اسم نطاق الجدول
Image - يتم استخدام اسم النطاق لوسيطة مصفوفة جدول VLOOKUP لاحقًا في هذا البرنامج التعليمي.
افتح مربع حوار VLOOKUP
على الرغم من أنه من الممكن كتابة صيغة البحث مباشرة في خلية في ورقة عمل ، إلا أن العديد من الأشخاص يجدون صعوبة في الحفاظ على بناء الجملة مستقيماً - خاصة بالنسبة للصيغة المعقدة مثل تلك المستخدمة في هذا البرنامج التعليمي.
كبديل ، استخدم مربع الحوار VLOOKUP Function Arguments. تحتوي جميع وظائف Excel تقريبًا على مربع حوار حيث يتم إدخال كل وسيطات الوظيفة في سطر منفصل.
-
حدد خلية E2من ورقة العمل. هذا هو المكان الذي ستظهر فيه نتائج صيغة البحث ثنائي الأبعاد.
Image -
على الشريط ، انتقل إلى علامة التبويب الصيغ وحدد بحث ومرجع.
Image -
حدد VLOOKUP لفتح مربع الحوار وسيطات الوظيفة.
Image - مربع حوار وسيطات الوظيفة هو المكان الذي يتم فيه إدخال معلمات وظيفة VLOOKUP.
أدخل وسيطة قيمة البحث
عادة ، تطابق قيمة البحث حقل بيانات في العمود الأول من جدول البيانات. في هذا المثال ، تشير قيمة البحث إلى اسم الجزء الذي تريد البحث عن معلومات. أنواع البيانات المسموح بها لقيمة البحث هي البيانات النصية والقيم المنطقية والأرقام ومراجع الخلايا.
مراجع الخلايا المطلقة
عند نسخ الصيغ في Excel ، تتغير مراجع الخلايا لتعكس الموقع الجديد. إذا حدث هذا ، D2 ، مرجع الخلية لقيمة البحث ، يتغير وينشئ أخطاء في الخلايا F2 و G2.
لا تتغير مراجع الخلايا المطلقة عند نسخ الصيغ.
لمنع الأخطاء ، قم بتحويل مرجع الخلية D2 إلى مرجع خلية مطلق. لإنشاء مرجع خلية مطلق ، اضغط على المفتاح F4. يؤدي هذا إلى إضافة علامات الدولار حول مرجع الخلية مثل $ D $ 2.
-
في مربع حوار وسيطات الوظيفة ، ضع المؤشر في مربع النص lookup_value. بعد ذلك ، في ورقة العمل ، حدد خلية D2 لإضافة مرجع الخلية هذا إلى lookup_value. الخلية D2 هي المكان الذي سيتم فيه إدخال اسم الجزء.
Image -
بدون تحريك نقطة الإدراج ، اضغط على مفتاح F4لتحويل D2 إلى مرجع الخلية المطلق $ D $ 2.
Image - اترك مربع حوار وظيفة VLOOKUP مفتوحًا للخطوة التالية في البرنامج التعليمي.
أدخل وسيطة مصفوفة الجدول
صفيف الجدول هو جدول البيانات الذي تبحث عنه صيغة البحث للعثور على المعلومات التي تريدها. يجب أن يحتوي صفيف الجدول على عمودين على الأقل من البيانات.
يحتوي العمود الأول على وسيطة قيمة البحث (التي تم إعدادها في القسم السابق) ، بينما يتم البحث في العمود الثاني بواسطة صيغة البحث للعثور على المعلومات التي تحددها.
يجب إدخال وسيطة مصفوفة الجدول إما كنطاق يحتوي على مراجع الخلايا لجدول البيانات أو كاسم نطاق.
لإضافة جدول البيانات إلى وظيفة VLOOKUP ، ضع المؤشر في مربع النص table_array في مربع الحوار واكتب Table لإدخال اسم النطاق لهذه الوسيطة.
عش وظيفة العمود
في العادة ، تقوم VLOOKUP بإرجاع البيانات من عمود واحد فقط في جدول البيانات.يتم تعيين هذا العمود بواسطة وسيطة رقم فهرس العمود. ومع ذلك ، في هذا المثال ، هناك ثلاثة أعمدة ، ويجب تغيير رقم فهرس العمود دون تحرير صيغة البحث. لإنجاز ذلك ، قم بتداخل الدالة COLUMN داخل دالة VLOOKUP كوسيطة Col_index_num.
عند تداخل الوظائف ، لا يفتح Excel مربع حوار الوظيفة الثانية لإدخال وسيطاتها. يجب إدخال وظيفة العمود يدويًا. تحتوي الدالة COLUMN على وسيطة واحدة فقط ، وهي الوسيطة Reference ، وهي مرجع خلية.
ترجع الدالة COLUMN رقم العمود المقدم كوسيطة مرجع. يحول حرف العمود إلى رقم
للعثور على سعر عنصر ما ، استخدم البيانات الموجودة في العمود 2 من جدول البيانات. يستخدم هذا المثال العمود B كمرجع لإدراج 2 في وسيطة Col_index_num.
-
في مربع الحوار Function Arguments ، ضع المؤشر في مربع النص Col_index_num واكتب COLUMN (. (تأكد من تضمين القوس الدائري المفتوح.)
Image -
في ورقة العمل ، حدد خلية B1لإدخال مرجع الخلية هذا كوسيطة مرجع.
Image - اكتب a قوس إغلاق دائريلإكمال وظيفة العمود.
أدخل وسيطة بحث نطاق VLOOKUP
الوسيطة Range_lookupVLOOKUP هي قيمة منطقية (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 بإرجاع مطابقة تامة للبيانات.
حدد OKلإكمال صيغة البحث وإغلاق مربع الحوار. ستحتوي الخلية E2 على الخطأN / A لأنه لم يتم إدخال معايير البحث في الخلية D2. هذا الخطأ مؤقت. سيتم تصحيحه عند إضافة معايير البحث في الخطوة الأخيرة من هذا البرنامج التعليمي.
انسخ صيغة البحث وأدخل المعايير
تسترد صيغة البحث البيانات من عدة أعمدة في جدول البيانات في وقت واحد. للقيام بذلك ، يجب أن تكون صيغة البحث موجودة في جميع الحقول التي تريد المعلومات منها.
لاسترداد البيانات من الأعمدة 2 و 3 و 4 من جدول البيانات (السعر ورقم الجزء واسم المورد) ، أدخل اسمًا جزئيًا مثل قيمة البحث.
نظرًا لأن البيانات موضوعة في نمط عادي في ورقة العمل ، انسخ صيغة البحث في خلية E2 إلى خلايا F2 و G2أثناء نسخ الصيغة ، يقوم Excel بتحديث مرجع الخلية النسبي في دالة COLUMN (الخلية B1) لتعكس الموقع الجديد للصيغة. لا يغير Excel مرجع الخلية المطلق (مثل $ D $ 2) والنطاق المسمى (الجدول) حيث يتم نسخ الصيغة.
هناك أكثر من طريقة لنسخ البيانات في Excel ، ولكن أسهل طريقة هي استخدام مقبض التعبئة.
-
حدد خلية E2، حيث توجد صيغة البحث ، لجعلها الخلية النشطة.
Image -
اسحب مقبض التعبئة عبر خلية G2. تعرض الخليتان F2 و G2 الخطأN / A الموجود في الخلية E2.
Image -
لاستخدام صيغ البحث لاسترداد المعلومات من جدول البيانات ، في ورقة العمل حدد خلية D2 ، واكتب Widget ، واضغط أدخل.
Image يتم عرض المعلومات التالية في الخلايا من E2 إلى G2.
- E2:$ 14.76 - سعر الأداة
- F2:PN-98769 - رقم القطعة لعنصر واجهة مستخدم
- G2:Widgets Inc. - اسم مورد الأدوات
-
لاختبار صيغة صفيف VLOOKUP ، اكتب اسم الأجزاء الأخرى في الخلية D2 ولاحظ النتائج في الخلايا من E2 إلى G2.
Image - تحتوي كل خلية تحتوي على صيغة البحث على جزء مختلف من البيانات حول عنصر الجهاز الذي بحثت عنه.
توفر وظيفة VLOOKUP ذات الوظائف المتداخلة مثل COLUMN طريقة فعالة للبحث عن البيانات داخل جدول ، باستخدام بيانات أخرى كمرجع بحث.
