Thông 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. Đây 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 ta 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, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Ở đây:

• max_range (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.
• criteria1 - đ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 / criteria2, ... (tùy chọn) - phạm vi bổ sung và tiêu chí liên quan của chúng. 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 (criteria_range1), sử dụng từ “football” làm criteria1, 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 criteria1 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à các ví dụ

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ị lớn nhất 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:

• max_range - 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.
• criteria1 - “basketball”, là đầu vào trong ô G1.
• criteria_range2 - một phạm vi ô xác định loại trường – C2: C11.
• criteria2 – “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í “junior”:

=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 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 tham chiếu 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 như 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:

criteria1: “> = 13”

criteria2: “<= 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ừ “ball”, 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 “ball” trong một số ô, ví dụ G1 và nối ký tự đại diện với tham chiếu ô:

Lấy 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ế Age cột với Birth date

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à để tránh việc giải thích sai, nên sử dụng hàm DATE

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

hoặc:

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

=MAXIFS(D2:D11, C2:C11, "10/5/2005")

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à TRUE. 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 “basketball” làm creteria1 và làm tiêu chí “football” 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 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 công thức MAX IF với logic OR.

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

Dưới đây bạn sẽ tìm thấy một số nhận xét giúp cải thiện công thức của mình và tránh các lỗi phổ biến. Một số 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 nếu bạn có được một bản tóm tắt ngắn gọn về những gì bạn đã học được:

1. Hàm MAXIFS trong Excel có thể nhận giá trị cao nhất dựa trên một hoặc nhiều tiêu chí.
2. Theo mặc định, MAXIFS hoạt động với logic AND, tức là trả về số tối đa đáp ứng tất cả các điều kiện đã chỉ định.
3. Để 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 kích thước và hình dạng.
4. Hàm SUMIF không phân biệt chữ hoa chữ thường, tức là nó không nhận dạng chữ cái trong tiêu chí văn bản.
5. Khi viết công thức MAXIFS cho nhiều ô, hãy nhớ khóa các phạm vi có tham chiếu ô tuyệt đối để công thức sao chép chính xác.
6. Hãy nhớ cú pháp của tiêu chí của bạn!  Dưới đây là các quy tắc chính:
   • Khi sử dụng riêng, văn bản và ngày tháng phải được đặt trong dấu ngoặc kép, số và tham chiếu ô không được đặt trong dấu ngoặc kép.
   • Khi sử dụng số, ngày tháng hoặc văn bản cù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 hàm khác phải được nối bằng cách sử dụng ký hiệu và như ">"&G1.
7. MAXIFS chỉ khả dụng trong Excel 2019 và Excel for Office 365. Ở các phiên bản trước, chức năng này không khả dụng.

Trên đây là cách giúp bạn có thể tìm thấy giá trị tối đa trong Excel với các điều kiện. Cảm ơn các bạn đã đọc và hy vọng bài viết giúp ích được cho các bạn!