VLOOKUP في Excel ، الجزء 2 استخدام VLOOKUP بدون قاعدة بيانات

في مقالة حديثة ، قدمنا وظيفة Excel تسمى VLOOKUP وشرح كيف يمكن استخدامه لاسترداد المعلومات من قاعدة البيانات إلى خلية في ورقة عمل محلية. في هذه المقالة ، ذكرنا أن هناك استخدامين لـ VLOOKUP ، وتعامل واحد منها فقط مع الاستعلام عن قواعد البيانات. في هذه المقالة ، الثانية والأخيرة في سلسلة VLOOKUP ، ندرس هذا الاستخدام الآخر الأقل استخدامًا لدالة VLOOKUP.

إذا لم تكن قد قمت بذلك بالفعل ، فالرجاء قراءة مقالة VLOOKUP الأولى - تفترض هذه المقالة أن العديد من المفاهيم التي تم شرحها في هذه المقالة معروفة للقارئ بالفعل.

عند العمل مع قواعد البيانات ، يتم تمرير VLOOKUP "معرف فريد" يعمل على تحديد سجل البيانات الذي نرغب في العثور عليه في قاعدة البيانات (على سبيل المثال ، كود المنتج أو معرف العميل). هذا المعرف الفريد يجب موجودة في قاعدة البيانات ، وإلا VLOOKUP يعيدنا خطأ. في هذه المقالة ، سنفحص طريقة استخدام VLOOKUP حيث لا يحتاج المعرّف إلى الوجود في قاعدة البيانات على الإطلاق. يبدو الأمر كما لو أن برنامج VLOOKUP يمكنه اعتماد منهج "قريب بما يكفي جيدًا بما فيه الكفاية" لإعادة البيانات التي نبحث عنها. في ظروف معينة ، هذا هو بالضبط ماذا نحتاج.

سنوضح هذا المقال بمثال واقعي - هو حساب العمولات التي يتم إنشاؤها على مجموعة من أرقام المبيعات. سنبدأ بسيناريو بسيط للغاية ، ومن ثم سنجعله أكثر تعقيدًا ، حتى يصبح الحل المنطقي الوحيد للمشكلة هو استخدام VLOOKUP. السيناريو الأول في شركتنا الخيالية يعمل على هذا النحو: إذا كان مندوب مبيعات يخلق مبيعات بقيمة أكثر من 30،000 دولار في سنة معينة ، فإن العمولة التي يكسبونها في تلك المبيعات هي 30٪. خلاف ذلك عمولة هي 20 ٪ فقط. حتى الآن هذه ورقة عمل بسيطة:

لاستخدام ورقة العمل هذه ، يدخل مندوب المبيعات أرقام مبيعاتهم في الخلية B1 ، وتحسب الصيغة في الخلية B2 معدل العمولة الصحيح الذي يحق لهم تلقيه ، والذي يستخدم في الخلية B3 لحساب إجمالي العمولة التي يستحقها مندوب المبيعات ( هو ضرب بسيط من B1 و B2).

تحتوي الخلية B2 على الجزء المثير الوحيد من ورقة العمل هذه - وهي صيغة تحديد سعر العمولة المطلوب استخدامه: واحد أدناه عتبة 30،000 دولار ، أو واحد في الاعلى العتبة. هذه الصيغة تستخدم وظيفة Excel تسمى إذا. بالنسبة لأولئك القراء الذين ليسوا على دراية بـ IF ، فإنها تعمل على النحو التالي:

إذا(الشرط ، القيمة إذا كانت صحيحة ، القيمة إذا كانت خاطئة)

أين ال شرط هو تعبير يقيّم إما صحيح أو خاطئة. في المثال أعلاه ، شرط هو التعبير B1, والتي يمكن قراءتها كـ "هل B1 أقل من B5؟" ، أو ، بطريقة أخرى ، "هل إجمالي المبيعات أقل من الحد الأدنى". إذا كان الجواب على هذا السؤال هو "نعم" (صحيح) ، فإننا نستخدم قيمة إذا كان صحيحا معلمة من وظيفة ، وهي B6 في هذه الحالة - سعر العمولة إذا كان إجمالي المبيعات أدناه العتبة. إذا كانت الإجابة على السؤال "لا" (خطأ) ، فإننا نستخدم قيمة إذا كاذبة معلمة من وظيفة ، وهي B7 في هذه الحالة - سعر العمولة إذا كان إجمالي المبيعات في الاعلى العتبة.

كما ترى ، فإن استخدام إجمالي مبيعات قدره 20،000 دولار يمنحنا عمولة قدرها 20٪ في الخلية B2. إذا أدخلنا قيمة قدرها 40000 دولار ، فسنحصل على سعر عمولة مختلف:

لذلك يعمل جدول البيانات لدينا.

دعونا نجعلها أكثر تعقيدًا. دعونا نقدم عتبة ثانية: إذا كان مندوب المبيعات يكسب أكثر من 40000 دولار ، فإن معدل عموله يزيد إلى 40٪:

سهل بما فيه الكفاية لفهمه في العالم الحقيقي ، ولكن في الخلية B2 أصبحت الصيغة أكثر تعقيدًا. إذا نظرت عن كثب إلى الصيغة ، سترى أن المعلمة الثالثة من الدالة IF الأصلي ( قيمة إذا كاذبة) الآن وظيفة IF كاملة في حد ذاتها. هذا ما يسمى ب وظيفة متداخلة (وظيفة داخل وظيفة). انها صالحة تماما في Excel (حتى يعمل!) ، ولكن من الصعب قراءة وفهم.

لن ندخل في الصواميل والمسامير حول كيفية ولماذا يعمل هذا ، ولن نفحص الفروق الدقيقة للوظائف المتداخلة. هذا هو برنامج تعليمي على VLOOKUP ، وليس على Excel بشكل عام.

على أي حال ، يصبح أسوأ! ماذا عندما نقرر أنه إذا كسبوا أكثر من 50.000 دولار ، يحق لهم الحصول على 50٪ عمولة ، وإذا كسبوا أكثر من 60.000 دولار ، فيحق لهم 60٪ عمولة?

الآن أصبحت الصيغة في الخلية B2 ، بينما صحيحة ، غير قابل للقراءة. لا ينبغي لأحد أن يكتب الصيغ حيث تتداخل الوظائف أربع مستويات! بالتأكيد يجب أن يكون هناك طريقة أبسط?

هناك بالتأكيد. VLOOKUP للانقاذ!

دعونا إعادة تصميم ورقة العمل قليلا. سنحتفظ بجميع الأرقام نفسها ، ولكن ننظمها بطريقة جديدة ، أكثر من ذلك مجدول الطريقة:

خذ لحظة وتحقق لنفسك من أن الجديد جدول معدل يعمل بالضبط نفس سلسلة العتبات أعلاه.

من الناحية المفاهيمية ، ما نحن بصدد القيام به هو استخدام VLOOKUP للبحث عن إجمالي مبيعات مندوب المبيعات (من B1) في جدول السعر وإرجاع إلينا سعر العمولة المقابل. لاحظ أن مندوب المبيعات ربما قام بالفعل بإنشاء مبيعات ليس واحدة من القيم الخمس في جدول السعر ($ 0 ، $ 30،000 ، $ 40،000 ، $ 50،000 أو $ 60،000). ربما تكون قد حققت مبيعات بقيمة 34،988 دولارًا أمريكيًا. من المهم ملاحظة أن 34،988 دولارًا أمريكيًا يفعل ذلك ليس تظهر في جدول المعدل. دعونا نرى ما إذا كان VLOOKUP يمكن أن يحل مشكلتنا على أي حال ...

نختار الخلية B2 (الموقع الذي نريد وضع صيغته) ، ثم ندخل الدالة VLOOKUP من الصيغ التبويب:

ال وسيطات الدالة المربع الخاص بـ VLOOKUP. نحن ملء الحجج (المعلمات) واحدا تلو الآخر ، بدءا من ابحث عن القيمة, وهو ، في هذه الحالة ، إجمالي المبيعات من الخلية B1. نضع المؤشر في ابحث عن القيمة الحقل ثم انقر مرة واحدة على الخلية B1:

بعد ذلك نحتاج إلى تحديد VLOOKUP أي جدول للبحث عن هذه البيانات. في هذا المثال ، يكون جدول الأسعار ، بالطبع. نضع المؤشر في Table_array الحقل ، ثم قم بتمييز جدول المعدل بالكامل - باستثناء العناوين:

بعد ذلك ، يجب أن نحدد العمود الموجود في الجدول الذي يحتوي على المعلومات التي نرغب في أن تعود إليها صيغتنا. في هذه الحالة ، نريد سعر العمولة ، الذي يوجد في العمود الثاني في الجدول ، لذلك ندخل 2 داخل ال Col_index_num حقل:

أخيرا ندخل قيمة في مجموعة البحث حقل.

هام: هو استخدام هذا الحقل الذي يميز طريقتين لاستخدام VLOOKUP. لاستخدام VLOOKUP مع قاعدة بيانات ، هذه المعلمة النهائية, مجموعة البحث, يجب أن يتم تعيينها دائمًا خاطئة, ولكن مع هذا الاستخدام الآخر لـ VLOOKUP ، يجب علينا إما تركه فارغًا أو إدخال قيمة صحيح. عند استخدام VLOOKUP ، من الضروري تحديد الاختيار الصحيح لهذه المعلمة النهائية.

لكي نكون واضحين ، سنقوم بإدخال قيمة صحيح في ال مجموعة البحث حقل. سيكون من الأفضل أيضًا تركه فارغًا ، نظرًا لأن هذه هي القيمة الافتراضية:

لقد أكملنا جميع المعلمات. نحن الآن انقر فوق حسنا الزر ، ويقوم Excel بإنشاء صيغة VLOOKUP الخاصة بنا لنا:

إذا قمنا بتجربة عدد قليل من المبالغ الإجمالية للمبيعات ، يمكننا أن نرضي أنفسنا أن الصيغة تعمل.

استنتاج

في نسخة "قاعدة البيانات" من VLOOKUP ، حيث مجموعة البحث المعلمة هي خاطئة, القيمة التي تم تمريرها في المعلمة الأولى (ابحث عن القيمة) يجب تكون موجودة في قاعدة البيانات. بمعنى آخر ، نحن نبحث عن تطابق تام.

ولكن في هذا الاستخدام الآخر لـ VLOOKUP ، لا نبحث بالضرورة عن مطابقة تامة. في هذه الحالة ، "قرب ما يكفي جيدًا بما يكفي". لكن ماذا نعني بـ "قريب بما فيه الكفاية"؟ دعونا نستخدم مثالاً: عند البحث عن سعر عمولة على إجمالي مبيعات يبلغ 34،988 دولارًا أمريكيًا ، ستُرجع إلينا صيغة VLOOKUP قيمة 30٪ ، وهي الإجابة الصحيحة. لماذا اخترت الصف في الجدول الذي يحتوي على 30 ٪؟ ما الذي يعني ، في الواقع ، "قريب بما فيه الكفاية" في هذه الحالة؟ دعونا نكون دقيقين:

متى مجموعة البحث تم تعيين ل صحيح (أو تم حذفها) ، ستبدو VLOOKUP في العمود 1 ومطابقتها أعلى قيمة ليست أكبر من ال ابحث عن القيمة معامل.

من المهم أيضًا ملاحظة أنه لكي يعمل هذا النظام, يجب فرز الجدول بترتيب تصاعدي في العمود 1!

إذا كنت ترغب في التمرن مع VLOOKUP ، فيمكن تنزيل ملف نموذج موضح في هذه المقالة من هنا.