บทความนี้จะแสดงวิธีแปลงข้อมูลเป็น การกระจายปกติ ใน Excel ในสถิติ การนำแนวคิด การกระจายปกติ มาใช้มีความสำคัญเพื่อทำความเข้าใจตำแหน่งของข้อมูลเดี่ยวหรือหลายข้อมูลในชุดข้อมูล การคำนวณค่า การกระจายปกติ ของข้อมูลด้วยตนเองใช้เวลานานและเป็นกระบวนการที่เหนื่อยล้า โชคดีที่เรามีฟังก์ชันใน Excel ที่สามารถแก้ปัญหาของเราได้ในทันที ชื่อฟังก์ชันนี้คือ NORM.DIST มาดูส่วนที่เหลือของบทความเพื่อความเข้าใจที่ดีขึ้น
การกระจายปกติคืออะไร?
การกระจายปกติ หรือเรียกอีกอย่างว่า การกระจายแบบ Gaussian เป็นการกระจายความน่าจะเป็นของอวกาศตัวอย่าง การกระจายของข้อมูลเหล่านี้เป็นสมมาตรกับค่าเฉลี่ย ยังเป็นการวัดว่าข้อมูลที่อยู่ใกล้ค่าเฉลี่ยปรากฏมากกว่าข้อมูลที่อยู่ไกลจากค่าเฉลี่ยเพียงใด สำคัญที่ต้องรู้ว่า ค่าเฉลี่ย เป็นศูนย์และ ส่วนเบี่ยงเบนมาตรฐาน เป็น 1 ใน การกระจายปกติ ในชีวิตจริง การกระจายปกติ ช่วยให้เราเข้าใจนัยสำคัญของข้อมูลในกลุ่มข้อมูล สมการเพื่อหา การกระจายปกติ ในชุดข้อมูลกำหนดด้านล่าง
ที่นี่
σ = ส่วนเบี่ยงเบนมาตรฐาน
x = ข้อมูลตัวอย่าง
μ = ค่าเฉลี่ย/ค่าเฉลี่ยเลขคณิต
2 วิธีแปลงข้อมูลเป็นการกระจายปกติใน Excel
ในชุดข้อมูล เรามีข้อมูลเกี่ยวกับผลงานของนักเรียนบางคนโดยคะแนนในการสอบ เราจะกำหนดว่าใครทำผลงานยוצאתจากกาลในการสอบครั้งนั้นโดยแปลงคะแนนของพวกเขาเป็น การกระจายปกติ โดยใช้ Excel
1. ใช้ฟังก์ชัน Excel เพื่อแปลงข้อมูลเป็นการกระจายปกติ
วิธีที่ดีที่สุดในการแปลงข้อมูลของคุณเป็น การกระจายปกติ จะเป็นการใช้ฟังก์ชัน NORM.DIST ของ Excel มันจะคำนวณข้อมูล การกระจายปกติ โดยอัตโนมัติโดยใช้สูตรที่กล่าวมาข้างต้น มาดูกระบวนการด้านล่าง
ขั้นตอน:
- ขั้นแรก สร้างคอลัมน์เพื่อเก็บค่า การกระจายปกติ และเลือกเซลล์สองเซลล์เพื่อเก็บ ค่าเฉลี่ย และ ส่วนเบี่ยงเบนมาตรฐาน ของคุณ
- พิมพ์สูตรต่อไปนี้เพื่อคำนวณ ค่าเฉลี่ย และกด ENTER คุณจะเห็น ค่าเฉลี่ย ของ คะแนน ของนักเรียนเหล่านี้
=AVERAGE(C5:C11)
ที่นี่ ฟังก์ชัน AVERAGE คืนค่า ค่าเฉลี่ย ของ คะแนน ของนักเรียน
- หลังจากนั้น ใช้สูตรต่อไปนี้เพื่อคำนวณ ส่วนเบี่ยงเบนมาตรฐาน
=STDEV.P(C5:C11)
ฟังก์ชัน STDEV.P คืนค่า ส่วนเบี่ยงเบนมาตรฐาน ของข้อมูลนี้
- ต่อไป พิมพ์สูตรต่อไปนี้ในเซลล์ D5
=NORM.DIST(C5,$C$13,$C$14,FALSE)
- ทีหลัง กดปุ่ม ENTER และคุณจะเห็นค่า การกระจายปกติ ของคะแนนในเซลล์ C5
- หลังจากนั้น ใช้ Fill Handle เพื่อ AutoFill เซลล์ด้านล่าง
ดังนั้น คุณสามารถแปลงข้อมูลทั้งหมดของชุดข้อมูลเป็น การกระจายปกติ ใน Excel ได้
- หากคุณต้องการแสดงข้อมูลใน กราฟการกระจายปกติ โปรดเลือกช่วง C4:D11
- หลังจากนั้น ไปที่ Insert >> Scatter with Smooth Lines
จากนั้น คุณจะเห็นข้อมูลใน กราฟการกระจายปกติ
โปรดจำไว้ว่า ประมาณ 68% ของข้อมูลของคุณอยู่ระหว่างช่วง Average ± Standard Deviation 95% ของข้อมูลอยู่ระหว่าง Average ± 2*Standard Deviation และ 99.7% ของข้อมูลจะอยู่ในช่วง Average ± 3*Standard Deviation นั่นหมายความว่า 68% ของนักเรียนมีคะแนนระหว่าง 74.57 ± 10.66 หรือ 63.91 ถึง 85.23 เช่นเดียวกัน คุณสามารถกำหนดช่วงอื่นๆ สำหรับ 95% และ 99.7% ได้
2. ใช้สูตรคณิตศาสตร์เพื่อแปลงข้อมูลเป็นการกระจายปกติ
หากคุณเก่งสถิติและไม่สนใจใช้ ฟังก์ชัน NORM.DIST ในตัว คุณสามารถใช้ สูตรคณิตศาสตร์ ของ การกระจายปกติ มาดูกระบวนการด้านล่าง
ขั้นตอน:
- ขั้นแรก สร้างคอลัมน์เพื่อเก็บค่า การกระจายปกติ และเลือกเซลล์สองเซลล์เพื่อเก็บ ค่าเฉลี่ย และ ส่วนเบี่ยงเบนมาตรฐาน ของคุณ
- คำนวณ ค่าเฉลี่ย และ ส่วนเบี่ยงเบนมาตรฐาน ตามกระบวนการของ ส่วนที่ 1
- ต่อไป พิมพ์สูตรต่อไปนี้ในเซลล์ D5
=EXP(-0.5*((C5-$C$13)/$C$14)^2)/($C$14*SQRT(2*PI()))
ที่นี่ สูตรใช้ ฟังก์ชัน EXP และ SQRT เพื่อสร้าง สูตรคณิตศาสตร์ ของการแปลง การกระจายปกติ จะคืนค่า การกระจายปกติ ของข้อมูลของคุณ
- ทีหลัง กดปุ่ม ENTER และคุณจะเห็นค่า การกระจายปกติ ของคะแนนในเซลล์ C5
- หลังจากนั้น ใช้ Fill Handle เพื่อ AutoFill เซลล์ด้านล่าง
ดังนั้น คุณสามารถแปลงข้อมูลทั้งหมดของชุดข้อมูลเป็น การกระจายปกติ โดยใช้ สูตรคณิตศาสตร์ ได้
- หากคุณต้องการแสดงข้อมูลใน กราฟการกระจายปกติ โปรดทำตามลิงก์ของ ส่วนที่ 1
ส่วนฝึกปฏิบัติ
ที่นี่ ให้ชุดข้อมูลของบทความนี้ เพื่อให้คุณสามารถฝึกปฏิบัติวิธีเหล่านี้ด้วยตัวเองได้
สรุป
พอที่จะพูดได้ว่า บทความนี้จะช่วยให้คุณเข้าใจวิธีพื้นฐานในการแปลงข้อมูลเป็น การกระจายปกติ ใน Excel หากคุณมีวิธีที่ดีกว่า หรือคำถาม หรือข้อเสนอแนะเกี่ยวกับบทความนี้ โปรดแชร์ในกล่องความคิดเห็น นี่จะช่วยให้บทความในอนาคตของฉันดียิ่งขึ้น สำหรับคำถามเพิ่มเติม กรุณาเยี่ยมชมเว็บไซต์ ExcelDemy ของเรา
