การถดถอยใน Excel: สมการตัวอย่าง การถดถอยเชิงเส้น

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

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

แนวคิดนี้ได้รับการแนะนำให้รู้จักกับคณิตศาสตร์ โดยฟรานซิสแกลตัน ในปี 1886 การถดถอยคือ:

• เชิงเส้น
• พาราโบลา;
• อำนาจ
• ชี้แจง;
• ผ่อนชำระ;
• ชี้แจง;
• เกี่ยวกับลอการิทึม

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

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

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

		B	C
1	X	จำนวนการลาออก	เงินเดือน
2		Y	30000 รูเบิล
3	1	60	35000 รูเบิล
4	2	35	40000 รูเบิล
5	3	20	45000 รูเบิล
6	4	20	50,000 รูเบิล
7	5	15	55000 รูเบิล
8	6	15	60000 รูเบิล

สำหรับปัญหาของการกำหนดพึ่งพาอาศัยของแรงงานจำนวนเงินที่แยกจากเงินเดือนเฉลี่ยสำหรับรูปแบบการถดถอย 6 รัฐวิสาหกิจที่มีรูปแบบของสมการ y = ₀ + ₁ x ₁ + ... + A _k x _{k ที่} x _ฉัน - _{ตัวแปรที่มีอิทธิพลต่อการฉัน} - ค่าสัมประสิทธิ์การถดถอย AK - จำนวนของปัจจัย

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

การควบคุมอำนาจของ "Excel" สเปรดชีต

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

• กับแท็บ "แฟ้ม" ไปที่ "ตั้งค่า";
• ในหน้าต่างที่เปิดให้เลือก 'Add-ons';
• คลิกที่ปุ่ม "Go" ตั้งอยู่ที่ด้านขวาล่างของเส้น "การจัดการ";
• ใส่เครื่องหมายถัดจาก "ToolPak การวิเคราะห์" และยืนยันการกระทำของคุณโดยการกดปุ่ม "OK"

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

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

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

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

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

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

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

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

การวิเคราะห์อัตราส่วน

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

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

การถดถอยพหุคูณ

ภายใต้คำนี้หมายถึงสมการสื่อสารกับตัวแปรอิสระหลายรูปแบบ:

การ y = f (x ₁ + ₂ + x ... x _{เมตร)} + εที่ Y - เป็นคะแนนคุณลักษณะ (ตัวแปรตาม) และ x _1, x _2, ... x _ม - เป็นสัญญาณปัจจัย (ตัวแปรอิสระ)

ประมาณค่าพารามิเตอร์

สำหรับการถดถอยพหุคูณ ( ม.ร.ว. ) มันจะดำเนินการโดยใช้วิธีการอย่างน้อยสี่เหลี่ยม (LSM) สำหรับสมการเชิงเส้นของรูปแบบ Y = A + B ₁ x _{1 + ... + B เมตร x m +} ε สร้างระบบสมการปกติ (ซม. ด้านล่าง)

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

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

ที่σ - คือความแปรปรวนของคุณลักษณะตามลำดับสะท้อนให้เห็นในดัชนี

MNC ใช้ได้กับนายสมการ standartiziruemom ขนาด ในกรณีนี้เราได้รับสมการ:

ประเด็นที _Y, เสื้อ _{x 1, ...} เสื้อ _XM - standartiziruemye ตัวแปรที่ค่าเฉลี่ย 0; _{เบต้าฉัน} - ค่าสัมประสิทธิ์ถดถอยมาตรฐานและค่าเบี่ยงเบนมาตรฐาน - 1

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

ปัญหาเกี่ยวกับการใช้สมการถดถอยเชิงเส้น

สมมติว่าคุณมีตารางของการเปลี่ยนแปลงของราคาของสินค้า N โดยเฉพาะอย่างยิ่งสำหรับ 8 เดือนที่ผ่าน มันเป็นสิ่งจำเป็นที่จะตัดสินใจว่าการเข้าซื้อกิจการของพรรคของเขาในราคา 1,850 รูเบิลได้. / ตัน

		B	C
1	เดือน	ชื่อของเดือน	ราคา N
2	1	มกราคม	1750 รูเบิลต่อตัน
3	2	กุมภาพันธ์	1,755 รูเบิลต่อตัน
4	3	มีนาคม	1,767 รูเบิลต่อตัน
5	4	เมษายน	1,760 รูเบิลต่อตัน
6	5	พฤษภาคม	1,770 รูเบิลต่อตัน
7	6	มิถุนายน	1,790 รูเบิลต่อตัน
8	7	กรกฎาคม	1,810 รูเบิลต่อตัน
9	8	สิงหาคม	1840 รูเบิลต่อตัน

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

เรากำลังสร้างบนเขาสมการเชิงเส้นของรูปแบบการ y = ขวาน + B ที่เป็นพารามิเตอร์และ b เป็นค่าสัมประสิทธิ์จากหมายเลขบรรทัดของเดือนและชื่อของสัมประสิทธิ์และ Y สี่แยก" สาย«ของแผ่นกับผลของการวิเคราะห์การถดถอยที่ ดังนั้นสมการถดถอยเชิงเส้น (EQ) ที่ 3 สำหรับปัญหาที่เกิดขึ้นสามารถเขียนเป็น:

ราคาของสินค้า N = จำนวน 11,714 * 1,727.54 เดือน

หรือสัญกรณ์พีชคณิต

การ y = 11,714 x + 1727,54

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

ที่จะตัดสินใจว่าได้รับสมการถดถอยเชิงเส้นอย่างเพียงพอโดยใช้ค่าสัมประสิทธิ์สหสัมพันธ์หลาย (CMC) และความมุ่งมั่นเช่นเดียวกับการทดสอบและการฟิชเชอร์ t-test ในตาราง "Excel" ถดถอยกับผลลัพธ์ที่พวกเขาทำหน้าที่ภายใต้ชื่อหลาย R, R-สแควร์, F-T-สถิติและสถิติตามลำดับ

KMC R ช่วยให้การประเมินความน่าจะเป็นความสัมพันธ์ที่ใกล้ชิดระหว่างตัวแปรอิสระและตัวแปร ที่มีมูลค่าสูงของมันแสดงให้เห็นการเชื่อมต่อที่แข็งแรงพอที่ระหว่างตัวแปร "จำนวนของเดือน" และ "N ราคาสินค้าในรูเบิลต่อ 1 ตัน." แต่ธรรมชาติของความสัมพันธ์นี้ไม่เป็นที่รู้จัก

ตารางของค่าสัมประสิทธิ์การตัดสินใจ R ² (RI) เป็นลักษณะตัวเลขของสัดส่วนของการกระจายรวมและแสดงให้เห็นถึงกระจายส่วนข้อมูลการทดลองที่นั่นคือ ค่าของตัวแปรที่สอดคล้องกับสมการถดถอยเชิงเส้น ในปัญหานี้ค่านี้เป็น 84.8%, MP. สถิติอีที่มีระดับสูงของความถูกต้องได้อธิบายไว้ใน SD

F-สถิติยังเป็นที่รู้จักฟิชเชอร์เกณฑ์ที่ใช้ในการประเมินความสำคัญของการพึ่งพาอาศัยเส้นหรือหักล้างสมมติฐานที่ยืนยันการดำรงอยู่ของมัน

มูลค่าของเสื้อสถิติ (ทดสอบเสื้อนักศึกษา) จะช่วยให้การประเมินความสำคัญของค่าสัมประสิทธิ์ที่สมาชิกพึ่งพาใด ๆ ที่ไม่รู้จักฟรีเชิงเส้น ถ้าค่าของ t-test> เสื้อ _{CR ที่สมมติฐานของเล็กน้อยสมการเชิงเส้นของระยะฟรีจะถูกปฏิเสธ}

ในปัญหานี้เป็นระยะฟรีผ่านเครื่องมือ "Excel" มันก็พบว่า t = 169,20903 และ p = 2,89E-12 t. อีมีความน่าจะเป็นศูนย์ที่มีจิตศรัทธาจะถูกปฏิเสธสมมติฐานของการไม่มีความหมายของคำเสรี สำหรับค่าสัมประสิทธิ์ที่ไม่รู้จักที่ t = 5,79405 และ p = 0,001158 ในคำอื่น ๆ น่าจะเป็นที่ปฏิเสธสมมติฐานที่ถูกต้องจะสำคัญลงของค่าสัมประสิทธิ์สำหรับที่ไม่รู้จักเป็น 0.12%

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

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

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

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

• เจ้าหนี้ (VK);
• ปริมาณการหมุนเวียนประจำปี (VO);
• ลูกหนี้ (VD);
• มูลค่าของสินทรัพย์ถาวร (SOF)

นอกจากนี้ยังใช้หนี้ค่าจ้างของผู้ประกอบการ (V3 U) ในหลายพันดอลลาร์สหรัฐ

หน่วยประมวลผลตารางการตัดสินใจหมายถึง Excel

ครั้งแรกที่คุณจะต้องสร้างตารางข้อมูลใส่ มันจะเป็นดังนี้:

ถัดไป:

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

มาร์คจุด "แผ่นใหม่" และคลิก "OK"

ได้รับการวิเคราะห์การถดถอยสำหรับงานนี้

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

"เก็บ" กลมจากข้อมูลที่นำเสนอข้างต้นบนโต๊ะแผ่นสม Excel ประมวลผลการถดถอย:

SD = 0.103 * SOF + 0541 * VO - 0031 * + VK 0405 * VD + 0691 * VZP - 265844

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

การ y = 0103 * x1 + x2 0541 * - * 0031 x3 + 0405 * x4 + 0691 * x5 - 265844

ข้อมูลสำหรับ«MMM» JSC นำเสนอในตารางด้านล่าง:

SOF, USD	VO, USD	VK, USD	VD, USD	VZP, USD	JV, USD
102.5	535.5	45.2	41.5	21.55	64.72

แทนพวกเขาเข้าไปในสมการถดถอยที่ได้รับร่างของ 64,720,000 ดอลลาร์สหรัฐ ซึ่งหมายความว่าหุ้นของ JSC «MMM»ไม่ควรซื้อเพราะค่าใช้จ่ายของพวกเขาจะค่อนข้างเกินราคาที่ 70 ล้านดอลลาร์สหรัฐ

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

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