Cách Tính Z‑score Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Cách Tính Khoản Thanh Toán Hàng Tháng Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Giới thiệu tổng quan về Z‑Score

Z‑Score (hay còn gọi là điểm chuẩn, điểm z) là một chỉ số thống kê giúp chuẩn hoá dữ liệu, cho phép so sánh các giá trị thuộc các tập hợp khác nhau bằng cách chuyển đổi chúng về một thang đo chung – trung bình bằng 0 và độ lệch chuẩn bằng 1. Khi một quan sát có Z‑Score = 0, nghĩa là giá trị đó bằng trung bình của mẫu; Z‑Score > 0 cho thấy giá trị lớn hơn trung bình, trong khi Z‑Score < 0 cho thấy giá trị nhỏ hơn trung bình.

1.1 Công thức tính Z‑Score

Z‑Score của một quan sát (x_i) được tính bằng:

Z_i = \frac{x_i – \mu}{\sigma}

Trong đó:

• (x_i) = giá trị quan sát cần chuẩn hoá.
• (\mu) = trung bình mẫu (mean).
• (\sigma) = độ lệch chuẩn mẫu (standard deviation).

Nếu bạn làm việc với toàn bộ dân số (population) thay vì mẫu (sample), công thức vẫn giống nhau, chỉ khác ở cách tính (\sigma) (độ lệch chuẩn dân số vs. mẫu).

1.2 Ứng dụng thực tiễn

• Phát hiện ngoại lệ (outlier): Các giá trị có |Z| > 3 thường được coi là ngoại lệ.
• So sánh giữa các biến: Khi các biến có đơn vị khác nhau (độ tuổi, thu nhập, điểm kiểm tra…), Z‑Score giúp đưa chúng về cùng thang đo.
• Chuẩn hoá dữ liệu cho mô hình Machine Learning: Nhiều thuật toán (như K‑Nearest Neighbors, SVM, Logistic Regression) yêu cầu dữ liệu đã được chuẩn hoá.

Có thể bạn quan tâm: Cách Tính Khoảng Tứ Phân (interquartile Range) Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Tại sao nên dùng Excel để tính Z‑Score?

Excel là công cụ phổ biến nhất trong môi trường văn phòng và phân tích dữ liệu cơ bản. Lý do bạn nên dùng Excel để tính Z‑Score:

1. Dễ tiếp cận: Không cần cài đặt phần mềm thống kê chuyên sâu.
2. Tính năng tích hợp sẵn: Các hàm AVERAGE, STDEV.P, STDEV.S, STANDARDIZE… đã có sẵn.
3. Tự động cập nhật: Khi dữ liệu thay đổi, các công thức sẽ tự động tính lại Z‑Score.
4. Khả năng visual: Bạn có thể nhanh chóng vẽ biểu đồ histogram, box‑plot để quan sát phân phối sau khi chuẩn hoá.

Có thể bạn quan tâm: Cách Tính Khoảng Cách Ngày Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

3. Chuẩn bị dữ liệu trong Excel

3.1 Định dạng dữ liệu

Giả sử bạn có một bảng dữ liệu đơn giản với các cột:

• Cột A: Mã định danh (có thể bỏ qua khi tính Z‑Score).
• Cột B: Dữ liệu số mà bạn muốn chuẩn hoá.

3.2 Kiểm tra dữ liệu

• Loại bỏ giá trị trống (#N/A, "").
• Kiểm tra kiểu dữ liệu: Đảm bảo cột B chứa số, không phải chuỗi văn bản.
• Xác định mẫu vs. dân số: Nếu bạn có toàn bộ dữ liệu của một tập hợp (ví dụ: điểm số của toàn lớp), dùng độ lệch chuẩn dân số (STDEV.P). Nếu chỉ là mẫu (ví dụ: một phần mẫu trong khảo sát), dùng độ lệch chuẩn mẫu (STDEV.S).

4. Các cách tính Z‑Score trong Excel

4.1 Sử dụng công thức thủ công (AVERAGE + STDEV)

Bước 1: Tính trung bình (Mean)

Trong một ô trống (ví dụ C1), nhập:

=AVERAGE(B2:B101)

Giả sử dữ liệu từ B2 đến B101 (100 quan sát).

Bước 2: Tính độ lệch chuẩn (Standard Deviation)

• Nếu dữ liệu là mẫu, dùng STDEV.S:

=STDEV.S(B2:B101)

• Nếu dữ liệu là dân số, dùng STDEV.P:

=STDEV.P(B2:B101)

Lưu kết quả ở ô C2 (hoặc bất kỳ ô nào bạn muốn).

Bước 3: Tính Z‑Score cho từng quan sát

Ở ô D2 (cạnh giá trị B2), nhập công thức:

=(B2-$C$1)/$C$2

• $C$1 và $C$2 là tham chiếu tuyệt đối tới trung bình và độ lệch chuẩn, giúp công thức kéo xuống dễ dàng.

Sau đó, kéo công thức từ D2 xuống D101 để tính Z‑Score cho toàn bộ quan sát.

Bước 4: Kiểm tra kết quả

• Trung bình của cột Z‑Score (cột D) nên gần 0.
• Độ lệch chuẩn của cột Z‑Score nên gần 1.

Bạn có thể dùng:

=AVERAGE(D2:D101) // Kiểm tra trung bình
=STDEV.S(D2:D101) // Kiểm tra độ lệch chuẩn (nên gần 1)

4.2 Dùng hàm STANDARDIZE (Excel 2026 trở lên)

Excel cung cấp hàm STANDARDIZE để tính Z‑Score một cách ngắn gọn:

=STANDARDIZE(x, mean, standard_dev)

Trong thực tế, công thức ở ô D2 sẽ là:

=STANDARDIZE(B2, $C$1, $C$2)

Bạn chỉ cần nhập công thức này và kéo xuống. Hàm này thực hiện cùng một phép tính như công thức thủ công, nhưng ngắn gọn hơn và giảm nguy cơ lỗi khi nhập công thức.

4.3 Sử dụng Power Query để chuẩn hoá dữ liệu (dành cho dữ liệu lớn)

Nếu bạn có hàng ngàn hoặc hàng triệu dòng, việc tính thủ công trong các ô có thể làm chậm workbook. Power Query (trong Excel 2026 trở lên) cho phép bạn thực hiện chuẩn hoá dữ liệu trong môi trường ETL (Extract‑Transform‑Load).

Các bước thực hiện:

1. Import dữ liệu vào Power Query:
2. Chọn Data → Get Data → From Table/Range.
3. Thêm cột trung bình và độ lệch chuẩn:
4. Trong Power Query Editor, vào Add Column → Statistics → Average → Chọn cột giá trị.
5. Lặp lại, chọn Standard Deviation.
6. Tạo cột Z‑Score:
7. Vào Add Column → Custom Column.
8. Đặt tên “ZScore”, công thức:

powerquery (Giá trị - Average) / Standard Deviation

1. Xóa các cột tạm thời (Average, Standard Deviation) nếu không cần.
2. Load về Excel:
3. Click Close & Load.

Power Query sẽ tự động cập nhật Z‑Score mỗi khi dữ liệu nguồn thay đổi, mà không làm nặng file Excel.

Có thể bạn quan tâm: Cách Tính P‑value Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

4.4 Sử dụng VBA (Macro) để tự động hoá

Đối với người dùng muốn một nút “Calculate Z‑Score” chỉ cần click một lần, VBA là lựa chọn tốt. Dưới đây là một macro mẫu:

Sub CalculateZScore() Dim ws As Worksheet Dim rng As Range Dim meanVal As Double, stdVal As Double Dim i As Long Set ws = ActiveSheet ' Giả sử dữ liệu ở cột B, bắt đầu từ B2 Set rng = ws.Range("B2", ws.Cells(ws.Rows.Count, "B").End(xlUp)) meanVal = Application.WorksheetFunction.Average(rng) stdVal = Application.WorksheetFunction.StDev_S(rng) ' dùng StDev_P nếu dân số ' Thêm tiêu đề cho cột Z‑Score ws.Range("C1").Value = "Z‑Score" For i = 2 To rng.Rows.Count + 1 ws.Cells(i, "C").Value = (ws.Cells(i, "B").Value - meanVal) / stdVal Next i MsgBox "Hoàn thành! Z‑Score đã được tính và lưu ở cột C.", vbInformation
End Sub

• Cách sử dụng:
• Nhấn Alt + F11 → Insert → Module → Dán code trên.
• Đóng VBA Editor, quay lại Excel, vào Developer → Macros → Chạy CalculateZScore.

Macro sẽ tự động tính trung bình, độ lệch chuẩn và điền Z‑Score vào cột C.

5. Kiểm tra và diễn giải kết quả Z‑Score

55.1 Kiểm tra tính hợp lệ

• Trung bình gần 0:

=ROUND(AVERAGE(D2:D101),4) // Kết quả nên là 0.0000 (hoặc rất gần)

• Độ lệch chuẩn gần 1:

=ROUND(STDEV.S(D2:D101),4) // Kết quả nên là 1.0000 (hoặc rất gần)

Nếu không, có thể do:

• Dùng sai hàm độ lệch chuẩn (population vs. sample).
• Dữ liệu chứa giá trị ngoại lệ cực đoan làm trung bình và độ lệch chuẩn bị sai lệch.

5.2 Diễn giải Z‑Score

Ví dụ: Nếu một học sinh có Z‑Score = 2.5 trong điểm kiểm tra, nghĩa là điểm của học sinh này cao hơn trung bình khoảng 2.5 độ lệch chuẩn, thuộc khoảng 0.6% (theo bảng chuẩn) của học sinh có điểm cao nhất.

6. Ứng dụng thực tiễn với Excel

6.1 Phát hiện ngoại lệ trong dữ liệu bán hàng

Giả sử bạn có dữ liệu doanh thu hàng ngày của một cửa hàng trong cột B. Sau khi tính Z‑Score, bạn lọc các giá trị có |Z| > 3 để xác định ngày có doanh thu bất thường (có thể do lỗi nhập liệu hoặc sự kiện đặc biệt).

=IF(ABS(D2)>3,"Ngoại lệ","Bình thường")

6.2 So sánh hiệu suất của hai phòng ban

Bạn có hai bộ dữ liệu:

• Phòng A: Cột B – thời gian hoàn thành dự án (ngày).
• Phòng B: Cột C – thời gian hoàn thành dự án (ngày).

Tính Z‑Score riêng cho mỗi phòng, sau đó tạo cột “DiffZ” = Z‑Score A – Z‑Score B. Nếu DiffZ > 0, phòng A tốt hơn (hoàn thành nhanh hơn) so với trung bình của phòng B.

6.3 Chuẩn hoá dữ liệu cho mô hình hồi quy

Khi xây dựng mô hình hồi quy trong Excel (Data Analysis → Regression), chuẩn hoá các biến độc lập bằng Z‑Score giúp giảm đa cộng tuyến và cải thiện độ ổn định của hệ số. Bạn chỉ cần tạo các cột Z‑Score cho từng biến, rồi đưa chúng vào mô hình.

7. Lưu ý khi sử dụng Z‑Score trong Excel

1. Không dùng Z‑Score cho dữ liệu không phân phối chuẩn: Nếu dữ liệu có phân phối lệch mạnh (skewed) hoặc có nhiều giá trị trùng lặp, Z‑Score có thể không phản ánh đúng mức độ “khác thường”. Cân nhắc dùng Box‑Cox transformation hoặc log transformation trước khi chuẩn hoá.
2. Kiểm tra outlier trước khi tính: Các ngoại lệ cực đoan có thể làm trung bình và độ lệch chuẩn bị sai lệch, làm Z‑Score không đáng tin cậy.
3. Độ lệch chuẩn mẫu vs. dân số: Nhớ lựa chọn hàm STDEV.S (sample) hoặc STDEV.P (population) tùy vào mục tiêu phân tích.
4. Độ chính xác của Excel: Excel tính toán bằng độ chính xác 15 chữ số thập phân. Đối với dữ liệu cực kỳ lớn hoặc yêu cầu độ chính xác cao, có thể cần phần mềm thống kê chuyên sâu (R, Python).

8. Tổng kết

• Z‑Score là công cụ mạnh mẽ để chuẩn hoá dữ liệu, giúp so sánh, phát hiện ngoại lệ và chuẩn bị dữ liệu cho các thuật toán máy học.
• Excel cung cấp nhiều cách để tính Z‑Score: công thức thủ công, hàm STANDARDIZE, Power Query, và VBA. Mỗi cách có ưu điểm riêng, phù hợp với quy mô và nhu cầu của người dùng.
• Kiểm tra và diễn giải kết quả là bước không thể thiếu; luôn xác nhận trung bình ≈ 0 và độ lệch chuẩn ≈ 1, đồng thời hiểu ý nghĩa của các giá trị Z‑Score trong ngữ cảnh thực tế.

Với những kiến thức và công cụ trên, bạn đã sẵn sàng áp dụng Z‑Score trong mọi dự án phân tích dữ liệu trên Excel – từ việc kiểm tra chất lượng dữ liệu, phát hiện bất thường, đến việc chuẩn bị dữ liệu cho các mô hình dự báo phức tạp. Hãy bắt đầu ngay hôm nay và trải nghiệm sức mạnh của chuẩn hoá thống kê ngay trong môi trường Excel quen thuộc!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.