การใช โปรแกรมส าเร จร ปเพ อการพยากรณ

(1)

การใช้ โปรแกรมส าเร็จรูปเพื่อการพยากรณ์

สารบัญ

หน้า การใช้ Excel ในงานด้านสถิติ 3 การเพิ่ม Add-In ของ MS Office 2007 11 การหาค่าสถิติเชิงพรรณนา โดย Excel 13 การใช้ Excel ในการพยากรณ์ 16

การพยากรณ์แบบ Simple Moving Average 16

การพยากรณ์แบบ Exponential Smoothing 19

(2)

การพยากรณ์ สามารถ ช่วยใน ด้านการปรับปรุงประสิทธิภาพของระบบ งาน ซึ่งอาจกล่าวได้ว่าการ พยากรณ์เชิงปริมาณเป็นเครื่องมือทางด้านสถิติเพื่อการท างานด้านวิศวกรรม โดยเฉพาะการใช้การพยากรณ์เพื่อ การวางแผนการผลิต ซึ่งในที่นี้จะกล่าวถึงการใช้โปรแกรมส าเร็จรูป Microsoft Excel เพื่อการพยากรณ์

การใช้ Excel ในงานด้านสถิติ

เป็นการหาค่าทางสถิติขั้นพื้นฐานของวิชาสถิติโดยทั่วในใน Excel สามารถท าได้ง่ายโดยการใช้ค าสั่ง หรือข้อมูลเพียงไม่กี่อย่างดังรายละเอียดจะแสดงให้เห็นดังต่อไปนี้ รูปที่ 1 แสดงข้อมูลดิบของคะแนนรายวิชาของนักศึกษา จากรูปที่ 1 ให้นักศึกษาหาค่า เฉลี่ย ค่ามากที่สุด ค่าน้อยที่สุด ค่าความแปรปรวน ค่าส่วนเบี่ยงเบน มาตรฐาน ของข้อมูลนั้น ค่าเฉลี่ยตามสูตรทางสถิติคือ 

_

i i x n x 1 การใช้ Excel ในการหาค่าเฉลี่ยสามารถท าได้ง่ายโดยขั้นตอนดังต่อไปนี้

(3)

1) น า Cursor ไปไว้ที่ เซลล์ที่ต้องการจะวางค่าเฉลี่ยไว้ในที่นี้สมมติให้ไปเซลล์ D22 ตามที่แสดงใน รูป 2

(4)

2) ไปที่ เมนูบาร์ แทรก แล้วไปที่ ฟังก์ชั่น ตามที่แสดงไว้ในรูป 3

(5)

3) หลังจากคลิก ที่ฟังก์ชั่นแล้วจะปรากฏกล่องโต้ตอบขึ้น ตามภาพที่ 4

รูปที่ 4 แสดงขั้นตอนที่ 3 ของวิธีการหาค่าเฉลี่ย

4) เลือกประเภทของ ฟังก์ชั่นที่จะใช้เป็น ทางสถิติ เพื่อเข้าไปใช้ Function ทางสถิติดังปรากฏในรูป 5

(6)

5) เลือกฟังก์ชั่น AVERAGE เพื่อหาค่าเฉลี่ย การเลือกฟังก์ชั่น AVERAGE สามารถท าได้โดยการคลิก ที่ฟังก์ชั่น AVERAGE แล้วคลิกปุ่ม ตกลง หรือ การดับเบิ้ลคลิก ที่ฟังก์ชั่น AVERAGE ดังแสดงในรูป 6 รูปที่ 6 แสดงขั้นตอนที่ 5 ของวิธีการหาค่าเฉลี่ย 6) หลังจากเลือกฟังก์ชั่น AVERAGE แล้วจะปรากฏหน้าต่างโต้ตอบดังปรากฏในรูป 7 รูปที่ 7 แสดงขั้นตอนที่ 6 ของวิธีการหาค่าเฉลี่ย Number 1 ให้กรอกช่วงของเซลล์ที่จะท าการหาค่าเฉลี่ย เช่น ตามตัวอย่างเซลล์ที่จะหาค่าเฉลี่ยคือเซลล์ หมายเลข D3 ถึง เซลล์หมายเลข D20 ค าว่าถึงใน Excel ใช้เครื่องหมาย : เป็นตัวแทน หลังจากเลือกค่าขอบเขต หรือช่วงของข้อมูลแล้วโปรแกรมจะท างานอัตโนมัติโดยการน าค่าเฉลี่ยที่ได้ไปวางไว้ในเซลล์ที่ระบุไว้ก่อน

(7)

หน้านี้ (ในขั้นตอนที่ 1) แต่ในกล่องโต้ตอบจะแสดงผลการค านวณให้ทราบด้วยซึ่งจะอยู่ด้านล่างของกล่อง โต้ตอบ ที่ปรากฏค าว่า ผลลัพธ์จากสูตร ในที่นี้ผลลัพธ์มีค่า 61.677777778 ตามรูปที่ 8 รูปที่ 8 แสดงผลการค านวณค่าเฉลี่ยจากการใช้ Excel การใช้ Excel ในการหาสูงสุดสามารถท าได้ง่ายโดยขั้นตอนดังต่อไปนี้ ) max(X_i Y  1) ด าเนินตามขั้นตอน 1-3 เหมือนกับการหาค่าเฉลี่ย 2) ด าเนินการตามขั้นตอนที่ 2 นี้แทนขั้นตอนที่ 4 ของการหาค่าเฉลี่ย แทนที่จะเลือก AVERAGE ให้ นักศึกษาเลือก MAX แล้วจะปรากฏแถบให้เลือกช่วงของข้อมูลอาจจะท าได้โดยการพิมพ์ D3:D20 หรือจะใช้ การลากเมาส์ค้างจากเซลล์ D3 ถึง เซลล์ D20 จากนั้นค่าของข้อมูลจะไปปรากฏในเซลล์ที่เลือกไว้ ในที่นี้คือ D23 ค าตอบ

(8)

รูปที่ 9 แสดงผลการค านวณค่าสูงสุดจากการใช้ Excel การใช้ Excel ในการหาต ่าที่สุดสามารถท าได้ง่ายโดยขั้นตอนเดียวกับการหาค่าสูงสุดแต่แทนที่จะเลือก MAX ให้เลือก MIN แทน ) min(Xi Y  การใช้ Excel ในการหาค่าความแปรปรวน สามารถท าได้ง่ายโดยขั้นตอนเดียวกับการหาค่าสูงสุดแต่ แทนที่จะเลือก MAX ให้เลือก VAR แทน ตามโจทย์ที่ถามจะได้ค าตอบดังแสดงได้ดังรูปที่ 10

(9)

รูปที่ 10 แสดงผลการค านวณค่าความแปรปรวนจากการใช้ Excel นอกจากนี้การค านวณคณิตศาสตร์พื้นฐาน เช่น การบวก ลบ คูณ และหาร ให้ปฏิบัติดังนี้

1) น า Cursor ไปไว้ที่ช่องที่ต้องการ

รูปที่ 11 วิธีการเริ่มค านวณคณิตศาสตร์พื้นฐานโดยใช้ Excel 1

(10)

2) หาผลรวม จากฟังก์ชั่นที่มีอยู่แล้ว เลือกช่วงข้อมูลที่ต้องการหา แล้วจึง Enter

รูปที่ 12 การใช้ฟังก์ชั่นของ Excelในการค านวณคณิตศาสตร์พื้นฐาน

3) เขียนสูตรค านวณ ให้น า Cursor ไปไว้ที่ช่องที่ต้องการ แล้วจึงน าไปที่ช่อง fx เขียนสูตร แล้ว Enter

รูปที่ 13 การเขียนสูตรเพื่อให้ Excelค านวณคณิตศาสตร์พื้นฐาน

นอกจากการค านวณทางคณิตศาสตร์พื้นฐานแล้ว Excel ยังสามารถช่วยหาค่า descriptive statistics หรือ สถิติเชิงพรรณนา และการวิเคราะห์ทางวิศวกรรม ได้ด้วยการใช้เมนู เครื่องมือ หรือ ข้อมูล > data analysis ถ้า หากไม่มี data analysis ต้องท าการ add-in ตามขั้นตอนในหัวข้อต่อไป

1

(11)

การเพิ่ม Add-In ของ MS Office 2007

ถ้าจ าเป็นต้องด าเนินการวิเคราะห์ทางสถิติหรือวิศวกรรมที่ซับซ้อน สามารถประหยัดขั้นตอนและเวลา ได้โดยใช้ Analysis ToolPak โดย Analysis ToolPak ประกอบด้วยเครื่องมือ ต่าง ๆ เช่น Anova, Correlation, Covariance, Descriptive Statistics, Exponential Smoothing, F-Test Two-Sample for Variances, Fourier Analysis, Histogram, Moving Average, Random Number Generation, Rank and Percentile, Regression, Sampling, t-Test, และ z-Test

เมื่อต้องการเข้าถึงเครื่องมือเหล่านี้ ให้คลิก Data Analysis ในกลุ่มวิเคราะห์บนแท็บข้อมูล ถ้าค าสั่ง Data Analysis ไม่พร้อมให้ใช้งาน จ าเป็นต้องโหลดโปรแกรม Add-in ของ Analysis ToolPak ซึ่งโปรแกรม Add-In เป็นโปรแกรมเสริมที่ทาง Microsoft สร้างขึ้นมาเพื่อเพิ่มค าสั่ง และความสามารถให้กับโปรแกรม ซึ่ง โดยปกติจะไม่ได้ถูก Install เข้ามาพร้อมกับตอนที่ท าการติดตั้งโปรแกรม จะต้องท าการติดตั้งเพิ่มเติมใน ภายหลัง ในที่นี้จะแสดงวิธีการเพิ่ม Add-In ของโปรแกรม MS Excel 2007

การโหลด Analysis ToolPak

1. คลิก ปุ่ม Microsoft Office จากนั้นคลิก ตัวเลือกของ Excel 2. คลิก Add-in แล้วในกล่อง ผสาน ให้เลือก Add-in ของ Excel

3. คลิก ไปในกล่อง Add-in ที่มีอยู่ ให้เลือกกล่องกาเครื่องหมาย Analysis ToolPak แล้วคลิก ตกลง 4. ถ้า Analysis ToolPak ไม่อยู่ในกล่อง Add-in ที่มีอยู่ ให้คลิก เรียกดู เพื่อระบุต าแหน่ง Analysis ToolPak 5. ถ้าได้รับการเตือนว่ายังไม่ได้ติดตั้ง Analysis ToolPak ลงในเครื่องคอมพิวเตอร์ ให้คลิก ใช่ เพื่อ ติดตั้ง Analysis ToolPak 6. ฟังก์ชั่นการวิเคราะห์ข้อมูลสามารถน ามาใช้บนแผ่นงานเพียงหนึ่งแผ่นในแต่ละครั้ง เมื่อท าการ วิเคราะห์ข้อมูลกับแผ่นงานที่จัดกลุ่ม ผลลัพธ์จะปรากฏที่แผ่นงานแรก และตารางที่จัดรูปแบบว่างจะปรากฏบน แผ่นงานที่เหลือ เมื่อต้องการวิเคราะห์ข้อมูลในแผ่นงานส่วนที่เหลือ ให้ใช้เครื่องมือวิเคราะห์ค านวณแผ่นงาน แต่ละแผ่นใหม่อีกครั้ง

(12)

รูปที่ 14 แสดงเมนูของการ add-in ฟังก์ชันพิเศษ

จากนั้น ท าการ Click ที่ช่องว่างตามหัวข้อที่ต้องการ add ในที่นี้นี้จะ add Analysis ToolPak แล้วเครื่อง จะท าการ ติดตั้งให้โดยอัตโนมัติ ก็จะสามารถใช้เมนู data analysis ได้ 1 2 3 4 5 6

(13)

การหาค่าสถิติเชิงพรรณนา โดย Excel

1. เลือก เมนู เครื่องมือ หรือ ข้อมูล > data analysis จะปรากฏแถบเครื่องมือดังรูป 15

รูปที่ 15 แสดงขั้นตอนที่ 1 ของการใช้ data analysis หา descriptive statistic 2. จากนั้นเลือกแถบ descriptive statistics แล้ว Click OK จะปรากฏแถบเครื่องมือดังรูป 16

(14)

3. Input range หมายถึงค่าของข้อมูลดิบที่ต้องการหาค่า descriptive statistics ซึ่งสามารถระบุได้ด้วย การลากเมาส์ค้างจาก ช่อง D3 ถึง D20

4. Output range หมายถึงช่วงของเซลล์ที่ต้องการให้ผลของการค านวณไปปรากฏ จะมีสามช่องให้ เลือก Output range ผลลัพธ์จะแสดงใน work sheet เดียวกันตามเซลล์ที่ระบุไว้ New Worksheet Ply ผลลัพธ์จะ สร้าง worksheet ใหม่และจะไปปรากฏในนั้นตามเซลล์ที่ระบุ และ New workbook ผลลัพธ์จะไปปรากฏใน workbook ใหม่ หรือ file ใหม่ 5. คลิกข้อมูลที่อยากทราบใน descriptive statistics โดยค่าต่าง ๆ ที่จะแสดงมีดังต่อไปนี้ Mean ค่าเฉลี่ยของกลุ่มตัวอย่าง Standard error n S SE  Median ส่งกลับค่ามัธยฐานหรือจ านวนที่อยู่ตรงกลางของชุดจ านวนที่ระบุ นั่นหมายถึง จ านวน ครึ่งหนึ่งมีค่ามากกว่าค่ามัธยฐาน และอีกครึ่งหนึ่งของจ านวนมีค่าน้อยกว่าค่ามัธยฐาน Mode ส่งกลับค่าฐานนิยม (ค่าเกิดขึ้น หรือท าซ ้าบ่อยที่สุด) ในอาร์เรย์ หรือช่วงของข้อมูล ฟังก์ชัน MODE เป็นการวัดต าแหน่งเช่นเดียวกับ MEDIAN Sample Variance _{ประมาณค่าความแปรปรวนจากค่าตัวอย่าง} Kutosis ส่งกลับค่าเคอร์โทซิส (kurtosis) ของชุดข้อมูล เคอร์โทซิสจะให้ลักษณะความสูงหรือ ความกว้างของการแจกแจงที่สอดคล้องเมื่อเปรียบเทียบกับการแจกแจงธรรมดา โดย เคอร์โทซิสเชิงบวกระบุถึงการแจกแจงแนวสูงที่สอดคล้อง และเคอร์โทซิสเชิงลบจะ ระบุถึงการแจกแจงแนวกว้างที่สอดคล้อง Skewness ส่งกลับค่าความเบ้ของการแจกแจง ความเบ้แสดงลักษณะระดับของความไม่สมมาตร ของการแจกแจงรอบ ๆ ค่าเฉลี่ย ความเบ้เชิงบวกแสดงถึงการแจกแจงที่มีด้านไม่ สมมาตรที่ขยายไปทางค่าที่เป็นบวกมากขึ้น ความเบ้เชิงลบแสดงถึงการแจกแจงที่มี ด้านไม่สมมาตรที่ขยายไปทางค่าที่เป็นลบมากขึ้น Range ความห่างของค่าต าสุดและสูงสุดของข้อมูลหนึ่งชุด

(15)

Mean ค่าเฉลี่ยของกลุ่มตัวอย่าง Minimum ส่งกลับตัวเลขน้อยสุดในชุดของค่าที่ระบุ Maximum ส่งกลับตัวเลขมากสุดในชุดของค่าที่ระบุ Sum ส่งกลับค่าผลรวมในชุดข้อมูลที่ระบุ Count นับจ านวนของเซลล์ที่มีตัวเลข รวมทั้งตัวเลขที่อยู่ภายในรายการอาร์กิวเมนต์ด้วย ให้ใช้ ฟังก์ชัน COUNT เพื่อนับจ านวนรายการข้อมูลในเขตข้อมูลตัวเลขที่มีค่าอยู่ในช่วงหรือ อาร์เรย์ของตัวเลขที่ระบุ นอกจากนี้ยังสามารถใช้แถบเครื่องมือเพื่อการค านวณต่าง ๆ ได้ตามรูป 17 รูปที่ 17 แสดงแถบเครื่องมือช่วยในการค านวณ

(16)

รูปที่ 18 แสดงผลการหา descriptive statistic ด้วย ฟังก์ชัน data analysis

การใช้ Excel ในการพยากรณ์

1. การพยากรณ์แบบ Simple Moving Average ล าดับขั้นการพยากรณ์ด้วย Excel

(17)

รูปที่ 19 แสดงขั้นตอนที่ 1 ของการพยากรณ์ด้วยวิธี moving average 2) เลือก เมนู เครื่องมือ > Data Analysis > Moving Average ดังรูป 20

รูปที่ 20 แสดงขั้นตอนที่ 2 ของการพยากรณ์ด้วยวิธี moving average 3) กดปุ่ม OK เพื่อด าเนินการต่อ หลังจากนั้นจะปรากฏแถบเครื่องมือดังรูป 21

(18)

รูปที่ 21 แสดงขั้นตอนที่ 3 ของการพยากรณ์ด้วยวิธี moving average

4) กรอกข้อมูล ลงในช่องว่าง Input Range หมายถึงช่วงของข้อมูลในอดีตที่ใช้เป็นข้อมูลเพื่อ พยากรณ์ในห้วงเวลาต่อไป ตามตัวอย่างจะกรอกช่วงข้อมูลอยู่ในช่วงข้อมูล D2 ถึง D21 และใช้ ค่า N หรือค่า คาบเวลาในการเฉลี่ยเท่ากับ 4 (interval) และค่า Output range ตั้งแต่ ค่า E2 ถึง E22 จะได้ค่าเฉลี่ย 4 ช่วงเวลาดัง ปรากฏในรูป 22

(19)

5) ค่าพยากรณ์คือ ค่าเฉลี่ยใน 1 คาบเวลาก่อนหน้าคาบเวลาที่จะท าการพยากรณ์เช่น ถ้า t =5 ค่า พยากรณ์จะเท่ากับค่าเฉลี่ยเมื่อ t=4 ท าเช่นเดียวกันจะได้ค่าพยากรณ์ทั้งหมดดังปรากฏในรูป 23

รูปที่ 23 แสดงขั้นตอนที่ 5 ของการพยากรณ์ด้วยวิธี moving average 2. การพยากรณ์แบบ Exponential Smoothing

(20)

รูปที่ 24 แสดงขั้นตอนที่ 1 ของการพยากรณ์ด้วยวิธี Exponential smoothing

2) เลือก เมนู เครื่องมือ > Data Analysis > Exponential smoothing ดังปรากฏตามรูป 25

(21)

3) กดปุ่ม OK เพื่อด าเนินการต่อ หลังจากนั้นจะปรากฏ ดังรูป 26

รูปที่ 26 แสดงขั้นตอนที่ 3 ของการพยากรณ์ด้วยวิธี Exponential smoothing

4) กรอกข้อมูล ลงในช่องว่าง Input Range หมายถึงช่วงของข้อมูลในอดีตที่ใช้เป็นข้อมูลเพื่อ พยากรณ์ในห้วงเวลาต่อไป ตามตัวอย่างจะกรอกช่วงข้อมูลอยู่ในช่วงข้อมูล B2 ถึง B12 และใช้ ค่า Damping Factor ซึ่งมีค่าเท่ากับ 1- ในสูตร Ft 



Dt1(1



)Ft1

ในที่นี้จะใช้ค่า  =0.2 หรือ ค่า Damping Factor เท่ากับ 0.8 และค่า Output range ตั้งแต่ ค่า C2 ถึง C12 และได้ผลตามรูป 27

(22)

3. การพยากรณ์ด้วย Regression Line

1) กรอกข้อมูล ลงในตาราง Excel ดังปรากฏในรูป 28

รูปที่ 28 แสดงขั้นตอนที่ 1 ของการพยากรณ์ด้วยวิธี Regression 2) เลือก เมนู เครื่องมือ > Data Analysis > Regression ดังปรากฏตาม รูป 29

(23)

3) กดปุ่ม OK เพื่อด าเนินการต่อ หลังจากนั้นจะปรากฏแถบเครื่องมือดังรูป 30

รูปที่ 30 แสดงขั้นตอนที่ 3 ของการพยากรณ์ด้วยวิธี Regression

4) กรอกข้อมูล ลงในช่องว่าง Input Y Range หมายถึงช่วงของข้อมูลในอดีตที่ใช้เป็นข้อมูลเพื่อ พยากรณ์ในห้วงเวลาต่อไป ตามตัวอย่างจะกรอกช่วงข้อมูลอยู่ในช่วงข้อมูล B2 ถึง B12 และ Input X Range หมายถึงช่วงของข้อมูลในอดีตที่ใช้เป็นข้อมูลอ้างอิงในพยากรณ์ในห้วงเวลาต่อไป ตามตัวอย่างจะกรอกช่วง ข้อมูลอยู่ในช่วงข้อมูล A2 ถึง A12 ส่วน output จะกรอกข้อมูลเพียงช่องเดียวเท่านั้น ส่วน residuals และกราฟ จะเป็นข้อมูลทางสถิติ ที่ต้องการรู้ หลังจาก กดปุ่ม OK แล้ว จะได้ผลดังรูป 31

(24)

รูปที่ 31 แสดงขั้นตอนที่ 4 ของการพยากรณ์ด้วยวิธี Regression และกราฟที่แสดง สามารถแสดงได้จากรูป 32 รูปที่ 32 แสดงกราฟที่ได้จากผลในขั้นตอนที่ 4 ของการ Excel ในการพยากรณ์ Multiple R = ค่าสัมประสิทธ์สหสัมพันธ์ R Square = ค่าสัมประสิทธ์การก าหนด Intercept = a X variable = b