جدول البيانات في Microsoft Excel
غالبًا ما يكون من الضروري حساب النتيجة النهائية لمجموعات مختلفة من بيانات الإدخال. وبالتالي ، سيتمكن المستخدم من تقييم جميع الخيارات الممكنة للإجراءات ، وتحديد تلك النتائج التي تحققها تفاعلاته ، وأخيرا اختيار الخيار الأمثل. في Excel ، لتنفيذ هذه المهمة هناك أداة خاصة - "جدول البيانات" ( "جدول الاستبدال" ). دعونا معرفة كيفية استخدامها لتنفيذ السيناريوهات المذكورة أعلاه.
اقرأ أيضا: اختيار المعلمة في Excel
محتوى
باستخدام جدول البيانات
تم تصميم أداة "جدول البيانات" لحساب نتيجة الاختلافات المختلفة لمتغير واحد أو اثنين محددين. بعد حساب جميع المتغيرات المحتملة سوف تظهر في شكل جدول ، والذي يسمى مصفوفة تحليل العوامل. يشير "جدول البيانات" إلى مجموعة التحليل "ماذا لو" ، الموجودة على الشريط في علامة التبويب "البيانات" في كتلة "العمل مع البيانات" . قبل Excel 2007 ، كانت هذه الأداة تسمى "جدول الاستبدال" ، والتي تعكس بدقة جوهرها أكثر من الاسم الحالي.
يمكن استخدام جدول الاستبدال في كثير من الحالات. على سبيل المثال ، الخيار النموذجي هو عندما تحتاج إلى حساب مبلغ دفعة القرض الشهرية لمختلف التغيرات في فترة الأرصدة ومقدار القرض ، أو فترة الإئتمان وسعر الفائدة. أيضا ، يمكن استخدام هذه الأداة في تحليل نماذج مشروعات الاستثمار.
ولكن يجب أن تعرف أيضًا أن الاستخدام المفرط لهذه الأداة يمكن أن يؤدي إلى كبح النظام ، حيث يتم إعادة حساب البيانات باستمرار. لذلك ، فمن المستحسن أن في المصفوفات الجدولة الصغيرة ، لحل المهام المتشابهة ، لا تستخدم هذه الأداة ، ولكن استخدم نسخ الصيغ باستخدام علامة التعبئة.
التطبيق المبرر "جدول البيانات" هو فقط في نطاقات الجدول الكبيرة ، عند نسخ الصيغ يمكن أن يستغرق الكثير من الوقت ، وخلال الإجراء يزيد من احتمال ارتكاب الأخطاء. ولكن في هذه الحالة ، يوصى أيضًا بتعطيل إعادة الحساب التلقائي للصيغ في نطاق جدول الاستبدال ، لتجنب التحميل غير الضروري على النظام.
والفرق الرئيسي بين الاستخدامات المختلفة لجدول البيانات هو عدد المتغيرات الداخلة في الحساب: متغير واحد أو اثنين.
الأسلوب 1: تطبيق أداة بمتغير واحد
دعونا على الفور النظر في الخيار عند استخدام جدول البيانات مع قيمة متغير واحد. لنأخذ المثال الأكثر نموذجية للإقراض.
لذلك ، في الوقت الحالي ، نعرض شروط الائتمان التالية:
- مدة القرض - 3 سنوات (36 شهرا) ؛
- مبلغ القرض هو 900000 روبل.
- سعر الفائدة هو 12.5 ٪ سنويا.
المدفوعات تحدث في نهاية فترة السداد (الشهر) في إطار مخطط الأقساط ، وهذا هو ، في أسهم متساوية. في نفس الوقت ، في بداية فترة القرض بأكملها ، جزء كبير من المدفوعات هو مدفوعات الفائدة ، ولكن مع انخفاض الجسد ، تنخفض مدفوعات الفائدة ، ويزيد مقدار سداد الجسد نفسه. المبلغ الإجمالي ، كما سبق ذكره أعلاه ، يبقى دون تغيير.
من الضروري حساب مبلغ الدفعة الشهرية التي تشمل سداد مبلغ القرض ومدفوعات الفائدة. للقيام بذلك ، هناك مشغل PLT في Excel.
تنتمي معاهدة قانون البراءات إلى مجموعة المهام المالية ومهمتها هي حساب القسط الشهري لنوع الأقساط استناداً إلى مبلغ مبلغ القرض ومدة القرض وسعر الفائدة. يتم تقديم بناء جملة هذه الدالة في هذا النموذج
=ПЛТ(ставка;кпер;пс;бс;тип)
"Rate" هي حجة تحدد سعر الفائدة على مدفوعات الائتمان. تم تعيين المؤشر لفترة. لدينا فترة دفع تعويضات شهر واحد. لذلك ، ينبغي تقسيم المعدل السنوي البالغ 12.5٪ إلى عدد الأشهر في السنة ، أي 12.
"Kper" - وهي عبارة عن حجة تحدد عدد الفترات الزمنية طوال فترة القرض. في مثالنا ، الفترة هي شهر واحد ، وفترة الاعتماد هي 3 سنوات أو 36 شهرا. وبالتالي ، سيكون عدد الفترات مبكرا 36.
"PS" هو الحجة التي تحدد القيمة الحالية للقرض ، أي حجم هيكل القرض في وقت إصداره. في حالتنا ، هذا الرقم هو 900،000 روبل.
"BS" هي حجة تشير إلى مبلغ جسم القرض في وقت سداده بالكامل. بطبيعة الحال ، سيكون هذا المؤشر صفرا. هذه الوسيطة ليست معلمة مطلوبة. إذا قمت بحذفها ، فمن المفترض أنها تساوي الرقم "0".
"النوع" هو أيضًا وسيطة اختيارية. ويبلغ عن موعد السداد: في بداية الفترة (المعلمة - "1" ) أو في نهاية الفترة (المعلمة - "0" ). وكما نتذكر ، فإننا ندفع في نهاية الشهر التقويمي ، أي أن قيمة هذه الوسيطة ستكون مساوية "0" . ولكن ، بالنظر إلى أن هذا المؤشر ليس إلزامياً ، وبشكل افتراضي ، إذا لم تستخدمه ، فإن القيمة وهكذا تقصد أن تكون "0" ، ففي هذا المثال لا يمكن استخدام هذا المؤشر بشكل عام.
- لذا ، لنبدأ الحساب. حدد الخلية على الورقة ، حيث سيتم عرض القيمة المحسوبة. نضغط على الزر "Insert function" .
- يبدأ معالج الدالة . نحن نجعل الانتقال إلى الفئة "المالية" ، حدد اسم "PLT" من القائمة وانقر على زر "موافق" .
- بعد ذلك ، يتم تنشيط نافذة الحجج للدالة أعلاه.
نضع المؤشر في حقل "الرهان" ، ثم نضغط على الخلية الموجودة على الورقة بقيمة سعر الفائدة السنوي. كما ترى ، يعرض الحقل إحداثياته على الفور. ولكن ، كما نتذكر ، نحن بحاجة إلى معدل شهري ، وبالتالي نقوم بتقسيم النتيجة على 12 ( / 12 ).
في حقل "Kper" بنفس الطريقة ندخل إحداثيات خلايا مدة القرض. في هذه الحالة ، لا تحتاج إلى تقسيم أي شيء.
في الحقل "Ps" ، تحتاج إلى تحديد إحداثيات الخلية التي تحتوي على مقدار نص القرض. نحن نفعل ذلك. ضع أيضًا علامة "-" قبل الإحداثيات المعروضة. والحقيقة هي أن وظيفة PLT بشكل افتراضي تعطي النتيجة النهائية بعلامة سلبية ، مع الأخذ بعين الاعتبار ، على وجه التحديد ، دفع القرض الشهري كخسارة. ولكن من أجل الوضوح في استخدام جدول البيانات ، يجب أن يكون هذا الرقم موجبًا. لذلك ، نضع علامة ناقص قبل واحدة من حجج الدالة. كما تعلم ، فإن ضرب "ناقص" بـ "ناقص" يعطي في النهاية " علامة زائد" .
في حقلي "Bc" و "Type" ، لا نجري أي بيانات على الإطلاق. انقر على زر "موافق" .
- بعد ذلك ، يقوم المشغل بحساب وإخراج نتيجة إجمالي الدفعة الشهرية - 30108.26 روبل إلى الخلية المحددة مسبقًا. لكن المشكلة هي أن المقترض قادر على دفع ما يصل إلى 29000 روبل في الشهر كحد أقصى ، أي أنه يجب عليه إما العثور على شروط عرض البنك بمعدل فائدة منخفض ، أو خفض مبلغ القرض ، أو زيادة فترة القرض. سيساعدنا جدول البدائل في حساب الخيارات المختلفة للعمل.
- أولاً ، نستخدم جدول التبديلات بمتغير واحد. دعونا نرى كيف أن قيمة الدفعة الشهرية الإلزامية تختلف مع اختلافات مختلفة في المعدل السنوي ، بدءا من 9.5 ٪ سنويا وتنتهي عند 12.5 ٪ سنويا بزيادات 0.5 ٪ . جميع الشروط الأخرى تبقى دون تغيير. نرسم نطاقًا جدوليًا تتطابق أسماء الأعمدة مع الأشكال المختلفة في معدل الفائدة. في الوقت نفسه ، يتم ترك السطر "الدفعات الشهرية" كما هو. يجب أن تحتوي الخلية الأولى على الصيغة التي قمنا بحسابها مسبقًا. لمزيد من المعلومات ، يمكنك إضافة سطور "إجمالي مبلغ القرض" و "إجمالي مبلغ الفائدة" . يتم تنفيذ العمود الذي يقع فيه الحساب بدون عنوان.
- بعد ذلك ، نحسب إجمالي مبلغ القرض في ظل الظروف الحالية. للقيام بذلك ، حدد الخلية الأولى من السطر "إجمالي مبلغ القرض" واضرب محتويات الخلايا "الدفع الشهري" و "فترة الائتمان" . بعد ذلك ، انقر على مفتاح Enter .
- لحساب المبلغ الإجمالي للفائدة في ظل الظروف الحالية ، نقوم بطرح مبلغ مبلغ القرض من إجمالي مبلغ القرض بطريقة مماثلة. لعرض النتيجة على الشاشة ، انقر فوق الزر Enter . وهكذا ، نحصل على المبلغ الذي ندفعه عند سداد القرض.
- الآن حان الوقت لتطبيق أداة جدول البيانات . حدد مصفوفة الجدول بالكامل ، باستثناء أسماء الصفوف. بعد ذلك انتقل إلى علامة التبويب "البيانات" . نضغط على الزر الموجود في الشريط "تحليل ماذا لو" ، الموجود في مجموعة الأدوات "العمل مع البيانات" (في Excel 2016 ، مجموعة أدوات "Forecast" ). ثم تفتح قائمة صغيرة. في ذلك ، حدد العنصر "جدول البيانات ..." .
- تفتح نافذة صغيرة تسمى "جدول البيانات" . كما ترون ، لديه حقلين. نظرًا لأننا نعمل مع متغير واحد ، فنحن نحتاج إلى واحد منهم فقط. ونظرًا لأننا نقوم بتغيير المتغير حسب الأعمدة ، فسوف نستخدم الحقل "استبدال القيم بالأعمدة" . قم بتعيين المؤشر هناك ، ثم انقر فوق الخلية الموجودة في مجموعة البيانات المصدر التي تحتوي على قيمة النسبة المئوية الحالية. بعد عرض إحداثيات الخلية في الحقل ، انقر فوق الزر "موافق" .
- تحسب الأداة نطاق الجدول بأكمله وتعبئه بالقيم التي تتوافق مع الخيارات المختلفة لسعر الفائدة. إذا وضعت المؤشر في أي عنصر من منطقة الجدول المعطى ، يمكنك أن ترى أن صيغة الصيغة لا تعرض الصيغة المعتادة لحساب الدفع ، ولكن صيغة خاصة لصفيف غير قابل للتجزئة. هذا هو ، الآن لا يمكنك تغيير القيم في الخلايا الفردية. يمكنك حذف نتائج الحساب فقط معًا ، وليس بشكل منفصل.
بالإضافة إلى ذلك ، يمكنك أن ترى أن قيمة الدفعة الشهرية بنسبة 12.5٪ سنوياً ، والتي يتم الحصول عليها كنتيجة لتطبيق جدول التباديل ، تقابل قيمة نفس مقدار الاهتمام الذي تلقيناه من خلال تطبيق وظيفة PLT . هذا مرة أخرى يثبت صحة الحساب.
عند تحليل مجموعة الجدول هذه ، ينبغي القول ، كما نرى ، فقط بنسبة 9.5٪ سنوياً مقبولة بالنسبة لنا مستوى الدفع الشهري (أقل من 29000 روبل).
الدرس: حساب الدفع السنوي في Excel
الطريقة الثانية: استخدم أداة ذات متغيرين
وبالطبع ، من الصعب للغاية العثور على البنوك التي تصدر القروض بمعدل 9.5٪ سنوياً ، هذا إن وجدت. لذلك دعونا نرى ما هي الخيارات المتاحة للاستثمار في مستوى مقبول من الدفعة الشهرية لمجموعات مختلفة من المتغيرات الأخرى: حجم جسم القرض ومدة القرض. في الوقت نفسه ، نبقي سعر الفائدة بدون تغيير (12.5 ٪). في حل هذه المهمة ، ستساعدنا أداة "جدول البيانات" في استخدام متغيرين.
- رسم صفيف جدول جديد. الآن أسماء الأعمدة سوف تشير إلى فترة الائتمان (من 2 إلى 6 سنوات في الأشهر بزيادات سنة واحدة) ، وفي الصفوف - مبلغ هيئة القرض (من 850000 إلى 950000 روبل مع خطوة من 10،000 روبل). الشرط الإلزامي هو أن الخلية التي توجد فيها صيغة الحساب (في حالتنا ، PLT ) تقع على حدود أسماء الصفوف والأعمدة. بدون هذا الشرط ، لن تعمل الأداة عند استخدام متغيرين.
- ثم نختار نطاق الجدول بأكمله الذي تم الحصول عليه ، بما في ذلك أسماء الأعمدة والصفوف والخلية باستخدام صيغة PLT . انتقل إلى علامة التبويب "البيانات" . كما في المرة السابقة ، نضغط على الزر "تحليل ماذا لو" في مجموعة الأدوات "العمل مع البيانات" . في القائمة المفتوحة ، حدد العنصر "جدول البيانات ..." .
- يتم تشغيل نافذة أداة "جدول البيانات" . في هذه الحالة ، نحن بحاجة إلى كلا الحقلين. في الحقل "استبدال القيم بالأعمدة" ، نشير إلى إحداثيات الخلية التي تحتوي على فترة الائتمان في البيانات الأساسية. في الحقل "قيم بديلة حسب الصفوف في" تشير إلى عنوان خلية المعلمات الأولية التي تحتوي على قيمة نص القرض. بعد إدخال جميع البيانات. انقر على زر "موافق" .
- يحسب البرنامج ويملأ نطاق الجدول مع البيانات. عند تقاطع الصفوف والأعمدة ، يمكنك الآن رؤية بالضبط ما ستكون عليه الدفعة الشهرية ، مع القيمة المقابلة للفائدة السنوية والمصطلح المحدد للقرض.
- كما ترون ، هناك الكثير من القيم. لحل مشاكل أخرى ، قد يكون هناك المزيد منها. لذلك ، لجعل إخراج النتائج أكثر وضوحا وتحديد الفور القيم التي لا تستوفي شرط معين ، يمكنك استخدام أدوات التصور. في حالتنا هذا سيكون التنسيق الشرطي. حدد جميع القيم في نطاق الجدول ، باستثناء رؤوس الصفوف والأعمدة.
- الانتقال إلى علامة التبويب "الصفحة الرئيسية" والنقر على الرمز "التنسيق الشرطي" . وهو موجود في مربع أداة "الأنماط" على الشريط. في القائمة المنسدلة ، حدد "قواعد اختيار الخلية" . في القائمة الإضافية ، انقر فوق العنصر "أقل ..." .
- بعد ذلك ، تفتح نافذة التنسيق الشرطي. في الحقل الأيسر ، حدد القيمة ، أقل مما سيتم تخصيص الخلايا. كما نذكر ، نحن راضون عن الحالة التي بموجبها سيكون الدفع الشهري بموجب القرض أقل من 29000 روبل. نحن ندخل هذا الرقم. في الحقل المناسب ، يمكنك تحديد لون التمييز ، على الرغم من أنه يمكنك تركه بشكل افتراضي. بعد إدخال جميع الإعدادات المطلوبة ، انقر فوق الزر "موافق" .
- بعد ذلك ، سيتم تمييز كل الخلايا ، والقيم التي تتوافق مع الشرط أعلاه ، بالألوان.
تحليل مجموعة الجدول ، يمكنك استخلاص بعض الاستنتاجات. كما نرى ، مع فترة القرض الحالية (36 شهرا) ، من أجل الاستثمار في المبلغ المذكور أعلاه من الدفعة الشهرية ، نحن بحاجة إلى أخذ قرض لا يتجاوز 86000000 روبل ، أي أقل بـ 40000 مما كان مقرراً في الأصل.
إذا كنا لا نزال نعتزم الحصول على قرض بقيمة 900،000 روبل ، فيجب أن تكون فترة الاعتماد 4 سنوات (48 شهراً). فقط في هذه الحالة ، لن يتجاوز الدفع الشهري الحد المقرر وهو 29000 روبل.
وبالتالي ، باستخدام هذا الجدول مجموعة وتحليل إيجابيات وسلبيات كل خيار ، يمكن للمقترض اتخاذ قرار محدد بشأن شروط القرض ، واختيار الخيار الأنسب من جميع الخيارات الممكنة.
بالطبع ، يمكن استخدام جدول البحث ليس فقط لحساب خيارات القروض ، ولكن أيضًا لحل العديد من المشاكل الأخرى.
الدرس: التنسيق الشرطي في Excel
بشكل عام ، تجدر الإشارة إلى أن جدول البحث هو أداة مفيدة للغاية وبسيطة نسبيا لتحديد النتيجة لمختلف توليفات المتغيرات. تطبيق التنسيق الشرطي في نفس الوقت ، بالإضافة إلى ذلك ، يمكنك تصور المعلومات الواردة.