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

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

روش پیش بینی

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

روش 1: روند خط

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

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

1. ما یک نمودار وابستگی بر اساس داده های جدولی متشکل از استدلال ها و مقادیر تابع ایجاد می کنیم. برای انجام این کار، spacespace را انتخاب کرده و سپس در « Tab » قرار دهید ، بر روی نماد نوع مورد نظر نمودار کلیک کنید، که در بلوک «Diagrams» قرار دارد. سپس نوع مناسب را برای وضعیت خاص انتخاب می کنیم. بهترین انتخاب یک نمودار پراکنده است. شما می توانید یک نمایش دیگر را انتخاب کنید، اما پس از آن، برای اینکه داده ها به درستی نمایش داده شوند، باید ویرایش کنید، به ویژه، خط استدلال را حذف کنید و مقیاس دیگری از محور افقی را انتخاب کنید.
2. حالا ما باید خط روند را بسازیم. ما بر روی هر یک از نقاط در نمودار کلیک راست کنید. در منوی زمینه فعال، انتخاب بر روی آیتم «افزودن یک خط روند» را متوقف کنید.
3. پنجره قالب بندی خط روند باز می شود. ممکن است یکی از شش نوع تقریب را انتخاب کنید:
   • خطی
   • لگاریتمی ؛
   • نمایشی ؛
   • قدرت
   • چندجملهای ؛
   • فیلتر کردن خطی

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

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

4. خط روند ساخته شده است و ما می توانیم از آن برای تعیین مقدار تقریبی سود پس از سه سال استفاده کنیم. همانطور که می بینیم، تا آن زمان باید 4500 هزار روبل منتقل شود. ضریب R2 ، همانطور که در بالا ذکر شد، کیفیت خط روند را نشان می دهد. در مورد ما، ارزش R2 0.89 است . ضریب بالاتر، بالاتر از قابلیت اطمینان خط است. حداکثر مقدار آن می تواند برابر با 1 باشد. اعتقاد بر این است که هنگامی که این نسبت بالاتر از 0.85 باشد خط روند قابل اعتماد است.
5. اگر شما از سطح اعتماد به نفس راضی نیستید، می توانید به پنجره فرمت خط روند بازگردید و هر نوع تقریبی دیگر را انتخاب کنید. شما می توانید تمام گزینه های موجود برای پیدا کردن دقیق ترین را امتحان کنید.

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

درس: چگونه یک خط روند در Excel ایجاد کنیم

روش 2: پیش بینی اپراتور

Extrapolation برای داده های جدولی را می توان با استفاده از تابع استاندارد Excel Pre-Decoration انجام داد. این استدلال متعلق به دسته ابزارهای آماری است و دارای نحو زیر است:

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

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

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

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

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

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

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

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

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

   به طور مشابه، در قسمت "مقادیر شناخته شده x"، آدرس ستون "Year" را با داده ها برای دوره گذشته وارد کنید.

   پس از وارد شدن تمام اطلاعات، روی دکمه «OK» کلیک کنید.

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

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

درس: استخراج اکسل

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

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

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

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

این اپراتور به طور موثر در حضور وابستگی خطی تابع استفاده می شود.

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

1. ما یک سلول را برای نشان دادن نتیجه ایجاد می کنیم و جادوگر تابع را به طور معمول راه اندازی می کنیم. در بخش "آمار" ما نام "TREND" را پیدا می کنیم . روی دکمه "OK" کلیک کنید.
2. پنجره استدلال اپراتور TENDENCY باز می شود. در فیلد "مقادیر شناخته شده ی" ، با استفاده از روش فوق توضیح داده شده، مختصات ستون "سود پروژه" را وارد کنید . در قسمت "مقادیر شناخته شده x"، آدرس ستون "Year" را وارد کنید. در فیلد "New x values"، مرجع به سلول را وارد کنید که در آن شماره سال که باید پیش بینی شده باشد، واقع شده است. در مورد ما این 2019 است. فیلد "Constant" خالی است. روی دکمه "OK" کلیک کنید.
3. اپراتور داده ها را پردازش می کند و نتیجه را روی صفحه نمایش می دهد. همانطور که می بینید، مقدار سود پیش بینی شده سال 2019، که به روش وابستگی خطی محاسبه می شود، همانند روش قبلی محاسبه، 4637.8 هزار روبل است.

روش 4: اپراتور رشد

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

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

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

1. سلول خروجی نتیجه را انتخاب کنید و Master Thumbها را با روش معمولی فراخوانی کنید. در فهرست اپراتورهای آماری، به دنبال مورد "رشد" ، آن را انتخاب کنید و با کلیک بر روی دکمه "OK" .
2. فعال کردن پنجره argument از تابع فوق رخ می دهد. داده های وارد شده در زمینه های این پنجره کاملا شبیه به نحوه ورود آنها به پنجره استدلال اپراتور TREND است. پس از وارد شدن اطلاعات، روی دکمه «OK» کلیک کنید.
3. نتیجه پردازش داده ها بر روی مانیتور در سلول قبلی مشخص شده نمایش داده می شود. همانطور که می بینید، این بار نتیجه 4682.1 هزار روبل است. تفاوت از نتایج پردازش داده ها توسط اپراتور TREND ناچیز است، اما آنها وجود دارند. این به دلیل این واقعیت است که این ابزار از روش های مختلف محاسبه استفاده می کند: روش وابستگی خطی و روش وابستگی نمایشی.

روش 5: اپراتور LINEST

اپراتور LINEST از روش تقریبی خطی برای محاسبه استفاده می کند. این نباید با روش وابستگی خطی که توسط ابزار TREND مورد استفاده قرار می گیرد اشتباه گرفته شود. نحو آن:

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

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

1. انتخاب یک سلول که در آن محاسبه انجام خواهد شد و کارشناسی ارشد توابع را راه اندازی کنید. نام "LINEST" را در بخش "Statistical" انتخاب کنید و روی دکمه "OK" کلیک کنید.
2. در فیلد "شناخته شده ی y" در پنجره استدلال باز شده، مختصات ستون "سود پروژه" را وارد کنید. در قسمت "مقادیر شناخته شده" ، آدرس ستون "سال" را وارد کنید . زمینه های باقی مانده خالی هستند. سپس بر روی دکمه "OK" کلیک کنید.
3. این برنامه محاسبه و نشان می دهد در سلول انتخاب شده ارزش روند خطی.
4. اکنون باید ارزش سود پیش بینی شده سال 2019 را بیابیم. علامت "=" را به هر سلول خالی روی صفحه تنظیم کنید. با کلیک بر روی سلول که حاوی مقدار واقعی سود برای سال گذشته مورد مطالعه (2016). ما علامت "+" قرار داده ایم بعد، بر روی سلول که حاوی روند خطی قبلی محاسبه شده کلیک کنید. ما علامت "*" را قرار دادیم. از آنجایی که بین سال گذشته دوره مطالعه (2016) و سالی که پیش بینی می شود (2019) یک دوره سه ساله است، ما تعداد سلول 3 را تعیین می کنیم. برای محاسبه، روی دکمه Enter کلیک کنید.

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

روش 6: اپراتور LOGEST

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

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

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

1. در لیست اپراتورهای کارشناسی ارشد توابع ، نام "LOGEST" را انتخاب کنید . روی دکمه "OK" کلیک کنید.
2. پنجره بحث شروع می شود. در آن اطلاعات دقیقا همانطور که با استفاده از تابع LINEST انجام می شود وارد می کنیم. روی دکمه "OK" کلیک کنید.
3. نتیجه روند نمایشی محاسبه و نمایش داده شده در سلول نشان داده شده است.
4. علامت "=" را در سلول خالی قرار دهید. براکت ها را باز کنید و سلول هایی را که حاوی مقدار درآمد برای آخرین دوره واقعی هستند، انتخاب کنید. علامت "*" را قرار دهید و سلول حاوی روند نمایشی را انتخاب کنید. ما علامت منفی گذاشتیم و دوباره بر روی عنصر که در آن میزان درآمد برای آخرین دوره قرار گرفته است کلیک کنید. بستن براکت و وارد کردن علامت "* 3 +" بدون نقل قول. دوباره روی همان سلول که در آخرین بار انتخاب شد کلیک کنید. برای انجام محاسبات روی دکمه Enter کلیک کنید .

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

درس: سایر توابع آماری در اکسل

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

• ← ماتریکس انتقال در مایکروسافت اکسل
• محاسبه NPV در مایکروسافت اکسل →