Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как посчитать количество ячеек с ненулевыми значениями в Excel?

Среднее значение без учета нулей в excel

Здравствуйте, подскажите пожалуйста, есть ряд числовых ячеек, например их количество 40, некоторые имеют значения, а некоторые нулевые. Нужно сложить ненулевые ячейки и разделить на их количество, а количество ненулевых ячеек постоянно разное. Очень помог ваш ресурс.

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

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

А теперь попробуем еще усложнить задачу. Допустим нам надо найти среднее значение по диапазону с условием, что суммируются значения больше "19". В этом случае критерий отбора влияет не только на количество значений но и на сумму. Поэтому вместо функции СУММ() мы применим функцию СУММЕСЛИ() и формула будет выглядеть так.

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

В этой статье мы продолжаем цикл статей о формулах массивов. Это отличный учебник по формулам массивов, если вы никогда не сталкивались с ними раньше:

Сделав это, вы сможете оценить формулы массивов и заинтересоваться ими. Если вас не нужно будоражить, и вы хотите понять это мощное оружие Excel, но не знаете, с чего начать, прочтите «Введение в формулы массивов» и «Некоторые примеры формул массивов».

Читайте так же:
Как преобразовать дату и время из одного часового пояса в другой в Excel?

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

Если вы уже постигли азы, предлагаю вам продолжить знакомство с формулами массива вместе с Джоном Уокенбахом и его книгой MS Excel 2007. Библия пользователя. – М.: Издательский дом «Вильямс», 2008. – 816 с.

Скачайте записку в формате Word и примеры в формате Excel.

Вычисление ненулевого среднего значения

На рисунке 1 показан рабочий лист, который рассчитывает средний объем продаж группы продавцов. Формула в ячейке B14 выглядит следующим образом: =Sales. Она вычисляет среднее значение значений из диапазона BZ:B10, который назван Sales. Некоторые продавцы не работали, но они также были учтены при расчете среднего значения. Ячейки с нулевыми значениями игнорируются при использовании SRSNACH, но пустые ячейки, на самом деле, учитываются.

Ячейка B15 приведенной ниже формулы массива возвращает среднее значение без учета ячеек с 0: <=SWF(IF(Sales>0;Sales))>. В этой формуле только ненулевые значения из поля Sales включаются в виртуальный массив. Функция SRSNAME использует этот массив в качестве параметра.

Тот же результат можно получить с помощью обычной формулы (не формулы массива), записанной в ячейке В16: =СУММ(Продажи)/СЧЁТЕСЛИ(Продажи;»>0″). Эта формула использует функцию СЧЁТЕСЛИ для определения числа ненулевых значений в заданном диапазоне, на которое затем делится сумма значений этого диапазона.

Если диапазон может содержать отрицательные значения и при этом необходимо вычислить среднее значение, не содержащее нулевых значений, формулу массива следует немного изменить: <=SIGNAL(IF(Sales<>0;Sales))>.

Рисунок 1: Табличная формула для вычисления среднего значения без учета нулевых значений

Примечание: Мы стараемся как можно быстрее предоставлять актуальные справочные материалы на вашем языке. Он может содержать неточности и грамматические ошибки, поскольку эта страница была переведена автоматически. Эта статья важна для нас, поэтому мы надеемся, что вы найдете ее полезной. Вы можете сообщить нам, если она оказалась полезной, нажав на кнопки внизу страницы. Для вашего удобства здесь также находится ссылка на оригинал (на английском языке).

Читайте так же:
Как получить текущее название книги в Excel?

В этой статье описывается синтаксис и использование функции COUNTERFUNCTION в программе Microsoft Excel.

Описание

Рассчитывает среднее значение (среднее арифметическое) для всех ячеек, условия которых совпадают.

Синтаксис

ОПРЕДЕЛИТЬ(диапазон, условия, [средние значения диапазонов])

Аргументы для функции SRZNACELY приведены ниже.

Диапазон. Обязательно. Одна или несколько ячеек для усреднения, включая числа или имена, таблицы или ссылки, содержащие числа.

Условие. Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, "32", ">32", "яблоки" или B4.

Интеркаляция. Дополнительно. Фактический набор ячеек для расчета среднего значения. Если этот параметр не указан, используется диапазон.

Замечания

Те ячейки, которые содержат значения TRUE или FALSE в диапазоне игнорируются.

Если ячейка в "диапазоне_усреднения" пустая, функция СРЗНАЧЕСЛИ игнорирует ее.

Если диапазон представляет собой значение или пустой текст, SRZNACELY возвращает значение ошибки #DIV0!

Если ячейка в условии пустая, "СРЗНАЧЕСЛИ" обрабатывает ее как ячейки со значением 0.

Если ни одна из ячеек в диапазоне не соответствует критериям, SRZNACELY возвращает значение ошибки #DIV/0!

В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак — это один символ; звездочка — это любая последовательность символов. Если это вопросительный знак или звездочка, то перед ними должна стоять тильда (

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

Если ассортимент

Примечание: Функция SRZNACHELY измеряет среднее значение, т.е. центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения: :

Читайте так же:
Как перечислить все файлы в папке и подпапках на листе?

Среднее значение — это среднее арифметическое, которое рассчитывается путем сложения набора чисел и последующего деления полученной суммы на их количество. Например, среднее значение чисел 2, 3, 5, 7 и 10 равно 5, что является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана — это число, которое находится в середине группы чисел, то есть половина чисел имеет значения больше медианы, а половина — меньше медианы. Например, медиана для чисел 2, 3, 3, 5, 7 и 10 равна 4.

Он определяется как число, которое встречается наиболее часто в данной группе. Например, мода для чисел 2, 3, 3, 5, 7 и 10 будет равна 3.

Если распределение набора чисел симметрично, то все три значения центральной тенденции будут совпадать. Если распределение набора перекошено, значения могут быть другими.

Примеры

Образцы данных из следующей таблицы можно скопировать и вставить в ячейку A1 нового листа Excel. Выделите результаты формулы и нажмите клавишу F2, затем нажмите Enter, чтобы отобразить их. Изменение ширины столбцов может помочь вам увидеть все данные.

Как посчитать количество ячеек не равных заданному значению

Если вы хотите подсчитать количество ячеек, содержащих значения, которые не равны определенному значению, вы можете использовать функцию CALCULATE. В общем виде формулы (см. выше) rng обозначает диапазон ячеек, а X — значение, которое вы не хотите считать. Все остальные значения будут учтены.

Количество ячеек, не равных критерию

Здесь приведена формула активной ячейки

SCREEN подсчитывает количество ячеек в диапазоне, которые соответствуют критериям.

В примере, мы используем «<>» (логический оператор «не равно») для подсчета ячеек в диапазоне D5:D11, которые не равны «Готово». СЧЁТЕСЛИ возвращает число в качестве результата.

Читайте так же:
Как переместить ось Y влево / вправо / в середину в диаграмме Excel?

СЧЁТЕСЛИ не чувствительна к регистру. В этом примере слово «готово» может появиться в любой комбинации прописных / строчных букв.

Как следствие, вы можете использовать значение из другой ячейки как часть критерия, используя символ соединения амперсанд (&):

Если значение в ячейке A1 равно «100», критерии будут «<> 100» после конъюнкции, и СЧЁТЕСЛИ будет считать ячейки не равные 100.

Количество ячеек, не равных нескольким критериям

= СЧЁТЗ (диапазон) — СУММПРОИЗВ (СЧЁТЕСЛИ (диапазон; значения))

Для подсчета ячеек, которые не равны нескольким критериям (т.е. не равны x, y, z и т.д.), можно использовать формулу на основе СКРУТНО, СУММПРОИЗВ и СКРУТЦ.

Количество ячеек, не равных нескольким критериям

В представленном примере формула находится в H5:

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

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

Эта формула использует именованный диапазон «Критерии» (E5: E7) для хранения значений, которые мы не хотим рассчитывать.

Мы начинаем с подсчета всех значений в диапазоне с помощью функции СКРУТЦ:

Затем, используя команду SCREEN, мы создаем список всех значений, которые мы не хотим считать, т.е.

Поскольку диапазон содержит несколько значений, SCAN возвращает несколько результатов. В примере мы получаем массив значений следующего вида:

И использовать функцию SUMMPROIZE для суммирования всех элементов массива, мы получим 5. Это число затем вычитается из исходной суммы, чтобы получить конечный результат.

Использование SUMPROIZV вместо SUMM устраняет необходимость использования формулы массива.

Читайте так же:
Как посчитать количество вхождений за год / квартал / месяц / неделю в Excel?

Количество ячеек, не равных х или у

Для подсчета ячеек, которые не равны той или иной, можно использовать функцию DICE с множественными критериями.

В приведенном выше примере столбец B содержит простой список цветов. Существует только 6 коробок с одним цветом, некоторые из которых дублируются.

Количество ячеек, не равных х или у

Для того, чтобы подсчитать количество ячеек, которые не равны «красный» или «синий», нужна формула в Е5:

В этом примере «rng» именованный диапазон, который равен B5:B10.

Функция SCREENSLIMN подсчитывает ячейки, удовлетворяющие одному или нескольким условиям. Для подсчета клеток должны быть выполнены все условия.

Ключевым в данном случае является использование оператора «не равно», который пишется <>.

Чтобы добавить больше критериев, необходимо добавить еще одну пару аргументов диапазон/критерий.

Альтернатива с СУММПРОИЗВ

Функция SUMPROIZE также может вычислять ячейки, удовлетворяющие более чем одному условию.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector