البحث عن مدي من القيمفي Excel

البحث عن مدي من القيم

مفهوم البحث في مدي صعب التنفيذ في أغلب برامج ولغات الكمبيوتر مع سهولته التامة في excel .. ولتناول ذلك هي نكمل عروض شركات الطوفان في أحد المعارض كعرض مؤقت للتعاملات أثناء باقي أيام المعرض أمامنا قائمة بفواتير الموزعون ونريد تحديد نسبة خصمهم الإضافي حسب القائمة التي حددتها الشركة ويجب العمل بها فورا وإلي أخر يوم بالمعرض...

1- اضف صفحة عمل جديدة لكتاب العمل لدبك وسميها عروض الموزعين وذلك كما يلي :-

اختر الأمر Worksheet من القائمة Insert تظهر لك صفحة عمل جديدة اضغط اسمها ضغطة مزدوجه ثم اكتب " عروض الموزعين" ثم اضغط Enter.

2- ادخل قائمة القائمة التالية

اسم الموزع	إجمالي مسحوبات المعرض
مكتبة المصري	5000
دار الناشر الحديث	10000
مكتبة الإخلاص	7500
القاهرة للنشر	21000
دار الصالحين	14500
مكتبة فارس الحديثة	8500
مكتبة التوكل	9000
دار 26 يوليو	2500
المكتبة الحديثة للتوريدات	25000
العربية لتسويق الكتب	65000

تذكر أن مفتاح Tab من لوحة المفاتيح ينقل للخلية التالية في الصف .. أما مفتاح Enter من لوحة المفاتيح ينقلك إلي بداية الصف التالي في قائمتك.

3- حدد الصف الأول بالضغط عند رأس الصف عندما يتحول المؤشر إلي سهم ثم اضغط الأيقونة Blod لتميز صف العناوين.

4- تحرك للخلية C1 ثم اكتب " نسبة خصم العرض" ثم اضغط Enter.

5- تحرك إلي الخلية F1 وأبدأ في إدخال القائمة التالية.

قيمة الفاتورة	نسبة العرض
0	0
3000	1
6000	2
10000	3
15000	4
20000	5
25000	6
35000	7
45000	8
55000	9
100000	10

معني هذه القائمة أن أي فاتورة

أقل من 3000 سنحصل علي 0% خصم إضافي بينما

الفواتير من 3000 إلي أقل من 6000 ستحصل علي 1% بينما

الفواتير من 6000 إلي أقل من 10000 ستحصل علي 2% بينما

الفواتير من 10000 إلي أقل من 15000 ستحصل علي 3% بينما

الفواتير من 15000 إلي أقل من 20000 ستحصل علي 4% بينما

الفواتير من 20000 إلي أقل من 25000 ستحصل علي 5% بينما

الفواتير من 25000 إلي أقل من 35000 ستحصل علي 6% بينما

الفواتير من 35000 إلي أقل من 45000 ستحصل علي 7% بينما

الفواتير من 45000 إلي أقل من 55000 ستحصل علي 8% بينما

الفواتير من 55000 إلي أقل من 100000 ستحصل علي خصم 9% بينما

أي فاتورة تتعداه 100000 ألف أو تساوي ستحصل علي خصم 10%

6- حدد الخلايا من F1:G11 ثم اكتب " عروض – الموزعين" في المربع Name Box ثم اضغط Enter.

7- حدد الخلايا B11:b2 ثم اضغط الأيقونة Currency لتنسيقهم علي أنها عملية وكذلك الخلايا من F2:F11.

8- ثم حدد الخلايا من G2:G11 ثم اضغط الأيقونة Percent Style لتنسقهم علي أنهم نسبة مئوية وكذلك الخلايا G2:G11 حتى يتم ملئهم بالنسبة الخاصة بكل موزع.

9- تحرك إلي الخلية C2 ثم اضغطها ثم اضغط الأيقونة Insert Function في شريط المعادلات ليظهر لك الصندوق الحواري Insert Function واختر الدالة Vlookup من الصندوق ثم اضغط ok.

10- يظهر لك الصندوق الحواري Function Arguments والذي يوضح لك أن للدالة أربع وسيطات. اضغط المربع Lookup – Value ليعرض لك Excel شرح لمعني الوسيطة الأولي والذي يوضح لك أنها القيمة التي نبحث عنها اضغط الخلية B2 ليكتب لك Excel محتوي الخلية بجوار المربع Lookup – Value.

11- اضغط المربع Table - Arry ليعرض لك Excel شرح لمعني الوسيطة الثانية والذي يوضح لك أنها القائم التي سنبحث فيها ... لإدخال اسم مدي الخلايا يمكنك كتابته واختيار الأمر Name من القائمة Insert تظهر لك قائمة اختر منها Paste يظهر لك الصندوق الحواري Paste Name يعرض لك الأسماء المتاحة في كتاب العمل اضغط " عروض – الموزعين" ثم اضغط ok.. ليكتب لك Excel اسم المدي بشكل صحيح.

12- اضغط المربع Col – index – num ليعرض لك Excel شرح لمعني الوسيطة الثالثة والذي يوضح لك أنها رقم العمود الذي ستعاد منه النسبة اكتب "2" ... وبذلك تكون أدخلت كل الوسيطات الأساسية لعمل الدالة – التي تعرض لك أسمائها بخط ثقيل Bold في الصندوق الحواري Function Arguments ستجد أن Excel كتب لك ناتج 0.01 أو 1% وهذه هي القيمة الصحيحة.

13- اضغط المربع Rang – lookup يعرض لك Excel شرح لمعني الوسيطة الرابعة والتي توضح القيمة الافتراضية True وأنها تعيد أقرب قيمة اتركها كما هي اكتب فيها True أو 1 ثم اضغط Ok.

ما تقوم به الدالة Vlookup عند تحديد الوسيطة Rang – lookup بـ True هو البحث في القائمة عن القيمة قيمة الفاتورة مثلا لتكن 5000 وعندما لا تجدها وتصل لقيمة أعلي منها 6000 تعطيك القيمة المقابلة للقيمة قبلها أي القيمة قبلها أي ىالقيمة المقابلة لل 3000 وهي 1% . لذلك لن تعمل الدالة في هذه الحالة جيدا إلا إذا كانت القائمة مرتبة تصاعديا Ascending.

أما في حالة البحث عن 10000 فالقيمة موجودة في القائمة عندها ترجع القيمة التي أمامها أي 3%.

أما في حالة 2500 فستجد 3000 أعلي سترجع للقيمة قبلها وهي 0 لنحصل علي 0% .

أما في حالة 150000 وهي أعلي من كل القيم في القائمة ستحصل علي القيمة المقابلة لأعلي قيمة 100000 وهي 15% وهذا هو مفهوم البحث عن مدي.

يجب أن ترتب القائمة ترتيب تصاعدي لكي تعمل الدالة Vlookup بشكل جيد عند البحث عن مدي وذلك بتحديد القائمة ثم بضغط اليقونة Sort Ascending

14- اضغط مربع الملأ للخلية C2 ضغطة مزدوجة لتملأ باقي خلايا القائمة.

عند استخدام أيا من الدوال Vlookup في البحث عن قيمة مطابقة تضع المتغير Rang – lookup بـ False أو 0 وعند البحث عن مدي نضع المنفذ Rang – lookup بـ True أو 1 وهي القيمة الافتراضية التي يدخلها لك Excel عند عدم إدخال أي قيمة .