Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

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

 

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

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 35650
Главная » Статьи » Эффективная работа в 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, появилась функция СРЗНАЧЕСЛИ()
Категория: Приёмы работы с формулами | Добавил: Serge_007 (23.09.2013)
Просмотров: 11329 | Комментарии: 3 | Теги: МИНЕСЛИ, Минимальное значение без учёта нуле, минимум | Рейтинг: 5.0/3


Всего комментариев: 3
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)
   Все работает как надо. Хотелось бы также рассмотреть примеры, как получить список ненулевых, неотрицательных, непустых, числовых или только текстовых значений.
Спасибо!

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!