Công thức chung
(=MAX(IF(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0,rng)))
Tóm lược
Để nhận giá trị tối đa của mỗi cột thứ n, bạn có thể sử dụng công thức mảng dựa trên các hàm MAX, MOD và COLUMN. Trong ví dụ được hiển thị, công thức trong M5 là:
=MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter.
Giải trình
Công thức này sử dụng các hàm MOD và COLUMN để lọc ra các giá trị không có trong cột thứ n, sau đó chạy MAX trên kết quả.
Điều quan trọng là đoạn mã này:
MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0
Ở đây, công thức sử dụng hàm COLUMN để nhận một tập hợp các số cột "tương đối" cho phạm vi (được giải thích chi tiết tại đây) trông giống như sau:
(1,2,3,4,5,6,7,8,9,10)
Mảng này đi vào hàm MOD dưới dạng đối số số:
MOD((1,2,3,4,5,6,7,8,9,10),K5)=0
trong đó L5 là giá trị để sử dụng cho "thứ n". Hàm MOD trả về phần còn lại cho mỗi số cột chia cho N. Vì vậy, ví dụ: khi N = 3, MOD sẽ trả về một mảng như sau:
(1,2,0,1,2,0,1,2,0,1)
Lưu ý rằng số không xuất hiện cho các cột 3, 6, 9, v.v., tương ứng với mọi cột thứ 3. Mảng này được so sánh với 0 với biểu thức logic = 0 để buộc TRUE khi phần dư là 0 và FALSE khi không. Các giá trị này đi vào hàm IF dưới dạng kiểm tra logic. Hàm IF lọc tương ứng, vì vậy chỉ các giá trị trong phạm vi ban đầu trong cột thứ n mới đưa nó vào mảng cuối cùng. Các giá trị khác trở thành FALSE.
Khi n = 3, mảng cuối cùng bên trong MAX trông giống như sau:
MAX((FALSE,FALSE,11,FALSE,FALSE,8,FALSE,FALSE,19,FALSE))
Cuối cùng, hàm MAX bỏ qua giá trị FALSE và trả về giá trị lớn nhất còn lại, 19.
Tối đa của mọi cột khác
Nếu bạn muốn nhận giá trị tối đa của mọi cột khác, chỉ cần điều chỉnh công thức này nếu cần, việc hiểu công thức sẽ tự động chỉ định 1 cho cột đầu tiên trong phạm vi. Để nhận tối đa EVEN cột, hãy sử dụng:
(=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=0,rng)))
Để nhận tối đa các cột ODD, hãy sử dụng:
(=MAX(IF(MOD(COLUMN(A1:Z1)-COLUMN(A1)+1,2)=1,rng)))
