ابزار پیش بینی در مایکروسافت اکسل

پیش بینی یک عنصر بسیار مهم تقریباً در هر زمینه فعالیتی است ، از اقتصاد گرفته تا مهندسی. تعداد زیادی نرم افزار وجود دارد که در این زمینه تخصص دارد. متأسفانه ، همه کاربران نمی دانند که پردازنده معمول صفحه گسترده اکسل در ابزارهای زرادخانه خود برای پیش بینی ، که از نظر کارایی در مقایسه با برنامه های حرفه ای چندان پایبند نیستند. بیایید دریابیم که این ابزارها چیست و چگونه می توان در عمل پیش بینی کرد.

روش پیش بینی

هدف از هر پیش بینی شناسایی روند فعلی و تعیین نتیجه مورد انتظار در رابطه با هدف مورد مطالعه در یک مقطع زمانی مشخص در آینده است.

روش 1: خط روند

یکی از محبوب ترین انواع پیش بینی گرافیکی در اکسل ، برون یابی با ساختن یک خط روند است.

بیایید سعی کنیم میزان سود شرکت در 3 سال را بر اساس داده های این شاخص برای 12 سال گذشته پیش بینی کنیم.

1. ما یک نمودار وابستگی را بر اساس داده های جدولی متشکل از آرگومان ها و مقادیر عملکرد ایجاد می کنیم. برای انجام این کار ، قسمت جدول را انتخاب کنید ، و سپس ، در برگه قرار بگیرید درج کنیدروی نماد نوع نمودار مورد نظر که در بلوک قرار دارد کلیک کنید نمودارها. سپس نوع مناسب برای یک وضعیت خاص را انتخاب می کنیم. بهتر است نمودار پراکندگی را انتخاب کنید. شما می توانید نمای دیگری را انتخاب کنید ، اما پس از آن ، به طوری که داده ها به درستی نمایش داده می شوند ، باید ویرایش را انجام دهید ، به ویژه ، خط آرگومان را بردارید و مقیاس دیگری از محور افقی را انتخاب کنید.



2. اکنون باید یک خط روند ایجاد کنیم. ما بر روی هر یک از نقاط نمودار کلیک راست می کنیم. در فهرست زمینه فعال شده ، انتخاب را روی مورد متوقف کنید Trend Line اضافه کنید.



3. پنجره قالب بندی خط روند باز می شود. در آن می توانید یکی از شش نوع تقریبی را انتخاب کنید:
   • خطی;
   • لگاریتمی;
   • نمایی;
   • قدرت;
   • چند جمله ای;
   • فیلتر خطی.

   بیایید با انتخاب یک تقریب خطی شروع کنیم.

   در قسمت تنظیمات "پیش بینی" در این زمینه "رو به جلو" شماره را تنظیم کنید "3,0"، از آنجا که ما نیاز به پیش بینی برای سه سال قبل. علاوه بر این ، می توانید تنظیمات را علامت بزنید. "نمایش معادله در نمودار" و "مقدار اطمینان تقریبی (R ^ 2) را روی نمودار قرار دهید". آخرین شاخص کیفیت خط روند را نشان می دهد. بعد از انجام تنظیمات ، روی دکمه کلیک کنید بستن.



4. خط روند ساخته شده است و از آن می توان مقدار تقریبی سود را در سه سال تعیین کرد. همانطور که می بینیم ، تا آن زمان باید بیش از 4500 هزار روبل باشد. ضریب R2همانطور که در بالا ذکر شد ، کیفیت خط روند را نشان می دهد. در مورد ما ، ارزش R2 تشکیل می دهد 0,89. هرچه ضریب بالاتر باشد ، قابلیت اطمینان خط نیز بیشتر است. حداکثر مقدار آن ممکن است برابر باشد 1. به طور کلی پذیرفته می شود که با ضریب فوق 0,85 خط روند قابل اعتماد است



5. اگر سطح اطمینان مناسب شما نیست ، می توانید به پنجره فرمت خط روند بازگردید و هر نوع تقریبی دیگر را انتخاب کنید. می توانید برای یافتن دقیق ترین گزینه ، تمام گزینه های موجود را امتحان کنید.

   

   لازم به ذکر است که پیش بینی با استفاده از برون یابی از طریق خط روند می تواند مؤثر باشد در صورتی که دوره پیش بینی بیش از 30٪ از پایه تجزیه و تحلیل دوره ها نباشد. یعنی وقتی یک دوره 12 ساله را تجزیه و تحلیل می کنیم ، بیش از 3-4 سال نمی توان پیش بینی موثری کرد. اما حتی در این حالت ، اگر در این مدت هیچ گونه فورس فورسی یا برعکس شرایط بسیار مطلوب ، که در دوره های قبل وجود نداشت ، نسبتاً قابل اعتماد خواهد بود.

درس: نحوه ساختن یک خط روند در اکسل

روش 2: اپراتور FORECAST

برون یابی برای داده های جدولی از طریق عملکرد استاندارد اکسل قابل انجام است تقدیر. این استدلال متعلق به دسته ابزارهای آماری است و دارای نحوی زیر است:

= PREDICT (X ؛ معروف_ی_والو ؛ معروف_x_values)

"X" استدلالی است که برای آن باید مقدار عملکرد تعیین شود. در مورد ما ، استدلالی سالی خواهد بود که باید پیش بینی شود.

ارزشهای شناخته شده - مقادیر شناخته شده تابع. در مورد ما ، نقش آن براساس میزان سود دوره های قبل بازی می شود.

ارزشهای شناخته شده x آرگومان هایی که مقادیر شناخته شده تابع با آنها مطابقت دارند. در نقش آنها ، شماره سالهایی را که برای آنها اطلاعات مربوط به سود سالهای گذشته جمع آوری شده ، داریم.

به طور طبیعی ، استدلال نیازی به زمان ندارد. به عنوان مثال ، ممکن است دما باشد و مقدار عملکرد ممکن است سطح گرم شدن آب هنگام گرم شدن باشد.

هنگام محاسبه این روش از روش رگرسیون خطی استفاده می شود.

بیایید به تفاوت های ظریف استفاده از اپراتور بپردازیم تقدیر به عنوان مثال تمام میز را بگیرید. ما باید پیش بینی سود برای سال 2018 را بدانیم.

1. یک سلول خالی را روی برگه ای که می خواهید نتیجه پردازش را نمایش دهید انتخاب کنید. روی دکمه کلیک کنید "درج عملکرد".



2. باز می شود جادوگر ویژگی. در دسته "آماری" نام را انتخاب کنید "نسخه"و سپس بر روی دکمه کلیک کنید "خوب".



3. پنجره آرگومان شروع می شود. در زمینه "X" مقدار آرگومان را که می خواهید مقدار عملکرد را پیدا کنید ، نشان دهید. در مورد ما ، این سال 2018 است. بنابراین ، ما می نویسیم "2018". اما بهتر است این شاخص را در یک سلول روی برگه و در قسمت نشان دهید "X" فقط یک لینک به آن بدهید این به شما امکان می دهد تا در آینده محاسبات را به صورت خودکار انجام دهید و در صورت لزوم به راحتی سال را تغییر دهید.

   در زمینه ارزشهای شناخته شده مختصات ستون را مشخص کنید "سود شرکت". این کار را می توان با قرار دادن مکان نما در قسمت زمینه و سپس نگه داشتن دکمه سمت چپ ماوس و برجسته کردن ستون مربوطه روی صفحه.

   به طور مشابه در زمینه ارزشهای شناخته شده x آدرس ستون را وارد کنید "سال" با داده های دوره گذشته

   پس از وارد کردن تمام اطلاعات ، روی دکمه کلیک کنید "خوب".



4. اپراتور بر اساس داده های وارد شده محاسبه می کند و نتیجه را روی صفحه نمایش می دهد. برای سال 2018 ، پیش بینی شده است که در منطقه 4،564.7 هزار روبل سود کسب کنید. بر اساس جدول به دست آمده ، می توانیم با استفاده از ابزارهای نمودار مورد بحث در مورد نمودار ، نمودار ایجاد کنیم.



5. اگر سال را در سلول مورد استفاده برای وارد کردن استدلال تغییر دهید ، نتیجه مطابق با آن تغییر می کند و برنامه به طور خودکار به روز می شود. به عنوان مثال ، طبق پیش بینی های انجام شده در سال 2019 ، میزان سود 4637.8 هزار روبل خواهد بود.

اما فراموش نکنید که ، مانند ساخت خط روند ، مدت زمان قبل از دوره پیش بینی نباید از 30٪ کل دوره زمانی که بانک اطلاعاتی انباشته شده بود تجاوز کند.

درس: برون یابی در اکسل

روش 3: اپراتور TREND

برای پیش بینی ، می توانید از یک عملکرد دیگر استفاده کنید - تجارت. همچنین به دسته اپراتورهای آماری تعلق دارد. نحو آن بسیار شبیه به نحو ابزار است تقدیر و مثل این است:

= TREND (ارزشهای شناخته شده_y؛ مقادیر شناخته شده_x؛ new_values_x؛ [ثابت])

همانطور که مشاهده می کنید استدلالها ارزشهای شناخته شده و ارزشهای شناخته شده x کاملاً با عناصر مشابه اپراتور مطابقت دارد تقدیرو استدلال "مقادیر x جدید" استدلال مسابقات "X" ابزار قبلی علاوه بر این ، تجارت یک استدلال اضافی وجود دارد "ثابت"اما اختیاری است و فقط در صورت وجود عوامل ثابت استفاده می شود.

این عملگر بیشتر در حضور وابستگی خطی از عملکرد مورد استفاده قرار می گیرد.

بیایید ببینیم چگونه این ابزار با همان آرایه داده کار خواهد کرد. برای مقایسه نتایج ، ما نقطه پیش بینی را به عنوان سال 2019 تعریف می کنیم.

1. ما سلول را برای نمایش نتیجه و اجرای آن تعیین می کنیم جادوگر ویژگی به روش معمول در دسته "آماری" نام را پیدا و برجسته کنید "TREND". روی دکمه کلیک کنید "خوب".



2. پنجره Argument Operator باز می شود تجارت. در زمینه ارزشهای شناخته شده با روشی که در بالا توضیح داده شد ، مختصات ستون را وارد می کنیم "سود شرکت". در زمینه ارزشهای شناخته شده x آدرس ستون را وارد کنید "سال". در زمینه "مقادیر x جدید" ما پیوند سلول را که شماره سال در آن قرار دارد وارد می کنیم و پیش بینی باید برای آن مشخص شود. در مورد ما ، این 2019 است. میدان "ثابت" آن را خالی بگذارید. روی دکمه کلیک کنید "خوب".



3. عملگر داده ها را پردازش می کند و نتیجه را روی صفحه نمایش می دهد. همانطور که مشاهده می کنید ، میزان سود پیش بینی شده برای سال 2019 ، که با روش وابستگی خطی محاسبه شده است ، مانند روش محاسبه قبلی 4637.8 هزار روبل خواهد بود.

روش 4: عملگر رشد

عملکرد دیگری که می تواند برای پیش بینی در اکسل مورد استفاده قرار گیرد ، عملگر GROWTH است. همچنین به گروه آماری ابزارها تعلق دارد ، اما برخلاف موارد قبلی ، هنگام محاسبه آن ، از روش وابستگی خطی استفاده نمی کند بلکه از روش نمایی استفاده می کند. نحو این ابزار به شرح زیر است:

= GROWTH (ارزشهای شناخته شده_y؛ مقادیر شناخته شده_x؛ new_values_x؛ [const])

همانطور که مشاهده می کنید ، آرگومان های این عملکرد دقیقاً آرگومان های اپراتور را تکرار می کنند تجارتبنابراین ، ما برای بار دوم به توضیحات آنها نخواهیم پرداخت ، اما بلافاصله به کاربرد عملی این ابزار ادامه خواهیم داد.

1. سلول را برای نتیجه گیری انتخاب می کنیم و به روش معمول آنرا صدا می کنیم جادوگر ویژگی. در لیست اپراتورهای آماری ، مورد را جستجو کنید ریستآن را انتخاب کرده و بر روی دکمه کلیک کنید "خوب".



2. پنجره آرگومان عملکرد فوق فعال می شود. داده ها را در قسمت های این پنجره به همان روشی که در پنجره آرگومان های اپراتور وارد کردیم وارد کنید تجارت. پس از وارد کردن اطلاعات ، روی دکمه کلیک کنید "خوب".



3. نتیجه پردازش داده ها در سلول قبلی نشان داده شده در مانیتور نمایش داده می شود. همانطور که مشاهده می کنید ، این بار نتیجه 4682.1 هزار روبل است. تفاوت از نتایج پردازش داده های اپراتور تجارت ناچیز است ، اما در دسترس هستند. این به این دلیل است که این ابزارها از روشهای مختلف محاسبه استفاده می کنند: روش وابستگی خطی و روش وابستگی نمایی.

روش 5: عملگر LINEAR

عملگر خط در محاسبه از روش تقریبی خطی استفاده شده است. نباید با روش وابستگی خطی که توسط ابزار استفاده می شود اشتباه گرفته شود. تجارت. نحو آن به شرح زیر است:

= LINE (ارزشهای شناخته شده_y؛ مقادیر شناخته شده_x؛ new_values_x؛ [ثابت]؛ [آمار])

دو دلیل اخیر اختیاری است. با دو مورد اول با روش های قبلی آشنا هستیم. اما احتمالاً متوجه شده اید که هیچ استدلالی در این عملکرد وجود ندارد که به مقادیر جدید اشاره کند. واقعیت این است که این ابزار فقط تغییر درآمدها را برای واحد دوره تعیین می کند ، که در مورد ما برابر با یک سال است ، اما ما باید نتیجه کل را بطور جداگانه محاسبه کنیم و نتیجه محاسبه اپراتور را به آخرین ارزش واقعی سود اضافه می کنیم خطبرابر تعداد سالها

1. سلول را انتخاب می کنیم که در آن محاسبه انجام خواهد شد و Function Wizard را اجرا می کنیم. نام را انتخاب کنید LINEIN در رده "آماری" و روی دکمه کلیک کنید "خوب".



2. در زمینه ارزشهای شناخته شده، پنجره باز شده آرگومان ها ، مختصات ستون را وارد کنید "سود شرکت". در زمینه ارزشهای شناخته شده x آدرس ستون را وارد کنید "سال". قسمت های باقی مانده خالی مانده است. سپس بر روی دکمه کلیک کنید "خوب".



3. این برنامه مقدار روند خطی را در سلول انتخابی محاسبه و نمایش می دهد.



4. حال باید اندازه سود پیش بینی شده برای سال 2019 را دریابیم. علامت را تنظیم کنید "=" به هر سلول خالی روی برگه ما بر روی سلول کلیک می کنیم که حاوی میزان واقعی سود سال گذشته مطالعه شده (2016) است. ما نشانه ای می گذاریم "+". بعد ، روی سلول که حاوی روند خطی قبلاً محاسبه شده است کلیک کنید. ما نشانه ای می گذاریم "*". از آنجا که بین سال گذشته دوره مطالعه (2016) و سالی که می خواهید پیش بینی کنید (2019) ، یک دوره سه ساله دروغ است ، ما شماره را در سلول قرار می دهیم "3". برای انجام محاسبه روی دکمه کلیک کنید وارد شوید.

همانطور که مشاهده می کنید ، حاشیه سود پیش بینی شده محاسبه شده با روش تقریبی خطی در سال 2019 مبلغ 4،614.9 هزار روبل خواهد بود.

روش 6: عملگر LGRFPPRIBLE

آخرین ابزاری که به بررسی آن خواهیم پرداخت LGRFPPRIBLE. این اپراتور محاسبات را بر اساس روش تقریبی نمایی انجام می دهد. نحو آن دارای ساختار زیر است:

= LGRFPRIBLE (ارزشهای شناخته شده_y؛ مقادیر شناخته شده_x؛ new_values_x؛ [const] ؛ [آمار])

همانطور که مشاهده می کنید ، همه آرگومان ها عناصر مربوط به عملکرد قبلی را کاملاً تکرار می کنند. الگوریتم محاسبه پیش بینی کمی تغییر خواهد کرد. این تابع روند نمایی را محاسبه می کند ، که نشان می دهد چند برابر مقدار درآمد برای یک دوره تغییر می یابد ، یعنی برای یک سال. ما باید تفاوت سود را بین آخرین دوره واقعی و اولین برنامه ریزی شده پیدا کنیم ، آن را با تعداد دوره های برنامه ریزی شده ضرب کنیم (3) و مبلغ آخرین دوره واقعی را به نتیجه اضافه کنید.

1. در لیست اپراتورهای Function Wizard ، نام را انتخاب کنید LGRFPPRIBL. روی دکمه کلیک کنید "خوب".



2. پنجره آرگومان شروع می شود. در آن ، ما با استفاده از عملکرد ، داده ها را دقیقاً مانند گذشته وارد می کنیم خط. روی دکمه کلیک کنید "خوب".



3. نتیجه روند نمایی در سلول تعیین شده محاسبه و نمایش داده می شود.



4. ما نشانه ای می گذاریم "=" داخل یک سلول خالی براکت ها را باز کنید و سلولی را که حاوی مقدار درآمد برای آخرین دوره واقعی است ، انتخاب کنید. ما نشانه ای می گذاریم "*" و سلول حاوی روند نمایی را انتخاب کنید. ما یک علامت منفی قرار داده و دوباره روی عنصری که در آن ارزش درآمد برای آخرین دوره قرار دارد کلیک می کنیم. براکت را ببندید و در کاراکترها رانندگی کنید "*3+" بدون نقل قول دوباره روی همان سلولی که برای آخرین بار انتخاب شده است کلیک کنید. برای انجام محاسبه ، روی دکمه کلیک کنید وارد شوید.

میزان پیش بینی سود در سال 2019 که با روش تقریبی نمایی محاسبه شده است ، 4،639.2 هزار روبل خواهد بود که باز هم تفاوت چندانی با نتایج به دست آمده در محاسبه قبلی نخواهد داشت.

درس: سایر عملکردهای آماری در اکسل

فهمیدیم که چگونه می توان پیش بینی هایی را در برنامه اکسل انجام داد. این کار با استفاده از یک خط روند و به صورت تحلیلی و با استفاده از تعدادی توابع آماری داخلی می تواند به صورت گرافیکی انجام شود. در نتیجه پردازش داده های یکسان توسط این اپراتورها ، می توان نتیجه متفاوتی بدست آورد. اما این تعجب آور نیست ، زیرا همه آنها از روش های مختلف محاسبه استفاده می کنند. اگر نوسانات كوچك باشد ، مي توان تمام اين گزينه ها را كه براي يك مورد خاص قابل استفاده است ، نسبتاً قابل اعتماد در نظر گرفت.