Excel среднее значение для ненулевых значений - Word и Excel

Среднее значение без учета ячеек с нулем

AleksFin

Дата: Среда, 07.06.2017, 11:27 |
Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Здравствуйте!

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

Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.

Пример не прилагаю, думаю он не нужен. Если нужен, приложу.

Ответить

Pelena

Дата: Среда, 07.06.2017, 11:31 |
Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 18797

Репутация:
4284
±

Замечаний:
±

Excel 2016 & Mac Excel

Здравствуйте.
Функция СРЗНАЧЕСЛИ() должна помочь

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 11:31 |
Сообщение № 3

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

СРЗНАЧЕСЛИ
СУММ/СЧЁТ

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

amfor

Дата: Среда, 07.06.2017, 11:32 |
Сообщение № 4

Группа: Проверенные

Ранг: Обитатель

Сообщений: 250

Репутация:
75
±

Замечаний:
0% ±

Excel 2016

Здравствуйте.
= СРЗНАЧЕСЛИ

Семь бед — один RESET.

Ответить

AleksFin

Дата: Среда, 07.06.2017, 11:39 |
Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

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

К сообщению приложен файл:

333.xlsx
(8.4 Kb)

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 11:43 |
Сообщение № 6

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Код

=СРЗНАЧЕСЛИ(B11:B13;»<>0″)

Или формула массива

Код

=СУММ(B11:B13)/СЧЁТ(1/B11:B13)

К сообщению приложен файл:

333_1.xlsx
(8.7 Kb)

Ответить

buchlotnik

Дата: Среда, 07.06.2017, 11:45 |
Сообщение № 7

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

Код

=СРЗНАЧЕСЛИ(B11:B13;»>0″)

К сообщению приложен файл:

333-1-.xlsx
(8.9 Kb)

Ответить

and_evg

Дата: Среда, 07.06.2017, 11:47 |
Сообщение № 8

Группа: Проверенные

Ранг: Обитатель

Сообщений: 416

Репутация:
72
±

Замечаний:
0% ±

Excel 2007

AleksFin, так вам ведь говорили:

Код

= СРЗНАЧЕСЛИ(B11:B13;»<>0″;B11:B13)

Сообщение отредактировал and_evg — Среда, 07.06.2017, 11:48

Ответить

AleksFin

Дата: Среда, 07.06.2017, 12:20 |
Сообщение № 9

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Спасибо за ответы.

В оригинальную форму когда вставляю, выдает «слишком много аргументов».

Зажимаю ctrl и подбираю по одному нужные мне ячейки, это можно обойти как-нибудь?

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 12:31 |
Сообщение № 10

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Можно. Для этого нужно показать пример оригинальной формы

Ответить

AleksFin

Дата: Среда, 07.06.2017, 12:35 |
Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Ок

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 12:46 |
Сообщение № 12

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Не, не так. Нужно понимать, почему Вы выбрали именно ячейки из 11,13,13,17 и 20 строк. Не нужно придумывать примеры, просто отрубите кусок своего файла, уберите конф. информацию, положите сюда и объясните как-то примерно так: «Если в столбце А находятся значения ХХХ, то по таким строкам нужно вычислить среднее значение по столбцу В, но не учитывая нулевые значения»
Предположу, что Вам нужно что-то примерно вот такое

Код

=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;»<>0″;A11:A21;»ХХХ»)

Или, если нужно для любого заполнения столбца А, то

Код

=СРЗНАЧЕСЛИМН(B11:B21;B11:B21;»<>0″;A11:A21;»*»)

=========
А вот это

Код

=СРЗНАЧЕСЛИ(B11;B13;B15;B17;B20;»>0″)

работать не будет. Почитайте справку по функции СРЗНАЧЕСЛИ и все сами поймете

Ответить

Sancho

Дата: Среда, 07.06.2017, 12:58 |
Сообщение № 13

Группа: Проверенные

Ранг: Обитатель

Сообщений: 279

Репутация:
19
±

Замечаний:
0% ±

2007, 2010, 2013

AleksFin, не понятно, из несвязанного диапазона надо выдернуть среднее значение среди ненулевых значений? тогда

Код

=СУММ(B11;B13;B15;B17;B20)/СУММПРОИЗВ((B11>0)+(B13>0)+(B15>0)+(B17>0)+(B20>0))

Ответить

AleksFin

Дата: Среда, 07.06.2017, 13:13 |
Сообщение № 14

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Выкладываю файлик, посмотрите пжл. Описание внутри.

К сообщению приложен файл:

1103380.xlsx
(10.3 Kb)

Ответить

_Boroda_

Дата: Среда, 07.06.2017, 13:18 |
Сообщение № 15

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Так?

Код

=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;»<>0″;Данные!D9:D18;B8&»*»)

На случай, если все нули

Код

=ЕСЛИОШИБКА(СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!F9:F18;»<>0″;Данные!D9:D18;B8&»*»);)

=
Вот теперь файл хороший и правильный.
В следующий раз тоже примерно так и делайте, тогда решение сразу получите, а не через 2 часа, как сейчас
=
И да, для цены <> не обязательно, достаточно просто >, как у Михаила

Ответить

buchlotnik

Дата: Среда, 07.06.2017, 13:20 |
Сообщение № 16

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

как-то так, наверное

Код

=СРЗНАЧЕСЛИМН(Данные!F9:F18;Данные!D9:D18;B8&»*»;Данные!F9:F18;»>0″)

К сообщению приложен файл:

1168525.xlsx
(10.7 Kb)

Ответить

Sancho

Дата: Среда, 07.06.2017, 13:40 |
Сообщение № 17

Группа: Проверенные

Ранг: Обитатель

Сообщений: 279

Репутация:
19
±

Замечаний:
0% ±

2007, 2010, 2013

AleksFin, то ли пример неудачный, то ли я чего то не понимаю? как может быть цена на изделие рассчитываться как среднее значение. в вашем варианте да, изделие из одной детали, а если из двух? одна деталь 15 другая 1, Средняя цена 8? че за бред какой то? если сравнить односоставное изделие со средней ценой 15 и двухсоставное изделие с ценой 8… в чем «соль»? какой вывод из этого следует?

Сообщение отредактировал Sancho — Среда, 07.06.2017, 13:43

Ответить

AleksFin

Дата: Среда, 07.06.2017, 14:18 |
Сообщение № 18

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Всем спасибо, очередной вопрос как всегда без сомнений решен. hands

Ответить

AleksFin

Дата: Среда, 07.06.2017, 14:39 |
Сообщение № 19

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Ребята, извините, я не полностью задачу обрисовал. :'(

Гляньте во вложении, я вторую строку добавил

К сообщению приложен файл:

7091850.xlsx
(10.8 Kb)

Ответить

Sancho

Дата: Четверг, 08.06.2017, 12:55 |
Сообщение № 20

Группа: Проверенные

Ранг: Обитатель

Сообщений: 279

Репутация:
19
±

Замечаний:
0% ±

2007, 2010, 2013

AleksFin, вы ищете машину, которой на листе «данные» нет.

Ответить

Источник

Содержание

Как посчитать среднее значение в excel без учета нулевых значений
Посчитать среднее диапазона, исключая нулевые значения
Excel. Использование формулы массива для вычисления среднего, не учитывающего нулевые значения
Как усреднить диапазон данных, игнорируя ноль в Excel?
Как усреднить диапазон данных, игнорируя ноль, в Excel?
Лучшее Инструменты для повышения производительности Office
СРЗНАЧ (функция СРЗНАЧ)
Описание
Синтаксис
Замечания
Пример
Как усреднить диапазон данных, игнорируя ноль в Excel?
Как усреднить диапазон данных, игнорируя ноль, в Excel?
Усреднить диапазон данных без нулей с помощью формулы
Среднее/суммирование/подсчет диапазона данных без нулей с помощью Kutools for Excel
Данные среднего/подсчета/суммы игнорируют нули
Лучшее Инструменты для повышения производительности Office
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Как посчитать среднее значение в excel без учета нулевых значений

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

То есть подобная, казалось бы, банальная конструкция — посчитать среднее, исключая из массива нули — не хочет работать со строковыми диапазонами. Это у меня с Excel что-то не так или нужно что-то еще сделать?

С Excel все в порядке, но нужно что-то еще сделать 🙂

Ни первая, ни вторая формулы без этого «чего-то» правильно работать не будут. Бывает, что результат совпадает с ожидаемым, но это совпадение, не более, и в каждом конкретном случае такое совпадение можно объяснить.

На рисунке пример. Формулы автора в закрашенных ячейках: в желтых для A1:A4, в зеленых для A1:D1. Ошибочно думать, что формула будет работать всегда, если она размещена в том же диапазоне строк/столбцов. Это только иллюстрация того, что такие формулы требуют особого обращения.

Тут все понятно — определяем среднее значение диапазона.

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

В поле для ввода формулы выделяем вложенную функцию с ее содержимым

нажимаем клавишу F9 . Что видим? Вместо выделенной части в формуле волшебным образом появился массив констант, состоящий из чисел и логического выражения ЛОЖЬ.

Кстати, F9 (запуск пересчета) удобно использовать для просмотра работы формулы или поиска ошибки.

Формула массива вводится тремя клавишами. Находясь в поле ввода формулы (или в режиме редактирования ячейки), нажимаем Ctrl+Shift+Enter — формула должна обрамиться фигурыми скобками. При правке фигурные скобки пропадают, завершать изменения формулы тоже нужно тремя клавишами.

Excel. Использование формулы массива для вычисления среднего, не учитывающего нулевые значения

Эта заметка продолжает цикл материалов по использованию формул массива. Если ранее вы не сталкивались с формулами массива, рекомендую прочитать:

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

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

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

Скачать заметку в формате Word, примеры в формате Excel

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

На рис. 1 показан рабочий лист, на котором вычисляется средний объем продаж группы продавцов. Формула в ячейке В14 имеет вид: =СРЗНАЧ(Продажи). Она вычисляет среднее значений из диапазона ВЗ:В10, которому присвоено имя Продажи. Некоторые продавцы не работали, но они также учитывались при вычислении среднего. Функция СРЗНАЧ игнорирует пустые ячейки, но учитывает ячейки с нулевыми значениями.

Следующая формула массива, записанная в ячейке В15, возвращает среднее без учета ячеек, содержащих 0: . Эта формула создает виртуальный массив, содержащий только ненулевые значения из диапазона Продажи. Этот массив используется в качестве аргумента в функции СРЗНАЧ.

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

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

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

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

Обычно функция «Среднее» может помочь вам вычислить среднее значение диапазона, включая нули, в Excel. Но здесь вы хотите исключить нули при применении функции Average. Как можно игнорировать нули при вычислении среднего значения?

Среднее значение диапазона данных, игнорируя нули, с помощью формулы

Среднее/суммирование/подсчет диапазона данных без нулей с помощью Kutools for Excel

Усреднить диапазон данных без нулей с помощью формулы

Если вы используете Excel 2007/2010/2013, эта простая функция AVERAGEIF может помочь вам быстро и легко решить эту проблему.

1 . Введите эту формулу = AVERAGEIF (B2: B13, » 0″) в пустую ячейку помимо ваших данных, см. Снимок экрана:

Примечание : в приведенной выше формуле B2: B13 — это диапазон данных, который вы хотите усреднить, исключая нули, вы можете изменить его по своему усмотрению. Если в диапазоне есть пустые ячейки, эта формула также усредняет данные, исключая пустые ячейки.

2 . Затем нажмите клавишу Enter , и вы получите результат, исключающий нулевые значения. См. Снимок экрана:

Примечание: Эта формула не вступит в силу в Excel 2003, если у вас есть Excel 2003, вы должны объединить функцию Sum и Countif , примените следующие формула: = СУММ (B2: B13)/СЧЁТЕСЛИ (B2: B13, «> 0») .

Среднее/суммирование/подсчет диапазона данных без нулей с помощью Kutools for Excel

Если вы хотите суммировать/усреднить/подсчитать, игнорируя нулевые ячейки, вы можете применить Kutools for Excel Select Specific Cells , чтобы выбрать ненулевые ячейки, и затем просмотрите результат расчета в строке состояния .

После бесплатной установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон, который вы хотите усреднить, нажмите Kutools > Выбрать > Выбрать определенные ячейки . См. Снимок экрана:

2. В появившемся диалоговом окне установите флажок Ячейка , а затем выберите Не равно из первого раскрывающегося списка в Определенный тип и перейдите в правое текстовое поле, чтобы ввести 0. См. снимок экрана:

3. Нажмите Ok , и все ячейки, которые больше 0, были выбраны, и вы можете просмотреть среднее значение, сумму, результат подсчета в строке состояния. См. Снимок экрана:

Совет. Если вы хотите получить бесплатную пробную версию функции Выбрать определенную ячейку , сначала перейдите к бесплатной загрузке Kutools for Excel, а затем примените операцию в соответствии с указанными выше шагами.

Данные среднего/подсчета/суммы игнорируют нули

Kutools for Excel: 300+ функций, которые вы должны иметь в Excel, 30-дневная бесплатная пробная версия отсюда.

Статья по теме:

Как вычислить среднее без максимальных и минимальных значений в Excel?

Лучшее Инструменты для повышения производительности Office

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование : быстро вставляйте сложные формулы, диаграммы и все, что y вы использовали раньше; Зашифровать ячейки паролем; Создавайте список рассылки и отправляйте электронные письма …
Панель супер формул (легко редактировать несколько строк текста и формул); Чтение макета (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон …
Объединить ячейки/строки/столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки/столбцы … Предотвратить дублирование ячеек; Сравнить диапазоны …
Выбрать повторяющиеся или уникальные строки; Выберите пустые строки (все ячейки пусты); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор …
Точное копирование нескольких ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и прочего …
Извлечь текст, добавить текст, удалить по положению, удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии …
Суперфильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцам/неделям/дням, частоте и другим параметрам; Специальный фильтр жирным шрифтом, курсивом …
Объединить рабочие книги и рабочие таблицы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF …

Группировка сводной таблицы по номеру недели, дню недели и другим параметрам … Показывать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки с формулой/именем … ->

Источник

СРЗНАЧ (функция СРЗНАЧ)

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

Описание

Возвращает среднее арифметическое аргументов. Например, если диапазон A1:A20 содержит числа, формула =СПБ(A1:A20) возвращает среднее из этих чисел.

Синтаксис

Аргументы функции СРЗНАЧ описаны ниже.

Число1 Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.

Число2. Необязательный. Дополнительные числа, ссылки на ячейки или диапазоны, для которых нужно вычесть среднее значение, не более 255.

Замечания

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

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

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

Аргументы, являющиеся значениями ошибок или текстом, которые не могут быть преобразованы в числа, вызывают ошибки.

Если логические значения и текстовые представления чисел необходимо учитывать в расчетах, используйте функцию СРЗНАЧА.

Если требуется вычислить среднее значение только для тех значений, которые удовлетворяют определенным критериям, используйте функцию СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН.

Примечание: Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения, описанных ниже.

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

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

Мода — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

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

Местонахождение флажка Показывать нули в ячейках, которые содержат нулевые значения

Откройте вкладку Файл , а затем нажмите кнопку Параметры и в категории Дополнительно найдите группу Показать параметры для следующего листа.

Пример

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

Источник

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

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

Среднее значение диапазона данных, игнорируя нули, с помощью формулы

Среднее/суммирование/подсчет диапазона данных без нулей с помощью Kutools for Excel

Усреднить диапазон данных без нулей с помощью формулы

1 . Введите эту формулу = AVERAGEIF (B2: B13, ” 0″) в пустую ячейку помимо ваших данных, см. Снимок экрана:

Примечание : в приведенной выше формуле B2: B13 – это диапазон данных, который вы хотите усреднить, исключая нули, вы можете изменить его по своему усмотрению. Если в диапазоне есть пустые ячейки, эта формула также усредняет данные, исключая пустые ячейки.

2 . Затем нажмите клавишу Enter , и вы получите результат, исключающий нулевые значения. См. Снимок экрана:

Среднее/суммирование/подсчет диапазона данных без нулей с помощью Kutools for Excel

После бесплатной установки Kutools for Excel, пожалуйста, сделайте следующее:

Данные среднего/подсчета/суммы игнорируют нули

Kutools for Excel , с более чем 300 удобными функциями, облегчит вашу работу.

Kutools for Excel: 300+ функций, которые вы должны иметь в Excel, 30-дневная бесплатная пробная версия отсюда.

Статья по теме:

Как вычислить среднее без максимальных и минимальных значений в Excel?

Лучшее Инструменты для повышения производительности Office

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование : быстро вставляйте сложные формулы, диаграммы и все, что y вы использовали раньше; Зашифровать ячейки паролем; Создавайте список рассылки и отправляйте электронные письма …
Панель супер формул (легко редактировать несколько строк текста и формул); Чтение макета (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон …
Объединить ячейки/строки/столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки/столбцы … Предотвратить дублирование ячеек; Сравнить диапазоны …
Выбрать повторяющиеся или уникальные строки; Выберите пустые строки (все ячейки пусты); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор …
Точное копирование нескольких ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и прочего …
Извлечь текст, добавить текст, удалить по положению, удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии …
Суперфильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцам/неделям/дням, частоте и другим параметрам; Специальный фильтр жирным шрифтом, курсивом …
Объединить рабочие книги и рабочие таблицы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF …

Более 300 мощных функций. Поддерживает Office/Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Источник

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

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

Описание

Синтаксис

СРЗНАЧ(число1;[число2];…)

Аргументы функции СРЗНАЧ описаны ниже.

Число1 Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.
Число2… Необязательный. Дополнительные числа, ссылки на ячейки или диапазоны, для которых нужно вычесть среднее значение, не более 255.

Замечания

Аргументы могут быть числами, именами или ссылками на диапазоны или ячейки, содержащие числа.
Логические значения и текстовые представления чисел, которые непосредственно введите в список аргументов, не учитываются.
Если аргумент является ссылкой на диапазон или ячейку, содержащую текст или логические значения, или ссылкой на пустую ячейку, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
Аргументы, являющиеся значениями ошибок или текстом, которые не могут быть преобразованы в числа, вызывают ошибки.
Если логические значения и текстовые представления чисел необходимо учитывать в расчетах, используйте функцию СРЗНАЧА.
Если требуется вычислить среднее значение только для тех значений, которые удовлетворяют определенным критериям, используйте функцию СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН.

Примечание: Функция СРЗНАЧ вычисляет среднее значение, то есть центр набора чисел в статистическом распределении. Существует три наиболее распространенных способа определения среднего значения, описанных ниже.

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

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

Местонахождение флажка Показывать нули в ячейках, которые содержат нулевые значения

Откройте вкладку Файл , а затем нажмите кнопку Параметры и в категории Дополнительно найдите группу Показать параметры для следующего листа.

Пример

Данные
10	15	32
7
9
27
2
Формула	Описание	Результат
=СРЗНАЧ(A2:A6)	Среднее значение чисел в ячейках A2–A6.	11
=СРЗНАЧ(A2:A6;5)	Среднее значение чисел в ячейках A2–A6 и числа 5.	10
=СРЗНАЧ(A2:C2)	Среднее значение чисел в ячейках A2–C2.	19

Нужна дополнительная помощь?

Источник

Добрый день,

У меня есть массив данных с колебаниями цен в процентах. Если значение <>0% то это и есть колебание. Мне необходимо рассчитать среднее значение этих колебаний. Пример прилагаю. Пытался через СРЗНАЧЕСЛИ. Нужно использовать еще ЕСЛИ?
Помогите пож-ста. Спасибо.

Пытливый

Пользователь

Сообщений: 4586
Регистрация: 22.12.2012

04.03.2015 10:31:48

Добрый день.

Код
=СРЗНАЧЕСЛИ(A3:L3;"<>0";A3:L3)

Кому решение нужно — тот пример и рисует.

	Скажите пож-ста, возможно ли данную формулу применять с ОКРУГЛ? и как? Изменено: iskander_s77 — 04.03.2015 10:39:24

	А собственно, что мешает? Формула правда станет массивной, но применять можно. С уважением, Федор/Все_просто

	Вот что получается с формулой СРЗНАЧЕСЛИ.. Почему, если там 0%? Прикрепленные файлы пример.xlsx (10.79 КБ)

Stics Пользователь Сообщений: 844 Регистрация: 12.08.2014	iskander_s77,методологически неправильно среднее изменение цен в % рассчитывать с помощью функции СРЗНАЧ или СРЗНАЧЕСЛИ. Рекомендую воспользоваться средней геометрической, т.е. функцией СРГЕОМ.

	Stics, мне необходимо среднее значение произошедших изменений, т.е. значений <>0. А среднее геометрическое учитывает и нулевые значения.

Stics

Пользователь

Сообщений: 844
Регистрация: 12.08.2014

04.03.2015 11:00:10

Цитата
iskander_s77 написал: Вот что получается с формулой СРЗНАЧЕСЛИ.. Почему, если там 0%?

Сделайте формат ячеек общим, или примените Условное Форматирование. Увидите, что в некоторых ячейках стоит не 0, а например -1,11022302462516E-16, что и учитывается при расчете функции СРЗНАЧЕСЛИ

Изменено: Stics — 04.03.2015 11:58:18

	вот поэтому я и спрашиваю про округление. Мне формат процентный необходим

Пытливый

Пользователь

Сообщений: 4586
Регистрация: 22.12.2012

#11

04.03.2015 11:49:19

Может быть тогда проще сначала к исходным данным применить такой коротенький макрос:

Код

Sub iskander_s77()
Dim objC As Range
    For Each objC In Selection 'для каждой ячейки в выделении
    'вставляем формулу округления значения ячейки до 4 знака после запятой
        objC.FormulaLocal = "=ОКРУГЛ(" & objC.Value & ";4)"
    Next
End Sub

с его помощью округлить исходные значения до 4 знака после запятой, а уж потом использовать СРЗНАЧЕСЛИ () ?
Выделяете диапазон с исходными данными, запускаете макрос.

Кому решение нужно — тот пример и рисует.

	Пытливый, дело в том что я не работаю с макросами — еще больше запутаюсь (( Изменено: iskander_s77 — 04.03.2015 11:54:17

Сергей

Пользователь

Сообщений: 11251
Регистрация: 01.01.1970

#13

04.03.2015 12:02:05

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

Код
=СУММПРОИЗВ(ОКРУГЛ(A3:K3;2))/СУММПРОИЗВ(--(ОКРУГЛ(A3:K3;2)<>0))

Лень двигатель прогресса, доказано!!!

iskander_s77 Пользователь Сообщений: 28 Регистрация: 02.03.2015	#14 04.03.2015 12:11:13 Сергей, спасибо большое.

Источник

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

С Excel все в порядке, но нужно что-то еще сделать 🙂

Тут все понятно — определяем среднее значение диапазона.

В поле для ввода формулы выделяем вложенную функцию с ее содержимым

Кстати, F9 (запуск пересчета) удобно использовать для просмотра работы формулы или поиска ошибки.

Вот и дошли до «чего-то».

Скачать заметку в формате Word, примеры в формате Excel

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

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

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

Среднее значение диапазона данных, игнорируя нули, с помощью формулы

Среднее/суммирование/подсчет диапазона данных без нулей с помощью Kutools for Excel

Усреднить диапазон данных без нулей с помощью формулы

1 . Введите эту формулу = AVERAGEIF (B2: B13, » 0″) в пустую ячейку помимо ваших данных, см. Снимок экрана:

2 . Затем нажмите клавишу Enter , и вы получите результат, исключающий нулевые значения. См. Снимок экрана:

Среднее/суммирование/подсчет диапазона данных без нулей с помощью Kutools for Excel

После бесплатной установки Kutools for Excel, пожалуйста, сделайте следующее:

Данные среднего/подсчета/суммы игнорируют нули

Kutools for Excel: 300+ функций, которые вы должны иметь в Excel, 30-дневная бесплатная пробная версия отсюда.

Статья по теме:

Как вычислить среднее без максимальных и минимальных значений в Excel?

Лучшее Инструменты для повышения производительности Office

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Повторное использование : быстро вставляйте сложные формулы, диаграммы и все, что y вы использовали раньше; Зашифровать ячейки паролем; Создавайте список рассылки и отправляйте электронные письма …
Панель супер формул (легко редактировать несколько строк текста и формул); Чтение макета (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон …
Объединить ячейки/строки/столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки/столбцы … Предотвратить дублирование ячеек; Сравнить диапазоны …
Выбрать повторяющиеся или уникальные строки; Выберите пустые строки (все ячейки пусты); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор …
Точное копирование нескольких ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и прочего …
Извлечь текст, добавить текст, удалить по положению, удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии …
Суперфильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцам/неделям/дням, частоте и другим параметрам; Специальный фильтр жирным шрифтом, курсивом …
Объединить рабочие книги и рабочие таблицы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF …

Источник

Как посчитать среднее значение в excel без учета нулевых значений

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

Excel. Использование формулы массива для вычисления среднего, не учитывающего нулевые значения

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

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

Лучшее Инструменты для повышения производительности Office

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

СРЗНАЧ (функция СРЗНАЧ)

Описание

Синтаксис

Замечания

Пример

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

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

Лучшее Инструменты для повышения производительности Office

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

Описание

Синтаксис

Замечания

Пример

Нужна дополнительная помощь?

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

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

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

Лучшее Инструменты для повышения производительности Office

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

А вот еще интересные статьи: