การใช้ 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 กับสถิติ เพื่อใช้กับงานทางวิษศวกรรมและวิจัยต่อไปนะครับ

การใช้สูตร Convert ใน Excel

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

Convert(number , from_unit,to_unit) โดย

number คือ ตัวเลขที่ต้องการแปลงหน่วย

from_unit คือ หน่วยของตัวเลขปัจจุบัน

to_unit คือหน่วยที่ต้องการแปลง

สิ่งสำคัญคือหน่วยที่ต้องแปลงต้องสอดคล้องกัน ดังนั้นผู้ใช้ Excel ต้องจดจำหน่วยที่ Excel รองรับ ในแต่ละมิติ ตัวอย่างการใช้สูตร Excel : Convert แปลงหน่วยอุณหภูมิ ดังภาพที่ 1

ภาพที่ 1 การแปลงหน่วยอุณภูมิ

ตย หน่วยที่รองรับใน Excel

ความยาว  :  m , mi(ไมล์) ,in (นิ้ว) , ft (ฟุต)

ดวามดัน   : Pa , PSI , mmHg

แรง           : N , lbf , pound

กำลัง        : HP , W

ความเร็ว   : kn(knot) , m/s , mph

mass         : g , sg(slug) , lbm(poundmass) , ton

 

การประยุกต์ใช้สูตร Convert สำหรับแปลงหน่วยที่ซับซ้อนขึ้นจะแสดงในบทความต่อๆไปครับ

Excel Tips : การใช้ Data Validation เพื่อป้องกันการกรอกข้อมูลผิดรูปแบบ-1

สวัสดีครับท่านผู้อ่าน เรื่องของ Data Validation ผมได้นำเสนอมาหลากหลายทั้ง การป้องกันการบันทึกรายการข้อมูลซ้ำกันเหมาะสำหรับพวกการเก็บ Invoice หรือข้อมูลที่ต้องไม่ซ้ำกัน หรือวิธีการสร้างรายการข้อมูล(List) และการสร้างรายการที่มีรายการย่อย เป็นต้น ในบทนี้จะขอนำเสนอการใช้ Data Validation อีกตัวอย่างหนึ่งใน Excel นั่นคือ การป้องกันการกรอกข้อมูลผิดรูปแบบ ตัวอย่างคือ Code ต่างๆที่มีรูปแบบแน่นอน  สมมุติว่า เป็น Code วัตถุดิบที่ต้องนำเข้า โดยรูปแบบของ Code จะต้องขึ้นต้นด้วยตัวอักษร R และต่อท้ายด้วยตัวเลข  4 หลัก เช่น R0125 เป็นต้น ดังนั้น เราต้องใช้ Data Validation ใน Excel ช่วยป้องกันการกรอก Code ผิดรูปแบบ มาดูแนวคิดกันครับ การทำงานของ Excel ต้องตรวจสอบเงื่อนไข ดังนี้ครับ

อักขระตัวแรกต้องเป็นตัว R และ ต้องมีความยาวของโค้ดรวม 5 ตัว และ 4 ตัวหลังต้องเป็นตัวเลข

สมมุติว่า Code วัตถุดิบถูกกรอกที่ Cell B2 

ให้ท่านทดลองเขียนสูตร Excel ในCell D3 ดังนี้ครับ AND(LEFT(B2) = "R",LEN(B2) = 5 , ISNUMBER(VALUE(RIGHT(B2,4)))

ขออธิบายสูตร Excel นี้นะครับ

ในสูตร AND จะมีเงื่อนไขให้ตรวจสอบ 3 เงื่อนไข โดยคืนค่า TRUE หรือ 1 ก็ต่อเมื่อ ทั้งสามเงื่อนไขเป็นจริง นอกนั้นจะคืนค่า FALSE หรือ 0 ครับ

เงื่อนไขแรก จะตรวจสอบอักขระตัวแรกว่าเป็น R หรือไม่ โดยใช้สูตร LEFT(B2) = "R" เป็นการบังคับให้ผู้กรอกต้องพิมพ์ R เท่านั้น

เงื่อนไขต่อมา จะตรวจสอบว่าอักขระที่กรอกลงไปมีทั้งหมด 5 ตัวหรือไม่ โดยใช้สูตร LEN(B2) = 5 เป็นการบังคับให้ต้องกรอกอักขระ 5 ตัวเท่านั้น

เงื่อนไขสุดท้าย จะเริ่มจากตัดอักขระ 4 ตัวจากด้านขวา โดยใช้สูตร RIGHT(B2,4) จากนั้นจะแปลงอักขระเป็นตัวเลขโดยใช้ สูตร VALUE สุดท้ายจะตรวจสอบว่าอักขระที่แปลงเป็นตัวใช้หรือไม่โดยใช้สูตร ISNUMBER 

ผลแสดงได้ดังภาพที่ 1

ภาพที่ 1 ผลการตรวจสอบเงื่อนไข

เอาหล่ะครับ ถึงตอนนี้ท่านผู้อ่านทดลองกรอก Code ในช่อง B2 และสังเกตุผลลัพธ์ใน Cell D2 ดูนะครับ จากนั้นเราจะ Copy สูตร Excel ใน Cell D2 ไปใช้ใน Data Validation มาต่อกันเลยครับ 

1. คลิกเมาส์เลือก Cell B2:B10 เลือก เมนู Data และเลือกทูลบาร์ Data Validation จะปรากฎไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

2. คลิก Tab การตั้งค่า

3. ในหัวข้อ อนุญาตให้ เลือก กำหนดเอง

4. ในหัวข้อ Formula ให้วางสูตร Excel ที่ Copy จาก D2 ลงไป ดังแสดงในภาพที่ 2

ภาพที่ 2 การกำหนดเงื่อนไขใน Formula

5. คลิก Tab ข้อความที่ใส่ เพื่อกำหนดการแสดงข้อความเมื่อ Cell B2:B10 ถูกเลือก เป็นการช่วยลดความผิดพลาดลงไปได้อีก ดังแสดงในภาพที่ 3 

ภาพที่ 3 การกำหนดข้อความแจ้งเตือนขณะกรอก Code

6. คลิก Tab การแจ้งเตือนข้อผิดพลาด เพื่อกำหนดข้อความแจ้งเตือนเมื่อมีการกรอก Code ผิดรูปแบบที่กำหนด ดังแสดงในภาพที่ 4

7. คลิกปุ่ม ตกลง เพื่อปิดไดอะล็อก การตรวจสอบความถูกต้องของข้อมูล 

ภาพที่ 4 การกำหนดการแจ้งเตือนข้อผิดพลาดที่เกิดขึ้น

ภาพที่ 5 ผลลัพธ์จากการกรอก Code วัตถุดิบผิดรูปแบบ

เอาหล่ะครับ มาถึงตรงนี้เราก็กำหนดเงื่อนไขเพื่อตรวจสอบความถูกต้องของ Code วัตถุดิบ Cell B2 ถึง B10 เสร็จเรียบร้อย หากท่านกรอก Code วัตถุดิบผิดรูปแบบ Excel ก็จะแจ้งเตือนความผิดพลาดท่านด้วย Dialog ที่เราได้กำหนดไว้ ดังแสดงในภาพที่ 5 เท่านี้เราก็มั่นใจได้แล้วว่าข้อมูลที่เราบันทึกเข้ามาเก็บใน Cell ดังกล่าวมีความถูกต้องแน่นอน หวังว่าจะนำไปประยุกต์ใช้กันต่อได้นะครับ 

บทความที่ใกล้เคียงกัน

การใช้ Data Validation ป้องกันการบันทึกวันเวลาผิดพลาด