183 CÁCH DÙNG HÀM MAX(IF) mới nhất

công thức excel MAX IF Công thức MAX IF có nhiều tiêu chí MAX IF không có mảng Công thức Excel MAX IF có đối số OR

Bài viết chỉ ra một số cách khác nhau để tìm giá trị lớn nhất trong Excel dựa trên một hoặc nhiều điều kiện mà bạn chỉ định.

Bạn đang xem: Cách sử dụng hàm max(if)

Trong hướng dẫn trước, chúng ta đã xem xét các cách sử dụng phổ biến của hàm MAX, được thiết kế để trả về số lớn nhất trong tập dữ liệu. Tuy nhiên, trong một số trường hợp, bạn có thể cần tìm hiểu sâu hơn về dữ liệu của mình để tìm giá trị tối đa dựa trên các tiêu chí nhất định. Điều này có thể được thực hiện 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 giải thích tất cả các phương pháp có thể.

công thức thạch excel max

Cho đến gần đây, Microsoft Excel không có hàm MAX IF tích hợp sẵn để tìm giá trị lớn nhất dựa trên các điều kiện. Họ đã giới thiệu MAXIFS trước đây và giờ đây, người dùng Excel 2019 và Excel 2016 có đăng ký Office 365 có thể dễ dàng tận dụng tối đa tình huống.

Trong Excel 2013 và các phiên bản cũ hơn, bạn vẫn phải tự tạo 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, giới hạn tối đa,

Để xem công thức MAX IF đơn giản này hoạt động như thế nào trên dữ liệu thực, vui lòng xem xét ví dụ sau. Giả sử bạn có một bảng chứa kết quả nhảy xa của nhiều học sinh. Jacobs cho biết bảng này bao gồm dữ liệu cho ba vòng và bạn đang tìm kiếm kết quả tốt nhất của một vận động viên cụ thể. Với tên sinh viên trong A2:A10 và khoảng cách trong C2:C10, công thức có dạng sau:

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

Hãy nhớ rằng một công thức mảng phải luôn được nhập bằng cách nhấn đồng thời các phím Ctrl + Shift + Enter. Do đó, nó sẽ tự động được bao quanh bởi các dấu ngoặc nhọn như trong ảnh chụp màn hình bên dưới (nhập dấu ngoặc nhọn theo cách thủ công sẽ không hoạt động!).

Tôi bảng tính thực, sẽ thuận tiện hơn khi nhập tiêu chí vào một số ô, vì vậy bạn có thể dễ dàng thay đổi điều kiện mà không cần thay đổi công thức. Vì vậy, chúng tôi nhập tên mong muốn vào F1 và nhận được đầu ra sau:

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

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

Khi kiểm tra logic của hàm IF, chúng tôi so sánh danh sách tên (A2:A10) với tên mục tiêu (F1). Kết quả của thao tác này là một mảng TRUE và FALSE, trong đó các giá trị TRUE đại diện cho các tên khớp với tên mục tiêu (Jacob):

{Người nói dối; Người nói dối; Người nói dối; sự thật; sự thật; sự thật; Người nói dối; Người nói dối; Người nói dối}

value_if_true logic, chúng tôi cung cấp một bước nhảy dài (C2:C10), vì vậy nếu kiểm tra logic đánh giá là TRUE, số tương ứng trong cột C sẽ được trả về. value_if_false Các đối số được bỏ qua, điều này sẽ chỉ có nghĩa là FALSE khi điều kiện không được đáp ứng:

{Người nói dối; Người nói dối; Người nói dối; 5,48; 5,42; 5,57; Người nói dối; Người nói dối; Người nói dối}

Mảng này được truyền cho hàm MAX, hàm trả về số giá trị FALSE tối đa bị loại bỏ.

tiền boa. Để xem các mảng bên trong được thảo luận ở trên, hãy chọn phần có liên quan của công thức trong trang tính của bạn và nhấn phím F9. Nhấn nút phím Escape để thoát khỏi chế độ đánh giá công thức.

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

Nếu cần tìm giá trị lớn nhất dựa trên nhiều điều kiện, bạn có thể làm như sau:Sử dụng các câu lệnh IF lồng nhau để bao gồm các tiêu chí bổ sung:

{=max(nếu(iteria_range1,tiêu chí 1Nếu(iteria_range2,tiêu chí 2, giới hạn tối đa,

hoặc xử lý nhiều tiêu chí bằng thao tác nhân:

{=max(nếu((iteria_range1,tiêu chí 1,iteria_range2,tiêu chí 2, giới hạn tối đa,

Giả sử bạn có kết quả nam và nữ trong cùng một bảng và bạn muốn tìm người nhảy xa nhất trong số các cô gái ở vòng 3. Để kết thúc, chúng ta nhập tiêu chí thứ nhất (nữ) vào G1, tiêu chí thứ hai (3) vào G2 và sử dụng công thức sau để tính giá trị lớn nhất:

=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 để hoàn thành chúng một cách chính xác.

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

,

Làm thế nào để các công thức này hoạt động?

Công thức đầu tiên sử dụng hai hàm IF lồng nhau để đánh giá hai tiêu chí. Trong thử nghiệm logic của câu lệnh IF đầu tiên, chúng tôi so sánh các giá trị trong cột Giới tính (B2:B16) với các tiêu chí trong G1 (“Nữ”). Kết quả là một mảng các giá trị TRUE và FALSE trong đó TRUE biểu thị dữ liệu phù hợp với tiêu chí:

{Sai lầm; Sai lầm; Sai lầm; sự thật; sự thật; sự thật; Sai lầm; Sai lầm; Sai lầm; Sai lầm; Sai lầm; Sai lầm; sự thật; sự thật; sự thật}

Tương tự, hàm IF thứ hai kiểm tra các giá trị trong cột ROUND (C2:C16) so với tiêu chí trong G2.

value_if_true Logic trong câu lệnh IF thứ hai dẫn đến một bước nhảy dài (D2:D16) và do đó chúng ta nhận được các mục có TRUE trong hai mảng đầu tiên ở các vị trí tương ứng (ví dụ: các mục có giới tính là “nữ” và được làm tròn lên đến 3):

{Sai lầm; Sai lầm; Sai lầm; Sai lầm; Sai lầm; 4,63; Sai lầm; Sai lầm; Sai lầm; Sai lầm; Sai lầm; Sai lầm; Sai lầm; Sai lầm; 4.52}

Mảng cuối cùng này đi vào hàm MAX và nó trả về số lớn nhất.

Công thức thứ hai đánh giá các điều kiện tương tự trong một bài kiểm tra logic đơn lẻ và hoạt động tương tự như toán tử AND:

Khi các giá trị TRUE và FALSE được sử dụng trong một phép toán số học, chúng được chuyển đổi thành 1 và 0 tương ứng. Và bởi vì nhân với 0 luôn cho kết quả bằng 0, mảng kết quả chỉ chứa 1 khi tất cả các điều kiện là TRUE. Mảng này được đánh giá trong bài kiểm tra logic của hàm IF, hàm này trả về khoảng cách tương ứng với phần tử 1 (TRUE).

max nếu 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ô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 số hàm mảng riêng và chúng ta có thể sử dụng một trong số chúng, đó là SUMMARY, 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:

=SUM(MAX((iteria_range1,tiêu chí 1,iteria_range2,tiêu chí 2, giới hạn tối đa,

Đương nhiên, bạn có thể thêm nhiều cặp danh mục/tiêu chí nếu cần.

Để xem công thức đang hoạt động, chúng ta sẽ sử dụng dữ liệu từ ví dụ trước. Mục tiêu nhảy tối đa của vận động viên nữ trong vòng 3 là:

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

Công thức này cạnh tranh với một lần nhấn phím Enter bình thường và trả về kết quả tương tự như công thức MAX IF cho một mảng:

,

Xem kỹ ảnh chụp màn hình ở trên, bạn có thể thấy rằng các bước nhảy không hợp lệ được đánh dấu bằng “x” trong các ví dụ trước có giá trị 0 ở các hàng 3, 11 và 15, v.v.

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

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

{0; 0; 0; 0; 0; 4,63; 0; 0; 0; 0; 0; 0; 0; 0; 4.52}

Nếu giới hạn tối đa Khi có giá trị văn bản, phép toán nhân sẽ trả về lỗi #VALUE vì toàn bộ công thức sẽ không hoạt động.

Xem thêm: Download Junior Screen Ruler 1, phần mềm đo kích thước đối tượng trong ảnh

Hàm MAX lấy nó từ đây và trả về số lớn nhất thỏa mãn các điều kiện đã chỉ định. Mảng kết quả bao gồm một phần tử duy nhất {4,63} được chuyển đến hàm SUMPRODVEL và nó xuất ra số lượng lớn nhất trong một ô.