Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tính “phân vị nếu”, lấy kNS phân vị trong cơn thịnh nộ của các giá trị với tiêu chí.
Hàm PERCENTILE
Hàm PERCENTILE được sử dụng để tính kNS phần trăm giá trị trong một phạm vi nơi k là giá trị phân vị từ 0 đến 1.
= PERCENTILE ($ D $ 2: $ D $ 10.0,75)
Tuy nhiên, điều này lấy phần trăm của toàn bộ phạm vi giá trị. Thay vào đó, để tạo “Phân vị nếu”, chúng ta sẽ sử dụng Hàm PERCENTILE cùng với Hàm IF trong một công thức mảng.
PERCENTILE IF
Bằng cách kết hợp PERCENTILE và IF trong một công thức mảng, về cơ bản chúng ta có thể tạo một hàm “PERCENTLE IF” hoạt động tương tự như cách hoạt động của hàm AVERAGEIF tích hợp sẵn. Hãy xem qua một ví dụ.
Chúng tôi có một danh sách các học sinh đạt được điểm trong hai môn học khác nhau:
Giả sử chúng tôi được yêu cầu tìm 75NS tỷ lệ phần trăm điểm số đạt được cho mỗi môn học như vậy:
Để thực hiện điều này, chúng ta có thể lồng một hàm IF với chủ thể như tiêu chí của chúng tôi bên trong hàm PERCENTILE như sau:
= PERCENTILE (IF (=,),)
= PERCENTILE (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Khi sử dụng Excel 2022 trở về trước, bạn phải nhập công thức mảng bằng cách nhấn CTRL + SHIFT + ENTER (thay vì ĐI VÀO), cho Excel biết rằng công thức trong một công thức mảng. Bạn sẽ biết đó là một công thức mảng bởi dấu ngoặc nhọn xuất hiện xung quanh công thức (xem hình ảnh trên cùng).
Công thức hoạt động như thế nào?
Hàm If đánh giá mỗi ô trong phạm vi tiêu chí của chúng tôi là TRUE hoặc FALSE, tạo ra hai mảng:
= PERCENTILE (IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Tiếp theo, Hàm IF tạo một mảng duy nhất, thay thế mỗi giá trị bằng FALSE nếu điều kiện của nó không được đáp ứng.
= PERCENTILE ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 0,75)
Bây giờ, Hàm PERCENTILE bỏ qua các giá trị FALSE và tính toán 75NS phần trăm của các giá trị còn lại (0,72 là 75NS phân vị).
PERCENTILE IF với nhiều tiêu chí
Để tính PERCENTILE IF với nhiều tiêu chí (tương tự như cách hoạt động của hàm AVERAGEIFS tích hợp sẵn), bạn có thể chỉ cần nhân các tiêu chí với nhau:
= PERCENTILE (IF ((=) * (=),),)
= PERCENTILE (IF (($ D $ 2: $ D $ 10 = $ H2) * ($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Một cách khác để bao gồm nhiều tiêu chí là lồng nhiều câu lệnh IF hơn trong công thức.
Các mẹo và thủ thuật:
- Nếu có thể, hãy luôn tham chiếu vị trí (k) từ ô trợ giúp và tham chiếu khóa (F4) vì điều này sẽ giúp tự động điền công thức dễ dàng hơn.
- Nếu bạn đang sử dụng Excel 2022 hoặc mới hơn, bạn có thể nhập công thức mà không cần CTRL + SHIFT + ENTER.
- Để truy xuất tên của những học sinh đạt điểm cao nhất, hãy kết hợp với tên này với INDEX MATCH.
PERCENTILE IF trong Google Trang tính
Hàm PERCENTILE IF hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
