من خلال دمج وظيفة VLOOKUP في Excel مع الدالة COLUMN ، يمكننا إنشاء صيغة بحث تسمح لك بإرجاع قيم متعددة من صف واحد من قاعدة البيانات أو جدول البيانات.
في المثال الموضح في الصورة أعلاه ، تسهل صيغة البحث إعادة جميع القيم - مثل السعر ورقم الجزء والمورد - ذات الصلة بالقطع المختلفة للأجهزة.
01 من 10
إرجاع قيم متعددة مع Excel VLOOKUP
يؤدي اتباع الخطوات المذكورة أدناه إلى إنشاء صيغة البحث المعروضة في الصورة أعلاه والتي تؤدي إلى إرجاع قيم متعددة من سجل بيانات واحد.
تتطلب صيغة البحث أن تكون الدالة COLUMN متداخلة داخل VLOOKUP.
تتضمن وظيفة التعشيق إدخال الدالة الثانية كواحدة من الوسيطات للدالة الأولى.
في هذا البرنامج التعليمي ، سيتم إدخال الدالة COLUMN كوسيطة رقم فهرس العمود لـ VLOOKUP.
تتضمن الخطوة الأخيرة في البرنامج التعليمي نسخ صيغة البحث إلى أعمدة إضافية لاسترداد قيم إضافية للجزء الذي تم اختياره.
محتويات البرنامج التعليمي
- دخول بيانات البرنامج التعليمي
- إنشاء نطاق مسمى لجدول البيانات
- بدء تشغيل وظيفة VLOOKUP
- الدخول إلى وسيطة قيمة البحث باستخدام مراجع الخلايا المطلقة
- دخول جدول المصفوفة الحجة
- الدخول إلى الدالة COLUMN المتداخلة
- استكمال وظيفة VLOOKUP
- نسخ صيغة البحث مع مقبض التعبئة
- استرجاع البيانات مع صيغة البحث
02 من 10
أدخل بيانات البرنامج التعليمي
الخطوة الأولى في البرنامج التعليمي هي إدخال البيانات في ورقة عمل Excel.
من أجل اتباع الخطوات الواردة في البرنامج التعليمي ، أدخل البيانات الموضحة في الصورة أعلاه في الخلايا التالية.
- أدخل النطاق الأعلى للبيانات في الخلايا من D1 إلى G1
- أدخل النطاق الثاني في الخلايا من D4 إلى G10
سيتم إدخال معايير البحث وصيغة البحث التي تم إنشاؤها خلال هذا البرنامج التعليمي في الصف 2 من ورقة العمل.
لا يتضمن البرنامج التعليمي التنسيق المرئي في الصورة ، ولكن هذا لن يؤثر في كيفية عمل صيغة البحث.
تتوفر معلومات حول خيارات التنسيق المشابهة لتلك المعروضة أعلاه في " دروس تنسيق Excel Basic" .
خطوات البرنامج التعليمي
- أدخل البيانات كما هو موضح في الصورة أعلاه في الخلايا من D1 إلى G10
03 من 10
إنشاء نطاق مسمى لجدول البيانات
النطاق المحدد هو طريقة سهلة للإشارة إلى نطاق من البيانات في صيغة. بدلاً من كتابة مراجع الخلية للبيانات ، يمكنك فقط كتابة اسم النطاق.
الميزة الثانية لاستخدام نطاق مسمى هي أن مراجع الخلية لهذا النطاق لا تتغير أبدًا حتى عند نسخ الصيغة إلى خلايا أخرى في ورقة العمل.
لذلك ، تعتبر أسماء النطاق بديلاً لاستخدام مراجع الخلية المطلقة لمنع الأخطاء عند نسخ الصيغ.
ملاحظة: لا يتضمن اسم النطاق العناوين أو أسماء الحقول للبيانات (الصف 4) ولكن البيانات فقط نفسها.
خطوات البرنامج التعليمي
- قم بتمييز الخلايا من D5 إلى G10 في ورقة العمل لتحديدها
- انقر فوق " مربع اسم" الموجود فوق العمود A
- اكتب "جدول" (لا علامات اقتباس) في "مربع الاسم"
- اضغط على المفتاح ENTER على لوحة المفاتيح
- أصبحت الخلايا من D5 إلى G10 الآن اسم نطاق "جدول". سنستخدم الاسم لوسيطة صفيف جدول VLOOKUP لاحقًا في البرنامج التعليمي
04 من 10
فتح مربع حوار VLOOKUP
على الرغم من أنه من الممكن فقط كتابة صيغة البحث الخاصة بنا مباشرة في خلية في ورقة عمل ، إلا أن العديد من الأشخاص يجدون صعوبة في الحفاظ على بناء الجملة مستقيمة - خاصة لصيغة معقدة مثل تلك التي نستخدمها في هذا البرنامج التعليمي.
بديل ، في هذه الحالة ، هو استخدام مربع الحوار VLOOKUP. تحتوي جميع وظائف Excel تقريبًا على مربع حوار يسمح لك بإدخال كل من وسائط الدالة على سطر منفصل.
خطوات البرنامج التعليمي
- انقر فوق الخلية E2 لورقة العمل - الموقع حيث سيتم عرض نتائج صيغة البحث ثنائية الأبعاد
- انقر فوق علامة التبويب صيغ من الشريط
- انقر فوق خيار البحث والمرجع في الشريط لفتح القائمة المنسدلة الدالة
- انقر فوق VLOOKUP في القائمة لفتح مربع الحوار الخاص بالوظيفة
05 من 10
الدخول إلى وسيطة قيمة البحث باستخدام مراجع الخلايا المطلقة
عادةً ، تطابق قيمة البحث حقل بيانات في العمود الأول من جدول البيانات.
في مثالنا ، تشير قيمة البحث إلى اسم جزء الأجهزة الذي نريد العثور على معلومات حوله.
الأنواع المسموح بها للبيانات لقيمة البحث هي:
- بيانات النص
- قيمة منطقية (TRUE أو FALSE فقط)
- رقم
- مرجع خلية لقيمة في ورقة العمل
في هذا المثال ، سنقوم بإدخال مرجع الخلية إلى حيث سيكون اسم الجزء موجودًا - الخلية D2.
مراجع الخلية المطلقة
في خطوة لاحقة في البرنامج التعليمي ، سننسخ صيغة البحث في الخلية E2 إلى الخلايا F2 و G2.
عادةً ، عند نسخ صيغ في Excel ، تتغير مراجع الخلايا لتعكس موقعها الجديد.
إذا حدث ذلك ، فسيتم تغيير D2 - مرجع الخلية لقيمة البحث - أثناء نسخ الصيغة لإنشاء أخطاء في الخلايا F2 و G2.
لمنع الأخطاء ، سنقوم بتحويل مرجع الخلية D2 إلى مرجع خلية مطلق .
لا تتغير مراجع الخلية المطلقة عند نسخ الصيغ.
يتم إنشاء مراجع الخلية المطلقة بالضغط على المفتاح F4 على لوحة المفاتيح. يؤدي القيام بذلك إلى إضافة علامات الدولار حول مرجع الخلية مثل $ D $ 2
خطوات البرنامج التعليمي
- انقر فوق خط lookup_value في مربع الحوار
- انقر فوق الخلية D2 لإضافة مرجع الخلية هذا إلى خط lookup_value . هذه هي الخلية التي سنكتب فيها اسم الجزء الذي نبحث عن المعلومات عنه
- بدون تحريك نقطة الإدراج ، اضغط على المفتاح F4 على لوحة المفاتيح لتحويل D2 إلى مرجع الخلية المطلق $ D $ 2
- اترك مربع حوار الدالة VLOOKUP مفتوحًا للخطوة التالية في البرنامج التعليمي
06 من 10
دخول جدول المصفوفة الحجة
مصفوفة الجدول هي جدول البيانات التي تبحث عنها صيغة البحث للعثور على المعلومات التي نريدها.
يجب أن يحتوي مصفوفة الجدول على عمودين على الأقل من البيانات .
- يحتوي العمود الأول على وسيطة قيمة البحث (الخطوة السابقة في البرنامج التعليمي)
- سيتم البحث في الثانية ، وأي أعمدة إضافية ، بواسطة صيغة البحث للعثور على المعلومات التي نحددها.
يجب إدخال وسيطة صفيف الجدول إما كنطاق يحتوي على مراجع الخلية لجدول البيانات أو كاسم نطاق .
في هذا المثال ، سنستخدم اسم النطاق الذي تم إنشاؤه في الخطوة 3 من البرنامج التعليمي.
خطوات البرنامج التعليمي
- انقر على سطر table_array في مربع الحوار
- اكتب "جدول" (بدون علامتي الاقتباس) لإدخال اسم النطاق لهذه الوسيطة
- اترك مربع حوار الدالة VLOOKUP مفتوحًا للخطوة التالية في البرنامج التعليمي
07 من 10
تداخل الدالة COLUMN
عادةً ، لا يُرجع VLOOKUP إلا البيانات من عمود واحد في جدول البيانات ، ويتم تعيين هذا العمود بواسطة وسيطة رقم فهرس العمود .
في هذا المثال ، لدينا ثلاثة أعمدة نرغب في إرجاع البيانات منها ، لذا نحتاج إلى طريقة لتغيير رقم فهرس العمود بسهولة دون تحرير صيغة البحث الخاصة بنا.
هذا هو المكان الذي تأتي فيه الدالة COLUMN. عن طريق إدخاله كوسيطة رقم فهرس العمود ، سوف يتغير عندما يتم نسخ صيغة البحث من الخلية D2 إلى الخلايا E2 و F2 لاحقًا في البرنامج التعليمي.
وظائف التعشيش
وبالتالي ، تعمل الدالة COLUMN كوسيطة رقم فهرسة عمود VLOOKUP.
يتم تحقيق ذلك بواسطة تداخل الدالة COLUMN داخل VLOOKUP في سطر Col_index_num لمربع الحوار.
دخول الدالة COLUMN يدويًا
عند تداخل الوظائف ، لا يسمح Excel لنا بفتح مربع الحوار الخاص بالوظيفة الثانية لإدخال الوسيطات الخاصة به.
لذلك ، يجب إدخال الدالة COLUMN يدويًا في سطر Col_index_num .
تحتوي الدالة COLUMN على وسيطة واحدة فقط - وسيطة Reference التي تمثل مرجعًا للخلية.
اختيار وسيطة مرجع الدالة COLUMN
مهمة الدالة COLUMN هي إرجاع رقم العمود المعطى على أنه الوسيطة Reference .
وبعبارة أخرى ، فإنه يحول حرف العمود إلى رقم مع العمود A ، وهو العمود الأول ، والعمود "ب" الثاني وهكذا.
نظرًا لأن حقل البيانات الأول الذي نريد إرجاعه هو سعر العنصر - الموجود في العمود الثاني من جدول البيانات - يمكننا اختيار مرجع الخلية لأي خلية في العمود B كمرجع وسيطة من أجل الحصول على الرقم 2 الوسيطة Col_index_num .
خطوات البرنامج التعليمي
- في مربع الحوار الدالة VLOOKUP ، انقر فوق سطر Col_index_num
- اكتب عمود اسم الوظيفة متبوعًا بقوس مستدير مفتوح " ( "
- انقر فوق الخلية B1 في ورقة العمل لإدخال مرجع الخلية هذا كوسيطة Reference
- اكتب قوس دائري للإغلاق " ) " لإكمال وظيفة COLUMN
- اترك مربع حوار الدالة VLOOKUP مفتوحًا للخطوة التالية في البرنامج التعليمي
08 من 10
دخول 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 ، سيكون الخطأ # N / A موجودًا في الخلية E2
- سيتم تصحيح هذا الخطأ عندما سنضيف معايير البحث في الخطوة الأخيرة من البرنامج التعليمي
09 من 10
نسخ صيغة البحث مع مقبض التعبئة
تهدف صيغة البحث إلى استرداد البيانات من أعمدة متعددة لجدول البيانات في وقت واحد.
للقيام بذلك ، يجب أن تتواجد صيغة البحث في جميع الحقول التي نريد المعلومات منها.
في هذا البرنامج التعليمي ، نرغب في استرداد البيانات من الأعمدة 2 و 3 و 4 من جدول البيانات - وهذا هو السعر ورقم الجزء واسم المورد عندما ندخل اسم جزء باسم Lookup_value.
نظرًا لأن البيانات موضحة في نمط منتظم في ورقة العمل ، فيمكننا نسخ صيغة البحث في الخلية E2 إلى الخاليتين F2 و G2.
عند نسخ الصيغة ، سيقوم Excel بتحديث مرجع الخلية النسبية في الدالة COLUMN (B1) ليعكس الموقع الجديد للصيغة.
كذلك ، لا يقوم Excel بتغيير مرجع الخلية المطلقة $ D $ 2 ونطاق الجدول المسمى كما يتم نسخ الصيغة.
هناك أكثر من طريقة لنسخ البيانات في Excel ، ولكن ربما تكون أسهل طريقة هي استخدام مقبض التعبئة .
خطوات البرنامج التعليمي
- انقر فوق الخلية E2 - حيث توجد صيغة البحث - لجعلها الخلية النشطة
- ضع مؤشر الماوس فوق المربع الأسود في الزاوية السفلية اليمنى. سيتغير المؤشر إلى علامة الجمع " + " - هذا هو مقبض التعبئة
- انقر فوق زر الماوس الأيسر واسحب مقبض التعبئة عبر الخلية G2
- حرر زر الماوس ويجب أن تحتوي الخلية F3 على صيغة البحث ثنائية الأبعاد
- إذا تم ذلك بشكل صحيح ، يجب أن تحتوي الخلايا F2 و G2 الآن أيضًا على الخطأ # N / A الموجود في الخلية E2
10 من 10
دخول معايير البحث
بمجرد نسخ صيغة البحث إلى الخلايا المطلوبة ، يمكن استخدامها لاسترداد المعلومات من جدول البيانات.
للقيام بذلك ، اكتب اسم العنصر الذي ترغب في استرداده في الخلية Lookup_value (D2) واضغط على المفتاح ENTER على لوحة المفاتيح.
بمجرد الانتهاء من ذلك ، يجب أن تحتوي كل خلية تحتوي على صيغة البحث على بيانات مختلفة حول عنصر الجهاز الذي تبحث عنه.
خطوات البرنامج التعليمي
- انقر فوق الخلية D2 في ورقة العمل
- اكتب Widget في الخلية D2 واضغط على المفتاح ENTER على لوحة المفاتيح
- يجب أن يتم عرض المعلومات التالية في الخلايا من E2 إلى G2:
- E2 - 14.76 دولار - سعر القطعة
- F2 - PN-98769 - رقم الجزء الخاص بعنصر واجهة مستخدم
- G2 - Widgets Inc. - اسم المورد للعناصر
- اختبر صيغة صفيف VLOOKUP بشكل أكبر عن طريق كتابة اسم الأجزاء الأخرى في الخلية D2 وملاحظة النتائج في الخلايا من E2 إلى G2
إذا ظهرت رسالة خطأ مثل #REF! يظهر في الخلايا E2 أو F2 أو G2 ، قد تساعدك هذه القائمة من رسائل الخطأ VLOOKUP في تحديد مكان تكمن المشكلة.