Công thức chung
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Tóm lược
Để tìm giá trị lớn nhất vào một ngày trong tuần nhất định (tức là Thứ Hai, Thứ Ba, Thứ Tư, v.v.), bạn có thể sử dụng công thức mảng đơn giản dựa trên các hàm MAX, IF và TEXT. Trong ví dụ được hiển thị, công thức trong ô F5 là:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Trong đó ngày (B5: B15) và giá trị (C5: C15) là phạm vi được đặt tên.
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
Làm việc từ trong ra ngoài, hàm TEXT được sử dụng để trích xuất giá trị ngày trong tuần cho mỗi ngày:
=TEXT(dates,"ddd")
Điều này dẫn đến một mảng như thế này:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
sau đó được so sánh với văn bản trong F4, "Mon". Kết quả là một mảng khác, chỉ chứa các giá trị TRUE và FALSE:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Lưu ý rằng mỗi TRUE tương ứng với một ngày Thứ Hai. Mảng này được trả về bên trong hàm IF dưới dạng kiểm tra logic. Nó hoạt động như một bộ lọc để lọc ra các giá trị vào các ngày khác trong tuần. Kết quả cuối cùng từ IF, được trả về trực tiếp cho hàm MAX, trông giống như sau:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX tự động bỏ qua các giá trị FALSE và trả về giá trị còn lại cao nhất, 94.
Với AGGREGATE
Đối với một công thức kỳ lạ hơn không yêu cầu control + shift + enter, bạn có thể sử dụng hàm AGGREGATE như sau:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Đây là công thức được sử dụng trong ô F6 trong ví dụ được hiển thị. Ở đây chúng tôi đưa ra AGGREGATE 14 cho đối số hàm (LARGE) và 6 cho đối số tùy chọn (bỏ qua lỗi). Sau đó, chúng tôi xây dựng một biểu thức logic bằng cách sử dụng hàm TEXT để kiểm tra tất cả các ngày cho các ngày Thứ Hai. Kết quả của phép toán này là một mảng các giá trị TRUE / FALSE, trở thành mẫu số của các giá trị ban đầu. Khi được sử dụng trong một phép toán, FALSE đánh giá là 0 và ném # DIV / 0! lỗi. TRUE đánh giá là 1 và trả về giá trị ban đầu. Mảng giá trị và lỗi cuối cùng hoạt động giống như một bộ lọc. AGGREGATE bỏ qua tất cả các lỗi và trả về giá trị lớn nhất (tối đa) trong số các giá trị còn sót lại.
MAXIFS
Hàm MAXIFS, có sẵn trong Excel Office 365, có thể trả về giá trị tối đa sử dụng trên một hoặc nhiều tiêu chí mà không cần công thức mảng. Tuy nhiên, MAXIFS là một hàm dựa trên phạm vi và sẽ không cho phép các hàm khác như TEXT xử lý các giá trị trong phạm vi tiêu chí. Tuy nhiên, bạn có thể thêm cột trợ giúp vào dữ liệu, tạo giá trị các ngày trong tuần bằng TEXT, sau đó sử dụng MAXIFS với cột trợ giúp làm phạm vi tiêu chí.
