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

Функция рабочего листа, наиболее подходящая для этой цели, — это СРЗНАЧЕСЛИ.

Вы можете использовать его следующим образом:

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

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

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

Конечно, если вы хотите подойти к проблеме «старой школы» (без использования СРЕДНЕЛИ или СРЕДНЕЛИ), то есть несколько способов, которыми вы можете действовать. Во-первых, нужно помнить, как рассчитывается среднее значение. Он определяется как сумма диапазона значений, деленная на количество элементов в диапазоне. Таким образом, вы можете рассчитать исключительное среднее, просто убедившись, что знаменатель (число, на которое вы делите) не включает никаких нулевых значений. Например:

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

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

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

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

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

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

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

В общем, проще использовать функции СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН.

Когда бы вы не захотели их использовать? Когда вам нужно поделиться своей книгой с пользователями версий Excel до Excel 2007. (Это когда функции были добавлены в Excel.)

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (7729) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-An_Average_that_Excludes_Zero_Values ​​[Среднее значение, исключающее нулевые значения].