Công thức Excel: Giá trị lớn nhất trong tháng nhất định
Công thức Excel: Giá trị lớn nhất trong tháng nhất định

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))

Ở đâu bán hàng (D5: D15), ngày (B5: B15) và giá trị (C5: C15) là phạm vi được đặt tên.

Giải trình

Các 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 theo 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ó các tùy chọn khác. Một lựa chọn là một công thức mảng 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 Chức năng tổng hợp như thế này:

=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 đơn giản hơn dựa trên Hàm TEXT trong đó sử dụng một đị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 là 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à 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 kết quả 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.

https://exceljet.net/formula/max-value-in-given-month