Công thức Excel: Tên của giá trị lớn thứ n với tiêu chí
Công thức Excel: Tên của giá trị lớn thứ n với tiêu chí

Công thức chung
=INDEX(range,MATCH(LARGE(filtered_range,F5),filtered_range,0))
Tóm lược

Để lấy tên của giá trị lớn thứ n với tiêu chí, bạn có thể sử dụng INDEX và MATCH, các Hàm LARGEvà một bộ lọc được tạo bằng Hàm IF. Trong ví dụ được hiển thị, công thức trong ô G5, được sao chép xuống, là:

=INDEX(name,MATCH(LARGE(IF(group="A",score),F5),IF(group="A",score),0))

Ở đâu Tên (B5: B16), nhóm (C5: C16), và ghi bàn (D5: D16) là phạm vi được đặt tên. Công thức trả về tên được liên kết với các giá trị cao nhất thứ 1, thứ 2 và thứ 3 trong Nhóm A.

Lưu ý: Đây là một công thức mảng phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.

Giải trình

Hàm LARGE là một cách dễ dàng để nhận giá trị lớn thứ n trong một phạm vi:

 =LARGE(range,1) // 1st largest
 =LARGE(range,2) // 2nd largest
 =LARGE(range,3) // 3rd largest

Trong ví dụ này, chúng ta có thể sử dụng hàm LARGE để có được điểm cao nhất, sau đó sử dụng điểm giống như “chìa khóa” để truy xuất tên được liên kết với INDEX và MATCH. Lưu ý rằng chúng tôi đang chọn các giá trị cho n từ phạm vi F5: F7, để có được điểm cao nhất thứ 1, thứ 2 và thứ 3.

Tuy nhiên, điều khác biệt trong trường hợp này là chúng ta cần phải phân biệt điểm số trong nhóm A và nhóm B. Nói cách khác, chúng ta cần áp dụng tiêu chí. Chúng tôi làm điều này với hàm IF, được sử dụng để “lọc” các giá trị trước khi chúng được đánh giá bằng LARGE. Như một ví dụ chung, để nhận được giá trị lớn nhất (tức là giá trị thứ nhất) trong phạm vi 2 trong đó phạm vi 1 = “A”, bạn có thể sử dụng công thức như sau:

LARGE(IF(range="A",range2),1)

Lưu ý: sử dụng IF theo cách này làm cho điều này trở thành công thức mảng.

Làm việc từ trong ra ngoài, bước đầu tiên là nhận giá trị lớn nhất “đầu tiên” trong dữ liệu được liên kết với Nhóm A bằng hàm LARGE:

LARGE(IF(group="A",score),F5)

Trong trường hợp này, giá trị trong F5 là 1, vì vậy chúng tôi đang yêu cầu điểm cao nhất trong Nhóm A. Khi hàm IF được đánh giá, nó sẽ kiểm tra từng giá trị trong phạm vi được đặt tên nhóm. Phạm vi được đặt tên ghi bàn được cung cấp cho value_if_true. Điều này tạo ra một mảng, được trả về trực tiếp cho hàm LARGE:

LARGE({79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE},1)

Lưu ý rằng điểm số duy nhất tồn tại qua bộ lọc là từ Nhóm A. LARGE sau đó trả về điểm số cao nhất còn lại, 93, trực tiếp đến hàm MATCH dưới dạng giá trị tra cứu. Bây giờ chúng ta có thể đơn giản hóa công thức thành:

=INDEX(name,MATCH(93,IF(group="A",score),0))

Bây giờ chúng ta có thể thấy rằng Hàm MATCH được cấu hình bằng cách sử dụng cùng một mảng đã lọc mà chúng ta đã thấy ở trên. Hàm IF lại lọc ra các giá trị không mong muốn và phần MATCH của công thức giải quyết thành: 

MATCH(93,{79;FALSE;93;FALSE;83;FALSE;67;FALSE;85;FALSE;69;FALSE},0)

Vì 93 xuất hiện ở vị trí thứ 3, MATCH trả về 3 trực tiếp cho Hàm INDEX:

=INDEX(name,3) // Hannah

Cuối cùng, hàm INDEX trả về tên ở hàng thứ 3, “Hannah”.

Với XLOOKUP

Các Hàm XLOOKUP cũng có thể được sử dụng để giải quyết vấn đề này, bằng cách sử dụng cùng một cách tiếp cận được giải thích ở trên:

=XLOOKUP(LARGE(IF(group="A",score),F5),IF(group="A",score),name)

Như trên, LARGE được định cấu hình để hoạt động với một mảng được lọc bởi IF và trả về kết quả 93 thành XLOOKUP dưới dạng giá trị tra cứu:

=XLOOKUP(93,IF(group="A",score),name) // Hannah

Mảng tra cứu cũng được tạo bằng cách sử dụng IF làm bộ lọc điểm số từ Nhóm A. Với mảng trả về được cung cấp như Tên (B5: B16). XLOOKUP trả về “Hannah” là kết quả cuối cùng.

Ghi chú

  1. Để lấy tên của giá trị thứ n với tiêu chí, (tức là giới hạn kết quả ở nhóm A hoặc B), bạn sẽ cần mở rộng công thức để sử dụng logic bổ sung.
  2. Trong Excel 365, các Chức năng FILTER là một cách tốt hơn để liệt kê động kết quả trên cùng hoặc dưới cùng. Cách tiếp cận này sẽ tự động xử lý các mối quan hệ.

https://exceljet.net/formula/name-of-nth-largest-value-with-criteria