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

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

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

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

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

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

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

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

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

جوهر روش

داده های جدول را می توان در صفحه دکارتی به عنوان نقاط 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 * که یک مدل رگرسیونی برای مثال مورد نظر است، مناسب است. البته، به شما اجازه نمی دهد که نتیجه دقیق را پیدا کنید، اما به شما کمک می کند تا تصور کنید که آیا خرید اعتباری برای یک فروشگاه در یک منطقه خاص نتیجه می دهد یا خیر.

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

اکسل تابعی برای محاسبه مقدار OLS دارد. شکل زیر را دارد: "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 داده شده در یک ستون یا یک ردیف قرار گیرد.

تابع FORECAST

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

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

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

چگونه افزونه Find Solution را فعال کنیم

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

1. به منوی "File" بروید و "Excel Options" را انتخاب کنید.

2. در پنجره ظاهر شده «Search for a solution» را انتخاب کرده و روی «go» کلیک کنید.

3. در پنجره بعدی، جلوی آیتم «جستجوی راه حل» تیک زده و «OK» را بزنید.

4. افزونه فعال شده است - اکنون می توانید آن را در آیتم منوی "داده" پیدا کنید.

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

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

فرض کنید پس از انجام آزمایشی که در آن تأثیر مقدار X را بر مقدار Y مطالعه کردیم، یک مجموعه داده داریم.

ما می خواهیم این تأثیر را به صورت ریاضی توصیف کنیم تا بعداً بتوانیم از این فرمول استفاده کنیم و بدانیم که اگر مقدار X را اینقدر تغییر دهیم ، مقدار Y را فلان و فلان بدست می آوریم ...

من یک مثال فوق العاده ساده می زنم (شکل را ببینید).

واضح است که نقاط یکی پس از دیگری مانند یک خط مستقیم قرار دارند و بنابراین ما با خیال راحت فرض می کنیم که وابستگی ما با یک تابع خطی y = kx + b توصیف می شود. در عین حال، ما قطعاً مطمئن هستیم که وقتی X برابر با صفر است، مقدار Y نیز برابر با صفر است. این بدان معنی است که تابع توصیف کننده وابستگی حتی ساده تر خواهد بود: y = kx (برنامه درسی مدرسه را به خاطر بسپارید).

به طور کلی باید ضریب k را پیدا کنیم. این کاری است که ما با آن انجام خواهیم داد OLS با استفاده از افزونه "جستجوی راه حل".

این روش شامل این واقعیت است که (در اینجا - توجه: باید در مورد آن فکر کنید) مجموع مربعات تفاوت بین مقادیر تجربی به دست آمده و مقادیر محاسبه شده مربوطه حداقل بود. یعنی وقتی X1 = 1 مقدار واقعی اندازه گیری شده Y1 = 4.6، و y1 = f (x1) محاسبه شده 4 باشد، مجذور اختلاف خواهد بود (y1-Y1) ^ 2 = (4-4.6) ^ 2 = 0.36 ... با موارد زیر یکسان: وقتی X2 = 2، مقدار واقعی اندازه گیری شده Y2 = 8.1، و y2 محاسبه شده 8 است، مجذور اختلاف خواهد بود (y2-Y2) ^ 2 = (8-8.1) ^ 2 = 0.01 . و مجموع همه این مربع ها باید تا حد امکان کوچک باشد.

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

استفاده از افزونه جستجوی راه حل

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

2. در سلول A1 مقدار "1" را وارد کنید. این واحد اولین تقریب مقدار واقعی ضریب (k) وابستگی عملکردی ما y = kx خواهد بود.

3. در ستون B، مقادیر پارامتر X، در ستون C - مقادیر پارامتر Y را داریم. در سلول های ستون D، فرمول را وارد می کنیم: "ضریب k ضرب در مقدار از X". به عنوان مثال، در سلول D1 "= A1 * B1" را وارد می کنیم، در سلول D2 "= A1 * B2" را وارد می کنیم و غیره.

4. ما معتقدیم که ضریب k برابر با یک استو تابع f (x) = y = 1 * x اولین تقریب حل ما است. ما می توانیم مجموع مجذورات تفاوت بین مقادیر اندازه گیری شده Y و مقادیر محاسبه شده با فرمول y = 1 * x را محاسبه کنیم. ما می‌توانیم همه این کارها را به صورت دستی با وارد کردن منابع سلولی مناسب به فرمول انجام دهیم: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... و غیره. در نهایت ما هستیم اشتباه می کنیم و می فهمیم که زمان زیادی را از دست داده ایم.در اکسل برای محاسبه مجموع مجذور تفاوت ها یک فرمول مخصوص SUMKVRAZN وجود دارد که همه کارها را برای ما انجام می دهد.آن را در سلول A2 وارد کنید و مقدار اولیه را تنظیم کنید. داده ها: محدوده مقادیر اندازه گیری شده Y (ستون C) و محدوده مقادیر Y محاسبه شده (ستون D).

4. مجموع تفاوت های مربع ها محاسبه شده است - اکنون به برگه "داده ها" می رویم و "جستجوی راه حل" را انتخاب می کنیم.

5. در منوی ظاهر شده، سلول A1 (که دارای ضریب k است) را به عنوان سلولی که باید تغییر کند انتخاب کنید.

6. سلول A2 را به عنوان هدف انتخاب کنید و شرط "set برابر با حداقل مقدار" را تنظیم کنید. به یاد داشته باشید که این سلولی است که مجموع مجذورات تفاوت بین مقادیر محاسبه شده و اندازه گیری شده را محاسبه می کنیم و این مجموع باید حداقل باشد. روی "اجرا" کلیک کنید.

7. ضریب k انتخاب شده است. اکنون می توانید بررسی کنید که مقادیر محاسبه شده اکنون بسیار نزدیک به مقادیر اندازه گیری شده است.

P.S.

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

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

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

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

LINEST(مقادیر_y; مقادیر X; کنست; آمار),

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

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

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

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

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

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

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

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

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

y		0.5		1.5		2.5		3.5
ایکس		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

بیایید یک جدول از مقادیر را در یک برگه 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.

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

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

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

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

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

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

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

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

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

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

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

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

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

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

اینکه نقطه تلاقی یک منحنی را با خط مستقیم نشان دهیم یا نه y = ثابت;

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

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

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

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

خطی y=a ∙ x+ب,

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

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

قانون قدرت y=a ∙ x b,

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

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

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

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

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

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

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

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

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

اجازه دهید ارزش ها آ, V, با, دیو بهدر سلول ها ذخیره می شود 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 و دکمه را فشار دهید اجرا کردن... اگر راه حلی پیدا شد، پنجره نشان داده شده در شکل 1. 15.

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

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

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

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

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

LINEST(مقادیر_y; مقادیر X; کنست; آمار),

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

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

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

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

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

y		0.5		1.5		2.5		3.5
ایکس		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

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

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

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

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

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

برنج. 6

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

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

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

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

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

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

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

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

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

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

اینکه نقطه تلاقی یک منحنی را با خط مستقیم نشان دهیم یا نه y = ثابت;

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

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

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

خطی y=a ∙ x+ب,

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

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

قانون قدرت y=a ∙ x b,

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

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

4.3. استفاده از بلوک تصمیم گیری

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

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

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

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

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

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

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

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

B5 = (B4-B3) ^ 2،

F7 = SUM (B5: J5).

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

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

5. مراجع

1. Alekseev ER, Chesnokova OV, حل مسائل ریاضیات محاسباتی در بسته های Mathcad12, MATLAB7, Maple9. - NT Press، 2006. - 596s. : مریض - (آموزش)

2. Alekseev E.R., Chesnokova O.V., E.A. رودچنکو، اسکیلب، حل مسائل مهندسی و ریاضی. – M., BINOM, 2008. – 260s.

3. Berezin IS, Zhidkov NP, Methods of Computation.-Moscow: Nauka, 1966.-632p.

4. Garnaev A.Yu.، استفاده از MS EXCEL و VBA در اقتصاد و امور مالی. - SPb .: BHV - پترزبورگ، 1999. - 332s.

5. Demidovich BP، Maron I A.، Shuvalova VZ، روش های عددی تجزیه و تحلیل - M.: Nauka، 1967. - 368p.

6. Korn G., Korn T., کتابچه راهنمای ریاضیات برای دانشمندان و مهندسان - M., 1970, 720p.

7. Alekseev E.R., Chesnokova O.V. دستورالعمل های روشیبه کارهای آزمایشگاهی در MS EXCEL. برای دانش آموزان تمام رشته ها Donetsk, DonNTU, 2004.112 p.

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

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

فعال کردن افزونه Solver

برای استفاده از OLS در اکسل، باید افزونه را فعال کنید "جستجوی راه حل"که به صورت پیش فرض غیرفعال است.

حالا تابع یافتن راه حلدر اکسل فعال شده و ابزارهای آن بر روی نوار ظاهر شده است.

شرایط مشکل

اجازه دهید کاربرد OLS را با یک مثال خاص توضیح دهیم. ما دو ردیف اعداد داریم ایکس و y که دنباله آن در تصویر زیر نشان داده شده است.

تابع می تواند این وابستگی را با دقت بیشتری توصیف کند:

علاوه بر این، مشخص است که برای x = 0 yنیز برابر است 0 ... از همین رو معادله داده شدهرا می توان به عنوان وابستگی توصیف کرد y = nx .

ما باید حداقل مجموع مجذورهای اختلاف را پیدا کنیم.

راه حل

بیایید به توضیح کاربرد مستقیم روش برویم.

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