معادلات استرجاع القيم من القوائم Lookup

معادلات استرجاع القيم من القوائم

Lookup

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

البحث عن قيمة في قائمة

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

1- اضف صفحة عمل جديدة لكتاب لديك وسميها عرض أسعار خاص وذلك كما يلي.

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

2- ادخل قائمة باسعار الكتب كما يلي:-

اسم الكتاب	السعر
مهارات Excel	5
مهارات المعادلات والدوال Excel	5
مهارات الإحتراف Excel	5
معا في عالم احتراف مهارات Excel	15
مهارات Word XP	5
مهارات التنسيق والإخراج Word XP	5
مهارات الإحتراف Word XP	5
معا في عالم احتراف مهارات Word XP	15
مهارات Access XP	5
مهارات التصميم Access XP	5
مهارات الإحتراف Access XP	5
معا في عالم احتراف مهارات Access XP	15

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

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

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

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

5- قم بتحديد الخلايا A1:B13 وذلك بالسحب عليهم.

6- حرك الماوس علي التحديد ليظهر لك مؤشر الماوس وقد تحول إلي سهم ذو أربع رؤوس اضغط واسحب الخلايا إلي E1:F13 اضغط مفتاح Ctrl من لوحة المفاتيح تظهر لك علامة (+) بجوار التحديد حرر مفتاح الماوس أولا ثم حرر مفتاح Ctrl ليظهر لك نسخ جديدة من القائمة في الخلايا E1: F13 .

اكمل الخطوة 7 ،8 مع الاحتفاظ بالقائم الجديد محدد

7- اضغط الأيقونة Sort Descending ليتم ترتيب النسخة الجديد تنازلي كما يلي:

8- لإضافة اسم القائمة الجديدة اكتب في مربع Name Box " عرض – خاص – رأسي" ثم اضغط Enter.

لا يسمح Excel بإضافة مسافات في اسماء الخلايا أو أسماء مدي الخلايا ولكن يسمح بإضافة Underscore والتي نحصل عليها بضغط مفتاح Shift أثناء ضغط مفتاح علامة "-"

9- حدد الخلايا F2: F10 بالسحب عليهم ثم اكتب ""4 ثم ثبت الضغط علي مفتاح Ctrl أثناء ضغط مفتاح Enter لتجد أن Excel كتب لك 4 في جميع الخلايا المحددة.. كرر ذلك مع الخلايا F11:F16 بتحديدهم ثم اكتب "13" ثم ثبت الضغط علي مفتاح Ctrl أثناء ضغط مفتاح Enter لتجد أن Excel كتب لك 13 في جميع الخلايا المحدد.. اضغط حد العمود E الأيسر ضغطة مزدوجه عند رأسه ليتسع لما به من أسماء كتب.

ما نريده الآن هو إضافة دالة لخلايا العمود " السعر بعد العرض " - العمود C- تحصل علي اسم الكتاب من العمود " اسم الكتاب" العمود A – ثم تبحث عنه في القائمة " عرض – خاص – رأسي" في العمود " اسم الكتاب" – العمود E- ثم تأتي بالسعر المقابل له من العمود "السعر" – العمود F- ليكتب في العمود " السعر بعد العرض" – العمود C-.

10- اضغط الخلية C2 ثم اضغط الأيقونة Insert Function يعرض لك الصندوق الحواري Insert Function اكتب في المربع " List Find value In a" Search for Function وهذا وصف الدالة التي نريدها اضغط المفتاح Go.

ستجد أن Excel عرض لك مجموعة من الدوال تفحصهم .. عند ضغط اسم أي من الدوال يظهر لك شرح لعملها ستجد أن الدالة VLOOKUP اختصار Vertical Lookup تبحث عن قيمة في العمود أقصي يسار القائمة 0 في اتجاه الصفحة من اليسار إلي اليمين – ثم تعيد القمة المقابلة بنفسي الصف ولكن من العمود الذي تحدده في الوسيط الثالث لدالة ولكن يفضل لعمل هذه الدالة ترتيب القائمة ترتيب تصاعدي.

الدالة VLOOKUP تعمل عند ترتيب القائمة تنازليا أو تصاعديا في حالة البحث عن قيمة محددة وليس مدي من القيم كما ستري في الفقرات القادمة.

11- الدالة VLOOKUP هي التي تريد اضغطها ثم اضغط OK.

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

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

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

15- اضغط المربع Range – Lookup يعرض لك Excel شرح لمعني الوسيط الرابعة والتي توضح أن القيمة الافتراضية True وأنها تعيد الدالة أقرب قيمة وليست القيمة المطابقة بينما القيمة False هي التي تعيد القيمة اكتب "False" أو "0" في المربع Range – Lookup ستجد أن القيمة "4" ظهرت كنتيجة للمعادلة وهي القيمة الصحيحة اضغط ok ... لإدخال المعادلة في الخلية C2.

16- الآن تريد نسخ المعادلة إلي باقي العمود لعمل ذلك باسرع وأسهل الطرق اضغط مربع الملأ Auto Fill للخلية C2 ضغطة مزدوجه.

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