วิธีการกำลังสองน้อยที่สุดและหาคำตอบใน Excel วิธีการกำลังสองน้อยที่สุดใน Excel การวิเคราะห์การถดถอย กำลังสองน้อยที่สุดในตัวอย่าง excel

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 แล้วกดปุ่ม ตกลง. เส้นการถดถอยจะถูกเพิ่มลงในแผนภาพข้อมูลทดลอง โดยค่าเริ่มต้น กราฟจะแสดงเป็นจุดที่ไม่ได้เชื่อมต่อกันด้วยเส้นที่ปรับให้เรียบ

ในการเปลี่ยนลักษณะที่ปรากฏของเส้นการถดถอย ให้ทำตามขั้นตอนต่อไปนี้ คลิกขวาที่จุดที่แสดงกราฟเส้น เลือกคำสั่ง ประเภทแผนภูมิและกำหนดประเภทของแผนภาพกระจาย ดังแสดงในรูปที่ 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) ไดอะแกรมผลลัพธ์จะแสดงในรูปที่ สิบสาม

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

เชิงเส้น 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 แล้วกดปุ่ม วิ่ง. หากพบวิธีแก้ปัญหา หน้าต่างที่แสดงในรูปที่ 15.

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

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

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

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

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

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

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

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

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

ดังที่คุณทราบใน Excel มีฟังก์ชัน autosum ในตัวที่ให้คุณคำนวณค่าของค่าทั้งหมดที่อยู่ในขอบเขตที่เลือก ดังนั้น ไม่มีอะไรจะป้องกันเราจากการคำนวณค่าของนิพจน์ (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 กำหนดอยู่ในคอลัมน์เดียวหรือหนึ่งแถว

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

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

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

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

วิธีการของช่องสี่เหลี่ยมน้อยที่สุด (LSM) เป็นคำอธิบายทางคณิตศาสตร์ของการพึ่งพาตัวแปรตัวหนึ่งกับตัวแปรอื่น สามารถใช้พยากรณ์ได้

เปิดใช้งาน Solver add-in

ในการใช้ OLS ใน Excel คุณต้องเปิดใช้งาน add-in "ค้นหาวิธีแก้ปัญหา"ซึ่งถูกปิดใช้งานโดยค่าเริ่มต้น

ตอนนี้ฟังก์ชั่น หาทางออกใน Excel ถูกเปิดใช้งาน และเครื่องมือจะปรากฏบน Ribbon

เงื่อนไขของปัญหา

ให้เราอธิบายการใช้ LSM ในตัวอย่างที่เฉพาะเจาะจง เรามีตัวเลขสองแถว x และ y ตามลำดับที่แสดงในภาพด้านล่าง

การพึ่งพาอาศัยกันนี้สามารถอธิบายได้อย่างแม่นยำที่สุดโดยฟังก์ชัน:

ขณะเดียวกันก็ทราบดีว่า x=0 yเท่ากัน 0 . ดังนั้นสมการนี้สามารถอธิบายได้โดยการพึ่งพา y=nx .

เราต้องหาผลรวมกำลังสองขั้นต่ำของผลต่าง

สารละลาย

ให้เราดำเนินการตามคำอธิบายของการใช้วิธีการโดยตรง

อย่างที่คุณเห็น การประยุกต์ใช้วิธีกำลังสองน้อยที่สุดเป็นขั้นตอนทางคณิตศาสตร์ที่ค่อนข้างซับซ้อน เราได้แสดงให้เห็นในการดำเนินการด้วยตัวอย่างที่ง่ายที่สุด แต่มีกรณีที่ซับซ้อนกว่านั้นมาก อย่างไรก็ตาม ชุดเครื่องมือ Microsoft Excel ได้รับการออกแบบมาเพื่อลดความซับซ้อนในการคำนวณให้มากที่สุด

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