Công thức Excel: Tối đa của mỗi cột thứ n
Công thức Excel: Tối đa của mỗi cột thứ n

Công thức chung
{=MAX(IF(MOD(COLUMN(rng)-COLUMN(rng.first)+1,n)=0,rng))}
Tóm lược

Để có được 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à một 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 đây) trông như thế này:

{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ư bằng 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 các 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 khi cần thiết, 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 được 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))}

https://exceljet.net/formula/max-of-every-nth-column