پیش بینی یک عنصر بسیار مهم در تقریبا هر حوزه فعالیت است، از جمله اقتصاد تا مهندسی. تعداد زیادی نرم افزار تخصصی در این زمینه وجود دارد. متاسفانه، همه کاربران نمی دانند که پردازشگر صفحه گسترده اکسل در ابزارهای زرادخانه خود برای انجام پیش بینی، که در کارایی آنها بسیار پایین تر از برنامه های حرفه ای است. بیایید پیدا کنیم که این ابزار چیست و چگونه می توان یک پیش بینی را در عمل انجام داد.
روش پیش بینی
هدف هر پیش بینی، شناسایی روند فعلی و تعیین نتیجه مورد انتظار در رابطه با موضوع مورد مطالعه در یک نقطه خاص در زمان آینده است.
روش 1: روند خط
یکی از محبوب ترین پیش بینی های گرافیکی در اکسل، استخراج یک ساختار خط روند است.
بگذارید سعی کنیم پیش بینی سود شرکت در 3 سال بر اساس داده های این شاخص برای 12 سال گذشته.
- ساخت گراف وابستگی بر اساس داده های جدولی متشکل از استدلال ها و مقادیر عملکرد. برای انجام این کار، spacespace را انتخاب کرده و سپس در زبانه قرار دهید "قرار دادن"، بر روی آیکون نوع مورد نظر نمودار کلیک کنید، که در بلوک واقع شده است "نمودارها". سپس نوع مناسب برای وضعیت خاص را انتخاب می کنیم. بهترین انتخاب یک نمودار پراکنده است. شما می توانید یک دید دیگر را انتخاب کنید، اما پس از آن، برای اینکه داده ها به درستی نمایش داده شوند، باید ویرایش کنید، به ویژه، خط استدلال را حذف کنید و مقیاس دیگری از محور افقی را انتخاب کنید.
- حالا ما باید خط روند را بسازیم. ما بر روی هر یک از نقاط در نمودار کلیک راست کنید. در منوی زمینه فعال شده، انتخاب بر روی آیتم را متوقف کنید "افزودن خط روند".
- پنجره قالب بندی خط روند باز می شود. ممکن است یکی از شش نوع تقریب را انتخاب کنید:
- خطی;
- لگاریتمی;
- نمایشی;
- قدرت;
- چندجملهای;
- فیلتر کردن خطی.
بیایید با تقریب خطی شروع کنیم.
در جعبه تنظیمات "پیش بینی" در حوزه "به جلو" شماره را تنظیم کنید "3,0"، از آنجا که ما نیاز به پیش بینی برای سه سال آینده. علاوه بر این، می توانید کادرهای تأیید را بررسی کنید "نمایش معادله در نمودار" و "نمودار ارزش دقت تقریبی (R ^ 2) را در نمودار قرار دهید". آخرین شاخص کیفیت خط روند را نشان می دهد. پس از تنظیمات، بر روی دکمه کلیک کنید. "بستن".
- خط روند ساخته شده است و ما می توانیم از آن برای تعیین مقدار تقریبی سود پس از سه سال استفاده کنیم. همانطور که می بینید، تا آن زمان باید 4500 هزار روبل منتقل شود. ضریب R2، همانطور که در بالا ذکر شد، کیفیت خط روند را نشان می دهد. در مورد ما، ارزش R2 بالا است 0,89. ضریب بالاتر، بالاتر از قابلیت اطمینان خط است. حداکثر مقدار آن می تواند برابر باشد 1. در نظر گرفته شده است که زمانی که نسبت به بیش از 0,85 خط روند قابل اعتماد است
- اگر سطح اطمینان راضی نباشید، می توانید به پنجره فرمت خط روند بازگردید و هر نوع تقریبی دیگر را انتخاب کنید. شما می توانید تمام گزینه های موجود برای پیدا کردن دقیق ترین را امتحان کنید.
لازم به ذکر است که پیش بینی مؤثر با استفاده از استخراج از طریق خط روند می تواند باشد، اگر دوره پیش بینی 30٪ از پایه دوره تحلیل شده را فراتر نگذارد. یعنی، در تجزیه و تحلیل دوره 12 ساله، ما نمیتوانیم پیش بینی مؤثر بیش از 3-4 سال را ایجاد کنیم. اما حتی در این مورد، نسبتا قابل اعتماد خواهد بود، در صورتی که در این زمان هیچ نیروی مهیج و یا برعکس شرایط بسیار مطلوب وجود نداشته باشد که در دوره های گذشته نبود.
درس: چگونه یک خط روند در Excel ایجاد کنیم
روش 2: پیش بینی اپراتور
Extrapolation برای داده های جدولی می تواند از طریق عملکرد استاندارد Excel انجام شود. پیش بینی. این استدلال متعلق به دسته ابزارهای آماری است و دارای نحو زیر است:
= PREDICT (x؛ شناخته شده_y_y؛ شناخته شده values_x)
"X" استدلال است، ارزش تابع که شما می خواهید تعیین کنید. در مورد ما این استدلال سالی است که باید پیش بینی شود.
"ارزش های شناخته شده" - پایه ارزش شناخته شده تابع. در مورد ما، نقش آن مقدار سود برای دوره های قبلی است.
"شناخته شده x" - این ها استدلال هایی هستند که با مقادیر معروف تابع مطابقت دارند. در نقش ما تعداد سالهایی را که اطلاعات مربوط به سود سال های گذشته جمع آوری شده اند، می توانیم پیدا کنیم.
به طور طبیعی، بحث باید لزوما یک دوره زمانی باشد. به عنوان مثال، آن می تواند درجه حرارت، و ارزش عملکرد می تواند سطح گسترش آب در هنگام گرم شدن.
هنگام محاسبه این روش از روش رگرسیون خطی استفاده می کند.
بیایید به تفاوت های ظاهری اپراتور نگاه کنیم پیش بینی در یک مثال خاص تمام میز را نگاه کنید ما باید پیش بینی سود سال 2018 را بدانیم.
- سلول خالی در ورق که در آن شما می خواهید نتیجه پردازش را نمایش دهید انتخاب کنید. ما دکمه را فشار می دهیم "قرار دادن عملکرد".
- باز می شود جادوگر تابع. در رده "آماری" نام را انتخاب کن "پیش بینی"و سپس بر روی دکمه کلیک کنید "خوب".
- پنجره استدلال شروع می شود. در حوزه "X" مقدار استدلالی را که می خواهید ارزش تابع را پیدا کنید تعیین کنید. در مورد ما این 2018 است. بنابراین، ما یک سابقه داریم "2018". اما بهتر است این شاخص را در سلول بر روی ورق و در میدان نشان دهید "X" فقط یک لینک به آن بدهید این امر به محاسبه خودکار در آینده امکان می دهد و در صورت لزوم به راحتی سال را تغییر می دهد.
در حوزه "ارزش های شناخته شده" مختصات ستون را مشخص کنید "سود شرکت". این را می توان با قرار دادن مکان نما در این زمینه و سپس نگه داشتن دکمه سمت چپ ماوس و انتخاب ستون مربوطه روی برگ انجام داد.
به طور مشابه در زمینه "شناخته شده x" ما آدرس ستون را وارد می کنیم "سال" با داده ها برای دوره گذشته.
پس از وارد شدن تمام اطلاعات، بر روی دکمه کلیک کنید. "خوب".
- اپراتور بر اساس داده های وارد شده محاسبه می کند و نتیجه را روی صفحه نمایش می دهد. برای سال 2018، سود در سطح 4564.7 هزار روبل برنامه ریزی شده است. بر اساس جدول نتیجه، ما می توانیم یک گراف با استفاده از ابزار ایجاد نمودار، که در بالا مورد بحث قرار می گیرد، ساخت کنیم.
- اگر سال را در سلول مورد استفاده برای ورود به بحث تغییر دهید، نتیجه بر اساس آن تغییر خواهد کرد و گراف به صورت خودکار به روز رسانی خواهد شد. به عنوان مثال، طبق پیش بینی های سال 2019، سود سود 4637.8 هزار روبل خواهد بود.
اما فراموش نکنید که همانطور که در ساخت خط روند، مدت زمان قبل از دوره پیش بینی نباید بیش از 30٪ از کل دوره که برای آن پایگاه داده جمع شده بود.
درس: استخراج اکسل
روش 3: اپراتور TENDENCY
برای پیش بینی، شما می توانید یک تابع دیگر استفاده کنید - TREND. این نیز متعلق به دسته اپراتورهای آماری است. نحو آن بسیار شبیه نحو ابزار است. پیش بینی و به نظر می رسد این است:
= TREND (شناخته شده values_y؛ شناخته شده values_x؛ new_values_x؛ [const])
همانطور که می بینید، استدلال "ارزش های شناخته شده" و "شناخته شده x" به طور کامل به عناصر مشابه اپراتور مربوط می شود پیش بینیو استدلال "مقادیر جدید x" مطابقت دارد "X" ابزار قبلی علاوه بر این، TREND یک استدلال اضافی وجود دارد "ثابت"اما این اجباری نیست و فقط در صورت وجود عوامل ثابت استفاده می شود.
این اپراتور به طور موثر در حضور وابستگی خطی تابع استفاده می شود.
بیایید ببینیم چگونه این ابزار با همان آرایه داده کار می کند. برای مقایسه نتایج به دست آمده، نقطه پیش بینی را در سال 2019 تعریف می کنیم.
- ما یک سلول را برای نشان دادن نتیجه و اجرای اجرا می کنیم جادوگر تابع به طور معمول در رده "آماری" پیدا کردن و انتخاب نام "TREND". ما دکمه را فشار می دهیم "خوب".
- پنجره استدلال اپراتور باز می شود TREND. در حوزه "ارزش های شناخته شده" قبلا در بالا توضیح داده شد، مختصات ستون را وارد کنید "سود شرکت". در حوزه "شناخته شده x" آدرس ستون را وارد کنید "سال". در حوزه "مقادیر جدید x" مراجعه به سلول را وارد کنید که در آن شماره سالی که پیش بینی باید مشخص شود، واقع شده است. در مورد ما این 2019 است. فیلد "ثابت" خالی بگذار روی دکمه کلیک کنید "خوب".
- اپراتور داده ها را پردازش می کند و نتیجه را روی صفحه نمایش می دهد. همانطور که می بینید، مقدار سود پیش بینی شده سال 2019، که به روش وابستگی خطی محاسبه می شود، همانند روش قبلی محاسبه، 4637.8 هزار روبل است.
روش 4: اپراتور رشد
یکی دیگر از تابع که می تواند برای پیش بینی در اکسل مورد استفاده قرار گیرد اپراتور GROWTH است. این نیز متعلق به گروه آماری ابزار است، اما، برخلاف گذشته، از روش وابستگی خطی استفاده نمی کند، بلکه روش نمونه ای برای محاسبه است. نحو این ابزار به این شکل است:
= رشد (ارزش های شناخته شده_؛ ارزش های شناخته شده_x؛ جدید_الایز_x؛ [const])
همانطور که می بینید، استدلالات این تابع دقیقا استدلال های اپراتور را تکرار می کند TRENDبه طوری که ما در مورد توصیف خود را بار دوم نمی گیریم، اما بلافاصله به کاربرد این ابزار در عمل تبدیل خواهد شد.
- سلول خروجی را انتخاب کنید و آن را به صورت معمولی فراخوانی کنید. جادوگر تابع. در لیست اپراتورهای آماری به دنبال آیتم هستند "رشد"آن را انتخاب کنید و بر روی دکمه کلیک کنید "خوب".
- فعال کردن پنجره argument از تابع فوق رخ می دهد. داده های وارد شده در زمینه های این پنجره به طور کامل همانند ما آنها را در پنجره استدلال اپراتور وارد کرده ایم TREND. پس از وارد شدن اطلاعات، روی دکمه کلیک کنید "خوب".
- نتیجه پردازش داده ها بر روی مانیتور در سلول قبلی مشخص شده نمایش داده می شود. همانطور که می بینید، این بار نتیجه 4682.1 هزار روبل است. تفاوت پردازش اطلاعات اپراتور TREND ناچیز است، اما آنها در دسترس هستند. این به دلیل این واقعیت است که این ابزار از روش های مختلف محاسبه استفاده می کند: روش وابستگی خطی و روش وابستگی نمایشی.
روش 5: اپراتور LINEST
اپراتور خط LINE هنگام محاسبه از روش تقریبی خطی استفاده می کند. این نباید با روش خطی که توسط ابزار استفاده می شود اشتباه گرفته شود. TREND. نحو آن:
= LINEST (مقادیر شناخته شده؛ ارزش های شناخته شده_x؛ New_values_x؛ [const]؛ [آمار])
دو استدلال گذشته اختیاری است. ما با دو روش اول با روش های قبلی آشنا هستیم. اما شما احتمالا متوجه شده اید که در این کار هیچ استدلالی به ارزش های جدید وجود ندارد. واقعیت این است که این ابزار فقط تغییر درآمد یک واحد زمانی را تعیین می کند، که در مورد ما یک سال است، اما ما باید کل نتیجه را به طور جداگانه محاسبه کنیم، اضافه کردن به آخرین ارزش واقعی واقعی نتیجه حاصل از محاسبه اپراتور خط LINEتعداد سالها ضرب شده است.
- انتخاب یک سلول که در آن محاسبه انجام خواهد شد و کارشناسی ارشد توابع را راه اندازی کنید. نام را انتخاب کنید "LINEYN" در رده "آماری" و بر روی دکمه کلیک کنید "خوب".
- در حوزه "ارزش های شناخته شده"از پنجره استدلال که باز می شود، مختصات ستون را وارد کنید "سود شرکت". در حوزه "شناخته شده x" آدرس ستون را وارد کنید "سال". زمینه های باقی مانده خالی هستند. سپس بر روی دکمه کلیک کنید "خوب".
- این برنامه محاسبه و نشان می دهد در سلول انتخاب شده ارزش روند خطی.
- اکنون باید ارزش سود پیش بینی شده سال 2019 را بیابیم. علامت را تنظیم کنید "=" به هر سلول خالی روی صفحه با کلیک بر روی سلول که شامل مقدار واقعی سود برای سال گذشته مورد مطالعه (2016). ما علامت گذاری کردیم "+". بعد، بر روی سلول که حاوی روند خطی قبلی محاسبه شده کلیک کنید. ما علامت گذاری کردیم "*". از آنجا که بین سال گذشته از دوره مطالعه (2016) و سالی که پیش بینی می شود (2019)، مدت سه سال ناپدید می شود، تعداد آن را در سلول تعیین می کنیم "3". برای محاسبه، روی دکمه کلیک کنید. وارد شوید.
همانطور که می بینید، ارزش پیش بینی شده سود، که با روش تقریبی خطی محاسبه می شود، در سال 2019 4614.9 هزار روبل خواهد بود.
روش 6: اپراتور LOGEST
آخرین ابزار ما این است که LGGRPRIBL. این اپراتور محاسبات را بر اساس روش تقریب آماری انجام می دهد. نحو آن ساختار زیر است:
= LOGPLPR (شناخته شده values_y؛ شناخته شده values_x؛ new_values_x؛ [const]؛ [آمار])
همانطور که می بینید، تمام استدلالات به طور کامل عناصر مربوطه از عملکرد قبلی را تکرار می کنند. الگوریتم محاسبه پیش بینی کمی تغییر می کند. این تابع محدوده روند نمایشی را نشان می دهد که نشان می دهد چند بار مقدار درآمد در یک دوره، یعنی یک سال تغییر خواهد کرد. ما نیاز به پیدا کردن تفاوت سود در بین آخرین دوره واقعی و اولین برنامه ریزی شده، آن را چندین دوره برنامه ریزی شده را چند برابر کنید. (3) و در نتیجه مجموع آخرین دوره واقعی را اضافه کنید.
- در لیست اپراتورهای Wizard Function، نام را انتخاب کنید LGRFPRIBL. روی دکمه کلیک کنید. "خوب".
- پنجره استدلال شروع می شود. در آن اطلاعات دقیقا همانطور که انجام شد، با استفاده از تابع وارد می کنیم خط LINE. روی دکمه کلیک کنید "خوب".
- نتیجه روند نمایشی محاسبه و نمایش داده شده در سلول نشان داده شده است.
- ما علامت گذاری کردیم "=" در یک سلول خالی براکت ها را باز کنید و سلول هایی را که حاوی مقدار درآمد برای آخرین دوره واقعی هستند را انتخاب کنید. ما علامت گذاری کردیم "*" و سلول حاوی روند نمایشی را انتخاب کنید. ما یک علامت منفی گذاشتیم و دوباره روی عنصر که در آن مقدار درآمد برای آخرین دوره است کلیک کنید. بستن براکت و رانندگی کاراکترها. "*3+" بدون نقل قول. باز هم روی یک سلول که در آخرین بار انتخاب شد کلیک کنید. برای محاسبه روی دکمه کلیک کنید وارد شوید.
مقدار پیش بینی شده سود در سال 2019، که با روش تقریب آماری محاسبه می شود، 46392 هزار روبل خواهد بود که در مقایسه با نتایج حاصل از محاسبات با روش های قبلی بسیار متفاوت نخواهد بود.
درس: سایر توابع آماری در اکسل
ما متوجه شدیم که چگونه می توان یک پیش بینی در برنامه اکسل ایجاد کرد. به صورت گرافیکی، این را می توان با استفاده از خط روند و به صورت تحلیلی با استفاده از تعدادی از توابع آماری ساخته شده انجام داد. در نتیجه پردازش داده های یکسان توسط این اپراتورها ممکن است یک نتیجه متفاوت باشد. اما این تعجب آور نیست، زیرا همه از روش های مختلف محاسبه استفاده می کنند. اگر نوسانات کوچک باشد، تمام این گزینه ها برای یک مورد خاص قابل قبول است.
