Обща формула
(=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)
Забележете, че „стойността на разликата“ за редове, където са празни или Продажби, или Разходи, вече е 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.
