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

ตัวอย่าง.

ข้อมูลการทดลองเกี่ยวกับค่าของตัวแปร เอ็กซ์และ ที่จะได้รับในตาราง

อันเป็นผลมาจากการจัดตำแหน่งฟังก์ชัน

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

สาระสำคัญของวิธีกำลังสองน้อยที่สุด (LSM)

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

ดังนั้น วิธีแก้ของตัวอย่างจึงลดลงจนเหลือเพียงการหาจุดสิ้นสุดของฟังก์ชันของตัวแปรสองตัว

ที่มาของสูตรในการหาค่าสัมประสิทธิ์

ระบบสมการสองสมการที่ไม่ทราบค่าสองตัวจะถูกรวบรวมและแก้ไข การค้นหาอนุพันธ์ย่อยของฟังก์ชันเทียบกับตัวแปร กและ ข, เราเปรียบอนุพันธ์เหล่านี้ให้เป็นศูนย์

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

พร้อมข้อมูล กและ ขการทำงาน ใช้ค่าที่น้อยที่สุด มีการให้หลักฐานข้อเท็จจริงนี้

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

ถึงเวลาจำตัวอย่างดั้งเดิมแล้ว

สารละลาย.

ในตัวอย่างของเรา n=5. เรากรอกตารางเพื่อความสะดวกในการคำนวณจำนวนเงินที่รวมอยู่ในสูตรของค่าสัมประสิทธิ์ที่ต้องการ

ค่าในแถวที่สี่ของตารางได้มาจากการคูณค่าของแถวที่ 2 ด้วยค่าของแถวที่ 3 สำหรับแต่ละตัวเลข ฉัน.

ค่าในแถวที่ห้าของตารางได้มาจากการยกกำลังสองค่าของแถวที่ 2 สำหรับแต่ละตัวเลข ฉัน.

ค่าของคอลัมน์สุดท้ายของตารางคือผลรวมของค่าระหว่างแถว

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

เพราะฉะนั้น, y=0.165x+2.184คือเส้นตรงโดยประมาณที่ต้องการ

มันยังคงค้นหาว่าบรรทัดไหน y=0.165x+2.184หรือ ประมาณข้อมูลเดิมได้ดีกว่า เช่น ประมาณการโดยใช้วิธีกำลังสองน้อยที่สุด

การประมาณค่าความผิดพลาดของวิธีกำลังสองน้อยที่สุด

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

เนื่องจากแล้วเส้น y=0.165x+2.184ใกล้เคียงกับข้อมูลต้นฉบับได้ดีกว่า

ภาพประกอบกราฟิกของวิธีกำลังสองน้อยที่สุด (LSM)

ทุกอย่างดูดีบนแผนภูมิ เส้นสีแดงคือเส้นที่พบ y=0.165x+2.184, เส้นสีน้ำเงินคือ จุดสีชมพูคือข้อมูลต้นฉบับ

มีไว้เพื่ออะไร การประมาณทั้งหมดนี้มีไว้เพื่ออะไร?

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

การพิสูจน์.

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

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

คำชี้แจงปัญหาโดยใช้ตัวอย่างเฉพาะ

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

ดังนั้น ให้ X เป็นพื้นที่ค้าปลีกของร้านขายของชำ มีหน่วยเป็นตารางเมตร และ Y เป็นมูลค่าการซื้อขายต่อปี มีหน่วยเป็นล้านรูเบิล

จำเป็นต้องคาดการณ์ว่าร้านค้าจะมียอดขายเท่าใด (Y) หากมีพื้นที่ค้าปลีกนี้หรือพื้นที่นั้น เห็นได้ชัดว่าฟังก์ชัน Y = f (X) เพิ่มขึ้นเนื่องจากไฮเปอร์มาร์เก็ตขายสินค้ามากกว่าแผงลอย

คำไม่กี่คำเกี่ยวกับความถูกต้องของข้อมูลเริ่มต้นที่ใช้ในการทำนาย

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

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

สาระสำคัญของวิธีการ

ข้อมูลตารางสามารถแสดงบนระนาบคาร์ทีเซียนในรูปแบบของจุด 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 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 และการเปลี่ยนแปลงผลรวม เราจะได้:

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

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

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

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

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

นอกจากนี้ สูตรยังมีตัวแปรเชิงตรรกะ “Const” หากคุณป้อน 1 ลงในช่องที่เกี่ยวข้อง หมายความว่าคุณควรดำเนินการคำนวณ โดยสมมติว่า b = 0

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

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

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

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

ฟังก์ชันการคาดการณ์

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

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

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

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

y t = a 0 + a 1 x 1 t +...+ a n x nt + ε t

ข้อมูลเริ่มต้นเมื่อประมาณค่าพารามิเตอร์ a 0 , 1 ,..., a n คือเวกเตอร์ของค่าของตัวแปรตาม ย= (y 1 , y 2 , ... , y T)" และเมทริกซ์ของค่าของตัวแปรอิสระ

โดยที่คอลัมน์แรกประกอบด้วยคอลัมน์ที่สอดคล้องกับค่าสัมประสิทธิ์ของแบบจำลอง

วิธีการกำลังสองน้อยที่สุดมีชื่อตามหลักการพื้นฐานที่ค่าประมาณพารามิเตอร์ที่ได้รับบนพื้นฐานของมันควรเป็นไปตาม: ผลรวมของกำลังสองของข้อผิดพลาดของโมเดลควรมีค่าน้อยที่สุด

ตัวอย่างการแก้ปัญหาด้วยวิธีกำลังสองน้อยที่สุด

ตัวอย่างที่ 2.1องค์กรการค้ามีเครือข่ายประกอบด้วยร้านค้า 12 แห่งข้อมูลเกี่ยวกับกิจกรรมที่นำเสนอในตาราง 2.1.

ฝ่ายบริหารของบริษัทอยากทราบว่าขนาดรายปีจะขึ้นอยู่กับพื้นที่ขายของร้านอย่างไร

ตารางที่ 2.1

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

คำตอบของกำลังสองน้อยที่สุดให้เราแสดงมูลค่าการซื้อขายประจำปีของร้านค้านั้นล้านรูเบิล — พื้นที่ค้าปลีกของร้าน th, พันตารางเมตร.

รูปที่.2.1. Scatterplot สำหรับตัวอย่าง 2.1

เพื่อกำหนดรูปแบบของความสัมพันธ์เชิงฟังก์ชันระหว่างตัวแปรและสร้างแผนภูมิกระจาย (รูปที่ 2.1)

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

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

ตารางที่ 2.2

ดังนั้น,

ดังนั้นเมื่อพื้นที่การค้าเพิ่มขึ้น 1,000 ตารางเมตร สิ่งอื่น ๆ ที่เท่ากัน มูลค่าการซื้อขายเฉลี่ยต่อปีเพิ่มขึ้น 67.8871 ล้านรูเบิล

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

ตารางที่ 2.3

สารละลาย.ให้เราแสดงจำนวนผู้เข้าชมร้านค้าโดยเฉลี่ยต่อวันพันคน

เพื่อกำหนดรูปแบบของความสัมพันธ์เชิงฟังก์ชันระหว่างตัวแปรและสร้างแผนภูมิกระจาย (รูปที่ 2.2)

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

ข้าว. 2.2. Scatterplot เช่น 2.2

ตารางที่ 2.4

โดยทั่วไป จำเป็นต้องกำหนดพารามิเตอร์ของแบบจำลองเศรษฐมิติแบบสองปัจจัย

y เสื้อ = a 0 + a 1 x 1 เสื้อ + a 2 x 2 เสื้อ + ε เสื้อ

ข้อมูลที่จำเป็นสำหรับการคำนวณเพิ่มเติมแสดงไว้ในตาราง 1 2.4.

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

ดังนั้น,

การประเมินค่าสัมประสิทธิ์ = 61.6583 แสดงให้เห็นว่าสิ่งอื่น ๆ ที่เท่าเทียมกันเมื่อพื้นที่การซื้อขายเพิ่มขึ้น 1,000 m 2 มูลค่าการซื้อขายต่อปีจะเพิ่มขึ้นโดยเฉลี่ย 61.6583 ล้านรูเบิล

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

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

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

สาระสำคัญของบรรษัทข้ามชาติ

ให้แบบจำลอง (พาราเมตริก) ของการพึ่งพาความน่าจะเป็น (การถดถอย) ระหว่างตัวแปร (อธิบาย) ยและปัจจัยหลายประการ (ตัวแปรอธิบาย) x

เวกเตอร์ของพารามิเตอร์แบบจำลองที่ไม่รู้จักอยู่ที่ไหน

- ข้อผิดพลาดของโมเดลแบบสุ่ม

ให้มีการสังเกตตัวอย่างค่าของตัวแปรที่ระบุด้วย อนุญาต เป็นหมายเลขสังเกต () จากนั้นเป็นค่าของตัวแปรในการสังเกต -th จากนั้นสำหรับค่าที่กำหนดของพารามิเตอร์ b คุณสามารถคำนวณค่าทางทฤษฎี (แบบจำลอง) ของตัวแปรที่อธิบาย y:

ค่าของส่วนที่เหลือขึ้นอยู่กับค่าของพารามิเตอร์ข

สาระสำคัญของ LSM (ธรรมดาคลาสสิก) คือการค้นหาพารามิเตอร์ดังกล่าว b ซึ่งผลรวมของกำลังสองของส่วนที่เหลือ (อังกฤษ. ผลรวมที่เหลือของกำลังสอง) จะน้อยที่สุด:

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

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

LSM ในกรณีของโมเดลเชิงเส้น

ปล่อยให้การพึ่งพาการถดถอยเป็นเส้นตรง:

อนุญาต ย- เวกเตอร์คอลัมน์ของการสังเกตของตัวแปรที่อธิบาย และ - เมทริกซ์ของการสังเกตของปัจจัย (แถวของเมทริกซ์ - เวกเตอร์ของค่าปัจจัยในการสังเกตที่กำหนด โดยคอลัมน์ - เวกเตอร์ของค่าของปัจจัยที่กำหนดในการสังเกตทั้งหมด) . การแสดงเมทริกซ์ของโมเดลเชิงเส้นมีรูปแบบ:

จากนั้นเวกเตอร์ของการประมาณค่าของตัวแปรที่อธิบายและเวกเตอร์ของเศษการถดถอยจะเท่ากับ

ดังนั้นผลรวมของกำลังสองของเศษการถดถอยจะเท่ากับ

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

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

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

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

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

ตัวอย่าง: การถดถอยอย่างง่าย (ตามคู่)

ในกรณีของการถดถอยเชิงเส้นแบบคู่ สูตรการคำนวณจะง่ายขึ้น (คุณสามารถทำได้โดยไม่ต้องใช้พีชคณิตเมทริกซ์):

คุณสมบัติของการประมาณค่า OLS

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

ความคาดหวังทางคณิตศาสตร์ของข้อผิดพลาดแบบสุ่มคือศูนย์ และ
ปัจจัยและข้อผิดพลาดแบบสุ่มเป็นตัวแปรสุ่มอิสระ

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

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

สมมติฐานเหล่านี้สามารถกำหนดสูตรสำหรับเมทริกซ์ความแปรปรวนร่วมของเวกเตอร์ข้อผิดพลาดแบบสุ่มได้

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

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

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

ได้รับการพิสูจน์แล้ว (ทฤษฎีบทของเอตเคน) ว่าสำหรับแบบจำลองการถดถอยเชิงเส้นทั่วไป (ซึ่งไม่มีข้อจำกัดใดๆ กำหนดไว้กับเมทริกซ์ความแปรปรวนร่วมของข้อผิดพลาดแบบสุ่ม) ค่าประมาณที่มีประสิทธิภาพมากที่สุด (ในกลุ่มของการประมาณค่าที่ไม่เอนเอียงเชิงเส้น) คือค่าประมาณของสิ่งที่เรียกว่า OLS ทั่วไป (OMNK, GLS - กำลังสองน้อยที่สุดทั่วไป)- วิธี LS ที่มีเมทริกซ์น้ำหนักเท่ากับเมทริกซ์ความแปรปรวนร่วมผกผันของข้อผิดพลาดแบบสุ่ม:

จะเห็นได้ว่าสูตรสำหรับการประมาณค่า GLS ของพารามิเตอร์ของแบบจำลองเชิงเส้นมีรูปแบบ

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

ในความเป็นจริง สาระสำคัญของ OLS อยู่ที่การแปลง (P) บางอย่าง (เชิงเส้น) ของข้อมูลต้นฉบับและการประยุกต์กำลังสองน้อยที่สุดตามปกติกับข้อมูลที่แปลง วัตถุประสงค์ของการแปลงนี้คือ สำหรับข้อมูลที่แปลงแล้ว ข้อผิดพลาดแบบสุ่มเป็นไปตามสมมติฐานดั้งเดิมอยู่แล้ว

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

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

กรณีพิเศษบางประการของการประยุกต์ใช้ LSM ในทางปฏิบัติ

การประมาณเชิงเส้น

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

โต๊ะ. ผลการวัด

หมายเลขการวัด
1
2
3
4
5
6

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

น้อยที่สุด

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

สูตรสุดท้ายช่วยให้เราสามารถหาค่าสัมประสิทธิ์ ซึ่งจำเป็นในการแก้ปัญหา

เรื่องราว

จนถึงต้นศตวรรษที่ 19 นักวิทยาศาสตร์ไม่มีกฎเกณฑ์ที่แน่นอนในการแก้ระบบสมการซึ่งจำนวนไม่ทราบค่าน้อยกว่าจำนวนสมการ จนถึงเวลานั้น มีการใช้วิธีการเฉพาะ ขึ้นอยู่กับประเภทของสมการและความเฉลียวฉลาดของเครื่องคิดเลข ดังนั้นเครื่องคิดเลขที่แตกต่างกันซึ่งเริ่มต้นจากข้อมูลเชิงสังเกตเดียวกันจึงได้ข้อสรุปที่ต่างกัน Gauss (1795) ได้รับการยกย่องว่าเป็นผู้ใช้วิธีนี้เป็นครั้งแรก และ Legendre (1805) ค้นพบและตีพิมพ์โดยอิสระภายใต้ชื่อสมัยใหม่ (fr. Methode des Moindres quarres ) . ลาปลาซเชื่อมโยงวิธีการนี้เข้ากับทฤษฎีความน่าจะเป็น และนักคณิตศาสตร์ชาวอเมริกัน แอดเรน (1808) ได้พิจารณาการประยุกต์ใช้ความน่าจะเป็นของมัน วิธีการนี้แพร่หลายและปรับปรุงโดยการวิจัยเพิ่มเติมโดย Encke, Bessel, Hansen และคนอื่นๆ

การใช้ทางเลือกอื่นของบรรษัทข้ามชาติ

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

แอปพลิเคชั่นหนึ่งคือ "การแก้" ระบบสมการเชิงเส้นซึ่งจำนวนสมการมากกว่าจำนวนตัวแปร

โดยที่เมทริกซ์ไม่ใช่สี่เหลี่ยมจัตุรัส แต่เป็นสี่เหลี่ยมจัตุรัส

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

ตัวอย่าง.

ข้อมูลการทดลองเกี่ยวกับค่าของตัวแปร เอ็กซ์และ ที่จะได้รับในตาราง

อันเป็นผลมาจากการจัดตำแหน่งฟังก์ชัน

สาระสำคัญของวิธีกำลังสองน้อยที่สุด (LSM)

ที่มาของสูตรในการหาค่าสัมประสิทธิ์

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

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

พร้อมข้อมูล กและ ขการทำงาน ใช้ค่าที่น้อยที่สุด มีการให้หลักฐานข้อเท็จจริงนี้ ด้านล่างในข้อความท้ายหน้า.

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

ถึงเวลาจำตัวอย่างดั้งเดิมแล้ว

สารละลาย.

ค่าของคอลัมน์สุดท้ายของตารางคือผลรวมของค่าระหว่างแถว

เพราะฉะนั้น, y=0.165x+2.184คือเส้นตรงโดยประมาณที่ต้องการ

การประมาณค่าความผิดพลาดของวิธีกำลังสองน้อยที่สุด

เนื่องจากแล้วเส้น y=0.165x+2.184ใกล้เคียงกับข้อมูลต้นฉบับได้ดีกว่า

ภาพประกอบกราฟิกของวิธีกำลังสองน้อยที่สุด (LSM)

ในทางปฏิบัติเมื่อสร้างแบบจำลองกระบวนการต่าง ๆ โดยเฉพาะอย่างยิ่งทางเศรษฐกิจ กายภาพ เทคนิค สังคม - วิธีการหนึ่งหรือวิธีอื่นในการคำนวณค่าโดยประมาณของฟังก์ชันจากค่าที่ทราบที่จุดคงที่นั้นมีการใช้กันอย่างแพร่หลาย

ปัญหาการประมาณฟังก์ชันประเภทนี้มักเกิดขึ้น:

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

ในการปริพันธ์เชิงตัวเลข การสร้างอนุพันธ์ การแก้สมการเชิงอนุพันธ์ ฯลฯ

หากจำเป็นให้คำนวณค่าของฟังก์ชันที่จุดกึ่งกลางของช่วงเวลาที่พิจารณา

เมื่อกำหนดค่าของปริมาณลักษณะเฉพาะของกระบวนการที่อยู่นอกช่วงเวลาที่พิจารณา โดยเฉพาะอย่างยิ่งเมื่อคาดการณ์

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

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

Excel มีสองตัวเลือกสำหรับการสร้างการถดถอย

การเพิ่มการถดถอยที่เลือก (เส้นแนวโน้ม) ลงในไดอะแกรมที่สร้างขึ้นบนพื้นฐานของตารางข้อมูลสำหรับคุณลักษณะกระบวนการภายใต้การศึกษา (ใช้ได้เฉพาะเมื่อมีการสร้างไดอะแกรมแล้ว)

การใช้ฟังก์ชันทางสถิติในตัวของแผ่นงาน Excel ช่วยให้คุณสามารถรับการถดถอย (เส้นแนวโน้ม) ได้โดยตรงจากตารางข้อมูลต้นฉบับ

การเพิ่มเส้นแนวโน้มลงในแผนภูมิ

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

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

เพิ่มสมการถดถอยที่สร้างขึ้นลงในแผนภาพ

กำหนดระดับความสอดคล้องของการถดถอยที่เลือกกับข้อมูลที่แสดงบนแผนภูมิ

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

ย = ย(x)

โดยที่ x เป็นตัวแปรอิสระที่มักจะรับค่าของลำดับของจำนวนธรรมชาติ (1; 2; 3; ...) และสร้างตัวอย่างเช่นการนับถอยหลังของเวลาของกระบวนการที่กำลังศึกษา (ลักษณะ)

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

y = mx + ข

โดยที่ m คือแทนเจนต์ของความชันการถดถอยเชิงเส้นกับแกน x b - พิกัดของจุดตัดของการถดถอยเชิงเส้นกับแกนพิกัด

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

ในกรณีนี้ เส้นแนวโน้มจะถูกสร้างขึ้นตามสมการ:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

โดยที่ค่าสัมประสิทธิ์ c0, c1, c2,... c6 เป็นค่าคงที่ซึ่งมีการกำหนดค่าระหว่างการก่อสร้าง

3 . เส้นแนวโน้มลอการิทึมถูกนำมาใช้อย่างประสบความสำเร็จเมื่อสร้างแบบจำลองคุณลักษณะที่มีค่าเริ่มแรกเปลี่ยนแปลงอย่างรวดเร็วจากนั้นจึงค่อย ๆ มีเสถียรภาพ

y = ค ln(x) + ข

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

สร้างตามสมการ:

y = ค xb

โดยที่สัมประสิทธิ์ b, c เป็นค่าคงที่

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

สร้างตามสมการ:

y = ค ebx

โดยที่สัมประสิทธิ์ b, c เป็นค่าคงที่

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

กำหนดโดยสูตร:

หากต้องการเพิ่มเส้นแนวโน้มลงในชุดข้อมูล:

เปิดใช้งานแผนภูมิตามชุดข้อมูล เช่น คลิกภายในพื้นที่แผนภูมิ รายการไดอะแกรมจะปรากฏในเมนูหลัก

หลังจากคลิกที่รายการนี้ เมนูจะปรากฏขึ้นบนหน้าจอซึ่งคุณควรเลือกคำสั่งเพิ่มเส้นแนวโน้ม

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

หลังจากนี้คุณจะต้อง:

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

1 . ช่องสร้างบนซีรีส์จะแสดงชุดข้อมูลทั้งหมดในแผนภูมิที่ต้องการ หากต้องการเพิ่มเส้นแนวโน้มให้กับชุดข้อมูลเฉพาะ ให้เลือกชื่อในช่องสร้างบนชุดข้อมูล

หากจำเป็น โดยไปที่แท็บพารามิเตอร์ (รูปที่ 2) คุณสามารถตั้งค่าพารามิเตอร์ต่อไปนี้สำหรับเส้นแนวโน้ม:

เปลี่ยนชื่อเส้นแนวโน้มในชื่อของฟิลด์เส้นโค้งโดยประมาณ (เรียบ)

กำหนดจำนวนงวด (ไปข้างหน้าหรือข้างหลัง) สำหรับการพยากรณ์ในช่องพยากรณ์

แสดงสมการของเส้นแนวโน้มในพื้นที่ไดอะแกรม ซึ่งคุณควรเปิดใช้งานการแสดงสมการบนกล่องกาเครื่องหมายไดอะแกรม

แสดงค่าความน่าเชื่อถือของการประมาณ R2 ในพื้นที่ไดอะแกรม ซึ่งคุณควรเปิดใช้งานช่องทำเครื่องหมาย วางค่าความน่าเชื่อถือของการประมาณบนไดอะแกรม (R^2)

ตั้งค่าจุดตัดของเส้นแนวโน้มด้วยแกน Y ซึ่งคุณควรเปิดใช้งานช่องทำเครื่องหมายสำหรับจุดตัดของเส้นโค้งโดยมีแกน Y อยู่ที่จุด

คลิกปุ่มตกลงเพื่อปิดกล่องโต้ตอบ

ในการเริ่มแก้ไขเส้นแนวโน้มที่วาดไว้แล้ว มีสามวิธี:

ใช้คำสั่งเส้นแนวโน้มที่เลือกจากเมนูรูปแบบ โดยเลือกเส้นแนวโน้มไว้ก่อนหน้านี้

เลือกคำสั่งจัดรูปแบบเส้นแนวโน้มจากเมนูบริบท ซึ่งเรียกขึ้นมาโดยการคลิกขวาที่เส้นแนวโน้ม

ดับเบิลคลิกที่เส้นแนวโน้ม

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

หากต้องการลบเส้นแนวโน้มที่วาดไว้แล้ว ให้เลือกเส้นแนวโน้มที่จะลบแล้วกดปุ่ม Delete

ข้อดีของเครื่องมือวิเคราะห์การถดถอยที่พิจารณาคือ:

ความง่ายในการสร้างเส้นแนวโน้มบนแผนภูมิโดยไม่ต้องสร้างตารางข้อมูล

รายการประเภทเส้นแนวโน้มที่นำเสนอที่ค่อนข้างกว้าง และรายการนี้รวมถึงประเภทการถดถอยที่ใช้บ่อยที่สุด

ความสามารถในการทำนายพฤติกรรมของกระบวนการภายใต้การศึกษาโดยจำนวนก้าวไปข้างหน้าและข้างหลังโดยพลการ (ภายในขอบเขตของสามัญสำนึก)

ความสามารถในการรับสมการเส้นแนวโน้มในรูปแบบการวิเคราะห์

ความเป็นไปได้ (หากจำเป็น) ในการประเมินความน่าเชื่อถือของการประมาณ

ข้อเสียมีดังต่อไปนี้:

การสร้างเส้นแนวโน้มจะดำเนินการเฉพาะในกรณีที่มีไดอะแกรมที่สร้างขึ้นจากชุดข้อมูล

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

ในรายงาน PivotChart การเปลี่ยนมุมมองของแผนภูมิหรือรายงาน PivotTable ที่เกี่ยวข้องจะไม่รักษาเส้นแนวโน้มที่มีอยู่ ซึ่งหมายความว่าก่อนที่คุณจะวาดเส้นแนวโน้มหรือจัดรูปแบบรายงาน PivotChart คุณควรตรวจสอบให้แน่ใจว่าเค้าโครงรายงานตรงตามข้อกำหนดที่จำเป็น

เส้นแนวโน้มสามารถใช้เพื่อเสริมชุดข้อมูลที่แสดงบนแผนภูมิ เช่น กราฟ ฮิสโตแกรม แผนภูมิพื้นที่ที่ไม่เป็นมาตรฐานแบบเรียบ แผนภูมิแท่ง แผนภูมิกระจาย แผนภูมิฟอง และแผนภูมิหุ้น

คุณไม่สามารถเพิ่มเส้นแนวโน้มลงในชุดข้อมูลในรูปแบบ 3 มิติ แผนภูมิปกติ แผนภูมิเรดาร์ แผนภูมิวงกลม และแผนภูมิโดนัทได้

การใช้ฟังก์ชันในตัวของ Excel

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

Excel มีฟังก์ชันหลายอย่างสำหรับสร้างการถดถอยเชิงเส้น โดยเฉพาะ:

แนวโน้ม;

ความลาดชันและการตัด

เช่นเดียวกับฟังก์ชันต่างๆ มากมายสำหรับการสร้างเส้นแนวโน้มเอ็กซ์โพเนนเชียล โดยเฉพาะ:

LGRFRIBL.

ควรสังเกตว่าเทคนิคในการสร้างการถดถอยโดยใช้ฟังก์ชัน TREND และ GROWTH เกือบจะเหมือนกัน สิ่งเดียวกันอาจกล่าวได้เกี่ยวกับคู่ของฟังก์ชัน LINEST และ LGRFPRIBL สำหรับฟังก์ชันทั้งสี่นี้ การสร้างตารางค่าจะใช้ฟีเจอร์ของ Excel เช่น สูตรอาร์เรย์ ซึ่งทำให้กระบวนการสร้างการถดถอยค่อนข้างเกะกะ โปรดทราบว่าในความคิดของเรา การสร้างการถดถอยเชิงเส้นนั้นทำได้ง่ายที่สุดโดยใช้ฟังก์ชัน SLOPE และ INTERCEPT โดยที่ฟังก์ชันแรกจะกำหนดความชันของการถดถอยเชิงเส้น และฟังก์ชันที่สองจะกำหนดส่วนที่ถูกดักจับโดยการถดถอยบน แกน y

ข้อดีของเครื่องมือฟังก์ชันในตัวสำหรับการวิเคราะห์การถดถอยคือ:

กระบวนการที่ค่อนข้างง่ายและสม่ำเสมอในการสร้างชุดข้อมูลของคุณลักษณะภายใต้การศึกษาสำหรับฟังก์ชันทางสถิติในตัวทั้งหมดที่กำหนดเส้นแนวโน้ม

วิธีการมาตรฐานสำหรับการสร้างเส้นแนวโน้มตามชุดข้อมูลที่สร้างขึ้น

ความสามารถในการทำนายพฤติกรรมของกระบวนการที่กำลังศึกษาตามจำนวนก้าวไปข้างหน้าหรือข้างหลังที่ต้องการ

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

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

ตัวอย่างการแก้ปัญหาเฉพาะ

มาดูการแก้ปัญหาเฉพาะโดยใช้เครื่องมือ Excel ที่ระบุไว้

ภารกิจที่ 1

พร้อมตารางข้อมูลเกี่ยวกับผลกำไรขององค์กรขนส่งยานยนต์ปี 2538-2545 คุณต้องทำสิ่งต่อไปนี้:

สร้างไดอะแกรม

เพิ่มเส้นแนวโน้มเชิงเส้นและพหุนาม (กำลังสองและลูกบาศก์) ลงในแผนภูมิ

ใช้สมการเส้นแนวโน้ม รับข้อมูลแบบตารางเกี่ยวกับผลกำไรขององค์กรสำหรับแต่ละเส้นแนวโน้มสำหรับปี 1995-2004

จัดทำการคาดการณ์ผลกำไรขององค์กรในปี 2546 และ 2547

การแก้ปัญหา

ในช่วงของเซลล์ A4:C11 ของแผ่นงาน Excel ให้ป้อนแผ่นงานที่แสดงในรูปที่ 1 4.

เมื่อเลือกช่วงของเซลล์ B4:C11 แล้ว เราจะสร้างไดอะแกรม

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

เพื่อรับข้อมูลแบบตารางเกี่ยวกับผลกำไรขององค์กรสำหรับแต่ละเส้นแนวโน้มสำหรับปี 1995-2004 ลองใช้สมการเส้นแนวโน้มที่แสดงในรูปที่ 1 5. เมื่อต้องการทำเช่นนี้ ในเซลล์ในช่วง D3:F3 ให้ป้อนข้อมูลข้อความเกี่ยวกับประเภทของเส้นแนวโน้มที่เลือก: แนวโน้มเชิงเส้น แนวโน้มกำลังสอง แนวโน้มลูกบาศก์ จากนั้น ป้อนสูตรการถดถอยเชิงเส้นในเซลล์ D4 และใช้เครื่องหมายเติม คัดลอกสูตรนี้โดยมีการอ้างอิงสัมพันธ์กับช่วงเซลล์ D5:D13 ควรสังเกตว่าแต่ละเซลล์ที่มีสูตรการถดถอยเชิงเส้นจากช่วงของเซลล์ D4:D13 มีเซลล์ที่สอดคล้องกันจากช่วง A4:A13 เป็นอาร์กิวเมนต์ ในทำนองเดียวกัน สำหรับการถดถอยกำลังสอง ให้เติมช่วงของเซลล์ E4:E13 และสำหรับการถดถอยลูกบาศก์ ให้เติมช่วงของเซลล์ F4:F13 ดังนั้นจึงได้มีการรวบรวมการคาดการณ์กำไรขององค์กรในปี 2546 และ 2547 โดยใช้ 3 เทรนด์ ตารางค่าผลลัพธ์จะแสดงในรูป 6.

ภารกิจที่ 2

สร้างไดอะแกรม

เพิ่มเส้นแนวโน้มลอการิทึม กำลัง และเอ็กซ์โพเนนเชียลลงในแผนภูมิ

หาสมการของเส้นแนวโน้มที่ได้รับตลอดจนค่าความน่าเชื่อถือของการประมาณ R2 สำหรับแต่ละเส้น

ใช้สมการเส้นแนวโน้ม รับข้อมูลแบบตารางเกี่ยวกับกำไรขององค์กรสำหรับแต่ละเส้นแนวโน้มสำหรับปี 1995-2002

คาดการณ์ผลกำไรของบริษัทในปี 2546 และ 2547 โดยใช้เส้นแนวโน้มเหล่านี้

การแก้ปัญหา

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

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

R2 = 0.8659

ค่าสูงสุดของ R2 สอดคล้องกับแบบจำลองที่มีแนวโน้มพหุนาม: กำลังสอง (R2 = 0.9263) และลูกบาศก์ (R2 = 0.933)

ภารกิจที่ 3

ด้วยตารางข้อมูลเกี่ยวกับผลกำไรขององค์กรขนส่งยานยนต์สำหรับปี 2538-2545 ที่ระบุในภารกิจที่ 1 คุณต้องทำตามขั้นตอนต่อไปนี้

รับชุดข้อมูลสำหรับเส้นแนวโน้มเชิงเส้นและเลขชี้กำลังโดยใช้ฟังก์ชัน TREND และ GROW

ใช้ฟังก์ชัน TREND และ GROWTH ทำการคาดการณ์ผลกำไรขององค์กรในปี 2546 และ 2547

สร้างไดอะแกรมสำหรับข้อมูลต้นฉบับและชุดข้อมูลผลลัพธ์

การแก้ปัญหา

ลองใช้แผ่นงานสำหรับปัญหาที่ 1 (ดูรูปที่ 4) เริ่มจากฟังก์ชัน TREND กันก่อน:

เลือกช่วงของเซลล์ D4:D11 ซึ่งควรเต็มไปด้วยค่าของฟังก์ชัน TREND ที่สอดคล้องกับข้อมูลที่ทราบเกี่ยวกับผลกำไรขององค์กร

เรียกคำสั่ง Function จากเมนู Insert ในกล่องโต้ตอบตัวช่วยสร้างฟังก์ชันที่ปรากฏขึ้น ให้เลือกฟังก์ชันแนวโน้มจากหมวดหมู่ทางสถิติ จากนั้นคลิกปุ่มตกลง การดำเนินการเดียวกันนี้สามารถทำได้โดยการคลิกปุ่ม (แทรกฟังก์ชัน) บนแถบเครื่องมือมาตรฐาน

ในกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันที่ปรากฏขึ้น ให้ป้อนช่วงของเซลล์ C4:C11 ในช่อง Known_values_y ในช่อง Known_values_x - ช่วงของเซลล์ B4:B11;

หากต้องการทำให้สูตรที่ป้อนกลายเป็นสูตรอาร์เรย์ ให้ใช้คีย์ผสม + +

สูตรที่เราป้อนในแถบสูตรจะมีลักษณะดังนี้: =(TREND(C4:C11,B4:B11))

เป็นผลให้ช่วงของเซลล์ D4:D11 เต็มไปด้วยค่าที่สอดคล้องกันของฟังก์ชัน TREND (รูปที่ 9)

เพื่อคาดการณ์ผลกำไรขององค์กรในปี 2546 และ 2547 จำเป็น:

เลือกช่วงของเซลล์ D12:D13 ที่จะป้อนค่าที่คาดการณ์โดยฟังก์ชัน TREND

เรียกใช้ฟังก์ชัน TREND และในกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันที่ปรากฏขึ้น ให้ป้อนในฟิลด์ Known_values_y - ช่วงของเซลล์ C4:C11; ในช่อง Known_values_x - ช่วงของเซลล์ B4:B11; และในช่อง New_values_x - ช่วงของเซลล์ B12:B13

เปลี่ยนสูตรนี้เป็นสูตรอาร์เรย์โดยใช้คีย์ผสม Ctrl + Shift + Enter

สูตรที่ป้อนจะมีลักษณะดังนี้: =(TREND(C4:C11;B4:B11;B12:B13)) และช่วงของเซลล์ D12:D13 จะถูกเติมด้วยค่าที่ทำนายไว้ของฟังก์ชัน TREND (ดูรูปที่ 1) 9)

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

รูปที่ 10 แสดงตารางในโหมดแสดงสูตร

สำหรับข้อมูลเริ่มต้นและชุดข้อมูลที่ได้รับ แผนภาพแสดงในรูปที่ 1 สิบเอ็ด

ภารกิจที่ 4

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

รับชุดข้อมูลสำหรับการถดถอยเชิงเส้น: การใช้ฟังก์ชัน SLOPE และ INTERCEPT โดยใช้ฟังก์ชัน LINEST

รับชุดข้อมูลสำหรับการถดถอยเอ็กซ์โปเนนเชียลโดยใช้ฟังก์ชัน LGRFPRIBL

ใช้ฟังก์ชันข้างต้น คาดการณ์เกี่ยวกับการรับใบสมัครไปยังบริการจัดส่งในช่วงวันที่ 12 ถึงวันที่ 14 ของเดือนปัจจุบัน

สร้างไดอะแกรมสำหรับชุดข้อมูลต้นฉบับและข้อมูลที่ได้รับ

การแก้ปัญหา

โปรดทราบว่าต่างจากฟังก์ชัน TREND และ GROWTH ตรงที่ไม่มีฟังก์ชันใดในรายการข้างต้น (SLOPE, INTERCEPT, LINEST, LGRFPRIB) เป็นการถดถอย ฟังก์ชันเหล่านี้มีบทบาทสนับสนุนเท่านั้น โดยกำหนดพารามิเตอร์การถดถอยที่จำเป็น

สำหรับการถดถอยเชิงเส้นและเอ็กซ์โปเนนเชียลที่สร้างขึ้นโดยใช้ฟังก์ชัน SLOPE, INTERCEPT, LINEST, LGRFPRIB จะทราบลักษณะที่ปรากฏของสมการอยู่เสมอ ตรงกันข้ามกับการถดถอยเชิงเส้นและเอ็กซ์โพเนนเชียลที่สอดคล้องกับฟังก์ชัน TREND และ GROWTH

1 . มาสร้างการถดถอยเชิงเส้นด้วยสมการกัน:

y = มx+ข

โดยใช้ฟังก์ชัน SLOPE และ INTERCEPT โดยมีความชันการถดถอย m กำหนดโดยฟังก์ชัน SLOPE และเทอมอิสระ b โดยฟังก์ชัน INTERCEPT

ในการดำเนินการนี้ เราดำเนินการดังต่อไปนี้:

ป้อนตารางต้นฉบับลงในช่วงเซลล์ A4:B14;

ค่าของพารามิเตอร์ m จะถูกกำหนดในเซลล์ C19 เลือกฟังก์ชันความชันจากหมวดสถิติ ป้อนช่วงของเซลล์ B4:B14 ในช่องknown_values_y และช่วงของเซลล์ A4:A14 ในช่องknown_values_x สูตรจะถูกป้อนในเซลล์ C19: =SLOPE(B4:B14,A4:A14);

เมื่อใช้เทคนิคที่คล้ายกัน จะกำหนดค่าของพารามิเตอร์ b ในเซลล์ D19 และเนื้อหาจะมีลักษณะดังนี้: =SEGMENT(B4:B14,A4:A14) ดังนั้นค่าของพารามิเตอร์ m และ b ที่จำเป็นสำหรับการสร้างการถดถอยเชิงเส้นจะถูกเก็บไว้ในเซลล์ C19, D19 ตามลำดับ

จากนั้น ป้อนสูตรการถดถอยเชิงเส้นในเซลล์ C4 ในรูปแบบ: =$C*A4+$D ในสูตรนี้ เซลล์ C19 และ D19 จะถูกเขียนด้วยการอ้างอิงแบบสัมบูรณ์ (ที่อยู่ของเซลล์ไม่ควรเปลี่ยนแปลงในระหว่างการคัดลอกที่เป็นไปได้) เครื่องหมายอ้างอิงสัมบูรณ์ $ สามารถพิมพ์ได้จากแป้นพิมพ์หรือใช้ปุ่ม F4 หลังจากวางเคอร์เซอร์บนที่อยู่ของเซลล์แล้ว ใช้จุดจับเติม คัดลอกสูตรนี้ลงในช่วงของเซลล์ C4:C17 เราได้รับชุดข้อมูลที่ต้องการ (รูปที่ 12) เนื่องจากจำนวนคำขอเป็นจำนวนเต็ม คุณควรตั้งค่ารูปแบบตัวเลขด้วยจำนวนตำแหน่งทศนิยมเป็น 0 บนแท็บตัวเลขของหน้าต่างรูปแบบเซลล์

2 . ตอนนี้เรามาสร้างการถดถอยเชิงเส้นที่กำหนดโดยสมการ:

y = มx+ข

โดยใช้ฟังก์ชัน LINEST

สำหรับสิ่งนี้:

ป้อนฟังก์ชัน LINEST เป็นสูตรอาร์เรย์ในช่วงเซลล์ C20:D20: =(LINEST(B4:B14,A4:A14)) เป็นผลให้เราได้รับค่าของพารามิเตอร์ m ในเซลล์ C20 และค่าของพารามิเตอร์ b ในเซลล์ D20

ป้อนสูตรในเซลล์ D4: =$C*A4+$D;

คัดลอกสูตรนี้โดยใช้เครื่องหมายเติมลงในช่วงเซลล์ D4:D17 และรับชุดข้อมูลที่ต้องการ

3 . เราสร้างการถดถอยเอ็กซ์โปเนนเชียลด้วยสมการ:

การใช้ฟังก์ชัน LGRFPRIBL จะดำเนินการในลักษณะเดียวกัน:

ในช่วงเซลล์ C21:D21 เราป้อนฟังก์ชัน LGRFPRIBL เป็นสูตรอาร์เรย์: =( LGRFPRIBL (B4:B14,A4:A14)) ในกรณีนี้ ค่าของพารามิเตอร์ m จะถูกกำหนดในเซลล์ C21 และค่าของพารามิเตอร์ b จะถูกกำหนดในเซลล์ D21

ใส่สูตรลงในเซลล์ E4: =$D*$C^A4;

โดยใช้เครื่องหมายเติม สูตรนี้จะถูกคัดลอกไปยังช่วงของเซลล์ E4:E17 โดยที่ชุดข้อมูลสำหรับการถดถอยเอ็กซ์โปเนนเชียลจะอยู่ (ดูรูปที่ 12)

ในรูป รูปที่ 13 แสดงตารางที่คุณสามารถดูฟังก์ชันที่เราใช้กับช่วงเซลล์ที่ต้องการ รวมถึงสูตรต่างๆ

ขนาด ร 2 เรียกว่า ค่าสัมประสิทธิ์การตัดสินใจ.

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

เพื่อประเมินความสำคัญของ R จะใช้การทดสอบ F ของฟิชเชอร์ โดยคำนวณโดยใช้สูตร

ที่ไหน n- ขนาดตัวอย่าง (จำนวนการทดลอง)

k คือจำนวนสัมประสิทธิ์แบบจำลอง

ถ้า F เกินค่าวิกฤตของข้อมูล nและ เคและความน่าจะเป็นของความเชื่อมั่นที่ยอมรับได้ จึงถือว่าค่า R มีนัยสำคัญ ตารางค่าวิกฤตของ F แสดงไว้ในหนังสืออ้างอิงเกี่ยวกับสถิติทางคณิตศาสตร์

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

ในการสร้างแบบจำลองการถดถอยเชิงเส้น คุณต้องมี:

1) เตรียมรายการ n แถวและ m คอลัมน์ที่มีข้อมูลการทดลอง (คอลัมน์ที่มีค่าเอาต์พุต ยจะต้องเป็นรายการแรกหรือรายการสุดท้าย) ตัวอย่างเช่น ลองนำข้อมูลจากงานก่อนหน้าโดยเพิ่มคอลัมน์ชื่อ “Period No” กำหนดหมายเลขช่วงตั้งแต่ 1 ถึง 12 (ซึ่งจะเป็นค่าเหล่านี้ เอ็กซ์)

2) ไปที่เมนู ข้อมูล/การวิเคราะห์ข้อมูล/การถดถอย