Đôi khi, thay vì chỉ tìm số lớn nhất hoặc tối đa cho tất cả dữ liệu của bạn; bạn cần tìm số lớn nhất trong một tập hợp con - chẳng hạn như số dương hoặc số âm lớn nhất.
Nếu số lượng dữ liệu nhỏ, công việc có thể dễ dàng thực hiện bằng cách chọn theo cách thủ công phạm vi chính xác cho hàm MAX.
Trong các trường hợp khác, chẳng hạn như một mẫu dữ liệu lớn chưa được sắp xếp, việc chọn phạm vi chính xác có thể khó khăn nếu không muốn nói là 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ó thể dễ dàng đặt các điều kiện - chẳng hạn như chỉ số dương hoặc số âm - để chỉ dữ liệu khớp với các tham số này mới được công thức kiểm tra.
MAX IF Phân tích công thức mảng
Công thức được sử 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 ngắn công thức. Trong trường hợp dữ liệu trong phạm vi đã chọn không đáp ứng tiêu chí đã đặt - các số lớn hơn 0 - công thức sẽ trả về số không (0)
Công việc của mỗi phần của công thức là:
- Hàm IF lọc dữ liệu để chỉ những số đáp ứng tiêu chí đã chọn mới được chuyển vào hàm MAX
- hàm MAX tìm giá trị cao nhất cho dữ liệu được lọc
- Công thức mảng - được biểu thị bằng dấu ngoặc nhọn {} xung quanh công thức - cho phép đối số kiểm tra logic của hàm IF tìm kiếm toàn bộ phạm vi dữ liệu cho một kết quả khớp - chẳng hạn như các số lớn hơn 0 - thay vì chỉ một ô dữ liệu duy nhất
Công thức CSE
Công thức mảng được tạo bằng cách nhấn Ctrl , Shift và Enter các phím trên bàn phím cùng một lúc khi công thức đã được nhập.
Kết quả là toàn bộ công thức - bao gồm cả dấu bằng - được bao quanh bởi dấu ngoặc nhọn. Một ví dụ sẽ là:
{=MAX( IF( A1:B5>0, A1:B5 ) ) }
Do các phím được nhấn để tạo công thức mảng, chúng đôi khi được gọi là CSE công thức.
Ví dụ về công thức mảng IF MAX trong Excel
Như đã thấy trong hình trên, ví dụ hướng dẫn này sử dụng công thức mảng MAX IF để tìm các giá trị âm và dương lớn nhất trong một dải số.
Các bước bên dưới trước tiên tạo công thức để tìm số dương lớn nhất, sau đó 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ìn thấy trong hình ảnh ở trên vào các ô từ A1 đến B5 của trang tính
- Trong các ô A6 và A7, hãy nhập các nhãn Tích cực tối đa và Phủ định tối đa
Nhập công thức lồng nhau MAX IF
Vì chúng tôi đang tạo cả công thức lồng nhau và công thức mảng, chúng tôi sẽ cần nhập toàn bộ công thức vào một ô trang tính.
Khi bạn đã nhập công thức KHÔNG ĐƯỢC nhấn Enter trên bàn phím hoặc nhấp chuột vào ô khác khi chúng ta cần chuyển công thức thành công thức mảng.
- Nhấp vào ô B6 - vị trí nơi kết quả công thức đầu tiên sẽ được hiển thị
- Nhập nội dung sau:
=MAX( IF ( A1:B5>0, A1:B5 ) )
Tạo công thức mảng
- Nhấn và giữ Ctrl và Shift các phím trên bàn phím
- Nhấn vào Enter trên bàn phím để tạo công thức mảng
- Câu trả lời 45 sẽ xuất hiện trong ô B6 vì đây là số dương lớn nhất trong danh sách
Nếu bạn nhấp vào ô B6, công thức mảng hoàn chỉnh có thể được nhìn thấy trong thanh công thức phía trên trang 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 chỉ khác với công thức đầu tiên trong toán tử so sánh được sử dụng trong đối số kiểm tra logic 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 sử dụng toán tử less than ( < ), chứ không phải toán tử lớn hơn (> ), để chỉ kiểm tra dữ liệu nhỏ hơn 0.
- Nhấp vào ô B7
- Nhập nội dung sau:
=MAX( IF ( A1:B5<0, A1:B5 ) )
- Làm theo các bước ở trên để tạo công thức mảng
- Câu trả lời -8 sẽ xuất hiện trong ô B7 vì đây là số âm lớn nhất trong danh sách
Nhận #VALUE! để có câu trả lời
Nếu ô B6 và B7 hiển thị lỗi #VALUE! giá trị lỗi thay vì các câu trả lời được chỉ ra ở trên, có thể là do công thức mảng không được tạo đúng cách.
Để khắc phục sự cố này, hãy nhấp vào công thức trong thanh công thức và nhấn Ctrl , Shift và Nhập trên bàn phím một lần nữa.