В таблице MS Excel, как усреднить все числовые значения в заданном столбце?

Вопрос или проблема

Рассмотрим следующую таблицу данных…

Год Температура
(°F)
2000 30.3
2001 M
2002 36.1
2003 M

Обратите внимание, что половина значений — это одиночный символ или строка, такие как M

Другая половина значений — это числа с плавающей запятой, такие как 36.1.

Какую формулу в Excel мы используем, чтобы усреднить все числовые значения? То есть мы хотим исключить все буквы M из нашего среднего значения.

Следующая формула моя отправная точка…

=AVERAGEIF(INDIRECT("Sheet1!B2:B"&ROWS(B:B)), "<> 0")
Язык
таблиц
Простой
английский
INDIRECT("Sheet1!B2:B"&ROWS(B:B)) Весь столбец B с листа 1, за исключением верхней строки столбца B
(игнорирует заголовки столбцов)
<> 0 не равно нулю

Мы хотим изменить <> 0 (“не равно нулю“) на что-то вроде “является числом“.

Обратите внимание, что строка текста M не является числом.

Десятичные числа в формате с плавающей запятой (такие как 36.12) являются числами… или, возможно, это числовая строка.


Что мы должны заменить в <> 0, чтобы функция AVERAGEIF пропускала все нечисловые строки в среднем столбце?

AVERAGE игнорирует нечисловые значения.

Второй пункт под “Замечаниями” на странице по ссылке выше:

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

С вашими исходными данными эти две формулы дают одинаковый результат:

=AVERAGE(B2:B5)
=AVERAGE(B2,B4)

enter image description here

Кроме того, я рекомендую не использовать INDIRECT для исключения первой строки, так как она будет проигнорирована AVERAGE в любом случае (поскольку заголовок содержит текст).

Так что этого достаточно:

=AVERAGE(B:B)

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

=AVERAGE(TRIMRANGE(B:B))

Или с оператором обрезки ссылок, вот так:

=AVERAGE(B:.B)

.

Ответ или решение

Чтобы вычислить среднее значение всех числовых данных в заданном столбце в Microsoft Excel, важно правильно учитывать, какие данные должны быть исключены из расчета. Рассмотрим исходные данные, где столбец «Температура» содержит как числовые значения, так и текстовые представления (например, буква "M"). Наша задача — игнорировать текстовые элементы и учитывать только числовые данные.

Теория:

Функция AVERAGE в Excel предназначена для вычисления среднего арифметического числовых значений в диапазоне, автоматически игнорируя любые текстовые значения. Это означает, что текстовые данные, такие как "M", не будут включены в вычисление среднего значения. Excel уже обладает встроенными механизмами для этого, что упрощает подобные задачи.

Пример:

Рассмотрим таблицу значений:

Год Температура (°F)
2000 30.3
2001 M
2002 36.1
2003 M

Здесь температуры 2001 и 2003 годов представлены буквами "M". Если использовать AVERAGE(B2:B5), функция автоматически исключит значения "M" и рассчитает среднее только для чисел (30.3 и 36.1).

Применение:

Для решения поставленной задачи и получения среднего значения по указанному столбцу, достаточно использовать следующую формулу:

=AVERAGE(B:B)

Данная формула работает через весь столбец B и игнорирует все текстовые элементы, поскольку AVERAGE автоматически учитывает только числовые данные. Не стоит беспокоиться о заголовках столбцов (если они присутствуют), так как они также будут проигнорированы.

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

=AVERAGE(TRIMRANGE(B:B))

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

Оцените материал
Добавить комментарий

Капча загружается...