การถดถอยเชิงเส้นคู่ใน excel การถดถอยใน Excel

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

การเชื่อมต่อแพ็คเกจการวิเคราะห์

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

1. ย้ายไปที่แท็บ "ไฟล์"
2. ไปที่ส่วน "การตั้งค่า"
3. หน้าต่างตัวเลือกของ Excel จะเปิดขึ้น ไปที่ส่วนย่อย "ส่วนเสริม"
4. ในส่วนล่างสุดของหน้าต่างที่เปิดขึ้น เราจัดเรียงสวิตช์ใหม่ในกลุ่ม "การจัดการ" เป็นตำแหน่ง "Excel Add-in" หากอยู่ในตำแหน่งอื่น คลิกที่ปุ่ม "ไป"
5. หน้าต่าง Add-in ของ Excel จะเปิดขึ้น ทำเครื่องหมายที่ช่องถัดจาก "แพ็คเกจการวิเคราะห์" คลิกที่ปุ่ม "ตกลง"

ตอนนี้เมื่อเราไปที่แท็บ "ข้อมูล" บนแถบเครื่องมือ "การวิเคราะห์" เราจะเห็นปุ่มใหม่ - "การวิเคราะห์ข้อมูล"

ประเภทของการวิเคราะห์การถดถอย

การถดถอยมีหลายประเภท:

• พาราโบลา;
• พลัง;
• ลอการิทึม;
• ชี้แจง;
• สาธิต;
• ไฮเปอร์โบลิก;
• การถดถอยเชิงเส้น.

เกี่ยวกับการดำเนินการของมุมมองล่าสุด การวิเคราะห์การถดถอยเราจะพูดถึง Excel เพิ่มเติมในภายหลัง

การถดถอยเชิงเส้นใน Excel

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

สมการถดถอยเชิงเส้นทั่วไปมีลักษณะดังนี้: Y = a0 + a1x1 + ... + axk ในสูตรนี้ Y หมายถึงตัวแปรที่เรากำลังพยายามศึกษาอิทธิพลของปัจจัยต่างๆ ในกรณีของเรา นี่คือจำนวนผู้ซื้อ ค่า x คือ ปัจจัยต่างๆที่ส่งผลต่อตัวแปร พารามิเตอร์ a คือค่าสัมประสิทธิ์การถดถอย นั่นคือพวกเขากำหนดความสำคัญของปัจจัยเฉพาะ ดัชนี k หมายถึงจำนวนทั้งหมดของปัจจัยเดียวกันนี้

การวิเคราะห์ผลการวิเคราะห์

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

หนึ่งในตัวบ่งชี้หลักคือ R-square มันบ่งบอกถึงคุณภาพของรุ่น ในกรณีของเรา ค่าสัมประสิทธิ์ที่กำหนดเท่ากับ 0.705 หรือประมาณ 70.5% มีคุณภาพในระดับที่ยอมรับได้ ความสัมพันธ์ที่น้อยกว่า 0.5 ถือว่าไม่ดี

ตัวบ่งชี้ที่สำคัญอีกตัวจะอยู่ในเซลล์ที่จุดตัดของเส้น "แยก Y" และคอลัมน์ "ค่าสัมประสิทธิ์" นี่คือการระบุว่าค่า Y จะมีค่าเท่าใด และในกรณีของเรา นี่คือจำนวนผู้ซื้อ โดยที่ปัจจัยอื่นๆ ทั้งหมดมีค่าเท่ากับศูนย์ ในตารางนี้ ค่าที่กำหนดเท่ากับ 58.04

ค่าที่จุดตัดของคอลัมน์ "ตัวแปร X1" และ "ค่าสัมประสิทธิ์" แสดงระดับการพึ่งพาของ Y บน X ในกรณีของเรา นี่คือระดับการพึ่งพาของจำนวนลูกค้าในร้านค้ากับอุณหภูมิ ค่าสัมประสิทธิ์ 1.31 ถือว่าค่อนข้างมาก อัตราสูงอิทธิพล.

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

เราดีใจที่สามารถช่วยคุณแก้ไขปัญหาได้

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

บทความนี้ช่วยคุณได้หรือไม่?

วิธีการถดถอยเชิงเส้นช่วยให้เราสามารถอธิบายเส้นตรงที่เหมาะกับชุดคู่อันดับ (x, y) ได้ดีที่สุด สมการของเส้นตรงที่เรียกว่าสมการเชิงเส้นแสดงไว้ด้านล่าง:

ŷ คือค่าที่คาดหวังของ y สำหรับค่าที่กำหนดของ x

x - ตัวแปรอิสระ

a - ส่วนบนแกน y สำหรับเส้นตรง

b คือความชันของเส้นตรง

ในภาพด้านล่าง แนวคิดนี้แสดงเป็นกราฟิก:

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

ŷ และ y มักสับสน กราฟแสดงคู่อันดับ 6 คู่ของจุดและเส้นตามสมการที่กำหนด

รูปนี้แสดงจุดที่ตรงกับคู่คำสั่ง x = 2 และ y = 4 โปรดทราบว่าค่าคาดหวังของ y ตามบรรทัดที่ เอ็กซ์= 2 คือ ŷ เราสามารถยืนยันได้ด้วยสมการต่อไปนี้:

ŷ = 2 + 0.5x =2 +0.5(2) =3.

ค่า y คือจุดจริงและค่า ŷ คือค่า y ที่คาดหวังโดยใช้ สมการเชิงเส้นสำหรับค่าที่กำหนดของ x

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

การใช้ Excel เพื่อกำหนดการถดถอยเชิงเส้น

ในการใช้เครื่องมือวิเคราะห์การถดถอยที่มีอยู่ใน Excel คุณต้องเปิดใช้งาน Add-in ชุดวิเคราะห์. คุณสามารถค้นหาได้โดยคลิกที่แท็บ ไฟล์ -> ตัวเลือก(2007+) ในกล่องโต้ตอบที่ปรากฏขึ้น ตัวเลือกเก่งไปที่แท็บ ส่วนเสริมในสนาม ควบคุมเลือก ส่วนเสริมเก่งแล้วคลิก ไป.ในหน้าต่างที่ปรากฏขึ้น ให้ทำเครื่องหมายที่ช่องถัดจาก ชุดวิเคราะห์,คลิก ตกลง.

ในแท็บ ข้อมูลในกลุ่ม การวิเคราะห์ปุ่มใหม่จะปรากฏขึ้น การวิเคราะห์ข้อมูล.

เพื่อสาธิตวิธีการทำงานของ Add-in ให้ใช้ข้อมูลจากบทความที่แล้ว ซึ่งผู้ชายและผู้หญิงใช้โต๊ะร่วมกันในห้องน้ำ ป้อนข้อมูลสำหรับตัวอย่างห้องน้ำของเราในคอลัมน์ A และ B ของแผ่นเปล่า

ไปที่แท็บ ข้อมูล,ในกลุ่ม การวิเคราะห์คลิก การวิเคราะห์ข้อมูล.ในหน้าต่างที่ปรากฏขึ้น การวิเคราะห์ข้อมูลเลือก การถดถอยดังรูปแล้วคลิกตกลง

ตั้งค่าพารามิเตอร์การถดถอยที่จำเป็นในหน้าต่าง การถดถอยตามที่แสดงในภาพ:

คลิก ตกลง.รูปด้านล่างแสดงผลที่ได้รับ:

ผลลัพธ์เหล่านี้สอดคล้องกับที่เราได้รับจากการคำนวณอิสระในบทความก่อนหน้า

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

ประเภทของการถดถอย

แนวคิดนี้ได้รับการแนะนำในวิชาคณิตศาสตร์โดย Francis Galton ในปี 1886 การถดถอยเกิดขึ้น:

• เชิงเส้น;
• พาราโบลา;
• พลัง;
• ชี้แจง;
• ไฮเปอร์โบลิก;
• สาธิต;
• ลอการิทึม

ตัวอย่างที่ 1

พิจารณาปัญหาในการพิจารณาการพึ่งพาจำนวนสมาชิกในทีมที่เกษียณจากเงินเดือนเฉลี่ยของสถานประกอบการอุตสาหกรรม 6 แห่ง

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

สำหรับปัญหาในการพิจารณาการพึ่งพาจำนวนพนักงานที่ถูกเลิกจ้างกับเงินเดือนเฉลี่ยที่ 6 องค์กร แบบจำลองการถดถอยมีรูปแบบของสมการ Y = a0 + a1 × 1 + ... + akxk โดยที่ хi คือ ตัวแปรที่มีอิทธิพล ai คือสัมประสิทธิ์การถดถอย และ k คือจำนวนของปัจจัย

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

การใช้ความสามารถของสเปรดชีต "Excel"

การวิเคราะห์การถดถอยใน Excel จะต้องนำหน้าด้วยการประยุกต์ใช้ฟังก์ชันที่มีอยู่แล้วภายในกับข้อมูลแบบตารางที่มีอยู่ อย่างไรก็ตาม สำหรับจุดประสงค์เหล่านี้ ควรใช้ Add-in "เครื่องมือวิเคราะห์" ที่มีประโยชน์มากจะดีกว่า ในการเปิดใช้งานคุณต้อง:

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

หากทำทุกอย่างถูกต้อง ปุ่มที่ต้องการจะปรากฏทางด้านขวาของแท็บข้อมูลซึ่งอยู่เหนือแผ่นงาน Excel

การถดถอยเชิงเส้นใน Excel

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

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

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

การวิเคราะห์ผลการถดถอยสำหรับ R-square

ใน Excel ข้อมูลที่ได้รับระหว่างการประมวลผลข้อมูลของตัวอย่างที่พิจารณาจะมีลักษณะดังนี้:

ก่อนอื่น คุณควรคำนึงถึงค่าของ R-square เป็นค่าสัมประสิทธิ์ของการกำหนด ในตัวอย่างนี้ R-square = 0.755 (75.5%) กล่าวคือ พารามิเตอร์ที่คำนวณได้ของแบบจำลองจะอธิบายความสัมพันธ์ระหว่างพารามิเตอร์ที่พิจารณาได้ 75.5% ยิ่งค่าสัมประสิทธิ์ของการกำหนดสูงเท่าใด โมเดลที่เลือกสำหรับงานเฉพาะก็จะยิ่งมีความเกี่ยวข้องมากขึ้นเท่านั้น เชื่อว่าอธิบายสถานการณ์จริงที่มีค่า R-squared สูงกว่า 0.8 ได้อย่างถูกต้อง ถ้า R-square เป็น tcr สมมติฐานของความไม่มีนัยสำคัญของเทอมอิสระของสมการเชิงเส้นจะถูกปฏิเสธ

ในปัญหาที่พิจารณาสำหรับสมาชิกฟรีโดยใช้เครื่องมือ Excel ได้รับว่า t = 169.20903 และ p = 2.89E-12 นั่นคือ เรามีความเป็นไปได้เป็นศูนย์ที่สมมติฐานที่ถูกต้องเกี่ยวกับความสำคัญของสมาชิกฟรีจะเป็น ปฏิเสธ สำหรับค่าสัมประสิทธิ์ที่ไม่ทราบค่า t=5.79405 และ p=0.001158 กล่าวอีกนัยหนึ่ง ความน่าจะเป็นที่สมมติฐานที่ถูกต้องเกี่ยวกับค่าสัมประสิทธิ์ที่ไม่มีนัยสำคัญจะถูกปฏิเสธคือ 0.12%

ดังนั้นจึงสามารถโต้แย้งได้ว่าสมการการถดถอยเชิงเส้นที่ได้นั้นเพียงพอแล้ว

ปัญหาเรื่องความเหมาะสมในการซื้อหุ้น

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

ฝ่ายบริหารของ NNN จะต้องตัดสินใจเกี่ยวกับความเหมาะสมในการซื้อหุ้น 20% ใน MMM SA ค่าใช้จ่ายของแพคเกจ (JV) 70 ล้านเหรียญสหรัฐ ผู้เชี่ยวชาญของ NNN รวบรวมข้อมูลเกี่ยวกับธุรกรรมที่คล้ายคลึงกัน มีการตัดสินใจที่จะประเมินมูลค่าของบล็อกหุ้นตามพารามิเตอร์ดังกล่าวซึ่งแสดงเป็นล้านดอลลาร์สหรัฐเป็น:

• บัญชีเจ้าหนี้ (VK);
• ผลประกอบการประจำปี (VO);
• บัญชีลูกหนี้ (VD);
• ต้นทุนของสินทรัพย์ถาวร (SOF)

นอกจากนี้ยังใช้พารามิเตอร์การค้างชำระเงินเดือนขององค์กร (V3 P) ในหน่วยหลายพันดอลลาร์สหรัฐฯ

วิธีแก้ปัญหาโดยใช้สเปรดชีต Excel

ก่อนอื่นคุณต้องสร้างตารางข้อมูลเริ่มต้น ดูเหมือนว่า:

• เรียกหน้าต่าง "การวิเคราะห์ข้อมูล"
• เลือกส่วน "การถดถอย"
• ในกล่อง "ช่วงเวลาอินพุต Y" ป้อนช่วงของค่าของตัวแปรตามจากคอลัมน์ G
• คลิกที่ไอคอนที่มีลูกศรสีแดงทางด้านขวาของกล่อง "ช่วงเวลาอินพุต X" และเลือกช่วงของค่าทั้งหมดจากแผ่นงาน คอลัมน์ B,C,ดี,เอฟ.

เลือก "แผ่นงานใหม่" แล้วคลิก "ตกลง"

รับการวิเคราะห์การถดถอยสำหรับปัญหาที่กำหนด

การตรวจสอบผลลัพธ์และข้อสรุป

“เรารวบรวม” จากข้อมูลที่ปัดเศษที่แสดงด้านบนในแผ่นงานสเปรดชีต Excel สมการการถดถอย:

SP \u003d 0.103 * SOF + 0.541 * VO - 0.031 * VK + 0.405 * VD + 0.691 * VZP - 265.844

ในรูปแบบทางคณิตศาสตร์ที่คุ้นเคยกันมากขึ้น สามารถเขียนเป็น:

y = 0.103*x1 + 0.541*x2 - 0.031*x3 +0.405*x4 +0.691*x5 - 265.844

ข้อมูลสำหรับ JSC "MMM" แสดงในตาราง:

เมื่อแทนลงในสมการถดถอยจะได้ตัวเลข 64.72 ล้านดอลลาร์สหรัฐ ซึ่งหมายความว่าไม่ควรซื้อหุ้นของ JSC MMM เนื่องจากมูลค่า 70 ล้านดอลลาร์สหรัฐนั้นค่อนข้างเกินจริง

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

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

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

งานหลักและประเภทของการถดถอย

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

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

ภารกิจหลักของการสร้างแบบจำลองการถดถอยมีดังนี้:

1. การเลือกตัวแปรอิสระที่มีนัยสำคัญ (Х1, Х2, …, Xk)
2. การเลือกประเภทของฟังก์ชัน
3. การสร้างค่าประมาณสำหรับค่าสัมประสิทธิ์
4. อาคาร ช่วงความมั่นใจและฟังก์ชันการถดถอย
5. ตรวจสอบความสำคัญของค่าประมาณที่คำนวณได้และสมการถดถอยที่สร้างขึ้น

การวิเคราะห์การถดถอยมีหลายประเภท:

• จับคู่ (ตัวแปรตาม 1 ตัวและตัวแปรอิสระ 1 ตัว);
• หลายตัว (ตัวแปรอิสระหลายตัว)

สมการถดถอยมีสองประเภท:

1. เชิงเส้น แสดงความสัมพันธ์เชิงเส้นที่เข้มงวดระหว่างตัวแปร
2. ไม่เชิงเส้น—สมการที่สามารถรวมยกกำลัง เศษส่วน และฟังก์ชันตรีโกณมิติ

คำแนะนำการสร้างแบบจำลอง

ในการดำเนินการก่อสร้างที่กำหนดใน Excel คุณต้องทำตามคำแนะนำ:

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

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

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

การถดถอยเกิดขึ้น:

เชิงเส้น (y = a + bx);

พาราโบลา (y = a + bx + cx 2);

เลขยกกำลัง (y = a * exp(bx));

กำลัง (y = a*x^b);

ไฮเปอร์โบลิก (y = b/x + a);

ลอการิทึม (y = b * 1n(x) + a);

เลขชี้กำลัง (y = a * b^x)

พิจารณาตัวอย่างการสร้างแบบจำลองการถดถอยใน Excel และตีความผลลัพธ์ ลองใช้การถดถอยแบบเชิงเส้น

งาน. ที่ 6 องค์กร เฉลี่ยต่อเดือน ค่าจ้างและจำนวนพนักงานที่เกษียณอายุ มีความจำเป็นต้องพิจารณาการพึ่งพาจำนวนพนักงานที่เกษียณจากเงินเดือนเฉลี่ย

แบบจำลองการถดถอยเชิงเส้นมีรูปแบบดังนี้

Y \u003d a 0 + a 1 x 1 + ... + a k x k

โดยที่ a คือสัมประสิทธิ์การถดถอย x คือตัวแปรที่มีอิทธิพล และ k คือจำนวนตัวประกอบ

ในตัวอย่างของเรา Y เป็นตัวบ่งชี้ของพนักงานที่ลาออก ปัจจัยที่มีอิทธิพลคือค่าจ้าง (x)

Excel มีฟังก์ชันในตัวที่สามารถใช้ในการคำนวณพารามิเตอร์ของแบบจำลองการถดถอยเชิงเส้น แต่ Add-in ของ Analysis ToolPak จะทำได้เร็วขึ้น

เปิดใช้งานเครื่องมือวิเคราะห์ที่ทรงพลัง:

1. คลิกปุ่ม "Office" และไปที่แท็บ "ตัวเลือก Excel" "ส่วนเสริม".

2. ด้านล่างภายใต้รายการแบบหล่นลงในช่อง "การจัดการ" จะมีข้อความว่า "Excel Add-in" (หากไม่มีให้คลิกที่ช่องทำเครื่องหมายทางด้านขวาแล้วเลือก) และปุ่มไป คลิก.

3. รายการส่วนเสริมที่มีอยู่จะเปิดขึ้น เลือก "แพ็คเกจการวิเคราะห์" และคลิกตกลง

เมื่อเปิดใช้งาน ส่วนเสริมจะพร้อมใช้งานภายใต้แท็บข้อมูล

ตอนนี้เราจะจัดการโดยตรงกับการวิเคราะห์การถดถอย

1. เปิดเมนูของเครื่องมือวิเคราะห์ข้อมูล เลือก "การถดถอย"

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

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

ก่อนอื่น เราใส่ใจกับ R-square และค่าสัมประสิทธิ์

R-square คือค่าสัมประสิทธิ์ของการกำหนด ในตัวอย่างของเรา มันคือ 0.755 หรือ 75.5% ซึ่งหมายความว่าพารามิเตอร์ที่คำนวณได้ของแบบจำลองจะอธิบายความสัมพันธ์ระหว่างพารามิเตอร์ที่ศึกษาได้ 75.5% ยิ่งค่าสัมประสิทธิ์ของการกำหนดสูงเท่าไร รุ่นที่ดีกว่า. ดี - สูงกว่า 0.8 แย่ - น้อยกว่า 0.5 (การวิเคราะห์ดังกล่าวแทบจะถือว่าสมเหตุสมผล) ในตัวอย่างของเรา - "ไม่เลว"

ค่าสัมประสิทธิ์ 64.1428 แสดงให้เห็นว่า Y จะเป็นอย่างไรหากตัวแปรทั้งหมดในแบบจำลองที่กำลังพิจารณามีค่าเท่ากับ 0 นั่นคือปัจจัยอื่นๆ ที่ไม่ได้อธิบายไว้ในแบบจำลองจะส่งผลต่อค่าของพารามิเตอร์ที่วิเคราะห์ด้วย

ค่าสัมประสิทธิ์ -0.16285 แสดงน้ำหนักของตัวแปร X บน Y นั่นคือ เงินเดือนเฉลี่ยต่อเดือนภายในแบบจำลองนี้ส่งผลต่อจำนวนของผู้ลาออกโดยมีค่าน้ำหนักเป็น -0.16285 (นี่เป็นระดับอิทธิพลเล็กน้อย) เครื่องหมาย “-” บ่งชี้ถึงผลกระทบเชิงลบ: ยิ่งเงินเดือนสูง ยิ่งลาออกน้อยลง ซึ่งเป็นธรรม.

การประมวลผลข้อมูลทางสถิติสามารถทำได้โดยใช้ Add-in แพ็คเกจการวิเคราะห์(รูปที่ 62)

จากรายการที่เสนอ เลือกรายการ " การถดถอย” และคลิกด้วยปุ่มซ้ายของเมาส์ ถัดไป คลิกตกลง

หน้าต่างที่แสดงในรูป 63.

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

เครื่องมือการถดถอยใช้ฟังก์ชัน LINEST.

กล่องโต้ตอบ REGRESS

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

ระดับความน่าเชื่อถือ เลือกกล่องกาเครื่องหมายเพื่อรวมระดับเพิ่มเติมในตารางผลรวมผลลัพธ์ ในฟิลด์ที่เหมาะสม ให้ป้อนระดับความเชื่อมั่นที่คุณต้องการใช้ นอกเหนือจากระดับความเชื่อมั่น 95% ที่เป็นค่าเริ่มต้น

ค่าคงที่ - ศูนย์ ทำเครื่องหมายในช่องเพื่อให้เส้นถดถอยผ่านจุดกำเนิด

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

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

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

ส่วนที่เหลือ เลือกกล่องกาเครื่องหมายเพื่อรวมส่วนที่เหลือในตารางผลลัพธ์

Standardized Residuals เลือกกล่องกาเครื่องหมายเพื่อรวม Standardized Residuals ในตารางเอาต์พุต

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

Fit Plot เลือกกล่องกาเครื่องหมายเพื่อลงจุดค่าที่คาดการณ์เทียบกับค่าที่สังเกตได้

พล็อตความน่าจะเป็นปกติทำเครื่องหมายในช่องเพื่อลงจุดความน่าจะเป็นปกติ

การทำงาน LINEST

ในการคำนวณให้เลือกเซลล์ที่เราต้องการแสดงค่าเฉลี่ยด้วยเคอร์เซอร์แล้วกดปุ่ม = บนแป้นพิมพ์ ถัดไป ในช่องชื่อ ให้ระบุฟังก์ชันที่ต้องการ เช่น เฉลี่ย(รูปที่ 22)

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

สมการของเส้นตรงคือ:

y=m 1 x 1 +m 2 x 2 +…+b (ในกรณีที่มีค่า x หลายช่วง)

โดยที่ค่าอิสระ y เป็นฟังก์ชันของค่าอิสระ x ค่า m คือค่าสัมประสิทธิ์ที่สอดคล้องกับตัวแปรอิสระ x แต่ละตัว และ b คือค่าคงที่ โปรดทราบว่า y, x และ m สามารถเป็นเวกเตอร์ได้ การทำงาน LINESTส่งคืนอาร์เรย์ (mn;mn-1;…;m 1 ;b) LINESTอาจส่งกลับสถิติการถดถอยเพิ่มเติม

LINEST(known_y-values, known_x-values, const; สถิติ)

ค่า Known_y - ชุดของค่า y ที่ทราบแล้วสำหรับความสัมพันธ์ y=mx+b

ถ้าอาร์เรย์ของ Know_y มีหนึ่งคอลัมน์ แต่ละคอลัมน์ของอาร์เรย์ของ Know_x จะถูกตีความเป็นตัวแปรแยกต่างหาก

หากอาร์เรย์ของ Know_y มีหนึ่งแถว แต่ละแถวของอาร์เรย์ของ Know_x จะถูกตีความเป็นตัวแปรแยกต่างหาก

ค่า Known_x - ชุดตัวเลือกของค่า x ที่ทราบอยู่แล้วสำหรับความสัมพันธ์ y=mx+b

อาร์เรย์ Know_x สามารถมีชุดของตัวแปรตั้งแต่หนึ่งชุดขึ้นไป หากใช้ตัวแปรเพียงตัวเดียว arrays_known_y_values ​​และknown_x_values ​​สามารถมีรูปร่างใดก็ได้ - ตราบใดที่มีขนาดเท่ากัน หากใช้ตัวแปรมากกว่าหนึ่งตัวแปร Know_y's จะต้องเป็นเวกเตอร์ (นั่นคือ สูงหนึ่งแถวหรือกว้างหนึ่งคอลัมน์)

หากไม่ระบุ array_known_x ระบบจะถือว่าอาร์เรย์นี้ (1;2;3;...) มีขนาดเท่ากับ array_known_y

Const เป็นค่าบูลีนที่ระบุว่าค่าคงที่ b จำเป็นต้องเป็น 0 หรือไม่

ถ้าอาร์กิวเมนต์ "const" เป็น TRUE หรือละไว้ ค่าคงที่ b จะได้รับการประเมินตามปกติ

หากอาร์กิวเมนต์ "const" เป็น FALSE ค่าของ b จะถือว่าเป็น 0 และค่าของ m จะถูกเลือกในลักษณะที่ความสัมพันธ์ y=mx พอใจ

สถิติคือค่าบูลีนที่ระบุว่าควรส่งคืนสถิติการถดถอยเพิ่มเติมหรือไม่

ถ้าสถิติเป็น TRUE LINEST จะส่งกลับสถิติการถดถอยเพิ่มเติม อาร์เรย์ที่ส่งคืนจะมีลักษณะดังนี้: (mn;mn-1;...;m1;b:sen;sen-1;...;se1;seb:r2;sey:F;df:ssreg;ssresid)

ถ้าสถิติเป็น FALSE หรือละเว้นไว้ LINEST จะส่งกลับเฉพาะค่าสัมประสิทธิ์ m และค่าคงที่ b

สถิติการถดถอยเพิ่มเติม (ตารางที่ 17)

ค่า	คำอธิบาย
se1,se2,...,เซ็น	ค่าความผิดพลาดมาตรฐานสำหรับค่าสัมประสิทธิ์ m1,m2,...,mn.
seb	ค่ามาตรฐานข้อผิดพลาดสำหรับค่าคงที่ b (seb = #N/A ถ้าอาร์กิวเมนต์ 'const' เป็น FALSE)
r2	ค่าสัมประสิทธิ์การกำหนด ค่าที่แท้จริงของ y จะเปรียบเทียบกับค่าที่ได้จากสมการเส้นตรง จากผลการเปรียบเทียบค่าสัมประสิทธิ์ของค่ากำหนดจะถูกคำนวณโดยทำให้เป็นมาตรฐานจาก 0 ถึง 1 หากมีค่าเท่ากับ 1 แสดงว่ามีความสัมพันธ์กับแบบจำลองอย่างสมบูรณ์ เช่น ไม่มีความแตกต่างระหว่างค่าจริงและค่าประมาณ ​ของ ย. มิฉะนั้น ถ้าสัมประสิทธิ์ของดีเทอร์มินิสต์เป็น 0 ก็ไม่มีประโยชน์ที่จะใช้สมการถดถอยเพื่อทำนายค่า y สำหรับการได้รับ ข้อมูลเพิ่มเติมสำหรับวิธีคำนวณ r2 โปรดดู "หมายเหตุ" ที่ส่วนท้ายของส่วนนี้
เซย์	ข้อผิดพลาดมาตรฐานสำหรับการประมาณค่า y
ฉ	ค่าสถิติ F หรือค่า F ที่สังเกตได้ สถิติ F ใช้เพื่อระบุว่าความสัมพันธ์ที่สังเกตได้ระหว่างตัวแปรตามและตัวแปรอิสระนั้นเป็นแบบสุ่มหรือไม่
ดีเอฟ	ระดับความอิสระ. องศาอิสระมีประโยชน์สำหรับการค้นหาค่าวิกฤต F ในตารางสถิติ ในการกำหนดระดับความเชื่อมั่นของแบบจำลอง คุณต้องเปรียบเทียบค่าในตารางกับสถิติ F ที่ส่งคืนโดย LINEST ดู "ข้อสังเกต" ที่ส่วนท้ายของส่วนนี้สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการคำนวณ df ตัวอย่างที่ 4 ด้านล่างแสดงการใช้ F และ df
ssreg	ผลรวมถดถอยของกำลังสอง
เอสเรสซิด	ผลรวมของกำลังสองที่เหลือ สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการคำนวณ ssreg และ ssresid โปรดดู "หมายเหตุ" ที่ส่วนท้ายของส่วนนี้

รูปภาพด้านล่างแสดงลำดับการส่งคืนสถิติการถดถอยเพิ่มเติม (รูปที่ 64)

หมายเหตุ:

เส้นตรงใดๆ สามารถอธิบายได้จากความชันและจุดตัดกับแกน y:

ความชัน (ม.): เพื่อกำหนดความชันของเส้น ซึ่งปกติเขียนแทนด้วย ม. คุณต้องใช้จุดสองจุดบนเส้น (x 1 ,y 1) และ (x 2 ,y 2); ความชันจะเท่ากับ (y 2 -y 1) / (x 2 -x 1)

จุดตัด y (b): จุดตัด y ของเส้น ซึ่งปกติเขียนแทนด้วย b คือค่า y สำหรับจุดที่เส้นตัดแกน y

สมการเส้นตรงมีรูปแบบ y=mx+b หากทราบค่าของ m และ b จุดใด ๆ บนเส้นสามารถคำนวณได้โดยการแทนค่าของ y หรือ x ลงในสมการ คุณยังสามารถใช้ฟังก์ชัน TREND

หากมีตัวแปรอิสระ x เพียงตัวเดียว คุณสามารถหาค่าความชันและค่าตัดแกน y ได้โดยตรงโดยใช้สูตรต่อไปนี้:

ความชัน: INDEX(LINEST(known_y's,known_x's), 1)

Y-intercept: INDEX(LINEST(known_y's,known_x's), 2)

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

โดยที่ x และ y เป็นค่าเฉลี่ยตัวอย่าง เช่น x = AVERAGE(known_x's) และ y = AVERAGE(known_y's)

ฟังก์ชันความพอดีของ LINEST และ LGRFPRIBL สามารถคำนวณเส้นโค้งแบบตรงหรือแบบเอ็กซ์โพเนนเชียลที่เหมาะกับข้อมูลได้ดีที่สุด อย่างไรก็ตาม พวกเขาไม่ได้ตอบคำถามว่าผลลัพธ์ใดในสองผลลัพธ์ที่เหมาะสมสำหรับการแก้ปัญหามากกว่ากัน คุณยังสามารถคำนวณฟังก์ชัน TREND(known_y-values; Know_x-values) สำหรับเส้นตรง หรือฟังก์ชัน GROWTH (known_y-values; Know_x-values) สำหรับเส้นโค้งเลขชี้กำลัง หากละเว้นฟังก์ชันเหล่านี้จากอาร์กิวเมนต์ new_x_values ​​ให้ส่งคืนอาร์เรย์ของค่า y ที่คำนวณแล้วสำหรับค่า x จริงตามเส้นตรงหรือเส้นโค้ง จากนั้นคุณสามารถเปรียบเทียบค่าที่คำนวณได้กับค่าจริง คุณยังสามารถสร้างแผนภูมิสำหรับการเปรียบเทียบภาพ

เมื่อทำการวิเคราะห์การถดถอย Microsoft Excel จะคำนวณกำลังสองของผลต่างระหว่างค่า y ที่ทำนายกับค่า y จริงสำหรับแต่ละจุด ผลรวมของผลต่างกำลังสองเหล่านี้เรียกว่าผลรวมที่เหลือของกำลังสอง (ssresid) จากนั้น Microsoft Excel จะคำนวณผลรวมของกำลังสองทั้งหมด (sstall) ถ้า const = TRUE หรือหากไม่ได้ระบุอาร์กิวเมนต์นี้ ผลรวมของกำลังสองจะเท่ากับผลรวมของผลต่างกำลังสองของค่า y จริงและค่า y เฉลี่ย ถ้า const = FALSE ผลรวมของกำลังสองจะเท่ากับผลรวมกำลังสองของค่า y จริง (โดยไม่ลบค่าเฉลี่ย y ออกจากผลหาร y) หลังจากนั้นสามารถคำนวณผลรวมการถดถอยของกำลังสองได้ดังนี้: ssreg = sstotal - ssresid ยิ่งผลรวมของกำลังสองที่เหลือน้อยลง ค่าสัมประสิทธิ์ของปัจจัยเชิงกำหนด r2 ก็จะยิ่งมากขึ้น ซึ่งบ่งชี้ว่าสมการที่ได้จากการวิเคราะห์การถดถอยอธิบายความสัมพันธ์ระหว่างตัวแปรได้ดีเพียงใด ค่าสัมประสิทธิ์ r2 เท่ากับ ssreg/sstotal

ในบางกรณี คอลัมน์ X อย่างน้อยหนึ่งคอลัมน์ (สมมติว่าค่า Y และ X อยู่ในคอลัมน์) ไม่มีค่าคาดการณ์เพิ่มเติมในคอลัมน์ X อื่น กล่าวอีกนัยหนึ่ง การลบคอลัมน์ X อย่างน้อยหนึ่งคอลัมน์อาจส่งผลให้มีค่า Y ได้ คำนวณด้วยความแม่นยำเท่ากัน ในกรณีนี้ คอลัมน์ X ที่ซ้ำซ้อนจะถูกแยกออกจากแบบจำลองการถดถอย ปรากฏการณ์นี้เรียกว่า "collinearity" เนื่องจากคอลัมน์ที่ซ้ำซ้อนของ X สามารถแสดงเป็นผลรวมของคอลัมน์ที่ไม่ซ้ำซ้อนหลายคอลัมน์ LINEST ตรวจสอบความสัมพันธ์เชิงเส้นและลบคอลัมน์ X ที่ซ้ำซ้อนออกจากแบบจำลองการถดถอยหากพบ คอลัมน์ X ที่ถูกลบออกสามารถระบุได้ในเอาต์พุต LINEST ด้วยปัจจัย 0 และค่า se เท่ากับ 0 การลบคอลัมน์อย่างน้อยหนึ่งคอลัมน์เนื่องจากซ้ำซ้อนจะเปลี่ยนค่าของ df เนื่องจากขึ้นอยู่กับจำนวนคอลัมน์ X ที่ใช้จริงเพื่อจุดประสงค์ในการทำนาย ดูตัวอย่างที่ 4 ด้านล่างสำหรับรายละเอียดเพิ่มเติมเกี่ยวกับการคำนวณ df เมื่อ df เปลี่ยนแปลงเนื่องจากการลบคอลัมน์ที่ซ้ำซ้อน ค่าของ sey และ F ก็จะเปลี่ยนไปด้วย มักไม่แนะนำให้ใช้ collinearity อย่างไรก็ตาม ควรใช้หากคอลัมน์ X บางคอลัมน์มี 0 หรือ 1 เป็นตัวบ่งชี้ว่าหัวข้อของการทดสอบอยู่ในกลุ่มแยกต่างหากหรือไม่ ถ้า const = TRUE หรือไม่ได้ระบุอาร์กิวเมนต์นี้ LINEST จะแทรกคอลัมน์ X เพิ่มเติมเพื่อจำลองจุดตัด หากมีคอลัมน์ที่มีค่า 1 สำหรับผู้ชายและ 0 สำหรับผู้หญิง และมีคอลัมน์ที่มีค่า 1 สำหรับผู้หญิงและ 0 สำหรับผู้ชาย คอลัมน์สุดท้ายจะถูกลบออกเนื่องจากสามารถรับค่าได้จาก คอลัมน์ "ตัวบ่งชี้ความเป็นชาย"

การคำนวณ df สำหรับกรณีที่คอลัมน์ X ไม่ได้ถูกลบออกจากแบบจำลองเนื่องจากความสอดคล้องกันจะเป็นดังนี้: หากมี k คอลัมน์ที่รู้จักและ const = TRUE หรือไม่ได้ระบุ ดังนั้น df = n - k - 1 ถ้า const = FALSE จากนั้น df = n - k ในทั้งสองกรณี การลบคอลัมน์ X เนื่องจาก collinearity จะเพิ่มค่าของ df ขึ้น 1

ต้องป้อนสูตรที่ส่งคืนอาร์เรย์เป็นสูตรอาร์เรย์

เมื่อป้อนอาร์เรย์ของค่าคงที่เป็นอาร์กิวเมนต์ที่รู้จักค่า x_values ​​ตัวอย่างเช่น ใช้เครื่องหมายอัฒภาคเพื่อแยกค่าในบรรทัดเดียวกัน และใช้เครื่องหมายทวิภาคเพื่อแยกบรรทัด อักขระตัวคั่นอาจแตกต่างกันไปขึ้นอยู่กับการตั้งค่าในหน้าต่าง "ภาษาและมาตรฐาน" ในแผงควบคุม

โปรดทราบว่าค่า y ที่ทำนายโดยสมการถดถอยอาจไม่ถูกต้องหากอยู่นอกช่วงของค่า y ที่ใช้ในการกำหนดสมการ

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

การทำงาน LINESTส่งกลับค่าเท่ากับ 0 อัลกอริทึมของฟังก์ชัน LINESTใช้เพื่อส่งคืนค่าที่เหมาะสมสำหรับข้อมูลเชิงเส้น ซึ่งในกรณีนี้สามารถหาคำตอบได้อย่างน้อยหนึ่งคำตอบ

ฟังก์ชัน SLOPE และ INTERCEPT ส่งกลับข้อผิดพลาด #DIV/0! อัลกอริทึมของฟังก์ชัน SLOPE และ INTERCEPT ใช้เพื่อหาคำตอบเพียงคำตอบเดียว และในกรณีนี้อาจมีหลายคำตอบ

นอกเหนือจากการคำนวณสถิติสำหรับการถดถอยประเภทอื่นๆ แล้ว LINEST ยังสามารถใช้คำนวณช่วงของการถดถอยประเภทอื่นๆ ได้ด้วยการป้อนฟังก์ชันของตัวแปร x และ y เป็นชุดของตัวแปร x และ y สำหรับ LINEST ตัวอย่างเช่น สูตรต่อไปนี้:

LINEST(ค่า y, ค่า x^COLUMN($A:$C))

ทำงานร่วมกับหนึ่งคอลัมน์ของค่า Y และหนึ่งคอลัมน์ของค่า X เพื่อคำนวณค่าประมาณลูกบาศก์ (พหุนามองศาที่ 3) ของรูปแบบต่อไปนี้:

y=m 1 x+m 2 x 2 +m 3 x 3 +b

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

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

ต้องใช้โปรแกรมเสริมในการทำงาน ชุดวิเคราะห์ซึ่งจะต้องเปิดใช้งานในรายการเมนู บริการ \ ส่วนเสริม

ใน Excel 2007 หากต้องการเปิดใช้งาน Analysis Pack ให้คลิกไปที่บล็อก ตัวเลือกของ Excelโดยคลิกปุ่มที่มุมซ้ายบน จากนั้นคลิก " ตัวเลือกของ Excel» ที่ด้านล่างของหน้าต่าง:

หากต้องการสร้างแบบจำลองการถดถอย ให้เลือกรายการ บริการ \ Data Analysis \ Regression. (ใน Excel 2007 โหมดนี้จะอยู่ใน ข้อมูล/การวิเคราะห์ข้อมูล/การถดถอย). กล่องโต้ตอบจะปรากฏขึ้นซึ่งจำเป็นต้องกรอก:

1) ช่วงอินพุต Y¾ มีลิงก์ไปยังเซลล์ที่มีค่าของแอตทริบิวต์ผลลัพธ์ ย. ค่าต้องอยู่ในคอลัมน์

2) ช่วงเวลาอินพุต X¾ มีลิงค์ไปยังเซลล์ที่มีค่าของปัจจัย ค่าต้องอยู่ในคอลัมน์

3) ลงชื่อ แท็กตั้งค่าว่าเซลล์แรกมีข้อความอธิบาย (ป้ายชื่อข้อมูล);

4) ระดับความน่าเชื่อถือ¾ คือระดับความเชื่อมั่น ซึ่งถือว่าเป็น 95% โดยค่าเริ่มต้น หากค่านี้ไม่เหมาะกับคุณ คุณต้องเปิดใช้งานคุณลักษณะนี้และป้อนค่าที่ต้องการ

5) ลงชื่อ ค่าคงที่เป็นศูนย์รวมอยู่ด้วยหากจำเป็นต้องสร้างสมการที่มีตัวแปรอิสระ ;

6) ตัวเลือกเอาต์พุตกำหนดตำแหน่งที่ควรวางผลลัพธ์ โหมดการสร้างเริ่มต้น แผ่นงานใหม่;

7) บล็อก ยังคงอยู่ช่วยให้คุณสามารถรวมผลลัพธ์ของสิ่งตกค้างและการสร้างกราฟได้

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

1. สถิติการถดถอย:

หลายรายการ รถูกกำหนดโดยสูตร ( ค่าสัมประสิทธิ์สหสัมพันธ์แบบเพียร์สัน);

ร (ค่าสัมประสิทธิ์การตัดสินใจ);

ทำให้เป็นมาตรฐาน ร- คำนวณโดยสูตร (ใช้สำหรับ การถดถอยพหุคูณ);

มาตรฐานบกพร่อง สคำนวณโดยสูตร ;

การสังเกต ¾ คือจำนวนข้อมูล น.

2. การวิเคราะห์ความแปรปรวน, เส้น การถดถอย:

พารามิเตอร์ ดีเอฟเท่ากับ ม(จำนวนชุดของตัวประกอบ x);

พารามิเตอร์ สสถูกกำหนดโดยสูตร ;

พารามิเตอร์ นางสาวถูกกำหนดโดยสูตร ;

สถิติ ฉถูกกำหนดโดยสูตร ;

ความสำคัญ ฉ. หากจำนวนผลลัพธ์เกิน แสดงว่ายอมรับสมมติฐาน (ไม่มีความสัมพันธ์เชิงเส้น) มิฉะนั้นจะยอมรับสมมติฐาน (มีความสัมพันธ์เชิงเส้น)

3. การวิเคราะห์ความแปรปรวน, เส้น ส่วนที่เหลือ:

พารามิเตอร์ ดีเอฟเท่ากับ ;

พารามิเตอร์ สสถูกกำหนดโดยสูตร ;

พารามิเตอร์ นางสาวถูกกำหนดโดยสูตร

4. การวิเคราะห์ความแปรปรวน, เส้น ทั้งหมดมีผลรวมของสองคอลัมน์แรก

5. การวิเคราะห์ความแปรปรวน, เส้น สี่แยกตัววายประกอบด้วยค่าสัมประสิทธิ์ ข้อผิดพลาดมาตรฐาน และ ที-สถิติ.

พี-value ¾ คือค่าของระดับนัยสำคัญที่สอดคล้องกับการคำนวณ ที- นักสถิติ กำหนดโดย STUDIST( ที-สถิติ; ). ถ้า พี-value เกิน ตัวแปรที่เกี่ยวข้องจะไม่มีนัยสำคัญทางสถิติและสามารถแยกออกจากแบบจำลองได้

ด้านล่าง 95%และ สูงสุด 95%¾ คือขอบเขตล่างและบนของช่วงความเชื่อมั่น 95 เปอร์เซ็นต์สำหรับค่าสัมประสิทธิ์ของสมการถดถอยเชิงเส้นเชิงทฤษฎี หากในการป้อนข้อมูลบล็อกค่า ระดับความเชื่อมั่นถูกปล่อยให้เป็นค่าเริ่มต้น จากนั้นสองคอลัมน์สุดท้ายจะซ้ำกับคอลัมน์ก่อนหน้า หากผู้ใช้ป้อนค่าความเชื่อมั่นที่กำหนดเอง สองคอลัมน์สุดท้ายจะมีค่าขอบเขตล่างและขอบเขตบนสำหรับระดับความเชื่อมั่นที่ระบุ

6. การวิเคราะห์ความแปรปรวนแถวมีค่าของสัมประสิทธิ์ ข้อผิดพลาดมาตรฐาน, ที-นักสถิติ พี- ค่าและช่วงความเชื่อมั่นสำหรับค่า .

7. บล็อก ถอนยอดคงเหลือมีค่าของการทำนาย ย(ในสัญกรณ์ของเรามันคือ ) และเศษ