Hướng dẫn này trình bày cách sử dụng Excel MAXtôiHàm FS và MINIFShàng tấn trong Excel để đếm dữ liệu đáp ứng các tiêu chí nhất định.
MAXIFS Tổng quan về chức năng
Bạn có thể sử dụng hàm MAXIFS trong Excel để đếm các ô chứa một giá trị cụ thể, đếm các ô lớn hơn hoặc bằng một giá trị, v.v.
(Chú ý cách các đầu vào công thức xuất hiện)
MAXIFS Cú pháp và đối số của hàm:
= MAXIFS (max_range, range1, tiêu chí1, [dải ô2], [tiêu chí2],…)
Phạm vi tối đa - Phạm vi giá trị được sử dụng để xác định giá trị tối đa.
range1 - Phạm vi đầu tiên để đánh giá.
tiêu chí1 - Các tiêu chí để sử dụng trên phạm vi1.
range2 - [tùy chọn] Phạm vi thứ hai để đánh giá.
range2 - [tùy chọn] Phạm vi thứ hai để đánh giá.
Hàm MAXIFS và MINIFS là gì?
Mặc dù Microsoft đã sớm cung cấp cho chúng tôi cách tìm tổng hoặc số lượng của một dải ô dựa trên tiêu chí, MAXIFS và MINIFS ra đời muộn hơn trong vòng đời của bảng tính. Rất may, bây giờ chúng ta có chúng và chúng có cấu trúc và cách sử dụng rất giống nhau. Nói một cách đơn giản, hai hàm có thể quét qua một loạt các ô để kiểm tra một tiêu chí cụ thể và sau đó đưa ra giá trị lớn nhất hoặc của các giá trị trong một phạm vi tương ứng với các giá trị đó. Bởi vì chúng được tạo sau bản cập nhật lớn 2007, không có “MAXIF / MINIF” cũ hơn để lo lắng.
Nếu bạn chưa làm như vậy, bạn có thể xem lại nhiều cấu trúc và ví dụ tương tự trong bài viết COUNTIFS.
Ví dụ cơ bản
Hãy xem xét bảng này:
Nếu chúng ta muốn tìm số lượng chuối tối đa được hái là bao nhiêu, chúng ta có thể sử dụng hàm MAXIFS. Chúng tôi sẽ luôn đưa ra phạm vi số mà chúng tôi muốn trả về kết quả làm đối số đầu tiên. Sau đó, chúng tôi sẽ liệt kê các phạm vi tiêu chí và tiêu chí tương ứng của chúng. Trong ví dụ của chúng tôi, chúng tôi có thể đặt công thức này trong D2:
= MAXIFS (B2: B7, A2: A7, D1)
Tương tự, nếu chúng ta muốn tìm số chuối nhỏ nhất / tối thiểu được hái, chúng ta có thể chỉ cần chuyển đổi tên của hàm trong khi giữ nguyên phần còn lại.
= MINIFS (B2: B7, A2: A7, D1)
Đối với phần còn lại của bài viết, chúng tôi sẽ có xu hướng trình bày các ví dụ bằng cách sử dụng MAXIFS hoặc MINIFS thay vì liệt kê cả hai mỗi lần. Chỉ cần nhớ rằng bạn có thể dễ dàng chuyển đổi giữa hai cách tùy thuộc vào kết quả mà bạn đang cố gắng đạt được.
Làm việc với Ngày, Nhiều tiêu chí
Khi làm việc với ngày tháng trong bảng tính, mặc dù có thể nhập ngày tháng trực tiếp vào công thức, nhưng cách tốt nhất là có ngày tháng trong một ô để bạn có thể chỉ tham chiếu ô trong công thức. Ví dụ: điều này giúp máy tính biết rằng bạn đang muốn sử dụng ngày 27/5/2020 chứ không phải số 5 chia cho 27 chia cho 2022.
Hãy xem bảng tiếp theo của chúng tôi ghi lại số lượng khách truy cập vào một trang web hai tuần một lần.
Chúng ta có thể chỉ định điểm bắt đầu và điểm kết thúc của phạm vi mà chúng ta muốn xem xét trong D2 và E2. Sau đó, công thức của chúng tôi để tìm số lượng khách truy cập cao nhất trong phạm vi này có thể là:
= MAXIFS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Lưu ý cách chúng tôi có thể nối các so sánh của “=” với các tham chiếu ô để tạo tiêu chí. Ngoài ra, mặc dù cả hai tiêu chí đang được áp dụng cho cùng một phạm vi ô (A2: A7), bạn cần phải viết ra phạm vi hai lần, một lần cho mỗi tiêu chí.
Nhiều cột
Khi sử dụng nhiều tiêu chí, bạn có thể áp dụng chúng cho cùng một phạm vi như chúng tôi đã làm với ví dụ trước đó hoặc bạn có thể áp dụng chúng cho các phạm vi khác nhau. Hãy kết hợp dữ liệu mẫu của chúng tôi vào bảng này:
Chúng tôi đã thiết lập một số ô để người dùng nhập nội dung họ muốn tìm kiếm vào các ô từ E2 đến G2. Do đó, chúng tôi cần một công thức tính số lượng táo ít nhất được hái trong tháng Hai. Công thức của chúng tôi trông giống như sau:
= MINIFS (C2: C7, B2: B7, “> =” & F2, B2: B7, “<=” & G2, A2: A7, E2)
MAXIFS / MINIFS với logic loại OR
Cho đến thời điểm này, các ví dụ mà chúng tôi đã sử dụng đều là so sánh dựa trên AND, nơi chúng tôi đang tìm kiếm các hàng đáp ứng tất cả các tiêu chí của chúng tôi. Bây giờ, chúng tôi sẽ xem xét trường hợp khi bạn muốn tìm kiếm khả năng một hàng đáp ứng một tiêu chí này hoặc tiêu chí khác.
Hãy xem danh sách bán hàng này:
Chúng tôi muốn tìm doanh số bán hàng tối đa cho cả Adam và Bob. Đơn giản nhất là lấy hai MAXIFS, và sau đó lấy MAX của cả hai hàm đó.
= MAX (MAXIFS (B2: B7, A2: A7, "Adam"), MAXIFS (B2: B7, A2: A7, "Bob"))
Ở đây, chúng tôi đã để máy tính tính điểm cá nhân của chúng tôi và sau đó chúng tôi cộng chúng lại với nhau.
Tùy chọn tiếp theo của chúng tôi phù hợp khi bạn có nhiều phạm vi tiêu chí hơn, như vậy bạn không muốn phải viết lại toàn bộ công thức nhiều lần. Trong công thức trước, chúng tôi đã yêu cầu máy tính tính toán hai MAXIFS khác nhau theo cách thủ công. Tuy nhiên, bạn cũng có thể làm điều này bằng cách viết các tiêu chí của bạn bên trong một mảng, như sau:
= MAX (MAXIFS (B2: B7, A2: A7, {"Adam", "Bob"}))
Nhìn vào cách mảng được xây dựng bên trong dấu ngoặc nhọn. Khi máy tính đánh giá công thức này, nó sẽ biết rằng chúng ta muốn tính hàm MAXIFS cho mỗi mục trong mảng của chúng ta, do đó tạo ra một mảng số. Sau đó, hàm MAX bên ngoài sẽ lấy mảng số đó và biến nó thành một số duy nhất. Bước qua đánh giá công thức, nó sẽ giống như sau:
= MAX (MAXIFS (B2: B7, A2: A7, {"Adam", "Bob"})) = MAX (14548, 24956) = 24956
Chúng tôi nhận được cùng một kết quả, nhưng chúng tôi có thể viết ra công thức ngắn gọn hơn một chút.
Xử lý khoảng trống
Đôi khi tập dữ liệu của bạn sẽ có các ô trống mà bạn cần tìm hoặc tránh. Việc thiết lập các tiêu chí cho những tiêu chí này có thể hơi phức tạp, vì vậy hãy xem một ví dụ khác.
Lưu ý rằng ô A3 thực sự trống, trong khi ô A5 có công thức trả về một chuỗi có độ dài bằng 0 là “”. Nếu chúng ta muốn tìm tổng số trung bình của thực sự ô trống, chúng tôi sẽ sử dụng tiêu chí là “=” và công thức của chúng tôi sẽ giống như sau:
= MAXIFS (B2: B7, A2: A7, "=")
Mặt khác, nếu chúng tôi muốn lấy giá trị trung bình cho tất cả các ô trông trống về mặt trực quan, chúng tôi sẽ thay đổi tiêu chí thành “” và công thức trông giống như
= MAXIFS (B2: B7, A2: A7, "")
Hãy lật lại: điều gì sẽ xảy ra nếu bạn muốn tìm giá trị trung bình của các ô không trống? Rất tiếc, thiết kế hiện tại sẽ không cho phép bạn tránh được chuỗi có độ dài bằng 0. Bạn có thể sử dụng tiêu chí là “”, nhưng như bạn có thể thấy trong ví dụ, tiêu chí đó vẫn bao gồm giá trị từ hàng 5.
= MAXIFS (B2: B7, A2: A7, "")
Nếu bạn không cần đếm các ô chứa chuỗi độ dài bằng 0, bạn sẽ muốn xem xét sử dụng hàm LEN bên trong hàm SUMPRODUCT
MAXIFS & MINIIFS trong Google Trang tính
Hàm MAXIFS & MINIIFS hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel:
