Tóm lược
Ví dụ này cho thấy cách truy xuất ngày sớm nhất và mới nhất liên quan đến một dự án. Trong ví dụ được hiển thị, các công thức trong H5 và I5 là:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
trong đó "dữ liệu" là một bảng Excel như được hiển thị và tên dự án trong cột G khớp với cột B.
Lưu ý: MINIFS và MAXIFS chỉ khả dụng trong Excel 365 và Excel 2019. Trong các phiên bản Excel khác, bạn có thể sử dụng công thức mảng đơn giản, như được giải thích bên dưới.
Giới thiệu
Nhiệm vụ ở đây là tìm ngày sớm nhất và mới nhất liên quan đến một dự án nhất định. Ngày sớm nhất đến từ cột Bắt đầu và ngày mới nhất đến từ cột Kết thúc .
Bạn có thể muốn sử dụng một hàm tra cứu như VLOOKUP, XLOOKUP hoặc INDEX và MATCH. Tuy nhiên, vì mỗi dự án có nhiều hơn một mục nhập và các mục nhập có thể không phải lúc nào cũng được sắp xếp theo ngày, điều này trở nên khó khăn.
Một cách tiếp cận tốt hơn là sử dụng quá trình loại bỏ: loại bỏ ngày tháng cho các dự án khác và chỉ làm việc với những ngày còn lại.
Giải trình
Hàm MINIFS trả về giá trị số nhỏ nhất đáp ứng tiêu chí được cung cấp và hàm MAXIFS trả về giá trị số lớn nhất đáp ứng tiêu chí được cung cấp.
Giống như COUNTIFS và SUMIFS, các hàm này sử dụng các "cặp" phạm vi / tiêu chí để áp dụng các điều kiện. Đối với cả hai công thức, chúng ta chỉ cần một điều kiện: tên dự án phải bằng tên trong cột G:
data(Project),G5 // condition
Để có ngày bắt đầu sớm nhất, chúng tôi sử dụng:
=MINIFS(data(Start),data(Project),G5) // earliest date
Ở đây, hàm MINIFS trả về giá trị nhỏ nhất trong cột Bắt đầu nơi dự án bằng "Omega" (từ ô G5). Vì ngày tháng trong Excel chỉ là số, ngày tối thiểu giống với ngày sớm nhất.
Để có ngày kết thúc mới nhất, chúng tôi sử dụng:
=MAXIFS(data(End),data(Project),G5) // latest date
Ở đây, MAXIFS trả về giá trị lớn nhất trong cột Kết thúc nơi dự án bằng "Omega". Như trên, giá trị lớn nhất giống với ngày gần nhất.
Công thức mảng thay thế
Nếu bạn không có MINIFS và MAXIFS, bạn có thể sử dụng các công thức mảng đơn giản, dựa trên các hàm MIN và MAX, để nhận được cùng một kết quả. Để có ngày bắt đầu sớm nhất:
(=MIN(IF(data(Project)=G5,data(Start))))
Đối với ngày kết thúc gần nhất:
(=MAX(IF(data(Project)=G5,data(End))))
Lưu ý: cả hai công thức đều là công thức mảng và phải được nhập bằng control + shift + enter, trong Excel 2019 hoặc phiên bản cũ hơn. Với Excel 365, bạn có thể nhập công thức bình thường, vì công thức mảng là công thức gốc.
Trong cả hai trường hợp, hàm IF được sử dụng để "lọc" các giá trị ngày tháng như sau:
IF(data(Project)=G5,data(End)) // filter dates by project
Khi G5 là "Omega", IF trả về ngày kết thúc. Nếu không, IF trả về FALSE. Vì chúng tôi đang kiểm tra tất cả các tên dự án trong bảng cùng một lúc, kết quả là một mảng giá trị như sau:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Các số sê-ri lớn là ngày Excel được liên kết với dự án Omega. Các giá trị khác là FALSE, vì dự án không phải là Omega. Bởi vì MIN và MAX được lập trình để bỏ qua các giá trị logic TRUE và FALSE, chúng chỉ hoạt động trên các giá trị còn lại. MIN trả về ngày nhỏ nhất (sớm nhất) và MAX trả về ngày lớn nhất (mới nhất).
