أدوات التنبؤ في Microsoft Excel

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

إجراء التنبؤ

الهدف من أي تنبؤ هو تحديد الاتجاه الحالي ، وتحديد النتيجة المتوقعة فيما يتعلق بالجسم المدروس في وقت معين في المستقبل.

الطريقة 1: خط الاتجاه

أحد أكثر أنواع التنبؤات الرسومية شيوعًا في Excel هو الاستقراء الذي يتم عن طريق بناء خط اتجاه.

دعونا نحاول التنبؤ بأرباح المشروع خلال 3 سنوات على أساس البيانات الخاصة بهذا المؤشر خلال الـ 12 عامًا السابقة.

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

   دعونا أولا اختيار التقريب الخطي.

   في كتلة إعدادات "Forecast" في الحقل "Forward to" ، عيِّن الرقم "3.0" ، نظرًا لأننا بحاجة إلى توقع لمدة ثلاث سنوات. بالإضافة إلى ذلك ، يمكنك التحقق من الإعدادات "إظهار المعادلة في الرسم التخطيطي" و "ضع قيمة دقة التقريب (R ^ 2) على الرسم التخطيطي" . يعكس المؤشر الأخير جودة خط الاتجاه. بعد إجراء الإعدادات ، انقر فوق الزر "إغلاق" .

4. تم بناء خط الاتجاه ويمكننا من خلاله تحديد المبلغ التقريبي للربح في ثلاث سنوات. كما نرى ، بحلول ذلك الوقت ينبغي أن يتجاوز 4500000 روبل. يعكس المعامل R2 ، كما سبق ذكره أعلاه ، جودة خط الاتجاه. في حالتنا ، R2 هو 0.89 . كلما زاد معامل ، كلما زادت موثوقية الخط. يمكن أن تساوي قيمته القصوى 1 . من المقبول عمومًا أنه مع وجود معامل فوق 0.85 ، يكون خط الاتجاه موثوقًا.
5. إذا لم تكن راضيًا عن مستوى الموثوقية ، يمكنك الرجوع إلى نافذة تنسيق خط الاتجاه واختيار أي نوع آخر من التقريب. يمكنك تجربة جميع الخيارات المتاحة للعثور على أكثر دقة.

   وتجدر الإشارة إلى أن التنبؤ الفعال باستخدام الاستقراء عبر خط الاتجاه يمكن أن يكون إذا لم تتجاوز فترة التنبؤ 30٪ من قاعدة الفترة التي تم تحليلها. أي ، عند تحليل فترة 12 عامًا ، لا يمكننا إجراء توقعات فعالة لأكثر من 3-4 سنوات. ولكن حتى في هذه الحالة ، ستكون موثوقة نسبياً ، إذا لم تكن هناك قوة قاهرة خلال هذا الوقت أو على العكس ، ظروف مواتية للغاية لم تكن في فترات سابقة.

الدرس: كيفية بناء خط الاتجاه في Excel

الطريقة 2: عامل التشغيل PREDICTION

يمكن تنفيذ الاستقراء للبيانات المجدولة من خلال وظيفة Exxel PRESCASE القياسية. تنتمي هذه الوسيطة إلى فئة الأدوات الإحصائية ولها الصيغة التالية:

=ПРЕДСКАЗ(X;известные_значения_y;известные значения_x)

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

"القيم المعروفة لـ y" هي أساس القيم المعروفة للدالة. في حالتنا ، دور الربح للفترات السابقة هو في دوره.

"القيم المعروفة x" هي الوسيطات التي تتوافق معها قيم الدالة المعروفة. في دورهم ، ونحن مرقمة لسنوات ، والتي تم جمعها عن معلومات عن أرباح السنوات السابقة.

بطبيعة الحال ، لا يجب بالضرورة أن يكون الخط الزمني حجة. على سبيل المثال ، يمكن أن تكون درجة الحرارة ، وقد تكون قيمة الدالة مستوى توسع الماء عند تسخينها.

عند حساب هذه الطريقة ، يتم استخدام طريقة الانحدار الخطي.

دعونا ننظر إلى الفروق الدقيقة في استخدام عامل PRESCASE للحصول على مثال محدد. خذ نفس الجدول. سنحتاج إلى معرفة توقعات الأرباح لعام 2018.

1. حدد خلية فارغة على الورقة حيث تريد طباعة نتيجة المعالجة. انقر على زر "إدراج وظيفة" .
2. يفتح معالج الدالة . في الفئة "Statistical" ، حدد الاسم "PRESCASE" ، ثم انقر فوق الزر "OK" .
3. تبدأ نافذة الحجج. في الحقل "X" ، نحدد قيمة الوسيطة التي نريد إيجاد قيمة الدالة لها. في حالتنا ، هذا هو 2018. لذلك ، ندخل الإدخال "2018" . ولكن من الأفضل تحديد هذا المؤشر في الخلية الموجودة على الورقة ، وفي الحقل "X" فقط قم بإعطاء رابط له. سيسمح هذا في المستقبل بأتمتة الحسابات ، وإذا لزم الأمر ، يمكنك بسهولة تغيير السنة.

   في حقل "القيم المعروفة لـ y" ، نشير إلى إحداثيات العمود "أرباح المؤسسة" . يمكن القيام بذلك عن طريق ضبط المؤشر في الحقل ، ثم الضغط مع الاستمرار على زر الماوس الأيسر وتحديد العمود المقابل على الورقة.

   وبالمثل ، في حقل "القيم المعروفة x" ، أدخلنا عنوان العمود "السنة" مع بيانات الفترة الماضية.

   بعد إدخال جميع المعلومات ، انقر فوق الزر "موافق" .

4. يحسب العامل على أساس البيانات المدخلة ويعرض النتيجة على الشاشة. بالنسبة لعام 2018 ، يتم التخطيط لتحقيق ربح يبلغ حوالي 4564.7 ألف روبل. استنادًا إلى الجدول الناتج ، يمكننا رسم الرسم البياني باستخدام أدوات التخطيط الموضحة أعلاه.
5. إذا قمت بتغيير السنة في الخلية التي تم استخدامها لإدخال الوسيطة ، فإن النتيجة ستتغير وفقًا لذلك ، وسيتم تحديث الرسم البياني تلقائيًا. على سبيل المثال ، وفقًا للتوقعات في عام 2019 ، سيكون مبلغ الربح 4637.8 ألف روبل.

ولكن لا تنسوا أنه ، كما هو الحال في بناء خط الاتجاه ، لا ينبغي أن يتجاوز الفاصل الزمني لفترة التنبؤ 30٪ من كامل الفترة التي تراكمت فيها قاعدة البيانات.

الدرس: الاستقراء في اكسل

الطريقة الثالثة: اتجاهات المشغل

للتنبؤ ، يمكنك استخدام وظيفة أخرى - TRENDS . كما أنه ينتمي إلى فئة المشغلين الإحصائيين. يشبه بناء الجملة في العديد من الطرق بناء جملة أداة PRESCASE ويبدو كما يلي:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

كما ترى ، تتطابق الوسيطات "Known value of y" و "Known value of x" بشكل كامل مع عناصر مشابهة من عامل PRESCASE ، وتتطابق الوسيطة "New values ​​x" مع الوسيط "X" للأداة السابقة. بالإضافة إلى ذلك ، يوجد في TRENDS حجة إضافية "ثابت" ، ولكنها ليست إلزامية ويتم استخدامها فقط إذا كانت هناك عوامل ثابتة.

يتم استخدام هذا المشغل بشكل أكثر فعالية في وجود الاعتماد الخطي للوظيفة.

دعونا نرى كيف ستعمل هذه الأداة مع نفس مجموعة البيانات. من أجل مقارنة النتائج التي تم الحصول عليها ، سنحدد نقطة التنبؤ في عام 2019.

1. نقوم بتعيين الخلية لإخراج النتيجة وتشغيل معالج الدالة بالطريقة المعتادة. في الفئة "إحصائي" ، نجد ونحدد اسم "الاتجاهات" . انقر على زر "موافق" .
2. يفتح مربع وسيطة مشغل TENDENCY. في الحقل "القيم المعروفة لـ y" ، نقوم بإدخال إحداثيات العمود "Enterprise Profit " بالطريقة المذكورة أعلاه. في الحقل "القيم المعروفة x" أدخل عنوان العمود "السنة" . في الحقل "New x values" ، ندخل مرجعًا إلى الخلية حيث يوجد رقم السنة ، والذي تريد تحديد التنبؤ به. في حالتنا هذه هي 2019. يتم ترك الحقل "ثابت" فارغًا. نضغط على زر "موافق" .
3. يعالج المشغل البيانات ويعرض النتيجة على الشاشة. وكما ترى ، فإن مجموع الربح المتوقع لعام 2019 ، المحسوب بطريقة الاعتماد الخطي ، سيحقق ، كما في طريقة الحساب السابقة ، 4637.8 ألف روبل.

الطريقة الرابعة: عامل التشغيل GROWTH

وظيفة أخرى يمكنك من خلالها إجراء تنبؤات في Excel ، هي عامل التشغيل GROWTH. كما يشير أيضًا إلى مجموعة الأدوات الإحصائية ، ولكن على عكس الأساليب السابقة ، لا يتم تطبيق الطريقة على طريقة الاعتماد الخطي ، ولكن بالطريقة الأسية. تبدو بنية هذه الأداة كما يلي:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

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

1. حدد خلية إخراج الإخراج واستدعاء معالج الدالة بالطريقة المعتادة. في قائمة المشغلين الإحصائيين ، ابحث عن العنصر "GROWTH" ، وحدده وانقر على زر "OK" .
2. يتم تنشيط نافذة الوسيطة الخاصة بالوظيفة المذكورة أعلاه. نقوم بإدخال البيانات في حقول هذه النافذة بنفس الطريقة التي أدخلناها في نافذة الوسيطة الخاصة بعامل TENDENCY. بعد إدخال المعلومات ، انقر فوق الزر "موافق" .
3. يتم عرض نتيجة معالجة البيانات على الشاشة في الخلية المحددة مسبقًا. كما ترون ، هذه المرة كانت النتيجة 4682.1 ألف روبل. الاختلافات عن نتائج معالجة البيانات من قبل المشغل TENDENCY غير ذات أهمية ، ولكنها متاحة. ويرجع ذلك إلى حقيقة أن هذه الأدوات تستخدم أساليب حسابية مختلفة: طريقة الاعتماد الخطي وطريقة الاعتماد الأسي.

الطريقة الخامسة: عامل LINEST

يستخدم عامل التشغيل LINEST أسلوب التقريب الخطي. لا ينبغي الخلط بينه وبين طريقة الاعتماد الخطي الذي تستخدمه الأداة TRENDS . بناء الجملة هو:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

1. نختار الخلية التي سيتم تنفيذ الحساب وتشغيل معالج الدالة. حدد الاسم "LINEST" في الفئة "إحصائي" وانقر على زر "موافق" .
2. في الحقل "القيم المعروفة ل y" ، نافذة الوسيطة المفتوحة ، أدخل إحداثيات العمود "Enterprise Profit . " في الحقل "القيم المعروفة x" ، ندخل عنوان العمود "السنة" . يتم ترك الحقول المتبقية فارغة. ثم انقر على زر "موافق" .
3. يقوم البرنامج بحساب وعرض قيمة الاتجاه الخطي في الخلية المحددة.
4. الآن علينا أن نعرف قيمة الربح المتوقع لعام 2019. عيّن علامة "=" على أي خلية فارغة في الورقة. نضغط على الخلية ، التي تحتوي على مبلغ الربح الفعلي للسنة الماضية التي تمت دراستها (2016). ضع العلامة "+" . بعد ذلك ، انقر فوق الخلية التي تحتوي على الاتجاه الخطي المحسوب مسبقًا. نضع العلامة "*" . منذ الفترة ما بين السنة الأخيرة من فترة الدراسة (2016) والسنة التي يتم التنبؤ بها (2019) ، تكون الفترة ثلاث سنوات ، ثم نحدد الرقم "3" في الخلية. لإجراء عملية حسابية ، انقر فوق الزر Enter .

كما نرى ، فإن الربح المتوقع ، المحسوب بطريقة التقريب الخطي ، في عام 2019 سيكون 4614.9 ألف روبل.

الطريقة 6: المشغل LGRFPRIBL

الأداة الأخيرة التي سننظر فيها ستكون LGRF . يقوم هذا المشغل بإجراء عمليات حسابية على أساس طريقة التقريب الأسية. يحتوي بناء الجملة على البنية التالية:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

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

1. في قائمة مشغلي Wizards من الدالات ، حدد الاسم "LGRFI" . انقر فوق "موافق" زر.
2. تبدأ نافذة الحجج. في ذلك ، ندخل البيانات بالضبط كما فعلنا مع الدالة LINEST . نضغط على زر "موافق" .
3. يتم حساب نتيجة الاتجاه الأسية والإخراج للخلية المحددة.
4. ضع "=" في الخلية الفارغة. افتح الأقواس وحدد الخلية التي تحتوي على قيمة الأرباح للفترة الفعلية الأخيرة. ضع علامة "*" وحدد الخلية التي تحتوي على الاتجاه الأسي. نضع علامة ناقص ونضغط مرة أخرى على العنصر الذي يقع فيه مبلغ الإيرادات للفترة الأخيرة. نغلق المسند ونقود الشخصيات "* 3 +" بدون علامات اقتباس. مرة أخرى ، انقر فوق نفس الخلية التي تم تحديدها للمرة الأخيرة. لإجراء الحساب ، اضغط على الزر Enter .

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

الدرس: دالات إحصائية أخرى في Excel

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

• ← تبديل المصفوفة في Microsoft Excel
• حساب NPV في Microsoft Excel →