Công thức Excel: Chênh lệch tối thiểu nếu không để trống
Công thức Excel: Chênh lệch tối thiểu nếu không để trống

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à một 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ảng như thế này:

{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ó Doanh số hoặc Chi phí để trống giờ là FALSE. Hàm MIN tự động bỏ qua các giá trị FALSE và trả về giá trị tối thiểu của các số 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 với một 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 là không phải một công thức mảng và không cần nhập bằng control + shift + enter.

https://exceljet.net/formula/minimum-difference-if-not-blank