1Aug
في مقالة حديثة، قدمنا دالة إكسيل تسمى فلوكوب وأوضح كيف يمكن استخدامه لاسترداد المعلومات من قاعدة بيانات إلى خلية في ورقة عمل محلية.في هذه المقالة ذكرنا أن هناك استخدامين ل فلوكوب، واحد فقط منهم التعامل مع قواعد بيانات الاستعلام.في هذه المقالة، الثانية والأخيرة في سلسلة فلوكوب، ونحن نفحص هذا الاستخدام الأخرى أقل شهرة لوظيفة فلوكوب.
إذا لم تكن قد قمت بذلك بالفعل، يرجى قراءة مقالة فلوكوب الأولى - تفترض هذه المقالة أن العديد من المفاهيم الموضحة في هذه المقالة معروفة للقارئ بالفعل.
عند العمل مع قواعد البيانات، يتم تمرير فلوكوب "معرف فريد" يعمل على تحديد سجل البيانات الذي نرغب في العثور عليه في قاعدة البيانات( على سبيل المثال رمز المنتج أو كود العميل).يجب أن يكون هذا معرف فريد موجود في قاعدة البيانات وإلا فلوكوب يعود لنا خطأ.في هذه المقالة، سوف ندرس طريقة استخدام فلوكوب حيث لا يحتاج المعرف إلى وجود في قاعدة البيانات على الإطلاق.انها تقريبا كما لو فلوكوب يمكن اعتماد نهج "قريبة بما فيه الكفاية جيدة بما فيه الكفاية" لإعادة البيانات التي نبحث عنها.في ظروف معينة، وهذا هو بالضبط ما نحتاج إليه.
سنوضح هذه المقالة بمثال واقعي - وهو حساب العمولات التي يتم توليدها على مجموعة من أرقام المبيعات.سوف نبدأ مع سيناريو بسيط جدا، ومن ثم جعل تدريجيا أكثر تعقيدا، حتى الحل الرشيد الوحيد للمشكلة هو استخدام فلوكوب.السيناريو الأولي في الشركة الوهمية لدينا يعمل على هذا النحو: إذا كان مندوب مبيعات يخلق أكثر من 30،000 $ قيمة المبيعات في سنة معينة، والعمولة التي يكسبون على هذه المبيعات هو 30٪.وإلا فإن عمولاتهم هي 20٪ فقط.حتى الآن هذه ورقة عمل بسيطة جدا:
لاستخدام ورقة العمل هذه، يدخل مندوب المبيعات أرقام مبيعاتهم في الخلية B1، وتحسب الصيغة في الخلية B2 معدل العمولة الصحيح الذي يحق لهم الحصول عليه، والذي يستخدم في الخلية B3 لحسابالعمولة الكلية التي يقوم بها مندوب المبيعات( وهو ضرب بسيط من B1 و B2).
تحتوي الخلية B2 على الجزء المثير للاهتمام فقط من ورقة العمل هذه - صيغة تحديد سعر العمولة المطلوب استخدامه: واحد أسفل عتبة 30،000 $ أو الحد الأقصى الموضح .تستخدم هذه الصيغة دالة إكسيل تسمى إف .بالنسبة إلى القراء الذين ليسوا على دراية إف، يعمل مثل هذا:
إف( الشرط والقيمة إذا ترو والقيمة إذا كاذب )
حيث الشرط هو تعبير الذي يقيم إما صحيح أو فالس .في المثال أعلاه، الشرط هو تعبير B1 & لوت؛ B5 ، الذي يمكن قراءته ب "هل B1 أقل من B5؟"، أو وضع طريقة أخرى "هل إجمالي المبيعات أقل من الحد الأدنى".إذا كان الجواب على هذا السؤال هو "نعم"( صحيح)، ثم نستخدم قيمة إذا المعلمة صحيح من الدالة، وهي B6 في هذه الحالة - معدل العمولة إذا كان إجمالي المبيعات أدناه عتبة.إذا كان الجواب على السؤال هو "لا"( كاذبة)، ثم نستخدم قيمة إذا معلمة كاذبة من الدالة، وهي B7 في هذه الحالة - معدل العمولة إذا كان مجموع المبيعات أعلاه العتبة.
كما ترون، وذلك باستخدام إجمالي المبيعات من 20،000 $ يعطينا معدل العمولة من 20٪ في الخلية B2.إذا أدخلنا قيمة $ 40،000، نحصل على سعر عمولة مختلف:
لذلك جدول البيانات لدينا يعمل.
دعونا نجعلها أكثر تعقيدا.دعونا إدخال عتبة الثانية: إذا كان مندوب مبيعات يكسب أكثر من 40،000 $، ثم يزيد معدل عمولاتهم إلى 40٪:
سهلة بما يكفي لفهم في العالم الحقيقي، ولكن في الخلية B2 صيغة لدينا هو الحصول على أكثر تعقيدا.إذا كنت تبحث عن كثب في الصيغة، سترى أن المعلمة الثالثة من الدالة إف الأصلي( قيمة إذا كاذب ) هي الآن وظيفة إف بالكامل في حد ذاتها.وهذا ما يسمى الدالة المتداخلة ( دالة داخل دالة).انها صالحة تماما في إكسيل( حتى يعمل!)، ولكن من الصعب قراءة وفهم.
لن نذهب إلى المكسرات والمسامير لكيفية ولماذا يعمل هذا، كما أننا لن ندرس الفروق الدقيقة في الوظائف المتداخلة.هذا هو البرنامج التعليمي على فلوكوب، وليس على إكسيل بشكل عام.
على أي حال، فإنه يزداد سوءا!ماذا عن عندما نقرر أنه إذا كان يكسب أكثر من 50،000 $ ثم انهم يستحقون عمولة 50٪، وإذا كانوا يكسبون أكثر من 60،000 $ ثم انهم يستحقون عمولة 60٪؟
الآن الصيغة في الخلية B2، في حين الصحيح، أصبح غير قابل للقراءة تقريبا.لا أحد يجب أن يكون لكتابة الصيغ حيث يتم تداخل وظائف أربعة مستويات عميقة!بالتأكيد يجب أن يكون هناك طريقة أبسط؟
هناك بالتأكيد.فلوكوب لانقاذ!
دعونا إعادة تصميم ورقة العمل قليلا.سنحافظ على نفس الأرقام، ولكن ننظمها بطريقة جديدة، أكثر جدول الطريقة:
خذ لحظة وتحقق لنفسك أن معدل الجدول يعمل بالضبط نفس سلسلة من العتبات أعلاه.
من الناحية النظرية، ما نحن على وشك القيام به هو استخدام فلوكوب للبحث عن إجمالي مبيعات مندوب مبيعات( من B1) في جدول معدل والعودة لنا سعر العمولة المقابلة.لاحظ أن مندوب المبيعات قد يكون قد خلق بالفعل المبيعات التي لا واحدة من القيم الخمس في جدول السعر( $ 0، $ 30،000، $ 40،000، $ 50،000 أو $ 60،000).وقد تكون قد أنشأت مبيعات قدرها 988 34 دولارا.من المهم أن نلاحظ أن 34،988 $ لا لا تظهر في جدول المعدل.دعونا نرى ما إذا كان يمكن حل فلوكوب حل مشكلتنا على أي حال. ..
نختار الخلية B2( الموقع الذي نريد وضع الصيغة لدينا) ثم قم بإدراج الدالة فلوكوب من علامة التبويب الصيغ :
يظهر مربع الوسيطات الدالة ل فلوكوب.نملأ الحجج( معلمات) واحدا تلو الآخر، بدءا من Lookup_value ، وهو، في هذه الحالة، إجمالي المبيعات من الخلية B1.نضع المؤشر في الحقل Lookup_value ثم انقر فوق مرة واحدة على الخلية B1:
التالي نحتاج إلى تحديد فلوكوب ما الجدول للبحث عن هذه البيانات في. في هذا المثال، انها جدول معدل، بطبيعة الحال.نضع المؤشر في الحقل Table_array ثم قم بتمييز جدول المعدل بالكامل - باستثناء العناوين :
التالي يجب علينا تحديد العمود الذي يحتوي على المعلومات التي نريد أن ترجع إلينا الصيغة الخاصة بنا.في هذه الحالة نريد معدل العمولة الذي يتم العثور عليه في العمود الثاني في الجدول ولذلك فإننا أدخل 2 في الحقل COL_index_num :
وأخيرا ندخل قيمة في الحقل range_lookup .
هام: هو استخدام هذا الحقل الذي يميز بين طريقتين لاستخدام فلوكوب.لاستخدام فلوكوب مع قاعدة بيانات، يجب تعيين هذه المعلمة النهائية range_lookup دائما إلى فالس ولكن مع هذا الاستخدام الآخر من فلوكوب يجب إما تركه فارغا أو إدخال قيمة ترو .عند استخدام فلوكوب، فمن الضروري أن تقوم بإجراء الاختيار الصحيح لهذه المعلمة النهائية.
لتكون صريحة، سوف نقوم بإدخال قيمة صحيح في الحقل range_lookup .سيكون من الجيد أيضا تركه فارغا، حيث أن هذه هي القيمة الافتراضية:
لقد أكملت جميع المعلمات.نضغط الآن على الزر موافق ، ويقوم إكسيل ببناء صيغة فلوكوب لنا:
إذا قمنا بتجربة عدد قليل من المبيعات الإجمالية، يمكننا أن نرضي أنفسنا بأن الصيغة تعمل.
الاستنتاج
في النسخة "قاعدة البيانات" من فلوكوب حيث المعلمة range_lookup فالس يجب أن تكون القيمة التي تم تمريرها في المعلمة الأولى( Lookup_value ) موجودة في قاعدة البيانات .وبعبارة أخرى، نحن نبحث عن مطابقة تامة.
ولكن في هذا الاستخدام الآخر من فلوكوب، نحن لسنا بالضرورة تبحث عن تطابق تام.في هذه الحالة، "بالقرب بما فيه الكفاية جيدة بما فيه الكفاية".ولكن ماذا نعني ب "شبه كافية"؟لنستخدم مثالا: عند البحث عن سعر عمولة على إجمالي مبيعات يبلغ 34،988 دولارا، ستعيد لنا صيغة فلوكوب قيمة 30٪، وهي الإجابة الصحيحة.لماذا اختارت الصف في الجدول الذي يحتوي على 30٪؟ما، في الواقع، لا "قريبة بما فيه الكفاية" يعني في هذه الحالة؟دعونا نكون دقيقين:
عندما يتم تعيين range_lookup إلى ترو ( أو حذف)، سوف ننظر في العمود 1 وتطابق أعلى قيمة ليست أكبر من المعلمة Lookup_value .
ومن المهم أيضا أن نلاحظ أنه لهذا النظام للعمل، يجب فرز الجدول في ترتيب تصاعدي على العمود 1 !
إذا كنت ترغب في ممارسة مع فلوكوب، نموذج الملف الموضح في هذه المقالة يمكن تحميلها من هنا.