Công thức chung
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Tóm lược
Để xác định vị trí của một giá trị trong mảng 2D, bạn có thể sử dụng hàm SUMPRODUCT. Trong ví dụ được hiển thị, các công thức được sử dụng để định vị số hàng và cột của giá trị lớn nhất trong mảng là:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
trong đó "dữ liệu" là dải ô được đặt tên C5: G14.
Lưu ý: đối với ví dụ này, chúng tôi tùy ý tìm vị trí của giá trị lớn nhất trong dữ liệu, nhưng bạn có thể thay thế dữ liệu = MAX (dữ liệu) bằng bất kỳ kiểm tra logic nào khác sẽ cô lập một giá trị nhất định. Cũng lưu ý rằng các công thức này sẽ không thành công nếu có các giá trị trùng lặp trong mảng.
Giải trình
Để nhận số hàng, dữ liệu được so sánh với giá trị lớn nhất, tạo ra một mảng kết quả TRUE FALSE. Chúng được nhân với kết quả của ROW (dữ liệu) tạo ra và mảng số hàng được liên kết với dải ô "dữ liệu" được đặt tên:
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
Thao tác nhân khiến Excel buộc các giá trị TRUE FALSE trong mảng đầu tiên thành 1s và 0, vì vậy chúng ta có thể hình dung một bước trung gian như sau:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
Sau đó hàm SUMPRODUCT trả về kết quả là 9, tương ứng với hàng thứ 9 trên trang tính. Để có được một chỉ mục liên quan đến phạm vi được đặt tên là "dữ liệu", chúng tôi sử dụng:
-ROW(data)+1
Kết quả cuối cùng là mảng (5; 4; 3; 2; 1; 0; -1), từ đó chỉ giá trị đầu tiên (5) được hiển thị.
Công thức xác định vị trí cột hoạt động theo cách tương tự.
Lưu ý: Tôi gặp phải phương pháp này trong một bình luận của Mike Erickson trên MrExcel.com. Cũng có một số ý tưởng hay khác trong chuỗi đó, bao gồm tùy chọn công thức mảng.
