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

Вход

Регистрация

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

 

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

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

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

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 56881
Главная » Статьи » Эффективная работа в 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)
Просмотров: 55275 | Комментарии: 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) [ Материал]
   СПАМ

0   Спам
8    kuiafana   (23.04.2018 09:52) [ Материал]
   СПАМ

0   Спам
9    VeraElund   (29.04.2018 17:10) [ Материал]
   СПАМ

0   Спам
10    seasbub   (09.05.2018 12:49) [ Материал]
   СПАМ

0   Спам
11    fasabub   (13.05.2018 13:11) [ Материал]
   СПАМ

0   Спам
12    BogdanUnlot   (18.05.2018 19:52) [ Материал]
   СПАМ
mr vine jb

0   Спам
13    Юрий_Ф   (06.12.2022 18:23) [ Материал]
   Serge_007

Формула: =МИН(ЕСЛИ(A1:A5>0;A1:A5)) реально не работает, пишет: #ЗНАЧ!

0  
14    Serge_007   (07.12.2022 09:18) [ Материал]
   Работает

В этом можно убедиться, скачав файл, приложенный к статье
Так же результат работы формулы виден на скрине в п. 2 статьи

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