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

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

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

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

  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 لدينا.

×