Cách tính phân vị trong Excel

Cách tính phân vị trong Excel

Excel cung cấp hai hàm chính để tính phân vị: PERCENTILE.INC() và PERCENTILE.EXC().

Chính xác mà nói, còn có hàm thứ ba—PERCENTILE()—được dùng trong các phiên bản cũ (Excel 2007 trở về trước) và nay được giữ lại vì mục đích tương thích. Hàm cũ này hoạt động giống với PERCENTILE.INC().

Trong hướng dẫn này, chúng ta sẽ tập trung vào các hàm mới hơn—PERCENTILE.INC() và PERCENTILE.EXC().

Hàm Excel PERCENTILE.INC()

Cú pháp

PERCENTILE.INC(array, k)

trong đó:

• array—mảng hoặc phạm vi dữ liệu cần tính phân vị,

• k—giá trị phân vị trong khoảng từ 0 đến 1, bao gồm cả hai đầu mút.

Cả hai đối số đều bắt buộc.

Hàm PERCENTILE.INC() trả về phân vị thứ k của các giá trị trong một phạm vi, trong đó k nằm trong khoảng từ 0 đến 1, bao gồm các giá trị 0 và 1. Nói cách khác, hàm này tính cả giá trị đầu tiên và cuối cùng của mảng.

Thuật toán

Bên dưới, hàm PERCENTILE.INC() trong Excel thực hiện các bước sau:

1. Sắp xếp các giá trị của mảng theo thứ tự tăng dần.
2. Xác định vị trí phân vị dựa trên đối số k và số lượng điểm dữ liệu N trong mảng theo công thức: k(N-1) + 1.
3. Nếu kết quả bước trước không phải số nguyên, thuật toán nội suy giữa các giá trị gần nhất của mảng.

Ví dụ

Giả sử chúng ta muốn tính phân vị thứ 30 cho một mảng số nguyên từ 1 đến 10 (bao gồm), sử dụng hàm PERCENTILE.INC() trong Excel. Công thức của chúng ta sẽ như sau:

Lưu ý: Mặc dù các giá trị của mảng trên được sắp xếp để minh họa, chúng ta không cần sắp xếp trước vì thuật toán sẽ thực hiện việc đó phía sau.

Dưới đây là kết quả phép tính:

Các lỗi có thể gặp

• #NUM!— khi mảng đầu vào rỗng, hoặc k < 0, hoặc k > 1.

• #VALUE!—khi k không phải số.

Hàm Excel PERCENTILE.EXC()

Cú pháp

PERCENTILE.EXC(array, k)

trong đó:

• array—mảng hoặc phạm vi dữ liệu cần tính phân vị,

• k—giá trị phân vị trong khoảng từ 0 đến 1, loại trừ hai đầu mút.

Cả hai đối số đều bắt buộc.

Hàm PERCENTILE.EXC() trả về phân vị thứ k của các giá trị trong một phạm vi, trong đó k nằm trong khoảng từ 0 đến 1, loại trừ các giá trị 0 và 1. Cụ thể hơn, với một mảng có N điểm dữ liệu, giá trị k phải thỏa mãn đồng thời hai điều kiện sau:

1. K > 1/(N+1)
2. K < N/(N+1)

Nói đơn giản, hàm PERCENTILE.EXC() chỉ xác định phân vị cho các xác suất nằm nghiêm ngặt giữa 0 và 1. Điều này có nghĩa hàm không tính giá trị cho mức cực tiểu tuyệt đối (phân vị 0) hoặc cực đại (phân vị 100), dù tất cả điểm dữ liệu vẫn được dùng trong nội suy.

Thuật toán

Bên dưới, hàm PERCENTILE.EXC() trong Excel thực hiện các bước sau:

1. Sắp xếp các giá trị của mảng theo thứ tự tăng dần.
2. Xác định vị trí phân vị dựa trên đối số k và số lượng điểm dữ liệu N trong mảng theo công thức: k(N+1).
3. Nếu kết quả bước trước không phải số nguyên, thuật toán nội suy giữa các giá trị gần nhất của mảng.

Ví dụ

Giả sử chúng ta muốn tính phân vị thứ 30 cho một mảng số nguyên từ 1 đến 10 (bao gồm), sử dụng hàm PERCENTILE.EXC() trong Excel. Công thức của chúng ta sẽ như sau:

Lưu ý: Mặc dù các giá trị của mảng trên được sắp xếp để minh họa, chúng ta không cần sắp xếp trước vì thuật toán sẽ thực hiện việc đó phía sau.

Dưới đây là kết quả phép tính:

Các lỗi có thể gặp

• #NUM!— khi mảng đầu vào rỗng, hoặc k <= 1/(N+1), hoặc k >= N/(N+1).

• #VALUE!—khi k không phải số.

Để học hoặc ôn các hàm Excel phổ biến khác, hãy xem hướng dẫn này: The 15 Basic Excel Formulas Everyone Needs to Know.

PERCENTILE.INC so với PERCENTILE.EXC trong Excel

Cho đến giờ, chúng ta có thể thấy nhiều điểm tương đồng giữa hai hàm PERCENTILE.INC() và PERCENTILE.EXC() trong Excel. Tuy nhiên, cũng có những khác biệt rõ ràng khiến chúng là hai hàm riêng biệt và cho kết quả khác nhau. Hãy tổng kết những điểm giống và khác nhau giữa PERCENTILE.INC() và PERCENTILE.EXC() trong Excel.

Giống nhau:

1. Cú pháp hàm.

2. Logic thuật toán tổng quát (trừ công thức xác định vị trí phân vị).

3. Các lỗi có thể gặp (#NUM! khi mảng rỗng hoặc k nằm ngoài phạm vi hợp lệ và #VALUE! khi k không phải số).

Khác nhau:

1. Phạm vi hợp lệ cho đối số k:

• PERCENTILE.INC()—từ 0 đến 1, bao gồm.

• PERCENTILE.EXC()—từ 1/(N+1) đến N/(N+1), loại trừ.

2. Cách xử lý các giá trị trong mảng:

• PERCENTILE.INC()—tính cả giá trị đầu và cuối.

• PERCENTILE.EXC()—không xác định phân vị tại các giá trị đầu và cuối (0% và 100%), nhưng vẫn dùng toàn bộ dữ liệu khi nội suy.

3. Công thức xác định vị trí phân vị:

• PERCENTILE.INC()—k(N-1) + 1.

• PERCENTILE.EXC()—k(N+1).

Hãy xem hai hàm PERCENTILE.INC() và PERCENTILE.EXC() trong Excel hoạt động ra sao bằng cách áp dụng cả hai lên cùng một mảng số nguyên từ 1 đến 10 (bao gồm) và thử với các giá trị k khác nhau:

Bảng trên cho thấy hàm PERCENTILE.EXC() không xác định khi k nằm tại hoặc vượt ngoài phạm vi hợp lệ (k ≤ 1/(N+1) hoặc k ≥ N/(N+1)). Đây không phải nhược điểm, mà phản ánh định nghĩa toán học của phân vị loại trừ. Những khác biệt nhỏ giữa kết quả .INC() và .EXC() gần các cực trị xuất phát từ cách mỗi hàm nội suy vị trí phân vị.

Mặt khác, chúng ta cần nhớ rằng trong đa số trường hợp, các giá trị k quá thấp hoặc quá cao không mang nhiều ý nghĩa về mặt thống kê. Những hạn chế này hiếm khi gây vấn đề trong thực tế. Hơn nữa, trong đời thực, chúng ta thường tính phân vị cho các mảng lớn hơn nhiều so với ví dụ trên, giúp giảm thiểu khác biệt kết quả.

Trong hầu hết các trường hợp, lựa chọn hợp lý hơn là dùng hàm PERCENTILE.INC(), vốn cũng phù hợp với các công thức mặc định để tính phân vị trong nhiều ngôn ngữ lập trình, bao gồm Python và R.

Tuy nhiên, nếu muốn tránh xác định phân vị ở rìa tập dữ liệu, PERCENTILE.EXC() là lựa chọn phù hợp. Hàm sẽ không tính kết quả cho phân vị 0 hoặc 100, nhưng vẫn dùng mọi điểm dữ liệu khi nội suy giữa các hạng.

Dữ liệu sạch và đại diện là rất quan trọng để rút ra các insight kinh doanh có giá trị. Khóa học Data Preparation in Excel và Data Manipulation in Excel Cheat Sheet là những nguồn hữu ích giúp bạn học cách chuẩn bị dữ liệu hiệu quả cho các bước phân tích tiếp theo.

Kết luận

Chúng ta đã thảo luận lý thuyết thống kê cốt lõi đằng sau phân vị, định nghĩa của chúng, các lĩnh vực ứng dụng chính, và hai hàm chính dùng để tính phân vị trong Excel, bao gồm cú pháp, thuật toán, ví dụ tính toán, và các lỗi tiềm ẩn.

Chúng ta cũng đã so sánh hai hàm trong Excel, nêu bật những điểm giống và khác quan trọng, áp dụng cả hai trên cùng một mảng dữ liệu để đối chiếu kết quả, và xác định bối cảnh phù hợp nhất cho từng hàm.

Để khai thác tối đa sức mạnh của Excel, hãy cân nhắc tham gia lộ trình kỹ năng dành cho người mới bắt đầu Excel Fundamentals, giúp bạn học thông qua các bài tập thực hành cả những tính năng và công thức cơ bản lẫn nâng cao cho chuẩn bị dữ liệu, trực quan hóa và phân tích trong Excel.