المحتوى
- أمثلة على كيفية استخدام VLOOKUP في Excel 2007 و 2010
- كيفية استخدام VLOOKUP لجلب معلومات التسعير للمنتجات في Excel 2007 و 2010
- استخدام VLOOKUP لإكمال عنوان العملاء في طلب باستخدام رقم العميل الخاص بهم في Excel 2007 و 2010
- استخدام دالة VLOOKUP في صيغة عندما تكون بياناتك في مصنف Excel 2007 أو 2010 آخر
- يتعامل Excel 2007 و 2010 مع بياناتك كجدول عند استخدام VLOOKUP
- والآن اختبار لمعرفة ما تعلمناه اليوم!
- مفتاح الحل
- تفسير درجاتك
- استنتاج
يكتب روبي في الغالب عن Skyrim ولكنه يلقي الضوء أحيانًا على شذوذ تطبيقات Microsoft مثل Excel و Outlook.
يحتوي كل من Excel 2007 و 2010 على عدد هائل من الوظائف المتاحة. سأبحث اليوم في واحدة من أقوى وظائف Excel ، VLOOKUP ، وكيف يمكن استخدامها في الصيغ. ما يفعله VLOOKUP في جوهره هو أنه يبحث عن قيمة ضمن جدول بيانات ويعيد بعض المعلومات المحددة المتعلقة بهذه القيمة. لتوضيح كيفية الاستفادة من قوة VLOOKUP ، سننظر في بعض الأمثلة:
- أولاً ، سنستخدم VLOOKUP لجلب معلومات الأسعار الخاصة بالمنتجات من كتالوج الأسعار الخاص بنا حتى نتمكن من تحديد قيمة الطلب.
- ثانيًا ، سيتم استخدام VLOOKUP لإكمال اسم المستخدم وعنوانه في الطلب من خلال البحث عن معرّف العميل الخاص بالعميل في قاعدة بيانات العملاء وإعادة التفاصيل الخاصة بهم.
- أخيرًا ، سننظر في استخدام VLOOKUP للبحث عن البيانات في مصنف آخر.
أمثلة على كيفية استخدام VLOOKUP في Excel 2007 و 2010
لنفترض أنك تمتلك متجرًا للموسيقى / موقعًا إلكترونيًا. لديك قائمة بالطلبات الواردة من العملاء في دفتر الطلبات ، والكتالوج الخاص بك يحتوي على جميع الأسعار الخاصة بك ، وقاعدة بيانات بتفاصيل عميلك.
يتم تخصيص رقم عميل لكل عميل ، بمجرد إتمام عملية الشراء ، ويتم إدخال تفاصيله في قاعدة البيانات. عندما يأتي طلب ، تقوم بتسجيل هذا الطلب في دفتر الطلبات باستخدام رقم العميل في جدول البيانات الخاص بك.
في مثالنا الأول ، نريد أن يتمكن Excel من إخبارك بقيمة طلب العميل. أنت تعلم أن العميل يريد الألبوم الأخضر من R.E.M. على قرص مضغوط على سبيل المثال. باستخدام VLOOKUP ، Excel 2007 orl 2010 يقارن طلبك مع الكتالوج الخاص بك ويعود بالسعر.
في المثال الثاني ، نريد أن يقوم برنامج Excel بإحضار عنوان العميل تلقائيًا عندما نقوم بإدخال معرف العميل الخاص به في الطلب.
دعونا نلقي نظرة على كل مثال الآن بمزيد من التفصيل ، قبل التحقيق أيضًا في كيفية استخدام VLOOKUP لجلب البيانات من مصنف آخر.
كيفية استخدام VLOOKUP لجلب معلومات التسعير للمنتجات في Excel 2007 و 2010
في مثالنا الأول ، سنستخدم VLOOKUP لجلب سعر منتجاتنا (كل وسائط مختلفة لها تكلفة مختلفة). في المثال أدناه ، لدي تفاصيل التسعير على اليمين وتفاصيل الطلب على اليسار. إذا كان متجر الموسيقى الخاص بي عبارة عن متجر فعلي ، فمن المحتمل أن تكون البيانات على أوراق مختلفة في نفس المصنف أو حتى في مصنف آخر بالكامل ولكن لديّها بجوار بعضها البعض لتسهيل معرفة كيفية توافق الصيغ معًا.
الخطوة التالية هي الصيغة نفسها. سأعرض الصيغة النهائية أولاً ثم أشرح كل جزء.
= VLOOKUP (C14 ، 19 دولارًا أمريكيًا: 22 دولارًا أمريكيًا ، 22 دولارًا أمريكيًا ، خطأ)
تتكون الصيغة من أربعة أجزاء:
- يخبر الجزء الأول برنامج Excel بما أطلب من VLOOKUP البحث عنه (في هذا المثال ، أبحث عن قرص مضغوط لذا أعطي Excel الخلية ج 14 كمرجع).
- الجزء الثاني يحدد نطاق الخلايا (وهو في هذه الحالة 19 دولارًا أمريكيًا: 22 دولارًا سنغافوريًا) أطلب من Excel البحث عن محتويات الخلية ج 14 (قرص مضغوط).
ملحوظة:يخبر $ الموجود بجوار عمود وصف الخلية Excel أنه لا يتغير من صيغة إلى أخرى ، فهم دائمًا ثابتون. هذا يضمن أن Excel يستخدم دائمًا النطاق الدقيق الخاص بك لصيغة VLOOKUPعند نسخه إلى خلايا أخرى في المصنف الخاص بك.
- الجزء الثالث معقد بعض الشيء ، لذا دعنا نقسم الصيغة لإظهار ما يفعله Excel بالضبط وكيف يستخدم الجزء الثالث من الصيغة:
- لقد طلبنا من VLOOKUP العثور على محتويات الخلية ج 14(الجزء 1) في النطاق 19 دولارًا أمريكيًا: 22 دولارًا أمريكيًا (الجزء الثاني).
- يبحث Excel في العمود الأول من النطاق (F19 دولار - F22) والعثور على القرص المضغوط في الخلية F19.
- الجزء 3 يطلب من Excel البحث في الصف الثاني عند العثور على قرص مضغوط ، لذلك عندما يعثر على القرص المضغوط في F19، تقوم بإرجاع قيمة 10 (G19) وهو في الصف الثاني وهو بالضبط ما أردناه أن يفعله!
- يتيح الجزء الأخير لبرنامج Excel معرفة ما إذا كنت تبحث عن تطابق تام (خطأ) أم تطابق تقريبي (صواب). نظرًا لأنني أبحث عن تطابق تام ، فقد اخترت False في هذه الحالة.
ملحوظة: يمكن أن يكون هذا الجزء معقدًا وخاصة عند استخدام True ، يمكن لبرنامج Excel أحيانًا إرجاع نتائج غير صحيحة. لدي مقال يوضح كيفية استخدام القيمة الصواب والخطأ بشكل صحيح بحيث تقوم VLOOKUP بإرجاع نتيجة صحيحة في كل مرة. يمكن العثور على هذه المقالة هنا.
في الصورة أدناه ، يمكنك رؤية الصيغة الكاملة بالإضافة إلى جميع الخلايا التي تشير إليها.
استخدام VLOOKUP لإكمال عنوان العملاء في طلب باستخدام رقم العميل الخاص بهم في Excel 2007 و 2010
في مثالنا التالي ، في كل مرة يطلب فيها أحد العملاء شيئًا ما من متجري ، أقوم بإدخال رقم العميل بالترتيب الخاص به وأود أن يقوم Excel بتعبئة أسمائهم وعناوينهم تلقائيًا باستخدام VLOOKUP. لديّ كل هذه التفاصيل مخزنة في قاعدة بيانات العملاء الخاصة بي وأود أن يقوم Excel بإحضارها تلقائيًا. لدي البيانات التي تم إعدادها على النحو التالي مع دفتر الطلبات الخاص بي على اليسار وقاعدة بيانات العملاء على اليمين.
تم إعداد الصيغة بنفس الطريقة تمامًا كما في المثال أعلاه.
= VLOOKUP (A14، $ N $ 16: $ O $ 25،2، FALSE)
مرة أخرى ، الخطوات الأربع مماثلة للمثال السابق.
- 1 (مضمن في الخلية A14) هو ما تطلب Excel 2007 أو 2010 أن تبحث عنه.
- 16 دولارًا أمريكيًا: 25 دولارًا أمريكيًا هو نطاق الخلايا الذي تريد أن يبحث Excel فيه.
- 2 هو الصف الذي يحتوي على البيانات التي تريد أن يقوم Excel بإرجاعها.
- خاطئة هو إخبار Excel بإرجاع البيانات فقط إذا عثر على تطابق تام.
يوضح الشكل أدناه الصيغة والخلايا التي يشير إليها حتى تتمكن من رؤية VLOOKUP أثناء العمل.
استخدام دالة VLOOKUP في صيغة عندما تكون بياناتك في مصنف Excel 2007 أو 2010 آخر
إذا كانت بعض بياناتك التي ستشير إليها VLOOKUP موجودة في مصنف آخر (على سبيل المثال ، قد يتم الاحتفاظ بقاعدة بيانات العملاء الخاصة بي بواسطة عضو مختلف في فريقي وإدخالها في مصنف آخر) ، فمن السهل جدًا إضافة هذا النطاق إلى ملف جديد أو الصيغة الحالية.
= VLOOKUP (E14، "C: [Book1] Sheet1"! $ A $ 1: $ B $ 4،2، FALSE)
التنسيق هو:
- أولاً ، موقع الملف (في حالتي C: ) مع "في المقدمة كما هو موضح أعلاه
- اسم الملف في [] (في حالتي Book2.xlsx).
- الورقة التي توجد بها البيانات (في حالتي الورقة 1) متبوعة بعلامة "!.
- وأخيرًا نطاق البيانات كالمعتاد.
هناك طريقة أخرى للقيام بذلك والتي سيجدها الكثيرون أسهل لأنك لست مضطرًا لتعديل أي صيغ.
افتح كلا مصنفات Excel 2007 وانقر فوق استعادة النافذة الزر (كما هو موضح أدناه) للمستندات بدلاً من Excel نفسه بحيث لا يشغل المصنف سوى جزء من نافذة Excel الإجمالية.
افتح المصنف الثاني وضعه بحيث يكون بجوار المصنف الآخر الذي قمت بفتحه. أدخل في الجزء الأول من الصيغة كالمعتاد ثم عند تحديد النطاق للجزء الثاني ، حدد البيانات في المصنف الثاني كما هو موضح أدناه. ستلاحظ أن المسار لا يبدو صحيحًا تمامًا في هذه الصيغة مع فتح كلا المصنفين. = VLOOKUP (E14، [Book1.xlsx] Sheet1! $ A $ 1: $ B $ 4،2، FALSE) بمجرد إغلاق المصنف الذي يحتوي على النطاق (في حالتي ، الكتاب 1) ، تتغير الصيغة إلى شيء يبدو أكثر منطقية. = VLOOKUP (E14، 'C: [Book1.xlsx] Sheet1'! $ A $ 1: $ B $ 4،2، FALSE)
يتعامل Excel 2007 و 2010 مع بياناتك كجدول عند استخدام VLOOKUP
ميزة رائعة في Excel 2007 و 2010 وكذلك وظيفة VLOOKUP هي أنه عند استخدام VLOOKUP ، يقوم Excel بتحويل بياناتك إلى جدول. لا يزال يبدو وكأنه بيانات عادية ، ولكن عندما أضع على سبيل المثال طلبًا جديدًا في المصنف الخاص بي في الصف 24 وأدخل رقم العميل الخاص بالشخص الذي قدم طلبًا جديدًا في الخلية A24 ، يملأ Excel تلقائيًا بيانات VLOOKUP (في حالتي ، تدخل في Harry James: 15 Queens Avenue إلى B24 بجوار الخمسة التي أدخلتها في الخلية A24). بمجرد إدخال ما طلبه العميل ، سيقوم Excel بعد ذلك بملء تكلفة الوحدة تلقائيًا وإدخالها في الخلية F24 من أجلي.
إذا كنت ترغب في كسر هذا السلوك ، اترك صفًا فارغًا ثم استمر في إدخال بيانات جديدة ولن يقوم Excel بتعبئة الخلايا تلقائيًا باستخدام VLOOKUP (إذا قمت بإدخال 7 في الخلية A26 ، فلن يقوم Excel بالتعبئة التلقائية لأن A25 فارغ لذا يعرف أن الجدول قد اكتمل).
والآن اختبار لمعرفة ما تعلمناه اليوم!
لكل سؤال ، اختر أفضل إجابة. مفتاح الإجابة أدناه.
- كم عدد الأجزاء الموجودة في صيغة VLOOKUP؟
- 2
- 4
- 3
- ماذا يقول $ الموجود أمام مرجع خلية لبرنامج Excel؟
- أن مراجع الخلية لا تتغير عند نسخها
- أن الخلية تحتوي على معلومات العملة
- أن الخلية تحتوي على صيغة
- ماذا يفعل الجزء الرابع من صيغة VLOOKUP؟
- للتحقق مما إذا كانت الصيغة سترجع إجابة عددية
- تحقق مما إذا كانت الصيغة تحتوي على خطأ
- أخبر Excel ما إذا كنت تريد البحث عن تطابق تام أو تقريبي
مفتاح الحل
- 4
- أن مراجع الخلية لا تتغير عند نسخها
- أخبر Excel ما إذا كنت تريد البحث عن تطابق تام أو تقريبي
تفسير درجاتك
إذا حصلت على 0 إجابة صحيحة: عفوًا ، لا تتردد في قراءة المحور مرة أخرى والتحقق من إجاباتك
إذا حصلت على إجابة واحدة صحيحة: أحسنت ، لقد انتهيت تقريبًا!
إذا حصلت على إجابتين صحيحتين: لقد نجحت ، جيد!
إذا حصلت على 3 إجابات صحيحة: أحسنت ، على رأس الفصل ، امنح نفسك تربيتًا افتراضيًا على الظهر!
استنتاج
VLOOKUP هي صيغة قوية جدًا يمكنها تقليل كمية إدخال البيانات والأخطاء المرتبطة بها بشكل كبير ، من خلال البحث عن البيانات وجلبها تلقائيًا. من خلال العمل من خلال وظيفة VLOOKUP في مثالين خطوة بخطوة ، آمل أن أكون قد نجحت في جعل وظيفة المظهر الشاقة هذه متاحة لك وأنك ستتمكن الآن من استخدامها في جداول البيانات الخاصة بك وأن توفر لك أيضًا الكثير من الوقت والكتابة!
هذه المقالة دقيقة وصحيحة على حد علم المؤلف. المحتوى لأغراض إعلامية أو ترفيهية فقط ولا يحل محل الاستشارة الشخصية أو المهنية في الأعمال التجارية أو المالية أو القانونية أو الفنية.
