Общая формула
(=MIN(IF((rng1"")*(rng2""),rng1-rng2)))
Резюме
Чтобы вычислить минимальную разницу между двумя наборами значений, игнорируя случаи, когда любое значение пусто, вы можете использовать формулу массива, основанную на функциях MIN и IF. В показанном примере формула в F4:
(=MIN(IF((B5:B12"")*(C5:C12""),B5:B12-C5:C12)))
который возвращает 115, минимум продажных затрат, игнорируя случаи, когда любое значение пусто.
Примечание. Это формула массива, которую необходимо вводить с помощью Control + Shift + Enter.
Объяснение
В показанном примере цель состоит в том, чтобы вычислить минимальную разницу продаж за вычетом затрат, но только после ввода обоих значений. Если какое-либо значение пусто, результат следует игнорировать. Чтобы подтвердить, что оба значения доступны, функция ЕСЛИ настроена на использование логической логики с этим выражением:
(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)
Обратите внимание, что «значение разницы» для строк, в которых либо «Продажи», либо «Стоимость» не заполнены, теперь равно 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,"")
Это не формула массива, и ее не нужно вводить с помощью Ctrl + Shift + Enter.
