التنبؤ في Excel: 6 طرق مجربة

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

التنبؤ الداخلي

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

الأسلوب 1: خط الاتجاه

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

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

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



2. الآن نحن بحاجة لبناء خط الاتجاه. نحن جعل النقر بزر الماوس الأيمن فوق على أي من النقاط من الرسم التخطيطي. في قائمة السياق تفعيلها، إيقاف الاختيار في الفقرة "إضافة خط الاتجاه".



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

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

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



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



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

   

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

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

الطريقة 2: التنبؤ المشغل

ويمكن إجراء استقراء للبيانات جداول من خلال ميزة قياسية من التنبؤ التفوق. تشير هذه الحجة لهذه الفئة من الأدوات الإحصائية وبناء الجملة التالي:

= توقع (س. known_stations_y، values_x معروفة)

"X" هو حجة، وقيمة الوظيفة التي تحتاج إلى تعريف. في حالتنا، كحجة ستكون السنة التي ينبغي بذل التنبؤ.

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

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

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

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

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

1. نسلط الضوء على خلية فارغة في ورقة حيث يخطط لنتيجة المعالجة. انقر فوق الزر "لصق وظيفة".



2. يفتح المعالج. في فئة "الإحصاء" تخصيص اسم "توقعات"، ثم انقر على زر "موافق".



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

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

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

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



4. يقوم المشغل بحساب أساس البيانات التي تم إدخالها وتعرض النتيجة على الشاشة. لعام 2018، ومن المقرر أن الربح حوالي 4564700 روبل. بناء على الجدول الناتج، يمكننا إنشاء جدول مستخدم باستخدام أدوات إنشاء المخطط التي تمت مناقشتها أعلاه.



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

لكن لا تنس ذلك، كما هو الحال في بناء خط الاتجاه، طول الوقت حتى لا يتجاوز الفترة المتوقعة 30٪ من الفترة الإجمالية التي تراكمت قاعدة البيانات.

درس: استقراء في Excel.

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

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

= الميل (القيم المعروفة؛ القيم المعروفة؛ new_dation_x؛ [const])

كما نرى، تتوافق الحجج "القيم Y المعروفة" و "قيم X المعروفة" بالكامل مع العناصر المماثلة لمشغل PREDSPOT، وتقوم وسيطة "القيم الجديدة X" بوسيطة "X" للأداة السابقة. بالإضافة إلى ذلك، فإن الاتجاه لديه حجة إضافية ثابتة، لكنه ليس إلزاميا ويستخدم إلا إذا كان هناك عوامل دائمة.

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

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

1. نحن ننتج تعيين الخلية لعرض النتيجة وتشغيل الوظائف الرئيسية بالطريقة المعتادة. في فئة "إحصائية" نجد وتخصيص اسم "الاتجاه". انقر فوق الزر "موافق".



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



3. يقوم المشغل بمعالجة البيانات ويعرض النتيجة على الشاشة. كما ترون، ومقدار الأرباح المتوقع لعام 2019 وتحسب على أساس أسلوب الاعتماد الخطي، سيكون كما في الطريقة السابقة من الحساب، 4637800 روبل.

الطريقة الرابعة: نمو المشغل

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

= النمو (values_y معروفا؛ values_x معروفا؛ new_stations_x، [CONST])

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

1. نخصص الخلية الانتاج وبالفعل تسبب عادة وظائف من وظائف. في قائمة المشغلين الإحصائية، نحن نبحث عن شرط "النمو"، ونحن تحيله وانقر على زر "موافق".



2. يتم تنشيط نافذة وسيطة الوظيفة المحددة. ندخل في مجال هذه النافذة، البيانات تشبه تماما كيفية إدخالها في نافذة حجج المشغل الاتجاه. بعد إجراء المعلومات، انقر فوق الزر "موافق".



3. يتم عرض نتيجة معالجة البيانات على الشاشة في الخلية المحددة مسبقا. كما نرى، هذه المرة النتيجة هي 4682.1 ألف روبل. الخلافات من نتائج معالجة البيانات عن طريق الميل المشغل غير ذات أهمية، ولكن كانت متوفرة. ويرجع ذلك إلى حقيقة أن هذه الأدوات تطبيق طرق حسابية مختلفة هذا: طريقة الاعتماد الخطي وطريقة إدمان الأسي.

الطريقة الخامسة: المشغل Linene

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

= Linene (values_y معروفا؛ values_x معروفا؛ new_stations_x، [CONST]، [إحصائيات])

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

1. ونحن ننتج اختيار الخلية التي سوف يتم حساب وتشغيل وظائف من وظائف. نخصص اسم "Linene" في فئة "الإحصائية" وانقر على زر "موافق".



2. في مجال "القيم R المعروفة"، الذي فتح نافذة من الحجج، وإدخال إحداثيات عمود "الربح المؤسسة". في مجال "القيم الشهيرة X"، ونحن نقدم عنوان العمود "السنة". يتم ترك الحقول الباقية فارغة. ثم انقر فوق الزر "موافق".



3. وبحساب البرنامج ويعرض قيمة الاتجاه الخطي إلى الخلية المحددة.



4. الآن لدينا لمعرفة حجم الأرباح المتوقعة ل2019. تثبيت "=" علامة في أي خلية فارغة على ورقة. انقر على الخلية، والتي تحتوي على القيمة الفعلية للأرباح للعام الماضي من العام (2016). وضع علامة "+". مزيد من الضغط على الخلية التي تحتوي على الاتجاه الخطي تحسب في السابق. نضع علامة "*". منذ بين السنة الأخيرة من الفترة المدروسة (2016) والسنة لجعل توقعات (2019) يكمن مدة ثلاث سنوات، وضعنا الرقم "3" في الخلية. لإجراء عملية حسابية من خلال النقر على زر ENTER.

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

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

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

= LGRFPRIBLIN (values_y معروفا؛ values_x معروفا؛ new_stations_x، [CONST]، [إحصائيات])

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

1. في قائمة القائمين على ماجستير في وظائف، ونحن تخصيص اسم "LGRFPribl". نحن جعل انقر على زر "موافق".



2. يبدأ نافذة الحجج. في ذلك نقدم البيانات تماما كما فعلوا عند استخدام وظيفة خطية. انقر فوق الزر "موافق".



3. ويتم احتساب نتيجة الاتجاه الأسي وتمتد إلى خلية معينة.



4. نضع علامة "=" إلى خلية فارغة. نفتح قوسين وحدد الخلية التي تحتوي على قيمة الإيرادات الفعلية للفترة الماضية. نضع علامة "*" وتسليط الضوء على الخلية التي تحتوي على الاتجاه المتسارع. نضع علامة ناقص ومرة ​​أخرى انقر على العنصر الذي مبلغ الإيرادات في الفترة الماضية. نغلق قوس ودفع الشخصيات "* 3 +" دون أن يستشهد. مرة أخرى، انقر على نفس الخلية، والتي تم تخصيصها للمرة الأخيرة. لتنفيذ العملية الحسابية، اضغط على زر ENTER.

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

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

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