Công thức chung
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Tóm lược
Để lấy tên của giá trị lớn thứ n, bạn có thể sử dụng INDEX và MATCH với hàm LARGE. Trong ví dụ được hiển thị, công thức trong ô H5 là:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
trong đó tên (B5: B16) và điểm (D5: D16) là các phạm vi được đặt tên.
Giải trình
Tóm lại, công thức này sử dụng hàm LARGE để tìm giá trị lớn thứ n trong một tập dữ liệu. Khi chúng tôi có giá trị đó, chúng tôi cắm nó vào công thức INDEX và MATCH chuẩn để truy xuất tên được liên kết. Nói cách khác, chúng tôi sử dụng giá trị lớn thứ n giống như một "chìa khóa" để lấy thông tin liên quan.
Hàm LARGE là một cách đơn giản để nhận giá trị lớn thứ n trong một phạm vi. Chỉ cần cung cấp một phạm vi cho đối số đầu tiên (mảng) và một giá trị cho n là đối số thứ hai (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Làm việc từ trong ra ngoài, bước đầu tiên là lấy giá trị lớn nhất "đầu tiên" trong dữ liệu bằng hàm LARGE:
LARGE(score,F5) // returns 93
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 số lớn nhất (tức là điểm số cao nhất), là 93. Bây giờ chúng tôi có thể đơn giản hóa công thức thành:
=INDEX(name,MATCH(93,score,0))
Bên trong hàm INDEX, các hàm MATCH được thiết lập để xác định vị trí của 93 trong tên dãy số (D5: D16):
MATCH(93,score,0) // returns 3
Vì 93 xuất hiện ở hàng thứ 3, hàm MATCH trả về 3 trực tiếp cho INDEX dưới dạng số hàng, với tên là mảng:
=INDEX(name,3) // Hannah
Cuối cùng, hàm INDEX trả về tên ở hàng thứ 3, "Hannah".
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 khi công thức được sao chép xuống.
Truy xuất nhóm
Công thức cơ bản tương tự sẽ hoạt động để truy xuất bất kỳ thông tin liên quan nào. Để có được nhóm cho các giá trị lớn nhất, bạn có thể chỉ cần thay đổi mảng được cung cấp cho INDEX bằng nhóm phạm vi được đặt tên :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Với giá trị 1 trong F5, LARGE sẽ nhận được điểm cao nhất và công thức sẽ trả về "A".
Lưu ý: với Excel 365, bạn có thể sử dụng hàm FILTER để liệt kê động các kết quả trên cùng hoặc dưới cùng.
Với XLOOKUP
Hàm XLOOKUP cũng có thể được sử dụng để trả về tên của giá trị lớn thứ n như sau:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE trả về giá trị lớn nhất, 93, trực tiếp tới XLOOKUP dưới dạng giá trị tra cứu:
=XLOOKUP(93,score,name) // Hannah
Với tên dãy số (D5: D16) là mảng tra cứu, và tên (B5: B16) là mảng lợi nhuận, lợi nhuận XLOOKUP "Hannah" như trước đây.
Xử lý mối quan hệ
Các giá trị trùng lặp trong dữ liệu số sẽ tạo ra một "ràng buộc". Ví dụ: nếu sự ràng buộc xảy ra trong các giá trị đang được xếp hạng, nếu giá trị lớn nhất và lớn thứ hai giống nhau, LARGE sẽ trả về cùng một giá trị cho mỗi giá trị. Khi giá trị này được chuyển vào hàm MATCH, hàm MATCH sẽ trả về vị trí của kết quả khớp đầu tiên, vì vậy bạn sẽ thấy cùng một tên (đầu tiên) được trả về.
Nếu có khả năng ràng buộc, bạn có thể muốn thực hiện một số loại chiến lược cắt đứt. Một cách tiếp cận là tạo một cột trợ giúp mới gồm các giá trị đã được điều chỉnh để phá vỡ mối quan hệ. Sau đó, sử dụng các giá trị cột trợ giúp để xếp hạng và truy xuất thông tin. Điều này làm cho logic được sử dụng để phá vỡ quan hệ rõ ràng và rõ ràng.
Một cách tiếp cận khác là phá vỡ quan hệ chỉ dựa trên vị trí (nghĩa là hòa đầu tiên "thắng"). Đây là một công thức áp dụng cách tiếp cận đó:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
Ở đây, chúng tôi sử dụng MATCH để tìm số 1 và chúng tôi xây dựng một mảng tra cứu bằng cách sử dụng logic boolean (1) so sánh tất cả các điểm số với giá trị được trả về bởi LARGE:
score=LARGE(score,F5)
và (2) sử dụng kiểm tra phạm vi mở rộng nếu tên đã có trong danh sách được xếp hạng:
COUNTIF(H$4:H4,name)=0
Khi một tên đã có trong danh sách, nó sẽ bị logic "hủy bỏ" và giá trị (trùng lặp) tiếp theo được khớp. Lưu ý rằng phạm vi mở rộng bắt đầu trên hàng trước, để tránh tham chiếu vòng tròn.
Cách tiếp cận này hoạt động trong ví dụ này vì không có tên trùng lặp trong cột tên. Tuy nhiên, nếu các tên trùng lặp xảy ra trong các giá trị được xếp hạng, cách tiếp cận cần được điều chỉnh. Giải pháp đơn giản nhất là đảm bảo rằng tên là duy nhất.
Ghi chú
- Để nhận được tên của giá trị thứ n với tiêu chí, (tức là giới hạn kết quả cho nhóm A hoặc B), bạn sẽ cần mở rộng công thức để sử dụng logic bổ sung.
- Trong Excel 365, hàm FILTER là cách tốt hơn để liệt kê động các 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ệ.
