Hàm tìm giá trị lớn nhất có điều kiện trong excel

MAXIF: Công thức mảng MAX+IF TÌM GIÁ TRỊ LỚN NHẤT có điều kiện. Công thức, diễn giải dễ hiểu, ngắn gọn.

Ví dụ rõ ràng, thực hành và áp dụng luôn

CÔNG THỨC MAXIF CHUNG

{= MAX (IF (iteria_range = iteria, value_range))}

—-

Bài viết liên quan:

DIỄN GIẢI CÔNG THỨC

Để tìm giá trị lớn nhất trong một phạm vi với các tiêu chí cụ thể, bạn có thể sử dụng công thức mảng cơ bản dựa trên hàm IF và hàm MAX.

Trong ví dụ hiển thị, công thức trong ô H5 là:

{=MAX(IF(D3:D8=F5,C3:C8))}

Giải thích:

{=MAX(NẾU(QUÉT CỘT D3:D8=F5,THÌ TÌM MAX CÁC GIÁ TRỊ TƯƠNG ỨNG VỚI VÙNG C3:C8))}

trong đó trả về nhiệt độ tối đa vào ngày trong H5.

Lưu ý:

• Đây là một công thức mảng và phải được kết thúc công thức bằng thao tác nhấn đồng thời tổ hợp phím: Ctrl + Shift + Enter
• Vùng so sánh và vùng quét cần có độ dài bằng nhau
• Kết quả trả về #VALUE là do 2 vùng đang không bằng nhau

CÔNG THỨC MẢNG NÀY HOẠT ĐỘNG NHƯ THẾ NÀO

Trong Ví dụ bên trên, chúng ta chỉ lấy có 6 dòng dữ liệu. Do đó viết công thức xong và nhấn Ctrl + shift + Enter sẽ trả kết quả luôn.

Với dữ liệu hiển thị chứa gần 10.000 hàng. Dữ liệu biểu thị số đọc nhiệt độ được thực hiện cứ sau 2 phút trong một khoảng thời gian. Đối với bất kỳ ngày nào (được cung cấp trong ô H5), chúng tôi muốn có được nhiệt độ tối đa vào ngày đó.

Nếu có 10.000 dòng dữ liệu bắt đầu từ 1 đến 10.000:

Bên trong hàm IF, kiểm tra logic được nhập dưới dạng D1: D10.000 = F5.

Diễn giải trong ví dụ bên trên: logic quét D3: D8 = F5

Vì chúng ta so sánh giá trị trong F5 với một phạm vi ô (một mảng), kết quả sẽ là một mảng kết quả, trong đó mỗi mục trong mảng là TRUE hoặc FALSE. Các giá trị TRUE biểu thị giá trị khớp với F5.

Đối với giá trị nếu đúng, excel trả về phạm vi C3:C8, lấy toàn bộ kết quả cột số lượng. Điều này trả về một mảng các giá trị có cùng kích thước với mảng đầu tiên.

Hàm IF hoạt động như một bộ lọc. Vì chúng ta cung cấp cho IF một mảng cho kiểm tra logic, IF trả về một mảng kết quả.

Nếu kết quả quét vùng từ D3:D8 khớp với F5 => mảng chứa giá trị. Nếu không khớp kết quả về FALSE.

Nói cách khác, chỉ có những giá trị thỏa mãn điều kiện lọc liên quan đến ô F5 (Hãng xe HD) mới trả về kết quả TRUE, và hiển thị giá trị trong hàm IF bên trên

Kết quả trả về từ hàm IF sẽ được hàm MAX làm việc tiếp và trả về giá trị đại diện cho kết quả lớn nhất, theo điều kiện

MAXIFS THÌ NHƯ NÀO

Trong Excel O365 và Excel 2019, hàm MAXIFS mới có thể tìm thấy giá trị lớn nhất với một hoặc nhiều tiêu chí mà không cần công thức mảng. Với MAXIFS, công thức tương đương cho ví dụ này là: