Công thức chung
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Tóm lược
Để tính toán sự khác biệt nhỏ nhất giữa hai bộ giá trị, bỏ qua các trường hợp một trong hai giá trị trống, bạn có thể sử dụng công thức mảng dựa trên các hàm MIN và IF. Trong ví dụ được hiển thị, công thức trong F4 là:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
trả về 115, mức tối thiểu của chi phí bán hàng, bỏ qua các trường hợp giá trị trống.
Lưu ý: đây là công thức mảng và phải được nhập bằng Control + Shift + Enter.
Giải trình
Trong ví dụ được hiển thị, mục tiêu là tính toán chênh lệch tối thiểu của doanh thu trừ đi chi phí, nhưng chỉ khi cả hai giá trị đã được nhập. Nếu một trong hai giá trị trống, kết quả sẽ được bỏ qua. Để xác nhận rằng cả hai giá trị đều khả dụng, hàm IF được định cấu hình để sử dụng logic boolean với biểu thức sau:
(B5:B12"")*(C5:C12"")
Bởi vì mỗi phạm vi chứa 8 ô, kết quả của thao tác này là một mảng như sau:
(1;1;1;0;1;1;0;0)
Mảng này hoạt động như một bộ lọc. Trong trường hợp giá trị là 1, IF cho phép các giá trị chuyển qua MIN. Các giá trị chênh lệch thực tế được tính bằng một hoạt động mảng khác:
B5:B12-C5:C12
tạo ra kết quả này:
(150;255;125;1100;150;115;-890;1025)
Sau khi kiểm tra logic được đánh giá, mảng được truyền vào hàm MIN trông giống như sau:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Lưu ý rằng "giá trị chênh lệch" cho các hàng có Bán hàng hoặc Chi phí để trống giờ là FALSE. Hàm MIN tự động bỏ qua các giá trị FALSE và trả về số nhỏ nhất còn lại, 115.
Chênh lệch tối đa bỏ qua khoảng trống
Để trả về chênh lệch tối đa bỏ qua các giá trị trống, bạn có thể thay MAX cho MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Công thức này hoạt động theo cách tương tự như đã giải thích ở trên.
Với MINIFS và cột trợ giúp
Hàm MINIFS có thể được sử dụng để giải quyết vấn đề này, nhưng nó yêu cầu sử dụng cột trợ giúp có công thức như sau:
=B5-C5
Với công thức ở trên trong cột D, MINIFS có thể được sử dụng như sau:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Đây không phải là công thức mảng và không cần nhập bằng control + shift + enter.
