Минимальное значение без учёта нулей - Приёмы работы с формулами - Эффективная работа в Excel - Статьи об Excel

Вход

Регистрация

Напомнить пароль

= Мир MS Excel/Статьи об Excel

Главная » Статьи » Эффективная работа в Excel » Приёмы работы с формулами

Минимальное значение без учёта нулей

Исходные данные Исходными данными может быть любой числовой набор данных, таких как горизонтальный или вертикальный диапазон, двумерный массив или даже не смежные диапазоны или отдельно заполненные ячейки. Для удобства я взял одномерный вертикальный массив А1:А5 Для решения этой задачи можно использовать как дополнительный столбец с промежуточной формулой, так и одну формулу массива 1. Решение с дополнительным столбцом В В1 напишите формулу =ЕСЛИ(A1<>0;A1;"") и скопируйте её на диапазон В2:В5. Эта промежуточная формула в дополнительном столбце будет "убирать" из исходных данных нули, заменяя их на пустую строку "" В С1 напишите формулу =МИН(B1:B5) Она и вернёт искомый результат - число 3 Если в диапазоне есть отрицательные числа, то вместо формулы =ЕСЛИ(A1<>0;A1;"") надо использовать формулу =ЕСЛИ(A1>0;A1;"") 2. Решение формулой массива Для решения этой задачи используйте такую формулу массива: =МИН(ЕСЛИ(A1:A5<>0;A1:A5)) Как это работает: Вот эта часть формулы A1:A5<>0 формирует массив {ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ИСТИНА}, после чего применяя функцию ЕСЛИ() мы преобразуем массив в следующий: {5:7:ЛОЖЬ:ЛОЖЬ:3}. Так как функция МИН() игнорирует текстовые и логические значения, то в итоге получаем искомое - число 3 Если в диапазоне есть отрицательные числа, то вместо формулы =МИН(ЕСЛИ(A1:A5<>0;A1:A5)) надо использовать формулу =МИН(ЕСЛИ(A1:A5>0;A1:A5)) 3. Решение простой формулой Используйте такую формулу: =НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;0)+1) Как это работает: С помощью функции СЧЁТЕСЛИ мы подсчитываем количество нулей в диапазоне и прибавив к этому количеству единицу мы возвращаем первое наименьшее число более нуля с помощью функции НАИМЕНЬШИЙ Если в диапазоне есть отрицательные числа, то вместо формулы =НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;0)+1) надо использовать формулу =НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;"<=0")+1) Примечание: Решение формулой массива можно применять не только к данным находящимся на листе, но и для виртуальных массивов В файле -примере приведены все решения, включая варианты для диапазонов, содержащих отрицательные числа Область применения: Любая версия Excel Комментарий: Этот же прием можно применять для получения среднего значения без учёта нулей в любой версии Excel, хотя начиная с версии Excel 2007, появилась функция СРЗНАЧЕСЛИ()

1 2 3 4 5 Категория: Приёмы работы с формулами \| Добавил: Serge_007 (23.09.2013)

Просмотров: 58854 \| Комментарии: 15 \| Теги: МИНЕСЛИ, Минимальное значение без учёта нуле, минимум \| Рейтинг: 4.8/4

Всего комментариев: 14

Порядок вывода комментариев:

0

Спам

1 Alexey19781978 (24.01.2016 19:01) [ Материал]

В формулах маленькая ошибка. Перед словом СЧЁТЕСЛИ должна стоять запятая, а не ;
=НАИМЕНЬШИЙ(A1:A5;СЧЁТЕСЛИ(A1:A5;"<=0")+1)
=НАИМЕНЬШИЙ(A1:A5,СЧЁТЕСЛИ(A1:A5;"<=0")+1)

0

2 Serge_007 (25.01.2016 20:18) [ Материал]

Alexey19781978, не путайте тех, кто будет читать статью

Разделители можно задавать любые, я в статье использовал те, которые используются в русской локали по умолчанию

0

Спам

3 Valery_Li (27.01.2016 07:08) [ Материал]

Все работает как надо. Хотелось бы также рассмотреть примеры, как получить список ненулевых, неотрицательных, непустых, числовых или только текстовых значений.
Спасибо!

0

Спам

4 flash708 (28.12.2017 10:37) [ Материал]

Формулы хорошие, но очень прошу добавить измененную формулу =МИН(ЕСЛИ(A1:A5<>0;A1:A5)) так, чтобы она работала для несмежных диапазонов (не A1:A5 а например A1; B2; C3)/

0

Спам

5 Clydefeala (18.04.2018 01:24) [ Материал]

СПАМ

Спам-сообщение скрыто. Показать

0

Спам

6 LizanoP (18.04.2018 13:12) [ Материал]

СПАМ

0

Спам

7 HopHeinna (19.04.2018 14:42) [ Материал]