Загальна формула
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Резюме
Щоб обчислити мінімальну різницю між двома наборами значень, ігноруючи випадки, коли будь-яке значення порожнє, ви можете використовувати формулу масиву на основі функцій MIN та IF. У наведеному прикладі формула в F4:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
який повертає 115, мінімальну вартість продажу, ігноруючи випадки, коли будь-яке значення пусте.
Примітка: це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Пояснення
У наведеному прикладі метою є обчислення мінімальної різниці продажів мінус витрати, але лише тоді, коли введені обидва значення. Якщо будь-яке значення порожнє, результат слід ігнорувати. Щоб підтвердити наявність обох значень, функція IF налаштована на використання логічної логіки з цим виразом:
(B5:B12"")*(C5:C12"")
Оскільки кожен діапазон містить 8 комірок, результатом цієї операції є такий масив:
(1;1;1;0;1;1;0;0)
Цей масив діє як фільтр. У випадках, коли значення дорівнює 1, IF дозволяє передавати значення до MIN. Фактичні значення різниці обчислюються за допомогою іншої операції масиву:
B5:B12-C5:C12
що породжує цей результат:
(150;255;125;1100;150;115;-890;1025)
Після оцінки логічного тесту масив, переданий у функцію MIN, виглядає так:
(150;255;125;FALSE;150;115;FALSE;FALSE)
Зверніть увагу, що "значення різниці" для рядків, у яких порожні продажі чи вартість, тепер НЕПРАВДИ. Функція MIN автоматично ігнорує значення FALSE і повертає мінімум решти цифр, 115.
Максимальна різниця, ігноруючи пробіли
Щоб повернути максимальну різницю, ігноруючи порожні значення, ви можете замінити MAX на MIN:
(=MAX(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
Ця формула працює так само, як пояснено вище.
З MINIFS та допоміжною колонкою
Функцію MINIFS можна використовувати для вирішення цієї проблеми, але вона вимагає використання допоміжного стовпця з такою формулою:
=B5-C5
За формулою, наведеною вище у колонці D, MINIFS можна використовувати так:
=MINIFS(D5:D12,B5:B12,"",C5:C12,"")
Це не формула масиву, і її не потрібно вводити за допомогою control + shift + enter.
