Tải xuống sổ làm việc mẫu
Hướng dẫn này sẽ trình bày cách tìm giá trị ô lớn nhất đáp ứng các điều kiện cụ thể trong Excel và Google Trang tính.
Hàm Max If Array
Hàm MAX xác định giá trị lớn nhất trong một chuỗi số.
Chúng ta có thể sử dụng Hàm MAX kết hợp với Hàm IF để xác định giá trị lớn nhất đáp ứng một điều kiện xác định.
Người dùng Google Trang tính và Excel 2022 trở lên được khuyến nghị sử dụng Hàm MAXIFS đơn giản hơn. Điều này được giải thích trong phần sau.
Ví dụ này sẽ sử dụng các Hàm MAX và IF trong một công thức mảng để xác định giá trị lớn nhất Kích thước đặt hàng cho mỗi Tên cửa hàng
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
Trong Office 365 và các phiên bản Excel sau năm 2022, bạn có thể chỉ cần nhập công thức trên như bình thường (bằng cách nhấn ENTER).
Tuy nhiên, đối với Excel 2022 trở về trước, bạn phải nhập công thức bằng cách nhấn CTRL + SHIFT + ENTER. Sau khi làm như vậy, bạn sẽ nhận thấy dấu ngoặc nhọn của mảng xuất hiện xung quanh công thức.
Để cho biết công thức này hoạt động như thế nào, hãy chia nhỏ công thức thành các bước.
Đây là công thức cuối cùng của chúng tôi (được hiển thị mà không có dấu ngoặc công thức mảng được thêm tự động):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Đầu tiên, các giá trị phạm vi ô được thêm vào công thức dưới dạng mảng:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Tiếp theo Tên cửa hàng Điều kiện = ”A” tạo ra một mảng các giá trị TRUE / FALSE:
| 1 | = MAX (IF ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Sau đó, Hàm IF thay đổi tất cả các giá trị TRUE thành giá trị có liên quan Kích thước đặt hàng:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
Hàm MAX xác định số lớn nhất trong mảng, trong khi bỏ qua bất kỳ giá trị FALSE nào, để hiển thị số lớn nhất Kích thước đặt hàng cho Tên cửa hàng = “A”:
| 1 | =600 |
Nếu tối đa - Nhiều tiêu chí
Chúng tôi cũng có thể xác định giá trị lớn nhất dựa trên nhiều tiêu chí bằng cách sử dụng logic Boolean.
Ví dụ này sẽ hiển thị Kích thước đặt hàng cho mỗi Tên cửa hàng, nhưng đối với Ngày đặt hàng trước 4/30/2021 bằng cách sử dụng các Hàm MAX, IF và DATE:
| 1 | {= MAX (IF ((B3: B8 = "A") * (C3: C8 |
Lưu ý ở đây chúng tôi nhân hai bộ tiêu chí TRUE / FALSE với nhau:
| 1 | (B3: B8 = "A") * (C3: C8 |
Nếu cả hai tiêu chí đều TRUE thì tổng điều kiện sẽ tính là TRUE, nhưng nếu một (hoặc nhiều) tiêu chí là FALSE thì điều kiện sẽ tính là FALSE.
Sử dụng phương pháp này, có thể thêm nhiều tiêu chí khác nhau vào công thức này.
Nếu tối đa - Nhiều tiêu chí với tham chiếu ô
Thông thường, việc viết mã các giá trị vào công thức là không tốt. Thay vào đó, sẽ linh hoạt hơn khi sử dụng các ô riêng biệt để xác định tiêu chí.
Để phù hợp với Tên cửa hàng đến giá trị được hiển thị trong cột F, chúng ta có thể cập nhật công thức thành:
| 1 | {= MAX (IF ((B3: B8 = F3) * (C3: C8 |
Khóa tham chiếu ô
Để làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị các công thức mà không có tham chiếu ô bị khóa:
| 1 | {= MAX (IF ((B3: B8 = F3) * (C3: C8 |
Nhưng những công thức này sẽ không hoạt động bình thường khi sao chép và dán ở nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tham chiếu ô bị khóa như sau:
| 1 | {= MAX (IF (($ B $ 3: $ B $ 8 = F3) * ($ C $ 3: $ C $ 8 |
Đọc bài viết của chúng tôi về Khóa tham chiếu ô để tìm hiểu thêm.
Hàm MAXIFS
Người dùng Google Trang tính và Excel 2022 trở lên có thể sử dụng một Hàm MAXIFS để sao chép hoạt động của Hàm MAX và IF được hiển thị trong các ví dụ trước đó.
Ví dụ tiếp theo này sử dụng Hàm MAXIFS và DATE để hiển thị giá trị lớn nhất Kích thước đặt hàng cho mỗi Tên cửa hàng vì Ngày đặt hàng trước 30/04/2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATE (2021,4,30)) |
Hàm MAXIFS không yêu cầu người dùng nhấn CTRL + SHIFT + ENTER khi nhập công thức.
Nếu tối đa (Giá trị tối đa kèm theo điều kiện) trong Google Trang tính
Các ví dụ được hiển thị ở trên hoạt động giống hệt nhau trong Google Trang tính cũng như trong Excel, nhưng vì Hàm MAXIFS khả dụng, bạn nên sử dụng một hàm duy nhất này thay vì kết hợp Hàm MAX và IF.
Nếu cần sử dụng các ví dụ sử dụng Hàm MAX và IF, thì Google Trang tính yêu cầu bạn nhập chúng dưới dạng công thức mảng. Thay vì hiển thị công thức với dấu ngoặc nhọn mảng Excel {}, nhấn CTRL + SHIFT + ENTER sẽ tự động thêm Hàm ARRAYFORMULA xung quanh công thức:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A") * (C3: C8 |
