Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách tìm giá trị lớn nhất thỏa mãn các điều kiện cụ thể trong Excel và Google Sheets. Hãy chuẩn bị sẵn sàng để khám phá tất cả những bí mật để nắm vững tính năng này!
Hàm MAXIFS
Người dùng Google Sheets và Excel 2019 (hoặc phiên bản sau) có thể sử dụng hàm duy nhất MAXIFS. Hàm này giúp tìm giá trị lớn nhất trong các điều kiện cụ thể.
Dưới đây là một ví dụ sử dụng hàm MAXIFS và DATE để tìm “Kích thước đơn hàng” lớn nhất cho mỗi “Tên cửa hàng” trước ngày 30/04/2021:
=MAXIFS(D3:D8, B3:B8, "A", C3:C8, "<" & DATE(2021,4,30))
Một điểm đặc biệt của hàm MAXIFS là không cần phải nhấn CTRL + SHIFT + ENTER khi nhập công thức.
Hàm Max If (hàm ma trận)
Nếu bạn không có quyền truy cập vào hàm MAXIFS, bạn có thể tạo một công thức ma trận để mô phỏng hoạt động của nó.
Hàm MAX cho phép xác định giá trị lớn nhất trong một dãy số. Bằng cách kết hợp hàm này với hàm IF, bạn có thể xác định giá trị lớn nhất thỏa mãn một điều kiện cụ thể.
Dưới đây là một ví dụ sử dụng hàm MAX và IF trong một công thức ma trận để tìm “Kích thước đơn hàng” lớn nhất cho mỗi “Tên cửa hàng”:
{=MAX(IF(B3:B8="A",D3:D8))}
Trong Office 365 và các phiên bản Excel sau 2019, bạn chỉ cần nhập công thức như bình thường (bằng cách nhấn ENTER). Tuy nhiên, đối với Excel 2019 và các phiên bản trước, bạn phải nhập công thức bằng cách nhấn CTRL + SHIFT + ENTER. Bạn sẽ thấy đóng mở ngoặc nhọn của công thức ma trận xuất hiện xung quanh công thức.
Để hiểu cách công thức hoạt động, chúng ta sẽ phân tích từng bước:
=MAX(IF(B3:B8="A",D3:D8))Trước tiên, các giá trị trong phạm vi ô được thêm vào công thức dưới dạng ma trận:
=MAX(IF({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200}))Tiếp theo, điều kiện “Tên cửa hàng” = “A” tạo ra một ma trận giá trị TRUE / FALSE:
=MAX(IF({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE},{500; 400; 300; 700; 600; 200}))Sau đó, hàm IF chuyển đổi tất cả các giá trị TRUE thành “Kích thước đơn hàng” tương ứng:
=MAX({500; FALSE; 300; FALSE; 600; FALSE})Hàm MAX xác định số lớn nhất trong ma trận, bỏ qua các giá trị FALSE, và hiển thị “Kích thước đơn hàng” lớn nhất cho “Tên cửa hàng” = “A”:
=600Thử nghiệm Trình tạo công thức AI của chúng tôi
Max Si – Nhiều tiêu chí
Cũng có thể xác định giá trị lớn nhất bằng cách sử dụng nhiều tiêu chí thông qua logic boolean.
Ví dụ này cho thấy cách tìm “Kích thước đơn hàng” lớn nhất cho mỗi “Tên cửa hàng”, nhưng chỉ cho các “Ngày đặt hàng” trước ngày 30/04/2021 bằng cách sử dụng các hàm MAX, IF và DATE:
{=MAX(IF((B3:B8="A")*(C3:C8<DATE(2021,4,30)),D3:D8))}
Lưu ý ở đây chúng ta nhân hai tập hợp tiêu chí TRUE / FALSE lại với nhau:
(B3:B8="A") * (C3:C8<DATE(2021,4,30))Nếu cả hai tiêu chí đều TRUE, thì điều kiện tổng thể sẽ được đánh giá là TRUE. Nếu một (hoặc nhiều) trong các tiêu chí là FALSE, điều kiện tổng thể sẽ được đánh giá là FALSE.
Bằng cách sử dụng phương pháp này, bạn có thể thêm nhiều tiêu chí khác nhau vào công thức này.
Max Si – Nhiều tiêu chí với tham chiếu tới ô
Nhìn chung, nên không cố định các giá trị trong công thức. Do đó, tốt hơn là sử dụng các ô riêng biệt để xác định các tiêu chí.
Để khớp “Tên cửa hàng” với giá trị được chỉ định trong cột F, chúng ta có thể cập nhật công thức như sau:
{=MAX(IF((B3:B8=F3)*(C3:C8<DATE(2021,4,30)),D3:D8))}
Khóa các tham chiếu ô
Để dễ đọc công thức của chúng ta, chúng tôi đã hiển thị công thức mà không khóa các tham chiếu ô:
{=MAX(IF((B3:B8=F3)*(C3:C8<DATE(2021,4,30)),D3:D8))}Tuy nhiên, các công thức này sẽ không hoạt động đúng nếu chúng được sao chép và dán vào nơi khác trong tệp của bạn. Do đó, bạn nên sử dụng các tham chiếu ô khóa như sau:
{=MAX(IF(($B$3:$B$8=F3)*($C$3:$C$8<DATE(2021,4,30)),$D$3:$D$8))}Đọc bài viết của chúng tôi về việc khóa tham chiếu ô để biết thêm thông tin chi tiết.
Max Si (Giá trị lớn nhất với Điều kiện) trong Google Sheets
Các ví dụ trên hoạt động hoàn toàn giống nhau trong Google Sheets như trong Excel. Tuy nhiên, vì có sẵn hàm MAXIFS, đề nghị sử dụng hàm này duy nhất thay vì kết hợp các hàm MAX và IF.
Nếu vẫn muốn sử dụng công thức MAX / IF ma trận, bạn phải nhập công thức trong hàm ARRAYFORMULA (hoặc chỉ cần nhấn CTRL + SHIFT + ENTER và Google Sheets sẽ thêm hàm này cho bạn):
=ARRAYFORMULA(MAX(IF((B3:B8="A")*(C3:C8<DATE(2021,4,30)),D3:D8)))
Bây giờ bạn đã có đủ thông tin để sử dụng hàm MAX IF trong Excel và Google Sheets, bạn có thể tận dụng tối đa tiềm năng của chúng!
Đừng quên ghé thăm trang web Crawlan.com của chúng tôi để biết thêm mẹo và hướng dẫn về việc sử dụng Google Sheets và các công cụ số khác.
Hãy sẵn sàng để ngạc nhiên bởi khả năng xử lý dữ liệu của bạn!
