Как посчитать игнорирование нулей или пустых ячеек в 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С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
- в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
=ЕСЛИ( 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
Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?
- создаем в книге новый стандартный модуль: Alt+F11 —Insert —Module()
- копируем в созданный модуль приведенный выше код
- выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
- нажимаем 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 дней, кредитная карта не требуется! Получить сейчас