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

Вход

Регистрация

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

 

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

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

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

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


Всего комментариев: 5
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    sipfake1ea   (19.01.2018 09:47)
   переписка с кидалами - не попадитесь

были проплачены услуги за регистрацию в системе 2300р и +15 евро на баланс

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

через некоторое время нельзя зайти в аккаунт и техподдержка на просьбу уладить проблему только игнорит

дай бог, если вы (техподдержка) это читаете, благословение на весь ваш кидальный бизнес, на все последующие годы-
чтоб вас также по жизни все швыряли и люди знали какие вы непорядочные свиньи
чтоб поскорей загнулись ваши кидальные сайты. дешевки.

переписка прилагается -
контакты непорядочных кидал - http://sip-system.com/
ICQ: 222-443
Skype: www.sip-system.com
E-Mail: support@sip-system.com
Telegram, Viber, WhatsApp: +79854165502
http://sipfake.ru/
support@sipfake.ru
----------------------

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