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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

روند به عنوان یک مدل

اگر مدلی بسازیم که این پدیده را توصیف کند، ابزاری نسبتاً ساده و بسیار راحت برای پیش‌بینی به دست می‌آوریم که برای بررسی اهمیت یا کفایت عوامل تأثیرگذار نیازی به محاسبات پیچیده یا هزینه‌های زمانی ندارد.

بنابراین، روند به عنوان یک مدل چیست؟ این مجموعه ای از ضرایب محاسبه شده معادله است که وابستگی رگرسیونی شاخص (Y) را به تغییر در زمان (t) بیان می کند. یعنی این دقیقاً همان رگرسیونی است که قبلاً در نظر گرفتیم ، فقط شاخص زمان در اینجا عامل تأثیرگذار است.

مهم!

در محاسبات، t معمولاً به معنای سال، تعداد ماه یا هفته نیست، بلکه به معنای عدد ترتیبی دوره در جامعه آماری مورد مطالعه - سری زمانی است. به عنوان مثال، اگر سری زمانی برای چندین سال مطالعه شود و داده ها به صورت ماهانه ثبت شوند، استفاده از شماره گذاری قابل تنظیم مجدد ماه ها، از 1 تا 12 و دوباره از ابتدا، اساساً اشتباه است. همچنین اگر مطالعه سری شروع شود، مثلاً از اسفند ماه، از 3 (سومین ماه سال) به عنوان مقدار t استفاده شود، اگر این اولین مقدار در جامعه مورد مطالعه باشد، باید عدد ترتیبی آن باشد. 1 باشد.

مدل روند خطی

مانند هر رگرسیون دیگری، روند می تواند خطی باشد (درجه ضریب تأثیر t 1 است) یا غیر خطی (درجه بزرگتر یا کمتر از یک است). از آنجایی که رگرسیون خطی ساده ترین است، اگرچه همیشه دقیق ترین نیست، این نوع روند را با جزئیات بیشتری در نظر خواهیم گرفت.

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

Y(t) = a 0 + a 1 *t + Ɛ

در جایی که a 0 ضریب رگرسیون صفر است، یعنی اگر ضریب تأثیرگذار صفر باشد Y چقدر خواهد بود، a 1 ضریب رگرسیون است که درجه وابستگی شاخص مورد مطالعه Y را به عامل تأثیرگذار t بیان می کند، Ɛ a است. مولفه تصادفی یا استاندارد خطا در واقع تفاوت بین مقادیر واقعی Y و مقادیر محاسبه شده است. t تنها عامل تأثیرگذار است - زمان.

هر چه روند رشد شاخص یا سقوط آن بیشتر باشد، ضریب a 1 بیشتر خواهد بود. بر این اساس، فرض بر این است که ثابت a 0 همراه با مولفه تصادفی Ɛ منعکس کننده بقیه رگرسیون، علاوه بر زمان، یعنی سایر عوامل تأثیرگذار ممکن است.

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

روش گرافیکی برای به دست آوردن روند خطی

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

بیایید یک ستون دیگر به داده های اولیه اضافه کنیم که آن را t می نامیم و با اعداد به ترتیب صعودی شماره سریال تمام مقادیر تولید ناخالص داخلی ثبت شده برای دوره مشخص شده از 2004 تا 2012 را علامت گذاری می کنیم. – 9 ساله یا 9 دوره.

اکسل یک فیلد خالی اضافه می کند - نشانه گذاری برای نمودار آینده، این نمودار را انتخاب کنید و برگه ای را که در نوار منو ظاهر می شود فعال کنید - سازنده، به دنبال یک دکمه است داده ها را انتخاب کنید، در پنجره باز شده دکمه را فشار دهید اضافه کردن. یک پنجره پاپ آپ از شما می خواهد که داده ها را برای رسم نمودار انتخاب کنید. به عنوان مقدار فیلد نام ردیفسلولی را انتخاب کنید که حاوی متنی است که به طور کامل با نام نمودار مطابقت دارد. در زمینه مقادیر Xفاصله سلولی ستون t را نشان می دهد - عامل تأثیرگذار. در زمینه مقادیر Yنشانگر فاصله سلول ها در ستون با مقادیر شناخته شده تولید ناخالص داخلی (Y) - شاخص مورد مطالعه است.

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

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

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

ایجاد یک روند خطی با استفاده از فرمول LINEST

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

ما باید دو سلول مجاور را انتخاب کنیم (در تصویر اینها سلول های A38 و B38 هستند)، سپس در نوار فرمول در بالا (که در تصویر بالا با رنگ قرمز مشخص شده است) با نوشتن "= LINEST (، پس از آن تابع را فراخوانی می کنیم. اکسل نکات مورد نیاز برای این توابع را نمایش می دهد، یعنی:

1. محدوده ای را با مقادیر شناخته شده نشانگر توصیف شده Y انتخاب کنید (در مورد ما GDP، در اسکرین شات محدوده با رنگ آبی مشخص شده است) و یک نقطه ویرگول قرار دهید.
2. محدوده فاکتورهای تأثیرگذار X را نشان دهید (در مورد ما، این نشانگر t است، تعداد سریال دوره ها، که در تصویر با رنگ سبز مشخص شده است) و یک نقطه ویرگول قرار دهید.
3. پارامتر بعدی مورد نیاز برای تابع این است که تعیین کنیم آیا محاسبه ثابت ضروری است، زیرا در ابتدا مدلی با ضریب ثابت در نظر می گیریم یک 0 )، سپس یا "TRUE" یا "1" و یک نقطه ویرگول قرار می دهیم
4. سپس باید مشخص کنید که آیا محاسبه پارامترهای آماری مورد نیاز است (اگر ما این گزینه را در نظر می گرفتیم، ابتدا باید محدوده "زیر فرمول" را چند خط پایین تر انتخاب کنیم). نیاز به محاسبه پارامترهای آماری را مشخص کنید خطای استاندارد برای ضرایب، ضریب جبر، خطای استاندارد برای Y، معیار فیشر، درجات آزادی و غیره.، فقط زمانی معنا پیدا می کند که منظور آنها را بفهمید، در این مورد ما "درست" یا "1" را قرار می دهیم. در مورد مدل سازی ساده که سعی در یادگیری آن داریم، در این مرحله از نوشتن فرمول، "FALSE" یا "0" را قرار داده و یک براکت بسته ")" را بعد از آن اضافه می کنیم.
5. برای "احیای" فرمول، یعنی برای کارکرد آن پس از تجویز تمام پارامترهای لازم، فشار دادن دکمه Enter کافی نیست، باید سه کلید را به ترتیب نگه دارید: Ctrl، Shift، Enter.

همانطور که در تصویر بالا می بینید، سلول هایی که برای فرمول انتخاب کردیم با مقادیر محاسبه شده ضرایب رگرسیون برای روند خطی، در سلول پر شده اند. B38ضریب پیدا می شود یک 0 ، و در سلول A38- ضریب وابستگی به پارامتر تی (یا ایکس )، به این معنا که یک 1 . مقادیر به دست آمده را در معادله یک تابع خطی جایگزین می کنیم و مدل نهایی را در یک عبارت ریاضی بدست می آوریم - y = 169572.2+138454.3*t

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

برای مقایسه مدل به دست آمده با داده های واقعی، می توانید دو نمودار بسازید، که در آن به عنوان X شماره سریال دوره را نشان می دهید، و به عنوان Y در یک مورد - GDP واقعی، و در دیگری - محاسبه می شود (در تصویر، نمودار سمت راست).

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

در مقاله، در واقع، این روش به طور کامل توضیح داده شده است، تنها تفاوت این است که در داده های اولیه ما تنها یک عامل تأثیرگذار وجود دارد. ایکس (شماره دوره - تی ).

همانطور که در شکل بالا مشاهده می شود، محدوده داده با مقادیر GDP شناخته شدهبه عنوان برجسته شده است فاصله ورودی Y، و مربوطه محدوده با اعداد دوره t - به عنوان بازه ورودی X. نتایج محاسبات بسته تجزیه و تحلیل در یک برگه جداگانه قرار می گیرد و مانند مجموعه ای از جداول است (شکل زیر را ببینید) که در آنها به سلول هایی که توسط من به رنگ زرد و سبز رنگ آمیزی شده اند علاقه مندیم. با قیاس با ترتیبی که در مقاله فوق توضیح داده شد، یک مدل روند خطی از ضرایب به دست آمده جمع آوری می شود. y=169572.2+138454.3*t، که بر اساس آن پیش بینی ها انجام می شود.

پیش بینی با روند خطی از طریق تابع TREND

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

در سلول هدف (سلولی که می خواهیم نتیجه را ببینیم) علامت را قرار می دهیم برابر استو تابع جادویی را با نوشتن " فراخوانی کنید روند("، سپس باید انتخاب کنید، یعنی بعد از اینکه نقطه ویرگول قرار دادیم و ما یک محدوده با مقادیر شناخته شده X، یعنی با تعداد دوره t انتخاب می کنیم، که با ستونی با مقادیر GDP شناخته شده مطابقت دارد، دوباره یک نقطه ویرگول قرار داده و سلولی را با تعداد دوره ای که برای آن پیش بینی می کنیم انتخاب کنید (اما در مورد ما، تعداد دوره را می توان با مرجع نشان داد. به سلول، اما به سادگی با یک عدد به طور مستقیم در فرمول)، سپس یک نقطه ویرگول دیگر قرار داده و نشان دهید درست است، واقعییا 1 ، به عنوان تاییدی برای محاسبه ضریب یک 0 در نهایت قرار داده است پرانتز بستنو کلید را فشار دهید وارد.

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

پیش بینی با روند خطی از طریق تابع FORECAST

ماهیت این تابع کاملاً مشابه تابع قبلی است، تنها تفاوت در ترتیب تجویز داده های اولیه در فرمول است و اینکه هیچ تنظیمی برای وجود یا عدم وجود ضریب وجود ندارد. یک 0 (یعنی تابع نشان می دهد که این ضریب به هر حال وجود دارد)

همانطور که در شکل بالا می بینید، در سلول هدف می نویسیم " =پیش بینی(” و سپس مشخص کنید سلول با شماره دوره، که برای آن باید مقدار را با توجه به روند خطی، یعنی پیش بینی محاسبه کرد، سپس یک نقطه ویرگول قرار داد، سپس انتخاب کرد. محدوده مقادیر Y شناخته شده، به این معنا که ستون با مقادیر GDP شناخته شده، سپس یک نقطه ویرگول قرار داده و برجسته کنید محدوده با مقادیر X شناخته شده، به این معنا که با اعداد دوره t، که با ستونی با مقادیر GDP شناخته شده مطابقت دارد و در نهایت تنظیم می شود پرانتز بستنو کلید را فشار دهید وارد.

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

خلاصه مقاله

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

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

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

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

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

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

اگر 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. انتخاب فرمول ها طبق برنامه زمانبندی. خط روند

قبلی12345678910111213141516بعدی

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

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

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

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

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

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

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

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

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

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

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

توطئه

تجزیه و تحلیل رگرسیون

معادله رگرسیون Yاز جانب ایکسوابستگی عملکردی نامیده می شود y=f(x)و نمودار آن یک خط رگرسیون است.

اکسل به شما امکان می دهد نمودارها و نمودارهایی با کیفیت نسبتاً قابل قبول ایجاد کنید. اکسل یک ابزار ویژه دارد - Chart Wizard که کاربر تحت هدایت آن هر چهار مرحله از فرآیند ساخت نمودار یا نمودار را طی می کند.

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

برنج. 31. استاد دیاگرامدر اکسل 2007

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

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

در اکسل 2007، عناوین محورها در تب منو قرار می گیرند چیدمان(شکل 32).

برنج. 32. تنظیم نام محورهای نمودار در اکسل 2007

برای به دست آوردن یک مدل ریاضی، باید یک خط روند روی نمودار رسم کرد. در اکسل 2003 و 2007 روی نقاط نمودار کلیک راست کنید. سپس در اکسل 2003 یک برگه با لیستی از مواردی که از بین آنها انتخاب می کنیم ظاهر می شود خط روند را اضافه کنید(شکل 33).

برنج. 33. خط روند را اضافه کنید

پس از کلیک بر روی مورد خط روند را اضافه کنیدیک پنجره ظاهر می شود خط روند(شکل 34). در تب TYPE می توانید انواع خطوط زیر را انتخاب کنید: خطی، لگاریتمی، نمایی، نمایی، چند جمله ای، فیلتر خطی.

برنج. 34. پنجره خط رونددر اکسل 2003

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

برنج. 35. تب گزینه ها

در اکسل 2007، پس از کلیک راست روی نقاط نمودار، لیستی از آیتم های منو ظاهر می شود که از آن افزودن خط روند را انتخاب کنید(شکل 36).

برنج. 36. خط روند را اضافه کنید

برنج. 37. تب پارامترهای خط روند

چک باکس های مورد نیاز را تنظیم کنید و روی دکمه کلیک کنید بستن.

یک خط روند روی نمودار، معادله مربوط به آن و مقدار قابلیت اطمینان تقریبی ظاهر می شود.

پیش زمینه نظری

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

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

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

این مقاله امکانات بسته 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 trend line را انتخاب کنید. کادر محاوره‌ای 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 وارد کنید و با استفاده از نشانگر پر، این فرمول را با ارجاع نسبی به محدوده سلول های 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. یک سری داده برای رگرسیون نمایی با استفاده از تابع LYFFPRIB بازیابی کنید.
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.