Tôi Dùng PERCENTILE.EXC Thay Vì PERCENTILE.INC Vì 3 Lý Do Này

Mọi thứ thay đổi khi tôi chuyển sang dùng PERCENTILE.EXC cho phân tích thống kê trong Excel. Trước đó, tôi luôn dùng PERCENTILE.INC vì nghĩ nó an toàn hơn khi chấp nhận mọi giá trị từ 0 đến 1. Nhưng kết quả phân tích của tôi liên tục bị chê là không khớp với các phần mềm thống kê khác.

Hóa ra PERCENTILE.EXC chính xác hơn theo định nghĩa thống kê truyền thống, mặc dù có giới hạn chặt chẽ hơn. Microsoft thêm hàm này từ Excel 2010 để khắc phục những hạn chế của hàm PERCENTILE cũ.

Mục Lục

PERCENTILE.EXC Loại Trừ Biên Ngoài Cùng

Chính xác hơn với định nghĩa thống kê chuẩn

Sự khác biệt lớn nhất nằm ở cách hai hàm xử lý giá trị biên. PERCENTILE.INC chấp nhận k từ 0 đến 1 bao gồm cả hai đầu mút. PERCENTILE.EXC loại trừ k=0 và k=1 hoàn toàn.

Tại sao điều này quan trọng: Trong thống kê, phân vị thứ 100 không tồn tại vì không có điểm dữ liệu nào mà 100% dữ liệu nằm dưới nó. Phân vị thứ 0 cũng vậy. PERCENTILE.EXC tuân theo logic này.

Công thức tính cũng khác:

PERCENTILE.INC: Vị trí = K × (N-1) + 1
PERCENTILE.EXC: Vị trí = K × (N+1)

Cách dùng đúng:

Với dữ liệu từ A2 đến A11 có 10 giá trị:

excel

=PERCENTILE.EXC(A2:A11, 0.25)  // Phân vị thứ 25 - hợp lệ
=PERCENTILE.EXC(A2:A11, 0.90)  // Phân vị thứ 90 - hợp lệ
=PERCENTILE.EXC(A2:A11, 0)     // Lỗi #NUM! - k không được bằng 0
=PERCENTILE.EXC(A2:A11, 1)     // Lỗi #NUM! - k không được bằng 1

Tôi chuyển sang PERCENTILE.EXC sau khi đối chiếu kết quả với phần mềm SPSS. Với cùng tập dữ liệu 100 điểm, PERCENTILE.EXC cho kết quả khớp hoàn toàn còn PERCENTILE.INC lệch 0.3 đến 0.8 điểm.

XEM THÊM: Làm Chủ Hàm T.INV.2T Trong 5 Phút: Cú Pháp Và Lỗi Thường Gặp

Giới Hạn k Rõ Ràng Tránh Lỗi Tính Toán

Phát hiện sai sót sớm thay vì cho kết quả sai

PERCENTILE.EXC có quy tắc nghiêm ngặt về giá trị k cho phép. Với n phần tử trong mảng, k phải nằm trong khoảng từ 1/(n+1) đến n/(n+1).

Ví dụ cụ thể: Tập dữ liệu có 9 giá trị (n=9):

Giới hạn dưới: 1/(9+1) = 0.1
Giới hạn trên: 9/(9+1) = 0.9
k từ 0.1 đến 0.9 hợp lệ
k = 0.05 hoặc k = 0.95 trả về lỗi #NUM!

Tại sao đây là ưu điểm:

Khi làm việc với tập dữ liệu nhỏ, yêu cầu phân vị quá cao hoặc quá thấp không có ý nghĩa thống kê. PERCENTILE.EXC báo lỗi ngay thay vì tính gần đúng theo cách có thể sai lệch.

excel

// Tập dữ liệu chỉ có 5 giá trị
=PERCENTILE.EXC(A2:A6, 0.95)   // Lỗi #NUM! - cảnh báo n quá nhỏ
=PERCENTILE.INC(A2:A6, 0.95)   // Trả về giá trị - nhưng độ tin cậy thấp

Cách xử lý khi gặp lỗi:

Kiểm tra số lượng phần tử trong mảng
Tính giới hạn k: 1/(n+1) và n/(n+1)
Điều chỉnh k về trong phạm vi cho phép
Hoặc thu thập thêm dữ liệu nếu cần phân vị cực trị

Tôi từng dùng PERCENTILE.INC tính phân vị thứ 99 cho 20 mẫu. Kết quả ra số nhưng sếp hỏi độ tin cậy thì không trả lời được. PERCENTILE.EXC với 20 mẫu chỉ tính được đến phân vị thứ 95, buộc tôi thu thêm 30 mẫu để đạt độ tin cậy cần thiết.

Nội Suy Chính Xác Với Dữ Liệu Rời Rạc

Kết quả khớp với phần mềm thống kê chuyên nghiệp

Khi phân vị yêu cầu nằm giữa hai giá trị trong mảng, Excel phải nội suy. Hai hàm dùng phương pháp khác nhau.

XEM THÊM: 3 Lỗi Thường Gặp Khi Dùng Hàm EXPON.DIST Và Cách Khắc Phục

Sự khác biệt trong nội suy:

Giả sử có 11 giá trị từ 10 đến 110 (cách đều 10 đơn vị), tìm phân vị thứ 25:

PERCENTILE.INC:

Vị trí = 0.25 × (11-1) + 1 = 3.5
Nội suy giữa vị trí 3 (30) và vị trí 4 (40)
Kết quả: 35

PERCENTILE.EXC:

Vị trí = 0.25 × (11+1) = 3
Giá trị tại vị trí 3
Kết quả: 30

Ứng dụng thực tế:

Tôi thường dùng PERCENTILE.EXC cho:

Phân tích điểm thi chuẩn hóa
Xác định ngưỡng lương theo phân vị thị trường
Đánh giá hiệu suất nhân viên theo thứ hạng
So sánh với dữ liệu từ SPSS, R, Python

Ví dụ với dữ liệu thực:

excel

// Điểm thi của 50 học sinh
=PERCENTILE.EXC(B2:B51, 0.75)  // Tìm điểm ở phân vị thứ 75

// Lương của 100 nhân viên
=PERCENTILE.EXC(C2:C101, 0.50) // Tìm lương trung vị

// Thời gian hoàn thành task
=PERCENTILE.EXC(D2:D201, 0.90) // 90% task hoàn thành trong bao lâu

Kiểm tra tính nhất quán:

Khi làm báo cáo phân tích lương cho 200 nhân viên, tôi xuất dữ liệu ra Python để kiểm chứng. Với phương pháp numpy.percentile mặc định, kết quả từ PERCENTILE.EXC khớp chính xác đến 0.01. PERCENTILE.INC lệch trung bình 2-5%.

Khi Nào Dùng Hàm Nào

PERCENTILE.EXC yêu cầu Excel 2010 trở lên. Không dùng được với Excel 2007 hoặc cũ hơn.

Dùng PERCENTILE.EXC khi:

Làm việc với dữ liệu thống kê chuẩn
Cần khớp kết quả với phần mềm khác
Tập dữ liệu đủ lớn (trên 20 phần tử)
Làm báo cáo khoa học hoặc nghiên cứu

XEM THÊM: Làm Chủ Hàm FORECAST.ETS.CONFINT Trong 10 Phút Với Ví Dụ Thực Tế

Dùng PERCENTILE.INC khi:

Tập dữ liệu rất nhỏ (dưới 10 phần tử)
Cần tính phân vị cực trị (0, 1, 99, 100)
Làm việc với Excel cũ hơn 2010
Cần tương thích ngược với file cũ

Lưu ý về lỗi:

Nếu gặp lỗi #NUM! với PERCENTILE.EXC, kiểm tra:

k có nằm trong khoảng 1/(n+1) đến n/(n+1) không
Mảng có đủ phần tử cho phân vị yêu cầu không
Có ô trống hoặc text trong mảng không

Chuyển sang PERCENTILE.EXC giúp tôi tiết kiệm 30 phút mỗi tuần vì không phải giải thích tại sao kết quả khác với phần mềm thống kê khác. Báo cáo của tôi giờ tham chiếu trực tiếp các nghiên cứu khoa học mà không cần điều chỉnh số liệu.