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

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

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

ابتدا ما درک خواهیم کرد که چگونه این ساختار فوق را روشن کنیم.

1. به منوی «فایل» بروید و مورد تنظیمات Excel را انتخاب کنید.

2. در پنجره ای که ظاهر می شود، "راه حل راه حل" را انتخاب کنید و روی "GO" کلیک کنید.

3. در پنجره بعدی، ما یک علامت را در مقابل "جستجو راه حل" قرار داده ایم و روی OK کلیک کنید.

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

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

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

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

ما می خواهیم این اثر را برای توصیف ریاضی، برای استفاده از این فرمول برای استفاده و بدانیم که اگر مقدار x را تغییر دهیم، ارزش Y را دریافت می کنیم ...

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

واضح است که نقاط برای یکدیگر به عنوان یک خط مستقیم واقع شده اند و بنابراین ما به شدت فرض می کنیم که وابستگی ما توسط تابع خطی Y \u003d KX + B توصیف شده است. در عین حال، ما دقیقا مطمئن هستیم که با X برابر با صفر است، مقدار Y نیز صفر است. بنابراین، یک تابع توصیف اعتیاد حتی ساده تر خواهد بود: Y \u003d KX (به یاد داشته باشید برنامه مدرسه).

به طور کلی، ما باید ضریب K را پیدا کنیم. این چیزی است که ما انجام خواهیم داد موز با استفاده از راه حل افزودنی برای راه حل ها.

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

بنابراین، ما به آموزش در مورد استفاده از MNK ادامه خواهیم داد افزودنی اکسل "جستجوی راه حل" .

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

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

2. در سلول A1، ما ارزش "1" را معرفی می کنیم. این واحد اولین تقریب به ارزش واقعی ضریب (K) وابستگی عملکردی ما Y \u003d KX خواهد بود.

3. در ستون B، ما مقادیر پارامتر X را در ستون C حل کردیم - مقادیر پارامتر y. در سلول های ستون، ما فرمول را معرفی می کنیم: "Ceffient K ضرب به مقدار x. به عنوان مثال، در سلول D1، ما وارد "\u003d A1 * B1"، در سلول D2 ما وارد "\u003d A1 * B2" و غیره

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

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

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

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

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

P.S.

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

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

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

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

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

اغلب، یک فرم خطی برای توصیف ارتباط آماری علائم استفاده می شود. هشدار به ارتباطات خطی به دلیل تفسیر اقتصادی روشن از پارامترهای آن، محدود شده توسط متغیرها توسط متغیرها و در اغلب موارد، فرم های غیر خطی ارتباطات برای محاسبات (توسط لگاریتم یا جایگزینی متغیرها) به شکل خطی تبدیل می شود.
در مورد یک پیوند جفتی خطی، معادله رگرسیون فرم را می گیرد: Y I \u003d a + b · x i + u i. پارامترهای این معادله A و B بر اساس مشاهدات آماری X و Y تخمین زده می شود. نتیجه چنین ارزیابی معادله است:، جایی که برآورد پارامترهای A و B، - مقدار ویژگی نتیجه (متغیر) به دست آمده از معادله رگرسیون (مقدار محاسبه شده).

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

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

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

حداقل روش مربع
حداکثر روش درست (برای مدل رگرسیون خطی کلاسیک معمولی، عادی بودن بقایای رگرسیون به تعویق افتاده است).
روش تعمیمي مربع هاي کوچکتر OMNA در مورد خودكارآمدي خطاها و در مورد هتروسواستاسيون مورد استفاده قرار مي گيرد.
روش کوچکترین مربعات معلق (یک مورد خاص OMNA با باقی مانده های ناهموار).

ما ماهیت را نشان می دهیم کلاسیک کوچکترین روش مربع گرافیکی. برای انجام این کار، ما یک برنامه نقطه ای را با توجه به مشاهدات (X I، Y I، I \u003d 1؛ N) در یک سیستم مختصات مستطیلی ساختیم (چنین نمودار نقطه ای میدان همبستگی نامیده می شود). ما سعی خواهیم کرد یک خط مستقیم را انتخاب کنیم که نزدیک به نقاط میدان همبستگی است. با توجه به روش کمترین مربعات، خط انتخاب شده است به طوری که مجموع مربعات فاصله عمودی بین نقاط میدان همبستگی و این خط حداقل خواهد بود.

رکورد ریاضی این کار: .
مقادیر Y I و X I \u003d 1 ... N به ما شناخته شده است، این داده های مشاهده شده است. در تابع S، آنها ثابت هستند. متغیرها در این ویژگی برآوردهای پارامتر مورد نظر هستند - ،. برای پیدا کردن حداقل توابع 2 متغیر، لازم است محاسبه مشتقات خصوصی این تابع برای هر یک از پارامترها و معادل آن صفر، I.E. .
در نتیجه، ما یک سیستم از 2 معادلات خطی طبیعی را به دست می آوریم:
حل این سیستم، ما برآوردهای پارامتر مورد نظر را پیدا خواهیم کرد:

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

برآورد تنگی ارتباط بین نشانه ها این کار با استفاده از ضریب همبستگی جفتی خطی - R X، Y انجام می شود. این را می توان با فرمول محاسبه کرد: . علاوه بر این، ضریب همبستگی جفتی خطی را می توان از طریق ضریب رگرسیون B تعیین کرد: .
محدوده مقادیر مجاز ضریب خطی همبستگی جفتی از -1 تا +1. علامت ضریب همبستگی نشان دهنده جهت ارتباطات است. اگر r x، y\u003e 0، سپس اتصال مستقیم است؛ اگر r x، y<0, то связь обратная.
اگر این ضریب نزدیک به یک باشد، اتصال بین ویژگی ها را می توان به عنوان خطی کاملا نزدیک تفسیر کرد. اگر ماژول آن برابر یک واحد Ê r x، y \u003d 1 باشد، اتصال بین علائم خطی عملکردی است. اگر علائم X و Y به طور خطی مستقل باشند، پس از آن R X، Y نزدیک به 0 است.
برای محاسبه R X، Y همچنین می توانید از جدول 1 استفاده کنید.

برای ارزیابی کیفیت معادله رگرسیون به دست آمده، ضریب تعیین نظری محاسبه می شود - 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 \u003d r 2 yx.

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

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

لینن(value_y; value_x.; کندن; آمار),

value_y - آرایه از ارزش y

value_x.- آرایه اختیاری از ارزش ها ایکس.اگر یک آرایه باشد h. حذف شده است، پس فرض می شود که این آرایه (1؛ 2، 3؛ ...) از همان اندازه به عنوان value_y,

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

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

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

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

کورل(عظیم 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، به کارشناسی ارشد وظایف و در رده اموزشی یک ویژگی را انتخاب کنید لینن. کادر محاوره ای ظاهر شده را پر کنید همانطور که در شکل نشان داده شده است. 3 و کلیک کنید خوب..

در نتیجه، مقدار محاسبه شده تنها در سلول ظاهر می شود A6 (شکل 4). به منظور ارزش در سلول ظاهر می شود B6. شما باید حالت ویرایش را وارد کنید (کلید F2)و سپس ترکیب کلید را فشار دهید Ctrl + Shift + Enter.

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

C7 \u003d Corvela (B3: J3؛ B2: J2).

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

B5 \u003d $ 7 * b2 + $ b $ 7 دلار

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

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

شکل. 6

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

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

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

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

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

مثال 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

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

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

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

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

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

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

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

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

· نشان دادن اینکه نقطه تقاطع منحنی با یک خط مستقیم y \u003d const;

· نمایش یک تابع تقریبی بر روی یک نمودار یا نه (پارامتر برای نشان دادن معادله بر روی نمودار)؛

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

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

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

· خطی y.=∙ x.+ب,

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

· نمایندگی y.=∙ e b,

· قدرت y.=∙ x b,

· چندجملهای y.=∙ x. 2 +b ∙ X.+c., y.=∙ 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) \u003d در 4 + bt 3 + ct 2 + dt + k حداقل مربعات روش

این وظیفه معادل وظیفه یافتن حداقل عملکرد پنج متغیر است.

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

اجازه دهید معانی را بگذار ولی, که در, از جانب, D. و به ذخیره شده در سلول ها A7: E7.. مقادیر نظری تابع را محاسبه کنید Z.(t.)=در 4 + BT 3 + CT 2 + DT + K برای مشخص شده t.(B2: J2.). برای انجام این کار در سلول B4 ما مقدار تابع را در نقطه اول وارد می کنیم (سلول B2):

B4 \u003d $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E 7 دلار.

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

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

B5 \u003d (B4-B3) ^ 2،

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

F7 \u003d مقادیر (B5: J5).

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

نتیجه بلوک قاطع نتیجه گیری در سلول خواهد بود A7: E7.مقادیر پارامتر کارکرد Z.(t.)=در 4 + BT 3 + CT 2 + DT + K. در سلول ها B4: J4. دريافت كردن تابع ارزش انتظار می رود در نقاط شروع. در سلول F7 ذخیره خواهد شد خطای کل درجه دوم.

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

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

5. فهرست منابع

1. Alekseev e.r.، Chesnokova O.V.، حل مشکلات محاسبات ریاضیات در MathCAD12 بسته ها، MATLAB7، Maple9. - NT Press، 2006.-596c. : ایل - (آموزش)

2. Alekseev e.r.، Chesnokova O.V.، E.A. Rudchenko، Scilab، حل وظایف مهندسی و ریاضی. -m، Binom، 2008.-260s.

3. Berezin I.S.، Lodine N.P.، روش های محاسبات. - m.: m.: علم، 1966.-632c.

4. Garnaev A.u.، با استفاده از MS اکسل و VBA در اقتصاد و مالی. - SPB: BHV - پترزبورگ، 1999.-332c.

5. Demidovich B.P.، Maron و A.، Shuvalova v.Z.، روش های عددی تجزیه و تحلیل. - m.: علم، 1967. -368c.

6. ذرت G.، Korn T.، دایرکتوری ریاضی برای دانشمندان و مهندسان. - متر، 1970، 720s.

7. Alekseev e.r.، Chesnokova O.V. دستورالعمل های روشنی برای انجام کار آزمایشگاهی در MS اکسل. برای دانش آموزان از همه تخصص ها. دونتسک، دوناتو، 2004. 112 پ.

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

تنظیم وظیفه در یک مثال خاص

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

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

لازم است پیش بینی کنیم که تجارت (Y) در فروشگاه خواهد بود، اگر او یک یا چند منطقه خرید داشته باشد. بدیهی است، تابع y \u003d f (x) در حال افزایش است، زیرا HyperMarket محصولات بیشتری را از یک غرفه به فروش می رساند.

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

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

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

ماهیت روش

داده های جدول را می توان بر روی یک هواپیما دپارتمان به شکل نقاط M 1 (x 1، y 1)، ... m n (x n، y n) نشان داده شود. در حال حاضر راه حل مشکل به انتخاب تابع تقریبی y \u003d f (x)، که دارای یک گراف است، عبور می کند، به عنوان نزدیک به نقاط M 1، M 2، .. M n.

البته، امکان استفاده از چندجملهای درجه بالایی وجود دارد، اما این گزینه نه تنها مشکل تر است، بلکه به سادگی نادرست است، زیرا این روند اصلی را منعکس نمی کند که لازم است تشخیص دهد. راه حل معقول ترین جستجو برای مستقیم Y \u003d ax + b است که بهترین داده های تجربی را به ارمغان می آورد و دقیق تر ضرایب - a و b.

برآورد دقت

در هر تقریبی، اهمیت خاصی برای ارزیابی دقت آن به دست می آورد. من تفاوت (انحراف) بین مقادیر عملکردی و تجربی برای نقطه X I، I.E. E I \u003d Y I - F (X I) را نشان می دهم.

واضح است که مقدار انحراف می تواند برای تخمین دقت تقریبی استفاده شود، یعنی هنگامی که خط مستقیم را برای نمایندگی تقریبی x از Y انتخاب می کنید، باید یکی را انتخاب کنید که کوچکترین مقدار مقدار EI در تمام نقاط مورد نظر. با این حال، همه چیز خیلی ساده نیست، زیرا همراه با انحرافات مثبت تقریبا حضور و منفی خواهد بود.

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

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

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

در رکورد ریاضی، این فرم را دارد:

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

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

برای انجام این کار، لازم است که با توجه به متغیرهای جدید A و B، به مشتقات صفر خصوصی معادل شود و سیستم ابتدایی را که شامل دو معادله با 2 گونه ناشناخته است، حل کند:

پس از تحولات ساده، از جمله تقسیم بر روی 2 و دستکاری با مبالغ، ما دریافت می کنیم:

به عنوان مثال، به عنوان مثال، با استفاده از دستگاه، یک نقطه ثابت با برخی از ضرایب A * و B * به دست می آوریم. این حداقل است، یعنی پیش بینی، که تجارت در یک منطقه خاص در فروشگاه خواهد بود، خط راست Y \u003d a * x + b * مناسب است، که یک مدل رگرسیون برای مثال است که مورد بحث قرار گرفته است. البته، اجازه نخواهد داد که یک نتیجه دقیق پیدا کند، اما به ایده ی اینکه آیا خرید یک منطقه بتنی خریداری خواهد شد، کمک خواهد کرد.

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

در "اکسل" یک تابع برای محاسبه ارزش MNA وجود دارد. این فرم زیر را دارد: "Trend" (شناخته شده. مقادیر Y؛ ارزش X؛ مقادیر جدید x؛ const.). فرمول محاسبه MNC را در اکسل به جدول ما اعمال کنید.

برای انجام این کار، در سلول که در آن نتیجه محاسبه مربع های کوچکتر باید در اکسل نمایش داده شود، علامت "\u003d" را معرفی می کنیم و عملکرد "Trend" را انتخاب می کنیم. در پنجره باز، زمینه های مربوطه را پر کنید، برجسته کردن:

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

علاوه بر این، فرمول دارای متغیر منطقی "const" است. اگر به فیلد مربوطه وارد شوید، این بدان معناست که محاسبات باید انجام شود، اعتقاد بر این است که B \u003d 0.

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

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

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

اگر طیف وسیعی از مقادیر شناخته شده متغیر Y را در یک ردیف یا ستون ترتیب دهید، هر خط (ستون) با مقادیر شناخته شده X توسط برنامه به عنوان یک متغیر جداگانه درک می شود.
اگر پنجره "Trend" یک محدوده را با شناخته شده X مشخص نکند، سپس در مورد استفاده از تابع در اکسل، این برنامه آن را به عنوان یک آرایه متشکل از اعداد صحیح در نظر می گیرد، تعداد آن مربوط به محدوده با مقادیر مشخص شده است از متغیر y
برای به دست آوردن مجموعه ای از ارزش های پیش بینی شده در خروج، بیان برای محاسبه روند باید به عنوان یک فرمول آرایه تجویز شود.
اگر مقادیر جدید X مشخص نشده باشند، عملکرد "Trend" آنها را برابر معروف می داند. اگر آنها مشخص نشده باشند، یک آرایه از 1 به عنوان یک استدلال گرفته شده است؛ 2؛ 3؛ 4؛ ...، که متناسب با یک محدوده با پارامترهای قبلا مشخص شده است.
محدوده ای حاوی مقادیر جدید X باید شامل ردیف های مشابه یا بیشتر یا ستون ها به عنوان محدوده ای با مقادیر مشخص Y باشد. به عبارت دیگر، باید با متغیرهای مستقل متناسب باشد.
در یک آرایه با مقادیر شناخته شده X، ممکن است چندین متغیر وجود داشته باشد. با این حال، اگر ما فقط در مورد آن صحبت کنیم، لازم است که محدوده ها با مقادیر مشخص شده X و Y متناسب باشند. در مورد متغیرهای مختلف، لازم است که محدوده با مقادیر مشخص شده از Y در یک ستون یا در یک ردیف قرار گیرد.

تابع پیش بینی شده

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

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