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

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

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

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

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

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

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

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

สำหรับปัญหาในการพิจารณาการพึ่งพาจำนวนแรงงานขาออกของเงินเดือนเฉลี่ยที่ 6 องค์กรรูปแบบการถดถอยมีสมการ Y = a₀ + a₁x₁ + ... + a_kx_kโดยที่ x_ผม - ตัวแปรที่มีอิทธิพลต่อ_ผม - สัมประสิทธิ์การถดถอย, a - จำนวนของปัจจัย

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

การใช้คุณสมบัติของตัวประมวลผลตาราง "Excel"

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

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

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

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

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

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

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

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

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

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

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

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

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

การถดถอยหลายครั้ง

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

y = f (x₁+ x₂+ … X_ม.) + εโดยที่ y คือคุณสมบัติที่มีประสิทธิภาพ (ตัวแปรตาม) และ x₁, x₂, ... x_ม. - สิ่งเหล่านี้เป็นสัญญาณปัจจัย (ตัวแปรอิสระ)

การประเมินผลพารามิเตอร์

สำหรับการถดถอยหลายครั้ง (MR) จะดำเนินการโดยใช้วิธีกำลังสองน้อยที่สุด (OLS) สำหรับสมการเชิงเส้นของรูปแบบ Y = a + b₁x₁ + ... + b_ม.x_ม.+ εสร้างระบบสมการปกติ (ดูด้านล่าง)

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

จากที่นี่เราจะได้รับ:

โดยที่σคือความแปรปรวนของคุณลักษณะที่สอดคล้องกันซึ่งแสดงในดัชนี

OLS สามารถใช้ได้กับสมการ MR ในระดับมาตรฐาน ในกรณีนี้เราจะได้สมการ:

ในที่_Yt_{x1, ...}เสื้อ_XM - ตัวแปรมาตรฐานที่ค่าเฉลี่ยคือ 0 β_ผม - สัมประสิทธิ์การถดถอยมาตรฐานและค่าเบี่ยงเบนมาตรฐาน - 1

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

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

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

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

เราสร้างสมการเชิงเส้นให้กับพวกเขาในรูปแบบ y = ax + b โดยที่พารามิเตอร์ a และ b คือค่าสัมประสิทธิ์ของแถวที่มีชื่อของหมายเลขเดือนและค่าสัมประสิทธิ์และเส้น“ Y-intersection” จากแผ่นงานที่มีผลการวิเคราะห์การถดถอย ดังนั้นสมการการถดถอยเชิงเส้น (SD) สำหรับปัญหา 3 จึงถูกเขียนในรูปแบบ:

ราคาของผลิตภัณฑ์คือ N = 11.714 * หมายเลขเดือน + 1727.54

หรือในเชิงพีชคณิต

y = 11.714 x + 1727.54

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

เพื่อตัดสินใจว่าสมการที่เกิดขึ้นนั้นเพียงพอหรือไม่การถดถอยเชิงเส้น, ค่าสัมประสิทธิ์ของความสัมพันธ์หลาย (KMK) และความมุ่งมั่นจะถูกนำมาใช้เช่นเดียวกับเกณฑ์ฟิชเชอร์และเกณฑ์ของนักเรียน ในตาราง Excel ที่มีผลลัพธ์การถดถอยจะปรากฏภายใต้ชื่อ R, R-squared, F-statistics และ t-statistics ตามลำดับ

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

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

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

ค่าของสถิติ t (เกณฑ์นักเรียน) ช่วยในการประเมินความสำคัญของสัมประสิทธิ์กับการพึ่งพาเชิงเส้นระยะที่ไม่รู้จัก หากค่าของ t-test> t_CRสมมติฐานของความไม่มีนัยสำคัญของเทอมอิสระของสมการเชิงเส้นจะถูกปฏิเสธ

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

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

ปัญหาของการแนะนำการซื้อหุ้น

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

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

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

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

โซลูชันโดยใช้เครื่องมือสเปรดชีต Excel

ก่อนอื่นจำเป็นต้องสร้างตารางของแหล่งข้อมูล มันมีแบบฟอร์มต่อไปนี้:

ถัดไป:

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

ทำเครื่องหมายรายการ“ แผ่นงานใหม่” และคลิก“ ตกลง”

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

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

"เรารวบรวม" จากข้อมูลที่ถูกปัดเศษที่แสดงด้านบนบนแผ่นงานของตัวประมวลผลสเปรดชีต Excel ซึ่งเป็นสมการการถดถอย

SP = 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 นำเสนอในตาราง:

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

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

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