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

خاصية جدول البيانات ج2 – استخدامات متقدمة

السلام عليكم وشهركم مبارك, ناقشنا في مقالنا السابق خاصية جدول البيانات – Data Table ع بعض الأمثلة البسيطة, سنتستعرض اليوم بعض الإستخدامات الأكثر تعقيداً والتي ستعطي صورة أكثر وضوحاً عن قدرات هذه الخاصيّة المميزة.

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

 

متابعة أكثر من نتيجة معاً بناءاً على تغير متغيّر واحد:

سنكمل نقاشنا باستخدام ذات مثالنا السابق, حساب قيمة قسط مبيع سيارة بعد اضافة معدل ربح ثابت لربط الموضوع بالمقال السابق من حيث الأفكار والإستخدامات.

مثال توضيحي لاستخدام خاصية جدول البيانات
مثال توضيحي لاستخدام جدول البيانات

 

لن نكتفي في مثالنا التالي باحتساب قيمة القسط عند تغيّر قيمة متغير ما (وليكن عدد السنوات) وإنما سنقوم بمراقبة تغيّر قيم مجموعة المخرجات (بناءاً على معادلات تربطها بمكونات معادلة القسط الأصليّة) كالتالي,

القيم الجديدة التي سيتم احتسابها (راجع الصورة التالية لمزيد من التوضيح):

Estimated Monthly Installment القسط التقديري الشهري (EMI) كما تم احتسابه في المقالة السابقة.

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

Total القيمة الإجمالية للمعاملة = قيمة السيارة + قيمة الربح

Profit / Cash أو نسبة الربح إلى سعر السيارة النقدي = مبلغ الربح \ قيمة السيارة

Profit / Total أو نسبة الربح إلى القيمة الإجمالية للمعاملة = مبلغ الربح \ القيمة الإجمالية للمعاملة

EMI with Documentation Fees مبلغ القسط مع مبلغ ثابت للمصاريف الإدارية = نفس طريقة احتساب القسط كما مر في المثال السابق مع فرق اضافة مبلغ الرسم الى مبلغ السيارة.

خاصية جدول البيانات مع مراقبة أكثر من نتيجة معاً
جدول البيانات مع مراقبة أكثر من نتيجة معاً

 

في الجدول أعلاه, وضعنا في العمود D قيم المتغير وهي عدد سنوات التمويل (سنة إلى خمس سنوات). الآن ووفق هذا المثال, نحن نريد مراقبة تغير القيم المذكورة اعلاه وفق تغير مدة التمويل. أي أننا نريد من خاصية جدول البيانات احتساب قيمة القسط, مبلغ الربح, مبلغ المعاملة الكلّي, معدل الربح إلى سعر السيارة النقدي, معدل الربح إلى قيمة المعاملة الكلّي, مبلغ القسط مع افتراض اضافة مبلغ مقطوع للمعاملة الإدارية (قد تُسمى رسوم فتح ملف, رسوم مبدئية, رسوم ورقية…). نكتب سطر المعادلات كما هو موضوع في الصورة اعلاه. لاحظ مايلي رجاءاً,

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

الآن, نحدد جدولنا (بدون سطر التسميات Headers) ثم نذهب الى التبويب بيانات – Data, من الأيقونة تحليل ماذا إذا – What-If Analysis, نختار خاصية جدول البيانات – Data Table,

ادراج خاصية جدول بيانات لمراقبة أكثر من متغير
ادراج جدول بيانات لمراقبة أكثر من متغير

 

لدينا متغير واحد هنا (مدة التمويل) والذي يأخذ مكان عمودي في جدول البيانات لدينا, إذاً نُشير في خلية إدخال العمود – Column input cell إلى الخلية B13 ثم نضغط موافق.

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

نتائج خاصية جدول البيانات مع أكثر من نتيجة
نتائج جدول البيانات مع أكثر من نتيجة

لاحظ أن نتائج جدول البيانات في المثال اعلاه مرتبة ومُنسّقة مسبقاً بسبب أنني قمت بتنسيق مكان الجدول بما يتناسب مع مخرجاته. يمكنك تنظيم التنسيق قبل أو بعد ادراج جدول البيانات.

 

متابعة أكثر من نتيجة معاً بناءاً على تغير أكثر من متغيّر:

سيزيد طموحنا الآن ونطلب من اكسل Excel أن يقوم بمراقبة أكثر من نتيجة ولكن ليس بناءاً على تغير قيم متغيّر واحد وحسب وإنما أكثر من متغير معاً. يمكن تحقق هذا الطلب باتباع الخطوات التالية,

أولاً: نُنشئ جدول للسيناروهات المطلوب اختبارها والذي سيحوي القيم المتغيرة ليقوم اكسل باختبار النتائج وفقها,

جدول السيناريوهات المطلوبة
جدول السيناريوهات المطلوبة

 

الجدول السابق يحوي 9 سيناريوهات مرقمة حسب العمود الأول في الجدول من جهة اليسار (اصحاب الواجهة العربية يجب أن يعكسوا الإتجاهات تلقائياً). كل سيناريو يحوي 4 متغيرات وهي معدل الربح, معدل الدفعة الأولى, مدة التمويل ومبلغ الرسوم الإدارية.

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

ربط المعادلة الأصلية بجدول السيناريوهات
ربط المعادلة الأصلية بجدول السيناريوهات

 

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

جدول البيانات مع ارقام السناريوهات
جدول البيانات مع ارقام السناريوهات

 

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

ادراج خاصية جدول بيانات مع سيناريوهات
ادراج جدول بيانات مع سيناريوهات

 

بالضغط على موافق, سنحصل على جدول بيانات للمعادلات التي أردنا احتسابها وفق تغيّر أكثر من متغيّر معاً (حسب سيناريوهات قمنا باعدادها سابقاً لهذا الغرض).

نتائج السيناريوهات المختبرة
نتائج السيناريوهات المختبرة

 

يمكنك تحميل ملف المثال المستخدم في المقالة اعلاه.

عن Hussein.B

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

15 تعليقات

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

    (إذاً سنقوم باضافة خلية لرقم السيناريو ونكتب فيها الرقم واحد. ثم نقوم باستخدام دالة Vlookup لاحتساب قيم المتغيرات الأربع المذكورة آنفاً وفقاً لرقم السناريو الذي اضفناه (واحد). )

    هذا الجزء غير مفهوم ؟؟؟؟؟ مع العلم أني اعرف استخدام دالة Vlookup …

    رجاء شرح وتوضيح هذه الخطوه .. ولكم جزيل الشكر ..

    وجزاكم الله خيرا ..

    • مرحباً آمال. اعتذر عن تأخري بالرد. الفكرة هي أن كل رقم سيناريو يشير الى مجموعة من المدخلات وبالتالي وعند جعل رقم السيناريو هو المتغير, سيقوم اكسل باستدعاء كل قيمة من قيم السيناريو المتاحة واعطاء القيم الناتجة المقابلة لها. ارجو أن يكون الموضوع اكتمل.

      • مهندسه / آمال

        بعد السلام عليكم …
        عفوا …. أين تكتب معادلة Vlookup وماهي مكوناتها والناتج منها ..
        رجاء توضيح هذا الجزء بالتفصيل .
        وشكرا .. جزاكم الله خيرا

        • مهندسة آمال, وعليكم السلام. مرحباً مرة ثانية. نمشي خطوة خطوة. حملي ملف المثال من آخر المقال, اذهبي الى ورقة العمل باسم “Example 2”.

          رح تلاحظي وجود ثلاثة اجزاء رئيسية,
          – جدول السيناريوهات Scenarios Table: وفيه رخ نكتب رقم السناريو مع المتغيرات المختلفة اللي رح تختلف باختلاف السناريو.

          – المعادلة الرئيسية وفيها رح نستخدم دالة VLookup لنقرأء المدخلات من جدول السناريوهات. لاحظي ان المعادلات اللي لونها احمر هي متغيرات في جدول السيناريوهات ومربوطة فيه من خلال رقم السناريو ودالة VLookup.

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

          إذاً, Vlookup ربطت المعادلة الرئيسية المستخدمة بجدول السناريوهات المتاحة, وجدول البيانات “النتائج” اعطانا ناتج كل السناريوهات المقترحة. الجدول مابيعرف شو هي تفاصيل السناريوهات, لكنه “اي جدول النتائج” يقوم بتغيير قيمة المتغير “اللي هو رقم السيناريو” في كل سطر ويحسب قيم النتائج المتربطة بقيمة هذا المتغير.

          اتمنى كون وصلت الفكرة بشكل أوضح

          • مهندسه / آمال

            بعد السلام عليكم …
            لاأستطيع أن أعبر لكم عن خالص شكري وإمتناني لإهتمامكم بالرد والتوضيح ..
            وأنا آسفه جدا لأني أرهقتكم بسبب أني لم ألاحظ وجود الورقه الثانيه الخاصه بالمثال 2 ..
            رعاكم الله دوما وبارك لكم .. مع خالص شكري ..

  2. طبعًا مع عدة خيارات ضمن اطار واحد أو خانة واحدة ….

  3. السلام عليكم ورحمة الله وبركاته

    أرجو اعطائي كيفية حل ما يلي:

    اذا أردت حساب قيمة من خلال قيم مدونة على الجدول يعني :

    اذا B1 الخانة = الخانة B2 و الخانة C1 = الخانة C3 اجل قيمة A1 الخانة مساوية للخانة مثلًا A2 + نفس التكرار اجعل مثلًأ A3 …. الخ على نفس السطر …… يعني عدة خيارات على خانة واحدة لأخذ رقم واحد أخيرًا

    وشكرًا

    • وعليكم السلام غسان. لافهم سؤالك بشكل صحيح,

      هل تحتاج لو كانت B1=B3 & C1=C3 النتيجة A1=A3???

      اقصد ان الشق الأول من سؤالك واضح لكن التتمة غير واضحة. هل ممكن تعطي توضيح اكثر رجاءاً.

      • شكرًا لردك علي …. الله يجزيك الخير
        المقصود عندي جدول من 10 أرقام وهذه الأرقام أريد أن أضعها ضمن خانة واحدة بإفتراض:
        اذا كان A1= أحد الأرقام في الجدول يجب أن يضع الرقم الذي يقابله و إذا كان A1 = الرقم الذي يليه في الجدول يجب أن يضع الرقم المقابل له سلسلة من الخيارات بالأخير لكي أحصل على رقم واحد في الحساب عندي .

        آسف على ازعاجك اذا في عند واتس اب ممكن أصور لك الجداول لكي تتخيل معي الوضع

        • مرحباً غسان. في شي مو واضح, هل الشرط هو فقط عمود A والنتيجة ستظهر في باقي الأعمدة أم بالعكس (الشروط في الأعمدة B & C, في حين أن النتيجة ستظهر في العمود A)؟

          ممكن ترسل نسخة من ملف تجريبي من خلال صفحة الموقع على الفيسبوك.

      • تمامًا مثل ما كتبت أنت المعادلة صحيحة هيك بدي ياها لو سمحت واتكرمت عليي …

أضف تعليقاً

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