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 toán "nếu lớn" hoặc "nếu nhỏ", truy xuất giá trị lớn nhất (hoặc nhỏ nhất) thứ n dựa trên các tiêu chí.
Hàm LỚN & NHỎ
Hàm LARGE được sử dụng để tính giá trị lớn thứ n (k) trong một mảng, trong khi Hàm SMALL trả về giá trị thứ n nhỏ nhất.
Để tạo "Nếu lớn", chúng ta sẽ sử dụng Hàm LARGE cùng với Hàm IF trong một công thức mảng.
NẾU LỚN
Bằng cách kết hợp LARGE (hoặc SMALL) 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 “LARGE IF” hoạt động tương tự như cách hoạt động của công thức SUMIF 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 ba điểm cao nhất đạ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 LARGE như vậy:
= LARGE (IF (=,),)
= LARGE (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Khi sử dụng Excel 2022 trở về trước, bạn phải nhập công thức bằng cách nhấn CTRL + SHIFT + ENTER để lấy dấu ngoặc nhọn xung quanh công thức.
Công thức hoạt động như thế nào?
Công thức hoạt động bằng cách đánh giá mỗi ô trong phạm vi tiêu chí của chúng tôi là TRUE hoặc FALSE.
Tìm giá trị cấp cao nhất (k = 1) trong Toán:
= LARGE (IF ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= LARGE (IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Tiếp theo, Hàm IF thay thế mỗi giá trị bằng FALSE nếu điều kiện của nó không được đáp ứng.
= LARGE ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 1)
Bây giờ, Hàm LARGE bỏ qua các giá trị FALSE và tính giá trị lớn nhất (k = 1) trong số các giá trị còn lại (0,81 là giá trị lớn nhất giữa 0,42 và 0,81).
NHỎ NẾU
Kỹ thuật tương tự cũng có thể được áp dụng với Hàm NHỎ.
= NHỎ (NẾU ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
NẾU LỚN với nhiều tiêu chí
Để sử dụng hàm IF LARGE với nhiều tiêu chí (tương tự như cách hoạt động của công thức SUMIFS tích hợp sẵn), chỉ cần lồng thêm các hàm IF vào hàm LARGE như sau:
= LARGE (IF (=, IF (=,)),)
= LARGE (IF ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Một cách khác để bao gồm nhiều tiêu chí là nhân các tiêu chí với nhau như được hiển thị trong bài viết này
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 sinh viên đã đạt được điểm cao nhất, hãy kết hợp với tên này với INDEX MATCH
