Công thức chung
=PERCENTILE(IF(criteria,values),k)
Tóm lược
Để tính toán phần trăm có điều kiện, bạn có thể sử dụng công thức mảng bằng cách sử dụng hàm IF bên trong hàm hàm PERCENTILE. Trong ví dụ được hiển thị, công thức trong G5 là:
=PERCENTILE(IF(Table(Gender)=G$4,Table(Score)),$F5)
Trong đó "Bảng" là một Bảng Excel với dữ liệu trong B5: D14.
Lưu ý: Đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Công thức này nằm bên trong một bảng tóm tắt nhỏ với các giá trị phần trăm trong cột F và giá trị giới tính trong G4 và H4.
Làm việc từ trong ra ngoài, hàm IF được thiết lập như sau:
IF(Table(Gender)=G$4,Table(Score))
Ở đây, mỗi giá trị trong cột giới tính được kiểm tra với giá trị trong G4, "Nam".
Kết quả là một mảng các giá trị boolean như sau:
(88;85;77;FALSE;FALSE;FALSE;83;FALSE;FALSE;79)
Chỉ điểm số liên quan đến nam mới được đưa vào mảng, điểm của nữ được chuyển thành FALSE. Mảng này đi vào hàm PERCENTILE với giá trị k từ F5, 90%.
PERCENTILE tự động bỏ qua các giá trị FALSE và trả về kết quả là 86,8.
Tham chiếu đến Giới tính trong G $ 4 bị khóa để ngăn hàng thay đổi. Tham chiếu đến k giá trị, $ F5 bị khóa để ngăn cột thay đổi. Do đó, công thức có thể được sao chép trên phạm vi G5: H7.
