Могут быть моменты, когда вам нужно получить наименьшее (или наибольшее)

значение из диапазона, если наименьшее (или наибольшее) значение не равно нулю. Например, у вас может быть диапазон значений, таких как \ {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))

Эти формулы будут работать для любого диапазона, если только диапазон не состоит полностью из нулей. В этом случае #NUM! ошибка возвращается.

Если вы используете 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.)

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (9750) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Deriving_High_and_Low_Non-Zero_Values ​​[Получение высоких и низких ненулевых значений].