excel maxif formula
Bài viết cho thấy một vài cách khác nhau để có được giá trị tối đa trong Excel dựa trên một hoặc 1 số ít điều kiện kèm theo mà bạn chỉ định .
Trong hướng dẫn trước của chúng tôi, chúng tôi đã xem xét những sử dụng phổ cập của Hàm MAX được phong cách thiết kế để trả về số lớn nhất trong bộ tài liệu. Tuy nhiên, trong một số ít trường hợp, bạn hoàn toàn có thể cần đi sâu vào tài liệu của mình hơn nữa để tìm giá trị tối đa dựa trên những tiêu chuẩn nhất định. Điều này hoàn toàn có thể được thực thi bằng cách sử dụng một vài công thức khác nhau và bài viết này lý giải toàn bộ những cách hoàn toàn có thể .

Công thức Excel MAX IF

Cho đến gần đây, Microsoft Excel không có tính năng MAX IF tích hợp để có được giá trị tối đa dựa trên những điều kiện kèm theo. Trước đây, họ đã trình làng MAXIFS và giờ đây, người dùng Excel 2019 và Excel năm nay có kèm theo ĐK Office 365 hoàn toàn có thể triển khai tối đa điều kiện kèm theo một cách thuận tiện .

Trong Excel 2013 và các phiên bản trước đó, bạn vẫn phải tạo riêng cho mình công thức mảng bằng cách kết hợp hàm MAX với câu lệnh IF:

{ = MAX ( NẾU ( tiêu chuẩn = = tiêu chuẩn, Phạm vi tối đa ) ) }
Để xem công thức MAX IF chung này hoạt động giải trí như thế nào trên tài liệu thực, vui mắt xem xét ví dụ sau. Giả sử, bạn có một bảng với tác dụng nhảy xa của nhiều sinh viên. Bảng gồm có tài liệu cho ba vòng và bạn đang tìm kiếm tác dụng tốt nhất của một vận động viên đơn cử, Jacob nói. Với tên sinh viên trong A2 : A10 và khoảng cách trong C2 : C10, công thức có hình dạng này :

=MAX(IF(A2:A10="Jacob", C2:C10))

Hãy nhớ rằng một công thức mảng phải luôn luôn được nhập bằng cách nhấn Ctrl + Shift + Enter Phím đồng thời. Kết quả là, nó được tự động hóa bao quanh với dấu ngoặc nhọn như hiển thị trong ảnh chụp màn hình hiển thị bên dưới ( nhập niềng răng bằng tay sẽ không hoạt động giải trí ! ) .
Tôi là bảng tính thực tiễn, sẽ thuận tiện hơn khi nhập tiêu chuẩn vào một số ít ô, để bạn hoàn toàn có thể thuận tiện đổi khác điều kiện kèm theo mà không cần đổi khác công thức. Vì vậy, chúng tôi nhập tên mong ước trong F1 và nhận được tác dụng sau :

=MAX(IF(A2:A10=F1, C2:C10))

Công thức Excel MAX IF để tìm giá trị cao nhất với điều kiện

Công thức này hoạt động như thế nào

Trong bài kiểm tra logic của hàm IF, chúng tôi so sánh list những tên ( A2 : A10 ) với tên đích ( F1 ). Kết quả của hoạt động giải trí này là một mảng TRUE và FALSE, trong đó những giá trị TRUE biểu lộ những tên khớp với tên đích ( Jacob ) :

{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

Cho giá trị_ if_true đối số, chúng tôi cung ứng tác dụng nhảy xa ( C2 : C10 ), vì thế nếu kiểm tra logic đánh giá thành TRUE, số tương ứng từ cột C được trả về. Các giá trị_ if_false đối số bị bỏ lỡ, nghĩa là sẽ chỉ có giá trị FALSE trong đó điều kiện kèm theo không được cung ứng :

{FALSE;FALSE;FALSE;5.48;5.42;5.57;FALSE;FALSE;FALSE}

Mảng này được đưa đến hàm MAX, trả về số lượng tối đa bỏ lỡ những giá trị FALSE .

Tiền boa. Để xem các mảng nội bộ được thảo luận ở trên, chọn phần tương ứng của công thức trong bảng tính của bạn và nhấn nút F9 Chìa khóa. Để thoát chế độ đánh giá công thức, nhấn nút Trốn Chìa khóa.

Công thức MAX IF với nhiều tiêu chí

Trong trường hợp khi bạn cần tìm giá trị tối đa dựa trên nhiều điều kiện, bạn có thể:
Sử dụng báo cáo IF lồng nhau để bao gồm các tiêu chí bổ sung:

{ = MAX ( NẾU ( iteria_range1 = = tiêu chí1, NẾU ( iteria_range2 = = tiêu chí2, Phạm vi tối đa ) ) ) }
Hoặc giải quyết và xử lý nhiều tiêu chuẩn bằng cách sử dụng thao tác nhân :
{ = MAX ( NẾU ( ( iteria_range1 = = tiêu chí1 ) * ( iteria_range2 = = tiêu chí2 ), Phạm vi tối đa ) ) }
Giả sử bạn có hiệu quả nam và nữ trong một bảng duy nhất và bạn muốn tìm ra bước nhảy dài nhất trong số những cô gái ở vòng 3. Để triển khai xong, chúng tôi nhập tiêu chuẩn tiên phong ( nữ ) vào G1, tiêu chuẩn thứ hai ( 3 ) trong G2 và sử dụng những công thức sau để tính giá trị tối đa :

=MAX(IF(B2:B16=G1, IF(C2:C16=G2, D2:D16)))

=MAX(IF((B2:B16=G1)*(C2:C16=G2), D2:D16))

Vì cả hai đều là công thức mảng, hãy nhớ nhấn Ctrl + Shift + Enter để triển khai xong chúng một cách đúng chuẩn .

Như được hiển thị trong ảnh chụp màn hình bên dưới, các công thức tạo ra kết quả tương tự, do đó, việc sử dụng cái nào là vấn đề sở thích cá nhân của bạn. Đối với tôi, công thức với logic Boolean dễ đọc và xây dựng hơn – nó cho phép thêm bao nhiêu điều kiện bạn muốn mà không cần lồng các hàm IF bổ sung.
Công thức MAX IF để có được số lớn nhất với nhiều tiêu chí

Những công thức này hoạt động như thế nào

Công thức tiên phong sử dụng hai hàm IF lồng nhau để nhìn nhận hai tiêu chuẩn. Trong thử nghiệm logic của câu lệnh IF tiên phong, chúng tôi so sánh những giá trị trong cột Giới tính ( B2 : B16 ) với tiêu chuẩn trong G1 ( “ Nữ ” ). Kết quả là một mảng những giá trị TRUE và FALSE trong đó TRUE đại diện thay mặt cho tài liệu tương thích với tiêu chuẩn :

{FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE}

Theo cách tương tự như, hàm IF thứ hai kiểm tra những giá trị trong cột Round ( C2 : C16 ) theo tiêu chuẩn trong G2 .
Cho value_if_true đối số trong câu lệnh IF thứ hai, chúng tôi cung ứng tác dụng nhảy xa ( D2 : D16 ) và bằng cách này, chúng tôi nhận được những mục có TRUE trong hai mảng tiên phong ở những vị trí tương ứng ( ví dụ : những mục có giới tính là “ nữ ” và làm tròn là 3 ) :

{FALSE; FALSE; FALSE; FALSE; FALSE; 4.63; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 4.52}

Mảng sau cuối này đi đến hàm MAX và nó trả về số lớn nhất .
Công thức thứ hai nhìn nhận những điều kiện kèm theo tựa như trong một thử nghiệm logic đơn và hoạt động giải trí nhân giống như toán tử AND :
Khi những giá trị TRUE và FALSE được sử dụng trong bất kể hoạt động giải trí số học nào, chúng sẽ được quy đổi thành 1 và 0, tương ứng. Và do tại nhân với 0 luôn cho số không, mảng hiệu quả chỉ có 1 khi toàn bộ những điều kiện kèm theo là TRUE. Mảng này được nhìn nhận trong bài kiểm tra logic của hàm IF, trả về khoảng cách tương ứng với những thành phần 1 ( TRUE ) .

MAX IF không có mảng

Nhiều người dùng Excel, bao gồm cả tôi, có thành kiến ​​với các công thức mảng và cố gắng loại bỏ chúng bất cứ khi nào có thể. May mắn thay, Microsoft Excel có một vài chức năng xử lý mảng nguyên bản và chúng ta có thể sử dụng một trong các chức năng đó, cụ thể là TÓM TẮT, như một loại “trình bao bọc” xung quanh MAX.

Công thức MAX IF chung không có mảng như sau :
= TỔNG HỢP ( MAX ( ( iteria_range1 = = tiêu chí1 ) * ( iteria_range2 = = tiêu chí2 ) * Phạm vi tối đa ) )
Đương nhiên, bạn hoàn toàn có thể thêm nhiều cặp khoanh vùng phạm vi / tiêu chuẩn nếu cần .
Để xem công thức hoạt động giải trí, chúng tôi sẽ sử dụng tài liệu từ ví dụ trước. Mục đích là để có được cú nhảy tối đa của một vận động viên nữ ở vòng 3 :

=SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))

Công thức này được cạnh tranh với một bình thường Đi vào tổ hợp phím và trả về kết quả giống như công thức MAX IF của mảng:
Công thức MAX IF không mảng trong Excel

Nhìn kỹ hơn vào ảnh chụp màn hình hiển thị ở trên, bạn hoàn toàn có thể nhận thấy rằng những bước nhảy không hợp lệ được lưu lại bằng “ x ” trong những ví dụ trước có 0 giá trị trong những hàng 3, 11 và 15 và phần tiếp theo lý giải nguyên do .

Công thức này hoạt động như thế nào

Như với công thức MAX IF, chúng tôi nhìn nhận hai tiêu chuẩn bằng cách so sánh từng giá trị trong những cột Giới tính ( B2 : B16 ) và Vòng ( C2 : C16 ) với những tiêu chuẩn trong những ô G1 và G2. Kết quả là hai mảng giá trị TRUE và FALSE. Nhân những thành phần của mảng trong cùng một vị trí sẽ chuyển TRUE và FALSE thành 1 và 0 tương ứng, trong đó 1 đại diện thay mặt cho những mục cung ứng cả hai tiêu chuẩn. Mảng nhân thứ ba chứa tác dụng nhảy xa ( D2 : D16 ). Và do tại nhân với 0 sẽ cho 0, chỉ những mục có 1 ( TRUE ) trong những vị trí tương ứng sống sót :

{0; 0; 0; 0; 0; 4.63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}

Trong trường hợp Phạm vi tối đa chứa bất kể giá trị văn bản nào, phép toán nhân trả về lỗi # VALUE do hàng loạt công thức sẽ không hoạt động giải trí .
Hàm MAX lấy nó từ đây và trả về số lớn nhất cung ứng những điều kiện kèm theo đã chỉ định. Mảng hiệu quả gồm có một thành phần duy nhất { 4,63 } đi đến hàm SUMPRODVEL và nó xuất ra số lượng tối đa trong một ô .

Ghi chú. Do logic cụ thể của nó, công thức hoạt động với các cảnh báo sau:

  • Phạm vi nơi bạn tìm kiếm giá trị cao nhất phải chỉ chứa các số. Nếu có bất kỳ giá trị văn bản nào, hãy #VALUE! lỗi được trả lại.
  • Công thức không thể đánh giá điều kiện “không bằng 0” trong tập dữ liệu âm. Đến tìm giá trị tối đa bỏ qua số không, sử dụng công thức MAX IF hoặc hàm MAXIFS.

Công thức Excel MAX IF với logic OR

Để tìm giá trị tối đa khi bất kì trong số các điều kiện đã chỉ định được đáp ứng, hãy sử dụng công thức MAX IF đã quen thuộc với logic Boolean, nhưng thêm các điều kiện thay vì nhân chúng.

{ = MAX ( NẾU ( ( iteria_range1 = = tiêu chí1 ) + ( iteria_range2 = = tiêu chí2 ), Phạm vi tối đa ) ) }
Ngoài ra, bạn hoàn toàn có thể sử dụng công thức không phải là mảng sau :
= TỔNG HỢP ( MAX ( ( ( iteria_range1 = = tiêu chí1 ) + ( iteria_range2 = = tiêu chí2 ) ) * Phạm vi tối đa ) )
Ví dụ : tất cả chúng ta hãy tìm ra hiệu quả tốt nhất trong vòng 2 và 3. Xin quan tâm rằng trong ngôn từ Excel, tác vụ được tạo theo cách khác : trả về giá trị tối đa nếu vòng là 2 hoặc 3 .
Với những vòng được liệt kê trong B2 : B10, hiệu quả trong C2 : C10 và tiêu chuẩn ở F1 và H1, công thức diễn ra như sau :

=MAX(IF((B2:B10=F1) + (B2:B10=H1), C2:C10))

Nhập công thức bằng cách nhấn Ctrl + Shift + Enter tổ hợp phím và bạn sẽ nhận được kết quả này:
Một công thức MAX IF mảng với logic OR

Giá trị tối đa với cùng điều kiện kèm theo cũng hoàn toàn có thể được tìm thấy bằng cách sử dụng công thức không phải là mảng này :

=SUMPRODUCT(MAX(((B2:B10=F1) + (B2:B10=H1)) * C2:C10))

Tuy nhiên, chúng ta cần thay thế tất cả các giá trị “x” trong cột C bằng các số 0 trong trường hợp này vì SUMPRODVEL MAX chỉ hoạt động với dữ liệu số:
Công thức MAX IF không mảng với logic OR

Những công thức này hoạt động như thế nào

Công thức mảng hoạt động giải trí đúng mực giống như MAX IF với logic AND ngoại trừ việc bạn tham gia những tiêu chuẩn bằng cách sử dụng thao tác bổ trợ thay vì nhân. Trong những công thức mảng, phép cộng hoạt động giải trí như toán tử OR :
Việc thêm hai mảng TRUE và FALSE ( hiệu quả của việc kiểm tra những giá trị trong B2 : B10 theo những tiêu chuẩn trong F1 và H1 ) tạo ra một mảng 1 và 0 trong đó 1 đại diện thay mặt cho những mục mà điều kiện kèm theo là TRUE và 0 đại diện thay mặt cho những mục trong đó cả hai điều kiện kèm theo là FALSE. Kết quả là, hàm IF “ giữ ” tổng thể những mục trong C2 : C10 ( value_if_true ) với bất kể điều kiện kèm theo nào là TRUE ( 1 ) ; những mục còn lại được thay thế sửa chữa bằng FALSE vì value_if_false đối số không được chỉ định .
Công thức không mảng hoạt động giải trí theo cách tựa như. Sự độc lạ là thay vì kiểm tra logic của IF, bạn nhân những thành phần của mảng 1 và 0 với những thành phần của mảng tác dụng nhảy xa ( C2 : C10 ) ở những vị trí tương ứng. Điều này vô hiệu những mục không phân phối bất kể điều kiện kèm theo nào ( có 0 trong mảng tiên phong ) và giữ những mục phân phối một trong những điều kiện kèm theo ( có 1 trong mảng tiên phong ) .

MAXIFS – cách dễ dàng để tìm giá trị cao nhất với các điều kiện

Người dùng Excel 2019 và Excel cho Office 365 không gặp rắc rối trong việc thuần hóa mảng để thiết kế xây dựng công thức MAX IF của riêng họ. Các phiên bản Excel này phân phối hàm MAXIFS được chờ đón từ lâu, giúp tìm giá trị lớn nhất với điều kiện kèm theo chơi của trẻ .
Trong đối số tiên phong của MAXIFS, bạn nhập khoanh vùng phạm vi cần tìm giá trị tối đa ( D2 : D16 trong trường hợp của chúng tôi ) và trong những đối số tiếp theo, bạn hoàn toàn có thể nhập tối đa 126 cặp khoanh vùng phạm vi / tiêu chuẩn. Ví dụ :

=MAXIFS(D2:D16, B2:B16, G1, C2:C16, G2)

Như được hiển thị trong ảnh chụp màn hình bên dưới, công thức đơn giản này không có vấn đề gì với việc xử lý phạm vi chứa cả giá trị số và văn bản:
Hàm MAXIFS của Excel để tìm giá trị cao nhất với các điều kiện

Để biết thông tin chi tiết cụ thể về công dụng này, vui mừng xem Hàm MAXIFS với những ví dụ về công thức .

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

Bài tập thực hành để tải về

Ví dụ về công thức Excel MAX IF

Bạn cũng có thể quan tâm:

Leave a Reply

Email của bạn sẽ không được hiển thị công khai.