วิธีการทำ Regression ใน Excel: คู่มือฉบับสมบูรณ์

ในการทำ regression ใน Excel ให้เปิดใช้งาน Analysis ToolPak จากนั้นไปที่ Data > Data Analysis แล้วเลือก Regression เลือกช่วง Y และช่วง X ของคุณ เลือกตำแหน่งที่ต้องการแสดงผล และเลือก OK โดย Excel จะแสดงค่า coefficients, R Square และสถิติอื่นๆ ในตารางใหม่
คู่มือนี้ครอบคลุมสามวิธีในการรัน regression ใน Excel ได้แก่ Analysis ToolPak, ฟังก์ชัน LINEST และเส้นแนวโน้มในแผนภูมิ นอกจากนี้ยังพาไปดูตัวอย่างฉบับเต็ม อธิบายวิธีอ่านผลลัพธ์ และแสดงวิธีที่เร็วกว่าในการทดสอบความสัมพันธ์ในไฟล์ขนาดใหญ่ ข้อมูลรายละเอียดมาจากหน้าสนับสนุนของ Microsoft ซึ่งอ่านเมื่อวันที่ 12 พฤศจิกายน 2026
regression บอกอะไรคุณได้บ้าง
regression วัดว่าค่าหนึ่งเปลี่ยนแปลงอย่างไรเมื่ออีกค่าหนึ่งเปลี่ยนไป ค่าที่คุณต้องการอธิบายคือตัวแปรตาม หรือ Y ส่วนค่าที่คุณคิดว่าเป็นตัวขับเคลื่อนคือตัวแปรอิสระ หรือ X
Microsoft อธิบายเครื่องมือ Regression ไว้ดังนี้ เครื่องมือนี้ "ทำการวิเคราะห์ linear regression โดยใช้วิธี "least squares" เพื่อสร้างเส้นที่เหมาะสมที่สุดผ่านชุดข้อมูลที่สังเกตได้"
เส้นนั้นมีสมการง่ายๆ Microsoft เขียนไว้เป็น y = mx + b โดยที่ m คือความชัน (slope) และ b คือจุดตัดแกน (intercept) หากมี X มากกว่าหนึ่งตัว สมการจะกลายเป็น y = m1x1 + m2x2 + ... + b
ในเชิงธุรกิจ regression ตอบคำถามเช่นนี้:
- ยอดขายเพิ่มขึ้นเท่าใดในแต่ละเดือนที่เติบโตขึ้น?
- ค่าใช้จ่ายในการโฆษณาสัมพันธ์กับการลงชื่อสมัครใช้มากน้อยเพียงใด?
- การเปลี่ยนแปลงของต้นทุนถูกอธิบายโดยปริมาณการสั่งซื้อมากน้อยเพียงใด?
ตัวอย่างของ Microsoft เองเป็นเรื่องเกี่ยวกับนักกีฬา โดยระบุว่าคุณสามารถวิเคราะห์ "ประสิทธิภาพของนักกีฬาได้รับผลกระทบจากปัจจัยต่างๆ เช่น อายุ ส่วนสูง และน้ำหนัก อย่างไร"
regression แสดงให้เห็นถึงความเกี่ยวข้องกัน ไม่ใช่การพิสูจน์สาเหตุ เส้นที่แข็งแกร่งระหว่างสองค่าอาจมาจากปัจจัยที่สามที่คุณไม่ได้วัดผลก็เป็นได้
ตั้งค่าข้อมูลของคุณใน Excel
Regression ต้องการคู่ตัวเลขที่สะอาดและจับคู่กันได้ แต่ละแถวควรมีข้อมูลการสังเกตหนึ่งรายการ โดยมี Y ในคอลัมน์หนึ่ง และ X แต่ละตัวในคอลัมน์ของตัวเอง
นี่คือข้อมูลจากตัวอย่าง simple linear regression ของ Microsoft ซึ่งแสดงรายการยอดขายหกเดือนในหน่วยดอลลาร์
| Month | Sales |
|---|---|
| 1 | 3,100 |
| 2 | 4,500 |
| 3 | 4,400 |
| 4 | 5,400 |
| 5 | 7,500 |
| 6 | 8,100 |
ใส่ Month ในคอลัมน์ A และ Sales ในคอลัมน์ B โดยมีหัวตารางในแถวที่ 1 ตัวอย่างด้านล่างใช้รูปแบบนี้ ข้อมูลจะอยู่ในแถวที่ 2 ถึง 7 ซึ่งเป็นเหตุผลว่าทำไมสูตรด้านล่างจึงใช้ช่วงเหล่านั้น
กฎสองสามข้อเพื่อให้ผลลัพธ์น่าเชื่อถือ:
- ห้ามมีเซลล์ว่าง ทุกแถวต้องมีทั้งค่า Y และค่า X
- ตัวเลขเท่านั้น ค่าที่เก็บเป็นข้อความจะทำให้เกิดข้อผิดพลาด
- จำนวนแถวที่เพียงพอ หกแถวใช้ได้สำหรับการสาธิต แต่การวิเคราะห์จริงต้องการมากกว่านั้นมาก
- หนึ่งแถวต่อหนึ่งการสังเกต อย่าผสมแถวรายเดือนและรายสัปดาห์ในหน้าเดียวกัน
เปิดใช้งาน Analysis ToolPak
เครื่องมือ Regression อยู่ใน Analysis ToolPak ซึ่งเป็น add-in ที่มาพร้อมกับ Excel คุณอาจต้องเปิดใช้งานก่อน
ขั้นตอนของ Microsoft สำหรับ Excel บน Windows คือ:
- เลือก File จากนั้นเลือก Options และเลือก Add-Ins
- ในกล่อง Manage ให้เลือก Excel Add-ins แล้วเลือก Go
- ในกล่อง Add-Ins ให้เลือกช่องทำเครื่องหมาย Analysis ToolPak แล้วเลือก OK
บน Mac Microsoft แนะนำให้ไปที่ Tools > Excel Add-ins จากเมนูไฟล์ หากไม่มี ToolPak ในรายการ ให้เลือก Browse เพื่อค้นหา
เมื่อเปิดใช้งานแล้ว คุณจะเห็น Data Analysis บนแท็บ Data โดย Microsoft ระบุว่าหากคำสั่งหายไป "คุณต้องโหลดและเปิดใช้งานโปรแกรม add-in Analysis ToolPak"
วิธีที่ 1: รัน regression ด้วย Analysis ToolPak
นี่เป็นวิธีที่สมบูรณ์ที่สุด โดยจะให้ตารางสรุปผลฉบับเต็มในขั้นตอนเดียว
- ไปที่แท็บ Data และเลือก Data Analysis
- เลือก Regression จากรายการแล้วเลือก OK
- ใน Input Y Range ให้เลือกคอลัมน์ Sales รวมถึงหัวตาราง
- ใน Input X Range ให้เลือกคอลัมน์ Month รวมถึงหัวตาราง
- ติ๊กถูกที่ Labels เนื่องจากทั้งสองช่วงมีหัวตารางรวมอยู่ด้วย
- เลือก Output Range หรือ New Worksheet Ply
- เลือก OK
Excel จะสร้างสรุปผลที่มีสามส่วน: สถิติ regression, ตาราง ANOVA และตาราง coefficients ส่วนถัดไปจะอธิบายวิธีอ่านค่าเหล่านี้
สำหรับ multiple regression ให้เลือกคอลัมน์ X หลายคอลัมน์ที่อยู่ติดกันในขั้นตอนที่ 4 Microsoft ระบุว่าเครื่องมือนี้สามารถวิเคราะห์ "ตัวแปรตามตัวเดียวได้รับผลกระทบจากค่าของตัวแปรอิสระหนึ่งตัวหรือมากกว่าอย่างไร"
เบื้องหลังเครื่องมือนี้ใช้ฟังก์ชันเวิร์กชีต โดย Microsoft กล่าวว่า "เครื่องมือ Regression ใช้ฟังก์ชันเวิร์กชีต LINEST"
วิธีที่ 2: ใช้ฟังก์ชัน LINEST
LINEST ให้ตัวเลขเดียวกันในรูปแบบสูตร เหมาะสำหรับผู้ที่ต้องการผลลัพธ์ที่อัปเดตเมื่อข้อมูลเปลี่ยนแปลง
ไวยากรณ์ของ Microsoft คือ LINEST(known_y's, [known_x's], [const], [stats]) โดย Known_y's คือช่วง Sales และ known_x's คือช่วง Month
สำหรับข้อมูลยอดขาย ให้พิมพ์สิ่งนี้ในเซลล์ว่าง:
=LINEST(B2:B7, A2:A7)
ฟังก์ชันจะคืนค่าสองค่า: ความชัน (slope) ตามด้วยจุดตัดแกน (intercept) Microsoft กล่าวว่าอาร์เรย์ที่ LINEST คืนค่าคือ "{mn,mn-1,...,m1,b}" ดังนั้นจุดตัดแกนจะอยู่ลำดับสุดท้ายเสมอ
หากต้องการสถิติเพิ่มเติม ให้ตั้งค่าอาร์กิวเมนต์สุดท้ายเป็น TRUE:
=LINEST(B2:B7, A2:A7, TRUE, TRUE)
เมื่อตั้งค่า stats เป็น TRUE ฟังก์ชัน LINEST จะคืนค่า standard errors, R squared, สถิติ F, degrees of freedom และ sums of squares โดย Microsoft ระบุว่าฟังก์ชันนี้ "ต้องป้อนเป็นสูตรอาร์เรย์" เนื่องจากคืนค่าหลายค่าพร้อมกัน
หน้าของ Microsoft ยังแสดงการพยากรณ์ในเซลล์เดียว สูตรด้านล่างประมาณการยอดขายเดือนที่ 9 จากเดือนที่ 1 ถึง 6:
=SUM(LINEST(B2:B7, A2:A7)*{9,1})
ผลลัพธ์ของ Microsoft สำหรับตัวอย่างนี้คือ 11,000
วิธีที่ 3: เพิ่มเส้นแนวโน้มในแผนภูมิกระจาย (Scatter Chart)
เส้นแนวโน้ม (trendline) แสดงเส้น regression บนแผนภูมิ เป็นวิธีที่เร็วที่สุดในการดูว่าเส้นตรงนั้นเหมาะสมกับข้อมูลหรือไม่
- เลือกข้อมูลทั้งสองคอลัมน์
- แทรกแผนภูมิกระจาย (scatter chart)
- เลือกแผนภูมิ จากนั้นเพิ่ม Trendline จากเมนูองค์ประกอบแผนภูมิ
- เปิดบานหน้าต่าง Format Trendline และเลือกตัวเลือก Linear
- ติ๊กถูกในช่องเพื่อแสดงสมการและค่า R-squared บนแผนภูมิ
แผนภูมิจะแสดงสมการ เช่น y = 1000x + 2000 ข้างๆ เส้น ซึ่งตรงกับผลลัพธ์ของ LINEST สำหรับข้อมูลยอดขาย
เส้นแนวโน้มยังสามารถพยากรณ์ไปข้างหน้าได้ Microsoft กล่าวว่าคุณสามารถ "ตั้งค่าในฟิลด์ Forward และ Backward เพื่อพยากรณ์ข้อมูลของคุณไปในอนาคต"
เส้นแนวโน้มเหมาะสำหรับการดูคร่าวๆ หรือใช้ในสไลด์ สำหรับ standard errors, p-values หรือตัวแปร X หลายตัว ให้ใช้วิธีที่ 1 หรือวิธีที่ 2
ควรใช้วิธีไหนดี
แต่ละวิธีเหมาะกับงานที่ต่างกัน เลือกตามสิ่งที่คุณต้องการนำเสนอและผู้ที่จะอ่านข้อมูลนั้น
| วิธี | เหมาะสำหรับ | ให้ค่า p-values | อัปเดตตามข้อมูล |
|---|---|---|---|
| Analysis ToolPak | รายงานฉบับเต็มของโมเดลเดียว | ใช่ | ไม่ ต้องรันเครื่องมือใหม่ |
| LINEST | โมเดลภายในสเปรดชีตที่ใช้งานอยู่ | ไม่ แต่ให้สถิติเพื่อนำไปคำนวณต่อได้ | ใช่ |
| Scatter trendline | การตรวจสอบด้วยภาพอย่างรวดเร็วหรือสไลด์ | ไม่ | ใช่ |
ใช้ ToolPak เมื่อคุณต้องการยืนยันผลลัพธ์ในการประชุม ตารางสรุปผลมีตัวเลขทุกตัวที่ผู้ตรวจสอบอาจถามถึง รวมถึง p-values สำหรับแต่ละ coefficient
ใช้ LINEST เมื่อโมเดลถูกนำไปใช้ต่อในสูตรอื่นๆ เช่น แผ่นงานพยากรณ์ เนื่องจากเป็นสูตร ความชันและจุดตัดแกนจะเปลี่ยนทันทีที่มีแถวข้อมูลใหม่เข้ามา
ใช้เส้นแนวโน้มเมื่อผู้ชมต้องการเห็นรูปแบบ แผนภูมิที่มีเส้นและสมการอ่านง่ายกว่าตาราง นักวิเคราะห์หลายคนใช้ทั้งสามวิธี: เส้นแนวโน้มเพื่อสำรวจ, LINEST เพื่อสร้าง และ ToolPak เพื่อรายงานผล
ตัวอย่าง regression
นี่คือสิ่งที่ทั้งสามวิธีคืนค่าสำหรับยอดขายหกเดือน
| สถิติ | ค่า | ความหมาย |
|---|---|---|
| Slope (m) | 1,000 | ยอดขายเพิ่มขึ้นประมาณ 1,000 ต่อเดือน |
| Intercept (b) | 2,000 | ค่าของเส้นที่เดือนที่ 0 |
| R squared | 0.93 | ประมาณ 93% ของความผันแปรของยอดขายเป็นไปตามแนวโน้มรายเดือน |
| ประมาณการเดือนที่ 9 | 11,000 | 1,000 × 9 + 2,000 |
คุณสามารถตรวจสอบความชันด้วยตัวเองได้ เดือนเฉลี่ยคือ 3.5 และค่ายอดขายเฉลี่ยคือ 5,500 ความชันคือผลรวมของผลคูณไขว้ของส่วนเบี่ยงเบนหารด้วยผลรวมของส่วนเบี่ยงเบนเดือนยกกำลังสอง ซึ่งคำนวณได้เป็น 17,500 หารด้วย 17.5 ซึ่งเท่ากับ 1,000
จากนั้นจะได้จุดตัดแกน ซึ่งคือค่ายอดขายเฉลี่ยลบด้วยความชันคูณด้วยเดือนเฉลี่ย: 5,500 ลบ 3,500 ซึ่งเท่ากับ 2,000
R squared บอกคุณว่าเส้นนั้นเหมาะสมเพียงใด Microsoft อธิบายว่าเป็นค่าที่ "มีค่าตั้งแต่ 0 ถึง 1" ที่ค่า 1 เส้นจะพอดีกับทุกจุด ที่ค่า 0 Microsoft กล่าวว่า "สมการ regression ไม่มีประโยชน์ในการทำนายค่า y"
วิธีอ่านผลลัพธ์ regression
ผลลัพธ์จาก Analysis ToolPak อาจดูซับซ้อน การตัดสินใจส่วนใหญ่ขึ้นอยู่กับตัวเลขเพียงไม่กี่ตัว
- Multiple R. ความสัมพันธ์ระหว่างค่าจริงและค่าที่ทำนายได้ ยิ่งใกล้ 1 ยิ่งแสดงว่ามีความแม่นยำสูง
- R Square. ส่วนแบ่งของความผันแปรใน Y ที่อธิบายโดย X ในที่นี้คือประมาณ 0.93
- Adjusted R Square. R Square ที่ปรับตามจำนวนตัวแปร X ใช้เมื่อเปรียบเทียบโมเดลที่มีจำนวนอินพุตต่างกัน
- Standard Error. ระยะห่างโดยทั่วไประหว่างจุดจริงและเส้น
- Coefficients. จุดตัดแกนและความชันสำหรับ X แต่ละตัว สิ่งเหล่านี้ใช้สร้างสมการของคุณ
- P-value. โอกาสที่จะเห็น coefficient ใหญ่ขนาดนี้หากผลกระทบที่แท้จริงเป็นศูนย์ นักวิเคราะห์หลายคนถือว่าค่าที่ต่ำกว่า 0.05 มีนัยสำคัญ
- F และ Significance F. การทดสอบโมเดลทั้งหมด ค่า Significance F ที่น้อยแสดงว่าโมเดลอธิบายได้มากกว่าเรื่องของโชคช่วย
Microsoft เพิ่มหมายเหตุเกี่ยวกับความแม่นยำว่า "ยิ่งข้อมูลมีความเป็นเส้นตรงมากเท่าใด โมเดล LINEST ก็จะยิ่งแม่นยำมากขึ้นเท่านั้น"
อ่านค่า coefficients ในหน่วยปกติ ความชัน 1,000 หมายความว่าแต่ละเดือนที่เพิ่มขึ้นจะมียอดขายเพิ่มขึ้นประมาณ 1,000 โดยเฉลี่ยในช่วงเวลานี้
ทำ regression ใน Excel ด้วย AI
เมื่อคุณมีคอลัมน์จำนวนมากและไม่แน่ใจว่าคอลัมน์ไหนสำคัญ การทดสอบแต่ละคู่ด้วยมืออาจใช้เวลานาน เครื่องมือ AI สามารถตรวจสอบความสัมพันธ์ในชุดข้อมูลและอธิบายผลได้
นี่คือวิธีในสามขั้นตอน โดยใช้ Powerdrill Bloom
ขั้นตอนที่ 1: อัปโหลดไฟล์ Excel ของคุณ
อัปโหลดเวิร์กบุ๊กที่มีคอลัมน์ Y และคอลัมน์ X ที่คุณสนใจ หน้า Excel Calculations จะขอให้คุณอัปโหลดเวิร์กบุ๊กก่อน และเสริมว่า: "ตรวจสอบให้แน่ใจว่าข้อมูลของคุณอยู่ในรูปแบบที่ถูกต้องเพื่อให้การคำนวณเป็นไปอย่างราบรื่น"
รักษาหนึ่งแถวต่อหนึ่งการสังเกตและหัวคอลัมน์ที่ชัดเจน AI จะใช้หัวคอลัมน์เพื่อทำความเข้าใจความหมายของแต่ละคอลัมน์
ขั้นตอนที่ 2: ขอการวิเคราะห์ด้วยภาษาธรรมชาติ
อธิบายสิ่งที่คุณต้องการทดสอบ ตัวอย่างเช่น: "รัน linear regression ของยอดขายเทียบกับเดือนและค่าโฆษณา และอธิบายว่าตัวไหนสำคัญกว่ากัน"
หน้าเดียวกันระบุว่าคุณสามารถ "ป้อนคำขอคำนวณที่คุณต้องการในรูปแบบข้อความ" ถามคำถามต่อเนื่องได้ เช่น ความสัมพันธ์ยังคงอยู่หรือไม่หากตัดเดือนที่เป็นค่าผิดปกติ (outlier) ออกไปหนึ่งเดือน
ขั้นตอนที่ 3: ตรวจสอบผลลัพธ์และสร้างแผนภูมิ
ตรวจสอบความชันเทียบกับ LINEST ในเวิร์กบุ๊กของคุณเอง จากนั้นแสดงความสัมพันธ์ด้วยภาพ Scatter Graph Creator ถูกสร้างขึ้นเพื่อ "แสดงภาพความสัมพันธ์ของข้อมูลด้วยแผนภูมิกระจายที่โต้ตอบได้และปรับแต่งได้"
สำหรับการพยากรณ์ที่มากกว่าเส้นตรง เครื่องมือ AI Forecasting จะสร้างแผนภูมิพยากรณ์พร้อมแถบความเชื่อมั่น สำหรับข้อมูลพื้นฐานเกี่ยวกับวิธีการนี้ โปรดดูคำอธิบายของเราเกี่ยวกับ การวิเคราะห์ regression
ข้อผิดพลาดทั่วไปที่ควรหลีกเลี่ยง
นี่คือข้อผิดพลาดที่เกิดขึ้นบ่อยที่สุดเมื่อผู้คนรัน regression ใน Excel
- สลับ X และ Y Y คือค่าที่คุณต้องการอธิบาย การสลับกันจะทำให้ความชันเปลี่ยนไป
- ลืมติ๊กช่อง Labels หากช่วงข้อมูลของคุณมีหัวตาราง ให้ติ๊ก Labels มิฉะนั้น Excel จะอ่านหัวตารางเป็นข้อมูล
- รัน regression ใน Excel สำหรับเว็บ Microsoft กล่าวว่าคุณ "ไม่สามารถสร้างการวิเคราะห์ regression" ที่นั่นได้ เนื่องจากไม่มีเครื่องมือ Regression ให้ใช้แอปบนเดสก์ท็อปแทน
- อ่านค่า R Square เพียงอย่างเดียว R Square ที่สูงไม่ได้พิสูจน์สาเหตุ และอาจซ่อนรูปแบบที่เป็นเส้นโค้งไว้ ให้ดูแผนภูมิกระจายเสมอ
- ใช้จำนวนแถวน้อยเกินไป จุดหกจุดอาจสร้างเส้นที่ดูดีได้โดยบังเอิญ ข้อมูลที่มากขึ้นจะให้ผลลัพธ์ที่น่าเชื่อถือกว่า
- การพยากรณ์ไกลเกินไป เส้นที่พอดีกับเดือนที่ 1 ถึง 6 อาจใช้ไม่ได้กับเดือนที่ 24
- ละเลยค่าผิดปกติ (outliers) เดือนที่ผิดปกติเพียงเดือนเดียวสามารถดึงเส้นทั้งเส้นได้ ตรวจสอบแผนภูมิเพื่อหาจุดที่อยู่ห่างจากจุดอื่นๆ
- เพิ่มคอลัมน์ X ที่สัมพันธ์กันมากเกินไป อินพุตที่เคลื่อนที่ไปพร้อมกันทำให้ coefficients ไม่เสถียร Microsoft ระบุว่า LINEST "ตรวจสอบ collinearity" และลบคอลัมน์ X ที่ซ้ำซ้อนออก
เมื่อคุณพร้อมที่จะทดสอบความสัมพันธ์ในชุดข้อมูลทั้งหมด ลองใช้ Powerdrill Bloom กับตัวเลขรายเดือนของปีที่แล้ว
คำถามที่พบบ่อย
ฉันจะทำ simple linear regression ใน Excel ได้อย่างไร?
เปิดใช้งาน Analysis ToolPak ไปที่ Data > Data Analysis และเลือก Regression เลือกช่วง Y และ X ของคุณ ติ๊ก Labels หากคุณรวมหัวตารางไว้ด้วย แล้วเลือก OK คุณยังสามารถใช้ =LINEST(ช่วง Y, ช่วง X) ได้อีกด้วย
ฉันสามารถทำ regression ใน Excel สำหรับเว็บได้หรือไม่?
ไม่สามารถทำได้เต็มรูปแบบ Microsoft กล่าวว่าคุณสามารถดูผลลัพธ์ regression ใน Excel สำหรับเว็บได้ แต่คุณไม่สามารถสร้างการวิเคราะห์ regression ที่นั่นได้ ให้เปิดไฟล์ในแอปบนเดสก์ท็อปเพื่อรัน
R squared หมายถึงอะไรใน Excel regression?
R squared แสดงว่าเส้นนั้นอธิบายความผันแปรใน Y ได้มากน้อยเพียงใด มีค่าตั้งแต่ 0 ถึง 1 ค่า 0.93 หมายความว่าประมาณ 93% ของความผันแปรเป็นไปตามเส้นที่สร้างขึ้น
ฉันจะทำ multiple regression ใน Excel ได้อย่างไร?
ใส่ตัวแปร X แต่ละตัวในคอลัมน์ที่อยู่ติดกัน ในเครื่องมือ Regression ให้เลือกคอลัมน์ X ทั้งหมดเป็น Input X Range ฟังก์ชัน LINEST ก็รองรับคอลัมน์ X หลายคอลัมน์เช่นกัน
LINEST และเครื่องมือ Regression ต่างกันอย่างไร?
ทั้งคู่ให้ coefficients เดียวกัน Microsoft กล่าวว่าเครื่องมือ Regression ใช้ LINEST โดยเครื่องมือจะเขียนตารางสรุปผลฉบับเต็ม ในขณะที่ LINEST จะคืนค่าเป็นสูตรที่อัปเดตตามข้อมูลของคุณ
แหล่งที่มา: Microsoft Support, Use the Analysis ToolPak to perform complex data analysis · Microsoft Support, LINEST function · Microsoft Support, Perform a regression analysis · Microsoft Support, Add a trend or moving average line to a chart.