في مقال حديث، قدمنا وظيفة Excel تسمى فلوكوب وأوضح كيف يمكن استخدامه لاسترداد المعلومات من قاعدة بيانات إلى خلية في ورقة عمل محلية. ذكرنا في تلك المقالة أن هناك استخدامين لـ VLOOKUP، أحدهما فقط تناول الاستعلام عن قواعد البيانات. في هذه المقالة، الثانية والأخيرة في سلسلة VLOOKUP، سنفحص هذا الاستخدام الآخر الأقل شهرة لوظيفة VLOOKUP. إذا لم تكن قد قمت بذلك بالفعل، يرجى قراءة مقالة VLOOKUP الأولى – ستفترض هذه المقالة أن العديد من المفاهيم الموضحة في هذه المقالة معروفة بالفعل للقارئ. عند العمل مع قواعد البيانات، يتم تمرير VLOOKUP “معرفًا فريدًا” يعمل على تحديد سجل البيانات الذي نرغب في العثور عليه في قاعدة البيانات (على سبيل المثال، رمز المنتج أو معرف العميل). يجب أن يكون هذا المعرف الفريد موجودًا في قاعدة البيانات، وإلا فسترجع لنا VLOOKUP خطأً. في هذه المقالة، سوف ندرس طريقة لاستخدام VLOOKUP حيث لا يلزم وجود المعرف في قاعدة البيانات على الإطلاق. يبدو الأمر كما لو أن VLOOKUP يمكنها اعتماد منهج “القريب بما فيه الكفاية جيد بما فيه الكفاية” لإرجاع البيانات التي نبحث عنها. وفي ظروف معينة، هذا هو بالضبط ما نحتاجه. سنوضح هذه المقالة بمثال واقعي، وهو حساب العمولات التي يتم إنشاؤها على مجموعة من أرقام المبيعات. سنبدأ بسيناريو بسيط للغاية، ثم نجعله أكثر تعقيدًا تدريجيًا، حتى يصبح الحل العقلاني الوحيد للمشكلة هو استخدام VLOOKUP. يعمل السيناريو الأولي في شركتنا الوهمية على النحو التالي: إذا قام مندوب المبيعات بإنشاء مبيعات تزيد قيمتها عن 30 ألف دولار في سنة معينة، فإن العمولة التي يكسبها على تلك المبيعات تبلغ 30٪. وإلا عمولتهم هي 20٪ فقط. هذه ورقة عمل بسيطة جدًا حتى الآن: لاستخدام ورقة العمل هذه، يقوم مندوب المبيعات بإدخال أرقام مبيعاته في الخلية B1، وتقوم الصيغة الموجودة في الخلية B2 بحساب معدل العمولة الصحيح الذي يحق لهم الحصول عليه، والذي يتم استخدامه في الخلية B3 لحساب معدل العمولة الصحيح. إجمالي العمولة المستحقة على مندوب المبيعات (وهي عبارة عن ضرب بسيط لـ B1 وB2). تحتوي الخلية B2 على الجزء الوحيد المثير للاهتمام من ورقة العمل هذه – صيغة تحديد سعر العمولة الذي سيتم استخدامه: السعر الذي يقل عن الحد الأدنى البالغ 30000 دولار، أو السعر الذي يتجاوز الحد. تستخدم هذه الصيغة وظيفة Excel تسمى لو. بالنسبة لأولئك القراء الذين ليسوا على دراية بـ IF، فهو يعمل على النحو التالي:
إذا (الشرط، القيمة إذا كانت صحيحة، القيمة إذا كانت خاطئة)
حيث يكون الشرط عبارة عن تعبير يتم تقييمه لأي منهما حقيقي أو خطأ شنيع. في المثال أعلاه، الشرط هو التعبير ب1، والتي يمكن قراءتها كـ “هل B1 أقل من B5؟”، أو بعبارة أخرى، “هل إجمالي المبيعات أقل من الحد الأدنى”. إذا كانت الإجابة على هذا السؤال هي “نعم” (صحيح)، فإننا نستخدم قيمة if true معلمة الدالة، وهي ب6 في هذه الحالة – معدل العمولة إذا كان إجمالي المبيعات أقل من العتبة. إذا كانت إجابة السؤال هي “لا” (خطأ)، فإننا نستخدم القيمة if false للدالة، وهي ب7 في هذه الحالة – معدل العمولة إذا كان إجمالي المبيعات أعلى من العتبة. كما ترون، فإن استخدام إجمالي مبيعات قدره 20000 دولار يمنحنا معدل عمولة قدره 20% في الخلية B2. إذا أدخلنا قيمة قدرها 40.000 دولار، فسنحصل على معدل عمولة مختلف:
لذا فإن جدول البيانات لدينا يعمل. دعونا نجعل الأمر أكثر تعقيدا. دعنا نقدم العتبة الثانية: إذا كان مندوب المبيعات يكسب أكثر من 40 ألف دولار، فإن معدل عمولته يرتفع إلى 40٪:
من السهل فهمها في العالم الحقيقي، ولكن في الخلية B2 أصبحت صيغتنا أكثر تعقيدًا. إذا نظرت عن كثب إلى الصيغة، فسترى أن المعلمة الثالثة للدالة IF الأصلية (القيمة إذا كانت false) أصبحت الآن دالة 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 من الخلية B2. الصيغ فاتورة غير مدفوعة:
ال الحجج الوظيفية يظهر مربع VLOOKUP. نقوم بملء الوسائط (المعلمات) واحدة تلو الأخرى، بدءًا من Lookup_value، وهو، في هذه الحالة، إجمالي المبيعات من الخلية B1. نضع المؤشر في Lookup_value الحقل ثم انقر مرة واحدة على الخلية B1:
بعد ذلك نحتاج إلى تحديد الجدول الذي سنبحث فيه عن هذه البيانات لـ VLOOKUP. في هذا المثال، هو جدول المعدل بالطبع. نضع المؤشر في Table_array الحقل، ثم قم بتمييز جدول الأسعار بالكامل – باستثناء العناوين:
بعد ذلك يجب علينا تحديد أي عمود في الجدول يحتوي على المعلومات التي نريد أن تعود إلينا صيغتنا. في هذه الحالة نريد سعر العمولة الموجود في العمود الثاني في الجدول، لذلك نقوم بإدخال أ 2 في Col_index_num مجال:
وأخيرا نقوم بإدخال قيمة في Range_lookup مجال. هام: إن استخدام هذا الحقل هو الذي يميز طريقتين لاستخدام VLOOKUP. لاستخدام VLOOKUP مع قاعدة بيانات، هذه المعلمة النهائية، Range_lookup، يجب دائمًا ضبطه على خطأ شنيع، ولكن مع هذا الاستخدام الآخر لـ VLOOKUP، يجب علينا إما تركه فارغًا أو إدخال قيمة حقيقي. عند استخدام VLOOKUP، من الضروري أن تقوم بالاختيار الصحيح لهذه المعلمة النهائية. لكي نكون صريحين، سوف نقوم بإدخال قيمة حقيقي في Range_lookup مجال. وسيكون من الجيد أيضًا تركها فارغة، لأن هذه هي القيمة الافتراضية:
لقد أكملنا جميع المعلمات. نقوم الآن بالنقر على نعم الزر، ويقوم Excel بإنشاء صيغة VLOOKUP لنا:
إذا قمنا بتجربة عدد قليل من المبالغ الإجمالية للمبيعات المختلفة، فيمكننا التأكد من أن الصيغة تعمل. خاتمة في إصدار “قاعدة البيانات” من VLOOKUP، حيث يوجد ملف Range_lookup المعلمة هي خطأ شنيع، القيمة التي تم تمريرها في المعلمة الأولى (Lookup_value) يجب أن تكون موجودة في قاعدة البيانات. وبعبارة أخرى، نحن نبحث عن تطابق تام. لكن في هذا الاستخدام الآخر لـ VLOOKUP، لا نبحث بالضرورة عن تطابق تام. في هذه الحالة، “القريب بما فيه الكفاية جيد بما فيه الكفاية”. ولكن ماذا نعني بـ “قريب بما فيه الكفاية”؟ لنستخدم مثالاً: عند البحث عن معدل عمولة على إجمالي مبيعات قدره 34,988 دولارًا أمريكيًا، ستعيد لنا صيغة VLOOKUP قيمة 30%، وهي الإجابة الصحيحة. لماذا اختار الصف في الجدول الذي يحتوي على 30%؟ في الواقع، ماذا تعني عبارة “قريب بما فيه الكفاية” في هذه الحالة؟ لنكن دقيقين:
متى
Range_lookup
تم ضبطه على
حقيقي
(أو تم حذفها)، ستبحث VLOOKUP في العمود 1 وستطابقها
أعلى قيمة ليست أكبر من
ال
Lookup_value
المعلمة.
ومن المهم أيضًا ملاحظة أنه لكي يعمل هذا النظام، يجب فرز الجدول بترتيب تصاعدي في العمود 1! إذا كنت ترغب في التدريب على استخدام VLOOKUP، فيمكن تنزيل نموذج الملف الموضح في هذه المقالة من هنا.
(العلامات للترجمة) مكتب (ر) مايكروسوفت إكسل