วิธีการของกำลังสองน้อยที่สุด (LSM) เป็นของสาขาการวิเคราะห์การถดถอย มีประโยชน์หลายอย่าง เนื่องจากช่วยให้สามารถแสดงฟังก์ชันที่กำหนดโดยฟังก์ชันอื่นที่ง่ายกว่าได้โดยประมาณ 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) ตอนนี้การแก้ปัญหาจะลดลงเหลือเพียงการเลือกฟังก์ชันการประมาณ y = f (x) ซึ่งมีกราฟผ่านเข้าใกล้จุด M 1, M 2, .. M n มากที่สุด

แน่นอน คุณสามารถใช้พหุนามระดับสูงได้ แต่ตัวเลือกนี้ไม่เพียงแต่ใช้งานยาก แต่ยังไม่ถูกต้อง เนื่องจากจะไม่สะท้อนถึงแนวโน้มหลักที่ต้องตรวจพบ วิธีแก้ปัญหาที่สมเหตุสมผลที่สุดคือการค้นหาเส้นตรง y = ax + b ซึ่งจะประมาณข้อมูลการทดลองได้ดีที่สุด และให้แม่นยำกว่านั้นคือ ค่าสัมประสิทธิ์ - a และ b

คะแนนความแม่นยำ

การประเมินความถูกต้องมีความสำคัญเป็นพิเศษสำหรับการประมาณค่าใดๆ ระบุโดย e ผม ความแตกต่าง (ส่วนเบี่ยงเบน) ระหว่างค่าการทำงานและค่าทดลองสำหรับจุด x ผม , i.e. e i = y i - f (x i).

เห็นได้ชัดว่า ในการประเมินความแม่นยำของการประมาณ คุณสามารถใช้ผลรวมของการเบี่ยงเบน เช่น เมื่อเลือกเส้นตรงสำหรับการแสดงค่าประมาณของการพึ่งพา X บน Y ควรกำหนดการตั้งค่าให้กับค่าที่มีค่าน้อยที่สุด ของผลรวม e i ทุกจุดที่อยู่ในการพิจารณา อย่างไรก็ตามไม่ใช่ทุกอย่างจะง่ายนักเนื่องจากจะมีการเบี่ยงเบนในทางบวกพร้อมกับค่าเบี่ยงเบนเชิงลบ

คุณสามารถแก้ปัญหาได้โดยใช้โมดูลส่วนเบี่ยงเบนหรือกำลังสอง วิธีหลังเป็นวิธีที่ใช้กันอย่างแพร่หลาย มีการใช้ในหลายพื้นที่ รวมถึงการวิเคราะห์การถดถอย (ใน Excel การใช้งานจะดำเนินการโดยใช้ฟังก์ชันในตัวสองตัว) และได้รับการพิสูจน์ประสิทธิภาพมาอย่างยาวนาน

วิธีกำลังสองน้อยที่สุด

อย่างที่คุณทราบใน Excel มีฟังก์ชันผลรวมอัตโนมัติในตัวที่ให้คุณคำนวณค่าของค่าทั้งหมดที่อยู่ในช่วงที่เลือก ดังนั้น ไม่มีอะไรจะป้องกันเราจากการคำนวณค่าของนิพจน์ (e 1 2 + e 2 2 + e 3 2 + ... e n 2)

ในสัญกรณ์คณิตศาสตร์ดูเหมือนว่า:

ตั้งแต่แรกเริ่มตัดสินใจประมาณโดยใช้เส้นตรง เรามี:

ดังนั้น งานในการหาเส้นตรงที่อธิบายความสัมพันธ์เฉพาะระหว่าง X และ Y ได้ดีที่สุด เท่ากับการคำนวณหาค่าฟังก์ชันขั้นต่ำของตัวแปรสองตัว:

สิ่งนี้ต้องเท่ากับศูนย์อนุพันธ์ย่อยบางส่วนเกี่ยวกับตัวแปรใหม่ a และ b และการแก้ระบบพื้นฐานที่ประกอบด้วยสมการสองสมการที่มี 2 รูปแบบที่ไม่ทราบรูปแบบ:

หลังจากการแปลงอย่างง่าย รวมถึงการหารด้วย 2 และจัดการผลรวม เราได้รับ:

การแก้ปัญหา ตัวอย่างเช่น โดยวิธีของ Cramer เราจะได้จุดคงที่ที่มีค่าสัมประสิทธิ์ a * และ b * นี่คือค่าต่ำสุด กล่าวคือ เพื่อคาดการณ์ว่ามูลค่าการซื้อขายของร้านค้าจะมีเท่าไรสำหรับพื้นที่หนึ่ง เส้นตรง y = a * x + b * เหมาะสม ซึ่งเป็นแบบจำลองการถดถอยสำหรับตัวอย่างที่เป็นปัญหา แน่นอนว่าจะไม่อนุญาตให้คุณค้นหาผลลัพธ์ที่แน่นอน แต่จะช่วยให้คุณเข้าใจว่าการซื้อร้านค้าด้วยเครดิตในพื้นที่ใดพื้นที่หนึ่งจะได้ผลหรือไม่

วิธีการใช้วิธีกำลังสองน้อยที่สุดใน Excel

Excel มีฟังก์ชันสำหรับคำนวณค่าของกำลังสองน้อยที่สุด มีรูปแบบดังต่อไปนี้: TREND (ค่า Y ที่รู้จัก ค่า X ที่รู้จัก ค่า X ใหม่ ค่าคงที่) ลองใช้สูตรการคำนวณ OLS ใน Excel กับตารางของเรา

ในการดำเนินการนี้ ในเซลล์ที่จะแสดงผลลัพธ์ของการคำนวณด้วยวิธีกำลังสองน้อยที่สุดใน Excel ให้ป้อนเครื่องหมาย "=" และเลือกฟังก์ชัน "TREND" ในหน้าต่างที่เปิดขึ้น ให้กรอกข้อมูลในฟิลด์ที่เหมาะสม โดยเน้น:

• ช่วงของค่าที่ทราบสำหรับ Y (ในกรณีนี้คือข้อมูลสำหรับการหมุนเวียน)
• ช่วง x 1 , …x n เช่น ขนาดของพื้นที่ค้าปลีก
• และค่าที่รู้จักและไม่รู้จักของ x ซึ่งคุณต้องค้นหาขนาดของการหมุนเวียน (สำหรับข้อมูลเกี่ยวกับตำแหน่งของพวกเขาในแผ่นงานดูด้านล่าง)

นอกจากนี้ยังมีตัวแปรตรรกะ "Const" ในสูตรอีกด้วย หากคุณป้อน 1 ในฟิลด์ที่ตรงกัน นี่จะหมายความว่าควรทำการคำนวณ โดยสมมติว่า b \u003d 0

หากคุณต้องการทราบค่าพยากรณ์มากกว่าหนึ่งค่า x จากนั้นหลังจากป้อนสูตรแล้ว คุณไม่ควรกด "Enter" แต่คุณต้องพิมพ์ชุดค่าผสม "Shift" + "Control" + "Enter" ("Enter" ) บนแป้นพิมพ์

คุณสมบัติบางอย่าง

การวิเคราะห์การถดถอยสามารถเข้าถึงได้แม้กระทั่งกับหุ่นจำลอง สูตร Excel สำหรับการทำนายค่าของอาร์เรย์ของตัวแปรที่ไม่รู้จัก - "TREND" - สามารถใช้ได้กับผู้ที่ไม่เคยได้ยินวิธีกำลังสองน้อยที่สุด แค่รู้คุณสมบัติบางอย่างของงานก็เพียงพอแล้ว โดยเฉพาะอย่างยิ่ง:

• หากคุณจัดเรียงช่วงของค่าที่ทราบของตัวแปร y ในหนึ่งแถวหรือหนึ่งคอลัมน์ โปรแกรมจะรับรู้แต่ละแถว (คอลัมน์) ที่มีค่า x ที่ทราบเป็นตัวแปรแยกต่างหาก
• หากไม่มีการระบุช่วงที่มีค่า x ในหน้าต่าง TREND ในกรณีของการใช้ฟังก์ชันใน Excel โปรแกรมจะพิจารณาว่าเป็นอาร์เรย์ที่ประกอบด้วยจำนวนเต็ม ซึ่งเป็นจำนวนที่สอดคล้องกับช่วงที่มีค่าที่กำหนด ​ของตัวแปร y
• หากต้องการส่งออกอาร์เรย์ของค่า "ที่คาดการณ์" จะต้องป้อนนิพจน์แนวโน้มเป็นสูตรอาร์เรย์
• หากไม่มีการระบุค่า x ใหม่ ฟังก์ชัน TREND จะถือว่าค่าเหล่านั้นเท่ากับค่าที่รู้จัก หากไม่ได้ระบุไว้ อาร์เรย์ 1 จะถูกนำมาเป็นอาร์กิวเมนต์ 2; 3; 4;… ซึ่งเทียบเท่ากับช่วงที่กำหนดพารามิเตอร์ y แล้ว
• ช่วงที่มีค่า x ใหม่ต้องมีแถวหรือคอลัมน์ที่เหมือนกันหรือมากกว่าเป็นช่วงที่มีค่า y ที่กำหนด กล่าวคือต้องได้สัดส่วนกับตัวแปรอิสระ
• อาร์เรย์ที่มีค่า x ที่รู้จักสามารถมีได้หลายตัวแปร อย่างไรก็ตามหากเรากำลังพูดถึงเพียงช่วงเดียวก็จำเป็นต้องมีช่วงที่มีค่า x และ y ที่กำหนด ในกรณีของตัวแปรหลายตัว จำเป็นต้องให้ช่วงที่มีค่า y ที่กำหนดอยู่ในคอลัมน์เดียวหรือหนึ่งแถว

ฟังก์ชันพยากรณ์

การวิเคราะห์การถดถอยใน Excel ดำเนินการโดยใช้หลายฟังก์ชัน หนึ่งในนั้นเรียกว่า "PREDICTION" คล้ายกับ TREND กล่าวคือ ให้ผลลัพธ์ของการคำนวณโดยใช้วิธีกำลังสองน้อยที่สุด อย่างไรก็ตาม สำหรับ X ตัวเดียวเท่านั้น ซึ่งไม่ทราบค่าของ Y

ตอนนี้คุณรู้สูตร Excel สำหรับหุ่นที่ให้คุณทำนายมูลค่าของมูลค่าในอนาคตของตัวบ่งชี้ตามแนวโน้มเชิงเส้นแล้ว

ที่เจอมากที่สุด ประยุกต์กว้างในสาขาวิทยาศาสตร์ต่างๆ และ กิจกรรมภาคปฏิบัติ. อาจเป็นฟิสิกส์ เคมี ชีววิทยา เศรษฐศาสตร์ สังคมวิทยา จิตวิทยา และอื่นๆ ตามความประสงค์ของโชคชะตาฉันมักจะต้องจัดการกับเศรษฐกิจดังนั้นวันนี้ฉันจะจัดตั๋วไปยังประเทศที่น่าอัศจรรย์ที่เรียกว่า เศรษฐมิติ=) … ไม่อยากได้หรือไง! มันดีมากที่นั่น - คุณแค่ต้องตัดสินใจ! …แต่สิ่งที่คุณอาจต้องการอย่างแน่นอนคือการเรียนรู้วิธีแก้ปัญหา สี่เหลี่ยมน้อยที่สุด. และโดยเฉพาะอย่างยิ่งผู้อ่านที่ขยันจะเรียนรู้ที่จะแก้ปัญหาเหล่านี้ไม่เพียง แต่แม่นยำ แต่ยังเร็วมาก ;-) แต่ก่อนอื่น คำชี้แจงปัญหาทั่วไป+ ตัวอย่างที่เกี่ยวข้อง:

ให้ศึกษาตัวชี้วัดในบางสาขาวิชาที่มีนิพจน์เชิงปริมาณ ในเวลาเดียวกัน มีทุกเหตุผลที่เชื่อได้ว่าตัวบ่งชี้ขึ้นอยู่กับตัวบ่งชี้ สมมติฐานนี้สามารถเป็นได้ทั้งสมมติฐานทางวิทยาศาสตร์และตามสามัญสำนึกเบื้องต้น อย่างไรก็ตาม ให้ทิ้งวิทยาศาสตร์ไว้ และสำรวจพื้นที่ที่น่ารับประทานมากขึ้น นั่นคือ ร้านขายของชำ แสดงโดย:

– พื้นที่ค้าปลีกของร้านขายของชำ ตร.ม.
- มูลค่าการซื้อขายประจำปีของร้านขายของชำ ล้านรูเบิล

มันค่อนข้างชัดเจนว่าคืออะไร พื้นที่มากขึ้นร้านค้ายิ่งหมุนเวียนมากขึ้นในกรณีส่วนใหญ่

สมมติว่าหลังจากการสังเกต / การทดลอง / การคำนวณ / การเต้นรำด้วยแทมบูรีน เรามีข้อมูลตัวเลขที่กำจัด:

สำหรับร้านขายของชำ ฉันคิดว่าทุกอย่างชัดเจน: - นี่คือพื้นที่ของร้านที่ 1 - มูลค่าการซื้อขายประจำปี - พื้นที่ของร้านที่ 2 - มูลค่าการซื้อขายประจำปี ฯลฯ โดยวิธีการที่ไม่จำเป็นต้องมีการเข้าถึงวัสดุที่เป็นความลับ - การประเมินมูลค่าการซื้อขายที่ถูกต้องอย่างเป็นธรรมสามารถทำได้โดยใช้ สถิติทางคณิตศาสตร์. อย่างไรก็ตามอย่าฟุ้งซ่านการจารกรรมเชิงพาณิชย์ได้จ่ายเงินไปแล้ว =)

ข้อมูลแบบตารางสามารถเขียนในรูปแบบของจุดและแสดงในลักษณะปกติสำหรับเรา ระบบคาร์ทีเซียน .

มาตอบคำถามสำคัญกัน: ต้องใช้กี่คะแนนในการศึกษาเชิงคุณภาพ?

ใหญ่กว่าดีกว่า. ชุดที่อนุญาตขั้นต่ำประกอบด้วย 5-6 คะแนน นอกจากนี้ ด้วยข้อมูลจำนวนเล็กน้อย ผลลัพธ์ที่ "ผิดปกติ" ไม่ควรรวมอยู่ในตัวอย่าง ตัวอย่างเช่น ร้านค้าเล็กๆ ระดับหัวกะทิเล็กๆ สามารถช่วยออกคำสั่งสำคัญๆ ได้มากกว่า "เพื่อนร่วมงาน" ซึ่งจะทำให้รูปแบบทั่วไปที่จำเป็นต้องพบบิดเบือนไป!

ถ้ามันค่อนข้างง่าย เราต้องเลือกฟังก์ชั่น , กำหนดการซึ่งผ่านเข้าใกล้จุดมากที่สุด . ฟังก์ชันดังกล่าวเรียกว่า ประมาณ (การประมาณ - การประมาณ)หรือ ฟังก์ชันทางทฤษฎี . โดยทั่วไป คำว่า "เสแสร้ง" จะปรากฏที่นี่ทันที ซึ่งเป็นพหุนามของดีกรีสูง กราฟที่ผ่านจุดทั้งหมด แต่ตัวเลือกนี้ซับซ้อนและมักไม่ถูกต้อง (เพราะกราฟจะ “คดเคี้ยว” ตลอดเวลาและสะท้อนแนวโน้มหลักได้ไม่ดี).

ดังนั้นฟังก์ชันที่ต้องการจะต้องเรียบง่ายเพียงพอและในขณะเดียวกันก็สะท้อนการพึ่งพาอาศัยกันอย่างเพียงพอ อย่างที่คุณอาจเดาได้ วิธีหนึ่งในการค้นหาฟังก์ชันดังกล่าวเรียกว่า สี่เหลี่ยมน้อยที่สุด. อันดับแรก มาวิเคราะห์สาระสำคัญโดยทั่วไปกันก่อน ให้ฟังก์ชันบางอย่างประมาณข้อมูลการทดลอง:


จะประเมินความถูกต้องของการประมาณนี้ได้อย่างไร? ให้เราคำนวณความแตกต่าง (ส่วนเบี่ยงเบน) ระหว่างค่าทดลองและค่าการใช้งาน (เราศึกษาการวาดภาพ). ความคิดแรกที่เข้ามาในหัวคือการประมาณว่าผลรวมนั้นมากขนาดไหน แต่ปัญหาคือความแตกต่างอาจเป็นค่าลบได้ (ตัวอย่างเช่น, ) และการเบี่ยงเบนจากผลรวมดังกล่าวจะตัดกันออกไป ดังนั้น ในการประมาณความแม่นยําของการประมาณ มันแนะนําตัวเองให้หาผลรวม โมดูลการเบี่ยงเบน:

หรือในรูปแบบพับ: (ทันใดนั้นใครไม่รู้: เป็นไอคอนผลรวมและเป็นตัวแปรเสริม - "ตัวนับ" ซึ่งใช้ค่าตั้งแต่ 1 ถึง ).

การประมาณจุดทดลองด้วยฟังก์ชันต่างๆ เราจะได้ ความหมายต่างกันและแน่นอนว่า เมื่อผลรวมนี้น้อยกว่า ฟังก์ชันนั้นจะแม่นยำกว่า

วิธีการดังกล่าวมีอยู่และเรียกว่า วิธีโมดูลัสน้อยที่สุด. อย่างไรก็ตามในทางปฏิบัติมันแพร่หลายมากขึ้น วิธีกำลังสองน้อยที่สุดซึ่งค่าลบที่เป็นไปได้นั้นไม่ได้ถูกกำจัดโดยโมดูลัส แต่โดยการยกกำลังสองส่วนเบี่ยงเบน:

หลังจากนั้นความพยายามจะมุ่งไปที่การเลือกฟังก์ชันดังกล่าวซึ่งผลรวมของการเบี่ยงเบนกำลังสอง มีขนาดเล็กที่สุด อันที่จริงแล้วจึงเป็นชื่อของวิธีการ

และตอนนี้เรากลับมาที่อื่นแล้ว จุดสำคัญ: ดังที่กล่าวไว้ข้างต้น ฟังก์ชันที่เลือกควรค่อนข้างง่าย - แต่ก็มีฟังก์ชันดังกล่าวมากมายเช่นกัน: เชิงเส้น , ไฮเปอร์โบลิก, เลขชี้กำลัง, ลอการิทึม, กำลังสอง เป็นต้น และแน่นอนว่าฉันต้องการ "ลดขอบเขตของกิจกรรม" ทันที ประเภทของฟังก์ชั่นให้เลือกสำหรับการวิจัย? เทคนิคดั้งเดิมแต่ได้ผล:

- วิธีที่ง่ายที่สุดในการวาดคะแนน บนภาพวาดและวิเคราะห์ตำแหน่งของพวกเขา หากมีแนวโน้มเป็นเส้นตรง ก็ควรมองหา สมการเส้นตรง ด้วยค่าที่เหมาะสมและ . กล่าวอีกนัยหนึ่ง ภารกิจคือการหาสัมประสิทธิ์ดังกล่าว - เพื่อให้ผลรวมของการเบี่ยงเบนกำลังสองมีค่าน้อยที่สุด

หากระบุจุดต่างๆ เช่น ตาม อติพจน์ดังนั้นจึงเป็นที่ชัดเจนว่าฟังก์ชันเชิงเส้นจะให้ค่าประมาณที่ไม่ดี ในกรณีนี้ เรากำลังมองหาสัมประสิทธิ์ที่ "เหมาะสม" ที่สุดสำหรับสมการไฮเปอร์โบลา - ผู้ที่ให้ผลรวมของกำลังสองขั้นต่ำ .

ตอนนี้สังเกตว่าในทั้งสองกรณีเรากำลังพูดถึง หน้าที่ของสองตัวแปร, ซึ่งมีข้อโต้แย้งคือ ค้นหาตัวเลือกการพึ่งพา:

และโดยพื้นฐานแล้ว เราต้องแก้ปัญหามาตรฐาน - เพื่อค้นหา ฟังก์ชันขั้นต่ำของสองตัวแปร.

จำตัวอย่างของเรา: สมมติว่าจุด "ร้านค้า" มีแนวโน้มที่จะเป็นเส้นตรงและมีเหตุผลทุกประการที่จะเชื่อว่ามีอยู่ ความสัมพันธ์เชิงเส้นมูลค่าการซื้อขายจากพื้นที่การค้า ลองหาสัมประสิทธิ์ดังกล่าว "a" และ "be" เพื่อให้ผลรวมของส่วนเบี่ยงเบนกำลังสอง มีขนาดเล็กที่สุด ทุกอย่างเหมือนเดิม - ก่อน อนุพันธ์บางส่วนของคำสั่งที่ 1. ตาม กฎความเป็นเส้นตรงคุณสามารถแยกความแตกต่างได้จากไอคอนผลรวม:

หากคุณต้องการใช้ข้อมูลนี้สำหรับการเขียนเรียงความหรือรายวิชา ฉันจะขอบคุณมากสำหรับลิงก์ในรายการแหล่งที่มา คุณจะไม่พบการคำนวณโดยละเอียดเช่นนี้ทุกที่:

เขียน ระบบมาตรฐาน:

เราลดสมการแต่ละสมการด้วย "สอง" และนอกจากนี้ "แยกส่วน" ผลรวม:

บันทึก : วิเคราะห์อย่างอิสระว่าทำไมจึงนำ "a" และ "be" ออกจากไอคอนผลรวมได้ อย่างไรก็ตาม อย่างเป็นทางการสามารถทำได้ด้วยผลรวม

มาเขียนระบบใหม่ในรูปแบบ "นำไปใช้":

หลังจากนั้นอัลกอริทึมสำหรับการแก้ปัญหาของเราก็เริ่มถูกดึงออกมา:

เรารู้พิกัดของจุดต่างๆ หรือไม่? พวกเรารู้. ผลรวม เราสามารถหา? อย่างง่ายดาย. เราเขียนง่ายที่สุด ระบบสมการเชิงเส้นสองสมการที่มีค่านิรนามสองตัว("a" และ "beh") เราแก้ระบบ เช่น วิธีการของแครมเมอร์ทำให้เกิดจุดคงที่ กำลังตรวจสอบ สภาพที่เพียงพอสำหรับสุดขั้วเราสามารถยืนยันได้ว่า ณ จุดนี้ฟังก์ชั่น ถึงแม่นๆ ขั้นต่ำ. การยืนยันเกี่ยวข้องกับการคำนวณเพิ่มเติม ดังนั้น เราจะทิ้งมันไว้เบื้องหลัง (ถ้าจำเป็นสามารถดูเฟรมที่หายไปได้). เราสรุปผลสุดท้าย:

การทำงาน วิธีที่ดีที่สุด (อย่างน้อยเมื่อเทียบกับฟังก์ชันเชิงเส้นอื่นๆ)ใกล้เคียงกับจุดทดลอง . กล่าวโดยคร่าว ๆ กราฟของมันผ่านเข้าใกล้จุดเหล่านี้มากที่สุด ตามประเพณี เศรษฐมิติฟังก์ชันการประมาณผลลัพธ์เรียกอีกอย่างว่า สมการถดถอยเชิงเส้นคู่ .

ปัญหาที่กำลังพิจารณามีความสำคัญในทางปฏิบัติอย่างมาก ในสถานการณ์ด้วยตัวอย่างของเรา สมการ ให้คุณทำนายว่าจะมีผลประกอบการแบบไหน ("ยิ๊ก")จะอยู่ที่ร้านค้าด้วยมูลค่าของพื้นที่ขายอย่างใดอย่างหนึ่ง (ความหมายอย่างใดอย่างหนึ่งของ "x"). ใช่ การคาดการณ์ผลลัพธ์จะเป็นเพียงการคาดการณ์ แต่ในหลายกรณี กลับกลายเป็นว่าแม่นยำทีเดียว

ฉันจะวิเคราะห์ปัญหาเดียวกับตัวเลข "ของจริง" เนื่องจากไม่มีปัญหา - การคำนวณทั้งหมดอยู่ที่ระดับของหลักสูตรของโรงเรียนในเกรด 7-8 ใน 95 เปอร์เซ็นต์ของกรณี คุณจะถูกขอให้ค้นหาเพียงฟังก์ชันเชิงเส้น แต่ในตอนท้ายของบทความ ฉันจะแสดงให้เห็นว่าการหาสมการของไฮเพอร์โบลาที่เหมาะสมที่สุด เลขชี้กำลัง และฟังก์ชันอื่นๆ นั้นไม่ใช่เรื่องยากอีกต่อไป

ในความเป็นจริง มันยังคงแจกจ่ายสารพัดที่สัญญาไว้ - เพื่อให้คุณได้เรียนรู้วิธีแก้ไขตัวอย่างดังกล่าว ไม่เพียงแต่ถูกต้องเท่านั้น แต่ยังรวดเร็วอีกด้วย เราศึกษามาตรฐานอย่างรอบคอบ:

งาน

จากการศึกษาความสัมพันธ์ระหว่างตัวบ่งชี้สองตัว ได้ตัวเลขคู่ต่อไปนี้:

ใช้วิธีกำลังสองน้อยที่สุด หาฟังก์ชันเชิงเส้นที่ใกล้เคียงกับค่าเชิงประจักษ์มากที่สุด (มีประสบการณ์)ข้อมูล. สร้างภาพวาดซึ่งในระบบพิกัดสี่เหลี่ยมคาร์ทีเซียน ให้พล็อตจุดทดลองและกราฟของฟังก์ชันการประมาณ . หาผลรวมของค่าเบี่ยงเบนกำลังสองระหว่างค่าเชิงประจักษ์และค่าทางทฤษฎี ค้นหาว่าฟังก์ชั่นจะดีกว่าไหม (ในแง่ของวิธีกำลังสองน้อยที่สุด)จุดทดลองโดยประมาณ

โปรดทราบว่าค่า "x" เป็นค่าธรรมชาติและสิ่งนี้มีความหมายที่มีความหมายซึ่งฉันจะพูดถึงในภายหลัง แต่แน่นอนว่าสามารถเป็นเศษส่วนได้ นอกจากนี้ ขึ้นอยู่กับเนื้อหาของงานเฉพาะ ค่าทั้ง "X" และ "G" อาจเป็นค่าลบทั้งหมดหรือบางส่วนก็ได้ เราได้รับภารกิจที่ "ไร้หน้า" และเราเริ่มต้นมัน วิธีการแก้:

เราพบค่าสัมประสิทธิ์ของฟังก์ชันที่เหมาะสมที่สุดเพื่อแก้ปัญหาของระบบ:

สำหรับวัตถุประสงค์ของสัญกรณ์ที่กระชับมากขึ้น คุณสามารถละเว้นตัวแปร "ตัวนับ" ได้ เนื่องจากเป็นที่ชัดเจนว่าการบวกดำเนินการตั้งแต่ 1 ถึง .

สะดวกกว่าในการคำนวณจำนวนเงินที่ต้องการในรูปแบบตาราง:


การคำนวณสามารถทำได้บนไมโครเครื่องคิดเลข แต่จะดีกว่ามากถ้าใช้ Excel - ทั้งเร็วกว่าและไม่มีข้อผิดพลาด ดูวิดีโอสั้น ๆ :

ดังนั้นเราจึงได้ดังนี้ ระบบ:

ที่นี่คุณสามารถคูณสมการที่สองด้วย 3 และ ลบที่ 2 จากเทอมสมการที่ 1 ด้วยเทอม. แต่นี่คือโชค - ในทางปฏิบัติ ระบบมักไม่มีของกำนัล และในกรณีเช่นนี้ จะช่วยประหยัด วิธีการของแครมเมอร์:
ดังนั้นระบบจึงมีโซลูชันที่ไม่เหมือนใคร

มาทำเช็คกัน ฉันเข้าใจว่าฉันไม่ต้องการ แต่ทำไมข้ามข้อผิดพลาดที่คุณไม่พลาดอย่างแน่นอน แทนที่คำตอบที่พบในด้านซ้ายของแต่ละสมการของระบบ:

ได้ส่วนที่ถูกต้องของสมการที่สอดคล้องกัน ซึ่งหมายความว่าระบบได้รับการแก้ไขอย่างถูกต้อง

ดังนั้น ฟังก์ชันการประมาณที่ต้องการ: – from ฟังก์ชันเชิงเส้นทั้งหมดข้อมูลการทดลองจะประมาณค่าได้ดีที่สุด

ไม่เหมือน ตรง การพึ่งพาการหมุนเวียนของร้านค้าในพื้นที่ การพึ่งพาที่พบคือ ย้อนกลับ (หลักการ ยิ่งมาก ยิ่งน้อย”)และความจริงข้อนี้จะถูกเปิดเผยโดยทันทีโดยแง่ลบ สัมประสิทธิ์เชิงมุม. การทำงาน แจ้งให้เราทราบว่าเมื่อตัวบ่งชี้เพิ่มขึ้น 1 หน่วย ค่าของตัวบ่งชี้ที่ขึ้นต่อกันจะลดลง เฉลี่ย 0.65 หน่วย อย่างที่พวกเขาพูดกันว่ายิ่งราคาบัควีทสูงเท่าไหร่ก็ยิ่งขายได้น้อยลงเท่านั้น

ในการพล็อตฟังก์ชันการประมาณ เราพบค่าสองค่า:

และดำเนินการวาด:


เส้นที่สร้างขึ้นเรียกว่า เส้นแนวโน้ม (กล่าวคือ เส้นแนวโน้มเชิงเส้น กล่าวคือ ในกรณีทั่วไป แนวโน้มไม่จำเป็นต้องเป็นเส้นตรง). ทุกคนคุ้นเคยกับสำนวนที่ว่า "to be in trend" และฉันคิดว่าคำนี้ไม่ต้องการความคิดเห็นเพิ่มเติม

คำนวณผลรวมของส่วนเบี่ยงเบนกำลังสอง ระหว่างค่าเชิงประจักษ์และเชิงทฤษฎี ในเชิงเรขาคณิต นี่คือผลรวมของกำลังสองของความยาวของส่วน "สีแดงเข้ม" (สองอันเล็กจนมองไม่เห็น).

มาสรุปการคำนวณในตารางกัน:


สามารถดำเนินการได้ด้วยตนเองอีกครั้ง ในกรณีที่ฉันจะยกตัวอย่างสำหรับจุดที่ 1:

แต่มันมีประสิทธิภาพมากกว่าที่จะทำวิธีที่รู้อยู่แล้ว:

มาทำซ้ำ: ความหมายของผลลัพธ์คืออะไร?จาก ฟังก์ชันเชิงเส้นทั้งหมดการทำงาน เลขชี้กำลังนั้นเล็กที่สุดนั่นคือในตระกูลมันเป็นค่าประมาณที่ดีที่สุด และอย่างไรก็ตาม คำถามสุดท้ายของปัญหาไม่ได้เกิดขึ้นโดยบังเอิญ ถ้าฟังก์ชันเลขชี้กำลังที่เสนอมาจะเป็นอย่างไร จะดีกว่าไหมถ้าจะประมาณจุดทดลอง?

มาหาผลรวมของการเบี่ยงเบนกำลังสองที่ตรงกัน - เพื่อแยกความแตกต่าง ฉันจะกำหนดพวกมันด้วยตัวอักษร "epsilon" เทคนิคเหมือนกันทุกประการ:


และอีกครั้งสำหรับการคำนวณไฟทุกครั้งสำหรับจุดที่ 1:

ใน Excel เราใช้ฟังก์ชันมาตรฐาน EXP (ไวยากรณ์สามารถพบได้ในวิธีใช้ Excel).

บทสรุป: ดังนั้น ฟังก์ชันเลขชี้กำลังจะประมาณจุดทดลองที่แย่กว่าเส้นตรง .

แต่ควรสังเกตตรงนี้ว่า "แย่กว่า" คือ ไม่ได้หมายความว่ายัง, เกิดอะไรขึ้น. ตอนนี้ฉันสร้างกราฟของฟังก์ชันเลขชี้กำลัง - และมันก็ส่งผ่านใกล้กับจุด - มากเสียจนไม่มีการศึกษาเชิงวิเคราะห์ เป็นการยากที่จะบอกว่าฟังก์ชันใดแม่นยำกว่า

วิธีนี้ทำให้การแก้ปัญหาเสร็จสมบูรณ์และฉันกลับไปที่คำถามเกี่ยวกับค่าธรรมชาติของการโต้แย้ง ในการศึกษาต่างๆ ตามกฎแล้ว เศรษฐกิจหรือสังคมวิทยา เดือน ปี หรือช่วงเวลาที่เท่าเทียมกันอื่นๆ จะถูกนับด้วย "X" ตามธรรมชาติ ยกตัวอย่างปัญหาดังกล่าว

4.1. การใช้ฟังก์ชันในตัว

การคำนวณ สัมประสิทธิ์การถดถอยดำเนินการโดยใช้ฟังก์ชัน

LINEST(Values_y; ค่า_x; Konst; สถิติ),

Values_y- อาร์เรย์ของค่า y

ค่า_x- อาร์เรย์เสริมของค่า xถ้าอาร์เรย์ Xละเว้น ถือว่านี่คืออาร์เรย์ (1;2;3;...) ที่มีขนาดเท่ากับ Values_y,

Konst- ค่าบูลีนที่ระบุว่าต้องการค่าคงที่หรือไม่ ขเท่ากับ 0 ถ้า Konstมีความหมาย จริงหรือละเว้นแล้ว ขคำนวณด้วยวิธีปกติ ถ้าข้อโต้แย้ง Konstเป็นเท็จ ดังนั้น ขจะถือว่าเป็น 0 และค่า เอถูกเลือกเพื่อให้ความสัมพันธ์ y=ขวาน

สถิติ- ค่าบูลีนที่ระบุว่าต้องส่งคืนสถิติการถดถอยเพิ่มเติมหรือไม่ ถ้าข้อโต้แย้ง สถิติมีความหมาย จริงจากนั้นฟังก์ชัน LINESTส่งกลับสถิติการถดถอยเพิ่มเติม ถ้าข้อโต้แย้ง สถิติมีความหมาย เท็จหรือละเว้นแล้วฟังก์ชัน LINESTส่งกลับเฉพาะค่าสัมประสิทธิ์ เอและถาวร ข.

ต้องจำไว้ว่าผลของการทำงาน LINEST()เป็นชุดของค่า - อาร์เรย์

สำหรับการคำนวณ ค่าสัมประสิทธิ์สหสัมพันธ์ใช้ฟังก์ชัน

CORREL(Array1;Array2),

ส่งคืนค่าสัมประสิทธิ์สหสัมพันธ์โดยที่ Array1- อาร์เรย์ของค่า y, Array2- อาร์เรย์ของค่า x. Array1และ Array2จะต้องมีขนาดเท่ากัน

ตัวอย่าง 1. ติดยาเสพติด y(x) ถูกนำเสนอในตาราง สร้าง เส้นถดถอยและคำนวณ ค่าสัมประสิทธิ์สหสัมพันธ์.

y		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คุณต้องเข้าสู่โหมดแก้ไข (key F2)แล้วกดคีย์ผสม CTRL+SHIFT+ENTER.

เพื่อคำนวณค่าสัมประสิทธิ์สหสัมพันธ์ต่อเซลล์ C6มีการแนะนำสูตรต่อไปนี้:

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

รู้ค่าสัมประสิทธิ์การถดถอย เอและ ขคำนวณค่าของฟังก์ชัน y=ขวาน+ขสำหรับให้ x. ในการทำเช่นนี้เราขอแนะนำสูตร

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

และคัดลอกไปยังช่วง С5:J5(รูปที่ 5).

ลองพลอตเส้นการถดถอยบนไดอะแกรม เลือกจุดทดลองบนแผนภูมิ คลิกขวาและเลือกคำสั่ง ข้อมูลเบื้องต้น. ในกล่องโต้ตอบที่ปรากฏขึ้น (รูปที่ 5) ให้เลือกแท็บ แถวและคลิกที่ปุ่ม เพิ่ม. กรอกข้อมูลลงในช่องป้อนข้อมูล ดังแสดงในรูปที่ 6 แล้วกดปุ่ม ตกลง. เส้นการถดถอยจะถูกเพิ่มลงในแผนภาพข้อมูลทดลอง โดยค่าเริ่มต้น กราฟจะแสดงเป็นจุดที่ไม่ได้เชื่อมต่อกันด้วยเส้นที่ปรับให้เรียบ

ข้าว. 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

เลือกและสร้างการพึ่งพาอาศัยกันโดยประมาณ สร้างกราฟการพึ่งพาเชิงวิเคราะห์แบบตารางและแบบติดตั้ง

การแก้ปัญหาสามารถแบ่งออกเป็นขั้นตอนต่อไปนี้: การป้อนข้อมูลเริ่มต้น การสร้างพล็อตกระจาย และการเพิ่มเส้นแนวโน้มลงในพล็อตนี้

ลองพิจารณากระบวนการนี้โดยละเอียด ป้อนข้อมูลเริ่มต้นลงในแผ่นงานและลงจุดข้อมูลการทดลอง จากนั้นเลือกจุดทดสอบบนแผนภูมิ คลิกขวาแล้วใช้คำสั่ง เพิ่ม l เส้นแนวโน้ม(รูปที่ 10).

กล่องโต้ตอบที่ปรากฏขึ้นช่วยให้คุณสร้างการพึ่งพาอาศัยกันโดยประมาณ

แท็บแรก (รูปที่ 11) ของหน้าต่างนี้ระบุประเภทของการพึ่งพาอาศัยกันโดยประมาณ

อันที่สอง (รูปที่ 12) กำหนดพารามิเตอร์การก่อสร้าง:

ชื่อของการพึ่งพาอาศัยกันโดยประมาณ

พยากรณ์ไปข้างหน้า (ย้อนกลับ) บน นหน่วย (พารามิเตอร์นี้กำหนดจำนวนหน่วยไปข้างหน้า (ย้อนกลับ) จำเป็นต้องขยายเส้นแนวโน้ม);

ว่าจะแสดงจุดตัดของเส้นโค้งกับเส้นหรือไม่ y=const;

จะแสดงฟังก์ชันการประมาณบนไดอะแกรมหรือไม่ (แสดงสมการบนพารามิเตอร์ไดอะแกรม);

จะวางค่าของค่าเบี่ยงเบนมาตรฐานบนไดอะแกรมหรือไม่ (พารามิเตอร์ใส่ค่าความน่าเชื่อถือโดยประมาณบนไดอะแกรม)

ให้เราเลือกพหุนามของดีกรีที่สองเป็นการพึ่งพาอาศัยกันโดยประมาณ (รูปที่ 11) และรับสมการที่อธิบายพหุนามนี้บนกราฟ (รูปที่ 12) ไดอะแกรมผลลัพธ์จะแสดงในรูปที่ 13.

ในทำนองเดียวกันกับ เส้นแนวโน้มคุณสามารถเลือกพารามิเตอร์ของการพึ่งพาเช่น

เชิงเส้น y=เป็∙x+ข,

ลอการิทึม y=ln(x)+ข,

เลขชี้กำลัง y=a∙eb,

พลัง y=ก x ข,

พหุนาม y=เป็∙x 2 +ข∙x+ค, y=เป็∙x 3 +ข∙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โดยวิธีกำลังสองน้อยที่สุด

ปัญหานี้เทียบเท่ากับปัญหาการหาค่าฟังก์ชันขั้นต่ำของตัวแปร 5 ตัว

พิจารณาขั้นตอนการแก้ปัญหาการปรับให้เหมาะสม (รูปที่ 14)

ให้ค่า แต่, ที่, จาก, ดีและ ถึงเก็บไว้ในเซลล์ A7:E7. คำนวณค่าทางทฤษฎีของฟังก์ชัน Z(t)=At4+Bt3+Ct2+Dt+Kสำหรับให้ t(B2:J2). การทำเช่นนี้ในเซลล์ B4ป้อนค่าของฟังก์ชันที่จุดแรก (cell B2):

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

คัดลอกสูตรนี้ลงในช่วง С4:J4และรับค่าที่คาดหวังของฟังก์ชัน ณ จุด ซึ่ง abscissas จะถูกเก็บไว้ในเซลล์ B2:J2.

สู่เซลล์ B5เราแนะนำสูตรที่คำนวณกำลังสองของความแตกต่างระหว่างจุดทดลองและจุดที่คำนวณได้:

B5=(B4-B3)^2,

และคัดลอกไปยังช่วง С5:J5. ในเซลล์ F7เราจะเก็บค่าความคลาดเคลื่อนกำลังสองทั้งหมด (10) ในการทำเช่นนี้ เราขอแนะนำสูตร:

F7 = SUM(B5:J5).

มาใช้คำสั่ง บริการ®ค้นหาโซลูชันและแก้ปัญหาการเพิ่มประสิทธิภาพโดยไม่มีข้อจำกัด กรอกข้อมูลลงในช่องป้อนข้อมูลที่เหมาะสมในกล่องโต้ตอบที่แสดงในรูปที่ 14 แล้วกดปุ่ม วิ่ง. หากพบวิธีแก้ปัญหา หน้าต่างที่แสดงในรูปที่ สิบห้า

ผลลัพธ์ของบล็อกการตัดสินใจจะเป็นผลลัพธ์ไปยังเซลล์ A7:E7ค่าพารามิเตอร์ฟังก์ชั่น Z(t)=At4+Bt3+Ct2+Dt+K. ในเซลล์ B4:J4เราได้รับ ค่าฟังก์ชันที่คาดไว้ที่จุดเริ่มต้น ในเซลล์ F7จะเก็บไว้ ข้อผิดพลาดกำลังสองทั้งหมด.

คุณสามารถแสดงจุดทดลองและเส้นที่พอดีในพื้นที่กราฟิกเดียวกันได้หากคุณเลือกช่วง B2:J4, เรียก ตัวช่วยสร้างแผนภูมิแล้วจัดรูปแบบลักษณะที่ปรากฏของกราฟผลลัพธ์

ข้าว. 17 แสดงเวิร์กชีต MS Excel หลังจากทำการคำนวณแล้ว

5. ข้อมูลอ้างอิง

1. Alekseev E.R. , Chesnokova O.V. , การแก้ปัญหาคณิตศาสตร์เชิงคำนวณในแพ็คเกจ Mathcad12, MATLAB7, Maple9 – NT Press, 2006.–596s. :ป่วย. – (กวดวิชา)

2. Alekseev E.R. , Chesnokova O.V. , E.A. Rudchenko, Scilab, การแก้ปัญหาทางวิศวกรรมและคณิตศาสตร์ –M., BINOM, 2008.–260 วินาที.

3. I. S. Berezin และ N. P. Zhidkov, วิธีการคำนวณ, มอสโก: Nauka, 1966

4. Garnaev A.Yu. การใช้ MS EXCEL และ VBA ในด้านเศรษฐศาสตร์และการเงิน - เซนต์ปีเตอร์สเบิร์ก: BHV - ปีเตอร์สเบิร์ก, 1999.-332p

5. B. P. Demidovich, I. A. Maron และ V. Z. Shuvalova วิธีการวิเคราะห์เชิงตัวเลข–M .: Nauka, 1967.–368p

6. Korn G. , Korn T. , คู่มือคณิตศาสตร์สำหรับนักวิทยาศาสตร์และวิศวกร.–ม., 1970, 720p.

7. Alekseev E.R. , Chesnokova O.V. แนวปฏิบัติเพื่อทำงานในห้องปฏิบัติการใน MS EXCEL สำหรับนักศึกษาพิเศษทุกสาขา Donetsk, DonNTU, 2004. 112 หน้า

วิธีการของกำลังสองน้อยที่สุด (LSM) ขึ้นอยู่กับการย่อผลรวมของการเบี่ยงเบนกำลังสองของฟังก์ชันที่เลือกจากข้อมูลที่อยู่ภายใต้การศึกษา ในบทความนี้ เราจะประมาณข้อมูลที่มีอยู่โดยใช้ฟังก์ชันเชิงเส้นy = เอ x + ข .

วิธีกำลังสองน้อยที่สุด(ภาษาอังกฤษ) สามัญ น้อยที่สุด สี่เหลี่ยม , OLS) เป็นหนึ่งในวิธีพื้นฐานของการวิเคราะห์การถดถอยในแง่ของการประมาณค่าพารามิเตอร์ที่ไม่รู้จัก ตัวแบบถดถอยตามข้อมูลตัวอย่าง

พิจารณาการประมาณตามฟังก์ชันโดยขึ้นอยู่กับตัวแปรเดียวเท่านั้น:

• เชิงเส้น: y=ax+b (บทความนี้)
• : y=a*Ln(x)+b
• : y=a*xm
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

บันทึก: กรณีการประมาณโดยพหุนามจากดีกรีที่ 3 ถึงระดับ 6 ได้รับการพิจารณาในบทความนี้ การประมาณโดยพหุนามตรีโกณมิติพิจารณาที่นี่

การพึ่งพาเชิงเส้น

เราสนใจความสัมพันธ์ของตัวแปร 2 ตัว Xและ y. มีข้อสันนิษฐานว่า yขึ้นอยู่กับ Xตามกฎเชิงเส้น y = ขวาน + ข. เพื่อหาค่าพารามิเตอร์ของความสัมพันธ์นี้ ผู้วิจัยได้ทำการสังเกต: สำหรับแต่ละค่าของ x i จะมีการวัดค่า y i (ดูไฟล์ตัวอย่าง) ดังนั้นให้มีค่า 20 คู่ (х i ; y i).

บันทึก:หากเปลี่ยนทีละขั้นโดย X คงที่แล้วจึงสร้าง scatterplotsใช้ได้ ถ้าไม่ก็ต้องใช้แผนภูมิประเภท จุด .

จากแผนภาพจะเห็นได้ชัดเจนว่าความสัมพันธ์ระหว่างตัวแปรนั้นใกล้เคียงกับเส้นตรง เพื่อทำความเข้าใจว่าเส้นตรงใดที่อธิบายความสัมพันธ์ระหว่างตัวแปรได้ "ถูกต้อง" มากที่สุด จำเป็นต้องกำหนดเกณฑ์ที่จะใช้เปรียบเทียบเส้น

เกณฑ์ดังกล่าว เราใช้นิพจน์:

ที่ไหน ŷ ผม = เอ * x ฉัน + ข ; 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)

นอกจากนี้เพื่อคำนวณพารามิเตอร์ เอ คุณสามารถใช้สูตร = ความชัน(C26:C45;B26:B45). สำหรับพารามิเตอร์ ข ใช้สูตร = อินเตอร์คัท(C26:C45;B26:B45) .

และสุดท้าย ฟังก์ชัน LINEST() ช่วยให้คุณคำนวณพารามิเตอร์ทั้งสองพร้อมกันได้ การใส่สูตร LINEST(C26:C45;B26:B45)เลือก 2 เซลล์ในแถวแล้วกด CTRL + กะ + เข้าสู่(ดูบทความเกี่ยวกับ). เซลล์ด้านซ้ายจะส่งคืนค่า เอ , ทางขวา ข .

บันทึก: เพื่อไม่ให้ยุ่งกับการป้อนข้อมูล สูตรอาร์เรย์คุณจะต้องใช้ฟังก์ชัน INDEX() เพิ่มเติม สูตร = ดัชนี(LINEST(C26:C45,B26:B45),1)หรือเพียงแค่ = LINEST(C26:C45;B26:B45)จะคืนค่าพารามิเตอร์ที่รับผิดชอบต่อความชันของเส้นตรงเช่น เอ . สูตร = ดัชนี(LINEST(C26:C45,B26:B45),2)จะส่งคืนพารามิเตอร์ที่รับผิดชอบจุดตัดของเส้นที่มีแกน Y นั่นคือ ข .

หลังจากคำนวณพารามิเตอร์แล้ว scatterplotสามารถลากเส้นได้

อีกวิธีหนึ่งในการวาดเส้นตรงโดยใช้วิธีกำลังสองน้อยที่สุดคือเครื่องมือแผนภูมิ เส้นแนวโน้ม. เมื่อต้องการทำเช่นนี้ เลือกไดอะแกรม เลือกจากเมนู แท็บเค้าโครง, ใน วิเคราะห์กลุ่มคลิก เส้นแนวโน้ม, แล้ว การประมาณเชิงเส้น .

โดยการทำเครื่องหมายที่ช่อง "แสดงสมการในไดอะแกรม" ในกล่องโต้ตอบ คุณสามารถตรวจสอบให้แน่ใจว่าพารามิเตอร์ที่พบด้านบนตรงกับค่าในไดอะแกรม

บันทึก: เพื่อให้พารามิเตอร์ตรงกัน ประเภทแผนภูมิจะต้องเป็น ความจริงก็คือเมื่อสร้างไดอะแกรม กำหนดการผู้ใช้ไม่สามารถตั้งค่าแกน x ได้ (ผู้ใช้ระบุได้เฉพาะป้ายกำกับที่ไม่ส่งผลต่อตำแหน่งของจุด) แทนที่จะใช้ค่า X จะใช้ลำดับที่ 1 2; 3; … (สำหรับการกำหนดหมายเลขหมวดหมู่) ดังนั้นหากสร้าง เส้นแนวโน้มบนไดอะแกรมประเภท กำหนดการจากนั้นค่าของลำดับนี้จะถูกใช้แทนค่าจริงของ X ซึ่งจะนำไปสู่ผลลัพธ์ที่ไม่ถูกต้อง (เว้นแต่แน่นอนว่าค่าจริงของ X จะไม่ตรงกับลำดับที่ 1; 2 ; 3; ...).

วิธีกำลังสองน้อยที่สุด (LSM)

ระบบ m สมการเชิงเส้นกับ n ที่ไม่รู้จักมีรูปแบบ:

เป็นไปได้สามกรณี: m น. กรณีที่พิจารณา m=n ในย่อหน้าที่แล้ว รูปร่าง

ถ้า m>n และระบบสอดคล้องกัน เมทริกซ์ A จะมีแถวที่ขึ้นกับเชิงเส้นอย่างน้อย m - n ที่นี่ สามารถหาวิธีแก้ปัญหาได้โดยเลือกสมการอิสระเชิงเส้น n สมการ (ถ้ามี) และใช้สูตร X=A -1 CV นั่นคือ ลดปัญหาให้เหลือสมการที่แก้ไขก่อนหน้านี้ ในกรณีนี้ ผลลัพธ์ที่ได้จะเป็นไปตามสมการ m - n ที่เหลือเสมอ

อย่างไรก็ตามเมื่อใช้คอมพิวเตอร์จะสะดวกกว่าถ้าใช้วิธีทั่วไปมากขึ้น - วิธีกำลังสองน้อยที่สุด

พีชคณิตกำลังสองน้อยที่สุด

วิธีพีชคณิตของกำลังสองน้อยที่สุดเป็นที่เข้าใจกันว่าเป็นวิธีแก้ระบบสมการเชิงเส้น

โดยการลดบรรทัดฐานแบบยุคลิดให้น้อยที่สุด

ขวาน? ข? > อินฟ (1.2)

การวิเคราะห์ข้อมูลทดลอง

ให้เราพิจารณาการทดลองบางอย่าง ในช่วงเวลานั้น

ตัวอย่างเช่น อุณหภูมิ Q(t) ถูกวัด ให้ผลการวัดถูกกำหนดโดยอาร์เรย์

ให้เราสมมติว่าเงื่อนไขของการทดลองนั้นทำให้การวัดมีข้อผิดพลาดที่ทราบ ในกรณีเหล่านี้ กฎของการเปลี่ยนแปลงอุณหภูมิ Q(t) ถูกหาโดยใช้พหุนามบางตัว

P(t) = + + + ... +,

การหาค่าสัมประสิทธิ์ที่ไม่รู้จัก ... จากการพิจารณาว่าค่า E(, ...,) ที่กำหนดโดยความเท่าเทียมกัน

เกาส์พีชคณิต exel ประมาณ

เอาค่าต่ำสุด เนื่องจากผลรวมของกำลังสองถูกย่อให้เล็กสุด วิธีนี้จึงเรียกว่ากำลังสองน้อยที่สุดที่พอดีกับข้อมูล

ถ้าเราแทนที่ P(t) ด้วยนิพจน์ เราจะได้

มาตั้งค่างานในการกำหนดอาร์เรย์ให้มีค่าน้อยที่สุด กล่าวคือ กำหนดอาร์เรย์โดยใช้วิธีกำลังสองน้อยที่สุด ในการทำเช่นนี้ เราให้อนุพันธ์ย่อยบางส่วนเท่ากับศูนย์:

หากคุณป้อน m × n เมทริกซ์ A = (), i = 1, 2..., m; j = 1, 2, ..., n โดยที่

ผม = 1, 2..., ม.; เจ = 1, 2, ..., น,

แล้วความเท่าเทียมที่เป็นลายลักษณ์อักษรก็จะอยู่ในรูป

ลองเขียนความเท่าเทียมกันใหม่ในแง่ของการดำเนินการกับเมทริกซ์ ตามคำจำกัดความ เรามีการคูณเมทริกซ์ด้วยคอลัมน์

สำหรับเมทริกซ์ทรานสโพส ความสัมพันธ์ที่คล้ายคลึงกันจะเป็นดังนี้

เราแนะนำสัญกรณ์: เราจะแสดงถึงองค์ประกอบที่ i ของเวกเตอร์ Axe ตามความเท่าเทียมกันของเมทริกซ์ที่เขียนไว้เราจะมี

ในรูปแบบเมทริกซ์ ความเท่าเทียมกันนี้สามารถเขียนใหม่เป็น

เอ ที x=เอ ที บี (1.3)

โดยที่ A คือเมทริกซ์สี่เหลี่ยมจัตุรัส m×n นอกจากนี้ ในปัญหาของการประมาณข้อมูล ตามกฎ m > n สมการ (1.3) เรียกว่าสมการปกติ

เป็นไปได้ตั้งแต่ต้นโดยใช้บรรทัดฐานของเวกเตอร์แบบยุคลิดเพื่อเขียนปัญหาในรูปแบบเมทริกซ์ที่เทียบเท่ากัน:

เป้าหมายของเราคือลดฟังก์ชันนี้ให้เหลือน้อยที่สุดใน x อนุพันธ์อันดับ 1 เทียบกับ x ณ จุดนี้ต้องเท่ากับศูนย์ อนุพันธ์ของฟังก์ชันนี้คือ

2A T B + 2A T ขวาน

ดังนั้นคำตอบจึงต้องเป็นไปตามระบบสมการเชิงเส้น

(A T A) x = (A T B)

สมการเหล่านี้เรียกว่าสมการปกติ ถ้า A เป็นเมทริกซ์ขนาด m × n แล้ว A>A - n × n เป็นเมทริกซ์ นั่นคือ เมทริกซ์สมการปกติจะเป็นเมทริกซ์สมมาตรสี่เหลี่ยมเสมอ ยิ่งไปกว่านั้น มันมีคุณสมบัติของการกำหนดเชิงบวกในแง่ที่ว่า (A>Axe, x) = (Axe, Axe) ? 0.

ความคิดเห็น บางครั้งคำตอบของสมการของรูปแบบ (1.3) เรียกว่าคำตอบของระบบ Axe = B โดยที่ A คือเมทริกซ์สี่เหลี่ยมจัตุรัส m × n (m > n) โดยวิธีกำลังสองน้อยที่สุด

ปัญหากำลังสองน้อยที่สุดสามารถตีความแบบกราฟิกได้ว่าเป็นการลดระยะทางแนวตั้งจากจุดข้อมูลไปยังเส้นโค้งแบบจำลอง (ดูรูปที่ 1.1) แนวคิดนี้ตั้งอยู่บนสมมติฐานที่ว่าข้อผิดพลาดในการประมาณค่าทั้งหมดสอดคล้องกับข้อผิดพลาดจากการสังเกต หากมีข้อผิดพลาดในตัวแปรอธิบายด้วย ก็อาจเหมาะสมกว่าที่จะลดระยะห่างแบบยุคลิดจากข้อมูลไปยังแบบจำลอง

OLS ใน Excel

อัลกอริทึมสำหรับการนำ OLS ไปใช้ใน Excel ด้านล่างถือว่าข้อมูลเริ่มต้นทั้งหมดเป็นที่รู้จักแล้ว เราคูณทั้งสองส่วนของสมการเมทริกซ์ AЧX=B ของระบบจากด้านซ้ายด้วยเมทริกซ์ทรานสโพสของระบบ А Т:

A T AX \u003d A T B

จากนั้นเราคูณสมการทั้งสองส่วนทางด้านซ้ายด้วยเมทริกซ์ (A T A) -1 ถ้าเมทริกซ์นี้มีอยู่ ระบบก็จะถูกกำหนด โดยคำนึงถึงความจริงที่ว่า

(A T A) -1 * (A T A) \u003d E เราได้

X \u003d (A T A) -1 A T B.

สมการเมทริกซ์ที่เป็นผลลัพธ์คือคำตอบของระบบสมการเชิงเส้น m โดยมีค่าไม่ทราบค่า n ค่าสำหรับ m>n

พิจารณาการประยุกต์ใช้อัลกอริธึมข้างต้นกับตัวอย่างเฉพาะ

ตัวอย่าง. ให้จำเป็นต้องแก้ระบบ

ใน Excel แผ่นงานโซลูชันในโหมดแสดงสูตรสำหรับปัญหานี้จะมีลักษณะดังนี้:

ผลการคำนวณ:

เวกเตอร์ X ที่ต้องการจะอยู่ในช่วง E11:E12

เมื่อแก้ระบบสมการเชิงเส้นที่กำหนด จะใช้ฟังก์ชันต่อไปนี้:

1. MINUTE - ส่งกลับค่าผกผันของเมทริกซ์ที่เก็บไว้ในอาร์เรย์

ไวยากรณ์: NBR(อาร์เรย์)

อาร์เรย์คืออาร์เรย์ตัวเลขที่มีจำนวนแถวและคอลัมน์เท่ากัน

2. MULTIP - ส่งคืนผลิตภัณฑ์ของเมทริกซ์ (เมทริกซ์ถูกเก็บไว้ในอาร์เรย์) ผลลัพธ์คืออาร์เรย์ที่มีจำนวนแถวเท่ากับ array1 และจำนวนคอลัมน์เท่ากับ array2

ไวยากรณ์: MULT(array1, array2)

Array1, array2 -- อาร์เรย์คูณ

หลังจากป้อนฟังก์ชันในเซลล์ด้านซ้ายบนของช่วงอาร์เรย์แล้ว ให้เลือกอาร์เรย์ โดยเริ่มจากเซลล์ที่มีสูตร ให้กดแป้น F2 แล้วกดแป้น CTRL+SHIFT+ENTER

3. TRANSPOSE - แปลงชุดเซลล์แนวตั้งให้เป็นแนวนอนหรือกลับกัน ผลลัพธ์ของการใช้ฟังก์ชันนี้คืออาร์เรย์ที่มีจำนวนแถวเท่ากับจำนวนคอลัมน์ในอาร์เรย์ดั้งเดิม และจำนวนคอลัมน์เท่ากับจำนวนแถวในอาร์เรย์เริ่มต้น