วิธีการคำนวณคะแนน Z ใน Excel: คู่มือที่ครอบคลุม
ในขอบเขตของสถิติและการวิเคราะห์ข้อมูล การทำความเข้าใจว่าข้อมูลของคุณเปรียบเทียบกับค่าเฉลี่ยอย่างไรเป็นสิ่งสำคัญ คะแนน z หรือที่เรียกว่าคะแนนมาตรฐาน เป็นวิธีการวัดระยะทางสัมพัทธ์ของจุดข้อมูลจากค่าเฉลี่ยของชุดข้อมูล ซึ่งแสดงในรูปของค่าเบี่ยงเบนมาตรฐาน ไม่ว่าคุณจะวิเคราะห์คะแนนทดสอบ ข้อมูลทางการเงิน หรือชุดข้อมูลตัวเลขอื่นๆ การคำนวณคะแนน z สามารถให้ข้อมูลเชิงลึกที่ลึกซึ้งเกี่ยวกับพฤติกรรมของข้อมูลของคุณได้
การใช้ Excel เพื่อคำนวณคะแนน z มอบความเรียบง่ายและมีประสิทธิภาพ ช่วยให้วิเคราะห์ชุดข้อมูลขนาดใหญ่ได้อย่างรวดเร็วสำหรับการเปรียบเทียบที่เป็นมาตรฐานและการตรวจจับค่าผิดปกติ บทช่วยสอนนี้จะแนะนำคุณในการทำความเข้าใจว่าคะแนน z คืออะไร วิธีค้นหาใน Excel ให้ตัวอย่างสูตร ตีความคะแนน z ในข้อมูลของคุณ และแบ่งปันเคล็ดลับสำคัญที่ต้องจำเมื่อทำการคำนวณเหล่านี้ |
z-score คืออะไร?
คะแนน z หรือที่เรียกว่าคะแนนมาตรฐาน เป็นหน่วยเมตริกทางสถิติที่ระบุระยะทางของจุดข้อมูลเฉพาะจากค่าเฉลี่ยของชุดข้อมูล ซึ่งแสดงในรูปของค่าเบี่ยงเบนมาตรฐาน การวัดนี้มีความสำคัญอย่างยิ่งต่อการทำความเข้าใจว่าจุดข้อมูลเบี่ยงเบนไปจากค่าเฉลี่ยของชุดข้อมูลไปไกลแค่ไหนและไปในทิศทางใด โดยพื้นฐานแล้ว คะแนน z จะแปลงจุดข้อมูลเป็นมาตราส่วนทั่วไป ช่วยให้สามารถเปรียบเทียบชุดข้อมูลต่างๆ หรือภายในประชากรที่หลากหลายได้อย่างตรงไปตรงมา โดยไม่คำนึงถึงมาตราส่วนดั้งเดิมของการวัดหรือการกระจายรูปร่าง
แนวคิดของคะแนน z มีความเชื่อมโยงอย่างใกล้ชิดกับการแจกแจงแบบปกติ การแจกแจงแบบปกติเป็นแนวคิดพื้นฐานในสถิติ ซึ่งแสดงถึงการแจกแจงที่การสังเกตส่วนใหญ่กระจุกรอบจุดสูงสุดตรงกลาง และความน่าจะเป็นที่ค่าจะเกิดขึ้นจะลดลงอย่างสมมาตรทั้งสองทิศทางจากค่าเฉลี่ย ในบริบทของการแจกแจงแบบปกติ: |
- ข้อมูลประมาณ 68% อยู่ภายในค่าเบี่ยงเบนมาตรฐานหนึ่งค่า (±1 z-score) ของค่าเฉลี่ย ซึ่งบ่งชี้ว่ามีค่าเบี่ยงเบนปานกลางจากค่าเฉลี่ย
- การสังเกตประมาณ 95% อยู่ภายในค่าเบี่ยงเบนมาตรฐานสองค่า (±2 คะแนน z) ซึ่งแสดงถึงค่าเบี่ยงเบนที่มีนัยสำคัญแต่ไม่รุนแรงมาก
- พบข้อมูลเกือบ 99.7% ภายในค่าเบี่ยงเบนมาตรฐานสามค่า (±3 คะแนน z) ซึ่งครอบคลุมการสังเกตเกือบทั้งหมดภายในการกระจายตัวและเน้นย้ำถึงค่าเบี่ยงเบนที่รุนแรง
คะแนน z เป็นเครื่องมือสำคัญในการวิเคราะห์ทางสถิติ ช่วยให้นักวิจัยและนักวิเคราะห์สร้างมาตรฐานการสังเกตแต่ละรายการจากชุดข้อมูลต่างๆ อำนวยความสะดวกในการเปรียบเทียบคะแนนจากการแจกแจงที่แตกต่างกัน ด้วยการแปลงข้อมูลเป็นคะแนน z เราสามารถระบุได้อย่างง่ายดายว่าการสังเกตเฉพาะนั้นผิดปกติหรือโดยทั่วไปอย่างไรภายในการแจกแจงที่กำหนด ทำให้เป็นเครื่องมือที่ขาดไม่ได้สำหรับการใช้งานต่างๆ รวมถึงการตรวจจับค่าผิดปกติ การทดสอบสมมติฐาน และการทำให้ข้อมูลเป็นมาตรฐาน
จะหาคะแนน z ใน Excel ได้อย่างไร?
ใน Excel ไม่มีฟังก์ชันเฉพาะสำหรับการคำนวณคะแนน z โดยตรง กระบวนการนี้เกี่ยวข้องกับการคำนวณเบื้องต้นของค่าเฉลี่ยของชุดข้อมูลของคุณ (μ) และส่วนเบี่ยงเบนมาตรฐาน (σ). หลังจากได้รับสถิติที่จำเป็นเหล่านี้แล้ว คุณมีสองวิธีหลักในการกำหนดคะแนน z:
- วิธีการคำนวณด้วยตนเอง: ใช้สูตร z-score:
=(x-μ)/σ
- ที่:
- x คือจุดข้อมูลที่คุณกำลังตรวจสอบ
μ คือค่าเฉลี่ยของชุดข้อมูลของคุณ
σ คือค่าเบี่ยงเบนมาตรฐานของชุดข้อมูลของคุณ - การใช้ฟังก์ชันมาตรฐาน: สำหรับแนวทางบูรณาการมากขึ้น Excel's มาตรฐาน ฟังก์ชันจะคำนวณคะแนน z โดยตรงโดยพิจารณาจากจุดข้อมูล ค่าเฉลี่ย และค่าเบี่ยงเบนมาตรฐานเป็นอินพุต:
=STANDARDIZE(x, mean, standard_dev)
ตัวอย่างสูตรในการคำนวณ z-score ใน Excel
สมมติว่าคุณมีชุดข้อมูลในคอลัมน์ A ซึ่งครอบคลุมจากเซลล์ A2 ไปยัง A101ต่อไปนี้คือวิธีคำนวณคะแนน z สำหรับค่าเหล่านี้:
- คำนวณค่าเฉลี่ย (μ): ใช้ เฉลี่ย(ช่วง) ฟังก์ชันหาค่าเฉลี่ย (μ) ของชุดข้อมูลของคุณ
=AVERAGE(A2:A101)
- คำนวณค่าเบี่ยงเบนมาตรฐาน (σ): เลือกสูตรที่เหมาะสมตามบริบทข้อมูลของคุณ
สำคัญ: การเลือกฟังก์ชันที่เหมาะสมสำหรับชุดข้อมูลของคุณถือเป็นสิ่งสำคัญในการรับรองการคำนวณที่แม่นยำ (สำหรับข้อมูลของฉันใน A2: A101 แทนประชากรทั้งหมด ผมจะใช้สูตรแรก)
- ใช้ STDEV.P (ช่วง) ฟังก์ชันหากข้อมูลของคุณแสดงถึงประชากรทั้งหมด (หมายความว่าไม่มีกลุ่มที่ใหญ่กว่านี้ที่สุ่มตัวอย่างค่าเหล่านี้)
=STDEV.P(A2:A101)
- ใช้ STDEV.S (ช่วง) ฟังก์ชันนี้หากข้อมูลของคุณเป็นตัวอย่างของประชากรจำนวนมาก หรือคุณต้องการประมาณค่าเบี่ยงเบนมาตรฐานของประชากรโดยอิงจากตัวอย่างของคุณ
=STDEV.S(A2:A101)
- ใช้ STDEV.P (ช่วง) ฟังก์ชันหากข้อมูลของคุณแสดงถึงประชากรทั้งหมด (หมายความว่าไม่มีกลุ่มที่ใหญ่กว่านี้ที่สุ่มตัวอย่างค่าเหล่านี้)
- คำนวณคะแนน Z สำหรับจุดข้อมูลใน A2: ใช้สูตรใดสูตรหนึ่งต่อไปนี้ ซึ่งจะให้ผลลัพธ์เหมือนกัน (ในกรณีนี้ ฉันจะเลือกใช้สูตรที่สอง)
- คำนวณด้วยตนเอง โดยลบค่าเฉลี่ยออกจากจุดข้อมูลแล้วหารผลลัพธ์นี้ด้วยค่าเบี่ยงเบนมาตรฐาน
=(A2 - $E$2) / $E$3
- ใช้ ขนาดมาตรฐาน(x, ค่าเฉลี่ย, standard_dev) ฟังก์ชัน
=STANDARDIZE(A2, $E$2, $E$3)
หมายเหตุ สัญญาณดอลลาร์ ($) บอกสูตรให้อ้างอิงเซลล์เฉพาะเสมอ (E2 สำหรับค่าเฉลี่ย E3 สำหรับค่าเบี่ยงเบนมาตรฐาน) โดยไม่คำนึงว่าสูตรจะถูกคัดลอกไปที่ใด
- คำนวณด้วยตนเอง โดยลบค่าเฉลี่ยออกจากจุดข้อมูลแล้วหารผลลัพธ์นี้ด้วยค่าเบี่ยงเบนมาตรฐาน
- คำนวณคะแนน Z สำหรับแต่ละค่าในชุดข้อมูลของคุณ: คัดลอกสูตรในขั้นตอนที่ 3 ลงในคอลัมน์เพื่อคำนวณคะแนน z สำหรับแต่ละค่าในชุดข้อมูลของคุณ เคล็ดลับ: ดับเบิลคลิกที่จุดจับเติมของเซลล์เพื่อขยายสูตรอย่างรวดเร็ว
- หากต้องการปรับปรุงการคำนวณคะแนน z ทั่วทั้งชุดข้อมูลโดยไม่ต้องพิมพ์สูตรสำหรับค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐานแยกกันในเซลล์แยกกัน คุณสามารถใช้สูตรที่ครอบคลุมรายการใดรายการหนึ่งต่อไปนี้ได้โดยตรง
=(A2 - AVERAGE($A$2:$A$101)) / STDEV.P($A$2:$A$101)
=STANDARDIZE(A2, AVERAGE($A$2:$A$101), STDEV.P($A$2:$A$101))
- การรักษาความแม่นยำที่สม่ำเสมอโดยใช้ทศนิยมสามตำแหน่งสำหรับคะแนน z ถือเป็นแนวทางปฏิบัติที่น่ายกย่องในงานทางวิทยาศาสตร์และสถิติ บรรลุสิ่งนี้โดยเลือกเซลล์คะแนน z ของคุณและใช้ ลดทศนิยม ตัวเลือกที่พบใน จำนวน กลุ่มใน หน้าแรก แถบ
การตีความคะแนน z ในข้อมูล
การตีความคะแนน z เป็นพื้นฐานในการทำความเข้าใจตำแหน่งและความสำคัญของจุดข้อมูลภายในชุดข้อมูล คะแนน z ให้การวัดโดยตรงว่าองค์ประกอบหนึ่งๆ มาจากค่าเฉลี่ยของชุดข้อมูลเป็นจำนวนเท่าใด โดยให้ข้อมูลเชิงลึกเกี่ยวกับตำแหน่งสัมพัทธ์และความหายากขององค์ประกอบนั้น
ความสัมพันธ์กับค่าเฉลี่ย
- คะแนน Z = 0: บ่งบอกถึงประสิทธิภาพโดยเฉลี่ย โดยจุดข้อมูลอยู่ที่ค่าเฉลี่ยทุกประการ
- คะแนน Z > 0: หมายถึงค่าที่สูงกว่าค่าเฉลี่ย โดยมีระยะห่างจากค่าเฉลี่ยมากขึ้นซึ่งส่งสัญญาณถึงประสิทธิภาพที่แข็งแกร่งกว่า
- คะแนน Z < 0: แสดงถึงค่าที่ต่ำกว่าค่าเฉลี่ย โดยที่คะแนนที่ต่ำกว่าแสดงถึงความเบี่ยงเบนที่มากกว่าที่ต่ำกว่าค่าเฉลี่ย
ระดับความเบี่ยงเบน
- |Z-คะแนน| < 1: จุดข้อมูลเหล่านี้ใกล้เคียงกับค่าเฉลี่ย โดยอยู่ภายในส่วนหลักของข้อมูลในการแจกแจงแบบปกติ ซึ่งเป็นการส่งสัญญาณถึงประสิทธิภาพมาตรฐาน
- |Z-คะแนน| < 2: เสนอแนะการเบี่ยงเบนปานกลางจากค่าเฉลี่ย ทำเครื่องหมายข้อสังเกตว่าไม่ปกติแต่ยังคงอยู่ในช่วงความแปรปรวนปกติ
- |Z-คะแนน| > 2: เน้นจุดข้อมูลที่ผิดปกติอย่างมีนัยสำคัญซึ่งอยู่ห่างจากค่าเฉลี่ย ซึ่งอาจบ่งชี้ถึงค่าผิดปกติหรือการเบี่ยงเบนอย่างมากจากบรรทัดฐานที่คาดไว้
คำอธิบายตัวอย่าง:
- คะแนน z 0.66 หมายความว่าจุดข้อมูลมีค่าเบี่ยงเบนมาตรฐาน 0.66 เหนือค่าเฉลี่ย สิ่งนี้บ่งชี้ว่าค่านั้นสูงกว่าค่าเฉลี่ยแต่ยังคงค่อนข้างใกล้เคียง โดยอยู่ในช่วงของความแปรผันทั่วไป
- ในทางกลับกัน คะแนน z -2.1 แสดงว่าจุดข้อมูลมีค่าเบี่ยงเบนมาตรฐานต่ำกว่าค่าเฉลี่ย 2.1 ค่านี้ต่ำกว่าค่าเฉลี่ยอย่างมาก ซึ่งบ่งชี้ว่าอยู่ห่างจากช่วงปกติ
สิ่งที่ต้องจำเมื่อคำนวณคะแนน z ใน Excel
เมื่อใช้ Excel เพื่อคำนวณคะแนน z ความแม่นยำและความแม่นยำเป็นสิ่งสำคัญยิ่ง มีข้อควรพิจารณาที่สำคัญที่ต้องคำนึงถึงเพื่อให้มั่นใจในความน่าเชื่อถือของผลลัพธ์ของคุณ:
- ตรวจสอบการกระจายแบบปกติ: คะแนน Z มีประสิทธิภาพมากที่สุดสำหรับข้อมูลที่เป็นไปตามการแจกแจงแบบปกติ หากชุดข้อมูลของคุณไม่เป็นไปตามการกระจายนี้ คะแนน z อาจไม่ทำหน้าที่เป็นเครื่องมือวิเคราะห์ที่เหมาะสม พิจารณาดำเนินการทดสอบภาวะปกติก่อนที่จะใช้การวิเคราะห์คะแนนมาตรฐาน
- รับรองว่าใช้สูตรถูกต้อง: ตรวจสอบให้แน่ใจว่าคุณเลือกฟังก์ชันส่วนเบี่ยงเบนมาตรฐานที่ถูกต้อง - ส.ดี.อี.พี สำหรับประชากรทั้งหมดและ STDEV.S สำหรับตัวอย่าง - ขึ้นอยู่กับลักษณะชุดข้อมูลของคุณ
- ใช้การอ้างอิงแบบสัมบูรณ์สำหรับค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐาน: เมื่อใช้สูตรกับหลายเซลล์ ให้ใช้การอ้างอิงแบบสัมบูรณ์ (เช่น $ ก $ 1) สำหรับค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐานในสูตรคะแนน z ของคุณเพื่อให้แน่ใจว่าการคำนวณมีความสอดคล้องกัน
- ระวังค่าผิดปกติ: ค่าผิดปกติมีผลกระทบอย่างมีนัยสำคัญต่อทั้งค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐาน ซึ่งอาจบิดเบือนคะแนน z ที่คำนวณได้
- ตรวจสอบความสมบูรณ์ของข้อมูล: ก่อนที่จะคำนวณคะแนน z ตรวจสอบให้แน่ใจว่าชุดข้อมูลของคุณสะอาดและปราศจากข้อผิดพลาด การป้อนข้อมูลที่ไม่ถูกต้อง ค่าที่ซ้ำกัน หรือค่าที่ไม่เกี่ยวข้องอาจส่งผลกระทบอย่างมีนัยสำคัญต่อค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐาน ซึ่งนำไปสู่คะแนน z ที่ทำให้เข้าใจผิด
- หลีกเลี่ยงการปัดเศษหรือการตัดทอนก่อนเวลาอันควร: Excel สามารถจัดการกับตำแหน่งทศนิยมจำนวนมากได้ และการรักษาตำแหน่งเหล่านี้ไว้สามารถป้องกันข้อผิดพลาดในการปัดเศษสะสมที่อาจบิดเบือนการวิเคราะห์ขั้นสุดท้ายของคุณ
ด้านบนคือเนื้อหาที่เกี่ยวข้องทั้งหมดที่เกี่ยวข้องกับการคำนวณคะแนน z ใน Excel ฉันหวังว่าคุณจะพบว่าบทช่วยสอนนี้มีประโยชน์ หากคุณต้องการสำรวจเคล็ดลับและคำแนะนำเพิ่มเติมเกี่ยวกับ Excel โปรดคลิกที่นี่ เพื่อเข้าถึงคอลเลคชันบทช่วยสอนมากมายกว่าพันบทของเรา
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox