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

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

มุมมองทั่วไปของแบบจำลองการถดถอยเชิงเส้น:

Y=a 0 +a 1 x 1 +...+a k x k

ที่ไหน ก - พารามิเตอร์การถดถอย (สัมประสิทธิ์) x - ปัจจัยที่มีอิทธิพล เค - จำนวนปัจจัยโมเดล

ข้อมูลเบื้องต้น

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

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

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

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

แพ็คเกจการวิเคราะห์

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

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

เปิดใช้งานแพ็คเกจการวิเคราะห์

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

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

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

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

คำแนะนำในการค้นหาพารามิเตอร์การถดถอยเชิงเส้นโดยใช้แพ็คเกจการวิเคราะห์

หลังจากเปิดใช้งาน Add-on Analysis Pack แล้ว Add-on จะพร้อมใช้งานในแท็บเมนูหลักเสมอ ข้อมูลใต้ลิงค์ การวิเคราะห์ข้อมูล

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

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

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

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

ดังนั้น, 0,865 - นี้ ร 2- ค่าสัมประสิทธิ์การกำหนด แสดงให้เห็นว่า 86.5% ของพารามิเตอร์ที่คำนวณของแบบจำลอง นั่นคือ ตัวแบบจำลองเอง อธิบายการพึ่งพาและการเปลี่ยนแปลงของพารามิเตอร์ที่กำลังศึกษา - ยจากปัจจัยที่ศึกษา - เอ็กซ์- ถ้าเกินจริงแล้ว นี่เป็นตัวบ่งชี้คุณภาพของโมเดลและยิ่งสูงก็ยิ่งดี เป็นที่ชัดเจนว่าต้องไม่เกิน 1 และถือว่าดีเมื่อ R 2 สูงกว่า 0.8 และหากน้อยกว่า 0.5 ก็สามารถตั้งคำถามถึงความสมเหตุสมผลของแบบจำลองดังกล่าวได้อย่างปลอดภัย

ตอนนี้เรามาดูกันดีกว่า ค่าสัมประสิทธิ์แบบจำลอง:
2079,85 - นี้ 0- ค่าสัมประสิทธิ์ที่แสดงให้เห็นว่า Y จะเป็นอย่างไรหากปัจจัยทั้งหมดที่ใช้ในแบบจำลองเท่ากับ 0 เป็นที่เข้าใจกันว่านี่เป็นการพึ่งพาปัจจัยอื่น ๆ ที่ไม่ได้อธิบายไว้ในแบบจำลอง
-0,0056 - 1- ค่าสัมประสิทธิ์ที่แสดงน้ำหนักของอิทธิพลของปัจจัย x 1 ต่อ Y นั่นคือจำนวนวิสาหกิจภายในแบบจำลองที่กำหนดส่งผลต่อตัวบ่งชี้ประชากรที่มีความกระตือรือร้นเชิงเศรษฐกิจโดยมีน้ำหนักเพียง -0.0056 (อิทธิพลค่อนข้างน้อย ). เครื่องหมายลบแสดงให้เห็นว่าอิทธิพลนี้เป็นเชิงลบ กล่าวคือ ยิ่งมีวิสาหกิจมากขึ้น ประชากรที่มีความกระตือรือร้นทางเศรษฐกิจก็จะน้อยลง ไม่ว่าความหมายจะขัดแย้งกันเพียงใดก็ตาม
-0,0026 - 2- สัมประสิทธิ์อิทธิพลของปริมาณการลงทุนต่อขนาดของประชากรเชิงเศรษฐกิจ ตามแบบจำลองอิทธิพลนี้ก็เป็นผลลบเช่นกัน
0,0028 - 3- สัมประสิทธิ์อิทธิพลของรายได้ประชากรต่อขนาดของประชากรเชิงเศรษฐกิจ ที่นี่อิทธิพลเป็นบวกนั่นคือตามแบบจำลอง การเพิ่มขึ้นของรายได้จะส่งผลให้ขนาดของประชากรเชิงเศรษฐกิจเพิ่มขึ้น

มารวบรวมค่าสัมประสิทธิ์ที่คำนวณได้ลงในแบบจำลอง:

Y = 2079.85 - 0.0056x 1 - 0.0026x 2 + 0.0028x 3

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

การประมาณค่าแบบจำลองและการพยากรณ์

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

ค่าจริง (ที่เกิดขึ้นในความเป็นจริง) และค่าที่คำนวณตามแบบจำลองในรูปเดียวกันจะแสดงในรูปแบบของกราฟเพื่อแสดงความแตกต่างและดังนั้นจึงเป็นข้อผิดพลาดของแบบจำลอง

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

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

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

28 ต.ค

สวัสดีตอนบ่ายผู้อ่านบล็อกที่รัก! วันนี้เราจะพูดถึงการถดถอยแบบไม่เชิงเส้น สามารถดูวิธีแก้ปัญหาการถดถอยเชิงเส้นได้ที่ LINK

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

ประเภทหลักของการถดถอยแบบไม่เชิงเส้นคือ:

• พหุนาม (กำลังสอง, ลูกบาศก์);
• ซึ่งเกินความจริง;
• สงบ;
• สาธิต;
• ลอการิทึม

สามารถใช้ชุดค่าผสมต่างๆ ได้ ตัวอย่างเช่น สำหรับการวิเคราะห์อนุกรมเวลาในการศึกษาการธนาคาร การประกันภัย และประชากรศาสตร์ จะใช้เส้นโค้ง Gompzer ซึ่งเป็นการถดถอยแบบลอการิทึมประเภทหนึ่ง

ในการพยากรณ์โดยใช้การถดถอยแบบไม่เชิงเส้น สิ่งสำคัญคือการหาค่าสัมประสิทธิ์สหสัมพันธ์ ซึ่งจะแสดงให้เราเห็นว่ามีความสัมพันธ์ใกล้ชิดระหว่างพารามิเตอร์สองตัวหรือไม่ ตามกฎแล้ว หากค่าสัมประสิทธิ์สหสัมพันธ์ใกล้กับ 1 แสดงว่ามีความเชื่อมโยงกัน และการพยากรณ์จะค่อนข้างแม่นยำ องค์ประกอบที่สำคัญอีกประการหนึ่งของการถดถอยแบบไม่เชิงเส้นคือข้อผิดพลาดสัมพัทธ์โดยเฉลี่ย ( ก ) หากอยู่ในช่วง<8…10%, значит модель достаточно точна.

นี่คือจุดที่เราอาจจะต้องจบบล็อกทางทฤษฎีและไปสู่การคำนวณเชิงปฏิบัติ

เรามีตารางยอดขายรถยนต์ในช่วง 15 ปี (แสดงว่าเป็น X) จำนวนขั้นตอนการวัดจะเป็นอาร์กิวเมนต์ n เรายังมีรายได้สำหรับช่วงเวลาเหล่านี้ด้วย (แสดงว่าเป็น Y) เราต้องคาดการณ์ว่าอะไร รายได้จะอยู่ในอนาคต มาสร้างตารางต่อไปนี้:

ในการศึกษา เราจะต้องแก้สมการ (การพึ่งพา Y บน X): y=ax 2 +bx+c+e นี่คือการถดถอยกำลังสองแบบคู่ ในกรณีนี้ เราใช้วิธีกำลังสองน้อยที่สุดเพื่อค้นหาอาร์กิวเมนต์ที่ไม่รู้จัก - a, b, c มันจะนำไปสู่ระบบสมการพีชคณิตในรูปแบบ:

เพื่อแก้ปัญหาระบบนี้ เราจะใช้ เช่น วิธีของแครเมอร์ เราเห็นว่าผลรวมที่รวมอยู่ในระบบเป็นค่าสัมประสิทธิ์ของสิ่งที่ไม่ทราบ ในการคำนวณ เราจะเพิ่มหลายคอลัมน์ลงในตาราง (D,E,F,G,H) และลงชื่อตามความหมายของการคำนวณ - ในคอลัมน์ D เราจะยกกำลังสอง x ใน E เราจะยกกำลังสาม ใน F เราจะคูณเลขยกกำลัง x และ y ใน H เราจะยกกำลัง x แล้วคูณด้วย y

คุณจะได้ตารางแบบฟอร์มที่เต็มไปด้วยสิ่งที่จำเป็นในการแก้สมการ

ลองสร้างเมทริกซ์กัน ก ระบบประกอบด้วยค่าสัมประสิทธิ์ของสิ่งที่ไม่ทราบทางด้านซ้ายของสมการ มาวางไว้ในเซลล์ A22 แล้วเรียกมันว่า " ก=- เราทำตามระบบสมการที่เราเลือกแก้การถดถอย

นั่นคือในเซลล์ B21 เราต้องวางผลรวมของคอลัมน์ที่เรายกตัวบ่งชี้ X ให้เป็นกำลังที่สี่ - F17 มาดูเซลล์ - “=F17” ต่อไปเราต้องการผลรวมของคอลัมน์ที่มีกำลังสามของ X - E17 จากนั้นเราจะดำเนินการตามระบบอย่างเคร่งครัด ดังนั้นเราจะต้องกรอกเมทริกซ์ทั้งหมด

ตามอัลกอริทึมของแครมเมอร์ เราจะพิมพ์เมทริกซ์ A1 ซึ่งคล้ายกับ A ซึ่งแทนที่จะวางองค์ประกอบของคอลัมน์แรก ควรวางองค์ประกอบของด้านขวาของสมการระบบ นั่นคือ ผลรวมของคอลัมน์ X กำลังสองคูณด้วย Y ผลรวมของคอลัมน์ XY และผลรวมของคอลัมน์ Y

นอกจากนี้เรายังต้องการเมทริกซ์อีกสองตัว เรียกเมทริกซ์เหล่านี้ว่า A2 และ A3 ซึ่งคอลัมน์ที่สองและสามจะประกอบด้วยค่าสัมประสิทธิ์ทางด้านขวามือของสมการ ภาพก็จะประมาณนี้

ตามอัลกอริทึมที่เลือก เราจะต้องคำนวณค่าของดีเทอร์มิแนนต์ (ดีเทอร์มิแนนต์, D) ของเมทริกซ์ผลลัพธ์ ลองใช้สูตร MOPRED กัน เราจะวางผลลัพธ์ไว้ในเซลล์ J21:K24

เราจะคำนวณค่าสัมประสิทธิ์ของสมการตาม Cramer ในเซลล์ที่อยู่ตรงข้ามกับปัจจัยที่เกี่ยวข้องโดยใช้สูตร: ก(ในเซลล์ M22) - “=K22/K21”; ข(ในเซลล์ M23) - “=K23/K21”; กับ(ในเซลล์ M24) - “=K24/K21”

เราได้สมการการถดถอยกำลังสองคู่ที่ต้องการ:

y=-0.074x 2 +2.151x+6.523

ให้เราประเมินความใกล้ชิดของความสัมพันธ์เชิงเส้นโดยใช้ดัชนีสหสัมพันธ์

ในการคำนวณ ให้เพิ่มคอลัมน์ J เพิ่มเติมลงในตาราง (เรียกว่า y*) การคำนวณจะเป็นดังนี้ (ตามสมการถดถอยที่เราได้รับ) - “=$m$22*B2*B2+$M$23*B2+$M$24”ลองวางไว้ในเซลล์ J2 สิ่งที่เหลืออยู่คือการลากเครื่องหมายป้อนอัตโนมัติลงไปที่เซลล์ J16

ในการคำนวณผลรวม (ค่าเฉลี่ย Y-Y) 2 ให้เพิ่มคอลัมน์ K และ L ลงในตารางด้วยสูตรที่เกี่ยวข้อง เราคำนวณค่าเฉลี่ยสำหรับคอลัมน์ Y โดยใช้ฟังก์ชัน AVERAGE

ในเซลล์ K25 เราจะวางสูตรสำหรับคำนวณดัชนีสหสัมพันธ์ - “=ROOT(1-(K17/L17))”

เราจะเห็นว่าค่า 0.959 ใกล้เคียงกับ 1 มาก ซึ่งหมายความว่ามีความสัมพันธ์แบบไม่เชิงเส้นที่ใกล้ชิดระหว่างยอดขายและปี

ยังคงประเมินคุณภาพของความพอดีของสมการถดถอยกำลังสองที่เกิดขึ้น (ดัชนีการกำหนด) คำนวณโดยใช้สูตรสำหรับดัชนีความสัมพันธ์กำลังสอง นั่นคือสูตรในเซลล์ K26 จะง่ายมาก - “=K25*K25”

ค่าสัมประสิทธิ์ 0.920 อยู่ใกล้กับ 1 แสดงถึงคุณภาพความพอดีที่สูง

ขั้นตอนสุดท้ายคือการคำนวณข้อผิดพลาดสัมพัทธ์ มาเพิ่มคอลัมน์แล้วป้อนสูตรที่นั่น: “=ABS((C2-J2)/C2), ABS - โมดูล, ค่าสัมบูรณ์ วาดเครื่องหมายลงและในเซลล์ M18 จะแสดงค่าเฉลี่ย (AVERAGE) กำหนดรูปแบบเปอร์เซ็นต์ให้กับเซลล์ ผลลัพธ์ที่ได้ - 7.79% อยู่ภายในค่าความผิดพลาดที่ยอมรับได้<8…10%. Значит вычисления достаточно точны.

หากจำเป็น เราสามารถสร้างกราฟโดยใช้ค่าที่ได้รับ

มีการแนบไฟล์ตัวอย่าง - LINK!

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

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

การวิเคราะห์การถดถอยใน Excel

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

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

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

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

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

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

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

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

การวิเคราะห์ความสัมพันธ์ใน Excel

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

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

ค่าสัมประสิทธิ์สหสัมพันธ์แสดงโดย r แตกต่างกันไปตั้งแต่ +1 ถึง -1 การจำแนกความสัมพันธ์ในแต่ละด้านจะแตกต่างกัน เมื่อค่าสัมประสิทธิ์เป็น 0 จะไม่มีความสัมพันธ์เชิงเส้นระหว่างตัวอย่าง

มาดูวิธีค้นหาค่าสัมประสิทธิ์สหสัมพันธ์โดยใช้ Excel

หากต้องการค้นหาค่าสัมประสิทธิ์ที่จับคู่ จะใช้ฟังก์ชัน CORREL

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

วางเคอร์เซอร์ในเซลล์ใดก็ได้แล้วกดปุ่ม fx

1. ในหมวดหมู่ "สถิติ" ให้เลือกฟังก์ชัน CORREL
2. อาร์กิวเมนต์ “อาร์เรย์ 1” - ช่วงแรกของค่า – เวลาการทำงานของเครื่อง: A2:A14
3. อาร์กิวเมนต์ "อาร์เรย์ 2" - ค่าช่วงที่สอง – ค่าซ่อม: B2:B14 คลิกตกลง

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

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

ค่าสัมประสิทธิ์ผลลัพธ์จะแสดงในเมทริกซ์สหสัมพันธ์ แบบนี้:

การวิเคราะห์สหสัมพันธ์และการถดถอย

ในทางปฏิบัติทั้งสองเทคนิคนี้มักใช้ร่วมกัน

ตัวอย่าง:

ขณะนี้ข้อมูลการวิเคราะห์การถดถอยปรากฏให้เห็นแล้ว

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

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

• 0.8 – 1 – รุ่นคุณภาพดี
• 0.5 – 0.8 – แบบจำลองคุณภาพที่ยอมรับได้
• 0 – 0.5 – โมเดลคุณภาพต่ำ

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

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

วิธีที่ 1: คำนวณค่าสัมประสิทธิ์การกำหนดฟังก์ชันเชิงเส้น

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

วิธีที่ 2: คำนวณค่าสัมประสิทธิ์การกำหนดในฟังก์ชันไม่เชิงเส้น

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

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



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



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



4. ชุดเครื่องมือ "การวิเคราะห์ข้อมูล"ในอินสแตนซ์ปัจจุบันของ Excel จะถูกเปิดใช้งาน การเข้าถึงจะอยู่ที่ Ribbon ในแท็บ "ข้อมูล"- ย้ายไปที่แท็บที่ระบุแล้วคลิกที่ปุ่ม "การวิเคราะห์ข้อมูล"ในกลุ่มการตั้งค่า "การวิเคราะห์".



5. หน้าต่างถูกเปิดใช้งาน "การวิเคราะห์ข้อมูล"พร้อมรายการเครื่องมือประมวลผลข้อมูลเฉพาะทาง เราเลือกจากรายการนี้ "การถดถอย"และคลิกที่ปุ่ม "ตกลง".



6. จากนั้นหน้าต่างเครื่องมือจะเปิดขึ้น "การถดถอย"- บล็อกแรกของการตั้งค่า – “ป้อนข้อมูล”- ที่นี่ในสองฟิลด์คุณจะต้องระบุที่อยู่ของช่วงที่มีค่าของอาร์กิวเมนต์และฟังก์ชันอยู่ วางเคอร์เซอร์ในช่อง "ช่วงอินพุต Y"และเน้นเนื้อหาของคอลัมน์บนแผ่นงาน "ย"- หลังจากที่ที่อยู่อาร์เรย์ปรากฏในหน้าต่าง "การถดถอย"ให้วางเคอร์เซอร์ในช่อง "ช่วงอินพุต Y"และเลือกเซลล์ของคอลัมน์ด้วยวิธีเดียวกันทุกประการ "เอ็กซ์".

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

   ในกลุ่ม “ตัวเลือกเอาท์พุต”คุณต้องระบุพื้นที่ที่จะแสดงผลการคำนวณ มีสามตัวเลือก:

   • พื้นที่บนแผ่นงานปัจจุบัน
   • อีกแผ่น;
   • เล่มอื่น(ไฟล์ใหม่)

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

   กลุ่มพารามิเตอร์ "ยังคงอยู่"และ “ความน่าจะเป็นปกติ”เราเพิกเฉยต่อพวกเขา เนื่องจากสิ่งเหล่านี้ไม่สำคัญสำหรับการแก้ไขงานที่ทำอยู่ หลังจากนั้นคลิกที่ปุ่ม "ตกลง"ซึ่งอยู่ที่มุมขวาบนของหน้าต่าง "การถดถอย".



7. โปรแกรมทำการคำนวณตามข้อมูลที่ป้อนก่อนหน้านี้และแสดงผลในช่วงที่ระบุ อย่างที่คุณเห็น เครื่องมือนี้แสดงผลลัพธ์จำนวนมากพอสมควรสำหรับพารามิเตอร์ต่างๆ บนชีต แต่ในบริบทของบทเรียนปัจจุบัน เราสนใจตัวบ่งชี้นี้ "R-กำลังสอง"- ในกรณีนี้จะเท่ากับ 0.947664 ซึ่งบ่งบอกลักษณะของรุ่นที่เลือกว่าเป็นรุ่นคุณภาพดี

วิธีที่ 3: ค่าสัมประสิทธิ์การกำหนดเส้นแนวโน้ม

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

1. เรามีกราฟตามตารางอาร์กิวเมนต์และค่าของฟังก์ชันที่ใช้สำหรับตัวอย่างก่อนหน้านี้ เรามาสร้างเส้นแนวโน้มให้มันกันดีกว่า คลิกที่ใดก็ได้ในพื้นที่การลงจุดซึ่งมีแผนภูมิอยู่ด้วยปุ่มซ้ายของเมาส์ ในเวลาเดียวกัน ชุดแท็บเพิ่มเติมจะปรากฏบน Ribbon - "การทำงานกับไดอะแกรม"- ไปที่แท็บ "เค้าโครง"- คลิกที่ปุ่ม "เส้นแนวโน้ม"ซึ่งอยู่ในบล็อกเครื่องมือ "การวิเคราะห์"- เมนูจะปรากฏขึ้นพร้อมตัวเลือกประเภทเส้นแนวโน้ม เราเลือกประเภทให้เหมาะสมกับงานเฉพาะ มาเลือกตัวเลือกสำหรับตัวอย่างของเรากัน “การประมาณค่าเอ็กซ์โปเนนเชียล”.



2. Excel วาดเส้นแนวโน้มในรูปแบบของเส้นโค้งสีดำเพิ่มเติมโดยตรงบนระนาบการลงจุด



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

   

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



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



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



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



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



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

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

   

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