Hàm MAXIFS trong Excel – tìm giá trị tối đa với nhiều tiêu chí

Hướng dẫn cho thấy cách sử dụng hàm MAXIFS trong Excel để có được giá trị tối đa với các điều kiện.

Theo truyền thống, khi bạn cần tìm giá trị cao nhất với các điều kiện trong Excel, bạn phải tự xây dựng Công thức MAX IF. Mặc dù không phải là vấn đề lớn đối với người dùng có kinh nghiệm, nhưng điều đó có thể gây ra một số khó khăn nhất định cho người mới bởi vì, trước tiên, bạn nên nhớ cú pháp của công thức và, thứ hai, bạn cần biết cách làm việc với các công thức mảng. May mắn thay, Microsoft gần đây đã giới thiệu một chức năng mới cho phép chúng tôi thực hiện tối đa điều kiện một cách dễ dàng!

Hàm MAXIFS của Excel

Hàm MAXIFS trả về giá trị số lớn nhất trong phạm vi được chỉ định dựa trên một hoặc nhiều tiêu chí.

Cú pháp của hàm MAXIFS như sau:

MAXIFS (max_range, iteria_range1, iteria1, [criteria_range2, criteria2], Giáo)

Ở đâu:

• Phạm vi tối đa (bắt buộc) – phạm vi ô mà bạn muốn tìm giá trị tối đa.
• Criteria_range1 (bắt buộc) – phạm vi đầu tiên để đánh giá với tiêu chí1.
• Tiêu chí1 – điều kiện để sử dụng trên phạm vi đầu tiên. Nó có thể được đại diện bởi một số, văn bản hoặc biểu thức.
• Criteria_range2 / tiêu chí2, Hoài (tùy chọn) – phạm vi bổ sung và tiêu chí liên quan của họ. Lên đến 126 cặp phạm vi / tiêu chí được hỗ trợ.

Hàm MAXIFS này có sẵn trong Excel 2019 và Excel cho Office 365 trên Windows và Mac.

Ví dụ, hãy tìm cầu thủ bóng đá cao nhất ở trường địa phương của chúng tôi. Giả sử chiều cao của học sinh nằm trong các ô C2: C11 (max_range) và các môn thể thao ở B2: B11 (iteria_range1), sử dụng từ “bóng đá” làm tiêu chí1, và bạn sẽ nhận được công thức này:

=MAXIFS(C2:C11, B2:B11, "football")

Để làm cho công thức linh hoạt hơn, bạn có thể nhập môn thể thao đích vào một số ô (giả sử G1) và bao gồm tham chiếu ô trong tiêu chí1 tranh luận:

=MAXIFS(C2:C11, B2:B11, G1)

Ghi chú. Các Phạm vi tối đa và tiêu chí các đối số phải có cùng kích thước và hình dạng, nghĩa là chứa số lượng hàng và cột bằng nhau, nếu không thì #VALUE! lỗi được trả lại.

Cách sử dụng hàm MAXIFS trong Excel – ví dụ về công thức

Như bạn đã thấy, Excel MAXIFS khá đơn giản và dễ sử dụng. Tuy nhiên, nó có một vài sắc thái nhỏ tạo nên sự khác biệt lớn. Trong các ví dụ dưới đây, chúng tôi sẽ cố gắng tận dụng tối đa điều kiện tối đa trong Excel.

Tìm giá trị tối đa dựa trên nhiều tiêu chí

Trong phần đầu tiên của hướng dẫn này, chúng tôi đã tạo ra một công thức MAXIFS ở dạng đơn giản nhất để có được giá trị tối đa dựa trên một điều kiện. Bây giờ, chúng ta sẽ đưa ví dụ đó đi xa hơn và đánh giá hai tiêu chí khác nhau.

Giả sử, bạn muốn tìm cầu thủ bóng rổ cao nhất ở trường trung học cơ sở. Để hoàn thành nó, hãy xác định các đối số sau:

• Phạm vi tối đa – một loạt các ô chứa chiều cao – D2: D11.
• Criteria_range1 – một loạt các ô chứa các môn thể thao – B2: B11.
• Tiêu chí1 – “bóng rổ”, là đầu vào trong ô G1.
• Criteria_range2 – một phạm vi ô xác định loại trường – C2: C11.
• Tiêu chí2 – “junior”, là đầu vào trong ô G2.

Đặt các đối số lại với nhau, chúng ta có được các công thức sau:

Với tiêu chí “mã hóa cứng”:

=MAXIFS(D2:D11, B2:B11, "basketball", C2:C11, "junior")

Với tiêu chí trong các ô được xác định trước:

=MAXIFS(D2:D11, B2:B11, G1, C2:C11, G2)

Xin lưu ý rằng hàm MAXIFS trong Excel là trường hợp không nhạy cảm, vì vậy bạn không cần phải lo lắng về trường hợp thư trong tiêu chí của bạn.

Trong trường hợp bạn dự định sử dụng công thức của mình trên nhiều ô, hãy nhớ khóa tất cả các phạm vi bằng tài liệu tham khảo tế bào tuyệt đối, như thế này:

=MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2)

Điều này sẽ đảm bảo rằng công thức sao chép chính xác vào các ô khác – các tham chiếu tiêu chí thay đổi dựa trên vị trí tương đối của ô nơi công thức được sao chép trong khi phạm vi không thay đổi:

Là một phần thưởng bổ sung, tôi sẽ chỉ cho bạn một cách nhanh chóng để trích xuất một giá trị từ một ô khác có liên quan đến giá trị tối đa. Trong trường hợp của chúng tôi, đó sẽ là tên của người cao nhất. Đối với điều này, chúng tôi sẽ sử dụng cổ điển Công thức INDEX MATCH và lồng MAXIFS trong đối số đầu tiên của MATCH làm giá trị tra cứu:

=INDEX($A$2:$A$11, MATCH(MAXIFS($D$2:$D$11, $B$2:$B$11, G1, $C$2:$C$11, G2), $D$2:$D$11, 0))

Công thức cho chúng ta biết tên của cầu thủ bóng rổ cao nhất ở trường trung học là Liam:

Excel MAXIFS với các toán tử logic

Trong trường hợp khi bạn cần đánh giá tiêu chí số, hãy sử dụng toán tử logic nhu la:

• lớn hơn (>)
• ít hơn (<)
• lớn hơn hoặc bằng (> =)
• nhỏ hơn hoặc bằng (<=)
• không bằng (<>)

Toán tử “bằng với” (=) có thể được bỏ qua trong hầu hết các trường hợp.

Thông thường, việc chọn một toán tử không phải là một vấn đề, phần khó nhất là xây dựng các tiêu chí với cú pháp đúng. Đây là cách thực hiện:

• Một toán tử logic theo sau là một số hoặc văn bản phải được đặt trong dấu ngoặc kép như “> = 14” hoặc “<> đang chạy”.
• Trong trường hợp tham chiếu ô hoặc hàm khác, hãy sử dụng dấu ngoặc kép để bắt đầu chuỗi và ký hiệu và nối chuỗi tham chiếu và kết thúc chuỗi tắt, ví dụ: “>” & B1 hoặc “<” & TODAY ().

Để xem cách nó hoạt động trong thực tế, hãy thêm cột Tuổi (cột C) vào bảng mẫu của chúng tôi và tìm chiều cao tối đa trong số các bé trai từ 13 đến 14. Điều này có thể được thực hiện với các tiêu chí sau:

Tiêu chí1: “> = 13”

Tiêu chí2: “<= 14”

Bởi vì chúng tôi so sánh các số trong cùng một cột, tiêu chí trong cả hai trường hợp là như nhau (C2: C11):

=MAXIFS(D2:D11, C2:C11, ">=13", C2:C11, "<=14")

Nếu bạn không muốn mã hóa các tiêu chí trong công thức, hãy nhập chúng vào các ô riêng biệt (ví dụ G1 và H1) và sử dụng cú pháp sau:

=MAXIFS(D2:D11, C2:C11, ">="&G1, C2:C11, "<="&H1)

Ảnh chụp màn hình bên dưới hiển thị kết quả:

Ngoài số, toán tử logic cũng có thể làm việc với tiêu chí văn bản. Cụ thể, toán tử “không bằng” có ích khi bạn muốn loại trừ thứ gì đó khỏi các tính toán của bạn. Ví dụ: để tìm học sinh cao nhất trong tất cả các môn thể thao trừ bóng chuyền, hãy sử dụng công thức sau:

=MAXIFS(D2:D11, B2:B11, "<>volleyball")

Hoặc cái này, trong đó G1 là môn thể thao bị loại trừ:

=MAXIFS(D2:D11, B2:B11, "<>"&G1)

Công thức MAXIFS với các ký tự đại diện (khớp một phần)

Để đánh giá một điều kiện có chứa một văn bản hoặc ký tự cụ thể, hãy bao gồm một trong các ký tự đại diện sau trong tiêu chí của bạn:

• Dấu hỏi (?) Để khớp với bất kỳ ký tự đơn nào.
• Dấu hoa thị

để phù hợp với bất kỳ chuỗi ký tự.

=MAXIFS(D2:D11, B2:B11, "*ball")

Trong ví dụ này, chúng ta hãy tìm ra anh chàng cao nhất trong thể thao trò chơi. Vì tên của tất cả các môn thể thao trò chơi trong tập dữ liệu của chúng tôi kết thúc bằng từ “bóng”, chúng tôi đưa từ này vào tiêu chí và sử dụng dấu hoa thị để khớp với bất kỳ ký tự nào trước đó:

=MAXIFS(D2:D11, B2:B11, "*"&G1)

Bạn cũng có thể nhập “bóng” trong một số ô, ví dụ G1 và nối ký tự đại diện với tham chiếu ô:

Công thức MAXIFS với ký tự đại diện

Nhận giá trị tối đa trong phạm vi ngày

Vì ngày được lưu trữ dưới dạng số sê-ri trong hệ thống Excel nội bộ, bạn làm việc với tiêu chí ngày theo cách tương tự như bạn làm việc với số. Để minh họa điều này, chúng tôi sẽ thay thế Tuổi tác cột với Ngày sinh

và cố gắng tìm ra chiều cao tối đa trong số các bé trai sinh vào một năm cụ thể, vào năm 2004. Để hoàn thành nhiệm vụ này, chúng ta cần “lọc” ngày sinh lớn hơn hoặc bằng 1 tháng 1 năm 2004 và nhỏ hơn hoặc bằng 31 tháng 12 năm 2004.

=MAXIFS(D2:D11, C2:C11, ">=1-Jan-2004", C2:C11, "<=31-Dec-2004")

Khi xây dựng tiêu chí của bạn, điều quan trọng là bạn cung cấp ngày theo định dạng mà Excel có thể hiểu:

=MAXIFS(D2:D11, C2:C11, ">=1/1/2004", C2:C11, "<=12/31/2004")

Hoặc là Để ngăn chặn việc giải thích sai, nên sử dụngHàm DATE

=MAXIFS(D2:D11, C2:C11, ">="&DATE(2004,1,1), C2:C11, "<="&DATE(2004,12,31))

:

=MAXIFS(D2:D11, C2:C11, ">="&DATE(G1,1,1), C2:C11, "<="&DATE(G1,12,31))

Không giống như số, ngày nên được đặt trong dấu ngoặc kép khi được sử dụng trong tiêu chí riêng. Ví dụ:

Tìm giá trị tối đa dựa trên nhiều tiêu chí với logic OR Hàm MAXIFS của Excel được thiết kế để kiểm tra các điều kiện với logic AND – tức là nó chỉ xử lý các số đó trong Phạm vi tối đa

trong đó tất cả các tiêu chí là ĐÚNG. Tuy nhiên, trong một số tình huống, bạn có thể cần phải đánh giá các điều kiện bằng logic OR – tức là xử lý tất cả các số mà bất kỳ tiêu chí nào được chỉ định là TRUE.

Để làm cho mọi thứ dễ hiểu hơn, vui lòng xem xét ví dụ sau. Giả sử bạn muốn tìm chiều cao maximin của những người chơi bóng rổ hoặc bóng đá. Bạn làm điều đó như thế nào? Sử dụng “bóng rổ” làm tiêu chí1 và làm tiêu chí “bóng đá” 2 sẽ không hiệu quả, vì Excel sẽ cho rằng cả hai tiêu chí sẽ đánh giá thành TRUE. Giải pháp là tạo 2 công thức MAXIFS riêng biệt, mỗi công thức cho mỗi môn thể thao, sau đó sử dụng công thức cũ tốt Hàm MAX

=MAX(MAXIFS(C2:C11, B2:B11, "basketball"), MAXIFS(C2:C11, B2:B11, "football"))

để trả về số cao hơn:

Tìm giá trị tối đa dựa trên nhiều tiêu chí với logic OR Một cách khác là sử dụng mộtCông thức MAX IF với logic OR

.

7 điều cần nhớ về MAXIFS Excel

1. Dưới đây bạn sẽ tìm thấy một vài nhận xét sẽ giúp cải thiện công thức của bạn và tránh các lỗi phổ biến. Một số trong những quan sát này đã được thảo luận dưới dạng các mẹo và ghi chú trong các ví dụ của chúng tôi, nhưng có thể hữu ích để có được một bản tóm tắt ngắn về những gì bạn đã học: Hàm MAXIFS trong Excel có thể nhận giá trị cao nhất dựa trên một hoặc lànhiều tiêu chí
2. . Theo mặc định, Excel MAXIFS hoạt động vớiVÀ logic
3. , tức là trả về số lượng tối đa đáp ứng tất cả các điều kiện đã chỉ định. Để chức năng hoạt động, phạm vi tối đa và phạm vi tiêu chí phải có cùng cỡ vàhình dạng
4. . Hàm SUMIF làtrường hợp không nhạy cảm
5. , tức là nó không nhận ra trường hợp chữ cái trong tiêu chí văn bản. Khi viết công thức MAXIFS cho nhiều ô, hãy nhớ khóa phạm vi
6. với các tham chiếu ô tuyệt đối cho công thức để sao chép chính xác. Tâm trícú pháp của tiêu chí của bạn
   • ! Dưới đây là các quy tắc chính:
   • Khi được sử dụng riêng, văn bản và ngày nên được đặt trong dấu ngoặc kép, số và tham chiếu ô không nên.
7. Khi một số, ngày hoặc văn bản được sử dụng với toán tử logic, toàn bộ biểu thức phải được đặt trong dấu ngoặc kép như “> = 10”; tham chiếu ô và các chức năng khác phải được nối bằng cách sử dụng một dấu và như “>” & G1.

MAXIFS chỉ khả dụng trong Excel 2019 và Excel cho Office 365. Trong các phiên bản trước, chức năng này không khả dụng.

Đó là cách bạn có thể tìm thấy giá trị tối đa trong Excel với các điều kiện. Tôi cảm ơn bạn đã đọc và hy vọng sớm gặp lại bạn trên blog của chúng tôi!

Tải xuống có sẵn:

Bài tập Excel MAXIFS mẫu