วิธีการคำนวณคะแนน Z โดยใช้ Microsoft Excel

คะแนน Z คือค่าสถิติที่บอกให้คุณทราบว่าค่าเบี่ยงเบนมาตรฐานจำนวนเท่าใดค่าหนึ่งที่เกิดขึ้นนั้นมาจากค่าเฉลี่ยของชุดข้อมูลทั้งหมด คุณสามารถใช้สูตร AVERAGE และ STDEV.S หรือ STDEV.P เพื่อคำนวณค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐานของข้อมูลของคุณแล้วใช้ผลลัพธ์เหล่านั้นเพื่อกำหนดคะแนน Z ของแต่ละค่า.

คะแนน Z คืออะไรและฟังก์ชัน AVERAGE, STDEV.S และ STDEV.P ทำอะไร?

Z-Score เป็นวิธีที่ง่ายในการเปรียบเทียบค่าจากชุดข้อมูลที่แตกต่างกันสองชุด มันถูกกำหนดให้เป็นจำนวนส่วนเบี่ยงเบนมาตรฐานห่างจากค่าเฉลี่ยของจุดข้อมูลอยู่ สูตรทั่วไปมีลักษณะดังนี้:

= (DataPoint เฉลี่ย (ชุดข้อมูล)) / STDEV (ชุดข้อมูล)

นี่คือตัวอย่างที่จะช่วยชี้แจง สมมติว่าคุณต้องการเปรียบเทียบผลการทดสอบของนักเรียนพีชคณิตสองคนที่สอนโดยครูที่แตกต่างกัน คุณรู้ว่านักเรียนคนแรกได้รับ 95% ในการสอบครั้งสุดท้ายในชั้นเรียนหนึ่งและนักเรียนในชั้นเรียนอื่น ๆ ได้คะแนน 87%.

เมื่อมองดูครั้งแรกเกรด 95% นั้นน่าประทับใจมากขึ้น แต่ถ้าครูของชั้นสองให้สอบได้ยากกว่านี้ล่ะ คุณสามารถคำนวณคะแนน Z ของคะแนนของนักเรียนแต่ละคนโดยใช้คะแนนเฉลี่ยในแต่ละชั้นเรียนและค่าเบี่ยงเบนมาตรฐานของคะแนนในแต่ละชั้นเรียน การเปรียบเทียบคะแนน 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" คือที่ที่เราจะคำนวณคะแนน Z โดยใช้ผลลัพธ์ที่เก็บไว้ในเซลล์ 'ตัวช่วย'.

อันดับแรกเราจะคำนวณค่าเฉลี่ยของค่าโดยใช้ฟังก์ชั่น“ AVERAGE” เลือกเซลล์ที่คุณจะเก็บผลลัพธ์ของฟังก์ชัน“ AVERAGE”.

พิมพ์สูตรต่อไปนี้แล้วกด เข้าสู่ -หรือใช้เมนู "สูตร".

= เฉลี่ย (E2: E13)

ในการเข้าถึงฟังก์ชั่นผ่านเมนู“ สูตร” ให้เลือกเมนูแบบเลื่อนลง“ ฟังก์ชั่นเพิ่มเติม” เลือกตัวเลือก“ ทางสถิติ” จากนั้นคลิกที่“ AVERAGE”

ในหน้าต่างอาร์กิวเมนต์ของฟังก์ชันให้เลือกเซลล์ทั้งหมดในคอลัมน์ "ค่า" เป็นอินพุตสำหรับฟิลด์ "Number1" คุณไม่จำเป็นต้องกังวลเกี่ยวกับช่อง“ หมายเลข 2”.

ตอนนี้กด“ ตกลง”

ต่อไปเราต้องคำนวณค่าเบี่ยงเบนมาตรฐานของค่าโดยใช้ฟังก์ชั่น“ 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 เราสามารถใช้สูตรง่าย ๆ ที่อ้างอิงเซลล์ที่มีผลลัพธ์ของฟังก์ชัน“ AVERAGE” และ“ STDEV.S” หรือ“ STDEV.P”.

เลือกเซลล์แรกในคอลัมน์“ Z-Score” เราจะใช้ผลลัพธ์ของฟังก์ชัน“ STDEV.S” สำหรับตัวอย่างนี้ แต่คุณสามารถใช้ผลลัพธ์จาก“ STDEV.P”

พิมพ์สูตรต่อไปนี้และกด Enter:

= (E3- $ G $ 3) / $ H $ 3

หรือคุณสามารถใช้ขั้นตอนต่อไปนี้เพื่อป้อนสูตรแทนการพิมพ์:

1. คลิกเซลล์ F3 และพิมพ์ = (
2. เลือกเซลล์ E3 (คุณสามารถกดปุ่ม ลูกศรซ้ายคีย์ หนึ่งครั้งหรือใช้เมาส์)
3. พิมพ์เครื่องหมายลบ -
4. เลือกเซลล์ G3 จากนั้นกด F4 เพื่อเพิ่มอักขระ“ $” เพื่อทำการอ้างอิง 'สัมบูรณ์' ไปยังเซลล์ (ซึ่งจะวนไปตาม "G3"> "$G$3 ">“ G$3 ">“$G3 ">" G3 "หากคุณกดต่อไป F4)
5. ชนิด ) /
6. เลือกเซลล์ H3 (หรือ I3 หากคุณใช้“ STDEV.P”) แล้วกด F4 เพื่อเพิ่มอักขระ“ $” สองตัว.
7. กดปุ่มตกลง

คำนวณคะแนน Z สำหรับค่าแรก มันคือ 0.15945 ส่วนเบี่ยงเบนมาตรฐานต่ำกว่าค่าเฉลี่ย ในการตรวจสอบผลลัพธ์คุณสามารถคูณค่าเบี่ยงเบนมาตรฐานด้วยผลลัพธ์นี้ (6.271629 * -0.15945) และตรวจสอบว่าผลลัพธ์นั้นเท่ากับความแตกต่างระหว่างค่าและค่าเฉลี่ย (499-500) ผลลัพธ์ทั้งสองเท่ากันดังนั้นค่าจึงสมเหตุสมผล.

ลองคำนวณคะแนน Z ของค่าที่เหลือ ไฮไลต์คอลัมน์ 'คะแนน Z' ทั้งหมดที่เริ่มต้นด้วยเซลล์ที่มีสูตร.

กด Ctrl + D ซึ่งคัดลอกสูตรในเซลล์ด้านบนลงไปยังเซลล์อื่น ๆ ที่เลือกทั้งหมด.

ตอนนี้สูตรได้ถูก 'เติมเต็ม' ให้กับเซลล์ทั้งหมดและแต่ละเซลล์จะอ้างอิงเซลล์ "AVERAGE" และ "STDEV.S" หรือ "STDEV.P" ที่ถูกต้องเสมอเพราะอักขระ "$" หากคุณได้รับข้อผิดพลาดให้ย้อนกลับและตรวจสอบให้แน่ใจว่ามีอักขระ“ $” รวมอยู่ในสูตรที่คุณป้อน.

การคำนวณคะแนน Z โดยไม่ต้องใช้เซลล์ 'ตัวช่วย'

เซลล์ตัวช่วยจัดเก็บผลลัพธ์เช่นเดียวกับที่เก็บผลลัพธ์ของฟังก์ชัน“ AVERAGE,”“ STDEV.S,” และ“ STDEV.P” มันมีประโยชน์ แต่ก็ไม่จำเป็นเสมอไป คุณสามารถข้ามไปพร้อมกันเมื่อคำนวณคะแนน Z โดยใช้สูตรทั่วไปต่อไปนี้แทน.

นี่คือหนึ่งในการใช้ฟังก์ชัน“ STDEV.S”:

= (ราคาเฉลี่ย (ค่า)) / STDEV.S (ค่า)

และอีกอันใช้ฟังก์ชัน“ STEV.P”:

= (ราคาเฉลี่ย (ค่า)) / STDEV.P (ค่า)

เมื่อเข้าสู่ช่วงเซลล์สำหรับ“ ค่า” ในฟังก์ชั่นตรวจสอบให้แน่ใจว่าได้เพิ่มการอ้างอิงแบบสัมบูรณ์ (“ $” โดยใช้ F4) เพื่อที่ว่าเมื่อคุณ 'เติมเต็ม' คุณไม่ได้คำนวณค่าเบี่ยงเบนมาตรฐาน ของเซลล์ในทุกสูตร.

หากคุณมีชุดข้อมูลขนาดใหญ่อาจมีประสิทธิภาพมากกว่าในการใช้เซลล์ตัวช่วยเพราะมันจะไม่คำนวณผลลัพธ์ของฟังก์ชัน“ AVERAGE” และ“ STDEV.S” หรือ“ STDEV.S” ในแต่ละครั้งช่วยประหยัดทรัพยากรโปรเซสเซอร์และ เร่งเวลาที่ใช้ในการคำนวณผลลัพธ์.

นอกจากนี้“ $ G $ 3” ใช้เวลาน้อยลงในการจัดเก็บและ RAM โหลดน้อยกว่า“ AVERAGE ($ E $ 3: $ E $ 12)” สิ่งนี้มีความสำคัญเนื่องจาก Excel รุ่น 32 บิตมาตรฐานนั้น จำกัด อยู่ที่ 2GB ของ RAM (รุ่น 64 บิตไม่มีข้อ จำกัด ใด ๆ เกี่ยวกับจำนวน RAM ที่สามารถใช้ได้).