Как посчитать пустые ячейки как ноль при использовании СРЕДНЕГО в Excel?

Как найти медиану в Excel

Использование функции MEDIAN в Microsoft Excel

В Excel есть несколько функций, которые рассчитывают часто используемые средние значения. Функция MEDIAN находит медиану или среднее значение списка чисел.

Примечание Приведенные ниже инструкции относятся к Excel 2019, 2016, 2013, 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011, Excel для Office 365 и Excel Online.

Как работает функция MEDIAN

Функция MEDIAN сортирует аргументы арифметически, чтобы найти значение, которое находится между двумя крайними точками.

Если число аргументов нечетное, функция определяет среднее значение в интервале как среднее.

В случае равного количества аргументов функция берет среднее арифметическое или среднее из двух средних.

аргументы

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

MEDIAN Синтаксис функции

Синтаксис функции описывает оформление и включает имя, круглые скобки, разделители-запятые и аргументы функции.

Функция MEDIAN описана следующим образом:

= MEDIAN . Все формулы MEDIAN начинаются таким образом.
Number1: Обязательные данные, которые должны быть рассчитаны функцией.
Number2: Необязательные дополнительные значения данных для расчета в среднем. Максимально допустимое количество записей – 255, каждая из которых должна быть разделена запятой.

Аргумент может включать в себя:

Список чисел, подлежащих усреднению
Ячейки относятся к расположению данных на листе
Диапазон ссылок ячеек
Диапазон с именем.

Параметры для ввода функции и ее аргументов:

Введите полную функцию, например, = MEDIANA (A2: F2) , в ячейку
Введите функции и аргументы в диалоговое окно функции

Пример функции MEDIAN

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

Нажмите на ячейку G2, чтобы просмотреть результаты.
Нажмите кнопку Вставить функцию, чтобы открыть диалоговое окно Вставить функцию.
В списке Категория выберите Статистика.
Выберите MEDIAN из списка функций и нажмите кнопку OK.
Выберите ячейки с A2 по F2 на рабочем листе, чтобы автоматически вставить это поле.
Нажмите Enter, чтобы выйти из функции и вернуться на рабочий лист.

Читайте так же:

Как перебирать строки до пустого в столбце Excel?

Ответ 20 появится в ячейке G2.

В строке формул над рабочим листом можно увидеть полное представление функции = МЕДИАНА (A2: F2).

Почему медиана равна 20? Среднее значение рассчитано для первого примера на картинке, так как здесь нечетное количество аргументов (пять). Здесь медиана равна 20, потому что есть два числа больше (49 и 65) и два числа меньше (4 и 12).

Пустые ячейки против нулевых значений

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

Как показывают приведенные выше примеры, функция MEAN игнорирует пустые ячейки, но не те, в которых содержится нулевое значение.

Медиана изменяется между первым и вторым примерами, потому что в ячейку A3 был добавлен ноль, а ячейка A2 пуста.
Добавление ноля к ячейке A3 изменяет число аргументов, передаваемых функции в ячейке G3, с пяти до шести – четное число. В результате медиана рассчитывается путем сложения двух средних значений (12 и 20) вместе, а затем деления на два, чтобы найти их среднее значение (16).

Excel по умолчанию отображает ноль (0) в ячейках с нулевыми значениями, как в примере выше. Такие ячейки останутся пустыми, если эта опция отключена, но нулевое значение для этой ячейки все равно будет включено в расчет медианы.

Примечание Этот параметр нельзя отключить в Excel Online.

Как включить или выключить эту опцию в Excel 2019, Excel 2016, Excel 2013 и Excel 2010 :

Перейдите на вкладку Файл, затем выберите Параметры.
Перейдите к категории Дополнительно в левой части панели Параметры.
С правой стороны прокрутите вниз, пока не найдете раздел «Параметры отображения» для этого рабочего листа.
Чтобы скрыть нулевые значения в ячейках, снимите флажок Показывать ноль в ячейках с нулевыми значениями. Чтобы показать нули, установите флажок.
Сохраните изменения с помощью кнопки OK .

Читайте так же:

Как перезапустить нумерацию страниц в Excel?

Чтобы включить или выключить эту опцию в Excel 2019 для Mac, Excel 2016 для Mac и Excel для Mac 2011 :

Как посчитать среднее значение в Excel

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

Как посчитать среднее значение чисел в Excel

Найдите среднее арифметическое чисел в Excel с помощью функции КРОМЕ.

Синтаксис СРЗНАЧ

=СРЗНАЧ(число1;[число2];…) — русская версия

Аргументы СРЗНАЧ

число1 — первое число или диапазон чисел, для расчета среднего арифметического;
число2 (Опционально) — второе число или диапазон чисел для расчета среднего арифметического. Максимальное количество аргументов функции — 255.

Выполните следующие шаги для расчета:

Выделите любую ячейку;
Напишите в ней формулу =СРЗНАЧ(
Выделите диапазон ячеек, для которого требуется сделать расчет;
Нажмите клавишу «Enter» на клавиатуре

Вычисляет среднее значение для диапазона ячеек с цифрами.

Как найти среднее значение с учетом текста

Если диапазон данных содержит текст, я рекомендую использовать функцию CHANGE. Она работает аналогично функции SCRIP. Разница в том, что он учитывает пустые ячейки, текст и значения FALSE/STYLE.

Если в диапазоне данных есть пустые строки или текст, то функция воспринимает их как «ноль». Если среди данных есть логические выражения ЛОЖЬ или ИСТИНА, то ЛОЖЬ функция воспринимает как «ноль», а ИСТИНА как «1».

Как найти среднее арифметическое по условию

Функция CROWN используется для расчета среднего значения для условия или критерия. Например, предположим, у нас есть данные о продажах товаров:

Наша задача — рассчитать среднюю стоимость продаж ручек. Для этого мы выполним следующие действия:

В ячейке A13 напишем название товара «Ручки»;
В ячейке B13 введем формулу:

Читайте так же:

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

Диапазон ячеек « А2:A10 » указывает на список товаров, в котором мы будем искать слово «Ручки». Аргумент A13 это ссылка на ячейку с текстом, который мы будем искать среди всего списка товаров. Диапазон ячеек « B2:B10 » это диапазон с данными продаж товаров, среди которых функция найдет «Ручки» и вычислит среднее значение.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

голоса

Рейтинг статьи