Как посчитать игнорирование нулей или пустых ячеек в Excel?

Как исключить 0 из Формулы MIN Excel

Мне нужно знать, как можно исключить 0 из строк и получить минимальное значение.

Но ячейку F1 также необходимо исключить.

Но если я сделаю это в Excel с =MIN(A1,B1,C1,C1,D1, E1), он возвращает 0.

Мы признательны за любую помощь.

9 ответов

• Как исключить нулевую запись из динамической формулы (SUB-TOTAL + SUMPRODUCT) в excel

Я работаю над формулой для получения стандартного отклонения. Она не работала, пока я не столкнулся с нулевым значением, которое превращает результат в #DIV/0! . Этот снимок экрана показывает ожидаемое значение. Однако, когда я использовал свою формулу, SD игрового времени вернулось 0. Как исключить это из.

У меня есть следующая формула в Excel =(MIN(H69,H52,H35,H18)*(1/H18))*10 , которая должна вернуть MIN диапазона и разделить его на текущую ячейку (*(1/H18)), а затем умножить на 10. Оператор типа NULLIF, похоже, не работает для меня. Я хочу иметь возможность (возможность) пустоты.

Нажмите Ctrl / Shift / ENTER и введите следующее в ячейку результата:

Попробуйте применить следующую формулу

Функции SMALL и FREQUENCY принимают в качестве аргументов «объединения», то есть ссылки на отдельные ячейки, разделенные запятыми и заключенные в квадратные скобки, например (A1,C1, E1).

Таким образом, формула использует FREQUENCY и INDEX для нахождения количества нулей в интервале, и если вы добавите 1, то получите значение k таким образом, что наименьшее k-ое значение всегда будет минимальным, исключая нуль.

Я полагаю, у вас нет отрицательных чисел.

Самым эффективным методом является использование формул SMALL и COUNTIF, как это показано ниже;

Наименьшее значение в наборе данных возвращает S MALL.

Где счетчик считает нули в интервале (+1) и используется для указания SMALL возвращать минимальное k-е значение.

Я не уверен, чего вы хотите, но если вы хотите исключить пустые ячейки из диапазона и опустить нули, это сработает, даже если это немного растягивает процесс:

Используйте Ctrl+Shift+Enter в качестве таблицы.

Здесь я заменяю нули на максимальное значение в списке.

Если все значения положительные, вы можете сделать — max (-n)

Я не смог воспользоваться перечисленными решениями. Ближе всех ко мне оказался шеф Виггам — у меня возникло желание добавить комментарий, но мне не хватило репутации, чтобы сделать это. Поэтому я размещаю отдельный ответ:

Затем вместо нажатия ENTER нажмите CTRL+SHIFT+ENTER и наблюдайте, как Excel добавляет < и >соответственно в начало и конец формулы (чтобы активировать формулу в массиве).

Шеф Виггам предложил использовать запятую «,» и оператор «Если», но они не работали в Excel 2013. Точка с запятой «;», а также полная шапка «IF» сделали свое дело. Хотя разница в синтаксисе незначительна, мне потребовалось 1,5 часа, чтобы понять, почему я получал ошибку с #VALUE.

Бросаю свою шляпу в кольцо:

1) Сначала мы запускаем функцию NOT на наборе целых чисел, оценивая ненули на 0, а нули на 1.

2) Затем мы ищем MAX в нашем исходном множестве целых чисел

Шаг 3. Умножьте каждое число из набора, полученного на шаге 1, на MAX, найденный на шаге 2, установив единицы на 0, а нули на MAX.

4) Далее мы добавляем набор, созданный в шаге 3, к исходному набору.

5) В конце концов, мы ищем MIN в массиве, созданном на шаге 4

Если вы знаете приблизительный диапазон чисел, вы можете заменить MAX(DIAPASS) константой. Это немного ускоряет процесс, но не настолько, чтобы конкурировать с более быстрыми функциями.

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

1.700859 Секунд Прошло | Прошло 5,301,902 Тика

Прошло 1,935807 секунды | 6 034 279 тиков

Прошло 3,127774 секунды | 9,749.865 тиков.

3.287850 Истекшие секунды | 10.248.852 Истекших тиков

Прошло 3,328824 секунды | 10 376 576 тиков прошло

3.394730 Секунд Прошло | Прошло 10 582017 Тиков

• Excel: Макрос изменения процента без нечисловых строк

У меня есть некоторые проблемы с Excel, и я хотел бы получить макрос, если это возможно, чтобы помочь мне. Проблема: Мне нужно найти процентную разницу для диапазона столбцов в строке (A1:A54). Что-то вроде =((max(xy:xy))-(MIN(xy:xy))/(MIN(xy:xy))/(MIN(xy:xy) НО мне нужно, чтобы Excel исключил.

В Excel существует как минимум две формы формул. Один из них используется для ячеек, а другой — для управления данными. Максимальная длина формулы управления данными составляет всего 210 символов. Именно эту проблему я и пытаюсь решить. Имеется набор данных с категориями и значениями категории value1.

Функция min() исключает значения BOOLEAN и STRING. если вы замените нули на «». (пустая строка) — функция min() выполнит свою работу так, как вы хотите!

Просто удалите «0» в ячейках, которые его содержат, и попробуйте снова. Это должно сработать.

Похожие вопросы:

Есть ли функция, которую я могу вставить в Excel, которая находит наименьшее значение для диапазона F3:G36 и игнорирует все ячейки, содержащие #N/A или 0? Я в основном ищу вторую низшую.

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

Я использую F# и Excel Interop для отправки данных на рабочий лист Excel. Мой первый подход заключался в установке каждой ячейки по отдельности: worksheet.Range(range1).Value2 <-.

Моя цель — разработать формулу для расчета стандартного отклонения. Я не мог добиться результата, пока не столкнулся с нулем, что делает #DIV/0! . Это скриншот ожидаемого результата.

Моя формула Excel =(MIN(H69,H52,H35,H18)*(1/H18))*10 , которая должна вернуть MIN диапазона; затем ее нужно разделить на текущую ячейку (*(1/H18)), а затем умножить на десять. У меня возникли.

Пожалуйста, если можете, предоставьте мне макрос. У меня возникли некоторые проблемы с Excel. Трудно найти разницу в процентах для ряда столбцов в строке.

Формулы доступны в Excel в двух различных формах. Одна из них используется для ячеек, а другая — для проверки данных. Максимальная длина формулы для проверки данных не должна превышать 210 символов. Вот, пожалуйста.

Как удалить формулу из пустой ячейки Excel. Например, я динамически ввожу эти формулы в одну из пустых ячеек Excel.

Я посмотрел на сайте, но советы по созданию формул Excel (формулы?) более читабельны, в целом направлены на использование именованных диапазонов или использование вспомогательных столбцов. Я не знаю.

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

Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?

Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.

И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа Редактирование —Очистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

1. Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
2. в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
   =ЕСЛИ( A1 =1;10;»»)
   =IF(A1=1,10,»»)
   в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
   Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: Файл —Параметры —Дополнительно —Показывать нули в ячейках, которые содержат нулевые значения

А если вы не создавали такой файл — он был «унаследован» или передан из другой программы, что делать? Я предлагаю этот простой код, который заменяет строки нулевой длины обычными пустыми ячейками во всех выделенных ячейках:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox "В выделенных ячейках нет значений!", vbInformation, "www.excel-vba.ru" Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value If Not IsArray(avR) Then Redim avR(1 to 1, 1 to 1) avR(1,1) = rR.Value end if For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = "" Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox "Строки нулевой длины заменены", vbInformation, "www.excel-vba.ru" Exit Sub End If MsgBox "Строк нулевой длины на листе нет или лист защищен", vbInformation, "www.excel-vba.ru" End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

1. создаем в книге новый стандартный модуль: Alt+F11 —Insert —Module()
2. копируем в созданный модуль приведенный выше код
3. выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Как посчитать игнорирование нулей или пустых ячеек в Excel?

При ежедневной работе с Excel функция среднего очень полезна. В этой статье мы расскажем, как рассчитать среднее значение ячеек со значениями (не пустых ячеек) в Excel.

• Усреднение без учета/исключение пустых ячеек с помощью формулы
• Усреднение только для ячеек со значениями (исключая пустые ячейки) с помощью Kutools for Excel (только 1 шаг).

Среднее с игнорированием / исключением пустых ячеек с формулой

Функция Average автоматически игнорирует действительно пустые ячейки. Однако, если вы отключили опцию Sкак ноль в ячейках с нулевым значением в диалоговом окне Параметры Excel (нажав Файл > Доступные опции > Дополнительно > Параметры отображения для этого рабочего листа) некоторые пустые ячейки могут иметь нулевые значения в Excel. В этом случае функция Average не исключает эти пустые ячейки с нулевыми значениями.

На самом деле мы можем исключить ячейки с нулями, чтобы решить эту проблему. Например, вы хотите усреднить диапазон A1: D13 с игнорированием / исключением пустых ячеек и нулей, вы можете применить формулу = СРЗНАЧЕСЛИ (A1: D13; «<> 0») как показано ниже:

Среднее значение только для ячеек со значениями (исключая пустые ячейки) с помощью Kutools for Excel

Microsoft Excel может автоматически усреднять выбранные ячейки и отображать рассчитанные результаты в строке состояния. Если мы можем выбрать только непустые ячейки в диапазоне, мы можем рассчитать среднее значение диапазона, исключив пустые ячейки. Утилита Kutools for Excel Выбрать непустые ячейки поможет вам сделать это немедленно.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, не требующая кредитной карты! Бесплатная пробная версия прямо сейчас!

Выберите диапазон, в котором вы хотите усреднить, за исключением пустых ячеек, и щелкните значок Кутулс > Выберите > Выберите Un blank Cells.

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

Содержит более 300 полезных инструментов для Excel. Пробная версия бесплатна в течение 30 дней, кредитная карта не требуется! Получить сейчас