การใช้ Excel ช่วยประมาณค่าของช่วงความเชื่อมั่น (Confidence Interval : CI)

สวัสดีครับ บทความการใช้ Excel ฉบับนี้ ผมจะนำทุกท่านไปเรียนรู้ การประยุกต์ใช้ Excel ช่วยคำนวณทางสถิติ เกี่ยวกับช่วงความเชื่อมั่น (Confidence Interval) ซึ่งต่อไปนี้เราจะเรียกว่า CI ครับ ตัวอย่างที่ยกมาคือ สมมุติต้องการกำหนด Specification ของค่าเฉลี่ยสมบัติทางกลของ material ชนิดหนึ่งที่ได้พัฒนาขึ้น ซึ่งในความเป็นจริง ในกระบวนการผลิต material ชนิดนี้ จะมีความคลาดเคลื่อนจากหลายๆด้านที่ทำให้ ค่าเฉลี่ยสมบัติทางกลมีการกระจายตัวในแต่ละรอบของการผลิต โดยไม่มีทางทราบค่าของ population mean(mu) อ่านว่า มิว มาถึงตรงนี้หากวิศวกรต้องการกำหนดช่วงค่าเฉลี่ยของสมบัติทางกลของ material ชนิดนี้ในลักษณะของค่า tolerance  เพื่อใช้ในการควบคุมการผลิต เราจะใช้หลักการของ 95% confidence interval คือ ทุกครั้งที่มีการสุ่มวัดสมบัติทางกลของ material ค่าสถิติ ต่างๆ เช่น ค่าเฉลี่ยตัวอย่าง ค่าเบียงเบนมาตรฐาน ความแปรปรวน ค่า p value จะเปลี่ยนไปทุกครั้ง แต่หากกระบวนการผลิต material อยู่ภายใต้การควบคุม (process is control) หากเราสุ่มวัดสมบัติทางกลที่ได้จากการผลิต material 20 ครั้ง จะพบว่า 19 ใน 20 ครั้งจะสามารถคร่อมค่า mu ของสมบัติทางกล material ชนิดนี้ได้ ดังแสดงในภาพ ซึ่งนักสถิติจะเรียก 95% นี้ว่า Confidence Interval นั่นแปลว่า Confidence Interval เก็บค่า population mean(mu) ของ material ไว้ได้ ถึง 95% ดังนั้น เราจึงนำ Confidence Interval ไปกำหนดเป็น Specification ของค่าเฉลี่ยสมบัติทางกล material ชนิดนี้ได้ Excel มีเครื่องมือช่วยคำนวณ Confidence Interval โดยเป็นเครื่องมือใน Data Analysis ซึ่งเป็นเครื่องมือใน Analysis Toolpack (ดูขั้นตอนการติดตั้งได้ที่นี่)  เริ่มต้นจาก Data Analysis จะพบกับหน้าต่างนี้

1. ให้เลือก Descriptive Statistic กด OK จะปรากฎหน้าต่าง Descriptive Statistic ดังภาพ

ให้กำหนดค่าดังนี้

1.1 Input Range เป็น $C$1:$C$25

1.2 คลิกเลือก Label in Firstrow

1.3 ในกลุ่ม Output option 

      1.3.1 หัวข้อ Output Range เป็น D2

      1.3.2 เลือกหัวข้อ Summary statictics

      1.3.3 เลือกหัวข้อ Confidence Level for mean กำหนดเป็น 95 

1.4 กด OK Excel จะคำนวณค่าสถิติของข้อมูลกลุ่มนี้มาดังภาพ

แนวคิด 95% Confidence Level สามารถดูคำอธิบายเพิ่มเติมที่นี่ 

ภาพ หน้าต่าง Data Analysis

ภาพ การกำหนดเงื่อนไขในหน้าต่าง Descriptive Statistic

ผลการคำนวณ Descriptive Statistics

จากผลการคำนวณที่ได้เราสามารถหาค่า 95% Confidence Interval ได้ดังนี้

upper level = Mean + Confidence Level (95%) = 120.34 + 0.77 = 121.11

lower level = Mean - Confidence Level (95%) = 120.34 - 0.77 =  119.57

ดังนั้น 95% Confidence Interval ของค่าเฉลี่ยสมบัติทางกล material ชนิดนี้คือ [119.57 , 121.11]

สรุป ในบทความนี้ได้เสนอวิธีการใช้เครื่องมือใน Excel สำหรับช่วยคำนวณค่า Confidence Interval ซึ่งมักใช้ถูกใช้งานในทางวิศวกรรมอยุ่บ่อยครั้งทั้งใน งานวิจัยและงานควบคุมคุณภาพ ในบทความต่อๆไปเราจะนำการประยุกต์ใช้ Excel กับสถิติ เพื่อใช้กับงานทางวิษศวกรรมและวิจัยต่อไปนะครับ

การใช้ excel คำนวณค่าพยากรณ์โดยใช้ค่าเฉลี่ยเคลื่อนที่ซ้ำสองครั้ง(Double Moving Average)

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

หลักการคำนวณ

จะเริ่มต้นคำนวณค่าเฉลี่ยเคลื่อนที่แบบธรรมดาของข้อมูล N ชุดแรก ตามที่ได้นำเสนอไปแล้ว จากนั้นจึงนำค่าเฉลี่ยที่ได้ในช่วงเวลาที่ผ่านมาแล้วคำนวณหาค่าเฉลี่ยอีกครั้ง ดังนั้นค่าพยากรณ์แบบค่าเฉลี่ยเคลื่อนที่ซ้ำสองครั้งจะต้องมีข้อมูลก่อนหน้าเท่ากับ 2N เมื่อ N คือจำนวนข้อมูลที่ต้องใช้คำนวณค่าพยากรณ์แบบค่าเฉลี่ยเคลื่อนที่ธรรมดา เช่น ค่าเฉลี่ยเคลื่อนที่แบบ 3 เดือน จะต้องใช้ข้อมูลย้อนหลัง 6 เดือนเพื่อให้สามารถพยากรณ์ได้ ดังแสดงในภาพที่ 1

ภาพที่ 1 สมการการคำนวณค่าพยากรณ์

มาดูตัวอย่างการใช้ excel ช่วยคำนวณค่าพยากรณ์ สมมุติเรามีข้อมูลยอดขายประจำสัปดาห์จริง 10 สัปดาห์ หากเราต้องการพยากรณ์ยอดขายสัปดาห์ที่ 11 โดยใช้วิธีค่าเฉลี่ยเคลื่อนที่ซ้ำสองครั้งแบบ 4 สัปดาห์ กำหนดให้หลัก B เป็นหลักที่กำหนดปริมาณยอดขายตามสัปดาห์ต่างๆ ดังแสดงในภาพที่ 2 

คำนวณค่าเฉลี่ยเคลื่อนที่แบบธรรมดาในหลัก C ตั้งแต่เซลล์ C8 (สัปดาห์ที่ 4 เป็นต้นไป) โดยใช้สูตร excel : AVERAGE(OFFSET($B$5,COUNT($B$5:B8)-$D$1,0,$D$1,1))

คำนวณค่าเฉลี่ยเคลื่อนที่ซ้ำครั้งที่สองในหลัก D ตั้งแต่เซลล์ D11 (สัปดาห์ที่ 7 เป็นต้นไป) โดยใช้สูตร excel : AVERAGE(C8:C11)

คำนวณค่าคงที่ a และ b ในหลัก E และ F ตั้งแต่แถวที่ 11 (สัปดาห์ที่ 7 เป็นต้นไป) โดยใช้สูตร excel :

E11 = 2*C11 - D11

F11 = 2*(C11 - D11)/($D$1)

สามารถคำนวณค่าพยากรณ์ได้ตั้งแต่สัปดาห์ที่ 8  โดยใช้สูตร excel : 

G12 = E11 + F11     (ให้ m = 1 หมายถึงช่วงเวลาที่นับจากเวลาปัจจุบันไป 1 สัปดาห์  )

หากต้องการค่าพยากรณ์ในสัปดาห์ที่ 13 โดยใช้ข้อมูลการคำนวณในสัปดาห์ที่ 10 ( m = 3) จะเขียนสูตร excel ได้เป็น E14 +3*F14

จากสูตร excel ที่คำนวณได้เราสามารถแสดงค่าพยากรณ์ยอดขายเดือนที่ 11 ได้ดังภาพที่ 2 

ภาพที่ 2 ผลการคำนวณค่าพยากรณ์ยอดขาย

เราสามารถ copy สูตร excel ในหลัก C14 , D14 , E14 ,F14 และ G15 เพื่อคำนวณค่าพยากรณ์ยอดขายในสัปดาห์ที่ 12 ได้ โดยวางสูตร excel ที่ copy มาลงในแถวถัดไป 

จากการนำเสนอการคำนวณค่าพยากรณ์ด้วยค่าเฉลี่ยเคลื่อนที่แบบธรรมดาและแบบซ้ำสองครั้งนั้นจะพบว่าข้อเสียของการคำนวณด้วยวิธีนี้คือ ต้องการจำนวนข้อมูลที่ค่อนข้างมากจึงจะพยากรณ์ได้ใกล้เคียง โดยพบว่าจำนวนข้อมูลย้อนหลังเท่ากับ 4 ให้ค่าพยากรณ์ที่แม่นยำขึ้น แต่นั่นหมายถึงหากต้องพยากรณ์ด้วยค่าเฉลี่ยเคลื่อนที่ซ้ำสองครั้งจะต้องใช้ข้อมูลย้อนหลังมากถึง 8 ข้อมูล ในบทความต่อๆไปเราจะนำเสนอวิธีการคำนวณค่าพยากรณ์ที่มีประสิทธิภาพมากกว่าค่าเฉลี่ยเคลื่อนที่นั่นคือ การปรับเรียบ exponential 

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

การใช้ excel คำนวณค่าเฉลี่ยเคลื่อนที่ (Moving average) เพื่อใช้ในการพยากรณ์

ค่าเฉลี่ยเคลื่อนที่เป็นเทคนิคการพยากรณ์ที่นิยมใช้กันมากในการพยากรณ์ปริมาณต่างๆที่สนใจ เช่น แนวโน้มการตลาดของยอดขายสินค้าหรือราคาวัตถุดิบ ในช่วงสั้นๆ เช่น อาจจะเป็นสัปดาห์หรือ เดือน เป็นต้น โดยวิธีการพยากรณ์ด้วยค่าเฉลี่ยเคลื่อนที่จะไม่ถือว่าเป็นวิธีการทางสถิติ แต่จะเป็นการกำหนดขึ้นโดยอาศัยหลักเกณฑ์แบบง่าย คือ การนำค่าข้อมูลที่เกิดขึ้นจริงในช่วงเวลาที่ผ่านมาคำนวณค่าเฉลี่ยและนำค่าเฉลี่ยที่คำนวณได้ไปเป็นค่าพยากรณ์ในช่วงเวลาถัดไป โดยตัวแปรสำคัญของ ค่าเฉลี่ยเคลื่อนที่คือ จำนวนข้อมูลที่เกิดขึ้นจริงที่นำมาคำนวณ (N) โดยเราจะเรียกค่าเฉลี่ยเคลื่อนที่ตามจำนวนข้อมูลที่นำมาคำนวณเช่น ค่าเฉลี่ยเคลื่อนที่แบบ 3 เดือน จะหมายถึงค่าพยากรณ์แบบที่ใช้ข้อมูลจริงจำนวน 3 เดือนย้อนหลังมาคำนวณ เป็นต้น ดังนั้นจากที่กล่าวมาขอสรุปลักษณะของการพยากรณ์ค่าเฉลี่ยเคลื่อนที่ไว้เป็น 2 ข้อคือ

1. ก่อนจะพยากรณ์ ผู้คำนวณต้องเตรียมข้อมูลในช่วงเวลาที่ผ่านมาให้มีจำนวนเท่ากับที่ต้องการก่อน เช่น หากต้องการพยากรณ์ด้วยค่าเฉลี่ยแบบ 3 เดือน เราจะทำการพยากรณ์ข้อมูลได้ตั้งแต่เดือนที่ 4 เป็นต้นไป
2. ค่าการพยากรณ์จะมีความเรียบมากขึ้นหากใช้จำนวนข้อมูลมากขึ้น

ในการคำนวณค่าเฉลี่ยเคลื่อนที่ เราจะใช้ excel ช่วยในการคำนวณเพื่อความสะดวกและถูกต้องของค่าการพยากรณ์ โดยในบทความนี้ขอยกตัวอย่างข้อมูลราคาสินค้าชนิดหนึ่งที่มีการสังเกตการไว้ สมมุติให้ข้อมูลที่เกิดขึ้นจริง 4 เดือนแรกอยู่ในหลัก B โดยเริ่มต้นที่ B3 ดังแสดงในภาพที่ 1 เราต้องการคำนวณค่าพยากรณ์ราคาสินค้าในเวลาถัดไปทุกครั้งที่มีการเพิ่มข้อมูลที่เกิดขึ้นจริงในหลัก B ซึ่งจากข้อกำหนดดังกล่าวมันเป็นการคำนวณแบบไดนามิกส์ สมมุติรูปแบบการจัดใน worksheet แสดงดังภาพที่ 1

โดยกำหนดสูตร excel : COUNT เพื่อช่วยนับจำนวนข้อมูลจริงในหลัก B และแสดงผลในเซลล์ C3 ในขณะที่ผู้ใช้จะกำหนดจำนวนเดือนเพื่อใช้คำนวณค่าเฉลี่ยเคลื่อนที่ได้ในเซลล์ที่ C2 

ภาพที่ 1 รูปแบบการแสดงผลข้อมูลใน excel 

สูตร excel สำหรับคำนวณค่าเฉลี่ยเคลื่อนที่

วิธีคำนวณ

• ใช้สูตร excel : AVERAGE เพื่อคำนวณค่าเฉลี่ย
• ใช้สูตร excel : OFFSET เพื่อนำข้อมูลส่งไปคำนวณค่าเฉลี่ย โดยจะใช้ข้อมูลย้อนหลังไปเป็นจำนวนเดือนที่กำหนดไว้ในเซลล์ C2 (การกำหนดข้อมูลแบบไดนามิกส์ผู้อ่านสามารถศึกษาในบทความ Excel tips การกำหนดข้อมูลแบบพลวัตรได้)

ดังนั้นสูตร excel ในเซลล์ D7 คือ =AVERAGE(OFFSET($B$6,C3-C2,0,C2,1))

ผลการคำนวณด้วย excel แสดงได้ดังภาพที่ 2 โดยค่าพยากรณ์ที่ได้จะเป็นค่าพยากรณ์ของเดือนเมษายน 55

ภาพที่ 2 ผลการคำนวณด้วย excel

หากท่านผู้อ่านเพิ่มราคาสินค้าจริงของเดือนเมษาลงไป ค่าพยากรณ์ใน D7 ก็จะเปลี่ยนไป โดยจะนำเอาราคาสินค้าเดือน กุมภาพันธ์- เมษายน มาคำนวณ โดยค่าพยากรณ์ที่ได้จะเป็นของเดือน พฤษภาคม ดังแสดงในภาพที่ 3

ภาพที่ 3 ผลการคำนวณหลังเพิ่มข้อมูล

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