من خلال الجمع بين اكسلVLOOKUP تعمل مععمود يمكننا إنشاء صيغة بحث تسمح لك بإرجاع قيم متعددة من صف واحد من قاعدة البيانات أو جدول البيانات.
01 من 10إرجاع قيم متعددة مع Excel VLOOKUP
يؤدي اتباع الخطوات المذكورة أدناه إلى إنشاء صيغة البحث المعروضة في الصورة أعلاه والتي تؤدي إلى إرجاع قيم متعددة من سجل بيانات واحد.
صيغة البحث تتطلب أن عمود وظيفة متداخلة داخل VLOOKUP. تتضمن وظيفة التعشيق إدخال الدالة الثانية كواحدة من الوسيطات للدالة الأولى.
في هذا البرنامج التعليمي ، و عمود سيتم إدخال الدالة باسم فهرس العمود رقم حجة ل VLOOKUP. تتضمن الخطوة الأخيرة في البرنامج التعليمي نسخ صيغة البحث إلى أعمدة إضافية لاسترداد قيم إضافية للجزء الذي تم اختياره.
أدخل بيانات البرنامج التعليمي
الخطوة الأولى في البرنامج التعليمي هي إدخال البيانات في ورقة عمل Excel. من أجل اتباع الخطوات الواردة في البرنامج التعليمي ، أدخل البيانات الموضحة في الصورة أعلاه في الخلايا التالية:
- أدخل النطاق الأعلى للبيانات في الخلايا D1 إلى G1.
- أدخل النطاق الثاني في خلايا D4 إلى G10.
سيتم إدخال معايير البحث وصيغة البحث التي تم إنشاؤها خلال هذا البرنامج التعليمي الصف 2 من ورقة العمل.
لا يتضمن البرنامج التعليمي التنسيق المرئي في الصورة ، ولكن هذا لن يؤثر في كيفية عمل صيغة البحث. تتوفر معلومات حول خيارات التنسيق المشابهة لتلك المعروضة أعلاه في "دروس تنسيق Excel Basic".
03 من 10إنشاء نطاق مسمى لجدول البيانات
النطاق المحدد هو طريقة سهلة للإشارة إلى نطاق من البيانات في صيغة. بدلاً من كتابة مراجع الخلية للبيانات ، يمكنك فقط كتابة اسم النطاق.
الميزة الثانية لاستخدام نطاق مسمى هي أن مراجع الخلية لهذا النطاق لا تتغير أبدًا حتى عند نسخ الصيغة إلى خلايا أخرى في ورقة العمل. لذلك ، تعتبر أسماء النطاق بديلاً لاستخدام مراجع الخلية المطلقة لمنع الأخطاء عند نسخ الصيغ.
لا يتضمن اسم النطاق العناوين أو أسماء الحقول للبيانات (الصف 4) ولكن فقط البيانات نفسها.
- تسليط الضوء الخلايا D5 إلى G10 في ورقة العمل لتحديدها.
- اضغط على اسم المربع الموجود أعلاه العمود أ.
- نوع الطاولة داخل ال اسم المربع.
- اضغط على أدخل المفتاح على لوحة المفاتيح.
- خلايا D5 إلى G10 لديك الآن اسم النطاق الطاولة. سوف نستخدم اسم ل VLOOKUP مجموعة الجدول حجة في وقت لاحق في البرنامج التعليمي.
فتح مربع حوار VLOOKUP
على الرغم من أنه من الممكن فقط كتابة صيغة البحث الخاصة بنا مباشرة في خلية في ورقة عمل ، إلا أن العديد من الأشخاص يجدون صعوبة في الحفاظ على بناء الجملة مستقيمة - خاصة لصيغة معقدة مثل تلك التي نستخدمها في هذا البرنامج التعليمي.
بديل ، في هذه الحالة ، هو استخدام VLOOKUP صندوق المحادثة. تحتوي جميع وظائف Excel تقريبًا على مربع حوار يسمح لك بإدخال كل من وسائط الدالة على سطر منفصل.
خطوات البرنامج التعليمي
- انقر فوق الخلية E2 من ورقة العمل - الموقع حيث سيتم عرض نتائج صيغة البحث ثنائي الأبعاد.
- اضغط على علامة تبويب الصيغ من شريط.
- اضغط على البحث والمراجع الخيار في شريط لفتح القائمة المنسدلة للوظيفة.
- انقر فوق VLOOKUP في القائمة لفتح مربع الحوار الخاص بالوظيفة.
الدخول إلى وسيطة قيمة البحث
عادة ، و ابحث عن القيمة يطابق مجال البيانات في العمود الأول من جدول البيانات. في مثالنا ، ابحث عن القيمة يشير إلى اسم جزء الأجهزة الذي نريد العثور على معلومات حوله. الأنواع المسموح بها من البيانات لل ابحث عن القيمة هي بيانات النص والقيم المنطقية والأرقام ومراجع الخلايا.
مراجع الخلية المطلقة
عادةً ، عند نسخ صيغ في Excel ، تتغير مراجع الخلايا لتعكس موقعها الجديد. ان حدث هذا، D2، مرجع الخلية ل ابحث عن القيمةسيتغير حيث يتم نسخ الصيغة لإنشاء أخطاء بها الخلايا F2 و G2.
لا تتغير مراجع الخلية المطلقة عند نسخ الصيغ.
لمنع الأخطاء ، سنقوم بتحويل مرجع الخلية D2 في مرجع خلية مطلق. يتم إنشاء مراجع الخلية المطلقة بالضغط على F4 المفتاح على لوحة المفاتيح. القيام بذلك يضيف علامات الدولار حول مرجع الخلية مثل $ D $ 2.
- اضغط على ابحث عن القيمة خط في مربع الحوار.
- انقر فوق زنزانة D2 لإضافة هذه الخلية المرجعية إلى ابحث عن القيمة line - هذه هي الخلية التي سنكتب فيها اسم الجزء الذي نبحث عن معلومات عنه
- بدون تحريك نقطة الإدراج ، اضغط على F4 مفتاح على لوحة المفاتيح لتحويل D2 في مرجع الخلية المطلقة $ D $ 2.
- اترك ال VLOOKUP فتح مربع الحوار الوظيفي للخطوة التالية في البرنامج التعليمي.
دخول جدول المصفوفة الحجة
مصفوفة الجدول هي جدول البيانات التي تبحث عنها صيغة البحث للعثور على المعلومات التي نريدها.يجب أن يحتوي مصفوفة الجدول على عمودين على الأقل من البيانات.
يحتوي العمود الأول على وسيطة قيمة البحث (الخطوة السابقة في البرنامج التعليمي) ، بينما سيتم البحث في العمود الثاني بواسطة صيغة البحث للعثور على المعلومات التي نحددها.
يجب إدخال وسيطة صفيف الجدول إما كنطاق يحتوي على مراجع الخلية لجدول البيانات أو كاسم نطاق.
- اضغط على table_array خط في مربع الحوار.
- نوع الطاولة لإدخال اسم النطاق لهذه الوسيطة.
- اترك ال VLOOKUP فتح مربع الحوار الوظيفي للخطوة التالية في البرنامج التعليمي.
تداخل الدالة COLUMN
بشكل طبيعي VLOOKUP فقط إرجاع البيانات من عمود واحد من جدول البيانات ويتم تعيين هذا العمود من قبل رقم فهرس العمود جدال.
في هذا المثال ، لدينا ثلاثة أعمدة نرغب في إرجاع البيانات منها ، لذا نحتاج إلى طريقة لتغييرها بسهولة رقم فهرس العمود دون تحرير صيغة البحث لدينا - وهذا هو المكان عمود وظيفة تأتي في.
عن طريق إدخالها ك رقم فهرس العمود وسيتغير ذلك نظرًا لأنه يتم نسخ صيغة البحث من الخلية D2 إلى الخلايا E2 و F2 في وقت لاحق في البرنامج التعليمي.
وظائف التعشيش
ال عمود وظيفة ، لذلك ، بمثابة VLOOKUPالصورة رقم فهرس العمود جدال؛ هذا يتحقق من خلال تداخل عمود وظيفة داخل VLOOKUP في ال Col_index_num خط من مربع الحوار.
دخول الدالة COLUMN يدويًا
عند تداخل الوظائف ، لا يسمح Excel لنا بفتح مربع الحوار الخاص بالوظيفة الثانية لإدخال الوسيطات الخاصة به. ال عمود وظيفة ، لذلك ، يجب إدخالها يدويا في Col_index_num خط. ال عمود وظيفة لديها حجة واحدة فقط - مرجع حجة وهي مرجع خلية.
اختيار وسيطة مرجع الدالة COLUMN
ال عمود مهمة الوظيفة هي إرجاع رقم العمود المعطى مرجع جدال. بمعنى آخر ، فإنه يقوم بتحويل حرف العمود إلى رقم به العمود أ كونه العمود الأول ، العمود ب الثانية وهلم جرا.
منذ حقل البيانات الأول ، ما نريد إرجاعه هو سعر العنصر الموجود في العمود الثاني من جدول البيانات. يمكننا اختيار مرجع الخلية لأي خلية في العمود ب كما مرجع حجة من أجل الحصول على الرقم 2 ل Col_index_num جدال.
- في ال VLOOKUP مربع الحوار الوظيفي ، انقر فوق Col_index_num خط.
- اكتب اسم الوظيفة عمود تليها فتح قوس مستدير.
- انقر فوق زنزانة B1 في ورقة العمل لإدخال مرجع الخلية ذلك مرجع جدال.
- نوع أ إغلاق قوس المستديرة لاستكمال عمود وظيفة
- اترك ال VLOOKUP فتح مربع الحوار الوظيفي للخطوة التالية في البرنامج التعليمي.
دخول VLOOKUP نطاق بحث وسيطة
VLOOKUPالصورة مجموعة البحث الحجة هي قيمة منطقية (صحيح أو خاطئة) يشير إلى ما إذا كنت تريد VLOOKUP للعثور على تطابق دقيق أو تقريبي لـ ابحث عن القيمة.
- إذا صحيح أو إذا تم حذف هذه الوسيطة: VLOOKUP إرجاع إما تطابق تام إلى ابحث عن القيمةأو ، إذا لم يتم العثور على تطابق تام ، VLOOKUP بإرجاع القيمة الأكبر التالية. للصيغة للقيام بذلك ، فإن البيانات في العمود الأول من Table_array يجب أن يتم فرزها بترتيب تصاعدي.
- إذا خاطئة: VLOOKUP سوف تستخدم فقط المطابقة التامة ل ابحث عن القيمة. إذا كان هناك قيمتان أو أكثر في العمود الأول من Table_array التي تتطابق مع قيمة البحث ، يتم استخدام أول قيمة تم العثور عليها. إذا لم يتم العثور على تطابق تام ، # N / A يتم إرجاع الخطأ.
في هذا البرنامج التعليمي ، نظرًا لأننا نبحث عن معلومات محددة حول عنصر معين للأجهزة ، فسنعمل على تعيينه مجموعة البحث يساوي خاطئة.
- اضغط على مجموعة البحث خط في مربع الحوار.
- اكتب الكلمة خاطئة في هذا الخط للإشارة إلى أننا نريد VLOOKUP لإرجاع مطابقة تامة للبيانات التي نسعى إليها.
- انقر حسنا لإكمال صيغة البحث وإغلاق مربع الحوار.
- وبما أننا لم ندخل بعد معايير البحث في زنزانة D2 ل خطأ # N / A سوف تكون موجودة في الخلية E2.
- سيتم تصحيح هذا الخطأ عندما سنضيف معايير البحث في الخطوة الأخيرة من البرنامج التعليمي.
نسخ صيغة البحث
تهدف صيغة البحث إلى استرداد البيانات من أعمدة متعددة لجدول البيانات في وقت واحد. للقيام بذلك ، يجب أن تتواجد صيغة البحث في جميع الحقول التي نريد المعلومات منها.
في هذا البرنامج التعليمي ، نرغب في استرداد البيانات منه الأعمدة 2, 3و 4 من جدول البيانات - هذا هو السعر ورقم الجزء واسم المورد عندما ندخل اسمًا جزئيًا باسم ابحث عن القيمة.
نظرًا لأن البيانات موضحة في نمط منتظم في ورقة العمل ، فيمكننا نسخ صيغة البحث في الخلية E2 إلى الخلايا F2 و G2.
كما يتم نسخ الصيغة ، سيقوم Excel بتحديث مرجع الخلية النسبية في عمود وظيفة (زنزانة B1) لتعكس الموقع الجديد للصيغة.
كذلك ، لا يغير Excel مرجع الخلية المطلقة $ D $ 2 ونطاق مسمى الطاولة كما يتم نسخ الصيغة.
هناك أكثر من طريقة لنسخ البيانات في Excel ، ولكن ربما أسهل طريقة باستخدام ملء مقبض.
- انقر فوق الخلية E2، حيث توجد صيغة البحث ، لتجعلها الخلية النشطة.
- ضع ال مؤشر الفأرة على مدار مربع أسود في الزاوية السفلية اليمنى - سيتغير المؤشر إلى علامة زائد لتدل على مقبض التعبئة.
- انقر فوق زر الماوس الأيسر و اسحب مقبض التعبئة يعبر الي الخلية G2.
- إطلاق سراح على زر الماوس و الخلية F3 يجب أن تحتوي على صيغة البحث ثنائية الأبعاد.
- إذا تم ذلك بشكل صحيح ، الخلايا F2 و G2 يجب أن تحتوي الآن أيضا # N / A الخطأ الموجود في الخلية E2.
دخول معايير البحث
بمجرد نسخ صيغة البحث إلى الخلايا المطلوبة ، يمكن استخدامها لاسترداد المعلومات من جدول البيانات. للقيام بذلك ، اكتب اسم البند كنت ترغب في استرداد في ابحث عن القيمة زنزانة (D2) واضغط على أدخل المفتاح على لوحة المفاتيح.
بمجرد الانتهاء من ذلك ، يجب أن تحتوي كل خلية تحتوي على صيغة البحث على بيانات مختلفة حول عنصر الجهاز الذي تبحث عنه.
- انقر فوق الخلية D2 في ورقة العمل.
- نوع القطعة إلى الخلية D2 واضغط على أدخل المفتاح على لوحة المفاتيح.
- يجب أن يتم عرض المعلومات التالية في الخلايا E2 إلى G2.
- E2: 14.76 دولار - سعر القطعة
- F2: PN-98769 - رقم الجزء الخاص بعنصر واجهة مستخدم
- G2: Widgets Inc. - اسم المورد للعناصر
- اختبر VLOOKUP صيغة صفيف أخرى عن طريق كتابة اسم أجزاء أخرى في الخلية D2 ومراقبة النتائج في الخلايا E2 إلى G2
