5 Điều Về Hàm PERCENTILE.INC Tôi Ước Biết Sớm Hơn Khi Phân Tích Dữ Liệu

Hai năm trước, tôi xử lý bảng điểm của 8000 học viên bằng hàm PERCENTILE trong Excel. Mọi thứ hoạt động tốt cho đến khi báo cáo bắt đầu trả về kết quả sai với dữ liệu mới. Hóa ra tôi đang dùng hàm đã lỗi thời từ năm 2007. Sau khi chuyển sang PERCENTILE.INC, tôi phát hiện thêm 4 điều quan trọng mà không một bài hướng dẫn nào đề cập đầy đủ.

Mục Lục

PERCENTILE cũ không còn được hỗ trợ từ Excel 2016

Hàm PERCENTILE là công cụ tính phân vị từ Excel 2003. Microsoft đã thay thế nó bằng hai hàm mới từ phiên bản 2010: PERCENTILE.INC và PERCENTILE.EXC. Tên gọi mới phản ánh rõ cách tính toán và tránh nhầm lẫn.

Sự khác biệt chính nằm ở công thức xác định vị trí:

PERCENTILE và PERCENTILE.INC: vị trí = (N-1) × k + 1
PERCENTILE.EXC: vị trí = k × (N+1)

Trong đó N là số lượng giá trị, k là phân vị cần tìm.

Cách kiểm tra hàm đang dùng:

Mở tệp Excel có công thức phân vị
Tìm các ô chứa =PERCENTILE(
Thay thế bằng =PERCENTILE.INC( giữ nguyên tham số
So sánh kết quả (thường giống nhau 95% trường hợp)

Tôi phát hiện vấn đề khi chuyển tệp từ Excel 2013 sang Excel 2019. Hàm PERCENTILE vẫn chạy nhưng hiển thị cảnh báo tương thích. Các tệp mới trên Excel 365 không nhận diện được hàm cũ nếu mở trên trình duyệt.

Điều quan trọng: PERCENTILE.INC tương thích hoàn toàn với PERCENTILE cũ. Công thức và kết quả giống hệt nhau. Chuyển đổi mất 5 phút cho 200 công thức bằng tính năng tìm và thay thế.

Hiểu được sự khác biệt giữa INC và EXC giúp chọn đúng hàm

PERCENTILE.INC và PERCENTILE.EXC trả về kết quả khác nhau với cùng dữ liệu. Sự khác biệt không nằm ở việc bao gồm hay loại trừ số 0 và 1 như nhiều người nghĩ.

XEM THÊM: Cách Dùng Hàm COUNTIFS Để Thống Kê Nhân Viên Nam Trên 25 Ngày Công Trong 1 Nốt Nhạc

Thực tế, INC cho phép giá trị k từ 0 đến 1 (bao gồm cả hai đầu). EXC chỉ chấp nhận k trong khoảng 1/(n+1) đến n/(n+1), trong đó n là số phần tử trong mảng.

Với dữ liệu 10 số, EXC không thể tính phân vị thứ 5 (k=0.05) vì 0.05 nhỏ hơn 1/11 = 0.091. Hàm sẽ trả về lỗi #NUM!. Trong khi đó, INC tính được mọi giá trị k từ 0 đến 1.

Khi nào dùng từng hàm:

PERCENTILE.INC: Phân tích điểm thi, xếp hạng chung, so sánh với chuẩn quốc tế
PERCENTILE.EXC: Phát hiện ngoại lệ trong kiểm soát chất lượng, phân tích thống kê chuyên sâu

Ngôn ngữ lập trình R và Python đều sử dụng thuật toán giống PERCENTILE.INC làm mặc định. Nếu cần so sánh kết quả giữa Excel và các công cụ phân tích khác, INC cho kết quả nhất quán.

Tôi chuyển hoàn toàn sang INC sau khi thử tính phân vị thứ 10 cho 50 giá trị bằng EXC. Hàm báo lỗi vì 0.1 nhỏ hơn ngưỡng tối thiểu 1/51 = 0.0196. Với INC, mọi phân vị từ 0 đến 100 đều tính được không lỗi.

Nắm vững cơ chế nội suy tuyến tính tránh kết quả bất ngờ

Phân vị thường rơi vào giữa hai giá trị trong dữ liệu. Excel sử dụng nội suy tuyến tính để tính giá trị chính xác thay vì làm tròn lên hoặc xuống.

Công thức nội suy hoạt động như sau:

Tính vị trí = (N-1) × k + 1
Nếu vị trí là số nguyên, lấy giá trị tại vị trí đó
Nếu vị trí có phần thập phân, lấy hai giá trị liền kề
Kết quả = giá trị dưới + phần thập phân × (giá trị trên – giá trị dưới)

Ví dụ với dãy số [10, 20, 30, 40, 50]:

Phân vị thứ 40: vị trí = (5-1) × 0.4 + 1 = 2.6
Giá trị tại vị trí 2 là 20, vị trí 3 là 30
Kết quả = 20 + 0.6 × (30-20) = 20 + 6 = 26

Cách kiểm tra tính toán:

Sắp xếp dữ liệu tăng dần trong cột A
Tạo công thức =PERCENTILE.INC(A:A, 0.25)
Xác định vị trí: =(COUNT(A:A)-1) × 0.25 + 1
Lấy giá trị: =INDEX(A:A, INT(vị trí)) và =INDEX(A:A, INT(vị trí)+1)
Tính thủ công: =giá trị dưới + MOD(vị trí,1) × (giá trị trên – giá trị dưới)

XEM THÊM: Hàm TREND Giúp Tôi Dự Báo Doanh Số 3 Tháng Tiếp Theo Chỉ Trong 30 Giây

Hiểu cơ chế này giúp tôi giải thích tại sao phân vị thứ 90 của 100 điểm thi là 87.5 chứ không phải 88 hoặc 87. Vị trí chính xác là 90.1 nên Excel nội suy giữa điểm 87 và 88.

Một lưu ý quan trọng: dữ liệu không cần sắp xếp trước khi dùng hàm. Excel tự động sắp xếp nội bộ. Tuy nhiên với dữ liệu trên 10000 hàng, sắp xếp trước giúp hàm chạy nhanh hơn 20-30%.

Xử lý lỗi đúng cách tiết kiệm hàng giờ debug

PERCENTILE.INC trả về hai loại lỗi chính: #NUM! và #VALUE!. Mỗi lỗi có nguyên nhân và cách khắc phục riêng.

Lỗi #NUM! xuất hiện khi:

Giá trị k nhỏ hơn 0 hoặc lớn hơn 1
Mảng dữ liệu rỗng (không có giá trị số)
Mảng chứa toàn văn bản hoặc ô trống

Lỗi #VALUE! xuất hiện khi:

Tham số k không phải là số (nhập chữ hoặc tham chiếu ô văn bản)
Sử dụng phạm vi không hợp lệ

Cách khắc phục hệ thống:

Kiểm tra giá trị k:

=IF(AND(k>=0, k<=1), PERCENTILE.INC(dữ liệu, k), "Giá trị k không hợp lệ")

Kiểm tra dữ liệu trước khi tính:

=IF(COUNT(A2:A100)>0, PERCENTILE.INC(A2:A100, 0.9), "Không có dữ liệu")

Lọc ô trống và văn bản:

=PERCENTILE.INC(IF(ISNUMBER(A2:A100), A2:A100), 0.75)

Nhập công thức này bằng Ctrl + Shift + Enter để Excel nhận dạng là mảng công thức.

Tôi gặp lỗi #NUM! liên tục với tệp nhập từ hệ thống khác. Nguyên nhân là các ô trống chen giữa dữ liệu làm Excel không đếm được số phần tử chính xác. Thêm hàm IF với ISNUMBER giải quyết hoàn toàn vấn đề.

Một mẹo nhỏ: dùng Data Validation để giới hạn k từ 0 đến 1 ngay từ đầu. Điều này ngăn người dùng nhập sai và tạo lỗi hàng loạt trong báo cáo tự động.

Tối ưu với dữ liệu lớn cải thiện tốc độ tính toán 60%

PERCENTILE.INC tính toán chậm với bảng dữ liệu trên 50000 hàng. Tôi thử nghiệm với tệp 100000 điểm thi và thấy mỗi lần tính toán mất 3-4 giây. Khi có 50 công thức phân vị khác nhau, tổng thời gian chờ lên đến 3 phút.

XEM THÊM: Hàm COUNTIF: 7 Tình Huống Thực Tế Dân Văn Phòng Cần Biết

Ba cách tối ưu giúp giảm thời gian xuống còn 1 phút:

Phương pháp 1: Chuyển sang bảng tính Chọn phạm vi dữ liệu, nhấn Ctrl + T để tạo bảng. Đổi tên bảng thành DuLieuDiem. Công thức mới:

=PERCENTILE.INC(DuLieuDiem[Diem], 0.9)

Bảng tính giúp Excel lập chỉ mục dữ liệu một lần. Các lần tính sau truy xuất trực tiếp từ chỉ mục thay vì quét lại toàn bộ phạm vi.

Phương pháp 2: Dùng bảng tổng hợp trung gian Thay vì tính phân vị từ dữ liệu gốc 100000 hàng, tạo bảng tổng hợp phân nhóm theo khoảng điểm. Tính phân vị từ bảng tổng hợp cho kết quả gần đúng 98% nhưng nhanh hơn 10 lần.

Phương pháp 3: Lưu kết quả vào mảng Nếu cần nhiều phân vị cùng lúc (10, 25, 50, 75, 90), tính một lần rồi lưu vào ô cố định:

Tính tất cả phân vị cần thiết
Copy kết quả
Paste Special > Values để chuyển thành giá trị tĩnh
Tham chiếu đến các ô này thay vì tính lại

Tôi áp dụng cả ba phương pháp cho báo cáo phân tích hàng tháng. Thời gian tạo báo cáo giảm từ 8 phút xuống 3 phút. Quan trọng hơn, tệp Excel không còn bị treo khi có nhiều người mở cùng lúc.

Lưu ý cuối: tắt tính toán tự động khi làm việc với nhiều công thức phân vị. Chuyển sang Calculation Options > Manual trong ribbon Formulas. Nhấn F9 để tính toán khi cần. Điều này tránh Excel tính lại mỗi khi thay đổi bất kỳ ô nào.

Áp dụng ngay hôm nay

PERCENTILE.INC hoạt động từ Excel 2010 trở lên, bao gồm cả Excel 365 và Excel Online. Hàm tương thích với mọi hệ điều hành Windows, Mac, và iOS.

Ba ưu tiên khi bắt đầu:

Thay thế tất cả hàm PERCENTILE cũ bằng PERCENTILE.INC để tránh cảnh báo tương thích
Dùng INC thay vì EXC trừ khi có yêu cầu thống kê đặc biệt
Thêm kiểm tra lỗi vào công thức quan trọng ngay từ đầu

Với dữ liệu trên 10000 hàng, cân nhắc tạo bảng tính hoặc bảng tổng hợp trước khi áp dụng hàm phân vị. Khoảng thời gian tiết kiệm xứng đáng với 5 phút thiết lập ban đầu.