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

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

تعد جداول البيانات جزءًا من مجموعة أوامر تسمى أدوات تحليل "ماذا إذا". فعندما تستخدم جداول البيانات، أنت تجري تحليل "ماذا إذا".

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

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

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

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

لمزيد من المعلومات حول تحليل "ماذا إذا"، راجع مقدمة إلى تحليل "ماذا إذا".

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

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

جداول بيانات بمتغير واحد     إذا أردت رؤية كيف ستغير القيم المختلفة لمتغير واحد في صيغة أو أكثر نتائج هذه الصيغ، فاستخدم جدول بيانات بمتغير واحد. على سبيل المثال، يمكنك استخدام جدول بيانات بمتغير واحد لمعرفة كيفية تأثير معدلات الفائدة المختلفة على مدفوعات الرهن الشهرية باستخدام دالة 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)، انقر فوق تحليل ماذا إذا، ثم انقر فوق جدول بيانات.

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

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

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

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

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

زيادة سرعة الحساب في ورقة العمل التي تحتوي على جداول بيانات

  1. قم بأحد الإجراءات التالية:

    • ضمن Excel 2007، انقر فوق زر ‏Microsoft Office‏ صورة زر Office ، ثم انقر فوق خيارات Excel‏، ثم فوق فئة الصيغ.

    • في كل الإصدارات الأخرى، انقر فوق ملف > خيارات > الصيغ.

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

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

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

ما الخطوة التالية؟

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

الاستهداف

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

Excel Solver

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

أعلى الصفحة

توسيع المهارات
استكشاف التدريب
الحصول على الميزات الجديدة أولاً
الانضمام إلى Office Insider

هل كانت المعلومات مفيدة؟

نشكرك على ملاحظاتك!

شكراً لك على الملاحظات! يبدو أنه من المفيد إيصالك بأحد وكلاء دعم Office لدينا.

×