المحتويات
لقد خصصنا مؤخرًا مقالًا لواحدة من أكثر وظائف Excel فائدة تسمى VPR وأوضح كيف يمكن استخدامه لاستخراج المعلومات المطلوبة من قاعدة بيانات إلى خلية ورقة عمل. ذكرنا أيضًا أن هناك حالتي استخدام للدالة VPR واحد منهم فقط يتعامل مع استعلامات قاعدة البيانات. في هذه المقالة ، ستتعلم طريقة أخرى أقل شهرة لاستخدام الوظيفة VPR في إكسيل.
إذا لم تكن قد فعلت ذلك بعد ، فتأكد من قراءة المقال الأخير حول الوظيفة VPR، لأن جميع المعلومات الواردة أدناه تفترض أنك على دراية بالمبادئ الموضحة في المقالة الأولى.
عند العمل مع قواعد البيانات والوظائف VPR يتم تمرير معرّف فريد يستخدم لتحديد المعلومات التي نريد العثور عليها (على سبيل المثال ، رمز المنتج أو رقم تعريف العميل). يجب أن يكون هذا الرمز الفريد موجودًا في قاعدة البيانات ، وإلا VPR سيبلغ عن خطأ. في هذه المقالة ، سنلقي نظرة على طريقة استخدام الوظيفة VPRعندما لا يكون المعرف موجودًا في قاعدة البيانات على الإطلاق. كما لو كانت الوظيفة VPR إلى الوضع التقريبي ، ويختار البيانات التي تزودنا بها عندما نريد العثور على شيء ما. في ظروف معينة ، هذا هو بالضبط ما هو مطلوب.
مثال من الحياة. نحن نحدد المهمة
دعنا نوضح هذه المقالة بمثال واقعي - حساب العمولات بناءً على مجموعة واسعة من مقاييس المبيعات. سنبدأ بخيار بسيط للغاية ، ثم نعقده تدريجيًا حتى يكون الحل العقلاني الوحيد للمشكلة هو استخدام الوظيفة VPR. السيناريو الأولي لمهمتنا الوهمية هو كما يلي: إذا حقق مندوب المبيعات أكثر من 30000 دولار في المبيعات في السنة ، فإن عمولته هي 30٪. خلاف ذلك ، تبلغ العمولة 20٪ فقط. لنضعها على شكل جدول:
يُدخل البائع بيانات مبيعاته في الخلية B1 ، وتحدد الصيغة في الخلية B2 معدل العمولة الصحيح الذي يمكن أن يتوقعه البائع. في المقابل ، يتم استخدام المعدل الناتج في الخلية B3 لحساب إجمالي العمولة التي يجب أن يتلقاها البائع (ببساطة بضرب الخلايا B1 و B2).
الجزء الأكثر إثارة للاهتمام من الجدول موجود في الخلية B2 - هذه هي الصيغة لتحديد معدل العمولة. تحتوي هذه الصيغة على دالة Excel تسمى IF (إذا). بالنسبة لأولئك القراء الذين ليسوا على دراية بهذه الوظيفة ، سأشرح كيف تعمل:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
الحالة هي وسيطة دالة تأخذ قيمة أي منهما الكود الحقيقي (صحيح أو خاطئة (خاطئة). في المثال أعلاه ، التعبير B1
هل صحيح أن B1 أقل من B5؟
أو يمكنك قول ذلك بشكل مختلف:
هل صحيح أن المبلغ الإجمالي للمبيعات للسنة أقل من قيمة العتبة؟
إذا أجبنا على هذا السؤال نعم (TRUE) ، ثم ترجع الدالة القيمة إذا كانت صحيحة (القيمة إذا كانت TRUE). في حالتنا ، ستكون هذه هي قيمة الخلية B6 ، أي معدل العمولة عندما يكون إجمالي المبيعات أقل من الحد الأدنى. إذا أجبنا على السؤال لا (FALSE) يعود بعد ذلك القيمة إذا كانت خاطئة (القيمة إذا كانت خطأ). في حالتنا ، هذه هي قيمة الخلية B7 ، أي معدل العمولة عندما يكون إجمالي المبيعات أعلى من الحد الأدنى.
كما ترى ، إذا أخذنا إجمالي المبيعات 20000 دولار ، فسنحصل على معدل عمولة 2٪ في الخلية B20. إذا أدخلنا قيمة قدرها 40000 دولار ، فسيتغير معدل العمولة بنسبة 30٪:
هذه هي الطريقة التي تعمل بها طاولتنا.
نحن نعقد المهمة
لنجعل الأمور أكثر صعوبة. دعنا نضع حدًا آخر: إذا كسب البائع أكثر من 40000 دولار ، فإن معدل العمولة يرتفع إلى 40٪:
يبدو أن كل شيء بسيط وواضح ، لكن الصيغة الخاصة بنا في الخلية B2 تصبح أكثر تعقيدًا بشكل ملحوظ. إذا نظرت عن كثب إلى الصيغة ، فسترى أن الوسيطة الثالثة للدالة IF (إذا) تحولت إلى وظيفة كاملة أخرى IF (إذا). يسمى هذا البناء تداخل الوظائف في بعضها البعض. يسمح Excel لحسن الحظ بهذه التركيبات ، بل إنها تعمل ، لكن قراءتها وفهمها أصعب بكثير.
لن نتعمق في التفاصيل الفنية - لماذا وكيف يعمل ، ولن ندخل في الفروق الدقيقة في كتابة الوظائف المتداخلة. بعد كل شيء ، هذا مقال مخصص للوظيفة VPR، وليس دليلاً كاملاً لبرنامج Excel.
مهما كان الأمر ، فإن الصيغة تصبح أكثر تعقيدًا! ماذا لو قدمنا خيارًا آخر لسعر عمولة بنسبة 50٪ لهؤلاء البائعين الذين يحققون مبيعات تزيد عن 50000 دولار. وإذا باع شخص ما أكثر من 60000 دولار ، فهل سيدفع عمولة بنسبة 60٪؟
الآن أصبحت الصيغة في الخلية B2 ، حتى لو تمت كتابتها بدون أخطاء ، غير قابلة للقراءة تمامًا. أعتقد أن هناك القليل ممن يرغبون في استخدام الصيغ ذات 4 مستويات من التداخل في مشاريعهم. يجب أن يكون هناك طريقة أسهل؟!
وهناك طريقة كهذه! ستساعدنا الوظيفة VPR.
نطبق وظيفة VLOOKUP لحل المشكلة
دعونا نغير تصميم طاولتنا قليلاً. سنحتفظ بجميع الحقول والبيانات نفسها ، لكننا نرتبها بطريقة جديدة وأكثر إحكاما:
خذ لحظة وتأكد من الجدول الجديد جدول الأسعار يتضمن نفس البيانات مثل جدول العتبة السابق.
الفكرة الرئيسية هي استخدام الوظيفة VPR لتحديد معدل التعريفة المطلوب حسب الجدول جدول الأسعار حسب حجم المبيعات. يرجى ملاحظة أنه يمكن للبائع بيع البضائع بمبلغ لا يساوي أحد العتبات الخمسة في الجدول. على سبيل المثال ، يمكنه البيع بمبلغ 34988 دولارًا ، لكن لا يوجد مثل هذا المبلغ. دعونا نرى كيف تعمل VPR يمكن التعامل مع مثل هذا الموقف.
إدخال وظيفة VLOOKUP
حدد الخلية B2 (حيث نريد إدراج الصيغة الخاصة بنا) والعثور عليها VLOOKUP (VLOOKUP) في مكتبة وظائف Excel: الصيغ (الصيغ)> مكتبة الوظائف (مكتبة الوظائف)> بحث ومرجع (المراجع والمصفوفات).
يظهر مربع حوار الحجج الوظيفية (حجج الوظيفة). نملأ قيم الوسيطات واحدة تلو الأخرى ، بدءًا من ابحث عن القيمة (ابحث عن القيمة). في هذا المثال ، هذا هو المبلغ الإجمالي للمبيعات من الخلية B1. ضع المؤشر في الحقل ابحث عن القيمة (Lookup_value) وحدد الخلية B1.
بعد ذلك ، تحتاج إلى تحديد الوظائف VPRأين تبحث عن البيانات. في مثالنا ، هذا جدول جدول الأسعار. ضع المؤشر في الحقل Table_array (جدول) وحدد الجدول بأكمله جدول الأسعارباستثناء الرؤوس.
بعد ذلك ، نحتاج إلى تحديد العمود الذي نستخرج منه البيانات باستخدام صيغتنا. نحن مهتمون بسعر العمولة الموجود في العمود الثاني من الجدول. لذلك ، للحجة Col_index_num (Column_number) أدخل القيمة 2.
وأخيرًا ، نقدم الحجة الأخيرة - مجموعة البحث (Interval_lookup).
هام: إن استخدام هذه الحجة هو الذي يصنع الفرق بين طريقتين لتطبيق الوظيفة VPR. عند العمل مع قواعد البيانات ، فإن الحجة مجموعة البحث يجب أن يكون لـ (range_lookup) قيمة دائمًا خاطئة (FALSE) للبحث عن تطابق تام. في استخدامنا للدالة VPR، يجب ترك هذا الحقل فارغًا أو إدخال قيمة الكود الحقيقي (صحيح). من المهم للغاية اختيار هذا الخيار بشكل صحيح.
لتوضيح الأمر ، سوف نقدمه الكود الحقيقي (صحيح) في الميدان مجموعة البحث (Interval_lookup). على الرغم من أنك إذا تركت الحقل فارغًا ، فلن يكون هذا خطأ ، منذ ذلك الحين الكود الحقيقي هي قيمته الافتراضية:
لقد ملأنا جميع المعلمات. الآن نضغط OK، ويقوم Excel بإنشاء صيغة لنا مع وظيفة VPR.
إذا جربنا عدة قيم مختلفة لإجمالي مبلغ المبيعات ، فسنتأكد من أن الصيغة تعمل بشكل صحيح.
وفي الختام
عندما تكون الوظيفة VPR يعمل مع قواعد البيانات والحجة مجموعة البحث (range_lookup) يجب أن يقبل خاطئة (خاطئة). ودخلت القيمة كـ ابحث عن القيمة (Lookup_value) يجب أن يكون موجودًا في قاعدة البيانات. بمعنى آخر ، إنه يبحث عن تطابق تام.
في المثال الذي نظرنا إليه في هذه المقالة ، ليست هناك حاجة للحصول على تطابق تام. هذا هو الحال عندما وظيفة VPR يجب التبديل إلى الوضع التقريبي لإرجاع النتيجة المرجوة.
فمثلا: نريد تحديد السعر الذي يجب استخدامه في حساب العمولة لمندوب مبيعات بحجم مبيعات قدره 34988 دولارًا. دور VPR تعيد لنا قيمة 30٪ ، وهذا صحيح تمامًا. ولكن لماذا حددت الصيغة الصف الذي يحتوي على 30٪ بالضبط وليس 20٪ أو 40٪؟ ما المقصود بالبحث التقريبي؟ دعونا نكون واضحين.
عندما الحجة مجموعة البحث (interal_lookup) له قيمة الكود الحقيقي (TRUE) أو مهملة ، دالة VPR يتكرر خلال العمود الأول ويحدد أكبر قيمة لا تتجاوز قيمة البحث.
نقطة مهمة: لكي يعمل هذا المخطط ، يجب فرز العمود الأول من الجدول بترتيب تصاعدي.