Hướng Dẫn Tính Hàm PERCENTILE Trong Excel: Cách Tìm Phân Vị Thứ 90 và 50 Chi Tiết

Table of Contents

Thời gian đọc ước tính: 15 phút

Những điểm chính

Định nghĩa: Percentile (Bách phân vị) xác định vị trí tương đối của một giá trị trong tập dữ liệu, giúp so sánh hiệu quả hơn so với điểm số thô.
Các hàm chính: Excel cung cấp ba hàm: PERCENTILE (cũ), PERCENTILE.INC (bao gồm biên), và PERCENTILE.EXC (loại trừ biên).
Ứng dụng: Hàm này dùng để phân tích lương thưởng, điểm thi, thời gian tải trang web và phát hiện các giá trị ngoại lai (outliers).
Cú pháp: =PERCENTILE.INC(array, k) với k là giá trị phần trăm từ 0 đến 1 (ví dụ: 0.9 cho 90%).
Khác biệt: PERCENTILE.INC luôn trả về kết quả hợp lệ cho k=0 và k=1, trong khi PERCENTILE.EXC sẽ báo lỗi #NUM! nếu tập dữ liệu quá nhỏ hoặc k nằm ngoài phạm vi tính toán thống kê.

Percentile là một thước đo thống kê quan trọng thường được sử dụng khi làm việc với các tập dữ liệu lớn.

Chỉ số này cung cấp cho bạn cái nhìn rõ ràng về vị trí của một giá trị cụ thể nằm ở đâu trong toàn bộ tập dữ liệu (tức là xếp hạng/vị trí của nó).

Trong thực tế, giá trị bách phân vị thường xuất hiện trong các kỳ thi cạnh tranh hoặc phân tích tài chính. Thay vì chỉ nhìn vào điểm số thô, bách phân vị cho biết bạn đứng ở đâu so với tất cả những người khác cùng tham gia kỳ thi đó.

Bài viết này sẽ giải thích chi tiết mọi khía cạnh kỹ thuật về hàm bách phân vị trong Excel, đồng thời cung cấp các ví dụ thực tế về cách tính bách phân vị thứ 90 hoặc bách phân vị thứ 50 trong Excel.

Percentile (Bách Phân Vị) Là Gì? Giải Thích Đơn Giản

Giá trị Percentile cho biết vị trí tương đối của một điểm dữ liệu trong toàn bộ tập dữ liệu.

Ví dụ: Nếu tôi có bảng điểm của 100 học sinh và thông báo rằng điểm số ở bách phân vị thứ 90 là 84, điều này có nghĩa là bất kỳ ai đạt 84 điểm đều có điểm số cao hơn 90% tổng số học sinh.

Tương tự, nếu giá trị bách phân vị thứ 50 cho một tập dữ liệu là 60, điều này khẳng định người đạt 60 điểm có khoảng 50% số người đạt điểm cao hơn và 50% số người đạt điểm thấp hơn họ.

Đây là phương pháp ưu việt hơn so với việc chỉ cung cấp điểm số thô vì nó mang lại ngữ cảnh so sánh rõ ràng.

Nếu tôi chỉ nói điểm của bạn là 90, con số đó không cho biết bạn đứng ở đâu so với những người khác (có thể đề thi rất dễ và ai cũng được 100). Nhưng nếu tôi nói điểm của bạn thuộc bách phân vị thứ 99, bạn biết ngay lập tức rằng mình đã làm tốt hơn 99% những người tham gia kỳ thi.

Việc tính toán giá trị này trong Excel cực kỳ đơn giản nhờ các hàm có sẵn được tối ưu hóa cho xử lý dữ liệu số lượng lớn.

Tại sao nên dùng Percentile thay vì Trung Bình (Average)?

Trong phân tích dữ liệu kinh doanh, việc dựa hoàn toàn vào giá trị trung bình đôi khi gây ra sai lệch lớn, đặc biệt khi dữ liệu có phân phối không đều.

Loại bỏ tác động của giá trị ngoại lai: Nếu một nhân viên có mức lương đột biến cao gấp 10 lần người khác, giá trị trung bình sẽ bị kéo lên cao, không phản ánh đúng thực tế. Percentile (đặc biệt là mức 50 – Trung vị) không bị ảnh hưởng bởi các giá trị cực đoan này.
Hiểu rõ phân phối dữ liệu: Percentile giúp bạn chia nhỏ dữ liệu thành các phần (Top 10%, Top 25%, Bottom 25%) để có chiến lược cụ thể cho từng nhóm.
Thiết lập tiêu chuẩn (Benchmarking): Các công ty thường dùng bách phân vị thứ 75 hoặc 90 để thiết lập mức lương cạnh tranh trên thị trường.

Các Hàm PERCENTILE Trong Excel

Excel cung cấp ba biến thể của hàm percentile. Nếu bạn đang sử dụng Excel 2010 hoặc các phiên bản mới hơn, bạn sẽ có quyền truy cập vào cả ba hàm này.

PERCENTILE: Đây là hàm cũ được giữ lại vì mục đích tương thích ngược với các file Excel phiên bản 2007 trở về trước. Bạn có thể sử dụng hàm này, nhưng Microsoft khuyến nghị nên dùng các hàm mới hơn để đảm bảo độ chính xác cao nhất trong tương lai. Kết quả của hàm này là một giá trị nằm giữa 0 và 1.
PERCENTILE.INC: Đây là công thức mới (hoạt động chính xác như hàm PERCENTILE cũ). Trong hầu hết các trường hợp phân tích dữ liệu thông thường, đây là hàm bạn cần sử dụng. Hậu tố “INC” viết tắt của “Inclusive” (Bao gồm), nghĩa là nó tính cả giá trị 0% và 100%. Kết quả trả về nằm trong khoảng từ 0 đến 1.
PERCENTILE.EXC: Hàm này hoạt động tương tự PERCENTILE.INC nhưng có sự khác biệt về mặt thống kê. Hậu tố “EXC” viết tắt của “Exclusive” (Loại trừ). Nó loại trừ các giá trị k nằm trong khoảng từ 0 đến 1/(N+1) cũng như từ N/(N+1) đến 1 (với N là kích thước mẫu). Điều này phù hợp cho các phân tích thống kê suy diễn khi bạn không thể xác định chắc chắn giá trị cực đại hoặc cực tiểu thực tế của quần thể.

Tóm lại: Hãy sử dụng PERCENTILE.INC cho hầu hết các báo cáo kinh doanh và phân tích thông thường. Chỉ sử dụng PERCENTILE.EXC khi có yêu cầu khắt khe về thống kê học thuật.

Cấu trúc hàm (Syntax)

Dưới đây là cú pháp chuẩn của hàm PERCENTILE.INC trong Excel:

=PERCENTILE.INC(array, k)

Trong đó:

array (mảng): Phạm vi ô chứa dữ liệu số mà bạn muốn tìm giá trị bách phân vị thứ K.
k: Giá trị nằm trong khoảng từ 0 đến 1, đại diện cho bách phân vị thứ k.
- Để tính bách phân vị thứ 90: Nhập 0.9 hoặc 90%.
- Để tính bách phân vị thứ 50: Nhập 0.5 hoặc 50%.

Cú pháp này áp dụng tương tự cho cả hàm PERCENTILE và PERCENTILE.EXC.

Lưu ý: Bạn có thể tham khảo thêm cách tính phạm vi tứ phân vị (IQR) để hiểu rõ hơn về sự phân tán của dữ liệu.

Hướng Dẫn Tính Bách Phân Vị Thứ 90 (Hoặc 50) Trong Excel

Giả sử bạn có một tập dữ liệu điểm số và bạn muốn xác định ngưỡng điểm mà 90% học sinh đều nằm dưới mức đó (Bách phân vị thứ 90).

Tập dữ liệu mẫu gồm 20 dòng chứa điểm số để tính toán hàm Percentile

Bước 1: Chuẩn bị dữ liệu

Đảm bảo dữ liệu của bạn ở định dạng số. Trong ví dụ này, dữ liệu nằm trong dải ô A2:A21.

Bước 2: Nhập công thức

Tại ô bạn muốn hiển thị kết quả, hãy nhập công thức sau để tính Bách phân vị thứ 90:

=PERCENTILE.INC(A2:A21, 90%)

Minh họa công thức PERCENTILE.INC được nhập trong Excel để tính giá trị phân vị thứ 90

Trong công thức trên:

A2:A21: Là vùng dữ liệu cần tính.
90%: Là giá trị k. Bạn hoàn toàn có thể thay thế bằng 0.9.

Bước 3: Phân tích kết quả

Kết quả trả về là 95.3. Điều này có nghĩa là 90% các giá trị trong tập dữ liệu này nhỏ hơn hoặc bằng 95.3. Chỉ có 10% số liệu là lớn hơn giá trị này.

Lưu ý quan trọng: Bạn không cần phải sắp xếp (sort) dữ liệu từ bé đến lớn trước khi dùng hàm này. Excel sẽ tự động xử lý việc sắp xếp trong thuật toán nền của hàm.

Tính Bách Phân Vị Thứ 50 (Trung Vị)

Nếu bạn muốn tính bách phân vị thứ 50, hãy sử dụng công thức:

=PERCENTILE.INC(A2:A21, 50%)

Kết quả của công thức này sẽ tương đương với việc sử dụng hàm =MEDIAN(A2:A21). Đây là giá trị nằm chính giữa tập dữ liệu, chia dữ liệu thành hai nửa bằng nhau.

So Sánh Chi Tiết: PERCENTILE.INC vs PERCENTILE.EXC

Nhiều người dùng Excel thường bối rối về lý do tồn tại hai hàm riêng biệt này. Sự khác biệt nằm ở cách xử lý toán học đối với các giá trị biên.

Cơ chế hoạt động

PERCENTILE.INC (Inclusive): Tính toán kết quả bao gồm cả giá trị đầu tiên và cuối cùng trong tập dữ liệu (từ 0% đến 100%). Nó chia khoảng cách giữa các điểm dữ liệu thành N-1 khoảng.
PERCENTILE.EXC (Exclusive): Tính toán kết quả bằng cách loại trừ biên, coi như tập dữ liệu là một mẫu từ một quần thể lớn hơn vô hạn. Nó chia khoảng cách thành N+1 khoảng.

Ví dụ minh họa sự khác biệt

Giả sử bạn có tập dữ liệu như hình dưới đây, và tôi đã tính toán các giá trị bách phân vị khác nhau (cột C) bằng cả hai hàm.

Bảng so sánh kết quả khác nhau giữa hàm PERCENTILE.INC và PERCENTILE.EXC trên cùng tập dữ liệu

Quan sát từ ví dụ:

Kết quả khác nhau: Với cùng một mức k (ví dụ 90%), PERCENTILE.INC trả về 95.3, trong khi PERCENTILE.EXC trả về 96.9. Hàm EXC thường trả về giá trị “gắt” hơn (xa trung tâm hơn) ở các mức phân vị cao.
Lỗi #NUM!: Hàm PERCENTILE.EXC trả về lỗi #NUM! khi cố gắng tính giá trị cho k = 0 (0%) hoặc k = 1 (100%).
Phạm vi lỗi của EXC: Hàm này sẽ báo lỗi nếu giá trị k nằm ngoài phạm vi hợp lệ được tính theo công thức:
- Nhỏ hơn 1/(N+1)
- Lớn hơn N/(N+1)

Trong ví dụ trên, N (số lượng phần tử) = 10.

Giới hạn dưới: 1/(10+1) = 1/11 ≈ 0.09 (9%)
Giới hạn trên: 10/11 ≈ 0.909 (90.9%)

Do đó, nếu bạn dùng PERCENTILE.EXC để tính bách phân vị thứ 5% (nhỏ hơn 9%) hoặc 95% (lớn hơn 90.9%) cho tập dữ liệu chỉ có 10 dòng này, Excel sẽ trả về lỗi #NUM!. Hàm PERCENTILE.INC vẫn tính toán bình thường trong các trường hợp này.

Lời khuyên: Hãy ưu tiên sử dụng PERCENTILE.INC hoặc hàm PERCENTILE cũ để đảm bảo tính toán được trên mọi tập dữ liệu, trừ khi bạn có kiến thức sâu về thống kê và có lý do cụ thể để dùng EXC.

Ứng Dụng Nâng Cao Của Hàm Percentile

Để khai thác tối đa sức mạnh của hàm Percentile, bạn có thể kết hợp nó với các tính năng khác của Excel.

1. Tô màu dữ liệu Top 10% (Conditional Formatting)

Bạn có thể dùng Percentile để tự động tô màu các ô có giá trị cao nhất.

Chọn vùng dữ liệu.
Vào Home > Conditional Formatting > Top/Bottom Rules > Top 10%.
Excel sẽ tự động tính toán bách phân vị thứ 90 và tô màu các ô nằm trên ngưỡng này.

2. Lọc dữ liệu theo nhóm (Hàm IF kết hợp PERCENTILE)

Đôi khi bạn muốn phân loại nhân viên dựa trên hiệu suất:

Nhóm A: Top 20%
Nhóm B: Top 20% – 50%
Nhóm C: Còn lại

Công thức mẫu:

=IF(A2>=PERCENTILE.INC($A$2:$A$21, 0.8), "Nhóm A", IF(A2>=PERCENTILE.INC($A$2:$A$21, 0.5), "Nhóm B", "Nhóm C"))

Lưu ý: Nhớ sử dụng địa chỉ tuyệt đối ($A$2:$A$21) cho vùng dữ liệu để cố định khi sao chép công thức.

3. Sử dụng trong Pivot Table

Mặc dù Pivot Table không có sẵn hàm Percentile trong phần “Summarize Values By”, bạn có thể thêm nó thông qua Calculated Fields hoặc sử dụng Power Pivot (DAX) để tính toán các chỉ số phân vị động cho báo cáo dashboard.

Các Lỗi Thường Gặp Khi Sử Dụng Hàm Percentile

Khi làm việc với hàm này, bạn có thể gặp một số mã lỗi. Dưới đây là nguyên nhân và cách khắc phục:

1. Lỗi #NUM!

Nguyên nhân 1: Giá trị k nhỏ hơn 0 hoặc lớn hơn 1.
- Khắc phục: Kiểm tra lại tham số k, đảm bảo nó nằm trong khoảng 0-1 (hoặc 0% – 100%).
Nguyên nhân 2: Sử dụng PERCENTILE.EXC với tập dữ liệu quá ít phần tử.
- Khắc phục: Chuyển sang dùng PERCENTILE.INC hoặc thêm dữ liệu vào mẫu.
Nguyên nhân 3: Vùng dữ liệu (Array) trống rỗng.

2. Lỗi #VALUE!

Nguyên nhân: Giá trị k không phải là dạng số (ví dụ: nhập chữ “90%” nhưng Excel hiểu là text, hoặc tham chiếu đến ô chứa text).
- Khắc phục: Đảm bảo k là số thực hoặc tham chiếu ô đúng định dạng.

3. Kết quả không chính xác do dữ liệu bẩn

Nếu vùng Array chứa các giá trị text hoặc lỗi, hàm Percentile sẽ bỏ qua các ô text nhưng sẽ trả về lỗi nếu có ô chứa lỗi (như #N/A, #DIV/0!).
Hãy làm sạch dữ liệu (Data Cleaning) trước khi tính toán.

Câu Hỏi Thường Gặp (FAQ)

Hàm Percentile có giống hàm Quartile không?
Về bản chất là giống nhau nhưng Quartile (Tứ phân vị) là trường hợp đặc biệt của Percentile.

Quartile 0 = Min = Percentile 0
Quartile 1 = Percentile 25th (25%)
Quartile 2 = Median = Percentile 50th (50%)
Quartile 3 = Percentile 75th (75%)
Quartile 4 = Max = Percentile 100th (100%)

Tôi nên dùng hàm nào cho báo cáo lương thưởng?
Bạn nên dùng PERCENTILE.INC. Đây là tiêu chuẩn chung trong nhân sự (HR) để tính các dải lương (P25, P50, P75, P90).

Làm sao để tìm thứ hạng ngược lại (từ giá trị ra phần trăm)?
Nếu bạn có giá trị (ví dụ: điểm 85) và muốn biết nó nằm ở bách phân vị thứ bao nhiêu, hãy dùng hàm PERCENTRANK.INC.
Cú pháp: =PERCENTRANK.INC(array, x, [significance]).

Hàm Percentile có hoạt động với dữ liệu ngày tháng không?
Có. Excel lưu trữ ngày tháng dưới dạng số seri liên tiếp, nên bạn hoàn toàn có thể tính bách phân vị cho cột thời gian (ví dụ: tìm mốc thời gian mà 90% đơn hàng đã được giao).

Tổng Kết

Hàm Percentile là một công cụ mạnh mẽ giúp bạn vượt qua các hạn chế của giá trị trung bình (Mean) để hiểu sâu hơn về phân phối dữ liệu. Dù bạn đang phân tích điểm thi, lương thưởng hay hiệu suất bán hàng, việc nắm vững PERCENTILE.INC sẽ giúp báo cáo của bạn chuyên nghiệp và chính xác hơn.

Hãy bắt đầu áp dụng ngay vào bảng tính của bạn để phân loại dữ liệu hiệu quả hơn.

Nguồn tham khảo: