Вопрос или проблема
Рассмотрим следующую таблицу данных…
Год | Температура (°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)
Кроме того, я рекомендую не использовать 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 для игнорирования нечисловых данных.