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

روش حداقل مربعات(انگلیسی) معمولی کمترین مربع ها , O.L.S.) یکی از روش های اساسی تحلیل رگرسیون از نظر تخمین پارامترهای مجهول است مدل های رگرسیونبا توجه به داده های نمونه

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

• خطی: y=ax+b (این مقاله)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

توجه داشته باشید: موارد تقریب با چند جمله ای از درجه 3 تا 6 در این مقاله بررسی می شود. تقریب توسط یک چند جمله ای مثلثاتی در اینجا در نظر گرفته شده است.

وابستگی خطی

ما علاقه مند به ارتباط بین 2 متغیر هستیم ایکسو y. این فرض وجود دارد که yبستگی دارد به ایکسطبق قانون خطی y = تبر + ب. برای تعیین پارامترهای این رابطه، محقق مشاهداتی انجام داد: برای هر مقدار x i، اندازه گیری y i انجام شد (به فایل مثال مراجعه کنید). بر این اساس، اجازه دهید 20 جفت مقدار (x i؛ y i) وجود داشته باشد.

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

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

به عنوان چنین معیاری از عبارت:

جایی که ŷ من = آ * x i + ب ; n - تعداد جفت مقادیر (در مورد ما n=20)

عبارت فوق مجموع مجذور فواصل بین مقادیر مشاهده شده y i و ŷ i است و اغلب با SSE نشان داده می شود. مجموع از مربع خطاها (باقیمانده ها، مجموع مربعات خطاها (باقیمانده)) .

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

توجه داشته باشید:هر خط در فضای دو بعدی به طور منحصر به فرد با مقادیر 2 پارامتر تعیین می شود: آ (شیب) و ب (تغییر مکان).

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

با انجام عملیات ریاضی نه چندان پیچیده (برای جزئیات بیشتر به آن مراجعه کنید)، می توانید پارامترها را محاسبه کنید آ و ب :

همانطور که از فرمول مشخص است، پارامتر آ نشان دهنده نسبت کوواریانس و بنابراین در MS EXCEL برای محاسبه پارامتر است آ می توانید از فرمول های زیر استفاده کنید (نگاه کنید به فایل نمونه ورق خطی):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45)یا

= COVARIANCE.B(B26:B45;C26:C45)/DISP.B(B26:B45)

همچنین برای محاسبه پارامتر آ می توانید از فرمول = استفاده کنید TILT(C26:C45;B26:B45). برای پارامتر ب از فرمول = استفاده کنید LEG(C26:C45;B26:B45) .

در نهایت، تابع LINEST() به شما امکان می دهد هر دو پارامتر را به طور همزمان محاسبه کنید. برای وارد کردن فرمول LINEST(C26:C45;B26:B45)شما باید 2 سلول را در یک ردیف انتخاب کنید و کلیک کنید CTRL + تغییر مکان + وارد(به مقاله در مورد مراجعه کنید). مقدار در سلول سمت چپ برگردانده می شود آ ، در سمت راست - ب .

توجه داشته باشید: برای جلوگیری از بهم ریختگی ورودی فرمول های آرایهعلاوه بر این باید از تابع INDEX() استفاده کنید. فرمول = INDEX(LINEST(C26:C45،B26:B45)،1)یا فقط = LINEST(C26:C45;B26:B45)پارامتر مسئول شیب خط را برمی گرداند، یعنی. آ . فرمول = INDEX(LINEST(C26:C45،B26:B45)،2)پارامتر مسئول تقاطع خط با محور Y را برمی گرداند، یعنی. ب .

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

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

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

توجه داشته باشید: برای اینکه پارامترها مطابقت داشته باشند، نوع نمودار باید باشد. نکته این است که هنگام ساخت نمودار برنامهمقادیر محور X را کاربر نمی‌تواند مشخص کند (کاربر فقط می‌تواند برچسب‌هایی را مشخص کند که روی مکان نقاط تأثیری ندارند). به جای مقادیر X، دنباله 1 استفاده می شود. 2 3; ... (برای شماره گذاری دسته ها). بنابراین، اگر بسازید خط رونددر یک نمودار نوع برنامه، سپس به جای مقادیر واقعی X از مقادیر این دنباله استفاده می شود که منجر به نتیجه نادرست می شود (مگر اینکه البته ارزش های واقعی X با دنباله 1 مطابقت ندارد. 2 3; ...).

کاربردهای زیادی دارد زیرا امکان نمایش تقریبی را فراهم می کند عملکرد داده شدهدیگران ساده تر هستند 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 (in در این موردداده های گردش تجاری)؛
• محدوده x 1، …x n، یعنی اندازه فضای خرده فروشی؛
• هم مقادیر شناخته شده و هم ناشناخته x، که برای آن باید اندازه گردش مالی را بدانید (برای اطلاعات در مورد مکان آنها در کاربرگ، به زیر مراجعه کنید).

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

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

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

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

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

تابع پیش بینی

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

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

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

سیستم m معادلات خطیبا n مجهول به شکل زیر است:

سه مورد ممکن است: m n حالتی که m=n در پاراگراف های قبل در نظر گرفته شد. زمانی که م

اگر m>n و سیستم سازگار باشد، ماتریس A حداقل دارای m - n ردیف وابسته خطی است. در اینجا راه حل را می توان با انتخاب n معادله مستقل خطی (در صورت وجود) و اعمال فرمول X = A -1 CV، یعنی کاهش مسئله به معادله ای که قبلاً حل شده است، به دست آورد. در این حالت، جواب به دست آمده همیشه معادلات m - n باقیمانده را برآورده می کند.

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

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

روش حداقل مربعات جبری روشی برای حل سیستم معادلات خطی است

با به حداقل رساندن هنجار اقلیدسی

تبر؟ ب > اطلاعات (1.2)

تجزیه و تحلیل داده های تجربی

اجازه دهید آزمایشی را در نظر بگیریم که طی آن در لحظاتی از زمان

برای مثال، دمای Q(t) اندازه گیری می شود. اجازه دهید نتایج اندازه گیری توسط یک آرایه مشخص شود

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

P(t) = + + + ... +،

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

تقریب گاوس جبری اکسل

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

اگر P(t) را با عبارت آن جایگزین کنیم، دریافت می کنیم

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

اگر ماتریس m × n را وارد کنید A = ()، i = 1, 2..., m; j = 1، 2، ...، n، که در آن

I = 1، 2...، m; j = 1، 2، ...، n،

سپس برابری نوشتاری شکل خواهد گرفت

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

برای یک ماتریس جابجا شده، یک رابطه مشابه به این شکل است

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

در فرم ماتریسی این برابری را می توان به صورت بازنویسی کرد

A T x = A T B (1.3)

در اینجا A یک ماتریس مستطیلی m×n است. علاوه بر این، در مسائل تقریب داده ها، به عنوان یک قاعده، m> n. معادله (1.3) معادله نرمال نامیده می شود.

از همان ابتدا می‌توان با استفاده از هنجار بردارهای اقلیدسی، مسئله را به شکل ماتریس معادل نوشت:

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

2A T B + 2A T Ax

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

(A T A)x = (A T B).

این معادلات را معادلات عادی می نامند. اگر A یک ماتریس m× n باشد، A>A - n × n یک ماتریس است، یعنی. ماتریس یک معادله عادی همیشه یک ماتریس متقارن مربع است. علاوه بر این، دارای خاصیت قطعیت مثبت است به این معنا که (A>Ax, x) = (Ax, Ax) ? 0.

اظهار نظر. گاهی اوقات راه حل معادله ای به شکل (1.3) را جواب سیستم Ax = B می نامند که در آن A یک ماتریس مستطیلی m × n (m > n) با استفاده از روش حداقل مربعات است.

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

MNC در اکسل

الگوریتم زیر برای پیاده سازی OLS در اکسل فرض می کند که تمام داده های اولیه از قبل شناخته شده هستند. هر دو طرف معادله ماتریسی AЧX=B سیستم سمت چپ را در ماتریس جابجا شده سیستم А Т ضرب می کنیم:

A T AX=A T B

سپس هر دو طرف معادله سمت چپ را در ماتریس (A T A) -1 ضرب می کنیم. اگر این ماتریس وجود داشته باشد، سیستم تعریف می شود. با توجه به اینکه

(A T A) -1 *(A T A)=E، بدست می آوریم

X=(A T A) -1 A T B.

معادله ماتریسی به‌دست‌آمده، راه‌حلی برای سیستم m معادلات خطی با n مجهول برای m>n است.

بیایید کاربرد الگوریتم بالا را در نظر بگیریم مثال خاص.

مثال. اجازه دهید برای حل سیستم لازم باشد

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

نتایج محاسبات:

بردار X مورد نیاز در محدوده E11:E12 قرار دارد.

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

1. MOBR - برمی‌گرداند ماتریس معکوسبرای یک ماتریس ذخیره شده در یک آرایه.

نحو: MOBR (آرایه).

آرایه یک آرایه عددی با تعداد سطر و ستون مساوی است.

2. MULTIPULT - حاصلضرب ماتریس ها را برمی گرداند (ماتریس ها در آرایه ها ذخیره می شوند). نتیجه آرایه ای با تعداد ردیف های مشابه آرایه 1 و همان تعداد ستون با آرایه 2 است.

نحو: MULTIPLE(array1,array2).

Array1, array2 آرایه های قابل ضرب هستند.

پس از وارد کردن یک تابع در سلول سمت چپ بالای یک محدوده آرایه، آرایه را انتخاب کنید، با سلول حاوی فرمول شروع کنید، F2 را فشار دهید و سپس CTRL+SHIFT+ENTER را فشار دهید.

3. TRANSPORT - یک مجموعه عمودی از سلول ها را به یک سلول افقی یا بالعکس تبدیل می کند. در نتیجه استفاده از این تابع، آرایه ای ظاهر می شود که تعداد ردیف های آن برابر با تعداد ستون های آرایه اصلی و تعداد ستون ها برابر با تعداد ردیف های آرایه اولیه است.

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

محاسبه ضرایب رگرسیونبا استفاده از تابع انجام می شود

LINEST(مقادیر_y; مقادیر x; Const; آمار),

مقادیر_y- آرایه ای از مقادیر y،

مقادیر x- آرایه اختیاری از مقادیر ایکس، اگر آرایه ایکسحذف شده است، فرض می شود که این آرایه ای (1;2;3;...) با همان اندازه است. مقادیر_y,

Const- یک مقدار بولی که نشان می دهد آیا ثابت مورد نیاز است یا خیر ببرابر 0 بود Constمعنی دارد درست است، واقعییا حذف شده است، پس ببه روش معمول محاسبه می شود. اگر استدلال Constپس نادرست است ب 0 و مقادیر در نظر گرفته شده است آطوری انتخاب می شوند که رابطه محقق شود y=ax.

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

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

برای محاسبه ضریب همبستگیتابع استفاده می شود

CORREL(آرایه 1;آرایه 2),

برگرداندن مقادیر ضریب همبستگی، که در آن آرایه 1- آرایه ای از مقادیر y, آرایه 2- آرایه ای از مقادیر ایکس. آرایه 1و آرایه 2باید یک اندازه باشد

مثال 1. اعتیاد y(ایکس) در جدول ارائه شده است. ساختن خط رگرسیونو محاسبه کنید ضریب همبستگی.

بیایید یک جدول از مقادیر را در یک برگه MS Excel وارد کنیم و یک نمودار پراکندگی بسازیم. کاربرگ به شکل نشان داده شده در شکل خواهد بود. 2.

به منظور محاسبه مقادیر ضرایب رگرسیون آو بسلول ها را انتخاب کنید A7:B7،بیایید به جادوگر تابع و در دسته بندی برویم آمارییک تابع را انتخاب کنید LINEST. بیایید کادر محاوره ای را که در شکل نشان داده شده است پر کنیم. 3 و فشار دهید خوب.

در نتیجه، مقدار محاسبه شده فقط در سلول ظاهر می شود A6(شکل 4). برای اینکه مقدار در سلول ظاهر شود B6باید وارد حالت ویرایش شوید (کلید F2)و سپس کلید ترکیبی را فشار دهید CTRL+SHIFT+ENTER.

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

C7=CORREL(B3:J3;B2:J2).

دانستن ضرایب رگرسیون آو ببیایید مقادیر تابع را محاسبه کنیم y=تبر+ببرای داده شده است ایکس. برای این کار فرمول را معرفی می کنیم

B5=$A$7*B2+$B$7

و آن را در محدوده کپی کنید C5:J5(شکل 5).

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

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

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

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

4.2. با استفاده از خط روند.

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

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

یک وابستگی تقریبی را انتخاب کرده و بسازید. نمودارهایی از وابستگی های تحلیلی جدولی و انتخابی بسازید.

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

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

کادر محاوره ای که ظاهر می شود به شما امکان می دهد یک رابطه تقریبی ایجاد کنید.

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

در مورد دوم (شکل 12) پارامترهای ساخت و ساز تعیین می شوند:

· نام وابستگی تقریبی.

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

آیا نقطه تقاطع یک منحنی را با یک خط مستقیم نشان دهیم y=const;

· نشان دادن تابع تقریبی در نمودار یا نه (گزینه نشان دادن معادله در نمودار).

· آیا مقدار انحراف معیار روی نمودار قرار داده شود یا نه (گزینه قرار دادن مقدار پایایی تقریبی روی نمودار).

اجازه دهید یک چند جمله ای درجه دوم را به عنوان یک وابستگی تقریبی انتخاب کنیم (شکل 11) و معادله ای را که این چند جمله ای را توصیف می کند روی یک نمودار نمایش دهیم (شکل 12). نمودار حاصل در شکل نشان داده شده است. 13.

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

خطی y=a∙x+ب,

لگاریتمی y=a∙ln(ایکس)+ب,

· نمایی y=a∙e ب,

· آرام بخش y=a∙x ب,

چند جمله ای y=a∙x 2 +b∙x+ج, y=a∙x 3 +b∙x 2 +c∙x+dو غیره، تا یک چند جمله ای از درجه 6 شامل،

· فیلتراسیون خطی.

4.3. استفاده از ابزار تحلیل گزینه ها: یافتن راه حل.

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

مثال 3. در نتیجه آزمایش، وابستگی z(t) به دست آمد که در جدول ارائه شده است

ضرایب وابستگی را انتخاب کنید Z(t)=در 4 +Bt 3 +Ct 2 +Dt+Kروش حداقل مربعات

این مسئله معادل مسئله یافتن حداقل یک تابع از پنج متغیر است

بیایید روند حل مسئله بهینه سازی را در نظر بگیریم (شکل 14).

اجازه دهید ارزش ها آ, که در, با, Dو بهدر سلول ها ذخیره می شود A7:E7. بیایید مقادیر نظری تابع را محاسبه کنیم ز(تی)=در 4 + Bt 3 + Ct 2 + Dt + Kبرای داده شده است تی(B2:J2). برای انجام این کار، در سلول B4مقدار تابع را در نقطه اول وارد کنید (سلول B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

بیایید این فرمول را در محدوده کپی کنیم C4:J4و مقدار مورد انتظار تابع را در نقاطی که ابسیساها در سلول ها ذخیره می شوند، بدست آورید B2:J2.

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

B5=(B4-B3)^2،

و آن را در محدوده کپی کنید C5:J5. در یک سلول F7مجذور مجذور کل خطا (10) را ذخیره می کنیم. برای این کار فرمول را وارد کنید:

F7 = SUM (B5:J5).

بیایید از دستور استفاده کنیم Service®به دنبال راه حل باشیدو مشکل بهینه سازی را بدون محدودیت حل کنید. بیایید فیلدهای ورودی را در کادر محاوره ای نشان داده شده در شکل بر این اساس پر کنیم. 14 و دکمه را فشار دهید اجرا کردن. اگر راه حلی پیدا شد، پنجره نشان داده شده در شکل. 15.

نتیجه بلوک تصمیم به سلول ها خروجی خواهد شد A7:E7مقادیر پارامترکارکرد ز(تی)=در 4 + Bt 3 + Ct 2 + Dt + K. در سلول ها B4:J4ما گرفتیم مقدار تابع مورد انتظاردر نقاط شروع در یک سلول F7ذخیره خواهد شد مجموع خطای مربع.

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

برنج. 17 کاربرگ MS Excel را پس از انجام محاسبات نمایش می دهد.