الرئيسية / متفرقات / خاصيّة جدول البيانات ج1
خاصيّة جدول البيانات
خاصيّة جدول البيانات

خاصيّة جدول البيانات ج1

السلام عليكم, تفيدنا خاصية جدول البيانات – Data Table في حل معضلة كثيراً مانمر بها حيث نريد اتخاذ قرارٍ ما بناءاً على قيم معادلة لدينا لكننا نحتاج أولاً لتجربة مجموعة مدخلات على هذه المعادلة للمقارنة بين النتائج. مثلاً وفي حال كنت مدير مبيعات لمعرض سيارات بطريقة التقسيط الشهري, فأنت تريد أن تعرض على عميلك قيم القسط الشهري لمدد قرض مختلفة و / أو دفعات مقدمة (أولى) مختلفة. ستقوم أولاً بكتابة البيانات الأساسيّة لمعادلة احتساب القسط الشهري (لتبسيط النقاش, سنعتمد هنا على احتساب الأرباح وفق اسلوب معدل الربح الثابت Fixed Rate) كما يلي:

القيمة الإجماليّة للبيع = سعر السيارة + أرباح فترة التمويل

==== سعر السيارة + (سعر السيارة – الدفعة الأولى) * معدل الربح السنوي * مدة السداد بالسنوات

قيمة القسط الشهري = القيمة الإجمالية للبيع \ مدة السداد بالأشهر

لنذهب الى اكسل ونكتب البيانات السابقة كما يلي,

معدل الربح السنوي الثابت Fixed Profit Rate: فرضاً 5%
السعر النقدي Cash Price: فرضاَ 100,000
معدل الدفعة الأولى Down Payment Percentage: فرضاً 10%
مدة القرض بالسنوات Term in years: فرضاً 3 سنوات

وبالتالي سيكون القسط الشهري Monthly Payment هو 3,152.78.

معادلة ارباح وفق معدل ربح ثابت
معادلة ارباح وفق معدل ربح ثابت

 

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

 

جدول البيانات مع متغير واحد بشكل عمودي:

أول تطبيق لخاصيّة جدول البيانات سيكون عن طريق احتساب ناتج المعادلة السابقة عند قيم متغيرة لأحد مدخلات المعادلة الأصلية وليكن قيمة مدة السداد بالسنوات. نقوم بكتابة قيم السنوات المختلفة التي نريد تجربتها وذلك بشكل عمودي (مرتبة فوق بعضها في عمود واحد) Column Wise كما في الصورة التوضيحيّة التالي, لاحظ أننا كتبنا القيم 1 حتى 5 (للدلالة على مدة السداد من سنة الى خمس سنوات) في الخلايا D11 حتى D15 ثم في الخلية E10 كتبنا اشارة مساواة الى الخلية التي تحوي معادلة القسط الشهري وهي الخلية B6.

التحضير لاضافة خاصية جدول البيانات
التحضير لاضافة خاصية جدول البيانات

 

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

الآن, حدد النطاق / المجال D10:E15 ثم اذهب الى التبويب بيانات – Data, من الأيقونة تحليل ماذا إذا – What-If Analysis, اختر جدول البيانات – Data Table,

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

 

الآن, النافذة التالية فيها امكانية تحديد خلية إدخال الصف – Row input cell / خلية إدخال العمود – Column input cell. طالما أن قيم المتغير في مثالنا الحالي مرتبة بشكل عمودي, اذاً يجب ان نحدد فقط خلية إدخال العمود – Column input cell. طالما أن المتغير في المثال هو مدة السداد, اذاً يجب أن نشير في خلية إدخال العمود – Column input cell إلى خلية مدة السداد المرتبطة بالمعادلة الأصلية اي الخلية E13,

اضافة جدول بيانات بمتغير واحد - عمود
اضافة جدول بيانات بمتغير واحد – عمودي

 

بمجرد الضغط على موافق, ستحصل على قيمة القسط الشهري عند فترات السداد التي اخترتها اعلاه,

جدول البيانات مع متغير واحد - عمودي
جدول البيانات مع متغير واحد – عمودي

 

جدول البيانات مع متغير واحد بشكل أفقي:

الآن, سنفترض أننا نريد مقارنة الإختلاف في مبلغ القسط الشهري عند تغيير معدل الدفعة الأولى مع العلم أن بياناتنا (نسب الدفعة الأولى التي نريد احتساب القسط وفقها) مجهزة بشكل أفقي Row wise,

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

 

لاحظ أننا اتبعنا نفس الخطوات المذكورة اعلاه, كتبنا قيم معدلات الدفعة الأولى مع خلية تُشير الى المعادلة الأصليّة, حددنا الجدول الجديد وذهبنا الى التبويب بيانات – Data, من الأيقونة تحليل ماذا إذا – What-If Analysis ثم اخترنا جدول البيانات – Data Table.

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

الآن, عليك تحديد خلية إدخال الصف – Row input cell (طالما أن قيم المتغير الجديد (معدل الدفعة الأولى) مجهّزة على شكل أفقي), اذاً نُشير إلى خلية معدل الدفعة الأولى التي استُخدمت في المعادلة الأصلية كما يلي لنحصل على قيم القسط الشهري عند نسب مختلفة للدفعة الأولى,

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

 

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

لعلك تساءلت, هل من الممكن احتساب قيم القسط الشهري حسب تغير قيمتي الدفعة الأولى و مدة السداد؟ الجواب هو نعم, يمكن تنفيذ ذلك كالتالي, جهز جدول البيانات الجديد بجعل معدل الدفعة الأولى في سطر (المتغير الأفقي) ومدة السداد في عمود (المتغير العمودي) و اجعل خلية التقاطع لهذا السطر مع العمود هي خلية الاشارة الى المعادلة الأصلية. حدد الجدول الجديد ثم اذهب الى التبويب بيانات – Data, من الأيقونة تحليل ماذا إذا – What-If Analysis اختر جدول البيانات – Data Table. الآن عليك تحديد خلية إدخال الصف – Row input cell لسطر المتغير في جدول البيانات (والتي تمثل قيم معدلات الدفعة الأولى, اذاً نُشير إلى خلية معدل الدفعة الأولى التي استُخدمت في المعادلة الأصلية) ثم نقوم بتحديد خلية إدخال العمود – Column input cell لعمود المتغير في جدول البيانات (والتي تمثل قيم مدة السداد, اذاً نُشير إلى خلية مدة السداد التي استُخدمت في المعادلة الأصلية).

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

 

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

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

 

تحذيرات عند استخدام خاصيّة جدول البيانات:

سأُشير هنا إلى مجموعة من النقاط المهمة للإنتباه إليها عند استخدام هذه الخاصيّة,

  • يجب ملاحظة أن خلية إدخال الصف – Row input cell و خلية إدخال العمود – Column input cell لا تُشيران أبداً إلى خلية المعادلة الأصلية وانما الى خلايا مرتبطة بحساب المعادلة المذكورة. أي أن هذه الخلايا (التي يمكن الاشارة إليها من خلال خلية إدخال الصف – Row input cell / خلية إدخال العمود – Column input cell) هي خلايا مرتبطة بالمعادلة الأصلية وتؤثر في قيمتها / نتيجتها.
  • نستنتج من الملاحظة السابقة باستحالة تطبيق خاصيّة جدول البيانات على معادلة ما لاتستخدم اي خلية كمدخل لها. أي أننا لن نستطيع تطبيق جدول البيانات على المعادلة التالية,
    لعدم وجود أي متغير فيها نستطيع استخدامه كخلية ادخال للصف أو العمود في جدول البيانات.
  • لايمكنك حذف جزء من جدول البيانات و إنما يجب عليك حذف قيم النتيجة كاملةً في حال اردت ازالة الجدول من ورقة العمل لديك,
    حذف جزء من جدول البيانات
    حذف جزء من جدول البيانات

     

  • يمكن لك تعديل على قيم سطر المتغير في جدول البيانات مباشرةً ليقوم اكسل Excel بتحديث قيم عمود النتيجة المرتبط بها,
    التعديل على قيمة ما من سطر المتغير
    التعديل على قيمة ما من سطر المتغير

     

  • يمكن كذلك التعديل على قيم عمود المتغير في جدول البيانات مباشرةً ليقوم اكسل Excel بتحديث قيم سطر النتيجة المرتبط بها,
    التعديل على قيمة ما من عمود المتغير
    التعديل على قيمة ما من عمود المتغير

     

  • لن يسمح لك اكسل Excel بالكتابة فوق اي قيمة من قيم النتيجة في جدول البيانات,
    الكتابة فوق النتيجة في جدول البيانات
    الكتابة فوق النتيجة في جدول البيانات

     

  • يجب عليك عدم حذف او الكتابة فوق الخلية التي تحوي اشارة المساواة الى المعادلة الأصلية,
    حذف خلية الاشارة الى المعادلة الأصلية
    حذف خلية الاشارة الى المعادلة الأصلية

     

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

     

بقي أن نتيح تحميل ملف المثال الذي تم مناقشته في هذه المقالة.

عن Hussein.B

مؤسس ومدير موقع viaexcel.com, اعمل في مجال الإئتمان, مهتم بمواضيع التدريب وتطوير الذات بالإضافة لحبي الشديد لبرنامج اكسل.

4 تعليقات

  1. مهندسه / آمال

    جميل جدا .. بارك الله لكم

  2. مهندسه / آمال

    روعه .. جميل جدا .. بارك الله لكم

أضف تعليقاً

لن يتم نشر عنوان بريدك الإلكتروني. الحقول الإلزامية مشار إليها بـ *