Могут быть моменты, когда вам нужно получить наименьшее (или наибольшее) значение из диапазона, если наименьшее (или наибольшее) значение не равно нулю. Например, у вас может быть диапазон значений, таких как {0, 3, 1, 4, 2}. В этом случае наименьшее значение равно нулю, но на самом деле вы хотите вернуть 1.
В Excel нет встроенной функции для возврата значения, как указано здесь. Однако вы можете создать формулу, которая поможет. Предполагая, что диапазон значений, которые вы хотите проанализировать, находится в C4: C8, следующая формула вернет наименьшее ненулевое значение:
= IF (MIN (C4: C8) = 0, SMALL (C4: C8, COUNTIF (C4: C8, "= 0") + 1), MIN (C4: C8))
В этой формуле используется функция MIN, чтобы определить, является ли наименьшее значение в диапазон равен нулю. Если это так, то функция МАЛЕНЬКИЙ используется для получения наименьшего значения, исключая нули. (Функция СЧЁТЕСЛИ возвращает количество нулей в диапазоне и, следовательно, сообщает МАЛЕНЬКИЙ, какой элемент из диапазона выбрать.)
Небольшое изменение в формуле позволяет использовать ее для возврата наибольшего ненулевое число в диапазоне:
= IF (MAX (C4: C8) = 0, LARGE (C4: C8, COUNTIF (C4: C8, "= 0") + 1), MAX (C4: C8))
Эти формулы будут работать для любого диапазона, если только диапазон не состоит полностью из нулей. В этом случае # ЧИСЛО! возвращается ошибка.
Если вы используете Excel 2019 или Excel в Office 365, вы можете использовать новую функцию MINIFS. В этом примере он будет использоваться следующим образом:
= MINIFS (C4: C8, C4: C8, "" & 0)
Дополнительная информация о Функцию MINIFS можно найти на этой странице поддержки Microsoft Office:
https://support.office.com/en-gb/article/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599
Если вы предпочитаете использовать формулы массива, вы можете сделать формулу намного короче. Эта версия возвращает наименьшее ненулевое значение:
= MIN (IF (C4: C8 = 0,9 ^ 9, C4: C8))
Не забудьте введите его, используя Ctrl + Shift + Enter . Также возникает проблема, если все значения в диапазоне равны 0; в этом случае возвращается 387420489, что равно 9 ^ 9. (Он также вернет это значение, если все значения в диапазоне будут больше 387420489.)