ฟังชั่น LINEST กับการสร้าง Linear Regression ใน Excel

อยากสร้าง Multiple Linear Regression ง่ายๆด้วยโปรแกรม Excel ภายในหนึ่งนาที?

บทความวันนี้สอนใช้ฟังชั่น LINEST ฉบับรวบรัดแค่หนึ่งฟังชั่นได้ครบหมดทั้งค่า R-squared และ regression coefficients อ่านบทความตอนที่แล้วเรื่อง Simple Linear Regression ได้ที่นี่

General Form

สำหรับสมการ Multiple Linear Regression (MLR) โดยทั่วไปเขียนได้แบบนี้

y = b0 + b1*x1 + b2*x2 + b3*x3 + … + bk*xk

โดยที่ y คือตัวแปรตาม (dependent variable) และ x คือตัวแปรต้น (independent variable) ในสมการ MLR จะมีตัวแปรต้นได้มากกว่าหนึ่งตัว เงื่อนไขสำคัญที่สุดในการสร้าง Linear Regression คือตัวแปรตามต้องเป็นแบบ continuous i.e. ตัวเลขแบบ real number มีทศนิยมได้

---

Excel Tutorial

เพื่อนๆสามารถดาวน์โหลด Excel File ตัวอย่างได้ที่นี่ กดที่ sheet MLR แล้วรีวิวสูตรใน cell G3:J7 ได้เลย ฟังชั่นที่เจ๋งมากๆที่ใช้สร้าง Linear Regression ใน Excel คือ LINEST ซึ่งทำงานแบบ Array Formulas ซึ่งมีขั้นตอนการเขียนสูตร ดังนี้

1. ใช้เม้าส์ลากคลุม cell G3:J7 ไว้
2. พิมส์สูตร =LINEST(B2:B16, C2:E16, TRUE, TRUE)
3. เสร็จแล้วกด CTRL + SHIFT + ENTER พร้อมกันเพื่อ populate สูตรไปยัง cell G3:J7 ที่เราเลือกไว้ในขั้นตอนที่หนึ่ง

สำหรับสูตรแบบ array formulas ถ้าเราเอาเม้าส์ไปคลิกดูใน cell G3:J7 จะเห็นว่าสูตรที่เราพิมพ์ไปเมื่อตะกี้จะมี {our formulas} เครื่องหมาย { } ครอบสูตรเราไว้อีกที i.e. ปีกกาคือสัญลักษณ์ของ array formulas ในโปรแกรม Excel

---

Useful Trick to Perform LINEST

ฟังชั่น LINEST มีอยู่ 4 arguments

• argument 1 คือ array ข้อมูลตัวแปรตาม (y)
• argument 2 คือ array ข้อมูลตัวแปรต้น (x) สามารถเลือกได้มากกว่าหนึ่งคอลั่ม
• argument 3 ให้เราพิมพ์ว่า TRUE เพื่อให้ฟังชั่นคำนวณค่า intercept b0 ให้เราด้วย (หรือพิมพ์ FALSE ถ้าไม่อยากได้ค่า b0 ในสมการ)
• argument 4 ให้เราพิมพ์ว่า TRUE เช่นกัน เพื่อให้ฟังชั่นคำนวณ coefficient, R-squared และค่าสถิติที่สำคัญอื่นๆของโมเดล Linear Regression ให้เราด้วย

แล้วเราจะรู้ได้ยังไงว่าต้องลากคลุม cell กี่ช่องก่อนพิมพ์สูตร LINEST?

ตาราง output ของฟังชั่น LINEST จะออกมา 5 rows เสมอ! ส่วนจำนวน columns ขึ้นอยู่กับจำนวนตัวแปรต้นในสมการ ถ้าเราเลือก 3 ตัวแปรต้น (x1 x2 x3) ใส่ลงไปในโมเดล → output จะออกมาทั้งหมด 3 + 1 = 4 columns

ทำไมถึงได้ผลออกมา 4 columns? เพราะเราจะได้ค่า b มาทั้งหมด 4 ค่าคือ intercept , b1, b2 และ b3 ในตัวอย่างของเรามี 3 ตัวแปรต้น ตอนแรกที่เราลากคลุม cell เลยต้องลากคลุม 5 rows x 4 columns (e.g. G3:J7) ก่อนพิมพ์สูตร LINEST นั่นเอง

อ่านวิธีการใช้งานฟังชั่น LINEST แบบละเอียดได้ที่นี่

---

Interpretation

สำหรับการอ่านผลของฟังชั่น LINEST สามารถดูตามรูปด้านบนได้เลย ตำแหน่งของค่าสถิติต่างๆจะอยู่ในตารางที่สอง สมการ MLR ของเราจะเขียนได้แบบนี้

mpg = 34.3059 – 0.0457hp + 0.0048disp – 3.0053wt

โมเดลของเรามีค่า R-squared เท่ากับ 0.8958 หรือพูดได้ว่าตัวแปรต้นทั้งสามตัว (hp, disp, wt) ที่เราใส่ไปในโมเดลอธิบายการเปลี่ยนแปลงของ mpg ได้สูงถึง 89.58%

จากผล Linear Regression พบว่าค่าสัมประสิทธิ์ของตัวแปร hp (-0.0457) และ wt (-3.0053) มีค่าติดลบ เราสามารถสรุปผลได้ว่าแรงม้า (hp) และน้ำหนักรถยนต์ (wt) แปรผกผันกับระยะทางที่รถยนต์วิ่งได้ต่อน้ำมันหนึ่งแกลลอน (miles per gallon)

---

Key Learnings

• Linear Regression สร้างได้ง่ายๆในโปรแกรม Excel ด้วยฟังชั่น LINEST
• LINEST เป็น array formulas ต้องลากคลุม cell ทั้งหมดก่อนพิิมพ์สูตร แล้วกด CTRL + SHIFT + ENTER เพื่อ populate สูตรแบบอัตโนมัติ
• ผลลัพธ์ที่ได้จากฟังชั่นจะออกมา 5 แถวเสมอ ส่วนจำนวนคอลั่มจะเท่ากับจำนวนตัวแปรต้นในสมการ + 1 (หรือนักสถิติชอบเขียนแบบนี้ k+1 โดยที่ k คือจำนวน x ทั้งหมดในสมการ)
• การใช้ฟังชั่น LINEST ได้ผลลัพธ์เหมือนกับการใช้ add-in Analysis Toolpak เลย เด็ดดด!!!

---

ถ้าเห็นว่าบทความนี้มีประโยชน์ อย่าลืมกด LIKE กด SHARE ให้เพื่อนได้อ่านด้วยนะคร้าบ #กราบบบ ติดตามข่าวสาร อัพเดทความรู้สถิติ | data science | programming ฟรีตลอดชีวิตที่ facebook ของเรา DataRockie