Công thức Excel: giá trị lớn thứ n với các bản sao
Công thức Excel: giá trị lớn thứ n với các bản sao

Công thức chung
=MAX(IF(rng<A2,rng))
Tóm lược

Để nhận giá trị lớn thứ n trong tập dữ liệu có các bản sao, bạn có thể sử dụng công thức mảng dựa trên các hàm MAX và IF.

Lưu ý: hàm LARGE sẽ dễ dàng trả về các giá trị thứ n, nhưng LARGE sẽ trả về các bản sao khi chúng tồn tại trong dữ liệu nguồn.

Trong ví dụ được hiển thị, công thức trong E6 là:

=MAX(IF(rng<E5,rng))

Trong đó “rng” là phạm vi được đặt tên B5: B11

Giải trình

Đầu tiên, chúng tôi nhận được giá trị lớn nhất bằng cách sử dụng hàm MAX trong E5:

=MAX(rng)

Khi chúng tôi đã thiết lập giá trị lớn nhất, chúng tôi tạo một công thức khác chỉ đơn giản là kiểm tra tất cả các giá trị trong phạm vi được đặt tên “rng” so với “giá trị lớn nhất cuối cùng”:

=MAX(IF(rng<E5,rng))

Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.

Bất kỳ giá trị nào thấp hơn giá trị “lớn nhất cuối cùng” sẽ tồn tại trong thử nghiệm và bất kỳ giá trị nào không kết thúc bằng FALSE. Mảng kết quả trông như thế này:

{12;FALSE;FALSE;11;12;12;10}

Khi công thức được sao chép xuống cột, E5 tăng lên ở mỗi hàng, do đó MAX và IF trả về một mảng mới. Hàm MAX tiếp tục trả về giá trị lớn nhất trong mỗi mảng – giá trị thứ n trong chuỗi.

Sẽ thật tuyệt nếu có một công thức tất cả trong một giải quyết trực tiếp với n. Nếu bạn có, hãy gọi bên dưới.

https://exceljet.net/formula/nth-largest-value-with-duplicates