Excel көлбеу энергиясындағы ең кіші квадраттар әдісі. Excel бағдарламасындағы ең кіші квадраттар әдісі

Әдіс ең кіші квадраттар(LSM) зерттелетін деректерден таңдалған функцияның квадраттық ауытқуларының қосындысын азайтуға негізделген. Бұл мақалада біз сызықтық функцияның көмегімен қол жетімді деректерді жуықтайтын боламызж = а x + б .

Ең кіші квадрат әдісі(ағылшын) Кәдімгі Ең аз Шаршы , О.Л.С.) белгісіз параметрлерді бағалау тұрғысынан регрессиялық талдаудың негізгі әдістерінің бірі болып табылады регрессия модельдеріүлгі деректеріне сәйкес.

Бір айнымалыға ғана тәуелді функциялар бойынша жуықтауды қарастырайық:

Сызықтық: 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Және ж. деген болжам бар жбайланысты Xсызықтық заңға сәйкес ж = балта + б. Бұл қатынастың параметрлерін анықтау үшін зерттеуші бақылаулар жасады: x i әрбір мәні үшін y i өлшемі жасалды (мысалы файлды қараңыз). Сәйкесінше, 20 жұп мәндер болсын (x i; y i).

Ескерту:Егер өзгерту қадамы болса X тұрақты, содан кейін құру шашыраңқы сызбаларпайдалануға болады, егер жоқ болса, онда диаграмма түрін пайдалану керек Дақ .

Диаграммадан айнымалылар арасындағы байланыс сызықтыққа жақын екені анық көрінеді. Көптеген түзулердің қайсысы айнымалылар арасындағы байланысты ең «дұрыс» сипаттайтынын түсіну үшін сызықтар салыстырылатын критерийді анықтау қажет.

Мұндай критерий ретінде біз өрнекті қолданамыз:

Қайда ŷ мен = а * x i + б ; n – мәндер жұптарының саны (біздің жағдайда n=20)

Жоғарыда келтірілген өрнек y i және ŷ i бақыланатын мәндерінің арасындағы квадраттық қашықтықтардың қосындысы болып табылады және көбінесе SSE ретінде белгіленеді ( сомасы ның Шаршы Қателер (Қалдықтар), квадрат қателерінің қосындысы (қалдық)) .

Ең кіші квадрат әдісіосындай жолды таңдау болып табылады ŷ = балта + б, ол үшін жоғарыдағы өрнек ең аз мәнді қабылдайды.

Ескерту:Екі өлшемді кеңістіктегі кез келген сызық 2 параметрдің мәндерімен бірегей түрде анықталады: а (еңіс) және б (ауысым).

Квадрат қашықтықтардың қосындысы неғұрлым аз болса, сәйкес сызық соғұрлым жақсырақ қол жетімді деректерге жақындайды және одан әрі х айнымалысынан y мәндерін болжау үшін пайдаланылуы мүмкін деп саналады. Шындығында айнымалылар арасында байланыс болмаса немесе байланыс сызықты емес болса да, OLS бәрібір «ең жақсы» жолды таңдайтыны анық. Осылайша, ең кіші квадраттар әдісі айнымалылар арасында нақты байланыстың болуы туралы ештеңе айтпайды, әдіс жай ғана осындай функция параметрлерін таңдауға мүмкіндік береді. а Және б , ол үшін жоғарыдағы өрнек минималды.

Өте күрделі емес математикалық операцияларды орындау арқылы (толығырақ ақпаратты қараңыз) параметрлерді есептеуге болады а Және б :

Формуладан көрініп тұрғандай, параметр а ковариацияның қатынасын білдіреді және сондықтан MS EXCEL-де параметрді есептеу үшін А Сіз келесі формулаларды пайдалана аласыз (қараңыз Сызықтық парақ мысалы файлы):

= KOVAR(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() функциясы екі параметрді де бірден есептеуге мүмкіндік береді. Формула енгізу үшін LINEST(C26:C45;B26:B45)Қатардағы 2 ұяшықты таңдап, басу керек CTRL + SHIFT + ЕНГІЗУ(туралы мақаланы қараңыз). Мән сол жақ ұяшықта қайтарылады А , оң жақта - б .

Ескерту: Кіріспен араласпау үшін массив формулалары INDEX() функциясын қосымша пайдалану қажет болады. Формула = ИНДЕКС(ЖОЛЫ(C26:C45,B26:B45),1)немесе жай = LINEST(C26:C45;B26:B45)сызықтың еңісіне жауапты параметрді қайтарады, яғни. А . Формула = ИНДЕКС(ЖОЛЫ(C26:C45,B26:B45),2)сызықтың Y осімен қиылысуына жауапты параметрді қайтарады, яғни. б .

Параметрлерді есептеп, шашырау диаграммасысәйкес сызықты салуға болады.

Ең кіші квадраттар әдісі арқылы түзу сызықты салудың тағы бір тәсілі – график құралы Тренд сызығы. Ол үшін диаграмманы таңдаңыз, мәзірден таңдаңыз Орналасу қойындысы, В топтық талдаубасыңыз Тренд сызығы, содан кейін Сызықтық жуықтау .

Диалогтық терезеде «теңдікті диаграммада көрсету» жолына құсбелгі қою арқылы жоғарыда табылған параметрлер диаграммадағы мәндерге сәйкес келетініне көз жеткізуге болады.

Ескерту: Параметрлердің сәйкес келуі үшін диаграмма түрі болуы керек. Мәселе мынада: диаграмманы құру кезінде Кесте X осінің мәндерін пайдаланушы көрсете алмайды (пайдаланушы нүктелердің орналасуына әсер етпейтін белгілерді ғана көрсете алады). Х мәндерінің орнына 1 реттілігі қолданылады; 2; 3; ... (санаттарды нөмірлеу үшін). Сондықтан, егер сіз салсаңыз тренд сызығытиптік диаграммада Кесте, содан кейін X нақты мәндерінің орнына дұрыс емес нәтижеге әкелетін осы реттілік мәндері пайдаланылады (әрине, X нақты мәндері 1 ретпен сәйкес келмесе; 2; 3; ...).

Ал, жұмыста біз инспекцияға хабарладық, мақала конференцияға үйде жазылған - енді біз блогта жаза аламыз. Деректерімді өңдеу барысында Excel бағдарламасындағы өте керемет және қажетті қондырма туралы жаза алмайтынымды түсіндім. Сонымен, мақала осы қосымшаға арналады, мен бұл туралы пайдалану үлгісін пайдаланып айтып беремін ең кіші квадраттар әдісі(LSM) эксперименттік деректерді сипаттау кезінде белгісіз теңдеу коэффициенттерін іздеу үшін.

«Шешімді іздеу» қосымшасын қалай қосуға болады

Алдымен, осы қосымшаны қалай қосуға болатынын анықтайық.

1. «Файл» мәзіріне өтіп, «Excel параметрлері» тармағын таңдаңыз.

2. Пайда болған терезеде «Шешімді іздеу» опциясын таңдап, «өту» түймесін басыңыз.

3. Келесі терезеде «шешімді іздеу» жанындағы құсбелгіні қойып, «OK» түймесін басыңыз.

4. Қондырма іске қосылды - енді оны «Деректер» мәзір элементінен табуға болады.

Ең кіші квадрат әдісі

Енді қысқаша туралы Ең кіші квадраттар әдісі (LSM) және оны қайда қолдануға болады.

Қандай да бір экспериментті орындағаннан кейін бізде деректер жиынтығы бар делік, онда X шамасының Y мәніне әсерін зерттедік.

Біз бұл әсерді математикалық түрде сипаттағымыз келеді, сонда біз осы формуланы пайдалана аламыз және егер біз X мәнін соншалықты көп өзгертсек, Y мәнін алатынымызды білеміз ...

Мен өте қарапайым мысал келтіремін (суретті қараңыз).

Нүктелердің бірінен соң бірі түзу сызықта орналасқаны бекер емес, сондықтан біздің тәуелділігіміз сипатталған деп сенімді түрде болжаймыз. сызықтық функция y=kx+b. Сонымен бірге, Х нөлге тең болғанда, 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. A1 ұяшығына «1» мәнін енгізіңіз. Бұл бірлік y=kx функционалдық қатынасымыздың (k) коэффициентінің нақты мәніне бірінші жуықтау болады.

3. В бағанында X параметрінің мәндері, С бағанында Y параметрінің мәндері бар. D бағанының ұяшықтарына формуланы енгіземіз: «к коэффициенті 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 ұяшығын (к коэффициенті бар) таңдаңыз.

6. Мақсат ретінде A2 ұяшығын таңдап, «ең төменгі мәнге тең орнату» шартын орнатыңыз. Бұл есептелген және өлшенген мәндер арасындағы айырмашылықтардың квадраттарының қосындысын есептейтін ұяшық екенін және бұл сома минималды болуы керек екенін есте ұстаймыз. «Орындау» түймесін басыңыз.

7. k коэффициенті таңдалды. Енді есептелген мәндердің өлшенгенге өте жақын екенін тексеруге болады.

P.S.

Жалпы алғанда, әрине, Excel бағдарламасында эксперименттік деректерді жуықтау үшін сызықтық, экспоненциалды, қуат және көпмүшелік функцияларды пайдаланып деректерді сипаттауға мүмкіндік беретін арнайы құралдар бар, сондықтан сіз жиі онсыз жасай аласыз. «шешімді іздеу» қосымшалары. Мен осы жуықтау әдістерінің барлығын өзімде айттым, сондықтан сізді қызықтыратын болса, қараңыз. Бірақ кейбір экзотикалық функцияға келгенде бір белгісіз коэффициентпеннемесе оңтайландыру мәселелері, содан кейін осында қондырмажақсы уақытта келе алмады.

Шешімді іздеу қондырмасыбасқа тапсырмалар үшін пайдалануға болады, ең бастысы мәнін түсіну: мәнді таңдайтын ұяшық бар және белгісіз параметрді таңдау шарты көрсетілген мақсатты ұяшық бар.
Осымен болды! Келесі мақалада мен сізге демалыс туралы ертегі айтып беремін, сондықтан мақаланың жариялануын жіберіп алмау үшін,

Оның көптеген қолданбалары бар, өйткені ол берілген функцияны басқа қарапайымдарымен шамамен көрсетуге мүмкіндік береді. LSM бақылауларды өңдеуде өте пайдалы болуы мүмкін және ол кездейсоқ қателерді қамтитын басқаларының өлшеу нәтижелеріне негізделген кейбір шамаларды бағалау үшін белсенді қолданылады. Бұл мақалада сіз Excel бағдарламасында ең кіші квадраттарды есептеуді қалай орындау керектігін үйренесіз.

Нақты мысалды пайдалана отырып, мәселені тұжырымдау

Екі X және Y көрсеткіші бар делік. Сонымен қатар, Y X-ға тәуелді. OLS бізді регрессиялық талдау тұрғысынан қызықтыратындықтан (Excel-де оның әдістері кіріктірілген функцияларды қолдану арқылы жүзеге асырылады), біз дереу мынаны қарастыруға көшуіміз керек. нақты мәселе.

Сонымен, X шаршы метрмен өлшенетін азық-түлік дүкенінің бөлшек сауда алаңы, ал 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) жуықтау функциясын таңдауға келтіріледі.

Әрине, сіз жоғары дәрежелі көпмүшені пайдалана аласыз, бірақ бұл опцияны жүзеге асыру қиын ғана емес, сонымен қатар жай дұрыс емес, өйткені ол анықталуы керек негізгі трендті көрсетпейді. Ең ақылға қонымды шешім - эксперименттік мәліметтерді, дәлірек айтқанда, a және b коэффициенттерін ең жақсы жуықтайтын y = ax + 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-ге бөлу және қосындыларды өңдеу, біз мынаны аламыз:

Оны шешу, мысалы, Крамер әдісін қолдана отырып, біз белгілі бір коэффициенттері бар стационарлық нүктені аламыз * және b *. Бұл ең аз, яғни белгілі бір аумақ үшін дүкеннің қандай айналымы болатынын болжау үшін y = a * x + b * түзу сызығы қолайлы, бұл қарастырылып отырған мысал үшін регрессия үлгісі болып табылады. Әрине, бұл сізге нақты нәтижені табуға мүмкіндік бермейді, бірақ бұл сізге белгілі бір аймақты дүкен несиесіне сатып алу өте тиімді болатыны туралы түсінік алуға көмектеседі.

Excel бағдарламасында ең кіші квадраттарды қалай жүзеге асыруға болады

Excel бағдарламасында ең кіші квадраттарды пайдаланып мәндерді есептеу функциясы бар. Оның келесі пішіні бар: «TREND» (белгілі Y мәндері; белгілі X мәндері; жаңа Х мәндері; тұрақты). Excel бағдарламасында OLS есептеу формуласын кестемізге қолданайық.

Ол үшін Excel бағдарламасындағы ең кіші квадраттар әдісімен есептеу нәтижесі көрсетілетін ұяшыққа «=» белгісін енгізіп, «TREND» функциясын таңдаңыз. Ашылған терезеде тиісті өрістерді толтырып, бөлектеңіз:

Y үшін белгілі мәндер ауқымы (бұл жағдайда тауар айналымы туралы деректер);
диапазон x 1 , …x n , яғни сауда алаңының көлемі;
x-тің белгілі және белгісіз мәндері, ол үшін айналым көлемін білу қажет (олардың жұмыс парағында орналасуы туралы ақпаратты төменде қараңыз).

Сонымен қатар, формулада «Const» логикалық айнымалысы бар. Сәйкес өріске 1 енгізсеңіз, бұл b = 0 деп есептей отырып, есептеулерді орындау керек дегенді білдіреді.

Егер сізге бірнеше x мәніне болжамды білу қажет болса, формуланы енгізгеннен кейін «Enter» пернесін баспау керек, бірақ пернетақтада «Shift» + «Control» + «Enter» тіркесімін теру керек.

Кейбір мүмкіндіктер

Регрессиялық талдаутіпті манекендерге де қолжетімді болуы мүмкін. Белгісіз айнымалылар массивінің мәнін болжауға арналған Excel формуласын - TREND - тіпті ең кіші квадраттар туралы ешқашан естімегендер де пайдалана алады. Оның жұмысының кейбір ерекшеліктерін білу жеткілікті. Сондай-ақ:

Егер сіз y айнымалысының белгілі мәндерінің ауқымын бір жолға немесе бағанға орналастырсаңыз, онда x белгілі мәндері бар әрбір жолды (бағанды) бағдарлама жеке айнымалы ретінде қабылдайды.
TREND терезесінде белгілі x диапазоны көрсетілмесе, Excel бағдарламасында функцияны пайдаланған кезде бағдарлама оны бүтін сандардан тұратын массив ретінде қарастырады, олардың саны берілген мәндері бар диапазонға сәйкес келеді. айнымалы y.
«Болжамды» мәндердің массивін шығару үшін трендті есептеуге арналған өрнек массив формуласы ретінде енгізілуі керек.
Егер x-тің жаңа мәндері көрсетілмесе, TREND функциясы оларды белгілі мәндерге тең деп санайды. Егер олар көрсетілмесе, онда аргумент ретінде 1-массив алынады; 2; 3; 4;…, ол бұрыннан көрсетілген y параметрлері бар диапазонға сәйкес.
Жаңа x мәндерін қамтитын ауқымда берілген y мәндерін қамтитын ауқыммен бірдей немесе бірнеше жолдар немесе бағандар болуы керек. Басқаша айтқанда, ол тәуелсіз айнымалыларға пропорционалды болуы керек.
Белгілі x мәндері бар массив бірнеше айнымалыларды қамтуы мүмкін. Алайда, егер біз тек біреуі туралы айтатын болсақ, онда x және y берілген мәндері бар диапазондар пропорционалды болуы керек. Бірнеше айнымалы болған жағдайда, берілген y мәндері бар диапазон бір бағанға немесе бір жолға сәйкес келуі керек.

БОЛЖАУ функциясы

Бірнеше функцияларды қолдану арқылы жүзеге асырылады. Солардың бірі «Болжау» деп аталады. Ол «TREND-ке» ұқсас, яғни ол ең кіші квадраттар әдісін қолданатын есептеулер нәтижесін береді. Алайда Y мәні белгісіз бір Х үшін ғана.

Енді сіз Excel бағдарламасында сызықтық тренд бойынша белгілі бір көрсеткіштің болашақ мәнін болжауға мүмкіндік беретін манекендерге арналған формулаларды білесіз.

Ең кіші квадраттар әдісі – құрудың математикалық процедурасы сызықтық теңдеу, ол екі қатар сандар жиынына ең жақын сәйкес келеді. Бұл әдісті қолданудың мақсаты жалпы квадрат қатесін азайту болып табылады. Excel бағдарламасында қолдануға болатын құралдар бар бұл әдісесептеулер кезінде. Мұның қалай жасалатынын анықтап көрейік.

Ең кіші квадраттар әдісі (LSM) бір айнымалының екіншісіне тәуелділігінің математикалық сипаттамасы болып табылады. Оны болжау үшін пайдалануға болады.

Шешімді табу қондырмасын қосу

Excel бағдарламасында MNC пайдалану үшін қондырманы қосу керек «Шешімін табу», ол әдепкі бойынша өшірілген.

Енді функция Шешімін табу Excel бағдарламасында іске қосылады және оның құралдары таспада пайда болады.

Мәселенің жағдайлары

Арнайы мысал арқылы LSM пайдалануды сипаттап көрейік. Бізде екі қатар сандар бар x Және ж , оның реті төмендегі суретте көрсетілген.

Бұл тәуелділікті ең дәл функция арқылы сипаттауға болады:

Сонымен бірге қашан екені белгілі x=0 жда тең 0 . Сондықтан бұл теңдеуді тәуелділік арқылы сипаттауға болады y=nx .

Айырмашылық квадраттарының ең аз қосындысын табуымыз керек.

Шешім

Әдістің тікелей қолданылуының сипаттамасына көшейік.

Көріп отырғаныңыздай, ең кіші квадраттар әдісін қолдану өте күрделі математикалық процедура. Біз оны қарапайым мысал арқылы іс жүзінде көрсеттік, бірақ әлдеқайда күрделі жағдайлар бар. Дегенмен, Microsoft Excel құралдары есептеулерді мүмкіндігінше жеңілдетуге арналған.

Ең кіші квадраттар әдісі (OLS) регрессиялық талдау саласына жатады. Оның көптеген қолданбалары бар, өйткені ол берілген функцияны басқа қарапайымдарымен шамамен көрсетуге мүмкіндік береді. LSM бақылауларды өңдеуде өте пайдалы болуы мүмкін және ол кездейсоқ қателерді қамтитын басқаларының өлшеу нәтижелеріне негізделген кейбір шамаларды бағалау үшін белсенді қолданылады. Бұл мақалада сіз Excel бағдарламасында ең кіші квадраттарды есептеуді қалай орындау керектігін үйренесіз.

Нақты мысалды пайдалана отырып, мәселені тұжырымдау

Болжау үшін пайдаланылатын бастапқы деректердің дұрыстығы туралы бірнеше сөз

Бізде n дүкенге арналған деректер арқылы құрастырылған кесте бар делік.

Математикалық статистикаға сәйкес, кем дегенде 5-6 нысан бойынша деректер зерттелсе, нәтиже азды-көпті дұрыс болады. Сонымен қатар, «аномальды» нәтижелерді пайдалану мүмкін емес. Атап айтқанда, элиталық шағын бутиктің «масмаркет» класындағы ірі сауда нүктелерінің айналымынан бірнеше есе артық айналымы болуы мүмкін.

Әдістің мәні

Дәлдік бағалау

Ең кіші квадрат әдісі

Математикалық белгілерде бұл келесідей көрінеді:

Бастапқыда түзу сызықты қолдану арқылы жуықтау туралы шешім қабылданғандықтан, бізде:

Бірнеше қарапайым түрлендірулерден кейін, соның ішінде 2-ге бөлу және қосындыларды өңдеу, біз мынаны аламыз:

Excel бағдарламасында ең кіші квадраттарды қалай жүзеге асыруға болады

Y үшін белгілі мәндер ауқымы (бұл жағдайда тауар айналымы туралы деректер);
диапазон x 1 , …x n , яғни сауда алаңының көлемі;
x-тің белгілі және белгісіз мәндері, ол үшін айналым көлемін білу қажет (олардың жұмыс парағында орналасуы туралы ақпаратты төменде қараңыз).

Кейбір мүмкіндіктер

Регрессиялық талдау тіпті манекендерге де қол жетімді болуы мүмкін. Белгісіз айнымалылар массивінің мәнін болжауға арналған Excel формуласын - TREND - тіпті ең кіші квадраттар туралы ешқашан естімегендер де пайдалана алады. Оның жұмысының кейбір ерекшеліктерін білу жеткілікті. Сондай-ақ:

Егер сіз y айнымалысының белгілі мәндерінің ауқымын бір жолға немесе бағанға орналастырсаңыз, онда x белгілі мәндері бар әрбір жолды (бағанды) бағдарлама жеке айнымалы ретінде қабылдайды.
TREND терезесінде белгілі x диапазоны көрсетілмесе, Excel бағдарламасында функцияны пайдаланған кезде бағдарлама оны бүтін сандардан тұратын массив ретінде қарастырады, олардың саны берілген мәндері бар диапазонға сәйкес келеді. айнымалы y.
«Болжамды» мәндердің массивін шығару үшін трендті есептеуге арналған өрнек массив формуласы ретінде енгізілуі керек.
Егер x-тің жаңа мәндері көрсетілмесе, TREND функциясы оларды белгілі мәндерге тең деп санайды. Егер олар көрсетілмесе, онда аргумент ретінде 1-массив алынады; 2; 3; 4;…, ол бұрыннан көрсетілген y параметрлері бар диапазонға сәйкес.
Жаңа x мәндерін қамтитын ауқымда берілген y мәндерін қамтитын ауқыммен бірдей немесе бірнеше жолдар немесе бағандар болуы керек. Басқаша айтқанда, ол тәуелсіз айнымалыларға пропорционалды болуы керек.
Белгілі x мәндері бар массив бірнеше айнымалыларды қамтуы мүмкін. Алайда, егер біз тек біреуі туралы айтатын болсақ, онда x және y берілген мәндері бар диапазондар пропорционалды болуы керек. Бірнеше айнымалы болған жағдайда, берілген y мәндері бар диапазон бір бағанға немесе бір жолға сәйкес келуі керек.

БОЛЖАУ функциясы

Excel бағдарламасындағы регрессиялық талдау бірнеше функцияларды қолдану арқылы жүзеге асырылады. Солардың бірі «Болжау» деп аталады. Ол «TREND-ке» ұқсас, яғни ол ең кіші квадраттар әдісін қолданатын есептеулер нәтижесін береді. Алайда Y мәні белгісіз бір Х үшін ғана.