วันเสาร์ที่ 25 สิงหาคม พ.ศ. 2555

การใช้ excel สร้างกราฟเส้นตรง (Linear graph)

บทความการใช้ excel ฉบับนี้ ขอนำเสนอเรื่องการสร้างกราฟเส้นตรงครับ กราฟเส้นตรงเป็นการแสดงความสัมพันธ์ระหว่างปริมาณสองปริมาณที่มีการเปลี่ยนแปลงอย่างคงที่ตลอด โดยทั่วไปกราฟเส้นตรงจะประกอบด้วยแกนตั้งและแกนนอน โดยปกติแกนตั้งจะแสดงถึงตัวแปรตาม แกนนอนจะแสดงถึงตัวแปรอิสระ รูปแบบมาตรฐานของ กราฟเส้นตรงหรือสมการเส้นตรง สามารถเขียนได้เป็น
Ax + By + C = 0
เราสามารถเขียนรูปแบบสมการเส้นตรงใหม่เพื่อให้ง่ายต่อการเข้าใจเป็น
y = mx + c ; เมื่อ m คือความชันของกราฟเส้นตรง c คือจุดตัดบนแกน y (x = 0)
สรุปได้ว่า กราฟเส้นตรงจะประกอบไปด้วย ค่าความชันและค่าจุดตัดบนแกน y โดยจะมีกรณีพิเศษเช่น
หาก c = 0 (m ไม่เท่ากับ 0) เราจะได้กราฟเส้นตรงที่ลากผ่านจุดกำเนิด (0,0)
หาก m = 0 (c ไม่เท่ากับ 0) เราจะได้กราฟเส้นตรงที่ขนานกับแกน x ตัดแกน y ที่จุด c
หาก m เป็น infinity เราจะได้กราฟเส้นตรงที่ขนานกับแกน y ตัดแกน x ที่จุด C
นอกจากนี้เรายังสามารถคำนวณมุมระหว่างกราฟเส้นตรงและแกน x ได้จาก มุม = atan(m)
เรามาดูว่าจะใช้ excel กับกราฟเส้นตรงหรือสมการเส้นตรงได้ในกรณีใดบ้าง
1. อยากทราบรูปแบบของสมการเส้นตรงเมื่อมีพิกัดบนกราฟเส้นตรงใดๆ
จากสมการเส้นตรงที่ผมนำเสนอไป จะพบว่าค่าคงที่ m และ c ของสมการเส้นตรงหาได้ โดยหากเราทราบพิกัดใดๆบนกราฟเส้นตรงนั้นจำนวน 2 จุด ดังนั้นหากเรามีพิกัด (x , y) 2 จุด เราสามารถใช้ excel ช่วยคำนวณหาความชัน (Slope) และจุดตัดบนแกน y ของเส้นตรงนั้นได้ มาดูตัวอย่างกันครับ
สมมุติเรามีพิกัด 2 จุด ซึ่งอยู่บนกราฟเส้นตรงใดๆ แสดงอยู่ในเซลล์ C3:D4
  1. ความชันของเส้นตรงจะแสดงที่เซลล์ C5 สามารถหาได้จากสูตร excel  C5 = SLOPE(D3:D4,C3:C4)
  2. จุดตัดบนแกน y แสดงที่เซลล์ C6 หาได้จากสูตร excel  C6 = INTERCEPT(D3:D4,C3:C4)
  3. เมื่อได้ค่าคงที่ของสมการเส้นตรงแล้ว เราสามารถคำนวณตัวแปรตาม y ซึ่งขึ้นกับ ตัวแปรอิสระ x ใดๆ(C8) ได้ในเซลล์ D8 โดยเขียนสูตร excel : D8 = $C$5*C8 + $C$6
ผลการรันสูตร excel แสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการรันสูตร excel


2. หากมีข้อมูลจำนวนมาก อยากเห็นรูปร่างการกระจายข้อมูลและเส้นแนวโน้มของ กราฟเส้นตรง
การใช้ excel สร้างกราฟเส้นตรง เราจะเริ่มจากการมีข้อมูลพิกัด x , y หลายๆจุด สมมุติให้ x และ y อยู่ในหลัก B และ C ให้ทำการพล็อตจุดข้อมูลเหล่านั้นโดยใช้การพล็อตกราฟแบบกระจาย (Scatter) ดังภาพที่ 2 เพื่อวิเคราะห์ลักษณะแนวโน้มของข้อมูล


ภาพที่ 2 การกระจายของข้อมูล
ใช้เราสามารถแก้ไขสมบัติของการพล็อตกราฟเพื่อแสดงเส้นแนวโน้มและสมการเส้นตรงที่เป้นตัวแทนของการกระจายข้อมูลเหล่านี้ได้ดังนี้
  1. คลิกขวาที่จุดข้อมูล และให้เลือก เพิ่มเส้นแนวโน้ม....(Add Trend Line...)
  2. จะปรากฎหน้าต่าง จัดรูปแบบเส้นแนวโน้ม ให้คลิกเลือก ตัวเลือกเส้นแนวโน้มในรายการด้านซ้าย ในรายการด้านขวาให้เลือก เชิงเส้น เป็นชนิดของแนวโน้ม/การถดถอย เลือกให้แสดงสมการเส้นบนแผนภูมิและแสดงค่า R-Squared บนแผนภูมิ ดังแสดงในภาพที่ 3
ท่านผู้อ่านสามารถปรับรูปแบบสีเส้นและลักษณะเส้นแนวโน้มได้โดยเลือกรายการด้านซ้าย คลิกปุ่ม ปิดหน้าต่าง จะปรากฎเส้นแนวโน้มเป็นกราฟเส้นตรงเพิ่มขึ้นในพื้นที่พล็อตกราฟ รวมถึงรูปแบบของสมการเส้นตรงและค่า R-Squared ดังแสดงในภาพที่ 4


ภาพที่ 3 การปรับเลือกเส้นแนวโน้มใน excel


ภาพที่ 4 กราฟเส้นตรงที่ได้จากการประมาณของข้อมูล

จากภาพที่ 4 เราจะเห็นว่าสมการเส้นตรงที่ได้ มีความชันเท่ากับ 0.067 และตัดแกน y ที่ y = 1.423 ส่วนค่า R-Squared เป็นสิ่งที่นักวิเคราะห์ต้องคำนึงถึง โดยหากค่า R มีค่าเข้าใกล้ 1 มากๆจะหมายถึงว่าความสัมพันธ์ระหว่าง y และ x เป็นแบบเชิงเส้นมาก นั้นหมายถึงว่าเราสามารถใช้ สมการเส้นตรงที่ได้จากกราฟเส้นตรงนี้พยากรณ์ตัวแปร y ได้ หากเราทราบตัวแปรอิสระ x โดยทั่วไป R-Squared ควรอยู่ประมาณ 0.9 - 1 ครับ 

3. เมื่อทราบว่าพิกัดข้อมูลที่มีอยู่มีแนวโน้มเป็นเส้นตรง จะคำนวณค่าตัวแปร y จากตัวแปร x ที่กำหนดให้

กรณีนี้สมมุติเรามีข้อมูล x , y ในหลัก Bและ C และเมื่อใช้ Scatter plot การกระจายแล้วพบว่ามีแนวโน้มเป็นเส้นตรง ดังภาพที่ 2 เราสามารถคำนวณหาค่า y เมื่อกำหนดตัวแปร x ได้ โดยใช้ สูตร excel : TREND โดยสูตร excel นี้มีรูปแบบดังนี้
TREND(y_known,x_known,x_new,const) โดยที่
y_known คือชุดข้อมูลพิกัดแกน y ที่ทราบค่า
x_known คือชุดข้อมูลพิกัดแกน x ที่ทราบค่า
x_new คือค่าตัวแปร x ที่ต้องการคำนวณค่า y
const คือตัวแปรเงื่อนไข หากกำหนดเป็น FALSE จะกำหนดให้ สมการเส้นตรงเป็น y = mx หากกำหนดเป็น TRUE จะกำหนดให้สมการเส้นตรงเป็น y = mx+c

สมมุติว่าเราต้องการทราบค่า y ที่ x = 11.5 จากการกระจายตัวแบบเส้นตรงของกลุ่มข้อมูลในหลัก B และ C เราสามารถเขียนสูตร excel ได้ดังนี้

= TREND(C5:C24,B5:B24,11,TRUE)  จะได้ค่า y ที่ x = 11 เป็น 2.162 เป็นต้น

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

บทความที่เกี่ยวข้อง








4 ความคิดเห็น:

kv กล่าวว่า...

จะใส่พวก exponential ได้มั้ยครับ แล้วใส่ยังไง

utid กล่าวว่า...

สำหรับสูตร trend ใช้ได้เฉพาะสมการเชิงเส้นครับ แต่หากเป็น exponential คงต้องประยุกต์สูตรกันนิดหน่อย แต่ต้องดูรูปแบบของสมการ exponential ก่อนครับ

utid กล่าวว่า...

ผมได้เขียนบทความสำหรับตอบโจทย์ที่คุณ kv ได้ถามมาแล้วนะครับ

utid กล่าวว่า...

ตาม Link นี้ได้เลยครับ
http://learning-be.blogspot.com/2013/03/excel-1.html

แสดงความคิดเห็น

Yahoo bot last visit powered by  Ybotvisit.com