უმცირესი კვადრატების მეთოდი (LSM) ეფუძნება შერჩეული ფუნქციის კვადრატული გადახრების ჯამის მინიმიზაციას შესასწავლი მონაცემებიდან. ამ სტატიაში ჩვენ ვაახლოებთ არსებულ მონაცემებს ხაზოვანი ფუნქციის გამოყენებითწ = ა x + ბ .

მინიმალური კვადრატის მეთოდი(ინგლისური) ჩვეულებრივი სულ მცირე კვადრატები , OLS) არის რეგრესიული ანალიზის ერთ-ერთი ძირითადი მეთოდი უცნობი პარამეტრების შეფასების თვალსაზრისით რეგრესიის მოდელებინიმუშის მონაცემების მიხედვით.

განვიხილოთ დაახლოება ფუნქციების მიხედვით, დამოკიდებულია მხოლოდ ერთ ცვლადზე:

• ხაზოვანი: y=ax+b (ეს სტატია)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

შენიშვნა: ამ სტატიაში განხილულია მე-3-დან მე-6 ხარისხამდე მრავალწევრით მიახლოების შემთხვევები. აქ განიხილება ტრიგონომეტრიული მრავალწევრით მიახლოება.

ხაზოვანი დამოკიდებულება

ჩვენ გვაინტერესებს 2 ცვლადის ურთიერთობა Xდა წ. არსებობს ვარაუდი, რომ წდამოკიდებულია Xხაზოვანი კანონის მიხედვით წ = ნაჯახი + ბ. ამ ურთიერთობის პარამეტრების დასადგენად მკვლევარმა გააკეთა დაკვირვება: x i-ის თითოეული მნიშვნელობისთვის გაკეთდა y i-ის გაზომვა (იხ. ფაილის მაგალითი). შესაბამისად, იყოს 20 წყვილი მნიშვნელობა (х i; y i).

Შენიშვნა:თუ ცვლილება ეტაპობრივად X არის მუდმივი, შემდეგ აშენება გაფანტული ნაკვეთებიშეიძლება გამოყენებულ იქნას, თუ არა, მაშინ უნდა გამოიყენოთ დიაგრამის ტიპი წერტილოვანი .

დიაგრამიდან აშკარაა, რომ ცვლადებს შორის კავშირი ახლოსაა წრფივთან. იმის გასაგებად, თუ რომელი სწორი ხაზიდან ყველაზე "სწორად" აღწერს ცვლადებს შორის ურთიერთობას, აუცილებელია განვსაზღვროთ კრიტერიუმი, რომლითაც მოხდება ხაზების შედარება.

როგორც ასეთი კრიტერიუმი, ჩვენ ვიყენებთ გამოთქმას:

სადაც ŷ მე = ა * x i + ბ ; n – მნიშვნელობების წყვილის რაოდენობა (ჩვენს შემთხვევაში n=20)

ზემოთ მოცემული გამოხატულება არის კვადრატული მანძილების ჯამი y i და ŷ i დაკვირვებულ მნიშვნელობებს შორის და ხშირად აღინიშნება როგორც SSE ( ჯამი დან კვადრატში შეცდომები (ნარჩენები), კვადრატული შეცდომების ჯამი (ნარჩენები)) .

მინიმალური კვადრატის მეთოდიარის ასეთი ხაზის შერჩევა ŷ = ნაჯახი + ბ, რომლისთვისაც ზემოაღნიშნული გამოხატულება იღებს მინიმალურ მნიშვნელობას.

Შენიშვნა:ნებისმიერი ხაზი ორგანზომილებიან სივრცეში ცალსახად განისაზღვრება 2 პარამეტრის მნიშვნელობებით: ა (დახრილობა) და ბ (ცვლა).

ითვლება, რომ რაც უფრო მცირეა კვადრატული მანძილების ჯამი, მით უკეთესია შესაბამისი ხაზი მიახლოებით ხელმისაწვდომ მონაცემებს და შემდგომში შეიძლება გამოყენებულ იქნას y-ის მნიშვნელობების პროგნოზირებისთვის x ცვლადიდან. ცხადია, რომ მაშინაც კი, თუ რეალურად არ არსებობს კავშირი ცვლადებს შორის ან ურთიერთობა არაწრფივია, მაშინ LSM მაინც შეარჩევს „საუკეთესო“ ხაზს. ამრიგად, LSM არაფერს ამბობს ცვლადების რეალური ურთიერთობის არსებობის შესახებ, მეთოდი უბრალოდ საშუალებას გაძლევთ აირჩიოთ ფუნქციის ასეთი პარამეტრები ა და ბ , რისთვისაც ზემოაღნიშნული გამოხატულება მინიმალურია.

არც თუ ისე რთული მათემატიკური ოპერაციების გაკეთების შემდეგ (იხილეთ მეტი დეტალები), შეგიძლიათ გამოთვალოთ პარამეტრები ა და ბ :

როგორც ფორმულიდან ჩანს, პარამეტრი ა არის კოვარიანტობის და , ასე რომ MS EXCEL-ში პარამეტრის გამოთვლა ა შეგიძლიათ გამოიყენოთ შემდეგი ფორმულები (იხ მაგალითი ფაილის ფურცელი Linear):

= COVAR(B26:B45;C26:C45)/VAR.G(B26:B45)ან

= COVARIATION.B(B26:B45;C26:C45)/VAR.B(B26:B45)

ასევე პარამეტრის გამოთვლა ა შეგიძლიათ გამოიყენოთ ფორმულა = SLOPE(C26:C45;B26:B45). პარამეტრისთვის ბ გამოიყენეთ ფორმულა = INTERCUT(C26:C45;B26:B45) .

და ბოლოს, LINEST() ფუნქცია საშუალებას გაძლევთ გამოთვალოთ ორივე პარამეტრი ერთდროულად. ფორმულის შესაყვანად LINEST(C26:C45;B26:B45)აირჩიეთ ზედიზედ 2 უჯრედი და დააჭირეთ CTRL + SHIFT + ENTER(იხილეთ სტატია შესახებ). მარცხენა უჯრედი დააბრუნებს მნიშვნელობას ა , მარჯვნივ ბ .

შენიშვნა: იმისათვის, რომ არ აურიოთ შეყვანა მასივის ფორმულებიდამატებით მოგიწევთ INDEX() ფუნქციის გამოყენება. ფორმულა = INDEX(LINEST(C26:C45,B26:B45),1)ან უბრალოდ = LINEST(C26:C45;B26:B45)დააბრუნებს პარამეტრს, რომელიც პასუხისმგებელია ხაზის დახრილობაზე, ე.ი. ა . ფორმულა = INDEX(LINEST(C26:C45,B26:B45),2)დააბრუნებს Y ღერძთან წრფის გადაკვეთაზე პასუხისმგებელ პარამეტრს, ე.ი. ბ .

პარამეტრების გაანგარიშების შემდეგ, გაფანტვახაზის დახატვა შეიძლება.

უმცირესი კვადრატების მეთოდის გამოყენებით სწორი ხაზის დახაზვის კიდევ ერთი გზა არის დიაგრამის ინსტრუმენტი ტრენდის ხაზი. ამისათვის აირჩიეთ დიაგრამა, აირჩიეთ მენიუდან განლაგების ჩანართი, in ჯგუფური ანალიზიდააწკაპუნეთ ტრენდის ხაზი, მაშინ წრფივი დაახლოება .

დიალოგურ ფანჯარაში ველის „განტოლების ჩვენება დიაგრამაში“ მონიშვნით, შეგიძლიათ დარწმუნდეთ, რომ ზემოთ ნაპოვნი პარამეტრები ემთხვევა დიაგრამაში მოცემულ მნიშვნელობებს.

შენიშვნა: იმისათვის, რომ პარამეტრები ემთხვეოდეს, დიაგრამის ტიპი უნდა იყოს . ფაქტია, რომ დიაგრამის აგებისას განრიგი x-ღერძის მნიშვნელობების დაყენება შეუძლებელია მომხმარებლის მიერ (მომხმარებელს შეუძლია მიუთითოს მხოლოდ ეტიკეტები, რომლებიც გავლენას არ მოახდენს წერტილების ადგილმდებარეობაზე). X მნიშვნელობების ნაცვლად გამოიყენება თანმიმდევრობა 1; 2; 3; … (კატეგორიის ნუმერაციისთვის). ამიტომ თუ აშენებს ტრენდის ხაზიტიპის დიაგრამაზე განრიგი, მაშინ ამ თანმიმდევრობის მნიშვნელობები გამოყენებული იქნება X-ის რეალური მნიშვნელობების ნაცვლად, რაც გამოიწვევს არასწორ შედეგს (თუ, რა თქმა უნდა, X-ის რეალური მნიშვნელობები არ ემთხვევა თანმიმდევრობას 1; 2. ; 3; ...).

ისე, სამსახურში შეატყობინეს ინსპექციას, სტატია დაიწერა სახლში კონფერენციაზე - ახლა შეგიძლიათ ბლოგში დაწეროთ. სანამ ჩემს მონაცემებს ვამუშავებდი, მივხვდი, რომ არ შემეძლო არ დავწერო Excel-ში ძალიან მაგარი და საჭირო დანამატის შესახებ, რომელსაც ჰქვია. ასე რომ, სტატია დაეთმობა ამ კონკრეტულ დანამატს და მე გეტყვით ამის შესახებ გამოყენების მაგალითის გამოყენებით უმცირესი კვადრატების მეთოდი(LSM) ექსპერიმენტული მონაცემების აღწერაში განტოლების უცნობი კოეფიციენტების მოსაძებნად.

როგორ ჩართოთ დანამატი "გადაწყვეტის ძიება"

პირველ რიგში, მოდით გაერკვნენ, თუ როგორ უნდა ჩართოთ ეს დანამატი.

1. გადადით მენიუში "ფაილი" და აირჩიეთ "Excel Options"

2. ფანჯარაში, რომელიც გამოჩნდება, აირჩიეთ "Search for solution" და დააწკაპუნეთ "go".

3. მომდევნო ფანჯარაში „გადაწყვეტის ძიება“ პუნქტის წინ დააწკაპუნეთ და დააწკაპუნეთ „OK“.

4. დანამატი გააქტიურებულია - ახლა მისი ნახვა შეგიძლიათ მენიუს პუნქტში "მონაცემები".

მინიმალური კვადრატის მეთოდი

ახლა მოკლედ შესახებ მინიმალური კვადრატების მეთოდი (LSM) და სად შეიძლება მისი გამოყენება.

ვთქვათ, გვაქვს მონაცემთა ნაკრები მას შემდეგ, რაც ჩავატარეთ ექსპერიმენტი, სადაც შევისწავლეთ X მნიშვნელობის გავლენა Y მნიშვნელობაზე.

ჩვენ გვსურს აღვწეროთ ეს გავლენა მათემატიკურად, რათა მოგვიანებით გამოვიყენოთ ეს ფორმულა და ვიცოდეთ, რომ თუ 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. A1 უჯრედში შეიყვანეთ მნიშვნელობა "1". ეს ერთეული იქნება პირველი მიახლოება ჩვენი ფუნქციური დამოკიდებულების (k) კოეფიციენტის რეალურ მნიშვნელობასთან y=kx.

3. B სვეტში გვაქვს X პარამეტრის მნიშვნელობები, C სვეტში - Y პარამეტრის მნიშვნელობები. სვეტის D უჯრედებში შევიყვანთ ფორმულას: "ფაქტორი k გამრავლებული X-ის მნიშვნელობაზე". მაგალითად, უჯრედში D1 შეიყვანეთ "=A1*B1", უჯრედში D2 შეიყვანეთ "=A1*B2" და ა.შ.

4. ჩვენ გვჯერა, რომ k კოეფიციენტი უდრის ერთს და ფუნქცია f (x) \u003d y \u003d 1 * x არის ჩვენი ამოხსნის პირველი მიახლოება. ჩვენ შეგვიძლია გამოვთვალოთ კვადრატული განსხვავებების ჯამი Y-ის გაზომილ მნიშვნელობებსა და მათ შორის, რომლებიც გამოითვლება ფორმულით y=1*x. ჩვენ შეგვიძლია გავაკეთოთ ეს ყველაფერი ხელით, უჯრედების შესაბამისი მითითებების ფორმულაში გადაყვანით: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... და ა.შ. ცდებიან და გვესმის, რომ ბევრი დრო დავკარგეთ. Excel-ში კვადრატული სხვაობების ჯამის გამოსათვლელად არის სპეციალური ფორმულა "SUMQDIFF", რომელიც ყველაფერს გააკეთებს ჩვენთვის. შევიტანოთ ის A2 უჯრედში და დავაყენოთ საწყისი მონაცემები: გაზომილი მნიშვნელობების დიაპაზონი Y (სვეტი C) და გამოთვლილი Y მნიშვნელობების დიაპაზონი (სვეტი D).

4. გამოითვალა კვადრატების სხვაობათა ჯამი - ახლა გადადით "მონაცემების" ჩანართზე და აირჩიეთ "გამოსავალის ძიება".

5. მენიუში, რომელიც გამოჩნდება, აირჩიეთ უჯრედი A1, როგორც შესაცვლელი უჯრედი (კ კოეფიციენტის მქონე).

6. როგორც სამიზნე, აირჩიეთ უჯრედი A2 და დააყენეთ პირობა "დააყენეთ უდრის მინიმალურ მნიშვნელობას". გახსოვდეთ, რომ ეს არის უჯრედი, სადაც ვიანგარიშებთ გამოთვლილ და გაზომილ მნიშვნელობებს შორის კვადრატული სხვაობების ჯამს და ეს თანხა უნდა იყოს მინიმალური. ჩვენ ვაჭერთ "შესრულებას".

7. არჩეულია კოეფიციენტი k. ახლა ჩანს, რომ გამოთვლილი მნიშვნელობები ახლა ძალიან ახლოს არის გაზომილთან.

P.S.

ზოგადად, რა თქმა უნდა, Excel-ში ექსპერიმენტული მონაცემების მიახლოებისთვის, არსებობს სპეციალური ხელსაწყოები, რომლებიც საშუალებას გაძლევთ აღწეროთ მონაცემები წრფივი, ექსპონენციალური, სიმძლავრის და პოლინომიური ფუნქციის გამოყენებით, ასე რომ თქვენ ხშირად შეგიძლიათ ამის გარეშე. დამატებები "გადაწყვეტის ძიება". მიახლოების ყველა ამ მეთოდზე ვისაუბრე ჩემს სტატიაში, ასე რომ, თუ გაინტერესებთ, გადახედეთ. მაგრამ როცა საქმე რაღაც ეგზოტიკურ ფუნქციას ეხება ერთი უცნობი კოეფიციენტითან ოპტიმიზაციის პრობლემები, მაშინ აქ ზედნაშენიისევე როგორც შესაძლებელია.

დანამატი "გადაწყვეტის ძიება"შეიძლება გამოყენებულ იქნას სხვა ამოცანებისთვის, მთავარია გავიგოთ არსი: არის უჯრედი, სადაც ვირჩევთ მნიშვნელობას და არის სამიზნე უჯრედი, რომელშიც დაყენებულია პირობა უცნობი პარამეტრის არჩევისთვის.
Სულ ეს არის! შემდეგ სტატიაში მე მოგიყვებით ზღაპარს შვებულების შესახებ, რათა არ გამოტოვოთ სტატიის გამოშვება,

4.1. ჩაშენებული ფუნქციების გამოყენება

გაანგარიშება რეგრესიის კოეფიციენტებიხორციელდება ფუნქციის გამოყენებით

LINEST(ღირებულებები_y; მნიშვნელობები_x; კონსტ; სტატისტიკა),

ღირებულებები_y- y მნიშვნელობების მასივი,

მნიშვნელობები_x- არჩევითი მნიშვნელობების მასივი xთუ მასივი Xგამოტოვებული, ვარაუდობენ, რომ ეს არის იგივე ზომის მასივი (1;2;3;...) ღირებულებები_y,

კონსტ- ლოგიკური მნიშვნელობა, რომელიც მიუთითებს, საჭიროა თუ არა მუდმივი ბტოლი იყო 0. თუ კონსტაქვს მნიშვნელობა მართალიან გამოტოვებული, მაშინ ბგამოითვლება ჩვეულებრივი გზით. თუ არგუმენტი კონსტმცდარია, მაშინ ბითვლება 0-ად და მნიშვნელობები აარჩეულია ისე, რომ მიმართება y=ax.

სტატისტიკა- ლოგიკური მნიშვნელობა, რომელიც მიუთითებს, საჭიროა თუ არა დამატებითი რეგრესიის სტატისტიკის დაბრუნება. თუ არგუმენტი სტატისტიკააქვს მნიშვნელობა მართალი, შემდეგ ფუნქცია LINESTაბრუნებს დამატებით რეგრესიის სტატისტიკას. თუ არგუმენტი სტატისტიკააქვს მნიშვნელობა ცრუან გამოტოვებული, შემდეგ ფუნქცია LINESTაბრუნებს მხოლოდ კოეფიციენტს ადა მუდმივი ბ.

უნდა გვახსოვდეს, რომ ფუნქციების შედეგი LINEST()არის მნიშვნელობების ნაკრები - მასივი.

გაანგარიშებისთვის კორელაციის კოეფიციენტიფუნქცია გამოიყენება

კორელი(მასივი 1;მასივი2),

კორელაციის კოეფიციენტის მნიშვნელობების დაბრუნება, სადაც მასივი 1- ღირებულებების მასივი წ, მასივი2- ღირებულებების მასივი x. მასივი 1და მასივი2უნდა იყოს იგივე ზომა.

მაგალითი 1. დამოკიდებულება წ(x) წარმოდგენილია ცხრილში. აშენება რეგრესიის ხაზიდა გამოთვალეთ კორელაციის კოეფიციენტი.

წ		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

მოდით შევიტანოთ მნიშვნელობების ცხრილი MS Excel ფურცელში და ავაშენოთ სკატერის ნაკვეთი. სამუშაო ფურცელი მიიღებს ნახ. 2.

რეგრესიის კოეფიციენტების მნიშვნელობების გამოსათვლელად ადა ბაირჩიეთ უჯრედები A7:B7,მოდით მივმართოთ ფუნქციის ოსტატს და კატეგორიაში სტატისტიკურიაირჩიეთ ფუნქცია LINEST. შეავსეთ დიალოგური ფანჯარა, რომელიც ნაჩვენებია ნახ. 3 და დააჭირეთ კარგი.

შედეგად, გამოთვლილი მნიშვნელობა გამოჩნდება მხოლოდ უჯრედში A6(ნახ. 4). იმისათვის, რომ მნიშვნელობა გამოჩნდეს უჯრედში B6თქვენ უნდა შეხვიდეთ რედაქტირების რეჟიმში (გასაღები F2)და შემდეგ დააჭირეთ კლავიშთა კომბინაციას CTRL+SHIFT+ENTER.

კორელაციის კოეფიციენტის მნიშვნელობის გამოთვლა უჯრედზე C6დაინერგა შემდეგი ფორმულა:

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

რეგრესიის კოეფიციენტების ცოდნა ადა ბგამოთვალეთ ფუნქციის მნიშვნელობები წ=ნაჯახი+ბმოცემულისთვის x. ამისათვის ჩვენ წარმოგიდგენთ ფორმულას

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

და დააკოპირეთ დიაპაზონში С5:J5(ნახ. 5).

დავხატოთ რეგრესიის ხაზი დიაგრამაზე. აირჩიეთ დიაგრამაზე ექსპერიმენტული წერტილები, დააწკაპუნეთ მარჯვენა ღილაკით და აირჩიეთ ბრძანება საწყისი მონაცემები. გამოსულ დიალოგურ ფანჯარაში (სურ. 5) აირჩიეთ ჩანართი მწკრივიდა დააჭირეთ ღილაკს დამატება. შეავსეთ შეყვანის ველები, როგორც ნაჩვენებია ნახ. 6 და დააჭირეთ ღილაკს კარგი. ექსპერიმენტული მონაცემების დიაგრამას დაემატება რეგრესიის ხაზი. ნაგულისხმევად, მისი გრაფიკი ნაჩვენები იქნება წერტილების სახით, რომლებიც არ არის დაკავშირებული გამარტივებული ხაზებით.

რეგრესიის ხაზის გარეგნობის შესაცვლელად, შეასრულეთ შემდეგი ნაბიჯები. დააწკაპუნეთ მაუსის მარჯვენა ღილაკით ხაზის გრაფიკის გამოსახულ წერტილებზე, აირჩიეთ ბრძანება დიაგრამის ტიპიდა დააყენეთ სკატერის ნაკვეთის ტიპი, როგორც ნაჩვენებია ნახ. 7.

ხაზის ტიპი, ფერი და სისქე შეიძლება შეიცვალოს შემდეგნაირად. აირჩიეთ ხაზი დიაგრამაზე, დააჭირეთ მაუსის მარჯვენა ღილაკს და აირჩიეთ ბრძანება კონტექსტურ მენიუში მონაცემთა სერიის ფორმატი…შემდეგი, გააკეთეთ პარამეტრები, მაგალითად, როგორც ნაჩვენებია ნახ. რვა.

ყველა გარდაქმნის შედეგად ვიღებთ ექსპერიმენტული მონაცემების გრაფიკს და რეგრესიის ხაზს ერთ გრაფიკულ არეში (სურ. 9).

4.2. ტრენდის ხაზის გამოყენება.

MS Excel-ში სხვადასხვა მიახლოებითი დამოკიდებულების აგება განხორციელებულია სქემის თვისების სახით - ტრენდის ხაზი.

მაგალითი 2. ექსპერიმენტის შედეგად დადგინდა გარკვეული ტაბულური დამოკიდებულება.

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

აირჩიეთ და შექმენით მიახლოებითი დამოკიდებულება. შექმენით ტაბულური და დამონტაჟებული ანალიტიკური დამოკიდებულებების გრაფიკები.

პრობლემის გადაწყვეტა შეიძლება დაიყოს შემდეგ ეტაპებად: საწყისი მონაცემების შეყვანა, სკატერის ნაკვეთის აგება და ტენდენციის ხაზის დამატება ამ ნაკვეთზე.

განვიხილოთ ეს პროცესი დეტალურად. შევიტანოთ საწყისი მონაცემები სამუშაო ფურცელში და გამოვსახოთ ექსპერიმენტული მონაცემები. შემდეგი, აირჩიეთ ექსპერიმენტული წერტილები სქემაზე, დააწკაპუნეთ მარჯვენა ღილაკით და გამოიყენეთ ბრძანება დამატებალ ტრენდის ხაზი(სურ. 10).

დიალოგური ფანჯარა, რომელიც გამოჩნდება, საშუალებას გაძლევთ შექმნათ მიახლოებითი დამოკიდებულება.

ამ ფანჯრის პირველი ჩანართი (ნახ. 11) მიუთითებს მიახლოებითი დამოკიდებულების ტიპზე.

მეორე (ნახ. 12) განსაზღვრავს კონსტრუქციის პარამეტრებს:

მიახლოებითი დამოკიდებულების დასახელება;

პროგნოზირება წინ (უკან) ჩართულია ნერთეულები (ეს პარამეტრი განსაზღვრავს რამდენი ერთეული წინ (უკან) არის საჭირო ტრენდის ხაზის გასაგრძელებლად);

აჩვენოს თუ არა მრუდის გადაკვეთის წერტილი წრფესთან y=კონსტ;

დიაგრამაზე მიახლოებითი ფუნქციის ჩვენება თუ არა (დიაგრამის პარამეტრზე განტოლების ჩვენება);

დიაგრამაზე სტანდარტული გადახრის მნიშვნელობის დაყენება თუ არა (პარამეტრი დიაგრამაზე მიახლოების სანდოობის მნიშვნელობის დასაყენებლად).

ავირჩიოთ მეორე ხარისხის მრავალწევრი მიახლოებით დამოკიდებულებად (სურ. 11) და გამოვიტანოთ ამ მრავალწევრის აღწერის განტოლება გრაფიკზე (სურ. 12). შედეგად მიღებული დიაგრამა ნაჩვენებია ნახ. ცამეტი.

ანალოგიურად, თან ტენდენციის ხაზებითქვენ შეგიძლიათ აირჩიოთ ისეთი დამოკიდებულებების პარამეტრები, როგორიცაა

ხაზოვანი წ=a∙x+ბ,

ლოგარითმული წ=ლნ(x)+ბ,

ექსპონენციალური წ=ა∙ებ,

ძალა წ=a x b,

მრავალწევრი წ=a∙x 2 +b∙x+გ, წ=a∙x 3 +b∙x 2 +c∙x+dდა ასე შემდეგ, მე-6 ხარისხის მრავალწევრის ჩათვლით,

ხაზოვანი ფილტრაცია.

4.3. პარამეტრების ანალიზის ინსტრუმენტის გამოყენება: გამოსავლის პოვნა.

მნიშვნელოვანი ინტერესია MS Excel-ში ფუნქციონალური დამოკიდებულების პარამეტრების არჩევის დანერგვა უმცირესი კვადრატების მეთოდით ოფციონის ანალიზის ხელსაწყოს გამოყენებით: გამოსავლის ძიება. ეს ტექნიკა საშუალებას გაძლევთ აირჩიოთ ნებისმიერი სახის ფუნქციის პარამეტრები. განვიხილოთ ეს შესაძლებლობა შემდეგი პრობლემის მაგალითზე.

მაგალითი 3. ექსპერიმენტის შედეგად ცხრილში წარმოდგენილი z(t) დამოკიდებულება

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

აირჩიეთ დამოკიდებულების კოეფიციენტები Z(t)=4 +Bt 3 +Ct 2 +Dt+Kუმცირესი კვადრატების მეთოდით.

ეს პრობლემა უდრის ხუთი ცვლადის ფუნქციის მინიმუმის პოვნის პრობლემას

განვიხილოთ ოპტიმიზაციის პრობლემის გადაჭრის პროცესი (სურ. 14).

მიეცით ღირებულებები მაგრამ, AT, თან, დდა რომინახება უჯრედებში A7:E7. გამოთვალეთ ფუნქციის თეორიული მნიშვნელობები ზ(ტ)=At4+Bt3+Ct2+Dt+Kმოცემულისთვის ტ(B2: J2). ამისათვის საკანში B4შეიყვანეთ ფუნქციის მნიშვნელობა პირველ წერტილში (უჯრედი B2):

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

დააკოპირეთ ეს ფორმულა დიაპაზონში С4:J4და მიიღეთ ფუნქციის მოსალოდნელი მნიშვნელობა წერტილებში, რომელთა აბსციები ინახება უჯრედებში B2: J2.

უჯრედამდე B5ჩვენ შემოგთავაზებთ ფორმულას, რომელიც ითვლის ექსპერიმენტულ და გამოთვლილ წერტილებს შორის სხვაობის კვადრატს:

B5=(B4-B3)^2,

და დააკოპირეთ დიაპაზონში С5:J5. საკანში F7ჩვენ ვინახავთ მთლიან კვადრატულ შეცდომას (10). ამისათვის ჩვენ შემოგთავაზებთ ფორმულას:

F7 = SUM(B5:J5).

მოდით გამოვიყენოთ ბრძანება სერვისი® მოძებნეთ გამოსავალიდა გადაჭრით ოპტიმიზაციის პრობლემა შეზღუდვების გარეშე. შეავსეთ შესაბამისი შეყვანის ველები დიალოგურ ფანჯარაში, რომელიც ნაჩვენებია ნახ. 14 და დააჭირეთ ღილაკს გაიქეცი. თუ გამოსავალი არის ნაპოვნი, ფანჯარა ნაჩვენებია ნახ. თხუთმეტი.

გადაწყვეტილების ბლოკის შედეგი იქნება გამომავალი უჯრედებში A7:E7პარამეტრის მნიშვნელობებიფუნქციები ზ(ტ)=At4+Bt3+Ct2+Dt+K. უჯრედებში B4: J4ვიღებთ მოსალოდნელი ფუნქციის მნიშვნელობასასტარტო წერტილებში. საკანში F7შეინახება მთლიანი კვადრატული შეცდომა.

დიაპაზონის არჩევის შემთხვევაში შეგიძლიათ აჩვენოთ ექსპერიმენტული წერტილები და დაყენებული ხაზი იმავე გრაფიკულ არეში B2: J4, დარეკეთ გრაფიკის ოსტატი, და შემდეგ დაფორმატება მიღებული გრაფიკების გამოჩენა.

ბრინჯი. 17 აჩვენებს MS Excel-ის სამუშაო ფურცელს გამოთვლების გაკეთების შემდეგ.

რომელიც პოულობს ყველაზე ფართო გამოყენებას მეცნიერებისა და პრაქტიკის სხვადასხვა დარგში. ეს შეიძლება იყოს ფიზიკა, ქიმია, ბიოლოგია, ეკონომიკა, სოციოლოგია, ფსიქოლოგია და ა.შ. ბედის ნებით ხშირად მიწევს ეკონომიკასთან შეხება და ამიტომ დღეს დაგიმზადებ ბილეთს გასაოცარ ქვეყანაში ე.წ. ეკონომიკა=) ... ეს როგორ არ გინდა?! იქ ძალიან კარგია - უბრალოდ უნდა გადაწყვიტო! ...მაგრამ ის, რაც თქვენ ალბათ ნამდვილად გსურთ, არის ისწავლოთ პრობლემების გადაჭრა უმცირესი კვადრატები. და განსაკუთრებით გულმოდგინე მკითხველები ისწავლიან მათ გადაჭრას არა მხოლოდ ზუსტად, არამედ ძალიან სწრაფად ;-) მაგრამ ჯერ პრობლემის ზოგადი განცხადება+ დაკავშირებული მაგალითი:

მოდით, ინდიკატორები შეისწავლოს ზოგიერთ საგნობრივ სფეროში, რომელსაც აქვს რაოდენობრივი გამოხატულება. ამავდროულად, ყველა მიზეზი არსებობს იმის დასაჯერებლად, რომ ინდიკატორი დამოკიდებულია ინდიკატორზე. ეს ვარაუდი შეიძლება იყოს როგორც მეცნიერული ჰიპოთეზა, ასევე ელემენტარული საღი აზრის საფუძველზე. თუმცა, მეცნიერებას თავი დავანებოთ და უფრო მადისაღმძვრელი სფეროები გამოვიკვლიოთ - კერძოდ, სასურსათო მაღაზიები. აღნიშნეთ:

– სასურსათო მაღაზიის საცალო ფართი, კვ.მ.
- სასურსათო მაღაზიის წლიური ბრუნვა, მილიონი რუბლი.

სავსებით გასაგებია, რომ რაც უფრო დიდია მაღაზიის ფართობი, მით მეტია მისი ბრუნვა უმეტეს შემთხვევაში.

დავუშვათ, რომ დაკვირვების / ექსპერიმენტების / გამოთვლების / ტამბურით ცეკვის ჩატარების შემდეგ, ჩვენს განკარგულებაშია რიცხვითი მონაცემები:

სასურსათო მაღაზიებთან, ვფიქრობ, ყველაფერი ნათელია: - ეს არის 1-ლი მაღაზიის ფართობი, - მისი წლიური ბრუნვა, - მე-2 მაღაზიის ფართობი, - მისი წლიური ბრუნვა და ა.შ. სხვათა შორის, საერთოდ არ არის აუცილებელი საიდუმლო მასალებზე წვდომა - ბრუნვის საკმაოდ ზუსტი შეფასება შეიძლება მიღებულ იქნას გამოყენებით მათემატიკური სტატისტიკა. თუმცა, ნუ გეშლებათ, კომერციული ჯაშუშობის კურსი უკვე ფასიანია =)

ტაბულური მონაცემები ასევე შეიძლება დაიწეროს წერტილების სახით და გამოსახული იყოს ჩვენთვის ჩვეულებრივი გზით. დეკარტის სისტემა .

მოდით ვუპასუხოთ მნიშვნელოვან კითხვას: რამდენი ქულაა საჭირო თვისებრივი კვლევისთვის?

რაც უფრო დიდია, მით უკეთესი. მინიმალური დასაშვები ნაკრები შედგება 5-6 ქულისგან. გარდა ამისა, მცირე რაოდენობის მონაცემებით, „არანორმალური“ შედეგები არ უნდა იყოს შეტანილი ნიმუშში. ასე, მაგალითად, პატარა ელიტარულ მაღაზიას შეუძლია დაეხმაროს უფრო მასშტაბურ ბრძანებებს, ვიდრე „მათი კოლეგები“, რითაც ამახინჯებს ზოგადი ნიმუში, რომელიც უნდა მოიძებნოს!

თუ ეს საკმაოდ მარტივია, ჩვენ უნდა ავირჩიოთ ფუნქცია, განრიგირომელიც რაც შეიძლება ახლოს გადის წერტილებთან . ასეთ ფუნქციას ე.წ მიახლოებითი (დაახლოება - დაახლოება)ან თეორიული ფუნქცია . საერთოდ, აქ მაშინვე ჩნდება აშკარა „პრეტენდენტი“ - მაღალი ხარისხის პოლინომი, რომლის გრაფიკი გადის ყველა წერტილში. მაგრამ ეს ვარიანტი რთულია და ხშირად უბრალოდ არასწორია. (რადგან დიაგრამა მუდმივად "ქარი" და ცუდად ასახავს მთავარ ტენდენციას).

ამრიგად, სასურველი ფუნქცია უნდა იყოს საკმარისად მარტივი და ამავე დროს ადეკვატურად ასახავდეს დამოკიდებულებას. როგორც თქვენ ალბათ მიხვდებით, ასეთი ფუნქციების პოვნის ერთ-ერთ მეთოდს ე.წ უმცირესი კვადრატები. პირველ რიგში, მოდით გავაანალიზოთ მისი არსი ზოგადი გზით. დაე, რომელიმე ფუნქცია მიახლოებით ახლდეს ექსპერიმენტულ მონაცემებს:


როგორ შევაფასოთ ამ მიახლოების სიზუსტე? ასევე გამოვთვალოთ განსხვავებები (გადახრები) ექსპერიმენტულ და ფუნქციურ მნიშვნელობებს შორის (ჩვენ ვსწავლობთ ნახატს). პირველი აზრი, რაც თავში მოდის, არის იმის შეფასება, თუ რამდენად დიდია თანხა, მაგრამ პრობლემა ის არის, რომ განსხვავებები შეიძლება იყოს უარყოფითი. (Მაგალითად, ) და ასეთი შეჯამების შედეგად გადახრები გააუქმებს ერთმანეთს. მაშასადამე, როგორც მიახლოების სიზუსტის შეფასება, ის თავის თავს გვთავაზობს ჯამის აღებას მოდულებიგადახრები:

ან დაკეცილი ფორმით: (მოულოდნელად, ვინ არ იცის: არის ჯამის ხატულა და არის დამხმარე ცვლადი - "მრიცხველი", რომელიც იღებს მნიშვნელობებს 1-დან ).

სხვადასხვა ფუნქციით ექსპერიმენტული წერტილების მიახლოებით, მივიღებთ სხვადასხვა მნიშვნელობებს და აშკარაა, რომ სადაც ეს ჯამი უფრო მცირეა, ეს ფუნქცია უფრო ზუსტია.

ასეთი მეთოდი არსებობს და ე.წ მინიმალური მოდულის მეთოდი. თუმცა, პრაქტიკაში ის ბევრად უფრო ფართოდ გავრცელდა. მინიმალური კვადრატის მეთოდი, რომელშიც შესაძლო უარყოფითი მნიშვნელობები აღმოიფხვრება არა მოდულით, არამედ გადახრების კვადრატში:

, რის შემდეგაც ძალისხმევა მიმართულია ისეთი ფუნქციის შერჩევაზე, რომ კვადრატული გადახრების ჯამი რაც შეიძლება პატარა იყო. სინამდვილეში, აქედან მოდის მეთოდის სახელი.

ახლა კი ჩვენ ვუბრუნდებით კიდევ ერთ მნიშვნელოვან პუნქტს: როგორც ზემოთ აღინიშნა, შერჩეული ფუნქცია საკმაოდ მარტივი უნდა იყოს - მაგრამ ასევე არსებობს მრავალი ასეთი ფუნქცია: ხაზოვანი , ჰიპერბოლური, ექსპონენციალური, ლოგარითმული, კვადრატული და ა.შ. და, რა თქმა უნდა, აქ მსურს დაუყოვნებლივ "შევამცირო საქმიანობის სფერო". რა კლასის ფუნქციები აირჩიოს კვლევისთვის? პრიმიტიული, მაგრამ ეფექტური ტექნიკა:

- ქულების დახატვის უმარტივესი გზა ნახატზე და გააანალიზეთ მათი მდებარეობა. თუ ისინი სწორ ხაზზე არიან, მაშინ უნდა მოძებნოთ სწორი ხაზის განტოლება ოპტიმალური მნიშვნელობებით და. სხვა სიტყვებით რომ ვთქვათ, ამოცანაა იპოვოთ ასეთი კოეფიციენტები - ისე, რომ კვადრატული გადახრების ჯამი იყოს ყველაზე მცირე.

თუ წერტილები მდებარეობს, მაგალითად, გასწვრივ ჰიპერბოლა, მაშინ ცხადია, რომ წრფივი ფუნქცია მისცემს ცუდ მიახლოებას. ამ შემთხვევაში, ჩვენ ვეძებთ ყველაზე "ხელსაყრელ" კოეფიციენტებს ჰიპერბოლის განტოლებისთვის - ისინი, რომლებიც იძლევა კვადრატების მინიმალურ ჯამს .

ახლა ყურადღება მიაქციეთ, რომ ორივე შემთხვევაში ჩვენ ვსაუბრობთ ორი ცვლადის ფუნქცია, რომლის არგუმენტებიც არის მოძებნილი დამოკიდებულების ვარიანტები:

და არსებითად, ჩვენ უნდა გადავწყვიტოთ სტანდარტული პრობლემა - ვიპოვოთ ორი ცვლადის ფუნქციის მინიმუმი.

გავიხსენოთ ჩვენი მაგალითი: დავუშვათ, რომ "მაღაზიის" წერტილები, როგორც წესი, განლაგებულია სწორ ხაზზე და არსებობს ყველა საფუძველი, რომ დავიჯეროთ მისი არსებობა. ხაზოვანი დამოკიდებულებაბრუნვა სავაჭრო ზონიდან. ვიპოვოთ ასეთი კოეფიციენტები "a" და "be" ისე, რომ კვადრატული გადახრების ჯამი იყო ყველაზე პატარა. ყველაფერი, როგორც ყოველთვის - პირველი 1-ლი რიგის ნაწილობრივი წარმოებულები. Მიხედვით წრფივი წესითქვენ შეგიძლიათ განასხვავოთ ჯამის ხატის ქვეშ:

თუ გსურთ გამოიყენოთ ეს ინფორმაცია ესეისთვის ან საკურსო ნაშრომისთვის, ძალიან მადლობელი ვიქნები წყაროების სიაში მოცემული ბმულისთვის, ასეთ დეტალურ გამოთვლებს ვერსად ნახავთ:

მოდით შევქმნათ სტანდარტული სისტემა:

ჩვენ ვამცირებთ თითოეულ განტოლებას "ორით" და, გარდა ამისა, "ვაყოფთ" ჯამებს:

შენიშვნა : დამოუკიდებლად გააანალიზეთ, რატომ შეიძლება ამოიღოთ "a" და "be" ჯამის ხატიდან. სხვათა შორის, ფორმალურად ეს შეიძლება გაკეთდეს თანხით

მოდით გადავიწეროთ სისტემა "გამოყენებითი" ფორმით:

რის შემდეგაც იწყება ჩვენი პრობლემის გადაჭრის ალგორითმის შედგენა:

ვიცით თუ არა წერტილების კოორდინატები? Ჩვენ ვიცით. თანხები შეგვიძლია ვიპოვოთ? ადვილად. ჩვენ ვწერთ უმარტივესს ორი წრფივი განტოლების სისტემა ორი უცნობით("ა" და "ბეჰ"). ჩვენ ვხსნით სისტემას, მაგალითად, კრამერის მეთოდი, რის შედეგადაც სტაციონარული წერტილი. შემოწმება საკმარისი პირობა ექსტრემისთვის, შეგვიძლია გადავამოწმოთ, რომ ამ ეტაპზე ფუნქცია ზუსტად აღწევს მინიმალური. გადამოწმება დაკავშირებულია დამატებით გამოთვლებთან და ამიტომ მას კულისებში დავტოვებთ. (საჭიროების შემთხვევაში, დაკარგული ჩარჩოს ნახვა შესაძლებელია). ჩვენ ვაკეთებთ საბოლოო დასკვნას:

ფუნქცია საუკეთესო გზა (ყოველ შემთხვევაში სხვა წრფივ ფუნქციასთან შედარებით)აახლოებს ექსპერიმენტულ წერტილებს . უხეშად რომ ვთქვათ, მისი გრაფიკი რაც შეიძლება ახლოს გადის ამ წერტილებთან. ტრადიციაში ეკონომეტრიაშედეგად მიახლოებით ფუნქციას ასევე უწოდებენ დაწყვილებული წრფივი რეგრესიის განტოლება .

განხილულ პრობლემას დიდი პრაქტიკული მნიშვნელობა აქვს. ჩვენი მაგალითის სიტუაციაში, განტოლება საშუალებას გაძლევთ წინასწარ განსაზღვროთ რა სახის ბრუნვა ("იგი")იქნება მაღაზიაში გასაყიდი ფართის ამა თუ იმ ღირებულებით ("x"-ის ერთი ან სხვა მნიშვნელობა). დიახ, შედეგად მიღებული პროგნოზი იქნება მხოლოდ პროგნოზი, მაგრამ ხშირ შემთხვევაში ის საკმაოდ ზუსტი აღმოჩნდება.

მე გავაანალიზებ მხოლოდ ერთ პრობლემას "რეალური" რიცხვებით, რადგან მასში არანაირი სირთულე არ არის - ყველა გამოთვლა სკოლის სასწავლო გეგმის დონეზეა 7-8 კლასებში. შემთხვევების 95 პროცენტში მოგეთხოვებათ მხოლოდ წრფივი ფუნქციის პოვნა, მაგრამ სტატიის ბოლოს მე გაჩვენებთ, რომ ოპტიმალური ჰიპერბოლის, მაჩვენებლის და სხვა ფუნქციების განტოლებების პოვნა აღარ არის რთული.

სინამდვილეში, რჩება დაპირებული სიკეთეების განაწილება - ასე რომ თქვენ ისწავლით როგორ ამოხსნათ ასეთი მაგალითები არა მხოლოდ ზუსტად, არამედ სწრაფად. ჩვენ ყურადღებით ვსწავლობთ სტანდარტს:

დავალება

ორ ინდიკატორს შორის ურთიერთობის შესწავლის შედეგად მიიღეს რიცხვების შემდეგი წყვილი:

უმცირესი კვადრატების მეთოდის გამოყენებით იპოვეთ წრფივი ფუნქცია, რომელიც საუკეთესოდ უახლოვდება ემპირიულს (გამოცდილი)მონაცემები. გააკეთეთ ნახაზი, რომელზედაც დეკარტის მართკუთხა კოორდინატთა სისტემაში გამოსახულია ექსპერიმენტული წერტილები და მიახლოებითი ფუნქციის გრაფიკი . იპოვეთ კვადრატული გადახრების ჯამი ემპირიულ და თეორიულ მნიშვნელობებს შორის. გაარკვიეთ არის თუ არა ფუნქცია უკეთესი (უმცირესი კვადრატების მეთოდის მიხედვით)სავარაუდო ექსპერიმენტული პუნქტები.

გაითვალისწინეთ, რომ "x" მნიშვნელობები ბუნებრივი მნიშვნელობებია და ამას აქვს დამახასიათებელი მნიშვნელობითი მნიშვნელობა, რაზეც ცოტა მოგვიანებით ვისაუბრებ; მაგრამ ისინი, რა თქმა უნდა, შეიძლება იყოს წილადი. გარდა ამისა, კონკრეტული ამოცანის შინაარსიდან გამომდინარე, ორივე "X" და "G" მნიშვნელობები შეიძლება იყოს მთლიანად ან ნაწილობრივ უარყოფითი. ჰოდა, „უსახო“ დავალება მოგვცეს და ვიწყებთ გადაწყვეტილება:

სისტემის ამოხსნის სახით ვპოულობთ ოპტიმალური ფუნქციის კოეფიციენტებს:

უფრო კომპაქტური აღნიშვნის მიზნით, „მრიცხველი“ ცვლადი შეიძლება გამოტოვდეს, რადგან უკვე ნათელია, რომ შეჯამება ხორციელდება 1-დან .

უფრო მოსახერხებელია საჭირო თანხების გამოთვლა ცხრილის სახით:


გამოთვლები შეიძლება განხორციელდეს მიკროკალკულატორზე, მაგრამ ბევრად უკეთესია Excel-ის გამოყენება - როგორც უფრო სწრაფად, ასევე შეცდომების გარეშე; უყურეთ მოკლე ვიდეოს:

ამრიგად, ჩვენ ვიღებთ შემდეგს სისტემა:

აქ შეგიძლიათ მეორე განტოლება გაამრავლოთ 3-ზე და გამოვაკლოთ მე-2 1-ლი განტოლებიდან ტერმინით. მაგრამ ეს არის იღბალი - პრაქტიკაში, სისტემები ხშირად არ არის ნიჭიერი და ასეთ შემთხვევებში ის ზოგავს კრამერის მეთოდი:
ასე რომ, სისტემას აქვს უნიკალური გადაწყვეტა.

მოდით შევამოწმოთ. მესმის, რომ არ მინდა, მაგრამ რატომ გამოტოვო შეცდომები, სადაც მათ აბსოლუტურად არ გამოტოვებ? შეცვალეთ ნაპოვნი ამონახსნები სისტემის თითოეული განტოლების მარცხენა მხარეს:

მიღებულია შესაბამისი განტოლებების სწორი ნაწილები, რაც ნიშნავს, რომ სისტემა სწორად არის ამოხსნილი.

ამრიგად, სასურველი მიახლოებითი ფუნქცია: – საწყისი ყველა წრფივი ფუნქციაექსპერიმენტული მონაცემები საუკეთესოდ არის მიახლოებული მასზე.

განსხვავებით სწორი მაღაზიის ბრუნვის დამოკიდებულება მის ფართობზე, აღმოჩენილი დამოკიდებულება არის საპირისპირო (პრინციპი "რაც მეტი - მით ნაკლები"), და ეს ფაქტი მაშინვე ნეგატივით ვლინდება კუთხოვანი კოეფიციენტი. ფუნქცია გვამცნობს, რომ გარკვეული მაჩვენებლის 1 ერთეულით გაზრდით, დამოკიდებული ინდიკატორის მნიშვნელობა მცირდება საშუალო 0,65 ერთეულით. როგორც ამბობენ, რაც უფრო მაღალია წიწიბურა, მით ნაკლები იყიდება.

მიახლოებითი ფუნქციის გამოსათვლელად, ჩვენ ვპოულობთ მის ორ მნიშვნელობას:

და შეასრულეთ ნახაზი:


აგებულ ხაზს ე.წ ტრენდის ხაზი (კერძოდ, წრფივი ტრენდის ხაზი, ანუ ზოგად შემთხვევაში ტენდენცია სულაც არ არის სწორი ხაზი). გამოთქმა „იყო ტრენდში“ ყველასთვის ცნობილია და ვფიქრობ, რომ ამ ტერმინს დამატებითი კომენტარები არ სჭირდება.

გამოთვალეთ კვადრატული გადახრების ჯამი ემპირიულ და თეორიულ ღირებულებებს შორის. გეომეტრიულად, ეს არის "ჟოლოსფერი" სეგმენტების სიგრძის კვადრატების ჯამი (ორი მათგანი ისეთი პატარაა, რომ ვერც კი ხედავთ).

მოდით შევაჯამოთ გამოთვლები ცხრილში:


მათი ხელახლა შესრულება შესაძლებელია, მხოლოდ იმ შემთხვევაში, თუ მე მოვიყვან მაგალითს პირველი პუნქტისთვის:

მაგრამ ბევრად უფრო ეფექტურია უკვე ცნობილი ხერხის გაკეთება:

გავიმეოროთ: რას ნიშნავს შედეგი?დან ყველა წრფივი ფუნქციაფუნქცია მაჩვენებელი ყველაზე პატარაა, ანუ საუკეთესო მიახლოებაა მის ოჯახში. და აქ, სხვათა შორის, პრობლემის საბოლოო კითხვა არ არის შემთხვევითი: რა მოხდება, თუ შემოთავაზებული ექსპონენციალური ფუნქცია უკეთესი იქნება ექსპერიმენტული ქულების მიახლოება?

ვიპოვოთ კვადრატული გადახრების შესაბამისი ჯამი - მათ გამოსაყოფად აღვნიშნავ ასო „ეპსილონს“. ტექნიკა ზუსტად იგივეა:


და ისევ ყოველი ხანძრის გაანგარიშებისთვის პირველი პუნქტისთვის:

Excel-ში ვიყენებთ სტანდარტულ ფუნქციას ვადა (სინტაქსი შეგიძლიათ იხილოთ Excel Help-ში).

დასკვნა: , ასე რომ ექსპონენციალური ფუნქცია აახლოებს სწორ წრფეზე უარეს ექსპერიმენტულ წერტილებს .

მაგრამ აქვე უნდა აღინიშნოს, რომ "უარესი". ჯერ არ ნიშნავს, რა მოხდა. ახლა მე ავაშენე ამ ექსპონენციალური ფუნქციის გრაფიკი - და ის ასევე გადის წერტილებთან ახლოს - იმდენად, რომ ანალიტიკური კვლევის გარეშე ძნელი სათქმელია, რომელი ფუნქციაა უფრო ზუსტი.

ეს ასრულებს ამოხსნას და მე ვუბრუნდები არგუმენტის ბუნებრივი მნიშვნელობების საკითხს. სხვადასხვა კვლევებში, როგორც წესი, ეკონომიკურ თუ სოციოლოგიურ თვეებს, წლებს ან სხვა თანაბარ დროის ინტერვალებს ითვლიან ბუნებრივი „X“-ით. განვიხილოთ, მაგალითად, ასეთი პრობლემა.

უმცირესი კვადრატების მეთოდი არის მათემატიკური პროცედურა წრფივი განტოლების ასაგებად, რომელიც ყველაზე მეტად ემთხვევა რიცხვთა ორი სერიის სიმრავლეს. ამ მეთოდის მიზანია შემცირდეს მთლიანი კვადრატული ცდომილება. Excel-ს აქვს ინსტრუმენტები, რომელთა გამოყენება შესაძლებელია ამ მეთოდის გამოთვლებში გამოსაყენებლად. ვნახოთ, როგორ კეთდება.

მეთოდის გამოყენება Excel-ში

o Solver დანამატის ჩართვა

o დავალების პირობები

o გადაწყვეტილება

მეთოდის გამოყენება Excel-ში

უმცირესი კვადრატების მეთოდი (LSM) არის ერთი ცვლადის მეორეზე დამოკიდებულების მათემატიკური აღწერა. ის შეიძლება გამოყენებულ იქნას პროგნოზირებისთვის.

ჩართეთ Solver დანამატი

იმისათვის, რომ გამოიყენოთ OLS Excel-ში, თქვენ უნდა ჩართოთ დანამატი "გადაწყვეტის ძიება", რომელიც ნაგულისხმევად გამორთულია.

1. გადადით ჩანართზე "ფაილი".

2. დააწკაპუნეთ განყოფილების სახელზე "Პარამეტრები".

3. ფანჯარაში, რომელიც იხსნება, შეაჩერეთ შერჩევა ქვეგანყოფილებაში "დამატებები".

4. ბლოკში "კონტროლი", რომელიც მდებარეობს ფანჯრის ბოლოში, დააყენეთ გადამრთველი პოზიციაზე "ექსელის დანამატები"(თუ მას სხვა მნიშვნელობა აქვს) და დააწკაპუნეთ ღილაკზე "წადი...".

5. იხსნება პატარა ფანჯარა. პარამეტრის გვერდით მოათავსეთ გამშვები ნიშანი "გადაწყვეტის ძიება". დააჭირეთ ღილაკს კარგი.

ახლა ფუნქცია გამოსავლის პოვნა Excel-ში გააქტიურებულია და მისი ინსტრუმენტები გამოჩნდება ლენტზე.

გაკვეთილი:გამოსავლის პოვნა Excel-ში

პრობლემის პირობები

მოდით აღვწეროთ LSM-ის გამოყენება კონკრეტულ მაგალითზე. ჩვენ გვაქვს ნომრების ორი რიგი xდა წ, რომლის თანმიმდევრობა ნაჩვენებია ქვემოთ მოცემულ სურათზე.

ეს დამოკიდებულება ყველაზე ზუსტად შეიძლება აღწერილი იყოს ფუნქციით:

ამასთან, ცნობილია, რომ x=0 yასევე თანაბარი 0 . ამრიგად, ეს განტოლება შეიძლება აღწერილი იყოს დამოკიდებულებით y=nx.

უნდა ვიპოვოთ სხვაობის კვადრატების მინიმალური ჯამი.

გადაწყვეტილება

მოდით გადავიდეთ მეთოდის პირდაპირი გამოყენების აღწერაზე.

1. პირველი მნიშვნელობის მარცხნივ xდააყენე ნომერი 1 . ეს იქნება კოეფიციენტის პირველი მნიშვნელობის სავარაუდო მნიშვნელობა ნ.

2. სვეტის მარჯვნივ წდაამატეთ კიდევ ერთი სვეტი nx. ამ სვეტის პირველ უჯრედში ვწერთ კოეფიციენტის გამრავლების ფორმულას ნპირველი ცვლადის უჯრედამდე x. ამავდროულად, ჩვენ ვაკეთებთ ველთან კავშირს აბსოლუტური კოეფიციენტით, რადგან ეს მნიშვნელობა არ შეიცვლება. ჩვენ დააჭირეთ ღილაკს შედი.

3. შევსების სახელურის გამოყენებით დააკოპირეთ ეს ფორმულა ცხრილის მთელ დიაპაზონში ქვემოთ მოცემულ სვეტში.

4. ცალკე უჯრაში ვიანგარიშებთ მნიშვნელობების კვადრატების განსხვავებების ჯამს წდა nx. ამისათვის დააჭირეთ ღილაკს "ფუნქციის ჩასმა".

5. გახსნილში "ფუნქციის ოსტატი"ეძებს შესასვლელს "SUMMKVRAZN". შეარჩიეთ იგი და დააჭირეთ ღილაკს კარგი.

6. არგუმენტების ფანჯარა იხსნება. მინდორში "მასივი_x" წ. მინდორში "მასივი_y"შეიყვანეთ სვეტის უჯრედების დიაპაზონი nx. მნიშვნელობების შესაყვანად, უბრალოდ მოათავსეთ კურსორი ველში და აირჩიეთ შესაბამისი დიაპაზონი ფურცელზე. შესვლის შემდეგ დააჭირეთ ღილაკს კარგი.

7. გადადით ჩანართზე "მონაცემები". ლენტაზე ხელსაწყოთა ყუთში "ანალიზი"დააჭირეთ ღილაკს "გადაწყვეტის ძიება".

8. იხსნება ხელსაწყოს პარამეტრების ფანჯარა. მინდორში "ობიექტური ფუნქციის ოპტიმიზაცია"მიუთითეთ უჯრედის მისამართი ფორმულით "SUMMKVRAZN". პარამეტრში "ადრე"დარწმუნდით, რომ დააყენეთ გადამრთველი პოზიციაზე "Მინიმალური". მინდორში "უჯრედების შეცვლა"მიუთითეთ მისამართი კოეფიციენტის მნიშვნელობით ნ. დააჭირეთ ღილაკს "იპოვე გამოსავალი".

9. გამოსავალი გამოჩნდება კოეფიციენტის უჯრედში ნ. სწორედ ეს მნიშვნელობა იქნება ფუნქციის უმცირესი კვადრატი. თუ შედეგი აკმაყოფილებს მომხმარებელს, დააჭირეთ ღილაკს კარგიდამატებით ფანჯარაში.

როგორც ხედავთ, უმცირესი კვადრატების მეთოდის გამოყენება საკმაოდ რთული მათემატიკური პროცედურაა. ჩვენ ეს ვაჩვენეთ მოქმედებაში უმარტივესი მაგალითით, მაგრამ არის ბევრად უფრო რთული შემთხვევები. თუმცა, Microsoft Excel-ის ინსტრუმენტარიუმის შექმნილია გამოთვლების მაქსიმალურად გამარტივებისთვის.

http://multitest.semico.ru/mnk.htm

ზოგადი დებულებები

რაც უფრო მცირეა რიცხვი აბსოლუტურ მნიშვნელობაში, მით უკეთესია სწორი ხაზი (2). სწორი ხაზის (2) შერჩევის სიზუსტის მახასიათებლად შეგვიძლია ავიღოთ კვადრატების ჯამი.

S-ისთვის მინიმალური პირობები იქნება

	(6)
	(7)

განტოლებები (6) და (7) შეიძლება დაიწეროს შემდეგი ფორმით:

	(8)
	(9)

(8) და (9) განტოლებიდან ადვილია a და b-ის პოვნა ექსპერიმენტული მნიშვნელობებიდან x i და y i. (8) და (9) განტოლებებით განსაზღვრულ წრფეს (2) ეწოდება უმცირესი კვადრატების მეთოდით მიღებულ წრფეს (ეს სახელი ხაზს უსვამს, რომ S კვადრატების ჯამს აქვს მინიმალური). განტოლებებს (8) და (9), რომლიდანაც სწორი ხაზი (2) განისაზღვრება, ნორმალურ განტოლებებს უწოდებენ.

შესაძლებელია ნორმალური განტოლებების შედგენის მარტივი და ზოგადი ხერხის მითითება. ექსპერიმენტული წერტილების (1) და განტოლების (2) გამოყენებით, შეგვიძლია ჩამოვწეროთ a და b განტოლებათა სისტემა.

y 1 \u003d ცული 1 +b,
y2=ax2+b, ...		(10)
yn=axn+b,

გაამრავლეთ თითოეული ამ განტოლების მარცხენა და მარჯვენა ნაწილები კოეფიციენტით პირველ უცნობი a (ანუ x 1 , x 2 , ..., x n) და დაამატეთ მიღებული განტოლებები, მიიღება პირველი ნორმალური განტოლება (8).

ჩვენ ვამრავლებთ თითოეული ამ განტოლების მარცხენა და მარჯვენა მხარეს მეორე უცნობი b-ის კოეფიციენტზე, ე.ი. 1-ით და დაამატეთ მიღებული განტოლებები, მიიღება მეორე ნორმალური განტოლება (9).

ნორმალური განტოლებების მიღების ეს მეთოდი ზოგადია: ის შესაფერისია, მაგალითად, ფუნქციისთვის

არის მუდმივი მნიშვნელობა და ის უნდა განისაზღვროს ექსპერიმენტული მონაცემებით (1).

k-ის განტოლებათა სისტემა შეიძლება დაიწეროს:

იპოვეთ წრფე (2) უმცირესი კვადრატების მეთოდის გამოყენებით.

გადაწყვეტილება.Ჩვენ ვიპოვეთ:

X i =21, y i =46.3, x i 2 =91, x i y i =179.1.

ვწერთ განტოლებებს (8) და (9)91a+21b=179.1,

21a+6b=46.3, აქედან ვპოულობთ
a=0.98 b=4.3.