حساب نتائج متعددة باستخدام جدول بيانات

في Microsoft Excel، تعد جداول البيانات جزءا من مجموعة من الأوامر المعروفة باسم أدوات تحليل What-If. عند إنشاء جداول البيانات وتحليلها، فإنك تقوم بتحليل ماذا لو.

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

أنواع تحليل ماذا لو    

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

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

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

تعرف على المزيد في المقالة، مقدمة What-If Analysis.

قم بإنشاء جداول بيانات متغير واحد أو متغيرين، اعتمادا على عدد المتغيرات والصيغ التي تحتاج إلى اختبارها.

جداول بيانات أحادية المتغير    

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

في الرسم التوضيحي التالي، تحتوي الخلية D2 على صيغة الدفع، =PMT(B3/12,B4,-B5) التي تشير إلى خلية الإدخال B3.

جداول بيانات ثنائية المتغيرات    

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

في الرسم التوضيحي التالي، تحتوي الخلية C2 على صيغة الدفع، =PMT(B3/12,B4,-B5) التي تستخدم خليتين إدخال، B3 وB4.

 

حسابات جدول البيانات    

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

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

اتبع الخطوات التالية:

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

2. نفّذ أحد الإجراءات التالية:

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

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

3. حدد نطاق الخلايا الذي يحتوي على الصيغ والقيم التي تريد استبدالها. في الشكل أعلاه، هذا النطاق هو C2:D5.

4. على علامة التبويب بيانات ، انقر فوق تحليل ماذا لو > جدول البيانات (في مجموعة أدوات البيانات أو مجموعة التنبؤ Excel 2016 ).

5. قم بتنفيذ أحد الإجراءات التالية:

   • إذا كان جدول البيانات موجها نحو العمود، أدخل مرجع الخلية لخلية الإدخال في حقل خلية إدخال العمود . في الشكل أعلاه، تكون خلية الإدخال B3.

   • إذا كان جدول البيانات موجها نحو الصف، أدخل مرجع الخلية لخلية الإدخال في حقل خلية إدخال الصف .

     ملاحظة: بعد إنشاء جدول البيانات، قد تحتاج إلى تغيير تنسيق خلايا النتيجة. في الشكل، يتم تنسيق خلايا النتيجة كعملة.

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

اتبع الخطوات التالية

1. قم بأي مما يلي:

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

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

2. حدد نطاق الخلايا الذي يحتوي على جدول البيانات والصيغة الجديدة.

3. على علامة التبويب بيانات ، انقر فوق تحليل ماذا لو > جدول البيانات (في مجموعة أدوات البيانات أو مجموعة التنبؤExcel 2016 ).

4. قم بأي مما يلي:

   • إذا كان جدول البيانات موجها نحو العمود، أدخل مرجع الخلية لخلية الإدخال في مربع خلية إدخال العمود .

   • إذا كان جدول البيانات موجها نحو الصف، أدخل مرجع الخلية لخلية الإدخال في مربع خلية إدخال الصف .

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

اتبع الخطوات التالية:

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

   في المثال التالي— حيث يتم إدخال قيم بدء الصيغة في الخلايا B3 وB4 وB5، يمكنك كتابة الصيغة =PMT(B3/12,B4,-B5) في الخلية C2.

2. اكتب قائمة واحدة من قيم الإدخال في العمود نفسه، أسفل الصيغة.

   في هذه الحالة، اكتب أسعار الفائدة المختلفة في الخلايا C3 وC4 وC5.

3. أدخل القائمة الثانية في الصف نفسه مثل الصيغة - إلى يمينها.

   اكتب شروط القرض (بالأشهر) في الخلايا D2 وE2.

4. حدد نطاق الخلايا الذي يحتوي على الصيغة (C2)، وصف القيم وعمودها (C3:C5 وD2:E2)، والخلايا التي تريد القيم المحسوبة فيها (D3:E5).

   في هذه الحالة، حدد النطاق C2:E5.

5. على علامة التبويب بيانات ، في المجموعة أدوات البيانات أو مجموعة التنبؤ (في Excel 2016 )، انقر فوق تحليل ماذا لو > جدول البيانات (في مجموعة أدوات البيانات أو مجموعة التنبؤ Excel 2016 ).

6. في حقل خلية إدخال الصف ، أدخل المرجع إلى خلية الإدخال لقيم الإدخال في الصف.
   اكتب الخلية B4 في مربع خلية إدخال الصف .

7. في حقل خلية إدخال العمود ، أدخل المرجع إلى خلية الإدخال لقيم الإدخال في العمود.
   اكتب B3 في مربع خلية إدخال العمود .

8. انقر فوق موافق.

مثال لجدول بيانات ثنائي المتغيرات

يمكن أن يوضح جدول البيانات ثنائي المتغير كيف ستؤثر مجموعات مختلفة من أسعار الفائدة وشروط القروض على دفع الرهن العقاري الشهري. في الشكل هنا، تحتوي الخلية C2 على صيغة الدفع، =PMT(B3/12,B4,-B5) التي تستخدم خليتين إدخال، B3 وB4.

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

اتبع هذه الخطوات لتحسين أداء الحساب:

1. انقر فوق خيارات > الملفات > الصيغ.

2. في قسم خيارات الحساب ، ضمن حساب، انقر فوق تلقائي باستثناء جداول البيانات.

   تلميح: اختياريا، في علامة التبويب الصيغ ، انقر فوق السهم على خيارات الحساب، ثم انقر فوق تلقائي باستثناء جداول البيانات (في المجموعة حساب ).

يمكنك استخدام بعض أدوات Excel الأخرى لإجراء تحليل ماذا لو إذا كان لديك أهداف محددة أو مجموعات أكبر من البيانات المتغيرة.

الاستهداف

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

Excel Solver

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