การถดถอยเชิงเส้นในตัวอย่าง Excel เศรษฐมิติ. การถดถอยเชิงเส้นใน MS Excel

10.07.2019

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

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

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

ดาวน์โหลดบันทึกในรูปแบบหรือตัวอย่างในรูปแบบ

ประเภทของตัวแบบการถดถอย

ที่ไหน ρ 1 – สัมประสิทธิ์ความสัมพันธ์อัตโนมัติ ถ้า ρ 1 = 0 (ไม่มีความสัมพันธ์อัตโนมัติ) ดีµ2 2; ถ้า ρ 1 µ 1 (ความสัมพันธ์อัตโนมัติเชิงบวก) ดีอยู่ที่ 0; ถ้า ρ 1 = -1 (ความสัมพันธ์อัตโนมัติเชิงลบ) ดี ≈ 4.

ในทางปฏิบัติ การประยุกต์ใช้เกณฑ์ Durbin-Watson จะขึ้นอยู่กับการเปรียบเทียบค่า ดีด้วยคุณค่าทางทฤษฎีเชิงวิพากษ์ ดี แอลและ ดี ยูสำหรับการสังเกตจำนวนหนึ่ง n, จำนวนตัวแปรอิสระของแบบจำลอง เค(สำหรับการถดถอยเชิงเส้นอย่างง่าย เค= 1) และระดับนัยสำคัญ α ถ้า ดี< d L สมมติฐานเกี่ยวกับความเป็นอิสระของการเบี่ยงเบนแบบสุ่มถูกปฏิเสธ (ดังนั้นจึงมีความสัมพันธ์อัตโนมัติเชิงบวก) ถ้า D>dUสมมติฐานไม่ถูกปฏิเสธ (นั่นคือ ไม่มีความสัมพันธ์อัตโนมัติ) ถ้า ดี แอล< D < d U ไม่มีเหตุเพียงพอในการตัดสินใจ เมื่อคำนวณค่าแล้ว ดีเกิน 2 แล้วด้วย ดี แอลและ ดี ยูไม่ใช่ค่าสัมประสิทธิ์ที่จะเปรียบเทียบ ดีและนิพจน์ (4 – ดี).

ในการคำนวณสถิติ Durbin-Watson ใน Excel ให้ไปที่ตารางด้านล่างในรูปที่ 1 14 การถอนยอดคงเหลือ- ตัวเศษในนิพจน์ (10) คำนวณโดยใช้ฟังก์ชัน =SUMMAR(array1;array2) และตัวส่วน =SUMMAR(array) (รูปที่ 16)

ข้าว. 16. สูตรคำนวณสถิติ Durbin-Watson

ในตัวอย่างของเรา ดี= 0.883. คำถามหลักคือ ค่าใดของสถิติ Durbin-Watson ที่ควรถือว่าน้อยพอที่จะสรุปได้ว่ามีความสัมพันธ์อัตโนมัติเชิงบวกอยู่ มีความจำเป็นต้องเชื่อมโยงค่าของ D กับค่าวิกฤต ( ดี แอลและ ดี ยู) ขึ้นอยู่กับจำนวนการสังเกต nและระดับนัยสำคัญ α (รูปที่ 17)

ข้าว. 17. ค่าวิกฤตของสถิติ Durbin-Watson (ส่วนของตาราง)

ดังนั้นปัญหาปริมาณการขายในร้านค้าที่ส่งสินค้าถึงบ้านจึงมีตัวแปรอิสระตัวหนึ่ง ( เค= 1) 15 การสังเกต ( n= 15) และระดับนัยสำคัญ α = 0.05 เพราะฉะนั้น, ดี แอล= 1.08 และ งยู= 1.36. เพราะว่า ดี = 0,883 < ดี แอล= 1.08 มีความสัมพันธ์อัตโนมัติเชิงบวกระหว่างค่าคงเหลือ ไม่สามารถใช้วิธีกำลังสองน้อยที่สุดได้

การทดสอบสมมติฐานเกี่ยวกับความชันและสัมประสิทธิ์สหสัมพันธ์

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

แอปพลิเคชันที -เกณฑ์ความชันด้วยการทดสอบว่าความชันของประชากร β 1 เท่ากับศูนย์หรือไม่ คุณสามารถระบุได้ว่ามีความสัมพันธ์ที่มีนัยสำคัญทางสถิติระหว่างตัวแปรต่างๆ หรือไม่ เอ็กซ์และ ย- หากสมมติฐานนี้ถูกปฏิเสธก็สามารถโต้แย้งได้ว่าระหว่างตัวแปรต่างๆ เอ็กซ์และ ยมีความสัมพันธ์เชิงเส้น สมมติฐานว่างและทางเลือกมีสูตรดังนี้: H 0: β 1 = 0 (ไม่มีการพึ่งพาเชิงเส้น), H1: β 1 ≠ 0 (มีการพึ่งพาเชิงเส้น) A-ไพรเออรี่ ที-สถิติเท่ากับความแตกต่างระหว่างความชันตัวอย่างและค่าสมมุติของความชันประชากร หารด้วยค่าคลาดเคลื่อนกำลังสองเฉลี่ยรากของการประมาณค่าความชัน:

(11) ที = (ข 1 – β 1 ) / สบ 1

ที่ไหน ข 1 – ความชันของการถดถอยโดยตรงของข้อมูลตัวอย่าง β1 – ความชันสมมุติของประชากรทางตรง และทดสอบสถิติ ทีมันมี ที-จำหน่ายด้วย n – 2ระดับความอิสระ.

ตรวจสอบว่ามีความสัมพันธ์ที่มีนัยสำคัญทางสถิติระหว่างขนาดร้านค้าและยอดขายต่อปีที่ α = 0.05 หรือไม่ ที-เกณฑ์จะแสดงพร้อมกับพารามิเตอร์อื่นๆ เมื่อใช้งาน แพ็คเกจการวิเคราะห์(ตัวเลือก การถดถอย- ผลลัพธ์ที่สมบูรณ์ของแพ็คเกจการวิเคราะห์แสดงไว้ในรูปที่ 1 4 ส่วนที่เกี่ยวข้องกับสถิติ t - ในรูป 18.

ข้าว. 18. ผลการสมัคร ที

เนื่องจากจำนวนร้านค้า n= 14 (ดูรูปที่ 3) ค่าวิกฤต ที- สถิติที่ระดับนัยสำคัญ α = 0.05 สามารถพบได้โดยใช้สูตร: ทีแอล=STUDENT.ARV(0.025,12) = –2.1788 โดยที่ 0.025 คือครึ่งหนึ่งของระดับนัยสำคัญ และ 12 = n – 2; คุณ=STUDENT.OBR(0.975,12) = +2.1788

เพราะว่า ที-สถิติ = 10.64 > คุณ= 2.1788 (รูปที่ 19) สมมติฐานว่าง เอช 0ถูกปฏิเสธ อีกด้านหนึ่ง ร-มูลค่าสำหรับ เอ็กซ์= 10.6411 คำนวณโดยสูตร =1-STUDENT.DIST(D3,12,TRUE) มีค่าประมาณเท่ากับศูนย์ ดังนั้นสมมติฐาน เอช 0ถูกปฏิเสธอีกครั้ง ความจริงที่ว่า ร-ค่าเกือบเป็นศูนย์หมายความว่าหากไม่มีความสัมพันธ์เชิงเส้นที่แท้จริงระหว่างขนาดร้านค้าและยอดขายต่อปี แทบจะเป็นไปไม่ได้เลยที่จะตรวจพบความสัมพันธ์ดังกล่าวโดยใช้การถดถอยเชิงเส้น ดังนั้นจึงมีความสัมพันธ์เชิงเส้นตรงที่มีนัยสำคัญทางสถิติระหว่างยอดขายร้านค้ารายปีโดยเฉลี่ยและขนาดร้านค้า

ข้าว. 19. การทดสอบสมมติฐานเกี่ยวกับความชันของประชากรที่ระดับนัยสำคัญ 0.05 และ 12 องศาอิสระ

แอปพลิเคชันเอฟ -เกณฑ์ความชันอีกวิธีหนึ่งในการทดสอบสมมติฐานเกี่ยวกับความชันของการถดถอยเชิงเส้นอย่างง่ายคือการใช้ เอฟ-เกณฑ์. ให้เรานึกถึงสิ่งนั้น เอฟ-test ใช้เพื่อทดสอบความสัมพันธ์ระหว่างความแปรปรวนสองค่า (ดูรายละเอียดเพิ่มเติมดู) เมื่อทดสอบสมมติฐานความชัน การวัดข้อผิดพลาดแบบสุ่มคือความแปรปรวนของข้อผิดพลาด (ผลรวมของข้อผิดพลาดกำลังสองหารด้วยจำนวนดีกรีอิสระ) ดังนั้น เอฟ-เกณฑ์ใช้อัตราส่วนของความแปรปรวนที่อธิบายโดยการถดถอย (เช่น ค่า สสสหารด้วยจำนวนตัวแปรอิสระ เค) ถึงความแปรปรวนของข้อผิดพลาด ( MSE = เอสวายเอ็กซ์ 2 ).

A-ไพรเออรี่ เอฟ-สถิติเท่ากับค่าเฉลี่ยกำลังสองของการถดถอย (MSR) หารด้วยความแปรปรวนของข้อผิดพลาด (MSE): เอฟ = เอ็มเอสอาร์/ มสธ, ที่ไหน เอ็มเอสอาร์=สสส / เค, มสธ. =สสส/(n– เค – 1) เค– จำนวนตัวแปรอิสระในแบบจำลองการถดถอย สถิติการทดสอบ เอฟมันมี เอฟ-จำหน่ายด้วย เคและ n– เค – 1ระดับความอิสระ.

สำหรับระดับนัยสำคัญที่กำหนด α กฎการตัดสินใจจะถูกกำหนดดังนี้: ถ้า ฉ>ฉยูสมมติฐานว่างถูกปฏิเสธ มิฉะนั้นจะไม่ถูกปฏิเสธ ผลลัพธ์ที่นำเสนอในรูปแบบของตารางสรุปการวิเคราะห์ความแปรปรวนจะแสดงในรูปที่ 1 20.

ข้าว. 20. การวิเคราะห์ตารางความแปรปรวนเพื่อทดสอบสมมติฐานเกี่ยวกับนัยสำคัญทางสถิติของสัมประสิทธิ์การถดถอย

เช่นเดียวกัน ที-เกณฑ์ เอฟ-เกณฑ์จะแสดงในตารางเมื่อใช้ แพ็คเกจการวิเคราะห์(ตัวเลือก การถดถอย- ผลลัพธ์ของงานเต็มที่ แพ็คเกจการวิเคราะห์จะแสดงในรูป 4 ส่วนที่เกี่ยวข้องกับ เอฟ-สถิติ – ในรูป 21.

ข้าว. 21. ผลการสมัคร เอฟ-เกณฑ์ที่ได้รับโดยใช้ Excel Analysis Package

สถิติ F คือ 113.23 และ ร-ค่าใกล้กับศูนย์ (เซลล์ ความสำคัญเอฟ- หากระดับนัยสำคัญ α เท่ากับ 0.05 ให้กำหนดค่าวิกฤติ เอฟ- การแจกแจงที่มีระดับอิสระ 1 และ 12 องศาสามารถรับได้โดยใช้สูตร เอฟ ยู=F.OBR(1-0.05;1;12) = 4.7472 (รูปที่ 22) เพราะว่า เอฟ = 113,23 > เอฟ ยู= 4.7472 และ ร-ค่าใกล้ 0< 0,05, нулевая гипотеза เอช 0ถูกปฏิเสธ เช่น ขนาดของร้านค้ามีความสัมพันธ์อย่างใกล้ชิดกับยอดขายประจำปี

ข้าว. 22. การทดสอบสมมติฐานความชันประชากรที่ระดับนัยสำคัญ 0.05 โดยมีองศาอิสระ 1 และ 12 องศา

ช่วงความเชื่อมั่นที่มีความชัน β 1เพื่อทดสอบสมมติฐานว่ามีความสัมพันธ์เชิงเส้นระหว่างตัวแปร คุณสามารถสร้างช่วงความเชื่อมั่นที่มีความชัน β 1 และตรวจสอบว่าค่าสมมุติ β 1 = 0 อยู่ในช่วงนี้ จุดศูนย์กลางของช่วงความเชื่อมั่นที่มีความชัน β 1 คือความชันของตัวอย่าง ข 1 และขอบเขตของมันคือปริมาณ ข 1 ±tn –2 สบ 1

ดังแสดงในรูป 18, ข 1 = +1,670, n = 14, สบ 1 = 0,157. ที 12 =STUDENT.ARV(0.975,12) = 2.1788 เพราะฉะนั้น, ข 1 ±tn –2 สบ 1 = +1.670 ± 2.1788 * 0.157 = +1.670 ± 0.342 หรือ + 1.328 ≤ β 1 ≤ +2.012 ดังนั้น มีความน่าจะเป็น 0.95 ที่ความชันของประชากรจะอยู่ระหว่าง +1.328 ถึง +2.012 (เช่น 1,328,000 ถึง 2,012,000 ดอลลาร์สหรัฐฯ) เนื่องจากค่าเหล่านี้มากกว่าศูนย์ จึงมีความสัมพันธ์เชิงเส้นตรงที่มีนัยสำคัญทางสถิติระหว่างยอดขายประจำปีและพื้นที่ร้านค้า ถ้าช่วงความเชื่อมั่นมีค่าเป็นศูนย์ ตัวแปรต่างๆ จะไม่มีความสัมพันธ์กัน นอกจากนี้ ช่วงความเชื่อมั่นหมายความว่าแต่ละพื้นที่ร้านค้าเพิ่มขึ้น 1,000 ตร.ม. ฟุต ส่งผลให้ปริมาณการขายเฉลี่ยเพิ่มขึ้นระหว่าง 1,328,000 ถึง 2,012,000 ดอลลาร์

การใช้งานที -เกณฑ์สำหรับค่าสัมประสิทธิ์สหสัมพันธ์มีการแนะนำสัมประสิทธิ์สหสัมพันธ์ รซึ่งเป็นการวัดความสัมพันธ์ระหว่างตัวแปรตัวเลขสองตัว สามารถใช้เพื่อพิจารณาว่ามีความสัมพันธ์ที่มีนัยสำคัญทางสถิติระหว่างตัวแปรสองตัวหรือไม่ ให้เราแสดงค่าสัมประสิทธิ์สหสัมพันธ์ระหว่างประชากรของตัวแปรทั้งสองด้วยสัญลักษณ์ ρ สมมติฐานว่างและทางเลือกมีการกำหนดดังนี้: เอช 0: ρ = 0 (ไม่มีความสัมพันธ์กัน) เอช 1: ρ ≠ 0 (มีความสัมพันธ์กัน) การตรวจสอบการมีอยู่ของความสัมพันธ์:

ที่ไหน ร = + , ถ้า ข 1 > 0, ร = – , ถ้า ข 1 < 0. Тестовая статистика ทีมันมี ที-จำหน่ายด้วย n – 2ระดับความอิสระ.

ในประเด็นปัญหาเกี่ยวกับเครือร้าน Sunflowers ร 2= 0.904, ก ข 1- +1.670 (ดูรูปที่ 4) เพราะว่า ข 1> 0 ค่าสัมประสิทธิ์สหสัมพันธ์ระหว่างยอดขายต่อปีและขนาดร้านค้าคือ ร= +√0.904 = +0.951. มาทดสอบสมมติฐานว่างว่าไม่มีความสัมพันธ์ระหว่างตัวแปรเหล่านี้ที่ใช้ ที-สถิติ:

ที่ระดับนัยสำคัญ α = 0.05 สมมติฐานว่างควรถูกปฏิเสธเนื่องจาก ที= 10.64 > 2.1788 ดังนั้นจึงอาจเป็นที่ถกเถียงกันอยู่ว่ามีความสัมพันธ์ที่มีนัยสำคัญทางสถิติระหว่างยอดขายต่อปีและขนาดร้านค้า

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

การประมาณค่าความคาดหวังทางคณิตศาสตร์และการทำนายค่าแต่ละค่า

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

การสร้างช่วงความเชื่อมั่นในตัวอย่างที่ 2 (ดูหัวข้อด้านบน วิธีกำลังสองน้อยที่สุด) สมการถดถอยทำให้สามารถทำนายค่าของตัวแปรได้ ย เอ็กซ์- ในปัญหาการเลือกทำเลสำหรับร้านค้าปลีกปริมาณการขายเฉลี่ยต่อปีในร้านค้าที่มีพื้นที่ 4,000 ตร.ม. ฟุตมีค่าเท่ากับ 7.644 ล้านดอลลาร์ อย่างไรก็ตาม การประมาณการความคาดหวังทางคณิตศาสตร์ของประชากรทั่วไปนี้เป็นแบบจุด เพื่อประมาณค่าความคาดหวังทางคณิตศาสตร์ของประชากร จึงเสนอแนวคิดเรื่องช่วงความเชื่อมั่น ในทำนองเดียวกันเราสามารถแนะนำแนวคิดนี้ได้ ช่วงความเชื่อมั่นสำหรับความคาดหวังทางคณิตศาสตร์ของการตอบสนองสำหรับค่าตัวแปรที่กำหนด เอ็กซ์:

ที่ไหน , = ข 0 + ข 1 เอ็กซ์ ฉัน– ค่าทำนายเป็นตัวแปร ยที่ เอ็กซ์ = เอ็กซ์ ฉัน, เอส วายเอ็กซ์– ค่าคลาดเคลื่อนกำลังสองเฉลี่ยรูท n- ขนาดตัวอย่าง, เอ็กซ์ฉัน- ค่าที่ระบุของตัวแปร เอ็กซ์, µ ย|เอ็กซ์ = เอ็กซ์ฉัน– ความคาดหวังทางคณิตศาสตร์ของตัวแปร ยที่ เอ็กซ์ = สี, SSX =

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

สมมติว่าเมื่อเลือกที่ตั้งร้านค้า เราต้องการสร้างช่วงความเชื่อมั่น 95% สำหรับยอดขายเฉลี่ยต่อปีของร้านค้าทั้งหมดที่มีพื้นที่ 4,000 ตารางเมตร เท้า:

ดังนั้นปริมาณการขายเฉลี่ยต่อปีในร้านค้าทั้งหมดที่มีพื้นที่ 4,000 ตร.ม. ฟุต โดยความน่าจะเป็น 95% อยู่ในช่วงตั้งแต่ 6.971 ถึง 8.317 ล้านดอลลาร์

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

สมมติว่าเมื่อเลือกที่ตั้งสำหรับร้านค้าปลีก เราต้องการสร้างช่วงความเชื่อมั่น 95% สำหรับปริมาณยอดขายต่อปีที่คาดการณ์ไว้สำหรับร้านค้าที่มีพื้นที่ 4,000 ตารางเมตร เท้า:

ดังนั้นการคาดการณ์ปริมาณการขายประจำปีสำหรับร้านค้าที่มีพื้นที่ 4,000 ตร.ม. ฟุต โดยมีความน่าจะเป็น 95% อยู่ในช่วงตั้งแต่ 5.433 ถึง 9.854 ล้านดอลลาร์ ดังที่เราเห็น ช่วงความเชื่อมั่นสำหรับค่าตอบสนองที่คาดการณ์ไว้นั้นกว้างกว่าช่วงความเชื่อมั่นสำหรับการคาดการณ์ทางคณิตศาสตร์มาก เนื่องจากความแปรปรวนในการทำนายค่าแต่ละค่านั้นมากกว่าการประมาณค่าความคาดหวังทางคณิตศาสตร์มาก

ข้อผิดพลาดและประเด็นทางจริยธรรมที่เกี่ยวข้องกับการใช้การถดถอย

ปัญหาที่เกี่ยวข้องกับการวิเคราะห์การถดถอย:

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

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

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

ข้าว. 23. ชุดข้อมูลปลอมสี่ชุด

ข้าว. 24. การวิเคราะห์การถดถอยของชุดข้อมูลปลอมสี่ชุด เสร็จแล้วด้วย แพ็คเกจการวิเคราะห์(คลิกที่ภาพเพื่อขยายภาพ)

ดังนั้น จากมุมมองของการวิเคราะห์การถดถอย ชุดข้อมูลเหล่านี้ทั้งหมดจะเหมือนกันโดยสิ้นเชิง หากการวิเคราะห์สิ้นสุดลง เราจะสูญเสียข้อมูลที่เป็นประโยชน์มากมาย สิ่งนี้เห็นได้จากแผนการกระจาย (รูปที่ 25) และแผนส่วนที่เหลือ (รูปที่ 26) ที่สร้างขึ้นสำหรับชุดข้อมูลเหล่านี้

ข้าว. 25. แผนกระจายสำหรับชุดข้อมูลสี่ชุด

แปลงกระจายและแปลงที่เหลือบ่งชี้ว่าข้อมูลเหล่านี้แตกต่างกัน ชุดเดียวที่กระจายไปตามเส้นตรงคือชุด A โดยแผนภาพเศษเหลือที่คำนวณจากชุด A ไม่มีรูปแบบใดๆ สิ่งนี้ไม่สามารถพูดได้เกี่ยวกับเซต B, C และ D แผนภาพกระจายที่พล็อตสำหรับเซต B แสดงรูปแบบกำลังสองที่เด่นชัด ข้อสรุปนี้ได้รับการยืนยันโดยโครงส่วนที่เหลือซึ่งมีรูปร่างพาราโบลา แผนภูมิกระจายและแผนภูมิที่เหลือแสดงว่าชุดข้อมูล B มีค่าผิดปกติ ในสถานการณ์นี้ จำเป็นต้องแยกค่าผิดปกติออกจากชุดข้อมูลและทำการวิเคราะห์ซ้ำ วิธีการตรวจจับและกำจัดค่าผิดปกติในการสังเกตเรียกว่าการวิเคราะห์แบบมีอิทธิพล หลังจากกำจัดค่าผิดปกติแล้ว ผลลัพธ์ของการประเมินแบบจำลองใหม่อาจแตกต่างกันอย่างสิ้นเชิง แผนภาพกระจายที่พล็อตจากข้อมูลจากชุด G แสดงให้เห็นถึงสถานการณ์ที่ผิดปกติซึ่งแบบจำลองเชิงประจักษ์นั้นขึ้นอยู่กับการตอบสนองของแต่ละบุคคลอย่างมีนัยสำคัญ ( เอ็กซ์ 8 = 19, ย 8 = 12.5) แบบจำลองการถดถอยดังกล่าวจะต้องคำนวณอย่างระมัดระวังเป็นพิเศษ ดังนั้นแปลงกระจายและแปลงที่เหลือจึงเป็นเครื่องมือสำคัญสำหรับการวิเคราะห์การถดถอยและควรเป็นส่วนสำคัญของการวิเคราะห์การถดถอย หากไม่มีสิ่งเหล่านี้ การวิเคราะห์การถดถอยก็ไม่น่าเชื่อถือ

ข้าว. 26. แปลงที่เหลือสำหรับชุดข้อมูลสี่ชุด

วิธีหลีกเลี่ยงข้อผิดพลาดในการวิเคราะห์การถดถอย:

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

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

ข้าว. 27. หมายเหตุโครงสร้างแผนภาพ

มีการใช้สื่อจากหนังสือ Levin และคณะ สถิติสำหรับผู้จัดการ – อ.: วิลเลียมส์, 2004. – หน้า. 792–872

ถ้าตัวแปรตามเป็นแบบแบ่งหมวดหมู่ ต้องใช้การถดถอยโลจิสติก

การถดถอยใน Excel

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

ข้าว. 17. หน้าต่าง โครงสร้างส่วนบน

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

ใน Excel 2007 ที่จะติดตั้ง แพคเกจการวิเคราะห์คุณต้องคลิกที่ปุ่ม OFFICE ที่มุมซ้ายบนของแผ่นงาน (รูปที่ 18a) จากนั้นคลิกที่ปุ่ม การตั้งค่า EXCEL- ในหน้าต่างที่ปรากฏขึ้น การตั้งค่า EXCELคลิกซ้ายที่รายการ โครงสร้างส่วนบนและทางด้านขวาของรายการแบบเลื่อนลงให้เลือกรายการ แพคเกจการวิเคราะห์คลิกถัดไป ตกลง.

ตัวเลือก Excel ปุ่มสำนักงาน

ข้าว. 18. การติดตั้ง แพคเกจการวิเคราะห์ในเอ็กเซล 2007

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

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

จะปรากฎหน้าต่างดังแสดงในรูป 21

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

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

กล่องโต้ตอบการถดถอย

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

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

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

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

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

เวิร์กบุ๊กใหม่ เลือกตัวเลือกนี้เพื่อสร้างเวิร์กบุ๊กใหม่พร้อมผลลัพธ์ที่เพิ่มลงในเวิร์กชีตใหม่

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

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

กราฟคงเหลือ เลือกกล่องกาเครื่องหมายเพื่อพล็อตกราฟคงเหลือสำหรับตัวแปรอิสระแต่ละตัว

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

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

การทำงาน ไลน์

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

ข้าว. 22 การค้นหาฟังก์ชันใน Excel 2003

ถ้าอยู่ในสนาม ชื่อชื่อของฟังก์ชันไม่ปรากฏขึ้นจากนั้นคลิกซ้ายที่สามเหลี่ยมถัดจากฟิลด์หลังจากนั้นหน้าต่างที่มีรายการฟังก์ชันจะปรากฏขึ้น หากฟังก์ชันนี้ไม่อยู่ในรายการ ให้คลิกซ้ายที่รายการ ฟังก์ชั่นอื่นๆกล่องโต้ตอบจะปรากฏขึ้น ฟังก์ชันมาสเตอร์ซึ่งใช้การเลื่อนแนวตั้งเลือกฟังก์ชันที่ต้องการ ไฮไลต์ด้วยเคอร์เซอร์แล้วคลิก ตกลง(รูปที่ 23)

ข้าว. 23. ตัวช่วยสร้างฟังก์ชัน

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

คุณยังสามารถเลือกแท็บสูตรและคลิกซ้ายที่ปุ่มในเมนู “ แทรกฟังก์ชัน"(รูปที่ 24) หน้าต่างจะปรากฏขึ้น ฟังก์ชันมาสเตอร์ลักษณะที่ปรากฏคล้ายกับ Excel 2003 นอกจากนี้ในเมนูคุณสามารถเลือกหมวดหมู่ของฟังก์ชันได้ทันที (ใช้ล่าสุด, การเงิน, ตรรกะ, ข้อความ, วันที่และเวลา, คณิตศาสตร์, ฟังก์ชั่นอื่น ๆ ) ซึ่งเราจะค้นหาสิ่งที่ต้องการ การทำงาน.

คุณสมบัติอื่นๆ ลิงค์และอาร์เรย์

คณิตศาสตร์

ข้าว. 24 การเลือกฟังก์ชันใน Excel 2007

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

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

(ในกรณีที่ค่า x หลายช่วง)

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

ไลน์(known_values_y;known_values_x; const; สถิติ)

Known_y_values คือชุดของค่า y ที่ทราบความสัมพันธ์อยู่แล้ว

หากอาร์เรย์known_y_values มีหนึ่งคอลัมน์ แต่ละคอลัมน์ในอาร์เรย์known_x_values จะถือเป็นตัวแปรแยกกัน

หากอาร์เรย์known_y_values มีหนึ่งแถว แต่ละแถวในอาร์เรย์known_x_values จะถือเป็นตัวแปรแยกกัน

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

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

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

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

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

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

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

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

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

สถิติการถดถอยเพิ่มเติม

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

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

หมายเหตุ:

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

ความชัน (m): ในการกำหนดความชันของเส้น ซึ่งโดยปกติจะแสดงด้วย m คุณจะต้องใช้จุดสองจุดบนเส้นตรง และ ; ความชันจะเท่ากัน .

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

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

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

ความชัน: INDEX(LINEST(known_y_values;known_x_values); 1)

ค่าตัดแกน Y: INDEX(LINEST(known_y_values;known_x_values); 2)

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

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

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

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

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

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

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

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

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

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

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

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

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

LINEST(y_values, x_values^COLUMN($A:$C))

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

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

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

จะปรากฎหน้าต่างดังแสดงในรูป 63.

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

กล่องโต้ตอบการถดถอย

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

การทำงาน ไลน์

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

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

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

ไลน์(known_values_y;known_values_x; const; สถิติ)

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

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

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

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

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

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

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

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

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

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

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

หมายเหตุ:

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

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

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

ความชัน: INDEX(LINEST(known_y_values;known_x_values); 1)

ค่าตัดแกน Y: INDEX(LINEST(known_y_values;known_x_values); 2)

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

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

LINEST(y_values, x_values^COLUMN($A:$C))

y=ม. 1 x+ม. 2 x 2 +ม. 3 x 3 +b

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

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

การวิเคราะห์การถดถอยใน 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

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

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

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

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

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

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

ตัวอย่าง:

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

วิธีการถดถอยเชิงเส้นช่วยให้เราสามารถอธิบายเส้นตรงที่เหมาะกับชุดของคู่อันดับ (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 เศรษฐมิติ. การถดถอยเชิงเส้นใน MS Excel

การถดถอยใน Excel

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

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

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

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

แจ้งการพิมพ์ผิด

ข้อความที่จะส่งถึงบรรณาธิการของเรา:

ความคิดเห็นของคุณ (ไม่บังคับ):