วิธีการคำนวณ Z-Score โดยใช้ Microsoft Excel
Z-Score คือค่าทางสถิติที่บอกให้คุณทราบว่าค่าเบี่ยงเบนมาตรฐานเป็นจำนวนเท่าใดจากค่าเฉลี่ยของชุดข้อมูลทั้งหมด คุณสามารถใช้สูตร AVERAGE และ STDEV.S หรือ STDEV.P เพื่อคำนวณค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐานของข้อมูลของคุณจากนั้นใช้ผลลัพธ์เหล่านั้นเพื่อกำหนด Z-Score ของแต่ละค่า
Z-Score คืออะไรและฟังก์ชัน AVERAGE, STDEV.S และ STDEV.P ทำหน้าที่อะไร
Z-Score เป็นวิธีง่ายๆในการเปรียบเทียบค่าจากชุดข้อมูลสองชุดที่แตกต่างกัน กำหนดเป็นจำนวนส่วนเบี่ยงเบนมาตรฐานที่อยู่ห่างจากค่าเฉลี่ยที่จุดข้อมูลอยู่ สูตรทั่วไปมีลักษณะดังนี้:
= (DataPoint-AVERAGE (ชุดข้อมูล)) / STDEV (ชุดข้อมูล)
นี่คือตัวอย่างที่จะช่วยชี้แจง สมมติว่าคุณต้องการเปรียบเทียบผลการทดสอบของนักเรียนพีชคณิตสองคนที่สอนโดยครูต่างกัน คุณทราบดีว่านักเรียนคนแรกได้คะแนน 95% ในการสอบปลายภาคในชั้นเรียนหนึ่งและนักเรียนในชั้นเรียนอื่น ๆ ได้คะแนน 87%
เมื่อมองแวบแรกเกรด 95% น่าประทับใจกว่า แต่ถ้าครูของชั้นสองให้ข้อสอบที่ยากขึ้นล่ะ? คุณสามารถคำนวณ Z-Score ของคะแนนของนักเรียนแต่ละคนโดยพิจารณาจากคะแนนเฉลี่ยในแต่ละชั้นเรียนและส่วนเบี่ยงเบนมาตรฐานของคะแนนในแต่ละชั้นเรียน การเปรียบเทียบคะแนน Z ของนักเรียนทั้งสองคนสามารถแสดงให้เห็นว่านักเรียนที่มีคะแนน 87% ทำได้ดีกว่าเมื่อเทียบกับนักเรียนที่เหลือในชั้นเรียนมากกว่านักเรียนที่มีคะแนน 98% เมื่อเทียบกับนักเรียนที่เหลือในชั้นเรียน
ค่าทางสถิติอันดับแรกที่คุณต้องการคือ 'ค่าเฉลี่ย' และฟังก์ชัน "AVERAGE" ของ Excel จะคำนวณค่านั้น เพียงแค่เพิ่มค่าทั้งหมดในช่วงเซลล์และหารผลรวมนั้นด้วยจำนวนเซลล์ที่มีค่าตัวเลข (จะไม่สนใจเซลล์ว่าง)
ค่าทางสถิติอื่น ๆ ที่เราต้องการคือ 'ค่าเบี่ยงเบนมาตรฐาน' และ Excel มีฟังก์ชันที่แตกต่างกันสองฟังก์ชันในการคำนวณค่าเบี่ยงเบนมาตรฐานด้วยวิธีที่แตกต่างกันเล็กน้อย
Excel เวอร์ชันก่อนหน้ามีเพียงฟังก์ชัน“ STDEV” ซึ่งจะคำนวณค่าเบี่ยงเบนมาตรฐานในขณะที่ถือว่าข้อมูลเป็น 'ตัวอย่าง' ของประชากร Excel 2010 แบ่งออกเป็นสองฟังก์ชันที่คำนวณค่าเบี่ยงเบนมาตรฐาน:
- STDEV.S:ฟังก์ชันนี้เหมือนกับฟังก์ชัน“ STDEV” ก่อนหน้านี้ จะคำนวณค่าเบี่ยงเบนมาตรฐานในขณะที่ถือว่าข้อมูลเป็น 'ตัวอย่าง' ของประชากร ตัวอย่างของประชากรอาจเป็นยุงบางชนิดที่เก็บรวบรวมจากโครงการวิจัยหรือรถยนต์ที่ถูกกันไว้และใช้สำหรับการทดสอบความปลอดภัยจากการชน
- STDEV.P:ฟังก์ชันนี้จะคำนวณค่าเบี่ยงเบนมาตรฐานในขณะที่ถือว่าข้อมูลเป็นประชากรทั้งหมด ประชากรทั้งหมดจะเป็นเหมือนยุงทั้งหมดบนโลกหรือรถยนต์ทุกคันในการผลิตเฉพาะรุ่น
ที่คุณเลือกขึ้นอยู่กับชุดข้อมูลของคุณ โดยทั่วไปความแตกต่างจะมีขนาดเล็ก แต่ผลลัพธ์ของฟังก์ชัน“ STDEV.P” จะมีขนาดเล็กกว่าผลลัพธ์ของฟังก์ชัน“ STDEV.S” สำหรับชุดข้อมูลเดียวกันเสมอ เป็นวิธีการที่อนุรักษ์นิยมกว่าที่จะถือว่าข้อมูลมีความแปรปรวนมากขึ้น
ลองดูตัวอย่าง
ตัวอย่างเช่นเรามีสองคอลัมน์ (“ ค่า” และ“ คะแนน Z”) และเซลล์“ ตัวช่วย” สามเซลล์สำหรับจัดเก็บผลลัพธ์ของฟังก์ชัน“ AVERAGE”“ STDEV.S” และ“ STDEV.P” คอลัมน์ "ค่า" ประกอบด้วยตัวเลขสุ่มสิบตัวที่อยู่ตรงกลาง 500 และคอลัมน์ "Z-Score" คือที่ที่เราจะคำนวณ Z-Score โดยใช้ผลลัพธ์ที่เก็บไว้ในเซลล์ "ตัวช่วย"
ขั้นแรกเราจะคำนวณค่าเฉลี่ยของค่าโดยใช้ฟังก์ชัน "AVERAGE" เลือกเซลล์ที่คุณจะจัดเก็บผลลัพธ์ของฟังก์ชัน“ AVERAGE”
พิมพ์สูตรต่อไปนี้แล้วกดEnterหรือใช้เมนู "สูตร"
= ค่าเฉลี่ย (E2: E13)
หากต้องการเข้าถึงฟังก์ชันผ่านเมนู "สูตร" เลือกเมนูแบบเลื่อนลง "ฟังก์ชันเพิ่มเติม" เลือกตัวเลือก "สถิติ" จากนั้นคลิกที่ "ค่าเฉลี่ย"
ในหน้าต่างอาร์กิวเมนต์ของฟังก์ชันให้เลือกเซลล์ทั้งหมดในคอลัมน์ "ค่า" เป็นอินพุตสำหรับช่อง "Number1" คุณไม่จำเป็นต้องกังวลเกี่ยวกับฟิลด์“ Number2”
ตอนนี้กด“ ตกลง”
ต่อไปเราต้องคำนวณค่าเบี่ยงเบนมาตรฐานของค่าโดยใช้ฟังก์ชัน“ STDEV.S” หรือ“ STDEV.P” ในตัวอย่างนี้เราจะแสดงวิธีคำนวณทั้งสองค่าเริ่มต้นด้วย“ STDEV.S” เลือกเซลล์ที่จะจัดเก็บผลลัพธ์
ในการคำนวณค่าเบี่ยงเบนมาตรฐานโดยใช้ฟังก์ชัน“ STDEV.S” ให้พิมพ์สูตรนี้แล้วกด Enter (หรือเข้าถึงผ่านเมนู“ สูตร”)
= STDEV.S (E3: E12)
ในการเข้าถึงฟังก์ชันผ่านเมนู“ สูตร” เลือกเมนูแบบเลื่อนลง“ ฟังก์ชั่นเพิ่มเติม” เลือกตัวเลือก“ สถิติ” เลื่อนลงเล็กน้อยจากนั้นคลิกคำสั่ง“ STDEV.S”
ในหน้าต่างอาร์กิวเมนต์ของฟังก์ชันให้เลือกเซลล์ทั้งหมดในคอลัมน์ "ค่า" เป็นอินพุตสำหรับช่อง "Number1" คุณไม่จำเป็นต้องกังวลเกี่ยวกับฟิลด์“ Number2” ที่นี่เช่นกัน
ตอนนี้กด“ ตกลง”
ต่อไปเราจะคำนวณค่าเบี่ยงเบนมาตรฐานโดยใช้ฟังก์ชัน“ STDEV.P” เลือกเซลล์ที่จะจัดเก็บผลลัพธ์
ในการคำนวณค่าเบี่ยงเบนมาตรฐานโดยใช้ฟังก์ชัน“ STDEV.P” ให้พิมพ์สูตรนี้แล้วกด Enter (หรือเข้าถึงผ่านเมนู“ สูตร”)
= STDEV.P (E3: E12)
ในการเข้าถึงฟังก์ชันผ่านเมนู“ สูตร” เลือกเมนูแบบเลื่อนลง“ ฟังก์ชั่นเพิ่มเติม” เลือกตัวเลือก“ สถิติ” เลื่อนลงเล็กน้อยจากนั้นคลิกที่สูตร“ STDEV.P”
ในหน้าต่างอาร์กิวเมนต์ของฟังก์ชันให้เลือกเซลล์ทั้งหมดในคอลัมน์ "ค่า" เป็นอินพุตสำหรับช่อง "Number1" อีกครั้งคุณไม่ต้องกังวลเกี่ยวกับฟิลด์“ Number2”
ตอนนี้กด“ ตกลง”
ตอนนี้เราได้คำนวณค่าเฉลี่ยและค่าเบี่ยงเบนมาตรฐานของข้อมูลแล้วเรามีทุกสิ่งที่จำเป็นในการคำนวณ Z-Score เราสามารถใช้สูตรง่ายๆที่อ้างอิงเซลล์ที่มีผลลัพธ์ของฟังก์ชัน“ AVERAGE” และ“ STDEV.S” หรือ“ STDEV.P”
เลือกเซลล์แรกในคอลัมน์“ Z-Score” เราจะใช้ผลลัพธ์ของฟังก์ชัน“ STDEV.S” สำหรับตัวอย่างนี้ แต่คุณสามารถใช้ผลลัพธ์จาก“ STDEV.P” ได้เช่นกัน
พิมพ์สูตรต่อไปนี้แล้วกด Enter:
= (E3- $ G $ 3) / $ H $ 3
หรือคุณสามารถใช้ขั้นตอนต่อไปนี้เพื่อป้อนสูตรแทนการพิมพ์:
- คลิกเซลล์ F3 แล้วพิมพ์
=( - เลือกเซลล์ E3 (คุณสามารถกดแป้นลูกศรซ้ายหนึ่งครั้งหรือใช้เมาส์)
- พิมพ์เครื่องหมายลบ
- - เลือกเซลล์ G3 จากนั้นกดF4เพื่อเพิ่มอักขระ“ $” เพื่อทำการอ้างอิงแบบ 'สัมบูรณ์' ไปยังเซลล์ (จะวนผ่าน“ G3”>“ $ G $ 3″>“ G $ 3″>“ $ G3″> “ G3” หากคุณกดF4ต่อไป)
- ประเภท
)/ - เลือกเซลล์ H3 (หรือ I3 หากคุณใช้“ STDEV.P”) แล้วกดF4เพื่อเพิ่มอักขระ“ $” สองตัว
- กดปุ่มตกลง
Z-Score ถูกคำนวณสำหรับค่าแรก มันคือ 0.15945 ค่าเบี่ยงเบนมาตรฐานที่ต่ำกว่าค่าเฉลี่ย ในการตรวจสอบผลลัพธ์คุณสามารถคูณค่าเบี่ยงเบนมาตรฐานด้วยผลลัพธ์นี้ (6.271629 * -0.15945) และตรวจสอบว่าผลลัพธ์เท่ากับความแตกต่างระหว่างค่าและค่าเฉลี่ย (499-500) ผลลัพธ์ทั้งสองเท่ากันดังนั้นค่าจึงสมเหตุสมผล
ลองคำนวณ Z-Scores ของค่าที่เหลือ เน้นคอลัมน์ 'Z-Score' ทั้งหมดโดยเริ่มจากเซลล์ที่มีสูตร
กด Ctrl + D ซึ่งคัดลอกสูตรในเซลล์ด้านบนลงไปตามเซลล์อื่น ๆ ที่เลือกทั้งหมด
ตอนนี้สูตรได้รับการ "เติมเต็ม" ลงในเซลล์ทั้งหมดแล้วและแต่ละเซลล์จะอ้างอิงเซลล์ "AVERAGE" และ "STDEV.S" หรือ "STDEV.P" ที่ถูกต้องเสมอเนื่องจากมีอักขระ "$" หากคุณพบข้อผิดพลาดให้ย้อนกลับไปและตรวจสอบให้แน่ใจว่ามีอักขระ“ $” อยู่ในสูตรที่คุณป้อน
การคำนวณ Z-Score โดยไม่ใช้เซลล์ 'Helper'
เซลล์ตัวช่วยเก็บผลลัพธ์เช่นเดียวกับเซลล์ที่เก็บผลลัพธ์ของฟังก์ชัน“ AVERAGE”“ STDEV.S” และ“ STDEV.P” มันมีประโยชน์ แต่ไม่จำเป็นเสมอไป คุณสามารถข้ามไปพร้อมกันได้เมื่อคำนวณ Z-Score โดยใช้สูตรทั่วไปต่อไปนี้แทน
นี่คือสิ่งที่ใช้ฟังก์ชัน“ STDEV.S”:
= (Value-AVERAGE (ค่า)) / STDEV.S (ค่า)
และอีกอันที่ใช้ฟังก์ชัน“ STEV.P”:
= (Value-AVERAGE (ค่า)) / STDEV.P (ค่า)
เมื่อป้อนช่วงเซลล์สำหรับ "ค่า" ในฟังก์ชันอย่าลืมเพิ่มการอ้างอิงแบบสัมบูรณ์ (“ $” โดยใช้ F4) เพื่อที่ว่าเมื่อคุณ "เติมข้อมูล" คุณจะไม่คำนวณค่าเฉลี่ยหรือค่าเบี่ยงเบนมาตรฐานของช่วงอื่น ของเซลล์ในทุกสูตร
หากคุณมีชุดข้อมูลขนาดใหญ่การใช้เซลล์ตัวช่วยอาจมีประสิทธิภาพมากกว่าเนื่องจากไม่ได้คำนวณผลลัพธ์ของฟังก์ชัน“ AVERAGE” และ“ STDEV.S” หรือ“ STDEV.P” ในแต่ละครั้งช่วยประหยัดทรัพยากรของโปรเซสเซอร์และ เร่งเวลาที่ใช้ในการคำนวณผลลัพธ์
นอกจากนี้“ $ G $ 3” ใช้ไบต์ในการจัดเก็บน้อยกว่าและโหลด RAM น้อยกว่า“ AVERAGE ($ E $ 3: $ E $ 12)” สิ่งนี้มีความสำคัญเนื่องจาก Excel เวอร์ชัน 32 บิตมาตรฐาน จำกัด ไว้ที่ RAM 2GB (เวอร์ชัน 64 บิตไม่มีข้อ จำกัด ในการใช้ RAM)
