Excel дэх налуу энергийн хамгийн бага квадратын арга. Excel дэх хамгийн бага квадратын арга

Арга хамгийн бага квадратууд(LSM) нь судалж буй өгөгдлөөс сонгосон функцийн квадрат хазайлтын нийлбэрийг багасгахад суурилдаг. Энэ нийтлэлд бид шугаман функцийг ашиглан боломжтой өгөгдлийг ойролцоогоор тооцоолох болноy = а x + б .

Хамгийн бага квадрат арга(Англи) Энгийн Хамгийн бага Квадратууд , O.L.S.) нь үл мэдэгдэх параметрүүдийг тооцох үүднээс регрессийн шинжилгээний үндсэн аргуудын нэг юм регрессийн загварууддээжийн мэдээллийн дагуу.

Зөвхөн нэг хувьсагчаас хамаарах функцүүдийн ойролцооллыг авч үзье.

Шугаман: y=ax+b (энэ нийтлэл)
: y=a*Ln(x)+b
: y=a*x м
: y=a*EXP(b*x)+с
: y=ax 2 +bx+c

Анхаарна уу: 3-аас 6-р зэргийн олон гишүүнт ойртох тохиолдлыг энэ зүйлд авч үзнэ. Тригонометрийн олон гишүүнтийн ойролцооллыг энд авч үзнэ.

Шугаман хамаарал

Бид 2 хувьсагчийн хоорондын холболтыг сонирхож байна XТэгээд y. гэсэн таамаг бий y-аас хамаарна Xшугаман хуулийн дагуу y = сүх + б. Энэхүү харилцааны параметрүүдийг тодорхойлохын тулд судлаач ажиглалт хийсэн: x i-ийн утга бүрийн хувьд y i хэмжилт хийсэн (жишээ файлыг үзнэ үү). Үүний дагуу 20 хос утгыг (x i; y i) байг.

Жич:Хэрэв өөрчлөлтийн алхам бол X тогтмол байна, дараа нь барих тараах талбайнуудашиглаж болно, хэрэв үгүй бол та диаграмын төрлийг ашиглах хэрэгтэй Толбо .

Хувьсагчдын хоорондын хамаарал шугамантай ойролцоо байгаа нь диаграмаас тодорхой харагдаж байна. Олон шулуун шугамын аль нь хувьсагчдын хоорондын хамаарлыг хамгийн "зөв" дүрсэлж байгааг ойлгохын тулд шугамыг харьцуулах шалгуурыг тодорхойлох шаардлагатай.

Ийм шалгуурын хувьд бид дараах илэрхийллийг ашигладаг.

Хаана ŷ би = а * x i + б ; n - хос утгын тоо (бидний тохиолдолд n = 20)

Дээрх илэрхийлэл нь y i ба ŷ i-ийн ажиглагдсан утгуудын хоорондох квадрат зайны нийлбэр бөгөөд ихэвчлэн SSE гэж тэмдэглэгддэг ( нийлбэр -ийн Дөрвөлжин Алдаа (Үлдэгдэл), квадрат алдааны нийлбэр (үлдэгдэл)) .

Хамгийн бага квадрат аргаийм мөрийг сонгох явдал юм ŷ = сүх + б, үүний хувьд дээрх илэрхийлэл хамгийн бага утгыг авна.

Жич:Хоёр хэмжээст орон зай дахь аливаа шугамыг 2 параметрийн утгуудаар өвөрмөц байдлаар тодорхойлно. а (налуу) ба б (ээлж).

Квадрат зайны нийлбэр бага байх тусам харгалзах шугам нь боломжтой өгөгдөлд ойртох тусам x хувьсагчийн y-ийн утгыг урьдчилан таамаглахад ашиглаж болно гэж үздэг. Бодит байдал дээр хувьсагчдын хооронд хамаарал байхгүй эсвэл шугаман бус байсан ч OLS нь "хамгийн сайн" мөрийг сонгох нь тодорхой байна. Тиймээс хамгийн бага квадратын арга нь хувьсагчдын хооронд бодит хамаарал байгаа эсэх талаар юу ч хэлдэггүй бөгөөд энэ арга нь ийм функцийн параметрүүдийг сонгох боломжийг танд олгоно. а Тэгээд б , үүний хувьд дээрх илэрхийлэл хамгийн бага байна.

Маш төвөгтэй биш математик үйлдлүүдийг хийснээр (дэлгэрэнгүй мэдээллийг үзнэ үү) та параметрүүдийг тооцоолж болно а Тэгээд б :

Томъѐогоос харахад параметр а нь ковариацын харьцааг илэрхийлдэг ба MS EXCEL-д параметрийг тооцоолох А Та дараах томъёог ашиглаж болно (харна уу Шугаман хуудасны жишээ файл):

= КОВАР(B26:B45;C26:C45)/ DISP.G(B26:B45)эсвэл

= КОВАРИАНС.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Мөн параметрийг тооцоолох А = томьёог ашиглаж болно НААЛТ(C26:C45;B26:B45). Параметрийн хувьд б = томьёог ашиглана уу ХӨЛ(C26:C45;B26:B45) .

Эцэст нь LINEST() функц нь хоёр параметрийг нэг дор тооцоолох боломжийг олгодог. Томъёо оруулахын тулд ШУГАМ(C26:C45;B26:B45)Та дараалсан 2 нүдийг сонгоод дарах хэрэгтэй CTRL + SHIFT + ENTER(тухай нийтлэлийг үзнэ үү). Энэ утгыг зүүн нүдэнд буцаана А , баруун талд - б .

Анхаарна уу: Оролтод саад учруулахгүйн тулд массив томьёоТа INDEX() функцийг нэмэлтээр ашиглах хэрэгтэй болно. Томъёо = ИНДЕКС(ШУГАМ(C26:C45,B26:B45),1)эсвэл зүгээр л = ШУГАМ(C26:C45;B26:B45)шугамын налууг хариуцах параметрийг буцаана, i.e. А . Томъёо = ИНДЕКС(ШУГАМ(C26:C45,B26:B45),2) Y тэнхлэгтэй шугамын огтлолцлыг хариуцах параметрийг буцаана, i.e. б .

Параметрүүдийг тооцоолсны дараа, тархалтын диаграмта тохирох шугамыг зурж болно.

Хамгийн бага квадратын аргыг ашиглан шулуун шугам зурах өөр нэг арга бол график хэрэгсэл юм Тренд шугам. Үүнийг хийхийн тулд диаграммыг сонгоод цэснээс сонгоно уу Бүдүүвч таб, В бүлгийн дүн шинжилгээдарна уу Тренд шугам, дараа нь Шугаман ойртолт .

Харилцах цонхон дээрх "тэгшитгэлийг диаграммд харуулах" нүдийг чагталснаар дээрх параметрүүд диаграмм дахь утгатай тохирч байгаа эсэхийг шалгах боломжтой.

Анхаарна уу: Параметрүүд тохирохын тулд диаграммын төрөл нь . Гол нь диаграммыг бүтээхэд байгаа юм Хуваарь X тэнхлэгийн утгыг хэрэглэгч зааж өгөх боломжгүй (хэрэглэгч зөвхөн цэгүүдийн байршилд нөлөөлөхгүй шошгыг зааж өгөх боломжтой). X утгын оронд 1-р дарааллыг ашиглана; 2; 3; ... (ангиллыг дугаарлах). Тиймээс, хэрэв та барих юм бол чиг хандлагын шугамтөрлийн диаграм дээр Хуваарь, дараа нь X-ийн бодит утгуудын оронд энэ дарааллын утгуудыг ашиглах бөгөөд энэ нь буруу үр дүнд хүргэх болно (мэдээжийн хэрэг X-ийн бодит утгууд 1-р дараалалтай давхцахгүй бол); 2; 3; ...).

За, ажил дээрээ бид шалгалтад мэдээлсэн, нийтлэлийг гэртээ чуулганд зориулж бичсэн - одоо бид блог дээр бичиж болно. Өгөгдлөө боловсруулж байхдаа би Excel-д маш сонирхолтой бөгөөд шаардлагатай нэмэлт хэрэгслийн талаар бичихээс өөр аргагүй гэдгийг ойлгосон. Тиймээс нийтлэлийг энэ нэмэлт хэрэгсэлд зориулах бөгөөд би ашиглах жишээг ашиглан энэ талаар танд хэлэх болно хамгийн бага квадратын арга(LSM) туршилтын өгөгдлийг тайлбарлахдаа үл мэдэгдэх тэгшитгэлийн коэффициентийг хайх.

"Шийдлийн эрэл хайгуул" нэмэлтийг хэрхэн идэвхжүүлэх вэ

Эхлээд энэ нэмэлтийг хэрхэн идэвхжүүлэхийг олж мэдье.

1. "Файл" цэс рүү очоод "Excel Options"-ыг сонгоно уу.

2. Гарч ирэх цонхноос “Шийдлийн эрэл хайгуул”-ыг сонгоод “Явах” дээр дарна уу.

3. Дараагийн цонхонд "шийдэл хайх" гэдгийн хажууд байгаа нүдийг сонгоод "OK" дээр дарна уу.

4. Нэмэлт идэвхжсэн - одоо үүнийг "Өгөгдөл" цэсийн зүйлээс олж болно.

Хамгийн бага квадрат арга

Одоо товчхондоо хамгийн бага квадратын арга (LSM) мөн хаана ашиглах боломжтой.

Бид ямар нэгэн туршилт хийж, Y утгад X утгын нөлөөллийг судалсан мэдээллийн багцтай боллоо гэж бодъё.

Бид энэ нөлөөллийг математикийн хувьд тайлбарлахыг хүсч байгаа бөгөөд ингэснээр бид энэ томьёог ашиглаж, хэрэв бид X-ийн утгыг маш их хэмжээгээр өөрчилбөл Y-ийн утгыг авах болно гэдгийг мэдэх болно ...

Би маш энгийн жишээ авах болно (зураг харна уу).

Цэгүүд нь шулуун шугаманд байгаа мэт ар араасаа байрладаг нь ухаалаг хэрэг биш тул бидний хараат байдлыг тайлбарласан гэж бид итгэлтэй байна. шугаман функц y=kx+b. Үүний зэрэгцээ, X нь тэгтэй тэнцүү байх үед Y-ийн утга нь тэгтэй тэнцүү байна гэдэгт бид бүрэн итгэлтэй байна. Энэ нь хамаарлыг тайлбарлах функц илүү хялбар болно гэсэн үг юм: y=kx (сургуулийн сургалтын хөтөлбөрийг санаарай).

Ерөнхийдөө бид k коэффициентийг олох ёстой. Үүнийг бид хийх болно MNC "шийдлийн хайлт" нэмэлтийг ашиглан.

Арга нь (энд анхаарлаа хандуулаарай: та энэ талаар бодох хэрэгтэй) туршилтаар олж авсан болон холбогдох тооцоолсон утгуудын хоорондох зөрүүний квадратуудын нийлбэр хамгийн бага байна. Өөрөөр хэлбэл 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. Мөн эдгээр бүх квадратуудын нийлбэр нь аль болох бага байх ёстой.

Ингээд LSM ба ашиглах сургалтыг эхлүүлцгээе Excel-ийн нэмэлтүүд "шийдэл хайх" .

Шийдлийг олохын тулд нэмэлтийг ашиглаж байна

1. Хэрэв та "шийдэл хайх" нэмэлтийг идэвхжүүлээгүй бол цэг рүү буцна уу. "Шийдэл хайх" нэмэлтийг хэрхэн идэвхжүүлж, асаах вэ 🙂

2. А1 нүдэнд “1” утгыг оруулна. Энэ нэгж нь y=kx функциональ харилцааны коэффициент (k)-ын бодит утгын анхны ойролцоолсон үзүүлэлт болно.

3. В баганад бид 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... гэх мэт томъёонд харгалзах нүдний лавлагааг оруулснаар бид энэ бүхнийг гараар хийж болно. Эцэст нь бид алдаа гаргаж, бид маш их цаг үрсэн гэдгээ ойлгоорой. Excel дээр квадрат зөрүүний нийлбэрийг тооцоолохын тулд "SUMQUARRENT" гэсэн тусгай томьёо байдаг бөгөөд энэ нь бидний төлөө бүх зүйлийг хийх болно. Үүнийг A2 нүдэнд оруулан тохируулна уу. анхны өгөгдөл: хэмжсэн утгын хүрээ Y (багана C) ба тооцоолсон Y утгын хүрээ (бага D).

4. Квадратуудын зөрүүний нийлбэрийг тооцоолсон - одоо "Өгөгдөл" таб руу очоод "Шийдлийн хайлт" -ыг сонгоно уу.

5. Гарч ирэх цэснээс A1 нүдийг (k коэффициенттэй) өөрчлөх нүдээр сонгоно.

6. Зорилтот нүдээр A2 нүдийг сонгоод “хамгийн бага утгатай тэнцүүлэх” нөхцөлийг тавь. Энэ нь тооцоолсон болон хэмжсэн утгуудын хоорондын зөрүүгийн квадратуудын нийлбэрийг тооцоолох нүд бөгөөд энэ нийлбэр нь хамгийн бага байх ёстой гэдгийг бид санаж байна. "Гүйцэтгэх" дээр дарна уу.

7. k коэффициентийг сонгосон. Одоо та тооцоолсон утгууд нь хэмжсэнтэй маш ойрхон байгаа эсэхийг шалгаж болно.

P.S.

Мэдээжийн хэрэг Excel-ийн туршилтын өгөгдлийг ойролцоогоор тооцоолохын тулд шугаман, экспоненциал, хүч, олон гишүүнт функцийг ашиглан өгөгдлийг дүрслэх боломжийг олгодог тусгай хэрэгслүүд байдаг тул та үүнийг хийхгүйгээр хийх боломжтой. "шийдэл хайх" нэмэлтүүд. Би эдгээр бүх ойртуулах аргуудын талаар өөр дээрээ ярьсан тул хэрэв та сонирхож байвал хараарай. Гэхдээ зарим нэг чамин функцийн тухай ярихад нэг үл мэдэгдэх коэффициенттэйэсвэл оновчлолын асуудлууд, дараа нь энд дээд бүтэцилүү сайн цагт ирж чадахгүй байсан.

Шийдэл хайх нэмэлтбусад ажлуудад ашиглаж болно, гол зүйл бол мөн чанарыг ойлгох явдал юм: утгыг сонгох нүд байдаг ба үл мэдэгдэх параметрийг сонгох нөхцөлийг тодорхойлсон зорилтот нүд байдаг.
Тэгээд л болоо! Дараагийн нийтлэлд би танд амралтын тухай үлгэр ярих болно, ингэснээр нийтлэлийг нийтлэхээ алдахгүйн тулд,

Өгөгдсөн функцийг бусад энгийн функцээр ойролцоогоор дүрслэх боломжийг олгодог тул олон програмтай. LSM нь ажиглалтыг боловсруулахад маш их хэрэгтэй байж болох бөгөөд санамсаргүй алдаа агуулсан бусдын хэмжилтийн үр дүнд үндэслэн зарим хэмжигдэхүүнийг тооцоолоход идэвхтэй ашигладаг. Энэ нийтлэлээс та Excel дээр хамгийн бага квадратуудын тооцоог хэрхэн хэрэгжүүлэх талаар сурах болно.

Тодорхой жишээ ашиглан асуудлын тайлбар

X ба Y гэсэн хоёр үзүүлэлт байна гэж бодъё. Түүнээс гадна, Y нь X-ээс хамаарна. OLS нь регрессийн шинжилгээний үүднээс (Excel-д түүний аргуудыг суулгасан функцуудыг ашиглан хэрэгжүүлдэг) сонирхдог тул бид нэн даруй дараахь зүйлийг анхаарч үзэх хэрэгтэй. тодорхой асуудал.

Тэгэхээр Х нь хүнсний дэлгүүрийн квадрат метрээр хэмжигдэх жижиглэнгийн талбай, Y нь сая рублиэр хэмжигдэх жилийн эргэлт гэж үзье.

Хэрэв дэлгүүрт энэ болон бусад жижиглэнгийн талбай байгаа бол ямар эргэлт (Y) байх талаар урьдчилан таамаглах шаардлагатай. Хайпермаркет нь лангуунаас илүү их бараа зардаг тул Y = f (X) функц нэмэгдэж байгаа нь ойлгомжтой.

Урьдчилан таамаглахад ашигласан анхны өгөгдлийн зөв байдлын талаар хэдэн үг хэлье

Бидэнд n дэлгүүрт зориулсан өгөгдлийг ашиглан бүтээсэн хүснэгт байна гэж бодъё.

дагуу математик статистик, наад зах нь 5-6 объектын мэдээллийг шалгавал үр дүн нь их бага зөв байх болно. Үүнээс гадна "гажиг" үр дүнг ашиглах боломжгүй. Ялангуяа элит жижиг нэрийн дэлгүүр нь "масмаркет" ангиллын томоохон жижиглэн худалдааны цэгүүдийн эргэлтээс хэд дахин их эргэлттэй байж болно.

Аргын мөн чанар

Хүснэгтийн өгөгдлийг декартын хавтгайд M 1 (x 1, y 1), ... M n (x n, y n) цэг хэлбэрээр дүрсэлж болно. Одоо асуудлын шийдлийг M 1, M 2, .. M n цэгүүдэд аль болох ойртуулах графиктай y = f (x) ойролцоох функцийг сонгох хүртэл бууруулна.

Мэдээжийн хэрэг та өндөр зэрэглэлийн олон гишүүнтийг ашиглаж болно, гэхдээ энэ сонголтыг хэрэгжүүлэхэд хэцүү төдийгүй зүгээр л буруу, учир нь энэ нь илрүүлэх шаардлагатай гол чиг хандлагыг тусгахгүй. Хамгийн боломжийн шийдэл бол туршилтын өгөгдлүүдийг хамгийн сайн ойролцоолсон y = ax + b шулуун шугамыг хайж олох явдал юм, эсвэл илүү нарийвчлалтай бол a, b коэффициентүүд.

Нарийвчлалын үнэлгээ

Аливаа ойролцоо тооцооллын хувьд түүний нарийвчлалыг үнэлэх нь онцгой ач холбогдолтой юм. x i цэгийн функциональ ба туршилтын утгуудын ялгааг (хазайлт) e i гэж тэмдэглэе, өөрөөр хэлбэл e i = y i - f (x i).

Ойролцоогоор үнэн зөвийг үнэлэхийн тулд хазайлтын нийлбэрийг ашиглаж болох нь ойлгомжтой, өөрөөр хэлбэл X-ийн Y-ээс хамаарлыг ойролцоогоор дүрслэх шулуун шугамыг сонгохдоо та хамгийн бага утгатай нэгийг сонгох хэрэгтэй. авч үзэж буй бүх цэгүүдэд нийлбэр e i. Гэсэн хэдий ч бүх зүйл тийм ч энгийн биш, учир нь эерэг хазайлтаас гадна сөрөг талууд байх болно.

Асуудлыг хазайлтын модуль эсвэл тэдгээрийн квадрат ашиглан шийдэж болно. Сүүлийн арга нь хамгийн өргөн хэрэглэгддэг. Энэ нь регрессийн шинжилгээ (Excel-д суурилуулсан хоёр функцийг ашиглан хэрэгжүүлсэн) зэрэг олон салбарт ашиглагддаг бөгөөд үр дүнтэй нь удаан хугацааны туршид батлагдсан.

Хамгийн бага квадрат арга

Excel нь та бүхний мэдэж байгаагаар сонгосон мужид байрлах бүх утгын утгыг тооцоолох боломжийг олгодог 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 * шулуун шугам тохиромжтой бөгөөд энэ нь тухайн жишээний регрессийн загвар юм. Мэдээжийн хэрэг, энэ нь тодорхой үр дүнг олох боломжийг танд олгохгүй, гэхдээ энэ нь дэлгүүрийн зээлээр тодорхой газар худалдаж авах нь үр дүнгээ өгөх эсэх талаар ойлголттой болоход тусална.

Excel дээр хамгийн бага квадратуудыг хэрхэн хэрэгжүүлэх вэ

Excel нь хамгийн бага квадратуудыг ашиглан утгыг тооцоолох функцтэй. Энэ нь дараах хэлбэртэй байна: “TREND” (мэдэгдэж буй Y утгууд; мэдэгдэж буй X утгууд; шинэ X утгууд; тогтмол). Excel-ийн OLS-ийг тооцоолох томъёог хүснэгтэндээ ашиглацгаая.

Үүнийг хийхийн тулд Excel програмын хамгийн бага квадратын аргыг ашиглан тооцооллын үр дүнг харуулах нүдэнд "=" тэмдгийг оруулаад "TREND" функцийг сонгоно уу. Нээгдсэн цонхонд тохирох талбаруудыг бөглөж, тодруулна уу:

Y-ийн мэдэгдэж буй утгын хүрээ (энэ тохиолдолд худалдааны эргэлтийн өгөгдөл);
муж x 1 , …x n , өөрөөр хэлбэл жижиглэнгийн талбайн хэмжээ;
Х-ийн мэдэгдэж байгаа болон үл мэдэгдэх утгууд, үүний тулд та эргэлтийн хэмжээг олж мэдэх хэрэгтэй (тэдгээрийн байршлын талаархи мэдээллийг ажлын хуудсан дээр доороос үзнэ үү).

Нэмж дурдахад томъёо нь "Const" логик хувьсагчийг агуулдаг. Хэрэв та харгалзах талбарт 1 гэж оруулбал b = 0 гэж тооцож тооцооллыг хийх ёстой гэсэн үг юм.

Хэрэв та нэгээс олон x утгын урьдчилсан мэдээг олж мэдэх шаардлагатай бол томьёог оруулсны дараа "Enter" товчийг дарж болохгүй, харин гар дээр "Shift" + "Control" + "Enter" хослолыг бичих хэрэгтэй.

Зарим онцлог

Регрессийн шинжилгээдамми хүмүүст ч хүртээмжтэй байж болно. Үл мэдэгдэх хувьсагчийн массивын утгыг таамаглах Excel томъёо-TREND-ийг хамгийн бага квадратын талаар сонсож байгаагүй хүмүүс ч ашиглаж болно. Түүний ажлын зарим шинж чанарыг мэдэхэд л хангалттай. Тухайлбал:

Хэрэв та y хувьсагчийн мэдэгдэж буй утгуудын мужийг нэг мөр эсвэл баганад байрлуулбал х-ийн мэдэгдэж буй утга бүхий мөр (багана) бүрийг програм нь тусдаа хувьсагч болгон хүлээн авах болно.
Хэрэв мэдэгдэж буй х мужийг TREND цонхонд заагаагүй бол Excel-д функцийг ашиглах үед програм нь үүнийг бүхэл тооноос бүрдэх массив гэж үзэх бөгөөд тэдгээрийн тоо нь өгөгдсөн утгуудын мужтай тохирч байх болно. хувьсагч y.
“Таамагласан” утгуудын массивыг гаргахын тулд чиг хандлагыг тооцоолох илэрхийллийг массивын томьёо болгон оруулах шаардлагатай.
Хэрэв x-ийн шинэ утгыг заагаагүй бол TREND функц нь тэдгээрийг мэдэгдэж байгаатай тэнцүү гэж үзнэ. Хэрэв тэдгээрийг заагаагүй бол 1-р массивыг аргумент болгон авна; 2; 3; 4;…, энэ нь аль хэдийн тодорхойлсон параметр y-тэй мужтай тохирч байна.
Шинэ x утгуудыг агуулсан муж нь өгөгдсөн y утгуудыг агуулсан мужтай ижил буюу түүнээс олон мөр эсвэл баганатай байх ёстой. Өөрөөр хэлбэл, бие даасан хувьсагчидтай пропорциональ байх ёстой.
Мэдэгдэж буй x утгууд бүхий массив нь олон хувьсагч агуулж болно. Гэсэн хэдий ч, хэрэв бид зөвхөн нэгний тухай ярьж байгаа бол x ба y-ийн өгөгдсөн утгатай мужууд пропорциональ байх шаардлагатай. Хэд хэдэн хувьсагчийн хувьд өгөгдсөн y утгатай мужийг нэг багана эсвэл нэг мөрөнд багтаах шаардлагатай.

ТААМАГЛАХ функц

Хэд хэдэн функцийг ашиглан хэрэгжүүлсэн. Тэдгээрийн нэгийг “ТААМАГЛАЛ” гэж нэрлэдэг. Энэ нь "TREND"-тэй төстэй, өөрөөр хэлбэл хамгийн бага квадратын аргыг ашиглан тооцооллын үр дүнг өгдөг. Гэсэн хэдий ч Y-ийн утга тодорхойгүй зөвхөн нэг X-д зориулагдсан.

Одоо та шугаман чиг хандлагын дагуу тодорхой үзүүлэлтийн ирээдүйн утгыг урьдчилан таамаглах боломжийг олгодог даммидад зориулсан Excel-ийн томъёог мэддэг болсон.

Хамгийн бага квадратын арга нь бүтээх математикийн арга юм шугаман тэгшитгэл, энэ нь хоёр цуврал тооны багцад хамгийн ойр таарах болно. Энэ аргыг ашиглах зорилго нь нийт квадрат алдааг багасгах явдал юм. Excel-д таны ашиглах хэрэгслүүд байдаг энэ аргатооцооллын үеэр. Үүнийг хэрхэн яаж хийхийг олж мэдье.

Хамгийн бага квадратын арга (LSM) нь нэг хувьсагчийн нөгөө хувьсагчийн хамаарлын математик тодорхойлолт юм. Үүнийг урьдчилан таамаглахад ашиглаж болно.

Шийдэл олох нэмэлтийг идэвхжүүлж байна

Excel дээр MNC ашиглахын тулд та нэмэлтийг идэвхжүүлэх хэрэгтэй "Шийдэл олох", энэ нь анхдагчаар идэвхгүй байна.

Одоо функц Шийдэл хайж байна Excel-д идэвхжсэн бөгөөд түүний хэрэгслүүд туузан дээр гарч ирнэ.

Асуудлын нөхцөл

Тодорхой жишээн дээр LSM-ийн хэрэглээг тайлбарлая. Бидэнд хоёр эгнээний тоо байна x Тэгээд y , тэдгээрийн дарааллыг доорх зурагт үзүүлэв.

Энэ хамаарлыг дараах функцээр хамгийн зөв тодорхойлж болно.

Үүний зэрэгцээ, хэзээ гэдгийг мэддэг x=0 yбас тэнцүү 0 . Тиймээс энэ тэгшитгэлийг хамаарлаар тодорхойлж болно y=nx .

Бид зөрүүгийн квадратуудын хамгийн бага нийлбэрийг олох ёстой.

Шийдэл

Аргын шууд хэрэглээний тайлбар руу шилжье.

Таны харж байгаагаар хамгийн бага квадратын аргыг хэрэглэх нь нэлээд төвөгтэй математикийн процедур юм. Бид үүнийг энгийн жишээн дээр харуулсан боловч илүү төвөгтэй тохиолдлууд байдаг. Гэсэн хэдий ч Microsoft Excel хэрэгслүүд нь тооцооллыг аль болох хялбаршуулах зорилготой юм.

Хамгийн бага квадратын арга (OLS) нь регрессийн шинжилгээний талбарт хамаарна. Өгөгдсөн функцийг бусад энгийн функцээр ойролцоогоор дүрслэх боломжийг олгодог тул олон програмтай. LSM нь ажиглалтыг боловсруулахад маш их хэрэгтэй байж болох бөгөөд санамсаргүй алдаа агуулсан бусдын хэмжилтийн үр дүнд үндэслэн зарим хэмжигдэхүүнийг тооцоолоход идэвхтэй ашигладаг. Энэ нийтлэлээс та Excel дээр хамгийн бага квадратуудын тооцоог хэрхэн хэрэгжүүлэх талаар сурах болно.

Тодорхой жишээ ашиглан асуудлын тайлбар

Урьдчилан таамаглахад ашигласан анхны өгөгдлийн зөв байдлын талаар хэдэн үг хэлье

Бидэнд n дэлгүүрт зориулсан өгөгдлийг ашиглан бүтээсэн хүснэгт байна гэж бодъё.

Математикийн статистик мэдээллээс үзэхэд хамгийн багадаа 5-6 объектын мэдээллийг шалгавал үр дүн нь их бага зөв байх болно. Үүнээс гадна "гажиг" үр дүнг ашиглах боломжгүй. Ялангуяа элит жижиг нэрийн дэлгүүр нь "масмаркет" ангиллын томоохон жижиглэн худалдааны цэгүүдийн эргэлтээс хэд дахин их эргэлттэй байж болно.

Аргын мөн чанар

Нарийвчлалын үнэлгээ

Хамгийн бага квадрат арга

Математик тэмдэглэгээнд энэ нь дараах байдалтай харагдаж байна.

Шулуун шугамыг ашиглан ойролцоогоор тооцоолох шийдвэрийг анх гаргасан тул бид дараах байдалтай байна.

2-т хуваах, нийлбэрийг удирдах зэрэг энгийн өөрчлөлтүүдийн дараа бид дараахь зүйлийг авна.

Excel дээр хамгийн бага квадратуудыг хэрхэн хэрэгжүүлэх вэ

Y-ийн мэдэгдэж буй утгын хүрээ (энэ тохиолдолд худалдааны эргэлтийн өгөгдөл);
муж x 1 , …x n , өөрөөр хэлбэл жижиглэнгийн талбайн хэмжээ;
Х-ийн мэдэгдэж байгаа болон үл мэдэгдэх утгууд, үүний тулд та эргэлтийн хэмжээг олж мэдэх хэрэгтэй (тэдгээрийн байршлын талаархи мэдээллийг ажлын хуудсан дээр доороос үзнэ үү).

Зарим онцлог

Регрессийн шинжилгээ нь дамми хүмүүст ч хүртээмжтэй байж болно. Үл мэдэгдэх хувьсагчийн массивын утгыг таамаглах Excel томъёо-TREND-ийг хамгийн бага квадратын талаар сонсож байгаагүй хүмүүс ч ашиглаж болно. Түүний ажлын зарим шинж чанарыг мэдэхэд л хангалттай. Тухайлбал:

Хэрэв та y хувьсагчийн мэдэгдэж буй утгуудын мужийг нэг мөр эсвэл баганад байрлуулбал х-ийн мэдэгдэж буй утга бүхий мөр (багана) бүрийг програм нь тусдаа хувьсагч болгон хүлээн авах болно.
Хэрэв мэдэгдэж буй х мужийг TREND цонхонд заагаагүй бол Excel-д функцийг ашиглах үед програм нь үүнийг бүхэл тооноос бүрдэх массив гэж үзэх бөгөөд тэдгээрийн тоо нь өгөгдсөн утгуудын мужтай тохирч байх болно. хувьсагч y.
“Таамагласан” утгуудын массивыг гаргахын тулд чиг хандлагыг тооцоолох илэрхийллийг массивын томьёо болгон оруулах шаардлагатай.
Хэрэв x-ийн шинэ утгыг заагаагүй бол TREND функц нь тэдгээрийг мэдэгдэж байгаатай тэнцүү гэж үзнэ. Хэрэв тэдгээрийг заагаагүй бол 1-р массивыг аргумент болгон авна; 2; 3; 4;…, энэ нь аль хэдийн тодорхойлсон параметр y-тэй мужтай тохирч байна.
Шинэ x утгуудыг агуулсан муж нь өгөгдсөн y утгуудыг агуулсан мужтай ижил буюу түүнээс олон мөр эсвэл баганатай байх ёстой. Өөрөөр хэлбэл, бие даасан хувьсагчидтай пропорциональ байх ёстой.
Мэдэгдэж буй x утгууд бүхий массив нь олон хувьсагч агуулж болно. Гэсэн хэдий ч, хэрэв бид зөвхөн нэгний тухай ярьж байгаа бол x ба y-ийн өгөгдсөн утгатай мужууд пропорциональ байх шаардлагатай. Хэд хэдэн хувьсагчийн хувьд өгөгдсөн y утгатай мужийг нэг багана эсвэл нэг мөрөнд багтаах шаардлагатай.

ТААМАГЛАХ функц

Excel-ийн регрессийн шинжилгээг хэд хэдэн функц ашиглан хэрэгжүүлдэг. Тэдгээрийн нэгийг “ТААМАГЛАЛ” гэж нэрлэдэг. Энэ нь "TREND"-тэй төстэй, өөрөөр хэлбэл хамгийн бага квадратын аргыг ашиглан тооцооллын үр дүнг өгдөг. Гэсэн хэдий ч Y-ийн утга тодорхойгүй зөвхөн нэг X-д зориулагдсан.