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

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

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

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

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

ملاحظة: يمكنك اجراء عمليات حسابيه أسرع باستخدام جداول البيانات و Visual Basic for Applications (VBA). للحصول علي مزيد من المعلومات ، راجع جداول البيانات "ماذا إذا" في Excel: حساب أسرع باستخدام VBA.

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

هناك ثلاثه أنواع من أدوات تحليل ماذا إذا في 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. علي علامة التبويب بيانات ، انقر فوق تحليل البيانات في _gt_، وفي المجموعة أدوات البيانات أو مجموعه التنبؤات في Excel 2016 ).  

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

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

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

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

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

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

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

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

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

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

  3. علي علامة التبويب بيانات ، انقر فوق تحليل البيانات في _gt_ ، وفي المجموعة أدوات البيانات أو مجموعه التنبؤات في 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 ) ، انقر فوق تحليلالبيانات الموجودة في القائمة ال_gt_ه (في مجموعه أدوات البيانات أو مجموعه التنبؤ بالExcel 2016 ).  

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

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

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

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

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

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

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

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

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

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

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

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

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

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

الاستهداف

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

Excel Solver

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

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

هنا ، جدول البيانات هو نطاق الخلايا B2: D8. يمكنك تغيير القيمة في B4 ، ومبلغ القرض ، والمدفوعات الشهرية في العمود D تحديث تلقائيا. باستخدام نسبه الفائدة في 3.75% ، ترجع الدالة D2 الدفع الشهري ل $1,042.01 باستخدام هذه الصيغة: = PMT (C2/12 ، $B $3 ، $B $4).

نطاق الخلايا هذا، B2:D8، هو جدول بيانات

يمكنك استخدام متغير واحد أو اثنين ، استنادا إلى عدد المتغيرات والصيغ التي تريد اختبارها.

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

في هذا المصنف المباشر ، تحتوي الخلية D2 علي صيغه الدفع =PMT (C2/12, $B $3, $B $4). تمثل الخلية B3 الخلية المتغيرة ، حيث يمكنك توصيل طول مختلف المصطلحات (عدد فترات السداد الشهرية). في الخلية D2 ، تقوم الدالة PMT بالتوصيل في معدل الفائدة 3.75 في 12 و 360 أشهر وقرض $225,000 ، ويحسب السداد الشهري $1,042.01.

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

في هذا المصنف المباشر ، تحتوي الخلية C3 علي صيغه الدفع =PMT ($B $3/12, $B $2, B4) ، التي تستخدم خليتين متغيرتين ، B2 و B3. في الخلية C2 ، يتم توصيل الدالة PMT بمعدل الفائدة 3.875 في 12 و 360 أشهر وقرض $225,000 ، ويحسب $1,058.03 سنه شهريا.

هل تحتاج إلى مزيد من المساعدة؟

ويمكنك دائماً الاستفسار من أحد الخبراء في مجتمع Excel‏ التقني،أو الحصول على الدعم من خلال إجابات المجتمع،أو اقتراح ميزة جديدة أو تحسين عن طريق ‏Excel User Voice‏.

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

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

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

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

×