Excel Machine Learning Statistics

Multiple Linear Regression in Excel

สอนใช้งานฟังชั่น LINEST แบบ array formulas เพื่อสร้าง Multiple Linear Regression ในโปรแกรม 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

1 comment

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.