Image DMIN with DataTable

إستخدام الـ DataTable  كبديل لمعادلة MinIf and MaxIf

واحدة من معادلات التحليل المهمة هي معرفة أقل قيمة مبيعات بناء على إسم الموظف , بمعنى أصغر قيمة بناء على معيار معين, أو أكبر قيمة, في المعادلة العادية يوفر الإكسيل هذه الميزة في ثلاث  حالات :

1.       الجمع الشرطي بإستخدام معادلة SumIF .

2.       العد الشرطي بإستخدام معادلة CountIF

3.       متوسط الحساب الشرطي بإستخدام معادلة AverageIF

 و لكن لا يوجد معادلة بشكل مباشر لمعرفة أكبر أو أصغر قيمة بإستخدام شرط, و لكن للمستخدمين المحترفين يمكن إستخدام معادلة الصفيف Array Formula,و ستجدون شرحها في مدونتي , لكن اليوم أحببت أن أشارككم في طريقة جديدة بإستخدام معادلات قواعد البيانات و إستخدام إحدى أدوات الـ WhatIf Analysis  , و سنبدأ في التطبيق مباشرة حتى تتضح الصورة, و هذه هي البيانات التي سنعمل عليها :

و في النطاق G1:G2 سنضع إسم العامود و المعيار الذي نبحث عنه, و في مثالنا سنتعامل مع أسماء الموظفين و سنختار إسم يحيى, بالشكل التالي :

و لاحظ اننا في الخلية H1 و ضعنا عنوان العامود الذي سنأخذ المبيعات منه, و الآن سنستخدم المعادلة DMIN, حيث تتكون هذه المعادلة من ثلاث متغيرات و الشكل العام لهذه المعادلة هي  :

و المتغيرات حسب الترتيب هي :

1.       Database : و المقصود بها كامل البيانات في الجدول أعلاه مع التأكيد على تحديد عنوان العامود Hdears, و في مثالنا البيانات في كامل النطاق A1:C20

2.       Field : عنوان العامود الذي نرغب في إستخراج الرقم منه, و في مثالنا هو H1 .

3.       Criteria : منطقة تحديد المعيار و يجب ربطها ما بين عنوان العامود و المعيار الذي نبحث عنه و في مثالنا هي G1:G2

و بناء على ما تقدم سيكون شكل المعادلة و المتغيرات كالتالي

و بناء على كل المعطيات السابقة ستكون النتيجة كما يلي  :

و لقد قمت بتلوين الخلايا التي تحتوي إسم يحيى باللون الأسود للتوضيح و سنجد أن أقل قيمة مبيعات ليحيى هي 1094

و من المثال السابق إستخرجنا النتيجة فقط ليحيى, و لكن يوجد لدي أسماء أخرى, و هي :

Yousef

Ali

Nader

Jamal

Emad

و هذه المعادلة صعب سحبها لباقي الأسماء, و لكن يبقى هنا البديل هو إستخدام الـ DataTable بمتغير واحد, و لتوضيح آلية إستخدامها سنضع باقي الأسماء في ترتيب أفقي أو عامودي حسب التقرير, و أصبح شكل التقرير كالتالي :

ملاحظات مهمه هنا :

في الخلية H4 ربطنا قيمتها مع قيمة الخلية H2 و التي تحتوي المعادلة الأصلية و نحن نرغب في تطبيق المعادلة الموجودة  في الخلية H2 لكل الأسماء في العامود G

 و لإستخدام الـ DataTable  مع متغير واحد يجب أن تكون التقرير في تنسيق واحد كما في الصورة التالية :

و هذا الشكل العام لها و هنا أيضاً يجب الإنتباه أن الخلية الأولى فارغة و هو ما قمنا بتصميمه في تقريرنا.

الخطوة التالية هي تحديد النطاق الكامل لإستخدام الـ DataTable  و هو G4:H9

ثم من تاب Data نختار WhatIf Analysis  و منها نختار DataTable و هو كما نلاحظ مربع صغير جدا بخيارين فقط

و بما أن الأسماء مرتبه بشكل عامودي فإن المتغير الي سنتعامل معه هو

Column input Cell

و تبقى الفكرة الأخير ما هو المرجع الذي سنقوم من خلاله بربط التقرير مع الـ Data Table

و لتوضيح الآلية نريد من الإكسيل إستبدال كل إسم في النطاق الذي حددناه مع الإسم الموجود في مرجع المعادلة الإصلية و هو إسم يحيى الموجود في الخلية  G2, إذاً ستكون حلقة الوصل هي هذه الخلية و سنختارها كما في الشكل التالي :

  

و هنا حصلنا على الشكل الأخير للتقرير و هي أقل قيمة مبيعات لكل موظف :

أ

تمنى أن تكون المعلومة مفيدة بالنسبة, دمتم في حفظ الله