تحليل و معالجة قواعد البيانات

وضع التنبؤات في Microsoft Excel

تحليل "ماذا لو..." What-If Analysis

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

 يوفر Excel عدة طرق لتوقع القيم والتنبؤ بها.

·        جداول المعطيات Data Tables؛

·        السيناريوهات Scenarios؛

·        أداة البحث عن الهدف Goal Seek؛

·        أداة حل المشاكل Solver.

جداول المعطيات

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

جداول المعطيات ذات المتحول الواحد

 لتوليد جداول المعطيات ذات المتحول الواحد:

 يجب أن تصمموا جداول المعطيات ذات المتحول الواحد بحيث تنتظم معطيات الدخل إما وفق عامود ما (توجه عامودي)، أو وفق سطر ما (توجه سطري). يجب أن تشير الصيغ المستخدمة في جداول المعطيات ذات المتحول الواحد إلى خلية دخل.

1. ضعوا قائمة بالقيم التي تريدون استبدالها في خلية الدخل إما وفق العامود أو السطر.

2. إذا ما تم تدوين قيم الدخل بشكل عامودي, قوموا بكتابة الصيغة في السطر الواقع فوق أول قيمة وفي الخلية الواقعة إلى يمين عامود القيم. أما إذا تم تدوين قيم الدخل بشكل سطري, فقوموا بكتابة الصيغة في العامود الواقع إلى يسار أول قيمة وفي الخلية الواقعة تحت سطر القيم؛

3. اختاروا فيما إذا كانت معطيات الجدول سطرية التوجه, اكتبوا مؤشر خلية الدخل في مربع خلية دخل السطر Row مجال الخلايا التي تحوي الصيغ والقيم المراد استبدالها؛

4. اختاروا جدول Table من قائمة المعطيات Data؛

5. إذا ما كانت معطيات الجدول عامودية التوجه, اكتبوا مؤشر خلية الدخل في في مربع خلية دخل العامود Column.

جداول المعطيات ذات المتحولين

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

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

لتوليد جداول المعطيات ذات المتحولين:

1. أدخلوا في إحدى الخلايا ضمن وريقة عمل الصيغة التي تشير إلى خليتي دخل؛

2. دونوا إحدى قائمتي قيم الدخل في نفس العامود تحت الصيغة. دونوا القائمة الثانية في نفس السطر, إلى يمين الصيغة؛

3. اختاروا مجال الخلايا الذي يحتوي على الصيغة وكل من عامود وسطر القيم؛

4. اختاروا جدول Table من قائمة المعطيات Data؛

5. في مربع خلية دخل السطر Row, ادخلوا مؤشراً إلى خلية دخل قيم الدخل في السطر؛

6. في مربع خلية دخل العامود Column, ادخلوا مؤشراً إلى خلية دخل قيم الدخل في العامود.

أداة مدير السيناريو Scenario Manager

 تعدّ جداول المعطيات جيدة في المواضع التي تتطلب متحولاً واحداً أو اثنين على الأكثر, لكن إذا كان هناك أكثر من متحولين، فيتوجب عليكم استخدام أداة "مدير السيناريو". فمثلا, إذا أردتم حساب الميزانية، ولكن لم تكونوا واثقين من معدل دخلكم, يمكنكم تحديد قيم مختلفة للدخل، ومن ثم التبديل بين السيناريوهات لتوليد تحليل "ماذا لو...".

 إن السيناريو هو مجموعة القيم التي تستخدمونها لتوقع خرج وريقة العمل. بإمكانكم توليد وحفظ سيناريوهات مختلفة في وريقة العمل، ومن ثم التبديل بينها لرؤية النتائج المختلفة.

 فمثلاً, إذا أردتم حساب الميزانية، ولكن لم تكونوا واثقين من معدل دخلكم، يمكنكم تحديد قيم مختلفة للدخل، ومن ثم توليد سيناريو لكل حالة.

 يمكنكم بعد ذلك التبديل بين السيناريوهات لتوليد تحليل "ماذا لو...".

 يولد Excel تقارير السيناريو التي تبين تأثير السيناريوهات المختلفة على النتيجة.

توليد سيناريو

للمحافظة على القيم الأصلية للخلايا المتغيرة, ولدوا سيناريو يستخدم القيم الأصلية للخلايا، وذلك قبل أن تولدوا السيناريوهات التي تغير هذه القيم.

1. اختاروا سيناريوهات Scenarios من قائمة الأدوات Tools؛

2. انقروا على إضافة Add؛

3. في مربع اسم السيناريو, اكتبوا اسم السيناريو؛

4. في مربع الخلايا المتغيرة, ادخلوا مؤشرات الخلايا التي تريدون تغييرها؛

5. تحت بند الحماية Under Protection, اختاروا الخيار الذي تريدون, ومن ثم انقروا موافق OK؛

6. في مربع حوار قيم السيناريو Scenario Values, دونوا قيم الخلايا المتغيرة؛

7. لتوليد السيناريو انقروا موافق OK؛

8. لتوليد سيناريوهات إضافية, انقروا على إضافة Add، ومن ثم انقروا على إغلاق Close في مربع حوار مدير السيناريو Scenario Manager؛

9. عند الانتهاء من توليد السيناريو، انقروا على موافق OK, ثم انقروا على إغلاق Close في مربع حوار مدير السيناريو Scenario Manager.

بعد أن يتم إعداد السيناريوهات، يمكن عرض نتيحتها بتعليم اسم السيناريو في مدير السيناريو Scenario Manager، والنقر على زر الإظهار Show.

تقارير السيناريو

 يمكن أن يولد مدير السيناريو Scenario Manager تقارير تظهر القيم المختلفة لكل سيناريو مع بعضها البعض، وستسخدم نتيجة الحسابات هذه القيم.

 اختاروا سيناريوهات Scenarios من قائمة الأدوات Tools، ومن ثم انقروا على ملخص Summary. يتوجب تحديد خلية نتيجة الحساب أيضا.

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

 وإذا كنتم تفضلون, يمكنكم توليد الملخصات بوساطة الجداول المحورية أيضا.

أداة البحث عن الهدف Goal Seek

 بوساطة البحث عن الهدف Goal Seek يمكنكم استخدام Excel لتغيير قيمة خلية واحدة حتى تصل قيمة هذه الخلية إلى القيمة المطلوبة. يتم هذا في الحقيقة بحساب القيم بشكل تراجعي بدءًا من قيمة الخرج المطلوبة:

1. في قائمة الأدوات Tools, انقروا على البحث عن الهدف Goal Seek؛

2. في مربع اختيار خلية Set Cell Box، ادخلوا مؤشراً إلى الخلية التي تحوي الصيغة التي تريدون حلها؛

3. في مربع "إلى القيمة" To Value Box، ادخلوا النتيجة التي تريدونها؛

4. في مربع تغيير الخلية By Changing Cell Box, ادخلوا مؤشراً إلى الخلية التي تحوي القيمة التي تريدون تعديلها. يجب أن تؤشر الصيغة في الخلية التي اخترتموها في مربع اختيار خلية Set Cell Box إلى هذه الخلية.

أداة حل المشاكل Solver

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

 تعدل أدة حل المشاكل Solver قيم الخلايا التي اخترتموها لتوليد النتيحة التي تريدونها من الصيغة.

يمكن استخدام أداة Solver في العديد من مهام اتخاذ القرارات في الأعمال التجارية، كالتنبؤ بالنفقات المستقبلية، أو تعقب النفقات الحالية مقارنة مع النفقات القائمة.

استخدام أداة حل المشاكل Solver

اختاروا Solver من قائمة الأدوات Tools،

 إذا لم تكن أداة Solver موجودة في قائمة الأدوات Tools, فستحتاجون لتنصيب مكتبة Solver البرمجية؛

 في مربع مجموعة الخلايا الهدف Set Target Cell, ادخلوا مؤشرات الخلايا أو اسم الخلية الهدف؛ يجب ان تحوي الخلية الهدف على صيغة.

 يمكنكم القيام بالحل للقيمة العليا أو الدنيا أو أي قيمة أخرى تريدونها, ولتحديد الخلية الهدف, اختاروا القيمة العليا Max، أو القيمة الدنيا Min، أو قيمة Value أو اكتبوا القيمة المطلوبة في المربع.

 يمكنكم بعد ذلك إدخال القيم إما بتغيير مربع الخلايا Cells، أو بالسماح لأداة Solver باقتراح الخلايا التي يجب تعديلها استناداً إلى الصيغة الموجودة في الخلية الهدف. يجب أن تعتمد الصيغة الموجودة في الخلية الهدف على الخلايا التي تم تعديلها.

 للسماح لأداة Solver باقتراح الخلايا, انقروا على تخمين Guess.

 لتحديد الخلايا المراد استخدامها, اكتبوا اسم أو مؤشر كل لخلية تم تعديلها. يمكن أن تقتصر هذه الخلايا على خلية واحدة, أو قد تكون عدة خلايا. وإذا كان هناك أكثر من خلية واحدة، قوموا بفصل الخلايا بفواصل.

 في مربع القيود Constraints، قوموا بإدخال أية قيود تريدون تنفيذها.

 هناك العديد من القيود التي قد ترغبون بوضعها. فمثلاً, قد ينبغي الحفاظ على قيم موجبة أو غير صفرية في بعض الخلايا.

 يسمح لكم زر خيارات Options بتعديل خوارزمية Solver الرقمية, بحيث يستخدم نموذجاً خطياً أو غير خطي.

 انقروا على حل Solve.

 ستحاول أداة Solver حل المشكلة. على أن Solver قد لاتتمكن، بالاستناد إلى القيود التي فرضتموها, من إيجاد حل للمشكلة.

 فإذا ما كان الحل ممكناً, ستحتاجون إلى مراجعة الحل للحكم على مدى ملائمته والتحقق مما إذا كان يوافق متطلباتكم.

 وإذا أردتم المحافظة على القيم الجديدة, فانقروا على الحفاظ على الحل Keep Solver Solution في مربع حوار نتيجة الحل Solver Results. أما لإستعادة المعطيات الأصلية, فانقروا على إستعادة القيم الأصلية Restore Original Value.