روش تعیین حداقل مربعات ضرایب. تقریب داده های تجربی

جدول 4

x n

y n

جدول 5

y من

0,705

0,495

0,426

0,357

0,368

0,406

0,549

0,768

جدول 6

№0

ایکس

0,1

0,2

0,3

0,4

0,5

0,6

0,7

0,8

y

3,030

3,142

3,358

3,463

3,772

3,251

3,170

3,665

№ 1

3,314

3,278

3,262

3,292

3,332

3,397

3,487

3,563

№ 2

1,045

1,162

1,264

1,172

1,070

0,898

0,656

0,344

№ 3

6,715

6,735

6,750

6,741

6,645

6,639

6,647

6,612

№ 4

2,325

2,515

2,638

2,700

2,696

2,626

2,491

2,291

№ 5

1.752

1,762

1,777

1,797

1,821

1,850

1,884

1,944

№ 6

1,924

1,710

1,525

1,370

1,264

1,190

1,148

1,127

№ 7

1,025

1,144

1,336

1,419

1,479

1,530

1,568

1,248

№ 8

5,785

5,685

5,605

5,545

5,505

5,480

5,495

5,510

№ 9

4,052

4,092

4,152

4,234

4,338

4,468

4,599

مثال.

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

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

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

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

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

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

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

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

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

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

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

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

راه حل.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

y = y(x)

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

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

y=mx+b

که در آن m مماس شیب رگرسیون خطی بر محور x است. ب - مختصات نقطه تقاطع رگرسیون خطی با محور y.

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

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

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

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

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

y = c ln(x) + b

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

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

y = cxb

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

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

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

y=cebx

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

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

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

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

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

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

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

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

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

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

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

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

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

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

مقدار قابلیت اطمینان تقریبی R2 را در ناحیه نمودار نمایش دهید، که برای آن باید چک باکس را فعال کنید و مقدار قابلیت اطمینان تقریبی (R^2) را روی نمودار قرار دهید.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

از معایب می توان به نکات زیر اشاره کرد:

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

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

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

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

شما نمی توانید خطوط روند را به سری های داده در نمودارهای 3-D، Standard، Radar، Pie و Donut اضافه کنید.

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

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

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

روند؛

• SLOPE و CUT.

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

LGRFPحدود

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

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

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

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

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

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

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

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

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

وظیفه 1

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

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

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

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

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

راه حل مشکل

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

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

نمودار ساخته شده را فعال می کنیم و با استفاده از روشی که در بالا توضیح داده شد، پس از انتخاب نوع خط روند در کادر محاوره ای خط روند (نگاه کنید به شکل 1)، به طور متناوب خطوط روند خطی، درجه دوم و مکعبی را به نمودار اضافه می کنیم. در همان کادر محاوره‌ای، تب پارامترها را باز کنید (شکل 2 را ببینید)، در نام فیلد منحنی تقریبی (صاف) نام روندی که باید اضافه شود را وارد کنید و در قسمت Forecast forward for: periods را تنظیم کنید. مقدار 2، زیرا قرار است پیش بینی سود برای دو سال آینده انجام شود. برای نمایش معادله رگرسیون و مقدار قابلیت اطمینان تقریبی R2 در ناحیه نمودار، کادرهای بررسی Show the equation on the screen را فعال کنید و مقدار قابلیت اطمینان تقریبی (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 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

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

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

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

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

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

راه حل مشکل

توجه داشته باشید که برخلاف توابع TREND و GROW، هیچ یک از توابع ذکر شده در بالا (SLOPE، INTERCEPTION، 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 تعیین می شود. و محتوای آن به این صورت خواهد بود: = INTERCEPT(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" کلیک کنید و نتایج را تجزیه و تحلیل کنید.

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

چرا مربع های خطا به حداقل می رسند، نه خود خطاها؟ واقعیت این است که در بیشتر موارد خطاها در هر دو جهت رخ می دهد: برآورد می تواند بزرگتر از اندازه گیری یا کمتر از آن باشد. اگر خطاهایی را با علائم مختلف اضافه کنیم، آنها یکدیگر را خنثی می کنند و در نتیجه، مجموع تصور نادرستی از کیفیت برآورد به ما می دهد. غالباً برای اینکه تخمین نهایی با مقادیر اندازه گیری شده دارای ابعاد یکسانی باشد، از مجموع مجذور خطاها جذر جذر گرفته می شود.

LSM در ریاضیات، به ویژه - در نظریه احتمالات و آمار ریاضی استفاده می شود. این روش بیشترین کاربرد را در مشکلات فیلترینگ دارد، زمانی که لازم است سیگنال مفید را از نویز قرار گرفته بر روی آن جدا کنید.

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

من به چند برنامه بسیار غیرمنتظره دیگر از LSM رسیدم که می خواهم در این مقاله در مورد آنها صحبت کنم.

MNC ها و اشتباهات املایی

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

من مشکل مشابهی داشتم: دو پایگاه داده با آدرس خانه های مسکو وجود داشت و آنها باید در یکی ترکیب می شدند. اما آدرس ها به سبک دیگری نوشته شده بودند. در یک پایگاه داده استاندارد KLADR (طبقه بندی کننده آدرس همه روسی) وجود داشت، به عنوان مثال: "BABUSHKINA PILOT UL., D10K3". و در پایگاه داده دیگری یک سبک پستی وجود داشت، به عنوان مثال: "St. خلبان بابوشکین، خانه 10 ساختمان 3. به نظر می رسد که در هر دو مورد خطایی وجود ندارد و خودکار کردن فرآیند فوق العاده دشوار است (هر پایگاه داده 40000 رکورد دارد!). اگرچه اشتباهات تایپی به اندازه کافی وجود داشت ... چگونه به رایانه بفهمانیم که 2 آدرس بالا متعلق به یک خانه هستند؟ اینجا جایی بود که MNC برای من مفید بود.

من چه کرده ام؟ با یافتن حرف بعدی در آدرس اول، به دنبال همان نامه در آدرس دوم گشتم. اگر هر دو در یک مکان بودند، پس من خطای آن حرف را 0 فرض می‌کردم. اگر در موقعیت‌های مجاور بودند، خطا 1 بود. اگر 2 موقعیت جابه‌جایی داشت، خطا 2 بود و غیره. اگر در آدرس دیگر چنین حرفی وجود نداشت، خطا n+1 در نظر گرفته می شد که n تعداد حروف در آدرس اول است. بنابراین، من مجموع مربعات خطاها را محاسبه کردم و رکوردهایی را که در آنها این مجموع حداقل بود، به هم وصل کردم.

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

OLS و جستجو بر اساس تصاویر، چهره ها و نقشه ها

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

یک عکس:

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

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

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

عکس: pixabay.com

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

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

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

بیان مسئله در یک مثال خاص

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

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

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

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

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

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

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

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

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

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

تابع FORECAST

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

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

حداقل مربعات یک روش ریاضی برای ساخت یک معادله خطی است که با یافتن مقادیر a و b، ضرایب در معادله خط مستقیم، به بهترین وجه با مجموعه ای از جفت های مرتب شده مطابقت دارد. هدف روش حداقل مربعات به حداقل رساندن مجذور مجذور کل خطا بین مقادیر y و ŷ است. اگر برای هر نقطه خطای ŷ را تعیین کنیم، روش حداقل مربعات به حداقل می رسد:

که در آن n = تعداد جفت های مرتب شده در اطراف خط. مرتبط ترین با داده ها

این مفهوم در شکل نشان داده شده است

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

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

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

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

a = y cf - bx cf

که در آن x cf مقدار متوسط ​​x، متغیر مستقل، y cf مقدار میانگین y، متغیر مستقل است.

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

منحنی اثر برای مثال وان حمام ما با معادله زیر ارائه می شود:

از آنجایی که معادله ما دارای شیب مثبت 0.976 است، مرد اثبات می کند که تعداد آیتم های روی میز در طول زمان با نرخ متوسط ​​1 مورد در ماه افزایش می یابد. نمودار منحنی اثر را با جفت های مرتب شده نشان می دهد.

تعداد مورد انتظار اقلام برای نیم سال آینده (ماه 16) به شرح زیر محاسبه می شود:

ŷ = 5.13 + 0.976x = 5.13 + 0.976(16) ~ 20.7 = 21 مورد

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

تابع TREND در اکسل

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

TREND (مقادیر Y شناخته شده؛ مقادیر X شناخته شده؛ مقادیر X جدید؛ const)

مقادیر شناخته شده Y - آرایه ای از متغیرهای وابسته، در مورد ما، تعداد موارد روی جدول

مقادیر شناخته شده X - آرایه ای از متغیرهای مستقل، در مورد ما یک ماه است

مقادیر X جدید - مقادیر جدید X (ماه) که برای آنها تابع TRENDمقدار مورد انتظار متغیرهای وابسته (تعداد موارد) را برمی گرداند.

const - اختیاری. یک مقدار بولی که مشخص می کند آیا ثابت b باید 0 باشد یا خیر.

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