แสดงอิทธิพลของค่าบางค่า (อิสระ, อิสระ) ต่อตัวแปรตาม ตัวอย่างเช่น จำนวนประชากรที่ทำงานเชิงเศรษฐกิจขึ้นอยู่กับจำนวนวิสาหกิจ ค่าจ้าง และพารามิเตอร์อื่นๆ อย่างไร หรือ: การลงทุนในต่างประเทศ ราคาพลังงาน ฯลฯ ส่งผลต่อระดับ 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 = a 0 + a 1 x 1 +…+akxk

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

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

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

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

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

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

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

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

ทีนี้มาวิเคราะห์การถดถอยกันดีกว่า

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

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

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

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

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

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

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

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

ŷ คือค่าคาดหวังของ y ที่ ตั้งค่าเอ็กซ์,

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.5х =2 +0.5(2) =3.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• พาราโบลา;
• สงบ;
• ลอการิทึม;
• เลขชี้กำลัง;
• สาธิต;
• ซึ่งเกินความจริง;
• การถดถอยเชิงเส้น

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

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

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

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

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

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

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

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

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

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

แพ็คเกจ MS Excel ช่วยให้คุณทำงานส่วนใหญ่ได้อย่างรวดเร็วเมื่อสร้างสมการการถดถอยเชิงเส้น สิ่งสำคัญคือต้องเข้าใจวิธีตีความผลลัพธ์ที่ได้รับ เมื่อต้องการสร้างแบบจำลองการถดถอย คุณต้องเลือก Tools\Data Analysis\Regression (ใน Excel 2007 โหมดนี้จะอยู่ในบล็อก Data/Data Analysis/Regression) จากนั้นคัดลอกผลลัพธ์ลงในบล็อกเพื่อการวิเคราะห์

ข้อมูลเริ่มต้น:

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

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

การตรวจสอบคุณภาพของสมการถดถอยที่สร้างขึ้น
ความสำคัญของสัมประสิทธิ์การถดถอย b i (สถิติ t การทดสอบของนักเรียน)
ความสำคัญของสมการโดยรวม (สถิติ F การทดสอบฟิชเชอร์) ค่าสัมประสิทธิ์การตัดสินใจ
การทดสอบ F บางส่วน

ระดับความสำคัญ 0.005 0.01 0.025 0.05 0.1 0.25 0.4

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

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

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

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

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

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

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

• พาราโบลา;
• สงบ;
• ลอการิทึม;
• เลขชี้กำลัง;
• สาธิต;
• ซึ่งเกินความจริง;
• การถดถอยเชิงเส้น

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

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

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

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

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

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

หนึ่งในตัวชี้วัดหลักคือ R-squared มันบ่งบอกถึงคุณภาพของแบบจำลอง ในกรณีของเรา ค่าสัมประสิทธิ์นี้คือ 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.5х =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 +…+аkxk โดยที่ хi เป็นตัวแปรที่มีอิทธิพล ai คือสัมประสิทธิ์การถดถอย และ k คือจำนวนปัจจัย

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

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

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

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

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

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

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

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

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

การวิเคราะห์ผลลัพธ์การถดถอยของ R-squared

ใน ข้อมูลเอ็กเซลที่ได้รับระหว่างการประมวลผลข้อมูลของตัวอย่างที่อยู่ระหว่างการพิจารณามีแบบฟอร์ม:

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

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

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

ปัญหาความเป็นไปได้ในการซื้อบล็อกหุ้น

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

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

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

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

โซลูชันที่ใช้ตัวประมวลผลสเปรดชีต Excel

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

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

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

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

การศึกษาผลลัพธ์และข้อสรุป

เรา "รวบรวม" จากข้อมูลแบบโค้งมนที่แสดงด้านบนบนแผ่นตาราง โปรเซสเซอร์เอ็กเซล, สมการถดถอย:

เอสพี = 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

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

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

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

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