Đôi khi, thay vì chỉ tìm số lớn nhất (tối đa) cho tất cả dữ liệu, bạn cần tìm số lớn nhất trong một tập con — như số dương lớn nhất hoặc số âm lớn nhất.
Nếu lượng dữ liệu nhỏ, việc này có thể dễ dàng thực hiện bằng cách thủ công chọn đúng phạm vi cho hàm MAX.
Trong trường hợp khác, như mẫu dữ liệu lớn chưa sắp xếp, chọn đúng phạm vi có thể khó khăn hoặc không thể.
Bằng cách kết hợp hàm IF với MAX trong công thức mảng, các điều kiện — như chỉ số dương hoặc chỉ số âm — có thể dễ dàng thiết lập để chỉ dữ liệu khớp tham số này được kiểm tra bởi công thức.
Phân tích công thức mảng MAX IF
Công thức dùng trong hướng dẫn này để tìm số dương lớn nhất là:
=MAX( IF( A1:B5>0, A1:B5 ))
Đối số value_if_false của hàm IF, là tùy chọn, bị bỏ qua để rút gọn công thức. Nếu dữ liệu trong phạm vi chọn không đáp ứng tiêu chí — số lớn hơn không — công thức sẽ trả về 0.
Công việc của từng phần công thức:
- Hàm IF lọc dữ liệu để chỉ những số đáp ứng tiêu chí chọn được chuyển cho hàm MAX
- hàm MAX tìm giá trị cao nhất cho dữ liệu đã lọc
- Công thức mảng — được chỉ bởi dấu ngoặc nhọn { } bao quanh công thức — cho phép đối số logical_test của hàm IF tìm kiếm toàn bộ phạm vi dữ liệu để tìm sự khớp — như số lớn hơn không — thay vì chỉ một ô đơn lẻ
Công thức CSE
Công thức mảng được tạo bằng cách nhấn phím Ctrl, Shift và Enter trên bàn phím cùng lúc sau khi đã gõ xong công thức.
Kết quả là toàn bộ công thức — kể cả dấu bằng — bị bao quanh bởi dấu ngoặc nhọn. Ví dụ:
{=MAX( IF( A1:B5>0, A1:B5 ) )}
Vì các phím nhấn để tạo công thức mảng, chúng đôi khi được gọi là công thức CSE.
Ví dụ công thức mảng MAX IF của Excel
Như thấy trong hình trên, ví dụ hướng dẫn này dùng công thức mảng MAX IF để tìm giá trị dương lớn nhất và âm lớn nhất trong một dãy số.
Các bước dưới đây trước tạo công thức tìm số dương lớn nhất, tiếp theo là các bước cần thiết để tìm số âm lớn nhất.
Nhập dữ liệu hướng dẫn
- Nhập các số như hình trên vào ô A1 đến B5 của bảng tính
- Ở ô A6 và A7 gõ nhãn Max Positive và Max Negative
Nhập công thức lồng MAX IF
Vì chúng ta tạo cả công thức lồng và công thức mảng, cần gõ toàn bộ công thức vào một ô bảng tính đơn lẻ.
Sau khi nhập công thức KHÔNG nhấn phím Enter trên bàn phím hoặc click chuột vào ô khác vì chúng ta cần biến công thức thành công thức mảng.
- Click vào ô B6 — vị trí hiển thị kết quả công thức đầu tiên
- Gõ sau đây:
=MAX( IF ( A1:B5>0, A1:B5 ) )
Tạo công thức mảng
- Nhấn giữ phím Ctrl và Shift trên bàn phím
- Nhấn phím Enter trên bàn phím để tạo công thức mảng
- Kết quả 45 sẽ xuất hiện ở ô B6 vì đây là số dương lớn nhất trong danh sách
Nếu Click vào Ô B6, Công thức Mảng Hoàn Chỉnh có thể thấy trong thanh công thức phía trên bảng tính
{ = MAX( IF( A1:B5>0, A1:B5 ) ) }
Tìm số âm lớn nhất
Công thức tìm số âm lớn nhất khác với công thức đầu tiên chỉ ở toán tử so sánh dùng trong đối số logical_test của hàm IF.
Vì mục tiêu bây giờ là tìm số âm lớn nhất, công thức thứ hai dùng toán tử nhỏ hơn (<), thay vì toán tử lớn hơn (>), để chỉ kiểm tra dữ liệu nhỏ hơn không.
- Click vào ô B7
- Gõ sau đây:
=MAX( IF ( A1:B5<0, A1:B5 ) )
- Làm theo các bước trên để tạo công thức mảng
- Kết quả -8 sẽ xuất hiện ở ô B7 vì đây là số âm lớn nhất trong danh sách
Nhận lỗi #VALUE! làm kết quả
Nếu ô B6 và B7 hiển thị giá trị lỗi #VALUE! thay vì các đáp án trên, có thể do công thức mảng chưa được tạo đúng.
Để khắc phục, click vào công thức trên thanh công thức và nhấn lại phím Ctrl, Shift và Enter trên bàn phím.