Công thức chung
=MAX(INDEX(data,0,MATCH(column,header,0)))
Tóm lược
Để truy xuất giá trị lớn nhất trong một tập dữ liệu, trong đó cột là biến, bạn có thể sử dụng INDEX và MATCH cùng với hàm MAX. Trong ví dụ được hiển thị, công thức trong J5 là:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
trong đó dữ liệu (B5: F15) và tiêu đề (B4: F4) là các phạm vi được đặt tên.
Giải trình
Lưu ý: Nếu bạn chưa quen với INDEX và MATCH, hãy xem: Cách sử dụng INDEX và MATCH
Trong cấu hình chuẩn, hàm INDEX lấy một giá trị tại một hàng và cột nhất định. Ví dụ: để nhận giá trị ở hàng 2 và cột 3 trong một phạm vi nhất định:
=INDEX(range,2,3) // get value at row 2, column 3
Tuy nhiên, INDEX có một thủ thuật đặc biệt - khả năng truy xuất toàn bộ cột và hàng. Cú pháp liên quan đến việc cung cấp số 0 cho đối số "other". Nếu bạn muốn toàn bộ một cột, bạn cung cấp hàng bằng 0. Nếu bạn muốn toàn bộ một hàng, bạn cung cấp cột bằng 0:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Trong ví dụ được hiển thị, chúng tôi muốn tìm giá trị lớn nhất trong một cột nhất định. Độ xoắn là cột cần phải thay đổi để có thể dễ dàng thay đổi. Trong F5, công thức là:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Làm việc từ trong ra ngoài, trước tiên chúng ta sử dụng hàm MATCH để lấy "chỉ mục" của cột được yêu cầu trong ô J4:
MATCH(J4,header,0) // get column index
Với "Green" trong J4, hàm MATCH trả về 3, vì Green là giá trị thứ ba trong tiêu đề phạm vi được đặt tên . Sau khi MATCH trả về một kết quả, công thức có thể được đơn giản hóa thành:
=MAX(INDEX(data,0,3))
Với số không được cung cấp làm số hàng, INDEX trả về tất cả các giá trị trong cột 3 của dữ liệu phạm vi được đặt tên . Kết quả được trả về hàm MAX trong một mảng như sau:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Và MAX trả về kết quả cuối cùng, 93.
Giá trị tối thiểu
Để nhận được giá trị nhỏ nhất với một cột biến, bạn chỉ cần thay thế hàm MAX bằng hàm MIN. Công thức trong J6 là:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Với bộ lọc
Hàm FILTER mới cũng có thể được sử dụng để giải quyết vấn đề này, vì FILTER có thể lọc dữ liệu theo hàng hoặc theo cột. Bí quyết là tạo một bộ lọc hợp lý sẽ loại trừ các cột khác. COUNTIF hoạt động tốt trong trường hợp này, nhưng nó phải được định cấu hình "ngược", với J4 là phạm vi và tiêu đề cho tiêu chí:
=MAX(FILTER(data,COUNTIF(J4,header)))
Sau khi COUNTIF chạy, chúng tôi có:
=MAX(FILTER(data,(0,0,1,0,0)))
Và FILTER phân phối cột thứ 3 thành MAX, giống như hàm INDEX ở trên.
Thay thế cho COUNTIF, bạn có thể sử dụng ISNUMBER + MATCH để thay thế:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Hàm MATCH lại được thiết lập "ngược", để chúng ta nhận được một mảng có 5 giá trị sẽ đóng vai trò là bộ lọc logic. Sau khi ISNUMBER và MATCH chạy, chúng tôi có:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Và FILTER lại phân phối cột thứ 3 thành MAX.
