روش حداقل مربعات چیست؟ تقریب داده های تجربی

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

با ساختن چند جمله ای درون یابی n درجه که عبور می کند مستقیماً از طریق تمام نقاطیک آرایه داده داده شده که در در این موردتابع تقریبی به صورت زیر نمایش داده می شود: یک چند جمله ای درون یابی به شکل لاگرانژ یا یک چند جمله ای درون یابی به شکل نیوتن.

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

روش حداقل مربعات(در ادبیات انگلیسی Ordinary Least Squares، OLS) یک روش ریاضی مبتنی بر تعیین یک تابع تقریبی است که در نزدیکترین مجاورت به نقاط از یک آرایه داده‌های تجربی ساخته می‌شود. نزدیکی توابع اصلی و تقریبی F(x) با یک اندازه گیری عددی تعیین می شود، یعنی: مجموع انحرافات مجذور داده های تجربی از منحنی تقریبی F(x) باید کوچکترین باشد.

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

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

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

برای یافتن راه حل در مورد معمولی (نه نادیده گرفته شده) سیستم های غیر خطیمعادلات؛

برای تقریب مقادیر نقطه با برخی از تابع های تقریبی.

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

مقادیر تابع تقریبی محاسبه شده در نقاط گرهی،

یک آرایه داده شده از داده های تجربی در نقاط گرهی.

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

بسته به شرایط مسئله، تابع تقریبی چند جمله ای درجه m است

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

∙ اگر درجه تابع تقریبی m=1 باشد، تابع جدولی را با یک خط مستقیم تقریب می زنیم (رگرسیون خطی).

∙ اگر درجه تابع تقریبی m=2 باشد، تابع جدول را تقریب می کنیم. سهمی درجه دوم(تقریبا درجه دوم).

∙ اگر درجه تابع تقریبی m=3 باشد، تابع جدول را با سهمی مکعبی تقریب می کنیم (تقریبا مکعب).

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

- ضرایب مجهول چند جمله ای تقریبی درجه m.

تعداد مقادیر جدول مشخص شده است.

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

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

این سیستم از عبارات جبری خطی را می توان به صورت ماتریسی بازنویسی کرد:

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

تقریب داده های منبع با وابستگی خطی

(رگرسیون خطی)

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

مختصات گره های جدول.

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

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

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

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

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

الگوریتم اجرای روش حداقل مربعات

1. داده های اولیه:

آرایه ای از داده های تجربی با تعداد اندازه گیری N مشخص شده است

درجه چند جمله ای تقریبی (m) مشخص شده است

2. الگوریتم محاسبه:

2.1. ضرایب برای ساخت یک سیستم معادلات با ابعاد تعیین می شود

ضرایب سیستم معادلات ( سمت چپمعادلات)

- شاخص شماره ستون ماتریس مربع سیستم معادلات

اصطلاحات آزاد سیستم معادلات خطی ( قسمت راستمعادلات)

- شاخص شماره ردیف ماتریس مربع سیستم معادلات

2.2. تشکیل سیستم معادلات خطی با بعد .

2.3. حل یک سیستم معادلات خطی برای تعیین ضرایب مجهول یک چند جمله ای تقریبی درجه m.

2.4. تعیین مجموع انحرافات مجذور چند جمله ای تقریبی از مقادیر اصلی در تمام نقاط گرهی

مقدار یافت شده مجموع مجذور انحرافات حداقل ممکن است.

تقریب با استفاده از توابع دیگر

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

تقریب لگاریتمی

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

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

جایی که - انحراف معیاربین مقدار واقعی مشاهده شده

و مقدار روند محاسبه شده مربوطه،

ارزش واقعی (مشاهده شده) پدیده مورد مطالعه،

مقدار محاسبه شده مدل روند،

تعداد مشاهدات پدیده مورد مطالعه.

MNC به ندرت به تنهایی استفاده می شود. به عنوان یک قاعده، اغلب از آن فقط به عنوان یک تکنیک فنی ضروری در مطالعات همبستگی استفاده می شود. باید به خاطر داشت که اساس اطلاعات یک MNC فقط می تواند قابل اعتماد باشد سری های آماری، و تعداد مشاهدات نباید کمتر از 4 باشد، در غیر این صورت روش های هموارسازی OLS ممکن است عقل سلیم را از دست بدهند.

جعبه ابزار MNC به رویه های زیر خلاصه می شود:

روش اول معلوم می‌شود که آیا در هنگام تغییر عامل انتخابی، تمایلی به تغییر ویژگی حاصل وجود دارد یا به عبارت دیگر، آیا ارتباطی بین « در "و" ایکس ».

رویه دوم مشخص می شود که کدام خط (مسیر) می تواند این روند را به بهترین نحو توصیف یا توصیف کند.

رویه سوم.

مثال. فرض کنید اطلاعاتی در مورد میانگین عملکرد آفتابگردان برای مزرعه مورد مطالعه داریم (جدول 9.1).

جدول 9.1

شماره مشاهده
بهره وری، c/ha

از آنجایی که سطح فناوری تولید آفتابگردان در کشور ما طی 10 سال گذشته تقریباً بدون تغییر باقی مانده است، به این معنی است که ظاهراً نوسانات عملکرد در دوره مورد تجزیه و تحلیل بسیار وابسته به نوسانات آب و هوا و شرایط اقلیمی بوده است. آیا این واقعا درست است؟

اولین روش OLS فرضیه وجود یک روند در تغییرات عملکرد آفتابگردان بسته به تغییرات آب و هوا و شرایط آب و هوایی در طول 10 سال مورد تجزیه و تحلیل آزمایش شده است.

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

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

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

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

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

		هذلولی:

سهمی مرتبه دوم: :

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

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

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

(9.2)

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

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

بیان مسئله با استفاده از یک مثال خاص

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

بنابراین، اجازه دهید X فضای خرده فروشی یک فروشگاه مواد غذایی باشد که بر حسب متر مربع اندازه گیری می شود، و Y گردش مالی سالانه است که در میلیون ها روبل اندازه گیری می شود.

لازم است پیش بینی کنید که فروشگاه در صورت داشتن این یا آن فضای خرده فروشی، چه گردش مالی (Y) خواهد داشت. بدیهی است که تابع Y = f (X) در حال افزایش است، زیرا هایپر مارکت کالاهای بیشتری از غرفه می فروشد.

چند کلمه در مورد صحت داده های اولیه مورد استفاده برای پیش بینی

فرض کنید جدولی داریم که با استفاده از داده برای n فروشگاه ساخته شده است.

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

ماهیت روش

داده های جدول را می توان در یک صفحه دکارتی به شکل نقاط M 1 (x 1، y 1)، ... M n (x n، y n) به تصویر کشید. اکنون راه حل مسئله به انتخاب یک تابع تقریبی y = f (x) کاهش می یابد، که دارای نموداری است که تا حد امکان به نقاط M 1، M 2، .. M n می گذرد.

البته، می توانید از یک چند جمله ای درجه بالا استفاده کنید، اما اجرای این گزینه نه تنها دشوار است، بلکه به سادگی نادرست است، زیرا روند اصلی را که باید شناسایی شود منعکس نمی کند. معقول ترین راه حل جستجوی خط مستقیم y = ax + b است که به بهترین وجه به داده های تجربی یا به طور دقیق تر، ضرایب a و b را تقریب می کند.

ارزیابی دقت

با هر تقریبی، ارزیابی دقت آن از اهمیت ویژه ای برخوردار است. اجازه دهید تفاوت (انحراف) بین مقادیر عملکردی و تجربی نقطه x i را با e i نشان دهیم، یعنی e i = y i - f (x i).

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

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

روش حداقل مربعات

همانطور که می دانید اکسل دارای یک تابع AutoSum داخلی است که به شما امکان می دهد مقادیر تمام مقادیر موجود در محدوده انتخاب شده را محاسبه کنید. بنابراین، هیچ چیز ما را از محاسبه مقدار عبارت باز نمی دارد (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

که در نماد ریاضیبه نظر می رسد:

از آنجایی که در ابتدا تصمیم به تقریب با استفاده از یک خط مستقیم گرفته شد، داریم:

بنابراین، وظیفه یافتن خطی است که به بهترین شکل توصیف می کند وابستگی خاصکمیت های X و Y، به محاسبه حداقل یک تابع از دو متغیر می رسد:

برای انجام این کار، شما باید مشتقات جزئی را با توجه به متغیرهای جدید a و b برابر با صفر کنید و یک سیستم ابتدایی متشکل از دو معادله با 2 مجهول شکل را حل کنید:

پس از چند تبدیل ساده، از جمله تقسیم بر 2 و دستکاری مجموع، به دست می آوریم:

برای حل آن، به عنوان مثال، با استفاده از روش کرامر، یک نقطه ثابت با ضرایب معین a * و b * به دست می آوریم. این حداقل است، یعنی برای پیش بینی اینکه یک فروشگاه برای یک منطقه خاص چه گردش مالی خواهد داشت، خط مستقیم y = a * x + b * مناسب است که یک مدل رگرسیونی برای مثال مورد نظر است. البته، این به شما اجازه نمی دهد که نتیجه دقیق را پیدا کنید، اما به شما کمک می کند تا تصور کنید که آیا خرید یک منطقه خاص با اعتبار فروشگاه نتیجه می دهد یا خیر.

نحوه پیاده سازی حداقل مربعات در اکسل

اکسل تابعی برای محاسبه مقادیر با استفاده از حداقل مربعات دارد. شکل زیر را دارد: "TREND" (مقادیر Y شناخته شده؛ مقادیر X شناخته شده؛ مقادیر X جدید؛ ثابت). بیایید فرمول محاسبه OLS در اکسل را در جدول خود اعمال کنیم.

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

• محدوده مقادیر شناخته شده برای Y (در این مورد، داده های گردش تجاری)؛
• محدوده x 1، …x n، یعنی اندازه فضای خرده فروشی؛
• هم معروف و هم مقادیر ناشناخته x، که برای آن باید اندازه گردش مالی را بدانید (برای اطلاعات در مورد مکان آنها در کاربرگ، به زیر مراجعه کنید).

علاوه بر این، فرمول حاوی متغیر منطقی "Const" است. اگر 1 را در فیلد مربوطه وارد کنید، به این معنی است که باید محاسبات را با فرض b = 0 انجام دهید.

اگر نیاز به پیش بینی بیش از یک مقدار x دارید، پس از وارد کردن فرمول نباید "Enter" را فشار دهید، بلکه باید ترکیب "Shift" + "Control" + "Enter" را روی صفحه کلید تایپ کنید.

برخی از ویژگی ها

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

• اگر محدوده مقادیر شناخته شده متغیر y را در یک سطر یا ستون مرتب کنید، سپس هر سطر (ستون) با ارزش های شناخته شده x توسط برنامه به عنوان یک متغیر جداگانه در نظر گرفته می شود.
• اگر پنجره TREND محدوده ای با x شناخته شده را نشان نمی دهد، اگر تابع در استفاده می شود برنامه اکسلآن را به عنوان یک آرایه متشکل از اعداد صحیح، که تعداد آنها با محدوده با مقادیر داده شده متغیر y مطابقت دارد، در نظر می گیرد.
• برای خروجی آرایه ای از مقادیر «پیش بینی شده»، عبارت محاسبه روند باید به عنوان فرمول آرایه وارد شود.
• اگر مقادیر جدید x مشخص نشده باشد، تابع TREND آنها را برابر با مقادیر شناخته شده در نظر می گیرد. اگر آنها مشخص نشده باشند، آرایه 1 به عنوان آرگومان در نظر گرفته می شود. 2 3; 4;…، که متناسب با محدوده با پارامترهای از قبل مشخص شده y است.
• محدوده حاوی مقادیر جدید x باید همان یا چند ردیف یا ستون به اندازه محدوده حاوی مقادیر y داده شده داشته باشد. به عبارت دیگر باید متناسب با متغیرهای مستقل باشد.
• آرایه ای با مقادیر x شناخته شده می تواند شامل چندین متغیر باشد. با این حال، اگر فقط در مورد یکی صحبت می کنیم، لازم است که محدوده هایی با مقادیر داده شده x و y متناسب باشند. در مورد چندین متغیر، لازم است که محدوده با مقادیر y داده شده در یک ستون یا یک ردیف قرار گیرد.

تابع پیش بینی

با استفاده از چندین توابع پیاده سازی شده است. یکی از آنها "پیش بینی" نام دارد. این شبیه به "TREND" است، یعنی نتیجه محاسبات را با استفاده از روش حداقل مربعات ارائه می دهد. با این حال، فقط برای یک X، که مقدار Y برای آن ناشناخته است.

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

مثال.

داده های تجربی در مورد مقادیر متغیرها ایکسو دردر جدول آورده شده است.

در نتیجه تراز آنها، تابع به دست می آید

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

ماهیت روش حداقل مربعات (LSM).

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

بنابراین، حل مثال به یافتن حداکثر یک تابع از دو متغیر ختم می شود.

استخراج فرمول برای یافتن ضرایب.

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

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

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

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

وقت آن است که نمونه اصلی را به خاطر بسپارید.

راه حل.

در مثال ما n=5. برای راحتی محاسبه مقادیری که در فرمول های ضرایب مورد نیاز گنجانده شده است، جدول را پر می کنیم.

مقادیر سطر چهارم جدول با ضرب مقادیر سطر دوم در مقادیر سطر 3 برای هر عدد به دست می آید. من.

مقادیر ردیف پنجم جدول با مربع کردن مقادیر ردیف دوم برای هر عدد به دست می آید. من.

مقادیر در آخرین ستون جدول مجموع مقادیر در سراسر سطرها هستند.

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

از این رو، y = 0.165x+2.184- خط مستقیم تقریبی مورد نظر.

باقی مانده است که بفهمیم کدام یک از خطوط y = 0.165x+2.184یا داده های اصلی را بهتر تقریب می کند، یعنی با استفاده از روش حداقل مربعات تخمین می زند.

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

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

از آن زمان، سپس مستقیم y = 0.165x+2.184داده های اصلی را بهتر تقریب می کند.

تصویر گرافیکی روش حداقل مربعات (LS).

همه چیز به وضوح در نمودارها قابل مشاهده است. خط قرمز خط مستقیم پیدا شده است y = 0.165x+2.184، خط آبی است ، نقطه های صورتی داده های اصلی هستند.

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

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

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

در ادغام عددی، تمایز، حل معادلات دیفرانسیلو غیره.؛

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

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

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

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

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

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

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

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

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

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

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

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

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

y = y(x)

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

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

y = mx + b

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

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

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

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

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

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

y = c ln(x) + b

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

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

y = c xb

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

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

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

y = c ebx

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

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

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

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

یک نمودار را بر اساس یک سری داده فعال کنید، یعنی در ناحیه نمودار کلیک کنید. مورد نمودار در منوی اصلی ظاهر می شود.

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

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

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

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

1 . فیلد Built on series تمام سری داده ها را در نمودار مورد نظر فهرست می کند. برای افزودن یک خط روند به یک سری داده خاص، نام آن را در قسمت Built on series انتخاب کنید.

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

نام خط روند را در نام فیلد منحنی تقریبی (صاف) تغییر دهید.

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

معادله خط روند را در ناحیه نمودار نمایش دهید، که برای آن باید معادله نمایش را در کادر چک نمودار فعال کنید.

مقدار اعتبار تقریبی R2 را در ناحیه نمودار نمایش دهید، که برای آن باید کادر انتخاب Place the approximation reliability on the diagram (R^2) را فعال کنید.

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

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

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

با انتخاب خط روند از منوی Format از دستور Selected trend line استفاده کنید.

دستور Format line trend را از منوی زمینه انتخاب کنید که با کلیک راست روی خط روند فراخوانی می شود.

روی خط روند دوبار کلیک کنید.

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

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

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

سهولت نسبی ساخت یک خط روند روی نمودارها بدون ایجاد جدول داده برای آن؛

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

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

توانایی به دست آوردن معادله خط روند به صورت تحلیلی؛

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

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

ساخت یک خط روند تنها در صورتی انجام می شود که نموداری بر اساس یک سری داده وجود داشته باشد.

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

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

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

شما نمی توانید خطوط روند را به سری های داده در نمودارهای سه بعدی، عادی، رادار، پای و دونات اضافه کنید.

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

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

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

روند؛

• SLOPE و CUT.

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

LGRFPRIBL.

لازم به ذکر است که تکنیک های ساخت رگرسیون با استفاده از توابع TREND و GROWTH تقریباً یکسان است. همین را می توان در مورد جفت توابع LINEST و LGRFPRIBL گفت. برای این چهار تابع، ایجاد جدول مقادیر از ویژگی‌های اکسل مانند فرمول‌های آرایه استفاده می‌کند که تا حدودی فرآیند ایجاد رگرسیون را به هم می‌ریزد. همچنین توجه داشته باشید که ساخت رگرسیون خطی، به نظر ما، به راحتی با استفاده از توابع SLOPE و INTERCEPT انجام می شود، که در آن اولی شیب رگرسیون خطی را تعیین می کند، و دومی قطعه قطع شده توسط رگرسیون در y را تعیین می کند. -محور.

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

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

روش استاندارد برای ساخت خطوط روند بر اساس سری داده های تولید شده؛

توانایی پیش بینی رفتار فرآیند مورد مطالعه با تعداد گام های لازم به جلو یا عقب.

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

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

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

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

مشکل 1

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

یک نمودار بسازید.

خطوط روند خطی و چند جمله ای (دو و مکعبی) را به نمودار اضافه کنید.

با استفاده از معادلات خط روند، داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2004 به دست آورید.

برای سال های 2003 و 2004 سود شرکت را پیش بینی کنید.

راه حل مشکل

در محدوده سلول های A4:C11 کاربرگ اکسل، کاربرگ نشان داده شده در شکل را وارد کنید. 4.

با انتخاب محدوده سلول های B4:C11، یک نمودار می سازیم.

نمودار ساخته شده را فعال می کنیم و طبق روشی که در بالا توضیح داده شد، پس از انتخاب نوع خط روند در کادر محاوره ای خط روند (نگاه کنید به شکل 1)، به طور متناوب خطوط روند خطی، درجه دوم و مکعبی را به نمودار اضافه می کنیم. در همان کادر محاوره ای، زبانه پارامترها را باز کنید (شکل 2 را ببینید)، در نام قسمت منحنی تقریبی (صاف) نام روند اضافه شده را وارد کنید و در قسمت Forecast forward for: periods، مقدار را تنظیم کنید. مقدار 2، زیرا برنامه ریزی شده است که پیش بینی سود برای دو سال آینده انجام شود. برای نمایش معادله رگرسیون و مقدار قابلیت اطمینان تقریبی R2 در ناحیه نمودار، معادله نمایش را در کادرهای انتخاب صفحه فعال کنید و مقدار قابلیت اطمینان تقریبی (R^2) را روی نمودار قرار دهید. برای درک بصری بهتر، نوع، رنگ و ضخامت خطوط روند ساخته شده را تغییر می دهیم، که برای آن از تب View در کادر محاوره ای Trend Line Format استفاده می کنیم (شکل 3 را ببینید). نمودار حاصل با خطوط روند اضافه شده در شکل نشان داده شده است. 5.

برای به دست آوردن داده های جدولی در مورد سود شرکت برای هر خط روند برای 1995-2004. بیایید از معادلات خط روند ارائه شده در شکل استفاده کنیم. 5. برای این کار در سلول های محدوده D3:F3 اطلاعات متنی نوع خط روند انتخابی را وارد کنید: روند خطی، روند درجه دوم، روند مکعبی. سپس فرمول رگرسیون خطی را در سلول D4 وارد کنید و با استفاده از نشانگر پر، این فرمول را با ارجاعات نسبی به محدوده سلولی D5:D13 کپی کنید. لازم به ذکر است که هر سلول با فرمول رگرسیون خطی از محدوده سلول‌های D4:D13 دارای یک سلول مربوطه از محدوده A4:A13 است. به طور مشابه، برای رگرسیون درجه دوم، محدوده سلول های E4:E13 و برای رگرسیون مکعبی، محدوده سلول های F4:F13 را پر کنید. بدین ترتیب پیش بینی سود شرکت برای سال های 2003 و 2004 تدوین شده است. با استفاده از سه گرایش جدول مقادیر حاصل در شکل نشان داده شده است. 6.

مشکل 2

یک نمودار بسازید.

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

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

با استفاده از معادلات خط روند، داده های جدولی در مورد سود شرکت برای هر خط روند برای سال های 1995-2002 به دست آورید.

با استفاده از این خطوط روند، سود شرکت را برای سال های 2003 و 2004 پیش بینی کنید.

راه حل مشکل

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

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

R2 = 0.8659

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

مشکل 3

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

سری داده ها را برای خطوط روند خطی و نمایی با استفاده از توابع TREND و GROW بدست آورید.

با استفاده از توابع TREND و GROWTH، سود شرکت را برای سال های 2003 و 2004 پیش بینی کنید.

یک نمودار برای داده های اصلی و سری داده های حاصل بسازید.

راه حل مشکل

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

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

دستور Function را از منوی Insert فراخوانی کنید. در کادر محاوره‌ای Function Wizard که ظاهر می‌شود، تابع TREND را از دسته Statistical انتخاب کنید و سپس روی دکمه OK کلیک کنید. همین عملیات را می توان با کلیک بر روی دکمه (Insert Function) در نوار ابزار استاندارد انجام داد.

در کادر محاوره‌ای Function Arguments که ظاهر می‌شود، محدوده سلول‌های C4:C11 را در قسمت Known_values_y وارد کنید. در قسمت Known_values_x - محدوده سلول های B4:B11;

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

فرمولی که در نوار فرمول وارد کردیم به صورت زیر خواهد بود: =(TREND(C4:C11,B4:B11)).

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

برای پیش بینی سود شرکت برای سال های 2003 و 2004. لازم:

محدوده سلول های D12:D13 را انتخاب کنید که در آن مقادیر پیش بینی شده توسط تابع TREND وارد می شود.

تابع TREND را فراخوانی کنید و در کادر محاوره ای Function Arguments که ظاهر می شود، در قسمت Known_values_y - محدوده سلول های C4:C11 را وارد کنید. در قسمت Known_values_x - محدوده سلول های B4:B11; و در قسمت New_values_x - محدوده سلول های B12:B13.

این فرمول را با استفاده از کلیدهای ترکیبی Ctrl + Shift + Enter به فرمول آرایه تبدیل کنید.

فرمول وارد شده به صورت زیر خواهد بود: =(TREND(C4:C11;B4:B11;B12:B13))، و محدوده سلول های D12:D13 با مقادیر پیش بینی شده تابع TREND پر می شود (شکل 1 را ببینید). 9).

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

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

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

مشکل 4

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

دریافت سری داده ها برای رگرسیون خطی: با استفاده از توابع SLOPE و INTERCEPT. با استفاده از تابع LINEST

با استفاده از تابع LGRFPRIBL یک سری داده برای رگرسیون نمایی بدست آورید.

با استفاده از توابع فوق، پیش بینی دریافت درخواست ها به سرویس اعزام را برای بازه زمانی 12 تا 14 ماه جاری انجام دهید.

یک نمودار برای سری داده های اصلی و دریافتی ایجاد کنید.

راه حل مشکل

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

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

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

y = mx+b

با استفاده از توابع SLOPE و INTERCEPT، با شیب رگرسیون m توسط تابع SLOPE، و عبارت آزاد b توسط تابع INTERCEPT تعیین می شود.

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

جدول اصلی را در محدوده سلولی A4:B14 وارد کنید.

مقدار پارامتر m در سلول C19 تعیین می شود. تابع Slope را از دسته آماری انتخاب کنید. محدوده سلول‌های B4:B14 را در قسمت Known_values_y و محدوده سلول‌های A4:A14 را در قسمت Known_values_x وارد کنید. فرمول در سلول C19 وارد می شود: =SLOPE(B4:B14,A4:A14);

با استفاده از تکنیک مشابه، مقدار پارامتر b در سلول D19 تعیین می شود. و محتویات آن به صورت زیر خواهد بود: =SEGMENT(B4:B14,A4:A14). بنابراین، مقادیر پارامترهای m و b مورد نیاز برای ساخت یک رگرسیون خطی به ترتیب در سلول‌های C19، D19 ذخیره می‌شوند.

سپس فرمول رگرسیون خطی را در سلول C4 به شکل =$C*A4+$D وارد کنید. در این فرمول، سلول های C19 و D19 با ارجاع مطلق نوشته شده اند (آدرس سلول نباید در طول کپی ممکن تغییر کند). علامت مرجع مطلق $ را می توان پس از قرار دادن مکان نما روی آدرس سلول یا از صفحه کلید یا با استفاده از کلید F4 تایپ کرد. با استفاده از دسته پر، این فرمول را در محدوده سلول های C4:C17 کپی کنید. سری داده های مورد نیاز را بدست می آوریم (شکل 12). با توجه به اینکه تعداد درخواست ها یک عدد صحیح است باید در تب Number پنجره Cell Format فرمت اعداد با تعداد ارقام اعشاری را 0 قرار دهید.

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

y = mx+b

با استفاده از تابع LINEST

برای این:

تابع LINEST را به عنوان یک فرمول آرایه در محدوده سلولی C20:D20: =(LINEST(B4:B14,A4:A14)) وارد کنید. در نتیجه، مقدار پارامتر m را در سلول C20 و مقدار پارامتر b را در سلول D20 به دست می آوریم.

فرمول را در سلول D4 وارد کنید: =$C*A4+$D;

این فرمول را با استفاده از نشانگر پر در محدوده سلولی D4:D17 کپی کنید و سری داده های مورد نظر را دریافت کنید.

3 . با معادله رگرسیون نمایی می سازیم:

با استفاده از تابع LGRFPRIBL به طور مشابه انجام می شود:

در محدوده سلولی C21:D21 تابع LGRFPRIBL را به عنوان فرمول آرایه وارد می کنیم: =( LGRFPRIBL (B4:B14,A4:A14)). در این حالت، مقدار پارامتر m در سلول C21 و مقدار پارامتر b در سلول D21 تعیین می شود.

فرمول در سلول E4 وارد می شود: =$D*$C^A4;

با استفاده از نشانگر پر، این فرمول در محدوده سلول های E4:E17، جایی که سری داده های رگرسیون نمایی قرار خواهد گرفت، کپی می شود (شکل 12 را ببینید).

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

اندازه آر 2 تماس گرفت ضریب تعیین.

وظیفه ساخت یک وابستگی رگرسیونی یافتن بردار ضرایب m مدل (1) است که در آن ضریب R حداکثر مقدار را به خود می گیرد.

برای ارزیابی اهمیت R، از آزمون F فیشر استفاده می شود که با استفاده از فرمول محاسبه می شود

جایی که n- حجم نمونه (تعداد آزمایش)؛

k تعداد ضرایب مدل است.

اگر F از مقدار بحرانی داده ها فراتر رود nو کو احتمال اطمینان پذیرفته شده، پس مقدار R معنی دار در نظر گرفته می شود. جداول ارزش های بحرانی F در کتاب های مرجع در مورد آمار ریاضی آورده شده است.

بنابراین، اهمیت R نه تنها با مقدار آن، بلکه با نسبت بین تعداد آزمایش ها و تعداد ضرایب (پارامترهای) مدل نیز تعیین می شود. در واقع، نسبت همبستگی برای n=2 برای یک مدل خطی ساده برابر با 1 است (یک خط مستقیم همیشه می تواند از طریق 2 نقطه در یک صفحه رسم شود). با این حال، اگر داده های تجربی متغیرهای تصادفی هستند، باید به چنین مقدار R با احتیاط زیادی اعتماد کرد. معمولاً برای به دست آوردن رگرسیون R قابل‌توجه و قابل اعتماد، آنها تلاش می‌کنند تا اطمینان حاصل کنند که تعداد آزمایش‌ها به طور قابل‌توجهی از تعداد ضرایب مدل (n>k) بیشتر است.

برای ساخت یک مدل رگرسیون خطی شما نیاز دارید:

1) فهرستی از n ردیف و m ستون حاوی داده های تجربی تهیه کنید (ستون حاوی مقدار خروجی Yباید اولین یا آخرین در لیست باشد). برای مثال، بیایید داده‌های کار قبلی را بگیریم، ستونی به نام دوره شماره اضافه کنیم، اعداد دوره را از 1 تا 12 شماره گذاری کنیم. (اینها مقادیر خواهند بود. ایکس)

2) به منوی Data/Data Analysis/Regression بروید

اگر مورد «تجزیه و تحلیل داده‌ها» در منوی «ابزارها» وجود ندارد، باید به آیتم «افزونه‌ها» در همان منو بروید و کادر «بسته تحلیل» را علامت بزنید.

3) در کادر گفتگوی "Regression"، تنظیم کنید:

· فاصله ورودی Y;

· فاصله ورودی X.

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

4) روی "OK" کلیک کنید و نتایج را تجزیه و تحلیل کنید.

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

تعداد خطوط (داده ی منبع)

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

1. انتخاب شکل اتصال (نوع معادله رگرسیون تحلیلی)؛
2. تخمین پارامترهای معادله;
3. ارزیابی کیفیت معادله رگرسیون تحلیلی.

اغلب، یک فرم خطی برای توصیف رابطه آماری ویژگی ها استفاده می شود. تمرکز بر روابط خطی با تفسیر اقتصادی واضح پارامترهای آن، تنوع محدود متغیرها، و این واقعیت که در بیشتر موارد اشکال غیرخطی روابط (با لگاریتم یا جایگزینی متغیرها) به شکل خطی برای انجام محاسبات تبدیل می‌شوند، توضیح داده می‌شود. .
در مورد یک رابطه زوجی خطی، معادله رگرسیون به شکل زیر خواهد بود: y i =a+b·x i +u i. پارامترهای a و b این معادله از روی داده ها تخمین زده می شود مشاهده آماری x و y نتیجه چنین ارزیابی معادله است: , که در آن، تخمین پارامترهای a و b هستند، مقدار مشخصه (متغیر) حاصل از معادله رگرسیون (مقدار محاسبه شده) است.

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

مسئله تخمین پارامترهای یک معادله جفت خطی با استفاده از روش حداقل مربعاتبه شرح زیر است: برای به دست آوردن چنین تخمینی از پارامترها، که در آن مجموع انحرافات مجذور مقادیر واقعی مشخصه حاصل - y i از مقادیر محاسبه شده - حداقل است.
به صورت رسمی تست OLSمی توان اینگونه نوشت: .

طبقه بندی روش های حداقل مربعات

1. روش حداقل مربعات
2. روش حداکثر درستنمایی (برای یک مدل رگرسیون خطی کلاسیک نرمال، نرمال بودن باقیمانده های رگرسیون فرض شده است).
3. روش حداقل مربعات تعمیم یافته OLS در مورد خودهمبستگی خطاها و در مورد ناهمسانی استفاده می شود.
4. روش حداقل مربعات وزنی ( مورد خاص OLS با باقیمانده های هتروسکداستیک).

بیایید موضوع را توضیح دهیم روش کلاسیکحداقل مربعات گرافیکی. برای انجام این کار، یک نمودار پراکندگی بر اساس داده های مشاهده ای (x i, y i, i=1;n) در یک سیستم مختصات مستطیلی می سازیم (به چنین نمودار پراکندگی، میدان همبستگی می گویند). بیایید سعی کنیم خط مستقیمی را انتخاب کنیم که نزدیکترین خط به نقاط میدان همبستگی باشد. بر اساس روش حداقل مربعات، خط به گونه ای انتخاب می شود که مجموع مجذورات فواصل عمودی بین نقاط میدان همبستگی و این خط حداقل باشد.

نماد ریاضی برای این مسئله: .
مقادیر y i و x i =1...n برای ما شناخته شده است؛ اینها داده های مشاهده ای هستند. در تابع S آنها ثابت ها را نشان می دهند. متغیرهای این تابع برآوردهای مورد نیاز پارامترهای - , . برای یافتن حداقل یک تابع از دو متغیر، لازم است مشتقات جزئی این تابع را برای هر یک از پارامترها محاسبه کرده و آنها را با صفر برابر کنیم، یعنی. .
در نتیجه سیستمی متشکل از 2 معادله خطی نرمال بدست می آوریم:
تصمیم گیری این سیستم، تخمین پارامترهای مورد نیاز را پیدا می کنیم:

صحت محاسبه پارامترهای معادله رگرسیون را می توان با مقایسه مقادیر بررسی کرد (ممکن است مقداری مغایرت به دلیل گرد کردن محاسبات وجود داشته باشد).
برای محاسبه تخمین پارامترها، می توانید جدول 1 را بسازید.
علامت ضریب رگرسیون b جهت رابطه را نشان می دهد (اگر b>0، رابطه مستقیم است، اگر b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
به طور رسمی، مقدار پارامتر a میانگین مقدار y با x برابر با صفر است. اگر ویژگی-factor مقدار صفر نداشته باشد و نمی تواند داشته باشد، تفسیر فوق از پارامتر a معنی ندارد.

ارزیابی نزدیکی رابطه بین ویژگی ها با استفاده از ضریب همبستگی جفت خطی - r x,y انجام شد. با استفاده از فرمول قابل محاسبه است: . علاوه بر این، ضریب همبستگی جفت خطی را می توان از طریق ضریب رگرسیون b تعیین کرد: .
دامنه مقادیر قابل قبول ضریب همبستگی جفت خطی از -1 تا +1 است. علامت ضریب همبستگی جهت رابطه را نشان می دهد. اگر r x، y > 0، آنگاه اتصال مستقیم است. اگر r x، y<0, то связь обратная.
اگر این ضریب از نظر مقدار نزدیک به وحدت باشد، رابطه بین ویژگی ها را می توان به عنوان یک رابطه خطی نسبتا نزدیک تفسیر کرد. اگر ماژول آن برابر با یک ê r x، y ê =1 باشد، آنگاه رابطه بین مشخصه ها تابعی خطی است. اگر ویژگی های x و y به صورت خطی مستقل باشند، آنگاه r x,y نزدیک به 0 است.
برای محاسبه r x,y می توانید از جدول 1 نیز استفاده کنید.

میز 1

N مشاهدات	x i	y من	x i ∙y i
1	x 1	y 1	x 1 y 1
2	x 2	y 2	x 2 y 2
...
n	x n	y n	x n y n
جمع ستون	∑x	∑y	∑xy
مقدار متوسط

برای ارزیابی کیفیت معادله رگرسیون حاصل، ضریب تعیین نظری را محاسبه کنید - R 2 yx:

,
که در آن d 2 واریانس y است که با معادله رگرسیون توضیح داده می شود.
e 2 - واریانس باقیمانده (غیر قابل توضیح با معادله رگرسیون) y;
s 2 y - واریانس کل (کل) y.
ضریب تعیین، نسبت تغییرات (پراکندگی) ویژگی حاصل y را که با رگرسیون (و در نتیجه، عامل x) در کل تغییرات (پراکندگی) y توضیح داده شده است، مشخص می کند. ضریب تعیین R 2 yx مقادیر 0 تا 1 را می گیرد. بر این اساس، مقدار 1-R 2 yx نسبت واریانس y را مشخص می کند که ناشی از تأثیر سایر عوامل در نظر گرفته نشده در مدل و خطاهای مشخصات است.
با رگرسیون خطی زوجی، R 2 yx = r 2 yx.