อย่างที่คุณทราบ มีการคำนวณทางคณิตศาสตร์มากมายที่คุณสามารถทำได้ด้วยMicrosoft Excel ^{(Microsoft Excel)}ในบทช่วยสอนนี้ เราจะแนะนำคุณตลอดขั้นตอนที่เกี่ยวข้องกับการคำนวณคะแนนZ^(Z-Score)ในExcel

หากนี่เป็นครั้งแรกที่คุณใช้Excelเราขอแนะนำให้คุณใช้เวลาห้านาทีเพื่ออ่านบทแนะนำสำหรับผู้เริ่มต้นใช้งาน Microsoft Excel^{(Microsoft Excel beginner’s tutorial)}นี้ คุณจะได้เรียนรู้ว่าฟังก์ชันที่จำเป็นทำงานอย่างไร ทางลัดการนำทาง การสร้างเวิร์กบุ๊ก การจัดรูปแบบข้อมูล และทุกสิ่งที่ควรรู้เกี่ยวกับการใช้โปรแกรมสเปรดชีตเป็นมือใหม่

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

Z-Score คืออะไร?

Z-Score (หรือที่เรียกว่า “คะแนนมาตรฐาน”) เป็นตัวชี้วัดที่เน้นความสัมพันธ์ระหว่างค่าในการแจกแจง อธิบายตำแหน่งของค่าในชุดข้อมูลที่สัมพันธ์กับค่าเฉลี่ยและส่วนเบี่ยงเบนมาตรฐานได้อย่างแม่นยำยิ่งขึ้น Z-Score ช่วยให้สามารถวัดและเปรียบเทียบค่าในชุดข้อมูลได้อย่างแม่นยำ

สูตรทางคณิตศาสตร์สำหรับการคำนวณZ-Scoreคือ(x-µ) / σ ; โดยที่x = Cell Value , µ = Meanและσ = Standard Deviationมาตรฐาน

บางครั้งบริษัทต่างๆ ใช้Z-Scoreเพื่อคาดการณ์และประเมินการล้มละลายที่กำลังจะเกิดขึ้น นอกจากนี้ยังเป็นตัวชี้วัดที่ยอดเยี่ยมสำหรับการตรวจสอบสถานะทางการเงินของสถาบัน นักวิจัย^{(Researchers)}ยังใช้Z-Scoreเพื่อเปรียบเทียบการสังเกตที่ได้จากกลุ่มตัวอย่างหรือประชากรที่แตกต่างกัน

วิธีการคำนวณคะแนน Z ใน Excel

เนื่องจากZ-Scoreเป็นฟังก์ชันของค่าเฉลี่ยและค่าเบี่ยงเบนมาตรฐาน คุณจะต้องคำนวณค่าเฉลี่ยและค่าเบี่ยงเบนมาตรฐานของชุดข้อมูลของคุณก่อน แม้ว่าคุณจะสามารถแยกค่าเฉลี่ยและค่าเบี่ยงเบนมาตรฐานในเซลล์ใดก็ได้ แต่เราได้สร้างคอลัมน์เฉพาะสำหรับ "ค่าเฉลี่ย" และ "ค่าเบี่ยงเบนมาตรฐาน" ในเวิร์กชีตของเรา นอกจากนี้เรายังได้สร้างคอลัมน์สำหรับ “ Z-Score ”

เอกสารตัวอย่างของเราประกอบด้วยการจัดอันดับประสิทธิภาพของพนักงาน 10 คนในบริษัทกระดาษ ตอนนี้ มาคำนวณคะแนน Z^(Z-Score)ของการให้คะแนนของพนักงานกัน

คำนวณค่าเฉลี่ยเฉลี่ย^{(Calculate Mean Average)}

ในการคำนวณค่าเฉลี่ยของชุดข้อมูลของคุณ ให้พิมพ์=AVERAGE(เลือกค่าแรก^{(first value)}ในชุดข้อมูล กดแป้นคอลัมน์^{(column key)}เลือกค่าสุดท้าย^{(last value)}ภายในช่วงชุดข้อมูล กดปุ่มวงเล็บปิด^{(closing parenthesis)}แล้วกดEnterสูตรควรมีลักษณะดังนี้ เช่นเดียวกับด้านล่าง:

=AVERAGE(B2:B11)

คุณควรเห็นค่าเฉลี่ยหรือค่าเฉลี่ยของชุดข้อมูลในเซลล์ที่คุณป้อนสูตร

คำนวณค่าเบี่ยงเบนมาตรฐาน^{(Calculate Standard Deviation)}

Excel ยังทำให้การคำนวณค่าเบี่ยงเบนมาตรฐานของชุดข้อมูลของคุณ^{(calculate the standard deviation of your dataset)}เป็นเรื่องง่ายด้วยการคลิกเพียงไม่กี่ครั้ง 

เลือกเซลล์ในคอลัมน์ "ค่าเบี่ยงเบนมาตรฐาน" พิมพ์=STDEV.P(จากนั้นเลือกค่าแรก^{(first value)}ในช่วง กดปุ่มคอลัมน์^{(column key)}เลือกค่าสุดท้าย ป้อนวงเล็บปิด^{(closing parenthesis)}แล้วกดEnterหากคุณ สงสัยสูตรผลลัพธ์ควรจะคล้ายกับด้านล่าง:

=STDEV.P(B2:B11)

คำนวณคะแนน Z ใน Excel: วิธีที่ 1^{(Calculate Z-Score in Excel: Method 1)}

Excelมีฟังก์ชันSTANDARDIZE ที่ให้ค่า ^{(STANDARDIZE)}Z-Scoreของชุดข้อมูลในการแจกแจง เลือกเซลล์แรกใน คอลัมน์ คะแนน Z^(Z-Score)และทำตามขั้นตอนด้านล่าง

1. ไปที่ แท็บ สูตร^(Formulas)และเลือกสูตร^{(More Formulas)}เพิ่มเติม

1. เลื่อนเมาส์ไปที่ ตัวเลือก สถิติ^{(Statistical)}แล้วเลือกSTANDARDIZE

ซึ่งจะเป็นการเปิด หน้าต่าง อาร์กิวเมนต์ฟังก์ชัน^{(Function Arguments)} ใหม่ ซึ่งคุณจะสามารถคำนวณคะแนน Z^(Z-Score)ของการแจกแจงได้

1. ป้อน^(Enter)การอ้างอิงเซลล์ของค่าแรกในช่อง "X"

1. ป้อน^(Enter)การอ้างอิงเซลล์ของค่าเฉลี่ยเลขคณิตในช่อง "ค่าเฉลี่ย" แล้วกดF4บนแป้นพิมพ์เพื่อล็อกการอ้างอิงเซลล์

1. สุดท้าย ให้ป้อนการอ้างอิงเซลล์ของค่าเบี่ยงเบนมาตรฐานในช่อง "Standard_dev" แล้วกดF4เพื่อล็อกการอ้างอิงเซลล์ เครื่องมือจะแสดงตัวอย่างค่าZ ^(Z-value)กดตกลง^(OK)เพื่อดำเนินการต่อ

ในการรับZ-Scoreสำหรับค่าอื่นๆ ให้เลื่อนเคอร์เซอร์ไปที่มุมล่างขวาของเซลล์แล้วลากไอคอนplus (+) iconลงไปที่คอลัมน์

Excelจะคัดลอกสูตรลงในคอลัมน์ และสร้างZ-Score โดยอัตโนมัติ สำหรับค่าอื่นๆ ในแถวที่เกี่ยวข้อง

คำนวณ Z-Score ใน Excel: วิธีที่ 2^{(Calculate Z-Score In Excel: Method 2)}

ดังที่กล่าวไว้ก่อนหน้านี้ คุณสามารถรับค่า Z-Score ของจุดข้อมูลได้โดยการลบค่าเฉลี่ยของชุดข้อมูลออกจากจุดข้อมูลและหารผลลัพธ์ด้วยค่าเบี่ยงเบนมาตรฐาน เมื่อใช้ (x-µ) / σ คุณสามารถคำนวณ z-score ในExcelได้โดยป้อนค่าเหล่านี้ด้วยตนเอง 

1. เลือกเซลล์แรกใน คอลัมน์ คะแนน Z^(Z-Score)พิมพ์เครื่องหมายเท่ากับ ( = ) ตามด้วยวงเล็บเปิด^{(open parenthesis)}และเลือกค่าแรกในคอลัมน์ที่มีชุดข้อมูลที่มีคะแนน Z ที่^(Z-Score)คุณต้องการคำนวณ หลังจากนั้น^(Afterward)ให้พิมพ์เครื่องหมายยัติภังค์^(hyphen)เลือกค่าเฉลี่ยเลขคณิต กดF4เพื่อสร้างค่ากลางแบบสัมบูรณ์/คงที่ แล้วกดไอคอนวงเล็บปิด ^{(close parenthesis)}สุดท้าย ให้กด แป้น เครื่องหมายทับ^{(forward-slash)} ( / ) เลือกค่าเบี่ยงเบนมาตรฐาน แล้วกดF4เพื่อล็อกการอ้างอิงเซลล์

สูตรสุดท้ายควรมีลักษณะดังนี้: =(B2-$C$2)/$D$2 2 กดEnterเพื่อดำเนินการตามสูตร

โปรดทราบว่าสูตรจะคำนวณเฉพาะคะแนน Z^(Z-Score)สำหรับค่าแรกในเซลล์ที่เลือก

1. เลื่อน^(Hover)เมาส์ไปที่มุมล่างขวาของ เซลล์ Z-Scoreเซลล์แรก แล้วลากไอคอนเครื่องหมายplus (+) iconลงไปที่คอลัมน์

การตีความ Z-Score

ชุดข้อมูลของคุณมักจะประกอบด้วยคะแนน Z^(Z-Score) เชิงลบและ บวก Z-Score ที่ เป็นบวกแสดงว่าค่า/คะแนนสูงกว่าค่าเฉลี่ยของชุดข้อมูล ค่า Z-Score^(Z-Score)เชิงลบบอกสิ่งตรงกันข้าม: ค่าต่ำกว่าค่าเฉลี่ย หากZ-Score ของดาต้าพอยท์ เป็นศูนย์ (0) นั่นเป็นเพราะค่าของจุดนั้นเท่ากับค่าเฉลี่ยเลขคณิต

ยิ่งจุดข้อมูลใหญ่ ค่าZ-Scoreก็ยิ่งสูงขึ้น ดูแผ่นงานของคุณแล้วคุณจะรู้ว่าค่าขนาดเล็กมีค่าZ-Score ต่ำ กว่า ใน ทำนองเดียวกัน^(Likewise)ค่าที่น้อยกว่าค่าเฉลี่ยเลขคณิตจะมีค่าZ-Scoreลบ

ตัวอย่างเช่น ในแผ่นงานตัวอย่าง คุณจะพบว่า “ไมเคิล” มีคะแนนสูงสุด (78) และคะแนนZ^(Z-Score) สูงสุด (1.679659) ในทางกลับกัน “Dwight” และ “Kevin” มีคะแนนต่ำสุด (34) และZ-Score ต่ำสุด ( -1.59047 )

มา เป็น^(Become)ผู้เชี่ยวชาญExcel : ที่เก็บบท ช่วยสอน

ตอนนี้คุณรู้วิธีคำนวณZ-Scoreของชุดข้อมูลแล้ว แสดงความคิดเห็นด้านล่างหากคุณมีคำถามหรือคำแนะนำที่เป็นประโยชน์อื่นๆ เกี่ยวกับการคำนวณZ  -Score^(Z-Score)ในExcel

เราขอแนะนำให้อ่านบทแนะนำเกี่ยวกับ Excel เกี่ยวกับการคำนวณความแปรปรวน^{(calculating variance)}การลบแถวที่ซ้ำกัน^{(removing duplicate rows)}ในชุดข้อมูลของคุณ และวิธีใช้ฟังก์ชันสรุปใน Excel^{(use summary functions in Excel)}เพื่อสรุปข้อมูล 

How to Calculate Z-Score in Excel

As you know, there are many mathematical calculatiоns you can perform with Microsoft Excel. In this tυtorial, we’ll walk you through the steps involved in calculating Z-Score in Excel.

If this is your first time using Excel, we recommend you take five minutes to peruse this Microsoft Excel beginner’s tutorial. You’ll learn how essential functions work, navigation shortcuts, creating workbooks, formatting data, and everything there is to know about using the spreadsheet program as a beginner.

When you’re done with that, proceed to the next section to learn how to calculate Z score in Excel. But first, let’s briefly explain Z-Score, its uses, why you might need to calculate one, and how to get it done.

What Is a Z-Score?

Z-Score (also known as a “Standard Score”) is a metric that highlights the relationship between values in a distribution. More precisely, it describes the position of values in a dataset with respect to their mean and standard deviation. Z-Score allows for accurate measurement and comparison of values in a dataset.

The mathematical formula for calculating Z-Score is (x-µ) / σ; where x = Cell Value, µ = Mean, and σ = Standard Deviation.

Companies sometimes use Z-Score to forecast and estimate impending bankruptcy. Additionally, it’s a great metric for ascertaining the financial position of an institution. Researchers also utilize Z-Score to compare observations obtained from different samples or populations.

How to Calculate Z-Score in Excel

Since Z-Score is a function of mean and standard deviation, you’ll need to first calculate the average and standard deviation of your dataset. Although you can extract the mean and standard deviation in any cell, we created dedicated columns for  “Mean” and “Standard Deviation” in our worksheet. We also created a column for “Z-Score.”

Our sample document contains the performance ratings of 10 employees in a paper company. Now let’s calculate the Z-Score of the employees’ rating.

Calculate Mean Average

To calculate the mean average of your dataset, type =AVERAGE(, select the first value in the dataset, press the column key, select the last value within the dataset range, press the closing parenthesis key, and press Enter. The formula should look like the one below:

=AVERAGE(B2:B11)

You should see the mean or average value of the dataset in the cell you entered the formula.

Calculate Standard Deviation

Excel also makes it pretty easy to calculate the standard deviation of your dataset in a few clicks. 

Select a cell in the “Standard Deviation” column, type =STDEV.P(, then select the first value in the range, press the column key, select the last value, enter the closing parenthesis, and press Enter. If you’re in doubt, the resulting formula should be similar to the one below:

=STDEV.P(B2:B11)

Calculate Z-Score in Excel: Method 1

Excel has a STANDARDIZE function that provides the Z-Score of a dataset in a distribution. Select the first cell in the Z-Score column and follow the steps below.

1. Go to the Formulas tab and select More Formulas.

1. Hover your mouse on the Statistical option and select the STANDARDIZE.

That’ll launch a new Function Arguments window where you’ll be able to calculate the Z-Score of the distribution.

1. Enter the cell reference of the first value in the “X” field.

1. Enter the cell reference of the arithmetic average in the “Mean” field and press the F4 on your keyboard to lock the cell reference.

1. Finally, enter the standard deviation’s cell reference in the “Standard_dev” field and press F4 to lock the cell reference. The tool will display a preview of the Z-value. Press OK to proceed.

To get the Z-Score for other values, hover the cursor to the bottom-right corner of the cell and drag the plus (+) icon down the column.

Excel will copy the formula down the column and automatically generate the Z-Score for other values in the corresponding rows.

Calculate Z-Score In Excel: Method 2

As mentioned earlier, you can obtain a datapoint’s Z-Score by subtracting the mean of the dataset from the datapoint and dividing the result by the standard deviation. Using the (x-µ) / σ, you can calculate z-score in Excel by inputting these values manually. 

1. Select the first cell in the Z-Score column, type an equal sign (=) followed by an open parenthesis, and select the first value in the column containing the datasets whose Z-Score you want to calculate. Afterward, type a hyphen, select the arithmetic mean, press F4 to make the mean absolute/fixed, and press the close parenthesis icon. Finally, press the forward-slash (/) key, select the standard deviation, and press F4 to lock the cell reference.

The final formula should look similar to this: =(B2-$C$2)/$D$2. Press Enter to execute the formula.

Note that the formula will only calculate the Z-Score for the first value in the selected cell.

1. Hover your mouse to the bottom-right corner of the first Z-Score cell and drag the plus (+) icon down the column.

Interpreting Z-Score

Your dataset will most likely contain a mix of negative and positive Z-Scores. A positive Z-Score indicates that the value/score is higher than the dataset’s mean average. A negative Z-Score, of course, tells the opposite: the value lies below the mean average. If a datapoint’s Z-Score is zero (0), that’s because its value equals the arithmetic mean.

The bigger a data point, the higher its Z-Score. Go through your worksheet and you’ll realize that the small values have lower Z-Scores. Likewise, values smaller than the arithmetic mean will have negative Z-Scores.

In our sample worksheet, for instance, you’ll discover that “Michael” had the highest rating (78) and highest Z-Score (1.679659). “Dwight” and “Kevin” on the other hand both had the lowest ratings (34) and lowest Z-Score (-1.59047).

Become an Excel Expert: Tutorial Repository

You now know how to calculate the Z-Score of a data set. Drop a comment below if you have questions or other useful tips regarding calculating Z-Score in Excel. 

We recommend reading Excel-related tutorials on calculating variance, removing duplicate rows in your dataset, and how to use summary functions in Excel to summarize data. 

Related posts

• วิธีการลบบรรทัดว่างใน Excel

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• วิธีแทรกแผ่นงาน Excel ลงใน Word Doc

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• วิธีแก้ไขแถวใน Excel

• วิธีใช้ Flash Fill ใน Excel

• วิธีใช้คำสั่ง if และ Nested If ใน Excel

• ความแตกต่างระหว่าง Microsoft Excel Online และ Excel สำหรับเดสก์ท็อป

• 5 วิธีในการแปลงข้อความเป็นตัวเลขใน Excel

• รักษาการอ้างอิงเซลล์เมื่อคัดลอกสูตรใน Excel

• วิธีแทรกหลายแถวอย่างรวดเร็วใน Excel

• 2 วิธีในการใช้ฟังก์ชัน Transpose ของ Excel

• วิธีสร้าง Pivot Table แบบง่ายใน Excel

• วิธีการย้ายคอลัมน์ใน Excel

• วิธีลบเส้นตารางใน Excel

• ทำไมคุณควรใช้ช่วงที่มีชื่อใน Excel

• 4 วิธีในการใช้เครื่องหมายถูกใน Excel

• วิธีการเขียนสูตร IF/คำสั่งใน Excel

• การใช้เครื่องมือค้นหาเป้าหมายการวิเคราะห์แบบ What-If ของ Excel

• Google ชีตกับ Microsoft Excel - อะไรคือความแตกต่าง?