Bài viết cho thấy thêm một vài phương pháp khác nhau để có được giá bán trị về tối đa trong Excel dựa trên một hoặc một vài điều kiện mà chúng ta chỉ định.
Bạn đang xem: Hàm max có điều kiện trong excel
Trong giải đáp trước của chúng tôi, chúng tôi đã coi xét những sử dụng thông dụng của Hàm MAX được thiết kế với để trả về số lớn số 1 trong cỗ dữ liệu. Mặc dù nhiên, trong một vài trường hợp, bạn có thể cần đi sâu vào dữ liệu của chính bản thân mình hơn nữa nhằm tìm giá chỉ trị về tối đa dựa trên các tiêu chuẩn nhất định. Điều này rất có thể được thực hiện bằng cách sử dụng một vài ba công thức khác nhau và bài viết này phân tích và lý giải tất cả những cách gồm thể.
Công thức Excel MAX IF
Cho mang đến gần đây, Microsoft Excel không có tác dụng MAX IF tích hợp để có được giá bán trị tối đa dựa trên các điều kiện. Trước đây, họ đã giới thiệu MAXIFS và bây giờ, người tiêu dùng Excel 2019 và Excel năm 2016 có kèm theo đăng ký Office 365 có thể thực hiện buổi tối đa đk một bí quyết dễ dàng.
Trong Excel 2013 và những phiên bạn dạng trước đó, bạn vẫn phải lập riêng cho doanh nghiệp công thức mảng bằng phương pháp kết hòa hợp hàm MAX cùng với câu lệnh IF:
= MAX (NẾU (tiêu chí= =tiêu chí, Phạm vi buổi tối đa))
Để xem cách làm MAX IF bình thường này vận động như thế nào trên tài liệu thực, vui tươi xem xét ví dụ sau. Giả sử, các bạn có một bảng với hiệu quả nhảy xa của khá nhiều sinh viên. Bảng bao gồm dữ liệu cho ba vòng và ai đang tìm kiếm tác dụng tốt tuyệt nhất của một tải viên vắt thể, Jacob nói. Cùng với tên sinh viên trong A2: A10 và khoảng cách trong C2: C10, bí quyết có bề ngoài này:
=MAX(IF(A2:A10="Jacob", C2:C10))
Hãy nhớ là 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 auto bao quanh với vết ngoặc nhọn như hiển thị trong hình ảnh chụp màn hình dưới (nhập niềng răng bằng tay sẽ ko hoạt động!).
Tôi là bảng tính thực tế, sẽ tiện lợi hơn lúc nhập tiêu chí vào một số trong những ô, để bạn có thể dễ dàng biến đổi điều kiện cơ mà không cần biến đổi công thức. Do vậy, shop chúng tôi nhập tên ước muốn trong F1 cùng nhận được tác dụng sau:
=MAX(IF(A2:A10=F1, C2:C10))
Công thức này chuyển động như thay nào
Trong bài kiểm tra ngắn gọn xúc tích của hàm IF, công ty chúng tôi so sánh danh sách các tên (A2: A10) với tên đích (F1). Hiệu quả của chuyển động này là một trong những mảng TRUE và FALSE, trong những số ấy các cực hiếm TRUE bộc lộ các tên khớp với thương hiệu đích (Jacob):
FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE
Cho giá trị_ if_true đối số, shop chúng tôi cung cấp tác dụng nhảy xa (C2: C10), vì chưng vậy trường hợp kiểm tra súc tích đá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ỏ qua, nghĩa là đang chỉ có giá trị FALSE trong số đó điều kiện ko được đáp ứng:
FALSE;FALSE;FALSE;5.48;5.42;5.57;FALSE;FALSE;FALSE
Mảng này được đưa tới hàm MAX, trả về con số tối đa bỏ qua những giá trị FALSE.
Tiền boa. Để xem những mảng nội cỗ được bàn luận ở trên, chọn phần tương ứng của phương pháp trong bảng tính của chúng ta và thừa nhận nút F9 Chìa khóa. Để thoát chính sách đánh giá chỉ công thức, dìm nút Trốn Chìa khóa.
Công thức MAX IF với khá nhiều tiêu chí
Trong ngôi trường hợp khi bạn cần tìm giá bán trị tối đa dựa trên nhiều điều kiện, các bạn có thể:Sử dụng report IF lồng nhau để bao hàm các tiêu chuẩn bổ sung:
= MAX (NẾU (iteria_range1= =tiêu chí1, NẾU(iteria_range2= =tiêu chí2, Phạm vi buổi tối đa)))
Hoặc xử lý những tiêu chí bằng cách sử dụng làm việ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àng trong một bảng tuyệt nhất và bạn có nhu cầu tìm ra bước nhảy lâu năm nhất trong số các cô gái ở vòng 3. Để trả thành, chúng tôi nhập tiêu chí đầu tiên (nữ) vào G1, tiêu chí thứ nhị (3) trong G2 và sử dụng những công thức sau để tính giá chỉ trị về 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 phần lớn là cách làm mảng, hãy nhớ thừa nhận Ctrl + Shift + Enter để xong xuôi chúng một cách bao gồm xác.
Như được hiển thị trong hình ảnh chụp screen bên dưới, những công thức chế tạo ra ra kết quả tương tự, bởi vì đó, việc thực hiện cái như thế nào là vấn đề sở thích cá thể của bạn. Đối với tôi, bí quyết với ngắn gọn xúc tích Boolean dễ nhìn đọc và kiến thiết hơn – nó chất nhận được thêm từng nào điều kiện bạn có nhu cầu mà không buộc phải lồng các hàm IF bổ sung.
Những phương pháp này vận động như cố kỉnh nào
Công thức thứ nhất sử dụng nhị hàm IF lồng nhau để reviews hai tiêu chí. Trong test nghiệm lô ghích của câu lệnh IF đầu tiên, chúng tôi so sánh các giá trị trong cột nam nữ (B2: B16) với tiêu chuẩn trong G1 (“Nữ”). Công dụng là một mảng các giá trị TRUE cùng FALSE trong các số ấy TRUE thay mặt đại diện cho dữ liệu cân xứng với tiêu chí:
FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; TRUE
Theo cách tương tự, hàm IF vật dụng hai kiểm tra những giá trị vào 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, công ty chúng tôi cung cấp tác dụng nhảy xa (D2: D16) và bằng phương pháp này, chúng tôi nhận được các mục có TRUE trong hai mảng đầu tiên ở những vị trí tương xứng (ví dụ: những mục tất cả giới tính là “nữ” và làm cho tròn là 3):
FALSE; FALSE; FALSE; FALSE; FALSE; 4.63; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; 4.52
Mảng cuối cùng này đi mang đến hàm MAX với nó trả về số mập nhất.
Công thức thiết bị hai reviews các điều kiện tương tự trong một thử nghiệm lô ghích đơn và hoạt động nhân giống như toán tử AND:
Khi những giá trị TRUE với FALSE được sử dụng trong ngẫu nhiên hoạt cồn số học nào, bọn chúng sẽ được đổi khác thành 1 với 0, tương ứng. Và chính vì nhân với 0 luôn luôn cho số không, mảng công dụng chỉ có 1 khi tất cả các đk là TRUE. Mảng này được nhận xét trong bài xích kiểm tra lô ghích của hàm IF, trả về khoảng cách tương ứng cùng với các phần tử 1 (TRUE).
MAX IF không có mảng
Nhiều người dùng Excel, bao hàm cả tôi, bao gồm thành kiến với các công thức mảng và nỗ lực loại quăng quật chúng bất cứ lúc nào có thể. Như mong muốn thay, Microsoft Excel bao gồm một vài công dụng xử lý mảng nguyên bạn dạng và chúng ta có thể sử dụng 1 trong các các chức năng đó, cụ thể là TÓM TẮT, như một một số loại “trình bao bọc” bao quanh MAX.
Công thức MAX IF chung không tồn tại mảng như sau:
= TỔNG HỢP (MAX ((iteria_range1= =tiêu chí1) * (iteria_range2= =tiêu chí2) * Phạm vi về tối đa))
Đương nhiên, bạn cũng có thể thêm các cặp phạm vi / tiêu chí nếu cần.
Để xem bí quyết hoạt động, cửa hàng chúng tôi sẽ sử dụng dữ liệu từ ví dụ trước. Mục tiêu là để sở hữu được cú nhảy buổi tối đa của một di chuyển viên nữ ở vòng 3:
=SUMPRODUCT(MAX(((B2:B16=G1) * (C2:C16=G2) * (D2:D16))))
Công thức này được tuyên chiến đối đầu với một thông thường Đi vào tổng hợp phím với trả về tác dụng giống như bí quyết MAX IF của mảng:
Nhìn kỹ rộng vào ảnh chụp màn hình ở trên, bạn cũng có thể nhận thấy rằng các bước nhảy chưa hợp lệ được khắc ghi bằng “x” trong những ví dụ trước tất cả 0 giá trị trong các hàng 3, 11 và 15 với phần tiếp theo lý giải lý do.
Công thức này hoạt động như nắm nào
Như với bí quyết MAX IF, chúng tôi đánh giá hai tiêu chí bằng cách so sánh từng giá chỉ trị trong số cột giới tính (B2: B16) và Vòng (C2: C16) với các tiêu chí trong các ô G1 cùng G2. Hiệu quả là nhị mảng giá trị TRUE và FALSE. Nhân các bộ phận của mảng trong và một vị trí sẽ chuyển TRUE và FALSE thành 1 với 0 tương ứng, trong những số ấy 1 đại diện cho các mục đáp ứng nhu cầu cả hai tiêu chí. Mảng nhân thứ cha chứa kết quả nhảy xa (D2: D16). Và bởi vì nhân với 0 sẽ mang lại 0, chỉ các mục có một (TRUE) trong các vị trí tương xứng tồn tại:
0; 0; 0; 0; 0; 4.63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52
Trong trường thích hợp Phạm vi buổi tối đa chứa ngẫu nhiên giá trị văn bạn dạng nào, phép toán nhân trả về lỗi #VALUE do cục bộ công thức sẽ không hoạt động.
Xem thêm: 6 Phong Các Phong Cách Thời Trang Trên Thế Giới, Các Phong Cách Thời Trang Bạn Nên Biết
Hàm MAX lấy nó từ đây cùng trả về số phệ nhất đáp ứng các đk đã chỉ định. Mảng công dụng bao gồm một trong những phần tử tốt nhất 4,63 đi mang lại hàm SUMPRODVEL cùng nó xuất ra con số tối nhiều trong một ô.