یک روند خطی بسازید. ساخت تابع روند در اکسل. پیش بینی سریع بدون فصلی

اغلب اوقات روند ظاهر می شود وابستگی خطی از مقدار مورد بررسی فرم

که در آن y متغیر مورد مطالعه (به عنوان مثال بهره وری) یا متغیر وابسته است.
x عددی است که موقعیت (دوم، سوم و ...) سال را در دوره پیش بینی یا یک متغیر مستقل تعیین می کند.

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

که در آن n حجم جمعیت مورد مطالعه (تعداد واحدهای مشاهدات) است.

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

مقادیر ثابت b و a یا ضریب متغیر X و جمله آزاد معادله با فرمول تعیین می شود:

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

جدول 5.1. محاسبه روند خطی

روش های صاف کردن ارتعاشات

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

روش «میانگین متحرک» (MSA).

MCC به شما امکان می دهد طیفی از مقادیر را به منظور برجسته کردن یک روند صاف کنید. این روش میانگین (معمولاً میانگین حسابی) تعداد ثابتی از مقادیر را می گیرد. به عنوان مثال، میانگین متحرک سه نقطه ای. اولین سه گانه مقادیر گرفته می شود که از داده های ژانویه، فوریه و مارس (10 + 12 + 13) تشکیل شده است و میانگین برابر با 35: 3 = 11.67 تعیین می شود.

مقدار حاصل از 11.67 در مرکز محدوده قرار می گیرد، یعنی. در خط فوریه سپس یک ماه اسلاید می کنیم و سه عدد دوم را از فوریه تا آوریل (12 + 13 + 16) می گیریم و میانگین را برابر با 41: 3 = 13.67 محاسبه می کنیم و به این ترتیب داده ها را پردازش می کنیم. کل سریال میانگین های حاصل نشان دهنده ردیف جدیدداده های ساخت یک روند و تقریب آن. هر چه امتیازهای بیشتری برای محاسبه میانگین متحرک گرفته شود، هموارسازی نوسانات قوی‌تر رخ می‌دهد. مثالی از MVA ساخت یک روند در جدول آورده شده است. 5.2 و در شکل. 5.4.

جدول 5.2 محاسبه روند با استفاده از روش میانگین متحرک سه نقطه ای

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

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

برنج. 5.4. ماهیت تغییر در فروش بر اساس ماه های سال:
داده های اولیه (ردیف 3)؛ میانگین متحرک (ردیف 4)؛ هموارسازی نمایی (ردیف 2)؛ روند رگرسیون (ردیف 1)

روش هموارسازی نمایی.

یک رویکرد جایگزین برای کاهش گسترش مقادیر سری، استفاده از روش هموارسازی نمایی است. روش "هموارسازی نمایی" نامیده می شود زیرا هر مقدار از دوره هایی که به گذشته می روند با یک ضریب (1 - α) کاهش می یابد.

هر مقدار هموار شده با استفاده از فرمولی مانند:

St =aYt +(1-α)St-1،

جایی که St مقدار هموار فعلی است.
Yt مقدار فعلی سری زمانی است. St - 1 - مقدار هموار قبلی؛ α یک ثابت هموارسازی است، 0 ≤ α ≤ 1.

هرچه مقدار ثابت α کمتر باشد، حساسیت آن نسبت به تغییرات روند در یک سری زمانی معین کمتر است.

مرجع نظری

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

مشکلات تقریب توابع از این نوع اغلب ایجاد می شود:

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

این مقاله قابلیت‌های بسته MS Excel را برای حل چنین مشکلاتی، به علاوه روش‌ها و تکنیک‌های ساخت (ایجاد) رگرسیون برای توابع مشخص شده جدولی (که اساس آن است) مورد بحث قرار می‌دهد. تجزیه و تحلیل رگرسیون).

دو گزینه برای ایجاد رگرسیون در اکسل وجود دارد.

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

افزودن خطوط روند به نمودار

برای جدولی از داده‌ها که یک فرآیند خاص را توصیف می‌کند و با یک نمودار نشان داده می‌شود، اکسل یک ابزار تحلیل رگرسیون موثر دارد که به شما امکان می‌دهد:

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

y = y(x)

که در آن x یک متغیر مستقل است که اغلب مقادیر دنباله‌ای از اعداد طبیعی (1؛ 2؛ 3؛ ...) را می‌گیرد و برای مثال، شمارش معکوس زمان فرآیند مورد مطالعه (ویژگی‌ها) را تولید می‌کند. .

1 . رگرسیون خطی در مدل‌سازی ویژگی‌هایی که با نرخ ثابت افزایش یا کاهش می‌یابند خوب است. این ساده ترین مدل فرآیند مورد مطالعه است. او

y=mx+b

که در آن m مماس شیب است رگرسیون خطیبه محور x؛ ب - مختصات نقطه تقاطع رگرسیون خطی با محور y.

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

در این مورد، خط روند مطابق با معادله ساخته می شود:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

که در آن ضرایب c0, c1, c2,...c6 ثابت هایی هستند که مقادیر آنها در حین ساخت تعیین می شود.

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

مطابق با معادله ساخته شده است:

y = c ln(x) + b

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

مطابق با معادله ساخته شده است:

y = cxb

که در آن ضرایب b و c ثابت هستند.

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

مطابق با معادله ساخته شده است:

y=cebx

که در آن ضرایب b و c ثابت هستند.

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

با فرمول تعیین می شود:

برای افزودن خط روند به یک سری داده:

• نمودار ساخته شده بر اساس سری داده ها را فعال کنید، یعنی در ناحیه نمودار کلیک کنید. مورد نمودار در منوی اصلی ظاهر می شود.
• پس از کلیک بر روی این مورد، منویی روی صفحه ظاهر می شود که در آن باید دستور Add trend line را انتخاب کنید.

اگر ماوس را روی نمودار مربوط به یکی از سری داده ها نگه دارید و راست کلیک کنید، همان اقدامات به راحتی انجام می شود. در ظاهر شد منوی زمینهدستور Add trendline را انتخاب کنید. کادر محاوره‌ای Trendline با باز شدن تب Type روی صفحه ظاهر می‌شود (شکل 1).

پس از آن شما نیاز دارید:

در تب Type، نوع خط روند مورد نیاز را انتخاب کنید (خطی به طور پیش فرض انتخاب شده است). برای نوع Polynomial در قسمت Degree درجه چند جمله ای انتخاب شده را مشخص کنید.

در صورت لزوم با رفتن به تب Parameters (شکل 2) می توانید پارامترهای زیر را برای خط روند تنظیم کنید:

• نام خط روند را در نام فیلد منحنی تقریبی (صاف) تغییر دهید.
• تعداد دوره ها (به جلو یا عقب) را برای پیش بینی در قسمت Forecast تنظیم کنید.
• معادله خط روند را در ناحیه نمودار نمایش دهید، که برای آن باید چک باکس نمایش معادله در نمودار را فعال کنید.
• مقدار قابلیت اطمینان تقریبی R2 را در ناحیه نمودار نمایش دهید، که برای آن باید چک باکس را فعال کنید و مقدار قابلیت اطمینان تقریبی (R^2) را روی نمودار قرار دهید.
• نقطه تقاطع خط روند را با محور Y تنظیم کنید، که برای آن باید چک باکس تقاطع منحنی با محور Y را در یک نقطه فعال کنید.
• روی دکمه OK کلیک کنید تا کادر محاوره ای بسته شود.

کادر محاوره‌ای Format Trendline بر روی صفحه ظاهر می‌شود (شکل 3)، که شامل سه زبانه است: View، Type، Parameters، و محتویات دو مورد آخر کاملاً با زبانه‌های مشابه کادر گفتگوی Trendline مطابقت دارد (شکل 1-2). ). در تب View می توانید نوع خط، رنگ و ضخامت آن را تعیین کنید.

برای حذف یک خط روند از قبل ساخته شده، خط روندی که باید حذف شود را انتخاب کنید و کلید Delete را فشار دهید.

مزایای ابزار تحلیل رگرسیون در نظر گرفته شده عبارتند از:

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

شما نمی توانید خطوط روند را به سری های داده در نمودارهای 3-D، Standard، Radar، Pie و Donut اضافه کنید.

استفاده از توابع داخلی اکسل

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

اکسل چندین توابع برای ایجاد رگرسیون خطی دارد، به ویژه:

• روند؛
• LINEST;
• SLOPE و CUT.

و همچنین چندین تابع برای ساخت یک خط روند نمایی، به ویژه:

• HEIGHT;
• LGRFPحدود

مزایای ابزار توابع داخلی برای تحلیل رگرسیون عبارتند از:

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

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

نمونه هایی از حل مسائل خاص

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

وظیفه 1

با جدولی از داده ها در مورد سود یک شرکت حمل و نقل موتوری برای سال 1995-2002. باید موارد زیر را انجام دهید

1. یک نمودار بسازید.
2. خطوط روند خطی و چند جمله ای (دو و مکعبی) را به نمودار اضافه کنید.
3. با استفاده از معادلات خط روند، داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2004 به دست آورید.
4. برای سال های 2003 و 2004 سود شرکت را پیش بینی کنید.

راه حل مشکل

1. در محدوده سلول های A4:C11 کاربرگ اکسل، کاربرگ نشان داده شده در شکل را وارد می کنیم. 4.
2. با انتخاب محدوده سلول های B4:C11، نموداری می سازیم.
3. نمودار ساخته شده را فعال می کنیم و با استفاده از روشی که در بالا توضیح داده شد، پس از انتخاب نوع خط روند در کادر محاوره ای خط روند (نگاه کنید به شکل 1)، به طور متناوب خطوط روند خطی، درجه دوم و مکعبی را به نمودار اضافه می کنیم. در همان کادر محاوره‌ای، تب پارامترها را باز کنید (شکل 2 را ببینید)، در نام فیلد منحنی تقریبی (صاف) نام روندی که باید اضافه شود را وارد کنید و در قسمت Forecast forward for: periods را تنظیم کنید. مقدار 2، زیرا قرار است پیش بینی سود برای دو سال آینده انجام شود. برای نمایش معادله رگرسیون و مقدار قابلیت اطمینان تقریبی R2 در ناحیه نمودار، کادرهای بررسی Show the equation on the screen را فعال کنید و مقدار قابلیت اطمینان تقریبی (R^2) را روی نمودار قرار دهید. برای درک بصری بهتر، نوع، رنگ و ضخامت خطوط روند رسم شده را تغییر می دهیم، که برای آن از تب View در کادر محاوره ای Trend Line Format استفاده می کنیم (شکل 3 را ببینید). نمودار حاصل با خطوط روند اضافه شده در شکل نشان داده شده است. 5.
4. برای به دست آوردن داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2004. بیایید از معادلات خطوط روند ارائه شده در شکل استفاده کنیم. 5. برای این کار در سلول های محدوده D3: F3 وارد کنید اطلاعات متنیدر مورد نوع خط روند انتخابی: روند خطی، روند چهارگانه، روند مکعبی. سپس فرمول رگرسیون خطی را در سلول D4 وارد کرده و با استفاده از نشانگر پر، این فرمول c را کپی کنید پیوندهای نسبیبه محدوده سلول های D5:D13. لازم به ذکر است که هر سلول با فرمول رگرسیون خطی از محدوده سلول های D4:D13 دارای یک سلول مربوطه از محدوده A4:A13 به عنوان آرگومان است. به طور مشابه، برای رگرسیون درجه دوم، محدوده سلولی E4:E13 و برای رگرسیون مکعبی، محدوده سلولی F4:F13 پر می شود. بنابراین پیش‌بینی سود شرکت برای سال‌های 2003 و 2004 انجام شد. با سه گرایش جدول مقادیر به دست آمده در شکل نشان داده شده است. 6.

وظیفه 2

1. یک نمودار بسازید.
2. خطوط روند لگاریتمی، نمایی و نمایی را به نمودار اضافه کنید.
3. معادلات خطوط روند به دست آمده و همچنین مقادیر قابلیت اطمینان تقریبی R2 را برای هر یک از آنها استخراج کنید.
4. با استفاده از معادلات خط روند، داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2002 به دست آورید.
5. با استفاده از این خطوط روند، سود سال های 2003 و 2004 را برای کسب و کار پیش بینی کنید.

راه حل مشکل

با پیروی از روش ارائه شده در حل مسئله 1، نموداری را با خطوط روند لگاریتمی، نمایی و نمایی اضافه می کنیم (شکل 7). علاوه بر این، با استفاده از معادلات خط روند به‌دست‌آمده، جدول مقادیر سود شرکت را شامل مقادیر پیش‌بینی‌شده برای سال‌های 2003 و 2004 پر می‌کنیم. (شکل 8).

روی انجیر 5 و شکل مشاهده می شود که مدل با روند لگاریتمی با کمترین مقدار پایایی تقریبی مطابقت دارد.

R2 = 0.8659

بالاترین مقادیر R2 مربوط به مدل هایی با روند چند جمله ای است: درجه دوم (R2 = 0.9263) و مکعب (R2 = 0.933).

وظیفه 3

با جدول داده‌های سود یک شرکت حمل و نقل موتوری برای سال‌های 1995-2002، که در کار 1 ارائه شده است، باید مراحل زیر را انجام دهید.

1. با استفاده از توابع TREND و GROW سری داده ها را برای خطوط روند خطی و نمایی دریافت کنید.
2. با استفاده از توابع TREND و GROWTH، برای سال 2003 و 2004 سود شرکت را پیش بینی کنید.
3. برای داده های اولیه و سری داده های دریافتی، یک نمودار بسازید.

راه حل مشکل

بیایید از کاربرگ وظیفه 1 استفاده کنیم (شکل 4 را ببینید). بیایید با تابع TREND شروع کنیم:

1. محدوده سلول های D4:D11 را انتخاب کنید که باید با مقادیر تابع TREND مربوط به داده های شناخته شده در مورد سود شرکت پر شود.
2. دستور Function را از منوی Insert فراخوانی کنید. در کادر محاوره‌ای Function Wizard که ظاهر می‌شود، تابع TREND را از دسته Statistical انتخاب کنید و سپس روی دکمه OK کلیک کنید. همین عملیات را می توان با فشار دادن دکمه (عملکرد Insert) انجام داد. پنل استانداردابزار.
3. در کادر محاوره‌ای Function Arguments که ظاهر می‌شود، محدوده سلول‌های C4:C11 را در قسمت Known_values_y وارد کنید. در قسمت Known_values_x - محدوده سلول های B4:B11;
4. برای تبدیل فرمول وارد شده به فرمول آرایه از کلید ترکیبی + + استفاده کنید.

در نتیجه، محدوده سلول های D4:D11 با مقادیر مربوط به تابع TREND پر می شود (شکل 9).

1. محدوده سلول های D12:D13 را انتخاب کنید، جایی که مقادیر پیش بینی شده توسط تابع TREND وارد می شود.
2. تابع TREND را فراخوانی کنید و در کادر محاوره ای Function Arguments که ظاهر می شود، در قسمت Known_values_y - محدوده سلول های C4:C11 را وارد کنید. در قسمت Known_values_x - محدوده سلول های B4:B11; و در فیلد New_values_x - محدوده سلول های B12:B13.
3. این فرمول را با استفاده از ترکیب به فرمول آرایه تبدیل کنید کلیدهای Ctrl+ Shift + Enter.
4. فرمول وارد شده به صورت زیر خواهد بود: =(TREND(C4:C11;B4:B11;B12:B13))، و محدوده سلول های D12:D13 با مقادیر پیش بینی شده تابع TREND پر می شود (شکل 1 را ببینید). 9).

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

شکل 10 جدول را در حالت نمایش فرمول نشان می دهد.

برای داده های اولیه و سری داده های به دست آمده، نمودار نشان داده شده در شکل. یازده

وظیفه 4

با جدولی از داده ها در مورد دریافت برنامه های خدمات توسط سرویس اعزام یک شرکت حمل و نقل موتوری برای دوره 1 تا 11 ماه جاریباید موارد زیر را انجام دهید

1. دریافت سری داده ها برای رگرسیون خطی:با استفاده از توابع SLOPE و INTERCEPT. با استفاده از تابع LINEST
2. یک سری داده برای رگرسیون نماییبا استفاده از تابع LGRFPRIB
3. با استفاده از توابع فوق، پیش بینی دریافت درخواست ها به سرویس اعزام را برای بازه زمانی دوازدهم تا چهاردهم ماه جاری انجام دهید.
4. برای سری داده های اصلی و دریافتی، یک نمودار بسازید.

راه حل مشکل

توجه داشته باشید که برخلاف توابع TREND و GROW، هیچ یک از توابع ذکر شده در بالا (SLOPE، INTERCEPTION، LINEST، LGRFPRIB) رگرسیون نیستند. این توابع فقط نقش کمکی ایفا می کنند و پارامترهای رگرسیون لازم را تعیین می کنند.

برای رگرسیون های خطی و نمایی ساخته شده با استفاده از توابع SLOPE، INTERCEPT، LINEST، LGRFPRIB، ظاهرمعادلات آنها بر خلاف رگرسیون های خطی و نمایی مربوط به توابع TREND و GROWTH همیشه شناخته شده است.

1 . بیایید یک رگرسیون خطی بسازیم که دارای معادله باشد:

y=mx+b

برای انجام این کار، اقدامات زیر را انجام می دهیم:

1. جدول منبع را در محدوده سلول های A4:B14 وارد کنید.
2. مقدار پارامتر m در سلول C19 تعیین می شود. از دسته آماری تابع Slope را انتخاب کنید. محدوده سلول‌های B4:B14 را در قسمت Known_values_y و محدوده سلول‌های A4:A14 را در قسمت Known_values_x وارد کنید. فرمول در سلول C19 وارد می شود: =SLOPE(B4:B14;A4:A14);
3. با استفاده از روش مشابه، مقدار پارامتر b در سلول D19 تعیین می شود. و محتوای آن به این صورت خواهد بود: = INTERCEPT(B4:B14;A4:A14).بنابراین، مقادیر پارامترهای m و b لازم برای ساخت یک رگرسیون خطی، به ترتیب در سلول‌های C19، D19 ذخیره می‌شوند.
4. سپس فرمول رگرسیون خطی را در سلول C4 به شکل = $ C * A4 + $ D وارد می کنیم. در این فرمول، سلول های C19 و D19 با ارجاع مطلق نوشته می شوند (آدرس سلول نباید با کپی کردن احتمالی تغییر کند). علامت مرجع مطلق $ را می توان پس از قرار دادن مکان نما روی آدرس سلول یا از صفحه کلید یا با استفاده از کلید F4 تایپ کرد. با استفاده از دسته پر، این فرمول را در محدوده سلول های C4:C17 کپی کنید. سری داده های مورد نظر را بدست می آوریم (شکل 12). با توجه به اینکه تعداد درخواست ها یک عدد صحیح است، باید فرمت اعداد را در تب Number پنجره Cell Format با تعداد ارقام اعشار روی 0 قرار دهید.

y=mx+b

برای این:

1. تابع LINEST را به عنوان یک فرمول آرایه در محدوده سلول های C20:D20: =(LINEST(B4:B14;A4:A14) وارد کنید. در نتیجه، مقدار پارامتر m را در سلول C20 و مقدار پارامتر b را در سلول D20 دریافت می کنیم.
2. فرمول را در سلول D4 وارد کنید: =$C*A4+$D;
3. این فرمول را با استفاده از نشانگر پر در محدوده سلول های D4:D17 کپی کنید و سری داده های مورد نظر را دریافت کنید.

y=bmx

روی انجیر 13 جدولی را نشان می دهد که در آن می توانیم توابعی را که با محدوده سلولی لازم استفاده می کنیم و همچنین فرمول ها را مشاهده کنیم.

برای داده های اولیه و سری داده های به دست آمده، نمودار نشان داده شده در شکل. 14.

اگر اندازه‌گیری زمان‌بندی برای حجم/اندازه‌های خاصی از محصولات وجود نداشته باشد، چه باید کرد؟ یا اینکه تعداد اندازه گیری ها کافی نیست و نمی توان در آینده نزدیک مشاهدات اضافی انجام داد؟ بهترین راهراه حل این مشکل ساخت وابستگی های محاسبه شده (معادلات رگرسیون) با استفاده از خطوط روند در MS Excel است.

یک وضعیت واقعی را در نظر بگیرید: در یک انبار، به منظور تعیین میزان هزینه های نیروی کار برای چیدن یک سفارش، مشاهدات زمان. نتایج این مشاهدات در جدول 1 در زیر ارائه شده است.

پس از آن، تعیین زمان صرف شده برای انتخاب 0.6 و 0.9 متر مکعب کالا/سفارش ضروری شد. با توجه به عدم امکان انجام مطالعات زمان سنجی اضافی، زمان صرف شده برای انتخاب این حجم های سفارش با استفاده از معادلات رگرسیون در MS Excel محاسبه شد. برای این کار جدول 1 به جدول 2 تبدیل شد.

انتخاب نمودار پراکندگی، شکل. 1

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

اضافه کردن یک خط روند، شکل. 2

در پنجره تنظیم فرمت خط روند ظاهر شد (شکل 3)به ترتیب انتخاب شدند: نوع خط خطی/قانون قدرت است و چک باکس ها برای موارد زیر تنظیم شده اند: "معادله را روی نمودار نشان دهید" و "مقدار اطمینان تقریبی را روی نمودار قرار دهید (R^2)" (ضریب تعیین).

فرمت خط روند، شکل. 3

در نتیجه، نمودارهای ارائه شده در شکل. 4 و 5.

وابستگی محاسبه شده خطی، شکل. 4

وابستگی محاسبه شده توان، شکل. 5

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

بیایید به عنوان اصلی در نظر بگیریم - یک وابستگی محاسبه شده خطی. سپس مقادیر زمان صرف شده بسته به مقدار کالا با فرمول تعیین می شود: y = 54.511x + 0.1489.نتایج این محاسبات برای مقدار کالایی که قبلاً مشاهدات زمانی برای آنها انجام شده است، در جدول 3 زیر ارائه شده است.

اجازه دهید میانگین انحراف هزینه های زمانی محاسبه شده توسط معادله رگرسیون را از هزینه های زمانی محاسبه شده از داده های مشاهدات زمان سنجی تعیین کنیم: (0.05-0.10-0.05+0.01)/4=0.0019. بنابراین، هزینه های زمانی محاسبه شده بر اساس معادله رگرسیون با هزینه های زمانی محاسبه شده بر اساس داده های مشاهدات زمان سنجی تنها توسط 0,19%. اختلاف داده ها ناچیز است.

با توجه به فرمول: y = 54.511x + 0.1489، ما هزینه های زمانی را برای مقدار کالاهایی که قبلا مشاهدات زمانی برای آنها انجام نشده است تعیین می کنیم. (جدول 4).

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

• گلبرگ
• گرد؛
• سطح؛
• حلقوی
• جلد؛
• با تجمع

تقریب خطی

نتیجه را می گیریم:

y = 4.503x + 6.1333

• 6.1333 - افست.
• x عدد دوره است.

y = 7.6403е^-0.084x

مثلا:

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

خط روند در اکسل

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

توطئه

برای ساختن یک نمودار، باید یک جدول آماده داشته باشید که بر اساس آن شکل بگیرد. به عنوان مثال، بیایید داده هایی را در مورد ارزش دلار به روبل برای یک دوره زمانی مشخص در نظر بگیریم.

1. ما در حال ساخت جدولی هستیم که در آن یک ستون شامل دوره های زمانی (در مورد ما، تاریخ ها) و ستون دیگر حاوی مقداری است که پویایی آن در نمودار نمایش داده می شود.
2. این جدول را انتخاب کنید. به تب "Insert" بروید. در آنجا، روی نوار در بلوک ابزار "نمودارها"، روی دکمه "گراف" کلیک کنید. از لیست ارائه شده، اولین گزینه را انتخاب کنید.
3. پس از آن، برنامه زمان بندی ساخته می شود، اما هنوز باید نهایی شود. ساخت عنوان نمودار. برای این کار روی آن کلیک کنید. در گروه برگه های «کار با نمودارها» که ظاهر می شود، به برگه «طرح بندی» بروید. در آن، روی دکمه "نام نمودار" کلیک کنید. در لیستی که باز می شود، مورد "در بالای نمودار" را انتخاب کنید.
4. در قسمتی که بالای نمودار ظاهر می شود، نامی را که مناسب می دانیم وارد کنید.
5. سپس محورها را امضا می کنیم. در همان تب Layout، روی دکمه روی نوار "Axis Names" کلیک کنید. ما به ترتیب موارد "نام محور افقی اصلی" و "نام زیر محور" را مرور می کنیم.
6. در فیلدی که ظاهر می شود، نام محور افقی را با توجه به زمینه داده های موجود در آن وارد کنید.
7. برای اختصاص یک نام به محور عمودی، از تب "Layout" نیز استفاده می کنیم. روی دکمه "Axis Names" کلیک کنید. به صورت متوالی در میان آیتم های منوی بازشو "نام محور عمودی اصلی" و "عنوان چرخشی" حرکت کنید. این نوع مکان نام محور است که برای نوع نمودارهای ما راحت تر خواهد بود.
8. در قسمت ظاهر شده برای نام محور عمودی، نام مورد نظر را وارد کنید.

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

ایجاد خط روند

اکنون باید مستقیماً خط روند را اضافه کنید.

1. با قرار گرفتن در تب "Layout"، روی دکمه "Trend Line" که در بلوک ابزار "Analysis" قرار دارد، کلیک کنید. از لیستی که باز می شود، مورد "تقریبا نمایی" یا "تقریبا خطی" را انتخاب کنید.
2. پس از آن، یک خط روند به نمودار اضافه می شود. به طور پیش فرض سیاه است.

تنظیم خط روند

احتمالش وجود داره تنظیمات اضافیخطوط

1. به طور متوالی به تب "Layout" در آیتم های منو "Analysis"، "Trend line" و " گزینه های اضافیخطوط روند ...
2. پنجره پارامتر باز می شود و می توان تنظیمات مختلفی را انجام داد. به عنوان مثال، می توانید با انتخاب یکی از شش گزینه، نوع صاف کردن و نصب را تغییر دهید:
   • چند جمله ای
   • خطی؛
   • قدرت؛
   • لگاریتمی؛
   • نمایی;
   • فیلتر خطی

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

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

اگر از سطح اطمینان راضی نیستید، می توانید به پارامترها برگردید و نوع هموارسازی و تقریب را تغییر دهید. سپس دوباره ضریب را ایجاد کنید.

پیش بینی

وظیفه اصلی خط روند، توانایی پیش بینی پیشرفت های بعدی در آن است.

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

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

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

سوال خود را در نظرات بپرسید و ماهیت مشکل را با جزئیات شرح دهید. کارشناسان ما سعی خواهند کرد در اسرع وقت پاسخ دهند.

آیا این مقاله به شما کمک کرد؟

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

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

چگونه نمودارها و نمودارها ما را فریب می دهند

با این حال، نمودارها (مخصوصاً وقتی صحبت از نمایش بصری مقدار زیادی داده می شود)، اگرچه برای درک بسیار راحت است، اما همیشه واضح نیستند.

بگذارید با یک مثال ساده نظرم را توضیح دهم:

نمودار بر اساس جدول در MS Excel

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

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

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

آیا نمودار واضح است؟ کاملا. اما آیا واضح است؟ بیایید آن را بفهمیم.

درک روندها در MS Excel

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

در نمودار چه چیزی می بینیم؟ ستون های نارنجی «پاییز» حداقل «بهار» بیشتر نیستند، یا حتی کمتر. این نشان دهنده موفقیت نیست، بلکه برعکس است - بازدیدکنندگان وارد می شوند، اما به طور متوسط ​​کمتر مطالعه می کنند و در سایت معطل نمی شوند!

وقت آن است که زنگ خطر را به صدا در آوریم و ... با چیزی مانند خط روند.

چرا به خط روند نیاز دارید؟

خط روند "به روش ساده" یک خط پیوسته است که بر اساس مقادیر متوسط ​​​​بر اساس الگوریتم های خاصی که نمودار ما از آنها ساخته شده است ، جمع آوری شده است. به عبارت دیگر، اگر داده‌های ما از سه نقطه گزارش از «-5» به «0» و سپس به «5+» بپرد، در پایان یک خط تقریباً صاف خواهیم داشت: «مضافات» وضعیت. بدیهی است که "معایب" را متعادل کنید.

بر اساس جهت خط روند، دیدن آن بسیار ساده تر است موقعیت واقعیموارد و همین روندها را ببینید، و بنابراین - برای آینده پیش بینی کنید. حالا دست به کار شوید!

نحوه رسم خط روند در MS Excel

اضافه کردن به نمودار در MS خط اکسلروند

بر روی یکی از ستون های "آبی" کلیک راست کرده و مورد را در منوی زمینه انتخاب کنید "افزودن خط روند".

اکنون صفحه نمودار نمایش داده می شود خط نقطه چینروند. همانطور که می بینید، 100٪ با مقادیر نمودار منطبق نیست - بر اساس میانگین وزنی ساخته شده است، فقط به طور کلی جهت خود را تکرار می کند. با این حال، این مانع از آن نمی شود که شاهد افزایش مداوم تعداد بازدیدکنندگان از سایت باشیم - حتی کاهش "تابستانی" بر نتیجه کلی تأثیر نمی گذارد.

خط روند برای ستون "بازدید کنندگان".

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

خط روند دیگری به شما امکان می دهد وضعیت را روشن کنید

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

بنابراین، صاحب پروژه باید فوراً آنچه را که در تابستان امسال انجام داده است به یاد بیاورد ("در بهار"، با قضاوت بر اساس برنامه، همه چیز کاملاً عادی بود) و فوراً اقداماتی را برای اصلاح وضعیت انجام دهد.

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

خط روند در اکسل. فرآیند ساخت

خط روند یکی از ابزارهای اصلی تجزیه و تحلیل داده ها است.

برای تشکیل یک خط روند، لازم است سه مرحله را تکمیل کنید، یعنی:
1. یک جدول ایجاد کنید.
2. یک نمودار بسازید.
3. نوع خط روند را انتخاب کنید.

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

اولین مرحله ایجاد جدول با داده های اصلی است. سپس محدوده مورد نیاز را انتخاب کرده و با رفتن به تب "Insert" تابع "Chart" را انتخاب کنید. پس از ساخت، نتیجه نهایی را می توان اعمال کرد ویژگی های اضافی، به صورت سربرگ و همچنین امضا. برای این کار کافیست بر روی نمودار کلیک چپ کرده، تبی به نام "Constructor" را انتخاب کرده و "Layout" را انتخاب کنید. مرحله بعدی این است که به سادگی عنوان را وارد کنید.

قدم بعدی ساخت خط روند خود است. بنابراین، برای این کار، باید دوباره نمودار را انتخاب کنید و برگه "Layout" را در نوار وظیفه انتخاب کنید. در مرحله بعد، در این منو، باید روی دکمه «خط روند» کلیک کنید و «تقریبا خطی» یا «تقریبا نمایی» را انتخاب کنید.

تغییرات مختلف lخطوط روند

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

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

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

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

تقریب توان افزایش و کاهش یکنواخت یک مقدار را مشخص می کند، اما اگر داده ها دارای مقادیر منفی و صفر باشند، کاربرد آن غیرممکن است.

میانگین متحرک برای نشان دادن بصری وابستگی مستقیم یکی به دیگری، با صاف کردن تمام نقاط نوسان استفاده می شود. این با برجسته کردن مقدار متوسط ​​بین دو نقطه مجاور به دست می آید. بنابراین، نمودار میانگین می شود و تعداد نقاط به مقداری که در منوی "نقاط" توسط کاربر انتخاب شده بود کاهش می یابد.

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

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

خط روند در اکسل. تنظیم پارامتردر خط عملکردی

با کلیک بر روی دکمه "Trend Line" منوی مورد نیاز به نام "Advanced Options" را انتخاب کنید. در پنجره ای که ظاهر می شود، روی "Trend Line Format" کلیک کنید و سپس یک علامت را در مقابل مقدار "put on chart the value of approximation reliability R ^ 2" قرار دهید. پس از آن، منو را با کلیک بر روی دکمه مربوطه ببندید. ضریب R^2= 0.6442 در خود نمودار ظاهر می شود.

پس از آن تغییرات معرفی شده را لغو می کنیم. پس از انتخاب نمودار و کلیک بر روی تب "Layout"، سپس روی "Trend Line" کلیک کرده و روی "None" کلیک کنید. در مرحله بعد، با رفتن به تابع "Trend Line Format"، روی خط چند جمله ای کلیک کنید و سعی کنید با تغییر درجه به مقدار R ^ 2 = 0.8321 برسید.

برای مشاهده فرمول‌ها یا ساختن فرمول‌های متفاوت، متفاوت از تغییرات استاندارد پیش‌بینی‌ها، کافی است از آزمایش مقادیر و به‌ویژه چند جمله‌ای‌ها نترسید. بنابراین، با استفاده از تنها یک برنامه اکسل، می توانید یک پیش بینی نسبتاً دقیق بر اساس داده های ورودی ایجاد کنید.

(بازدید شده 10 510 بار، 27 بازدید امروز)

خط روند در اکسل در نمودارهای مختلف

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

بیایید ببینیم چگونه یک خط روند را به نمودار در اکسل اضافه کنیم.

افزودن خط روند به نمودار

به عنوان مثال، بیایید میانگین قیمت نفت از سال 2000 را از منابع باز در نظر بگیریم. داده ها را برای تجزیه و تحلیل در جدول وارد می کنیم:

1. بیایید یک نمودار بر اساس جدول بسازیم. محدوده را انتخاب کنید - به برگه "درج" بروید. از بین انواع نمودارهای پیشنهادی، یک نمودار ساده را انتخاب می کنیم. افقی - سال، عمودی - قیمت.
2. روی خود نمودار کلیک راست کنید. روی «افزودن خط روند» کلیک کنید.
3. پنجره ای برای پیکربندی پارامترهای خط باز می شود. بیایید نوع خطی را انتخاب کنیم و مقدار قابلیت اطمینان تقریبی را روی نمودار قرار دهیم.
4. یک خط اریب روی نمودار ظاهر می شود.

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

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

توجه!!! خط روند نمی تواند اضافه شود انواع زیرنمودارها و نمودارها:

• گلبرگ
• گرد؛
• سطح؛
• حلقوی
• جلد؛
• با تجمع

معادله خط روند در اکسل

در مثال بالا، یک تقریب خطی فقط برای نشان دادن الگوریتم انتخاب شد. همانطور که توسط مقدار قابلیت اطمینان نشان داده شده است، انتخاب کاملاً موفق نبود.

شما باید نوع نمایشگری را انتخاب کنید که با دقت بیشتری روند ورودی کاربر را نشان می دهد. بیایید نگاهی به گزینه ها بیندازیم.

تقریب خطی

نمایش هندسی آن یک خط مستقیم است. بنابراین، از یک تقریب خطی برای نشان دادن شاخصی استفاده می‌شود که با یک نرخ ثابت افزایش یا کاهش می‌یابد.

تعداد مشروط قراردادهای منعقد شده توسط مدیر برای 10 ماه را در نظر بگیرید:

بر اساس داده های موجود در صفحه گسترده اکسلبیایید یک نمودار پراکنده بسازیم (این به نشان دادن نوع خطی کمک می کند):

نمودار را انتخاب کنید - "افزودن یک خط روند". در پارامترها، نوع خطی را انتخاب کنید. ما مقدار قابلیت اطمینان تقریبی و معادله خط روند را در اکسل اضافه می کنیم (فقط کادرهای پایین پنجره "پارامترها" را علامت بزنید).

نتیجه را می گیریم:

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

y = 4.503x + 6.1333

• که در آن 4.503 نشانگر شیب است.
• 6.1333 - افست.
• y دنباله ای از مقادیر است،
• x عدد دوره است.

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

برای پیش بینی تعداد قراردادهای منعقد شده، به عنوان مثال، در دوره یازدهم، باید عدد 11 را به جای x در معادله جایگزین کنید. در جریان محاسبات متوجه می شویم که در دوره یازدهم این مدیر 55 تا 56 قرارداد منعقد خواهد کرد.

خط روند نمایی

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

بیایید یک خط روند نمایی در اکسل بسازیم. بیایید به عنوان مثال مقادیر شرطی عرضه مفید برق در منطقه X را در نظر بگیریم:

نمودار می سازیم. یک خط نمایی اضافه کنید.

معادله به شکل زیر است:

y = 7.6403е^-0.084x

• که در آن 7.6403 و -0.084 ثابت هستند.
• e پایه لگاریتم طبیعی است.

شاخص قابلیت اطمینان تقریبی 0.938 بود - منحنی با داده ها مطابقت دارد، خطا حداقل است، پیش بینی ها دقیق خواهند بود.

لاگین Trendline در اکسل

برای تغییرات زیر در شاخص استفاده می شود: اول، افزایش یا کاهش سریع، سپس ثبات نسبی. منحنی بهینه شده به خوبی با این "رفتار" کمیت سازگار است. روند لگاریتمی برای پیش بینی فروش محصول جدیدی که به تازگی به بازار معرفی می شود مناسب است.

بر مرحله اولیهوظیفه سازنده افزایش مشتری است. هنگامی که محصول خریدار خود را دارد، باید آن را حفظ کرد، ارائه کرد.

بیایید یک نمودار بسازیم و یک خط روند لگاریتمی اضافه کنیم تا فروش یک محصول مشروط را پیش بینی کنیم:

R2 از نظر مقدار نزدیک به 1 (0.9633) است که نشان دهنده حداقل خطای تقریب است. ما حجم فروش را در دوره های بعدی پیش بینی خواهیم کرد. برای این کار باید عدد دوره را به جای x جایگزین کنید.

مثلا:

برای محاسبه ارقام پیش بینی شده از فرمول زیر استفاده شده است: =272.14*LN(B18)+287.21. جایی که B18 عدد دوره است.

خط روند چند جمله ای در اکسل

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

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

برای به دست آوردن چنین مقداری از قابلیت اطمینان تقریبی (0.9256)، مجبور شدم درجه 6 را قرار دهم.

دانلود نمونه نمودارها با خط روند

اما چنین روندی به شما امکان می دهد پیش بینی های کم و بیش دقیقی داشته باشید.

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

1) روند چیست (این به عنوان نقطه شروع مهم است)
2) ایجاد خطوط روند
3) در تجارت عملی استفاده کنید
4) موضوعیت روش

1) روند چیست
_________________
قبل از اقدام به ساخت خط روند، لازم است مستقیماً با خود روند برخورد کنید. ما وارد بحث های آکادمیک نمی شویم و برای سادگی فرمول زیر را می پذیریم:

روند (به سمت بالا) دنباله ای از بالا و پایین های صعودی است که هر بالا (و پایین) بعدی بالاتر از موارد قبلی است.

روند (نزولی) دنباله ای از نزولی (کاهش) اوج و پایین است، که در آن هر پایین (و بالا) بعدی کمتر از قبلی است.

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

2) ایجاد خطوط روند
____________________________

این سخت ترین سوال است! من برای بسیاری از صفحات بحث هایی را فقط در مورد نحوه ترسیم یک خط روند به درستی دیده ام! اما ما نه تنها نیاز به ساخت، بلکه به تجارت بر روی آن داریم ...

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

رعایت قوانین زیر هنگام ترسیم خطوط مهم است:

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

این قوانین به شما کمک می کند خطوط روند را به درستی بسازید!

3) با خطوط روند معامله کنید
____________________________

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

هر دو روش خوب هستند اگر بدانید چگونه آنها را به درستی بپزید.

بنابراین، ما یک خط بر روی دو نقطه ایجاد کرده ایم. به محض اینکه قیمت به خط رسید، باید در جهت روند موجود وارد بازار شویم. برای ورود، از سفارش‌هایی از نوع «محدودیت خرید یا حد فروش» استفاده می‌کنیم.

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

اگر می‌خواهیم روی شکستن خط روند بازی کنیم، باید کمی متفاوت عمل کنیم:
1) منتظر لمس خط هستیم
2) ما منتظر یک ریباند هستیم
3) یک سفارش خرید توقف (یا توقف فروش) را در کادر انتخاب قرار دهید
به نقاشی توجه کنید.

ما منتظر ماندیم تا علامت چک ظاهر شود و یک سفارش توقف خرید را در حداکثر آن قرار دادیم.

بعد از مدتی سفارش عملی شد و وارد بازار شدیم.
ناشی می شود سوال مشروع- چرا ورود فوری به بازار غیرممکن بود؟
مسئله این است که ما نمی دانیم که آیا تست خط روند موفقیت آمیز خواهد بود یا خیر. و با منتظر ماندن برای "تیک"، ما به طور چشمگیری شانس موفقیت خود را افزایش می دهیم (سیگنال های نادرست را حذف می کنیم).

4) موضوعی بودن روش
_________________________

همه چیز ساده به نظر می رسد؟ در واقع با استفاده از این روش، با مشکلات زیر روبرو خواهیم شد:
الف) شیب خط (شما همیشه می توانید خطوط روند را با شیب های مختلف ترسیم کنید.
ب) چه چیزی شکست خط روند در نظر گرفته می شود (قیمت با چند نقطه یا درصد باید خط را بشکند تا این یک شکست در نظر گرفته شود)؟
ج) چه زمانی یک خط باید «منسوخ» در نظر گرفته شود و خط جدید ساخته شود؟

به نقاشی توجه کنید.

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

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

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

آیا در معاملات خود به این ابزار نیاز دارید - این به شما بستگی دارد!

موفق باشید و تجارت مبارک. آرتور
blog-forex.org

پست های مرتبط:

مفهوم تجارت روند (ویدئو)

الگوهای روند (شکل ها)

ویدیو برای این موضوع:

قسمت 10. انتخاب فرمول ها طبق برنامه زمانبندی. خط روند

برای مسائل در نظر گرفته شده در بالا، امکان ساخت یک معادله یا یک سیستم معادلات وجود داشت.

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

فرآیند انتخاب فرمول تجربی P(x)برای اعتیاد با تجربه F(x)تماس گرفت تقریب(صاف کردن). برای وابستگی‌هایی با یک مجهول، اکسل از نمودارها و برای وابستگی‌هایی با مجهولات زیاد، از جفت توابع از گروه استفاده می‌کند. آماری LINEST و TREND، LGRFP و GROWTH.

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

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

اکسل 5 نوع توابع تقریبی را ارائه می دهد:

1. خطی - y=cx+b. این ساده ترین عملکرد، که رشد و کاهش داده ها را با نرخ ثابتی منعکس می کند.

2. چند جمله ای – y=c0+c1x+c2x2+…+c6x6. تابع به طور متناوب افزایش و کاهش داده ها را توصیف می کند. یک چند جمله ای درجه 2 می تواند یک انتها داشته باشد (حداقل یا حداکثر)، یک چند جمله ای درجه 3 می تواند تا 2 منتهی داشته باشد، یک چند جمله ای درجه 4 می تواند تا 3 منتهی داشته باشد و غیره.

3. لگاریتمی - y=cلوگاریتم x+b. این تابع داده های به سرعت در حال افزایش (کاهش) را توصیف می کند که سپس تثبیت می شود.

4. توان - y=cxb, (ایکس> 0 و y> 0). این تابع داده ها را با نرخ رشد دائمی در حال افزایش (کاهش) نمایش می دهد.

5. نمایی – y=cebx, (هپایه لگاریتم طبیعی است). این تابع داده‌های در حال رشد (کاهش) سریع را توصیف می‌کند که سپس تثبیت می‌شود.

برای هر 5 نوع توابع، تقریب داده ها با روش حداقل مربعات استفاده می شود (به راهنمای F1 "خط روند" مراجعه کنید).

مانند مثالوابستگی فروش به تبلیغات را در نظر بگیرید که توسط آمار زیر برای یک شرکت خاص ارائه شده است:

ما باید یک تابع بسازیم بهترین راهمنعکس کننده این رابطه علاوه بر این، لازم است فروش برای سرمایه گذاری های تبلیغاتی 6 هزار روبل برآورد شود.

بریم سراغ راه حل. اول از همه، این داده ها را وارد اکسل کنید و یک نمودار بسازید، مانند شکل. 38. همانطور که می بینید، نمودار بر اساس محدوده B2:J2 است. در مرحله بعد، با کلیک راست بر روی نمودار، یک خط روند اضافه کنید، همانطور که در شکل نشان داده شده است. 38.

برای برچسب زدن محور X با مقادیر تبلیغاتی مربوطه (مانند شکل 38)، مورد را در منوی کشویی انتخاب کنید (شکل 38). وداده های مشابه. در پنجره باز شده به همین نام، در تب ردیف، در زمینه پبرچسب های محور x، محدوده سلول هایی را که مقادیر X نوشته شده است مشخص کنید (اینجا $B$1:$K$1):

در پنجره تنظیمات که باز می شود (شکل 39)، در زبانه تایپ کنیدبرای تقریب انتخاب کنید لگاریتمیخط روند (با توجه به نوع نمودار). نشانک گزینه هاکادرهایی را که معادله و ضریب تعیین را روی نمودار نشان می دهند علامت بزنید.

پس از فشار دادن خوبنتیجه ای مانند شکل بدست خواهید آورد. 40. ضریب تعیین R2= 0.9846 که درجه خوبی از نزدیکی است. برای تأیید صحت عملکرد انتخاب شده (از آنجایی که ملاحظات نظری دیگری وجود ندارد)، توسعه فروش را برای 10 دوره آینده پیش بینی کنید. برای این کار روی خط روند - تغییر فرمت - پس از آن در فیلد راست کلیک کنید پیش بینی: فوروارد توسط:مجموعه 10 (شکل

پس از تنظیم پیش بینی، تغییر منحنی نمودار را برای 10 دوره مشاهده پیش رو خواهید دید، مانند شکل. 42. به احتمال زیاد افزایش بیشتر در فروش را با افزایش سرمایه گذاری های تبلیغاتی منعکس می کند.

محاسبه طبق فرمول به دست آمده \u003d 237.96 * LN (6) + 5.9606 در اکسل مقدار 432 هزار روبل می دهد.

اکسل دارای یک تابع FORECAST () است که یک مقدار Y آینده را از جفت مقادیر X و Y موجود با استفاده از رگرسیون خطی محاسبه می کند. تابع Y باید تا حد امکان خطی باشد، یعنی. با یک معادله از نوع توصیف شود c+bx.

تابع پیش بینی برای مثال ما این خواهد بود: =PREDICTION(K1;B2:J2;B1:J1). آن را بنویسید - باید ارزش 643.6 هزار روبل دریافت کنید.

قسمت 11. کنترل وظایف

قبلی12345678910111213141516بعدی

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

خط روند در اکسل. فرآیند ساخت

خط روند یکی از ابزارهای اصلی تجزیه و تحلیل داده ها است.

برای تشکیل یک خط روند، باید سه مرحله را تکمیل کنید، یعنی:
1. یک جدول ایجاد کنید.
2.
3. نوع خط روند را انتخاب کنید.

پس از جمع آوری تمامی اطلاعات لازم، می توانید مستقیماً نسبت به اجرای مراحل در جهت حصول نتیجه نهایی اقدام نمایید.

اولین مرحله ایجاد جدول با داده های اصلی است. سپس محدوده مورد نیاز را انتخاب کرده و با رفتن به تب "Insert" تابع "Chart" را انتخاب کنید. پس از ساخت، ویژگی های اضافی را می توان در نتیجه نهایی، به صورت سرفصل ها و همچنین امضاها، اعمال کرد. برای این کار کافیست بر روی نمودار کلیک چپ کرده، تبی به نام "Constructor" را انتخاب کرده و "Layout" را انتخاب کنید. مرحله بعدی این است که به سادگی عنوان را وارد کنید.

قدم بعدی ساخت خط روند خود است. بنابراین، برای این کار، باید دوباره نمودار را انتخاب کنید و برگه "Layout" را در نوار وظیفه انتخاب کنید. بعد، در این منو، باید روی دکمه "خط روند"و انتخاب کنید" تقریب خطی "یا" تقریب نمایی " کلیک کنید.

تغییرات مختلف lخطوط روند

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

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

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

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

تقریب توان. افزایش و کاهش یکنواخت یک مقدار را مشخص می کند، اما اگر داده ها دارای مقادیر منفی و صفر باشند، کاربرد آن غیرممکن است.

میانگین متحرک. برای نشان دادن بصری وابستگی مستقیم یکی به دیگری، با صاف کردن تمام نقاط نوسان استفاده می شود. این با برجسته کردن مقدار متوسط ​​بین دو نقطه مجاور به دست می آید. بنابراین، نمودار میانگین می شود و تعداد نقاط به مقداری که در منوی "نقاط" توسط کاربر انتخاب شده بود کاهش می یابد.

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

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

خط روند در اکسل. تنظیم پارامتردر خط عملکردی

با کلیک بر روی دکمه "Trendline"، منوی مورد نیاز به نام "گزینه های اضافی" را انتخاب کنید. در پنجره ای که ظاهر می شود، روی " Format line trend" کلیک کنید و سپس یک علامت را در مقابل مقدار "Put on chart the value of approximation reliability R ^ 2" قرار دهید. پس از آن، منو را با کلیک بر روی دکمه مربوطه ببندید. ضریب R^2= 0.6442 در خود نمودار ظاهر می شود.

پس از آن تغییرات معرفی شده را لغو می کنیم. پس از انتخاب نمودار و کلیک بر روی تب "Layout"، سپس روی "Trendline" کلیک کنید و روی "No" کلیک کنید. بعد، با رفتن به تابع "Trendline Format"، روی آن کلیک کنید چند جمله ایخط و سعی کنید با تغییر درجه به مقدار R^2= 0.8321 برسید.

برای مشاهده فرمول‌ها یا ساختن فرمول‌های متفاوت، متفاوت از تغییرات استاندارد پیش‌بینی‌ها، کافی است از آزمایش مقادیر و به‌ویژه چند جمله‌ای‌ها نترسید. بنابراین، تنها با استفاده از یک برنامه اکسل، می توانید یک پیش بینی نسبتاً دقیق بر اساس داده های ورودی ایجاد کنید.