Công thức chung
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Tóm lược
Để tìm giá trị lớn nhất trong một tháng nhất định, bạn có thể sử dụng hàm MAXIFS hoặc một trong các lựa chọn thay thế khác bên dưới. Trong ví dụ được hiển thị, công thức trong G5 là:
=MAXIFS(sales,dates,">="&G4,dates,"<="&EOMONTH(G4,0))
Trong đó doanh số (D5: D15), ngày (B5: B15) và giá trị (C5: C15) là các phạm vi được đặt tên.
Giải trình
Hàm MAXIFS có thể tìm giá trị lớn nhất trong một phạm vi dựa trên một hoặc nhiều tiêu chí. Trong ví dụ được hiển thị, chúng tôi đang sử dụng MAXIFS để tìm giá trị bán hàng tối đa dựa trên một tháng nhất định bằng các ngày "tiếp thị lại" giữa ngày đầu tiên của tháng và ngày cuối cùng của tháng. Tiêu chí đầu tiên kiểm tra xem ngày có lớn hơn hoặc bằng ngày đầu tiên của tháng hay không:
dates,">="&G4,dates // first of month
Lưu ý: chúng tôi giả định ngày trong G4 là ngày "đầu tiên của tháng".
Tiêu chí thứ hai kiểm tra xem ngày có nhỏ hơn hoặc bằng ngày cuối cùng của tháng hay không, được tính bằng hàm EOMONTH:
dates,"<="&EOMONTH(G4,0) // last of month
Khi cả hai tiêu chí trả về TRUE, ngày đó nằm trong tháng nhất định và MAXIFS trả về giá trị tối đa của ngày đáp ứng tiêu chí.
Công thức thay thế
Nếu phiên bản Excel của bạn không bao gồm hàm MAXIFS, có những tùy chọn khác. Một tùy chọn là công thức mảng đơn giản dựa trên các hàm MAX và IF:
(=MAX(IF(TEXT(dates,"my")=TEXT(G4,"my"),sales)))
Lưu ý: Đây là công thức mảng và phải được nhập bằng control + shift + enter.
Một tùy chọn khác là sử dụng hàm AGGREGATE như sau:
=AGGREGATE(14,6,sales/(TEXT(dates,"my")=TEXT(G4,"my")),1)
Cả hai tùy chọn trên đều sử dụng một phương pháp kiểm tra ngày tháng đơn giản hơn dựa trên hàm TEXT sử dụng định dạng số để trích xuất tháng và năm từ một ngày. Không thể sử dụng hàm TEXT như thế này bên trong hàm MAXIFS vì MAXIFS yêu cầu một phạm vi. Các cách tiếp cận thay thế này được giải thích chi tiết hơn ở đây.
Khách hàng tối đa
Để truy xuất khách hàng được liên kết với giá trị tối đa trong một tháng nhất định, công thức trong G6 là:
(=INDEX(clients,MATCH(1,(sales=G5)*(TEXT(dates,"my")=TEXT(G4,"my")),0)))
Đây là một công thức mảng và phải được nhập bằng control + shift + enter.
Ý tưởng ở đây là chúng ta đã biết giá trị tối đa trong một tháng (G5) và chúng ta có thể sử dụng giá trị đó làm "chìa khóa" để tra cứu khách hàng. Lưu ý rằng chúng ta phải lặp lại logic được sử dụng để tách biệt các giá trị trong tháng nhất định, để tránh khớp sai trong một tháng khác.
Giống như các lựa chọn thay thế được đề cập ở trên, công thức này cũng sử dụng một bài kiểm tra đơn giản hóa cho các ngày dựa trên hàm TEXT. Đọc thêm về INDEX và MATCH với nhiều tiêu chí tại đây.
Đối với công thức tất cả trong một, hãy thay thế G5 bằng một trong các tùy chọn công thức được giải thích ở trên để xác định giá trị tối đa trong một tháng nhất định.
