Multiple Linear Regression in Excel

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

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


General Form

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

y = b_0 + b_1x_1 + b_2x_2 + b_3x_3 + ... + b_kx_k

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


Excel Tutorial

Linear Regression Model
Picture: https://unsplash.com/photos/NDfqqq_7QWM

เพื่อนๆสามารถดาวน์โหลด 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 (b_0) ให้เราด้วย (หรือพิมพ์ FALSE ถ้าไม่อยากได้ค่า b_0 ในสมการ)
  • argument 4 ให้เราพิมพ์ว่า TRUE เช่นกัน เพื่อให้ฟังชั่นคำนวณ coefficient, R-squared และค่าสถิติที่สำคัญอื่นๆของโมเดล Linear Regression ให้เราด้วย

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

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

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

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


Interpretation

LINEST output
ตารางบนคือ output ที่ได้จากฟังชั่น LINEST ส่วนตารางล่างคือชื่อของสถิติที่ได้ออกมา

สำหรับการอ่านผลของฟังชั่น 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

One thought on “Multiple Linear Regression in Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.