Проблема со средней функцией

Question

У меня очень раздражающая проблема со средней функцией.

Я делаю средние, где иногда у меня нет данных в столбце (столбец C на рисунке ниже). Затем он записывает 0, но все равно рассматривает его как среднее число, что совсем не удобно.

Можно ли получить среднее значение, чтобы при значении 0 оно не учитывалось в среднем. В противном случае все мои данные неверны.

Спасибо за помощь!!

Answer 1

Возможно ли среднее, чтобы 0 исключалось?

Используйте AVERAGEIF или AVERAGEIFS вместо AVERAGE:

Чтобы исключить 0:

=AVERAGEIF(A1:A50,">0")

Чтобы исключить пустые ячейки:

=AVERAGEIF(A1:A50,"<>""")

Чтобы исключить 0 и пустые ячейки:

=AVERAGEIFS(A1:A50,A1:A50,">0",A1:A50,"<>""")

Замечания:

• AVERAGEIF и AVERAGEIFS были введены в Excel 2007
• Для более ранних версий Excel см . Среднее, исключающее нулевые значения.

---

Среднее значение, исключающее нулевые значения (Excel 2007 и 2010)

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

Функция листа больше всего подходит для этой цели - использовать AVERAGEIF . Вы можете использовать это следующим образом:

=AVERAGEIF(A1:A50,">0")

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

=AVERAGEIFS(A1:A50,A1:A50,">0",A1:A50,"<>""")

Конечно, если вы хотите подойти к проблеме "старая школа" (без использования AVERAGEIF или AVERAGEIFS), то есть несколько способов, которыми вы можете продолжить. Во-первых, помните, как рассчитывается среднее значение. Он определяется как сумма диапазона значений, деленная на количество элементов в диапазоне. Таким образом, вы можете вычислить среднее значение исключения, просто убедившись, что знаменатель (число, на которое вы делите) не содержит нулевых значений. Например:

=SUM(A1:A50)/COUNTIF(A1:A50,"<>0")

Этот подход использует функцию COUNTIF для определения количества ячеек в диапазоне (A1:A50) которые не содержат ноль. Если этот диапазон содержит не только нули, но и пустые ячейки, и вы не хотите, чтобы пустые ячейки фигурировали в результате, вам нужно использовать более сложную формулу:

=SUM(A1:A50)/(COUNTIF(A1:A50,"<>0")-COUNTBLANK(A1:A50)- (COUNTA(A1:A50)-COUNT(A1:A50)))

Функция COUNTIF подсчитывает ячейки, которые не имеют явного значения 0, но подсчитывает пустые и текстовые ячейки. Термин COUNTBLANK корректирует пустые ячейки, а разница между COUNTA и COUNT регулирует общее количество ячеек, содержащих текст.

Конечно, вы также можете использовать формулу массива для выполнения ваших расчетов:

=AVERAGE(IF(A1:A50<>0,A1:A50))

Помните, что формулы массива необходимо вводить с помощью комбинации Ctrl+Shift+Enter . Эта формула массива также исключает пробелы или ячейки, содержащие текст.

В общем, проще использовать функции AVERAGEIF или AVERAGEIFS .

Источник Среднее значение, исключающее нулевые значения